Data Quality Testing
Assert on the data, not just hope for it
Data quality testing puts assertions on your warehouse: freshness, uniqueness, not-null, accepted values. A failing test pages the owner instead of letting bad numbers poison the dashboard.
What you will learn
- Explain the difference between a test and a monitor
- Write the four classic warehouse tests on a table
- Decide how strict a model's tests should be
What is this?
A data test is a SQL assertion that should pass on every run: this column is never null, this key is unique, this value falls in an accepted set, this table's data is fresh enough. Tests run as part of the pipeline, and a failure blocks or flags the deployment — the warehouse's equivalent of a unit test.
Why do you need it?
- Bad data that reaches dashboards is hard to un-see and erodes trust across the company.
- Tests codify the contract you agreed on for a dataset.
- Early failure is cheap; a silently wrong weekly report is expensive.
Mental model
A smoke detector in the house
A data quality test
The detector does not stop the fire, but it raises the alarm the moment smoke is in the air — while a monitor is the thermostat that records the temperature all day.
How it works
- Pick the assertions that match how a dataset is used (its contract).
- Write tests: uniqueness, not_null, accepted_values, relationships, freshness.
- Run them on every pipeline run or daily, and route failures to owners.
- Grade failures: warn on soft drift, block on hard violations.
Every model ships with assertions that run before it is trusted — or deployed.
In production
- A unique test on order_id catches pipeline retries double-loading rows.
- Freshness tests page the owner when the ETL has silently died for two days.
- Accepted-value tests on status columns catch a new enum before dashboards break on it.
Best practices
- Test what the dataset contracts — keys, money, status enums — not every column.
- Favor early blocking over late warnings, but warn first for soft quality drift.
- Let upstream failures cascade visibly: a broken source should fail its staging model's freshness test.
Common mistakes
- Writing a uniqueness test on a column that was never unique — then arguing with it forever.
- Silencing failed tests 'for now' until they rot.
- Monitoring row counts but not freshness, so a dead pipeline looks fine all week.
Debugging tips
- A unique test failing at 0.01% is usually a retry/duplicate — sample the dupes before rewriting the pipeline.
- A not-null test appearing after a join is often a join-cardinality surprise, not null data.
Interview checkpoint
Hands-on lab
- Add uniqueness, not-null, accepted-values, and freshness tests
- Run tests and read a failure report
- Decide between warn and error grading for each test
Write dbt Tests on a Mart
35 min · intermediate
Quick quiz
A uniqueness test on a primary key catches
Freshness checks fail when