How do I check referential integrity on MotherDuck?
Declare the check on the asset that produces the data, not in a separate suite. In Bruin you add a checks block to the column inside the MotherDuck asset definition and bruin run executes it as part of every run, failing the pipeline before downstream tables read bad data. Because the check lives in the same file as the SQL, it cannot drift out of sync with the column it protects. Great Expectations and Soda Core do the same job as a separate step, which is the better fit if you need checks across tools Bruin does not produce.
Command
bruin runDefined in
YAML
Works with
MotherDuck + Bruin CLI
What you get
How to do it
- 1
Open the MotherDuck asset that produces the table you want to guard.
- 2
Add a checks entry under the relevant column for the referential integrity rule.
- 3
Set blocking: true so a failure stops the run rather than logging a warning.
- 4
Run bruin validate to confirm the definition parses.
- 5
Run bruin run against MotherDuck and confirm the check appears in the run output.
- 6
Wire the same command into CI so the check runs before a merge reaches production.
How it works in code
columns:
- name: order_id
checks:
- name: referential_integrity
blocking: trueRun bruin run and Bruin asserts that each key has a matching parent row on MotherDuck before any downstream asset runs.
Worth knowing
A check that only warns will be ignored within a month. Set blocking: true on the checks that matter. On MotherDuck specifically, hybrid execution means you should be deliberate about which half of a query runs in the cloud
Other ways to do this
Bruin is not always the right answer. Here is where the alternatives are stronger.
| Option | When it is the better choice |
|---|---|
| Bruin | Verify that every foreign key resolves on MotherDuck, as a blocking step inside the pipeline rather than a separate monitoring job. |
| Great Expectations | The largest open-source expectation library. Better if you need checks across sources Bruin does not produce, at the cost of running it as its own layer. |
| Soda Core | Readable YAML checks that a stakeholder can review, and the strongest option for formal data contracts between teams. |
| Monte Carlo / Anomalo | Managed observability that learns MotherDuck baselines automatically. Catches what you did not write a rule for, but alerts after the fact rather than blocking. |
Common questions
How do I check referential integrity on MotherDuck?
Add a checks block to the column in the MotherDuck asset definition and run the pipeline. Bruin asserts that each key has a matching parent row and fails the run when it does not hold.
Can a referential integrity check block a MotherDuck pipeline?
Yes. Set blocking: true and the run stops on failure, so downstream assets never read the bad data. Without it the check logs a warning and the pipeline continues.
What is the best open-source way to check referential integrity?
Bruin, Great Expectations, and Soda Core are all open source and all handle it. Bruin declares the check inside the asset so it runs on every pipeline run; the other two run as a separate step, which suits checks spanning tools outside your pipeline.
Related use cases
Check referential integrity on Snowflake
How do I check referential integrity on Snowflake?
Referential integrity by warehouseCheck referential integrity on BigQuery
How do I check referential integrity on BigQuery?
Referential integrity by warehouseCheck referential integrity on Databricks
How do I check referential integrity on Databricks?
About Bruin
What is Bruin?
Bruin is an open-source, AI-powered data platform. One project covers the whole path from source to answer: data ingestion (with ingestr), SQL and Python transformations, quality checks, column-level lineage, a data catalog and scheduling. On top of that data, an AI data analyst answers questions in Slack, Microsoft Teams, Google Chat, WhatsApp, Discord, Telegram, email or the browser, and builds dashboards from a prompt.
Pipelines are plain SQL, Python and YAML files in your own Git repository, and the work runs inside your own warehouse: Snowflake, BigQuery, Databricks, Redshift, Postgres, ClickHouse, DuckDB and others. The Bruin CLI is open source under Apache 2.0 and runs on a laptop, in CI or on-prem. Bruin Cloud is optional and adds scheduling, the AI data analyst, governance, SSO and audit logs.
For data quality that means checks written next to each table, run on every load, that stop bad data before it reaches reports.
- Replaces
- Separate tools for ingestion, transformation, orchestration, data quality, cataloguing and BI
- Open source
- Bruin CLI under Apache 2.0; ingestr source-available under the Functional Source License
- Your data
- Stays in your warehouse; pipelines stay in your Git repository
- Security
- ISO/IEC 27001:2022 certified and SOC 2 Type 2 attested
- Used by
- Data teams at Lessmore, StarBerry, NativeMinds, SCG, M8 Games and Fabrikatör
Why Bruin for data quality
Checks live with the table
Declare checks on each column in the asset file: not_null, unique, accepted_values, positive, min, max, pattern and relationships.
Custom SQL when you need it
Write a custom check as a SQL query for business rules that no built-in check covers.
Bad data stops at the source
When a blocking check fails, the asset is marked failed and the assets downstream of it do not run.
Logic is tested too
Unit tests run each query against mocked input rows, so a broken join is caught before it touches real data.
How it works
01
Add checks
Add column checks and custom checks to the assets that feed your reports.
02
Run them with the pipeline
Checks run after each asset on every bruin run. In Bruin Cloud, a failure can alert a Slack channel or an email address.
03
Gate pull requests
Run bruin validate and bruin unit-test in CI so changes are tested before they merge.
Catch bad data before it ships
Bruin CLI and ingestr are on GitHub.
A demo walks through your own data.