Data quality checks: freshness, volume, nulls and uniqueness as a minimum test set

Goal

Detect a broken or partial load before anyone builds a report on it, with checks that are cheap enough to run on every interval and specific enough that a failure names the table and the rule.

Prerequisites

Each table has a declared grain (what one row represents) and a load timestamp column or partition. The dbt documentation (cited) models a check as a select statement that returns failing records: zero rows means the assertion holds. Out of the box it offers not-null, unique, accepted-values and relationship (foreign-key) checks per column, and a source freshness configuration with warn_after and error_after thresholds computed from a loaded_at_field; if neither threshold is provided, freshness is not calculated. The same four rules can be written as plain SQL in any scheduler.

Steps

1. Freshness: for every source table, record the expected load cadence and set a warning threshold slightly above it and an error threshold at the point where the downstream report would be wrong. Check max(loaded_at) against the clock at run time.
2. Volume: for the interval just loaded, count rows and compare with the same interval of previous periods (same weekday for daily loads). Store the counts in a small history table and flag counts outside a band defined from that history; start with a wide band and narrow it after a few weeks of observation.
3. Nulls: assert not-null on primary and foreign keys, on the timestamp used for partitioning, and on measures that reports sum. Do not assert not-null on optional attributes; instead track their null rate over time.
4. Uniqueness: assert that the columns forming the grain are unique. A duplicate here is either an upstream retry, a join that fanned out, or a rerun that appended instead of replaced.
5. Order the checks after the load and before the step that publishes the table to consumers (view swap, partition promotion), so that a failing error-level check stops publication.
6. Route each failure to the owner of the table with the failing rows attached, and record every result, including passes, so that flapping checks can be identified.

Expected result

A load that arrives late, half-complete, doubled or with lost keys is reported at the table that broke, within one scheduling interval, before it propagates.

Limits and test basis

These checks find structural breakage, not wrong values that are well-formed; business-rule checks (totals reconcile with the source system) come next. Volume bands need seasonal awareness or they page on every holiday. The set is a proposed protocol derived from the cited documentation; no detection rate is claimed.