Data Quality Checks: From Rules to Useful Alerts

Write the rule before the test

For a daily order table, “the data looks right” is not a rule. “Each order_id appears once, all completed orders have an amount, and the reporting total matches the source under the same refund definition” is testable. Record the expected population and the action to take when the rule fails.

Check	Example failure	Response
Uniqueness	One order appears twice	Inspect ingestion and join grain
Completeness	A completed order lacks an amount	Quarantine and trace the source
Relationships	An order has no customer record	Check late arrivals and key mapping
Freshness	The latest event is older than expected	Check schedule and source availability
Reconciliation	Report total differs from source	Compare matched populations and definitions

Use a small failure you can explain

Consider three rows: order 1 for $100, order 1 again for $100, and order 2 for $50. The total is $250, but the intended one-row-per-order population totals $150. A non-null test passes. A uniqueness test fails. This is why one passing check does not certify an entire dataset.

WITH orders(order_id, amount_usd) AS (
  VALUES (1, 100), (1, 100), (2, 50)
)
SELECT order_id, COUNT(*) AS row_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Expected result: order_id 1 with row_count 2. This query identifies the problem; it does not decide which duplicate to keep. Removing an arbitrary row is unsafe if repeated IDs can represent legitimate updates. Use source sequence, event time and documented tie-breaking rules.

Choose severity deliberately

A missing primary key can block a model. A late optional marketing attribute might trigger a warning instead. Define thresholds from the data contract and actual operational needs. Do not silence repeated alerts by widening a threshold without investigating what changed. For freshness, compare the expected schedule and time zone rather than assuming every table updates continuously.

Practice task and review

Write five checks for a subscription table. For each, list the failing rows, owner, severity and recovery action. Include a legitimate cancellation and a late event so your rules do not reject valid business behaviour. Explain which report could still be wrong even if all five checks pass. Finish with a reconciliation test that checks a business total independently.

The example uses PostgreSQL-style SQL and was executed with DuckDB. Production syntax and orchestration depend on your environment.

Documentation and further reading

dbt data quality checks