Data Quality/Referential integrity by warehouseAnalytics Engineer

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.

Get Bruin on GitHub How data quality works

Command

bruin run

Defined in

YAML

Works with

MotherDuck + Bruin CLI

What you get

Referential integrity checkblocking or warningruns on every pipeline run

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: true

Run 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