Day 13. Quality gates, quarantine and the DQ report

⏱ 4h · learn 60m · lab 140m · ship 25m · review 15m🏷 allBefore: Day 12

By the end of today you can

Build a quarantine table with reasons instead of dropping rows
Implement freshness, volume, schema, validity and referential checks
Detect volume anomalies with a robust statistic
Produce a daily DQ report people actually read

1. Why this matters

When a number is wrong, the first question is "did the data quality checks catch it?". You want to answer "yes, here's the alert from 06:12, and the affected rows are in quarantine with a reason". That sentence is what makes a BI team trusted.

2. The five families of checks

Family	Question	Example
Freshness	Did today's data arrive on time?	Latest hotel file `received_at` < 06:00
Volume	Is the amount normal?	QSR lines per restaurant within the usual range
Schema	Same columns and types as the contract?	pandera strict mode
Validity	Values inside rules?	`qty ≥ 1`, dates parseable, channel in the list
Referential	Do keys exist in reference data?	every `menu_item_id` in `menu`

Plus reconciliation checks (Week 6): totals match the source's control total.

3. Quarantine, don't drop

def split_valid(df: pl.DataFrame) -> tuple[pl.DataFrame, pl.DataFrame]:
    reasons = pl.concat_str([
        pl.when(pl.col("order_ts").is_null()).then(pl.lit("bad_timestamp;")),
        pl.when(pl.col("qty") < 1).then(pl.lit("qty_lt_1;")),
        pl.when(~pl.col("menu_item_id").is_in(menu_ids)).then(pl.lit("unknown_item;")),
    ], ignore_nulls=True)
    df = df.with_columns(reject_reason=reasons)
    good = df.filter(pl.col("reject_reason") == "").drop("reject_reason")
    bad = df.filter(pl.col("reject_reason") != "").with_columns(quarantined_at=pl.lit(datetime.utcnow()))
    return good, bad

The quarantine table keeps all original columns plus reject_reason, src_file, quarantined_at. When the source fixes a row, you can replay it.

4. Volume anomalies, robustly

The mean and standard deviation are dragged around by the very outliers you're looking for. Use the median and MAD (median absolute deviation) over the same weekday in recent weeks.

Maths and principle

Principle: a robust z-score measures distance from typical in units of typical spread, and outliers barely move it.

Equation: \(\tilde z = \dfrac{x - \text{median}(X)}{1.4826 \cdot \text{MAD}(X)}\), with \(\text{MAD} = \text{median}(|X_i - \text{median}(X)|)\). Flag \(|\tilde z| > 3.5\). The 1.4826 makes MAD comparable to \(\sigma\) for normal data.

Analogy: in astronomy you subtract the sky background with a median, because one bright star would ruin a mean. Where it breaks: seasonality and holidays. Compare like with like (same weekday, holiday calendar), or you'll flag every long weekend.

Try it: last 6 Saturdays: 510, 498, 530, 505, 515, 2,400 (one double-load). Median? (512.5.) Would a mean-based check flag next Saturday's 1,020? (Mean ≈ 826, sd ≈ 772, z ≈ 0.25: no. Robust: MAD = 11, z̃ ≈ 31: yes.)

5. Tooling choices

pandera: contracts in Python next to the code (Day 8).
Great Expectations (GX): large library of expectations, data docs; heavier setup.
Soda Core: checks in YAML (row_count > 0, missing_count(order_id) = 0), runs against warehouses.
dbt tests (Week 5) and Dagster asset checks (Week 3): checks where the pipeline runs. Most teams end up with these two plus a few pandera contracts at the edge.

Alert fatigue is a design problem

If a check fires every day, people stop reading all of them. Rules: every alert has an owner and a runbook line ("if this fires: check the SFTP folder for .partial files; if present wait 30 min"); warnings go to a daily digest, only blockers page someone; and you review noisy checks monthly and tune or delete them. Report "unavailable" separately from "zero": a check that couldn't run is not a pass.

Lab

Implement split_valid for QSR POS and retail sales. Write quarantine to data/quarantine/<source>/business_date=…/.
Implement checks as small functions returning {check, source, business_date, status: pass|warn|fail|unavailable, observed, expected, detail}.
Freshness from the manifest, volume with robust z per restaurant per weekday, schema from the contract, validity and referential from quarantine counts.
Write all results to a dq_results table (DuckDB), one row per check per day.
Render reports/dq_<date>.html: a summary line ("2 fails, 5 warns, 1 unavailable"), then failures first, each with its runbook line. Make sure R107's silent days and the late hotel file show up correctly.

Ship

feat(dq): quarantine, five check families, robust volume anomalies, daily DQ report.