S053 · Data Quality — Freshness, Volume, Schema, Distribution

Module M05: Data Engineering · Session 53 of 130 · Track: DE 🎚️

What you'll be able to do after this session

• Explain data quality to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S051 — dbt — Models, Tests, Docs, Warehouse-Native ELT

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · The 5 Pillars of Data Observability — Monte Carlo — the industry-standard framing (freshness, volume, schema, distribution, lineage) in 10 minutes.
• 🎥 Deep dive (30–60 min) · Great Expectations — full walkthrough — end-to-end tour of the most popular open-source DQ framework.
• 🎥 Hands-on demo (10–20 min) · Data quality tests with dbt in 15 minutes — the fastest path to your first passing/failing test.
• 📖 Canonical article · Google — Data Validation for Machine Learning (paper) — the TFX/TFDV paper; the definitions of "data anomaly" still shape the field.
• 📖 Book chapter / tutorial · Great Expectations docs — Core concepts — 20-minute skim; you'll see the "Expectation Suite" pattern used everywhere.
• 📖 Engineering blog · Airbnb — Data Quality at Airbnb (Midas / Wall) — how a real org went from spreadsheets to a certified-dataset program.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: How to know your data is trustworthy.

Imagine your car's dashboard. You don't inspect the engine every morning — you glance at four gauges (fuel, temperature, oil, speed). If any of them is red, you pull over. Data quality is the same idea for pipelines. You can't manually inspect a billion rows, so you watch four vital signs on every table:

1. Freshness — did new data arrive on time?
2. Volume — is the row count in the usual range?
3. Schema — did any column type/name change unexpectedly?
4. Distribution — do the values look like they normally do (mean, null rate, uniques)?

These four (plus lineage — knowing what depends on what) are called the "pillars of data observability." Every modern DQ tool — Monte Carlo, Elementary, Great Expectations, dbt tests, Soda, Databand — is a different UI on top of these same four questions.

The reason this exists is brutal: bad data is worse than no data. A missing dashboard makes execs email you; a wrong dashboard makes them make wrong decisions and blame you six months later. In ML, a subtle distribution shift (nulls in a feature that used to be dense) silently degrades model accuracy and no alert fires — until revenue drops.

Forever gotcha: DQ tests catch what you thought to check. The scariest bugs are the ones nobody wrote a test for. Layered defence — schema tests + volume monitors + distribution anomaly detection — is the only way to catch unknown-unknowns.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

Worked example — a payments table:

How the alert flow actually plays out (real timeline of an incident):

1. 02:15 — upstream Postgres schema migration renames country_code → card_country.
2. 02:30 — Fivetran connector auto-adds the new column, leaves the old one NULL.
3. 02:31 — DQ tool records null_rate(country_code) = 100%; anomaly detector fires (was 0.2%).
4. 02:32 — Slack alert lands in #data-oncall with lineage: "affects mart_revenue_by_country, ml_fraud_features, exec_dashboard".
5. 02:35 — on-call flips the model to use card_country, backfills last 3h from the old column, closes the alert.

Without DQ tooling, the same incident would surface as "hey, our fraud model started blocking every payment" 3 days later. The 3-day gap is the ROI.

Freshness vs SLA: you set an SLO like "table X is fresh within 2h, 99% of the time." Data-contract SaaS tools now let producer teams commit to these SLOs the same way backend teams commit to API SLOs.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Zero-dependency DQ script on a DuckDB table — the same checks a real tool runs.

python -m venv .venv && source .venv/bin/activate
pip install duckdb pandas

# dq_check.py
import duckdb, datetime as dt, statistics as st, json, sys
 
con = duckdb.connect("/tmp/shop.duckdb")
 
# 1. Seed a payments table (in real life, this is your warehouse)
con.execute("CREATE OR REPLACE TABLE payments AS SELECT * FROM (VALUES "
    "(1, 'US', 25.0, TIMESTAMP '2025-01-10 10:00:00'),"
    "(2, 'IN', 42.5, TIMESTAMP '2025-01-10 10:05:00'),"
    "(3, 'US', 99.9, TIMESTAMP '2025-01-10 10:10:00'),"
    "(4, NULL, 12.0, TIMESTAMP '2025-01-10 10:15:00'),"
    "(5, 'GB', 31.2, TIMESTAMP '2025-01-10 10:20:00')"
    ") t(id, country, amount_usd, created_at)")
 
def check(name, sql, predicate, expected):
    val = con.execute(sql).fetchone()[0]
    ok = predicate(val)
    print(f"[{'PASS' if ok else 'FAIL'}] {name}: got {val!r}, expected {expected}")
    return ok
 
results = []
 
# 2. Freshness — max(created_at) within last 24h of "now" (fake now)
now = dt.datetime(2025, 1, 10, 11, 0, 0)
freshness_sql = "SELECT DATEDIFF('minute', MAX(created_at), TIMESTAMP '2025-01-10 11:00:00') FROM payments"
results.append(check("freshness ≤ 60 min", freshness_sql,
                     lambda v: v <= 60, "≤ 60"))
 
# 3. Volume — row count in [3, 1000]
results.append(check("volume in [3, 1000]", "SELECT COUNT(*) FROM payments",
                     lambda v: 3 <= v <= 1000, "3–1000"))
 
# 4. Schema — expected columns
schema = con.execute("DESCRIBE payments").fetchall()
cols = {r[0]: r[1] for r in schema}
expected_cols = {"id": "INTEGER", "country": "VARCHAR",
                 "amount_usd": "DOUBLE", "created_at": "TIMESTAMP"}
schema_ok = cols == expected_cols
print(f"[{'PASS' if schema_ok else 'FAIL'}] schema matches contract: {cols}")
results.append(schema_ok)
 
# 5. Distribution — null rate on country ≤ 20%
results.append(check("null_rate(country) ≤ 0.20",
    "SELECT AVG(CASE WHEN country IS NULL THEN 1.0 ELSE 0 END) FROM payments",
    lambda v: v <= 0.20, "≤ 0.20"))
 
# 6. Distribution — mean amount between $10 and $200
results.append(check("mean(amount_usd) in [10, 200]",
    "SELECT AVG(amount_usd) FROM payments",
    lambda v: 10 <= v <= 200, "10–200"))
 
# 7. Exit code — CI-friendly
sys.exit(0 if all(results) else 1)

Run: python dq_check.py && echo OK || echo FAILED

What to observe:

1. Every check is a plain SQL query — no magic. That's what Great Expectations, Soda, dbt tests all do underneath.
2. The script exits non-zero on failure — plug into GitHub Actions or Airflow to fail the DAG.
3. Change the null-rate threshold from 0.20 to 0.10 — watch the check flip to FAIL.
4. The "schema contract" is a Python dict; in production, you'd store this in git as YAML and diff against the live table.
5. Notice we mocked "now" — real freshness tests need a stable clock (usually the orchestrator's).

Try this modification: add a distribution anomaly check that compares today's avg(amount_usd) to a rolling 7-day average stored in a dq_history table, alerting if it changes by more than 3 standard deviations. That's how Monte Carlo / Elementary auto-detect anomalies without you writing rules.

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

Every mature data org lands on some flavour of these four layers:

1. In-pipeline assertions (dbt tests, Great Expectations) — cheap, fail-fast, block bad data from propagating. Run inside your DAG.
2. Post-load observability (Monte Carlo, Elementary, Soda, Databand) — anomaly-detection on freshness/volume/distribution across every table. Catches the unknown-unknowns.
3. Contracts (data contracts, Schema Registry for Kafka, Avro/Protobuf) — producer commits to a schema; breaking changes require review. Prevents 60% of incidents that ever happen.
4. SLOs & lineage (OpenLineage, DataHub, Amundsen) — every critical table has an owner, an SLO, and a lineage graph so incidents get routed to the right team.

War stories:

• The silent-corruption drip. A currency-conversion service started returning INR values as USD due to a config flip. Row counts, freshness, schema — all normal. Only the mean(amount_usd) distribution check would have caught it. It took 11 days and a finance reconciliation to notice. Cost: ~$400k in wrong incentive payouts. Lesson: value-level distribution checks matter as much as row-level ones.
• The alert fatigue spiral. Team turned on every default check → 400 daily alerts → nobody looks → real incident missed. Fix: tier alerts (P0/P1/P2), route by owner, auto-close self-healing ones, review monthly.
• The upstream rename. Product team renamed user_id → account_id in a Postgres source. Fivetran happily replicated the schema change. Every downstream model returned NULL joins. Two dashboards went blank; one didn't (because it silently ignored NULL joins). Fix: schema-contract enforcement + freshness alerts on downstream models.
• The "we tested it locally" incident. Dev wrote a test that passed on 1000 rows sample, deployed, blew up at 10 billion. Some tests (uniqueness, cross-table referential integrity) don't scale linearly. Fix: run production tests on a staged copy first, or use sampling with a documented confidence interval.

How top teams handle it: Airbnb's "Data Certification" program marks tables Bronze / Silver / Gold based on ownership + tests + SLOs coverage. Uber and Netflix run internal DQ platforms that auto-instrument every table. LinkedIn open-sourced DataHub for lineage + governance. The shift is from "engineers manually write tests" to "platform auto-generates baseline tests, engineers add high-value ones."

Gotcha to memorise: if a DQ test never fails, it's either brilliantly designed or completely broken — usually the latter. Deliberately break your pipeline in a staging environment to verify your alerts actually fire. Untested alerting is the same as no alerting.

(e) Quiz + exercise · 10 min

1. Name the four pillars of data observability.
2. What kind of bug does a distribution check catch that a schema check cannot?
3. Why is a lineage graph a prerequisite for good on-call routing?
4. What's the difference between an in-pipeline assertion (like a dbt test) and an observability tool like Monte Carlo?
5. Why should you deliberately break your pipeline in staging?

Stretch (connects to S051 — dbt): Take the mart_revenue model from S051 and design a full DQ layer for it: (a) 2 in-model dbt tests, (b) 1 freshness SLO, (c) 1 distribution alert. Write out the YAML / SQL for each.

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is data quality? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S054): Governance & Cost — Lineage, PII, Attribution
• Previous (S052): Lakehouse — Delta / Iceberg / Hudi, ACID on Files
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.