Automate data quality checks for a large-scale data warehouse

Official answer

Define checks from data contracts and business risks, with an owner, severity, expected frequency and response for each. A format or range check establishes validity under a rule, not the truth of the underlying real-world fact.

For an order-line dataset, check required keys, unique grain, valid currencies, relationships and source-to-target counts/amounts. Track freshness and completeness separately: a recent load can still omit half its expected rows. Add distribution or volume anomaly checks as investigation signals, with documented thresholds and expected seasonal variation.

Run inexpensive checks at ingestion and before publication. Use incremental checks where they preserve the intended coverage, plus periodic full reconciliation to catch older defects. Sampled checks must disclose their detection limits; sampling cannot certify global uniqueness or catch every rare violation.

On a blocking failure, keep the prior trusted version or quarantine the affected batch and make staleness visible. Record safe diagnostic examples, alert the owner and support deterministic repair/replay. Avoid silently accepting every detected schema change or discarding failed rows without accounting for them.

Measure detection time, unresolved exceptions, false alarms and successful repair. Version the checks and test them with deliberately invalid fixtures, including duplicates, missing partitions and late corrections. A green dashboard means the defined checks passed, not that every possible quality issue is absent.