data-quality

Check data quality across six dimensions: completeness, uniqueness, validity, consistency, freshness, and accuracy. Use SQL assertions like duplicate key counts, NULL ratios, and orphan foreign key detection, placed as per-batch gates that fail the pipeline run on errors.

Profile each important column with row counts, NULL ratios, distinct values, min/max, and medians, then inspect top values for sentinel garbage like 'N/A', 1970-01-01, or -1. Hunt for red flags including future timestamps, encoding mojibake, and test rows in production.

Reports usually disagree due to different filters, time semantics (event vs load time, UTC vs local), join fan-out duplicating rows, different data freshness, or metric definition drift. Fix it by defining one canonical metric in a dbt model or view that both reports read.

Reconciliation compares sums in the warehouse against the source system daily, alerting when deltas exceed tolerance. It is the only check that catches cases where everything is individually valid but collectively wrong, making it essential for money and critical counts.

Use error severity to block downstream consumers for money and key integrity issues, and warn severity for distribution drift that only needs logging and metrics. Invalid records should go to a quarantine table with reason and raw payload rather than being silently dropped.