Recap — Building a Data Quality Discipline

# Building a Data Quality Discipline Pipelines succeeding is not the same as data being correct. A pipeline can run flawlessly while producing garbage. Data quality testing closes the gap. ## What can go wrong 1. **Source schema changed** — column dropped, type changed, semantics shifted. 2. **Source data corrupted** — vendor sent bad file, upstream system buggy. 3. **Transformation logic broken** — your dbt model has a bug. 4. **Joins exploded** — many-to-many join when you expected many-to-one. 5. **Duplicates** — at-least-once delivery delivered twice. 6. **Late-arriving data** — events for yesterday arrived after the daily cutoff. 7. **Volume drop** — 90% of expected rows missing; pipeline didn't notice. Without checks, these silently corrupt downstream. Reports show "wrong" numbers; nobody knows why. ## The four levels of data quality ### Level 1 — Schema validation Does the data have the columns you expect? Right types? ```yaml # dbt test columns: - name: customer_id tests: [not_null, unique] - name: order_total tests: - not_null - dbt_utils.expression_is_true: expression: ">= 0" ``` ### Level 2 — Row-level validation Are individual rows valid? ```yaml - name: status tests: - accepted_values: values: ['pending', 'paid', 'shipped', 'cancelled'] ``` ### Level 3 — Cross-row / aggregate validation Do totals match? Is the sum across rows what you expect? ```sql -- Custom test: today's revenue is within 50% of yesterday's WITH today AS (SELECT SUM(amount) AS s FROM marts.orders WHERE date = current_date), yest AS (SELECT SUM(amount) AS s FROM marts.orders WHERE date = current_date - 1) SELECT 1 WHERE (SELECT s FROM today) (SELECT s FROM yest) * 1.5 ``` ### Level 4 — Business-rule validation Does the data conform to business invariants? ```sql -- A customer should not appear with two different account_creation_date values SELECT customer_id, COUNT(DISTINCT account_creation_date) FROM marts.customers GROUP BY customer_id HAVING COUNT(DISTINCT account_creation_date) > 1 ``` ## Two main tools ### dbt tests Built into dbt. Tests run after models. ```yaml # models/marts/dim_customer.yml version: 2 models: - name: dim_customer columns: - name: customer_id tests: - not_null - unique - name: lifetime_value tests: - not_null ``` ```bash dbt test --select dim_customer ``` Pros: free, integrated with dbt, simple. Cons: only post-transformation; not great for source data. ### Great Expectations Standalone framework. Define "expectations" about data; run them in pipelines. ```python import great_expectations as gx df = gx.read_csv("data.csv") df.expect_column_values_to_not_be_null("customer_id") df.expect_column_values_to_be_unique("order_id") df.expect_column_value_lengths_to_be_between("phone", min=10, max=15) validation_result = df.validate() if not validation_result["success"]: raise ValueError("Data quality check failed") ``` Pros: richer expectations (statistical, custom), works on any data (not just SQL), can profile data automatically. Cons: more complex, separate framework to learn. ### When to use which - **dbt tests** for warehouse-side validation (after your dbt models). - **Great Expectations** for source-side / pre-load validation, or when dbt is not in your stack. Most production teams use dbt tests as the default and add Great Expectations for source-side checks or non-dbt pipelines. ## Where to put tests in the pipeline ``` Source → [Ingestion] → Raw → [dbt staging] → Staging → [dbt marts] → Marts → Downstream Tests: After ingestion (raw): expected schema; row count reasonable After staging: no duplicates; required fields present After marts: business invariants hold; cross-table joins clean ``` Three places to test. Three different scopes. ## What to actually test (the 80/20) For every important table, test: 1. **Primary key**: not_null + unique. 2. **Required columns**: not_null on critical fields. 3. **Allowed values**: status, type, category fields. 4. **Referential integrity**: FK columns exist in parent table. 5. **Row count**: row count > 0 and within expected range. For aggregates / facts: 6. **Sum invariants**: today's total within 50% of yesterday's. 7. **Distinct counts**: number of distinct customers matches expected. 8. **Date freshness**: latest row's date is recent (within last 24h). For raw / source data: 9. **Schema match**: columns and types match contract. 10. **No new columns**: alert if source added unexpected columns. ## Anomaly detection vs static tests Static tests check fixed conditions ("count > 0"). Anomaly detection catches deviations ("today's count is 80% lower than usual"). Tools for anomaly detection: - **Monte Carlo / Soda** — commercial observability platforms. - **OpenLineage + custom logic** — DIY. - **Great Expectations profiler** — automated ba