The Flatline Weekend

Friday 28 Aug, 19:40 CEST. The hourly loader for fct_order_item picked up every order placed before 19:00 — 181 rows for the day — and finished green. Then it stopped. An upstream credential rotated at 19:52; the next attempt exited before it logged anything the alerting rule recognised. No red DAG, no page.

Between Friday evening and Sunday midnight the marketplace sold 682 items worth €29,236. The warehouse learned about none of them.

Loud failure: the job runs and throws — red DAG, stack trace, Slack message. Silent failure: the job doesn’t run, or runs and writes wrong data — a dashboard that renders beautifully.

The trend chart didn’t draw a gap. A gap needs rows missing from a range the chart knows about. With no rows after Friday 18:59 the date axis simply ended early, and the final bar was short — which is exactly what the newest bar always looks like when today isn’t over.

The KPI card was wrong in a plausible direction.

The uncomfortable part: −10.9% was outside all 147 comparable historical windows (observed range −5.2% to +6.2%). The anomaly was not subtle and took ~8 lines of SQL to express. It went unnoticed because nobody and nothing was computing the comparison. A human reading “−10.9%” cannot tell a soft month from a broken pipe; a machine with 147 prior windows can.

Detected Monday 11:05 by Finance, reconciling payouts against the payment provider. Time to detection: 63h 25m. With an hourly freshness check at a 4.5 h threshold it would have fired Sat 01:00 — 5h 20m. The backfill itself took 11 minutes (the loader was idempotent — Lesson 04). The repair was never the expensive part; the three days of not knowing were.

-- 1. Freshness — the only test that fires when NOTHING happens
SELECT max(loaded_at) AS last_load,
       date_diff('minute', max(loaded_at), now()) / 60.0 AS age_hours,
       CASE WHEN date_diff('minute', max(loaded_at), now()) / 60.0 > 4.5
            THEN 'FAIL' ELSE 'PASS' END AS status
FROM fct_order_item;
-- 2026-08-28 19:40:00 | 61.33 | FAIL

-- 2. Volume — note the coalesce. Written as a plain WHERE day = '2026-08-29'
--    this returns zero rows on the day it matters, and a test returning no rows PASSES.
WITH d AS (SELECT ordered_at::DATE AS day, count(*) AS rows_loaded
           FROM fct_order_item GROUP BY 1),
     b AS (SELECT median(rows_loaded) AS med, min(rows_loaded) AS lo FROM d
           WHERE day BETWEEN DATE '2026-07-31' AND DATE '2026-08-27')
SELECT coalesce((SELECT rows_loaded FROM d WHERE day = DATE '2026-08-29'), 0) AS rows_loaded,
       b.med, b.lo,
       CASE WHEN coalesce((SELECT rows_loaded FROM d WHERE day = DATE '2026-08-29'), 0)
                 < 0.6 * b.med THEN 'FAIL' ELSE 'PASS' END AS status
FROM b;
-- 0 | 242 | 197 | FAIL

-- 3. Uniqueness            -- 5. Referential integrity
SELECT count(*) AS duplicate_keys, sum(n) - count(*) AS extra_rows
FROM (SELECT order_item_id, count(*) n FROM fct_order_item GROUP BY 1 HAVING count(*) > 1);

SELECT count(*) AS orphan_rows, count(DISTINCT f.brand_id) AS missing_brands,
       round(sum(f.price_cents)/100.0, 2) AS gmv_at_risk
FROM fct_order_item f LEFT JOIN dim_brand b USING (brand_id) WHERE b.brand_id IS NULL;

-- 6. Accepted values
SELECT count(*) FILTER (WHERE price_cents <= 0)                        AS non_positive_price,
       count(*) FILTER (WHERE price_cents > 500000)                    AS absurd_price,
       count(*) FILTER (WHERE condition_grade NOT IN ('A','B','C','D')) AS bad_grade
FROM fct_order_item;

Test 4 is the subtle one: a column that is 0.0% null for months and then 4.3% null forever after is not a data problem, it is a product release nobody told you about (a new guest checkout stopped sending buyer_country on 20 Aug). The total row count never moved, so no volume test could see it — only a rate could.

Load into a staging table nobody can query → run the tests there → only swap a passing batch into the table dashboards read. A failing batch is quarantined and yesterday’s good data stays published.

• Blocking, at the gate — tests 3, 5, 6. Cheap, unambiguous; a failure means this batch is wrong. Stale-but-correct beats fresh-and-wrong for almost every business question.
• Non-blocking, after publish — test 4. Describes reality rather than corruption. Publish, then tell someone it changed.
• Independent, on a clock — tests 1, 2. These must run whether or not a pipeline ran, so they cannot live inside the pipeline. This is the distinction that cost 63 hours.

Loader runs hourly at :40, with known quiet windows (nightly backup skips 02:40; longer Sunday vacuum; one monthly maintenance slot). Longest legitimate gap: 4h 20m.

threshold = expected interval + longest legitimate gap + one interval of grace

The proportions are the lesson. Going from no check to a crude one recovers 58 hours. Tuning that check from 4.5 h to 2.5 h recovers 2 more. Teams routinely spend a week arguing the second number and never ship the first.

Severity

• Page — money is wrong now (duplicate keys in a payout table; freshness breach on a pricing feed). Bar: someone acts on this data before the next working hour.
• Ticket — something drifted (null rate stepped, two orphan brands). Nothing improves by fixing it at 03:00.
• Dashboard badge — a freshness stamp on the report itself: “data through Fri 28 Aug, 19:00”. Cheapest fix in the lesson; on its own it ends this incident at 09:00 Monday.

An alert nobody can act on is not a safety measure, it is a tax.

1. What is the oldest our most-used table may get before someone is told — and who is told?
2. If a scheduled load never starts, what fires? (“The DAG would go red” is wrong — a job that doesn’t start has no colour.)
3. Do our tests block the publish, or describe it afterwards?
4. How many alerts did this pipeline send last month, and how many led to an action?
5. Does the dashboard say when its data is from?

The dataset is fully deterministic — no random seed — so your figures match to the cent. Generator is in the artifact (seed.py); it writes fct_order_item.csv (46,659 rows, weekend already missing) and dim_brand.csv.

1. Load both into DuckDB (pip install duckdb, read_csv_auto). No server, no Docker.
2. Write the six tests. Wrap them in a function taking (name, SQL, pass condition) that prints PASS/FAIL. That function is the entire concept of a testing framework.
3. Check against: 61.33 h stale · 0 rows on 29 Aug vs median 242 · 3 duplicate keys · 91 null countries · 14 orphan rows worth €755.84 · 3 items at €0.00.
4. Make it a gate: load to stg_order_item, run the blocking tests, and only then CREATE OR REPLACE TABLE fct_order_item AS SELECT * FROM stg_order_item. Break something deliberately and watch the publish refuse.
5. Plot your own alert curve: list every expected load time in a week including maintenance, find the longest legitimate gap, set the threshold just above it. That number is an SLA.

Push as de-practice/06-data-quality with a README stating your freshness threshold and why.

A rendering pipeline and a data pipeline fail independently. The chart drew perfectly for three days on top of a table that had stopped moving, because drawing is not knowing. Make the data assert something about itself, on a clock, whether or not anyone is looking.

Smallest useful action: put max(loaded_at) on the face of your three most-used dashboards.

• Freshness — age of the newest row. The only quality signal that fires when nothing happens.
• Silent failure — a pipeline that succeeds at producing wrong, missing or stale data.
• Write–Audit–Publish (WAP) — stage, test, then swap into the table readers query.
• Blocking vs non-blocking test — corruption blocks the publish; drift annotates it.
• Volume anomaly test — row count vs the recent distribution, not a hard-coded number.
• Referential integrity — every FK in a fact exists in its dimension.
• Alert fatigue — the state where a monitor’s output is reliably ignored.
• Time to detection — outage start → a human knows. Here: 63h25m → 5h20m.
• Data SLA — a written freshness/completeness promise for a table, with a named owner.