Data Quality Checks That Catch Real Incidents
Freshness, volume, schema, uniqueness, nulls and referential integrity — what to check, where to check it, and what to do when a check fails.
Data WarehousingBeginner3 min readUpdated 11 Oct 2026
Six checks that cover most failures
| Check | Question it answers | Example rule |
|---|---|---|
| Freshness | Did new data arrive on time? | max(loaded_at) within the last 2 hours |
| Volume | Is the amount of data plausible? | today's row count within ±30% of the 7-day average |
| Schema | Did columns or types change? | expected columns present with expected types |
| Uniqueness | Is the key really a key? | no duplicate order_id |
| Completeness | Are required values present? | customer_id never null |
| Referential integrity | Do references resolve? | every customer_id exists in dim_customer |
Written as SQL
-- uniqueness: should return zero rows
SELECT order_id, COUNT(*) FROM fact_orders GROUP BY order_id HAVING COUNT(*) > 1;
-- referential integrity: orphan facts
SELECT f.customer_id
FROM fact_orders f
LEFT JOIN dim_customer d ON d.customer_id = f.customer_id
WHERE d.customer_id IS NULL;
-- freshness
SELECT MAX(loaded_at) < CURRENT_TIMESTAMP - INTERVAL '2 hours' AS is_stale FROM fact_orders;Frameworks such as dbt tests, Great Expectations or Soda express the same rules declaratively; the rules matter more than the tool.
Where to check
Where data quality checks runSwipe to explore →Technical diagram
Rendering where data quality checks run
- On arrival: schema and volume, so bad files never enter the pipeline.
- Before publishing: keys, nulls and references, so consumers never see broken data.
- After publishing: freshness and drift monitoring, so you learn about silent failures before users do.
What to do when a check fails
- Quarantine failing records with the reason, instead of silently dropping them.
- Keep the last good version published so dashboards don't break.
- Record check results per run, so you can see trends rather than single failures.
A sensible starting set
For every important table: one freshness check, one volume check, a uniqueness test on its key and not-null tests on required columns. Add referential checks for fact-to-dimension joins. That small set catches most incidents.
Finished this note?