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

Goal

Detect an incomplete, late, duplicated or keyless load run at the table that broke, within one load interval and before anyone builds a report on it.

Prerequisites

Every table has a declared grain (what one row means) and a load-time column or partition. The dbt documentation models a check as a SELECT that returns failing records, so zero rows means the check passes. It ships the generic tests not_null, unique, accepted_values and relationships. For sources, freshness can be configured with loaded_at_field and the thresholds warn_after and error_after. If you do not use dbt, write the same four rules as SQL in your own scheduler. In the database itself, the NOT NULL, UNIQUE and FOREIGN KEY constraints documented by PostgreSQL permanently cover part of this.

Steps

1. Freshness: for each source, record the expected load cadence. Set a warning threshold just above it and an error threshold at the point where the downstream report would become wrong. Compare max(geladen_am) against the clock at run time.
2. Volume: compare the row count of the interval just loaded with the same intervals in earlier periods (for daily runs, the same weekday). Store the counts in a small history table. Start with a wide band and narrow it after a few weeks of observation.
3. Nulls: put NOT NULL on primary and foreign keys, on the partitioning time, and on measures that reports sum up. For optional attributes, track the null rate over time instead.
4. Uniqueness: the columns that make up the grain must be unique. A duplicate is a repeat from the source, a fanned-out join, or a rerun that appended instead of replacing.
5. Order: run the checks after loading and before the step that makes the table visible (view swap, partition release). That way a failure at level "error" stops publication, while "warn" only reports.
6. Store every result, including passing ones, with a timestamp, and route failures together with the affected rows to the table's owner. This makes flaky checks visible.

Expected result

A load run that arrives late, half-complete, duplicated or with lost keys is reported at the point of failure, before it propagates.

Limits and test basis

The four checks find structural breaks, not well-formed but wrong values. Reconciliation against the source system (totals match) is the next step. Volume bands without knowledge of seasonality trigger on every public holiday. This set is a proposal based on the cited documentation; no detection rate is claimed.