data-quality-checks

Data Quality Checks (assertions, reconciliation, honest reporting)

The seven check families, as assertions

Every useful check is a predicate plus a threshold, evaluable automatically against a table:

Completeness   assert count(*) where required_col is null = 0
               assert count(distinct load_date) over window = expected_days  (partitions present)
Uniqueness     assert count(*) = count(distinct <business_key>)              (the declared grain)
Validity/range assert age between 0 and 120; status in (...); amount >= 0; format/length rules
Consistency    assert abs(sum(a.rev) - sum(b.rev)) / sum(b.rev) <= tol       (source vs source)
Referential    assert count(child left join parent where parent.id is null) = 0   (no orphans)
Timeliness     assert max(event_ts) >= now() - <SLA interval>                (freshness)
Drift          assert abs(mean_today - mean_baseline) <= k * stddev_baseline
               assert null_rate_today <= null_rate_baseline * (1 + tol)

Each assertion needs a grain, a severity (warn vs fail) and an owner. An unowned failing check gets muted, which is worse than having no check at all.

Reconciliation, source to destination

Row counts alone prove nothing — dedup, filtering and late arrivals legitimately change them. Reconcile counts and sums per partition, every term measured rather than assumed:

source_rows - filtered_rows - deduped_rows = destination_rows
sum(source.amount) over the window = sum(dest.amount) ± rounding tolerance
per-key spot check: sample N business keys, compare field by field

Reconcile a closed window, never the currently-loading partition, and state the tolerance with its cause (float rounding, FX conversion, timezone edge) instead of picking whatever makes it pass.

Grain and duplicate keys

Use the business key that defines the grain, not the surrogate key — surrogates are unique by construction and prove nothing. Typical real keys: (event_id), (user_id, event_ts, event_type), (order_id, line_no). Test near-duplicates too: same natural key with different surrogate ids (double ingestion), same payload at different timestamps (retry storms). Document the resolution rule — keep first, keep last by ingestion time, keep highest version — because distinct picks one silently.