Test at every boundary, alert on the tests, and make the tests part of the pipeline so bad data stops rather than flows.
The layers:
Contracts at the source: a schema the producer commits to, checked on ingest. A new column is fine; a renamed one fails the load.
Row-level tests on the models: not null on keys, unique on the grain, accepted values on enums, referential integrity between facts and dimensions. In dbt these are one line each and run on every build.
Freshness and volume: the table was updated when expected, and today's row count is within a band of the same day last week. Most silent failures are a job that "succeeded" with zero rows.
Reconciliation: totals against a trusted source, revenue against the billing system, users against the auth system, on a schedule.
Anomaly detection on key metrics: a sudden shift in a distribution, a null rate that jumps from 1% to 30%. Cheap statistical checks catch what schema tests cannot.
# dbt: the tests that catch most real incidents
models:
- name: fact_orders
columns:
- name: order_id
tests: [unique, not_null]
- name: customer_key
tests:
- relationships: {to: ref('dim_customer'), field: customer_key}
Then the organisational part: an owner per dataset, alerts routed to that owner, and a rule that a failed test blocks downstream models rather than warning into a channel nobody reads. Data quality is a property of the process, not a dashboard of red and green.
What they are checking: tests at the grain (unique, not null), freshness and volume, and blocking on failure.
Common mistake: a quality dashboard that reports problems after the CEO has already seen the wrong number.