Data quality assertions
A wrong number looks exactly like a right one. Nothing about a dashboard, a type check or a green job reveals that the load was half-empty, that a join fanned out, or that a column has been null since Tuesday. The only thing that reveals it is an assertion someone wrote in advance — placed where it fires before a consumer acts on the number, not after.
When this fires
Checks are being added to or reviewed on a dataset; a bad value reached a consumer and no check caught it; or an existing check is noisy enough that people are muting it. It does not fire for a one-off verification of a migration, for enforcing an invariant inside a single application write path, or for deciding what a metric means.
Procedure
-
Start from the failure that hurt, not from a catalog of check types. Which wrong value reached whom, how long it took to notice, and what would have caught it earliest. Generic not-null checks generated across four hundred columns produce noise, and a noisy channel is a muted channel — that is a net loss, not a neutral one.
-
Place each assertion on the lowest rung that can hold it. This is the load-bearing decision and it is usually made by accident:
- A schema constraint — NOT NULL, UNIQUE, a foreign key, a CHECK. It cannot be bypassed by
any writer and fails at write time. If the rule can be a constraint, it is a constraint
(
schema-designcovers designing it). - A gate at the pipeline's write boundary — runs before publish and blocks it. Use when the rule needs the whole batch, or the destination cannot express the constraint.
- A scheduled check against the landed table — alerts after the fact. Use when the rule needs cross-table or historical context that only exists once the data is landed.
- A check in the consumer's own query or report — last resort. Cheapest to add, latest to fire, and the easiest for everyone to scroll past.
For each assertion, record the rung and why not the one above it.
- A schema constraint — NOT NULL, UNIQUE, a foreign key, a CHECK. It cannot be bypassed by
any writer and fails at write time. If the rule can be a constraint, it is a constraint
(
-
Write every assertion as a query that returns the offending rows, not a boolean and not a count. Zero rows is the pass; a non-zero result hands the next person the reproduction case instead of a number to go and re-derive.
-
Assert freshness against event time, not against the job. A job that succeeded and wrote nothing passes every liveness check there is. Assert that the maximum event timestamp in the table is within the consumer's stated tolerance of now, per partition or per source, and derive that tolerance from what the consumer actually needs rather than from how often the job happens to run.
-
Assert volume with both a floor and a band. An absolute floor catches the empty load. A relative band against comparable recent windows — same weekday, same hour — catches the half-load that a floor sails past. Neither alone is enough: a floor misses a fifty percent drop, a band alone fires every holiday and every launch. State the comparison window you chose.
-
Assert distribution on the fields decisions depend on. Null rate, distinct cardinality, category mix against the known set of allowed values, numeric range, and a mean or percentile within a band. A column full of schema-valid wrong values passes every type check in the stack. Anchor to a known-correct set — an enum, a dimension table, a reference feed — wherever one exists; comparing to yesterday only inherits yesterday's defects.
-
Assert referential integrity and grain in the same pass. Orphans at each join key (fact rows with no matching dimension row, and the reverse where it matters), and uniqueness of the key that defines the grain. Then check fan-out explicitly: a join emitting more rows than its left side is a duplicate-key defect, and it inflates every sum computed downstream of it while leaving each individual row looking correct.
-
Reconcile against the system of record, not just internally. Row count and the sums of additive measures per window, compared to the source. Internal consistency proves the transform agrees with itself. State the tolerance and where it comes from; a tolerance chosen after seeing the discrepancy is not a tolerance.
-
Give every check a severity, an owner and an action, decided at the same time as the threshold. Blocking the publish, or alerting a named person. A check with no action is a metric. A blocking check with no recorded override path gets deleted during the first incident where someone needs the data anyway — provide the override and log its use instead.
-
Make each check fail on purpose before trusting it. Run it against a deliberately broken fixture or a slice you have corrupted — a nulled column, a dropped day, a duplicated key. An assertion never observed failing is not known to work, and this is the step almost every check skips. A check that cannot be made to fail is asserting nothing.
-
Stop before touching data to satisfy a check. Quarantining, deleting, overwriting or excluding rows so a check passes is destructive and is the user's call, not yours. So is wiring a check to page a team. Report the offending rows; propose the remedy; ask.
-
Keep the words apart in the report — asserted, executed, failed on purpose, reconciled, deployed. A check that exists in a file is written, not running.
Checklist
- Assertions derived from a real failure or a real consumer need
- Each one placed on a named rung, with the reason it is not one rung lower
- Every check returns offending rows rather than a boolean
- Freshness measured on event time, against a consumer-stated tolerance
- Volume has both an absolute floor and a relative band, with the comparison window named
- Distribution checks cover the fields decisions depend on, anchored to a known-correct set
- Orphan, grain-uniqueness and fan-out checks all present
- Source reconciliation run, with a tolerance set in advance
- Severity, owner, action and override path recorded per check
- Each check observed failing against a broken fixture
- Anything left unasserted stated explicitly
Failure handling
- A check fires and the data is fine — that is a defect in the check. Tune the threshold using the observed history or delete it. Muting it is deleting it while pretending otherwise.
- A check fires and the data is wrong — capture the offending keys before anything reruns or overwrites them; those rows are the reproduction case, and the next run may erase them.
- Reconciliation is off by a small amount — do not widen the tolerance to cover it. Find which rows account for the difference; a persistent small gap is usually a systematic rule, not noise.
- Nobody can state a freshness or volume expectation — say the check is unanchored rather than inventing a threshold. An invented threshold fires on the wrong things and teaches people the channel is unreliable.
- Only read access to the target — the assertions still stand as queries and still run. Report them as run and any gating or scheduling as not installed, rather than downgrading the whole thing to an opinion.
Evidence to report
Each assertion: the query verbatim, the rung it sits on, the threshold and where the threshold came from. The result of running it now, as rows or as an explicit zero. The deliberate-failure run that showed it works. The reconciliation figures against the source, per window, with the tolerance stated in advance. The severity, owner and action for each check. Then the fields, tables and windows deliberately left unasserted — the gap is part of the report, not an omission from it.
