A dbt contract that stops a bad batch before the mart is rebuilt from it. The sabotaged batch loads without a single error and reports revenue of $4,905,051; a clean batch from the same generator reports $395,751. Almost all of the gap is one row — order 401 at 4,500,000 — and a plain pipeline waves it straight through. The contract fails 12 tests and skips the mart build, so the mart keeps the last good run’s numbers.
Public · synthetic demoEvery one of the twelve defects in the sabotaged batch is a defect that survives a plain load. Nothing throws. A duplicated customer file, an amount thousands of times larger than any other order, a status with a capital D — the rows land, the job exits 0, and the number a stakeholder reads is wrong by an order of magnitude.
| run | rows loaded | revenue reported | errors | tests failed |
|---|---|---|---|---|
| Plain load — clean batch | 900 | $395,751.28 | 0 | — |
| Plain load — sabotaged | 901 | $4,905,051.18 | 0 | — |
| Contract — clean batch | 900 | $395,751.28 | 0 | 0 of 15 |
| Contract — sabotaged | 901 (staging) | mart not rebuilt | 12 | 12 of 15 |
D9 tests the raw value, not the normalised one. It is tempting to lower() the status in staging and test the clean column — but then the test can never fail, and the downstream filter doing an exact match on 'delivered' still silently drops the row. The model exposes both status_raw and status_normalised, and the contract tests status_raw — which is only trimmed, not untouched (see the limitations below).
D7 is a contract, not a bug. Nothing is wrong with an EUR order. What is wrong is that fct_revenue_daily sums amount without conversion, so mixing currencies makes the total meaningless. The single-currency rule is written down as a test precisely because the assumption lives in a model somewhere else.
| # | what arrived | caught by |
|---|---|---|
| D1 | customer_id 8 duplicated — a re-sent file loaded twice | unique |
| D2 | customer 16 email arrived blank (the seed loader reads it as NULL) | not_null |
| D3 | country USA instead of ISO-2 US | accepted_values |
| D4 | signup_date 2027-06-01, in the future | not_in_future (compares with today’s date) |
| D5 | order 101 references customer 99999, which does not exist | relationships |
| D6 | order 201 amount is -450.0 on an order that is not a refund | non_negative |
| D7 | orders 301–302 switched to EUR while the mart sums as USD | accepted_values |
| D8 | order 401 amount 4,500,000 — thousands of times any other order | within_magnitude |
| D9 | status Delivered with a capital D | accepted_values on raw |
| D10 | order 601 present twice — double revenue recognition | unique |
| D11 | order 701 dated before the reporting window opens | within_reporting_window |
| D12 | order 801 amount arrived empty (NULL); SUM silently skips it | not_null |
python3 -m venv .venv && . .venv/bin/activate pip install dbt-core dbt-duckdb python3 scripts/make_batches.py # regenerates both batches, seed is fixed ./scripts/run_evidence.sh # runs the contract over each, writes evidence/ python3 scripts/naive_vs_gate.py # shows what a plain load reports instead
The warehouse here is DuckDB so the whole thing runs on a laptop. The staging models use DuckDB SQL (try_cast, cast(… as double)); moving to another warehouse means porting those, not only changing profiles.yml. Last checked with dbt-core 1.12.5 and dbt-duckdb 1.11.0.
· Synthetic batches (900 rows, fixed seed) — a demonstrator of the method, not a benchmark. · Contracts stop bad data. They do not tell you a job never ran, or that a scraper returned an empty page — those are pipeline-heartbeat and scraper-canary. · The twelve defects are the ones that survive a plain load. Defects that crash the load are already visible and are deliberately out of scope. · The magnitude test is a fixed ceiling (100,000). A real ×100 unit slip on orders under $900 lands under 90,000 and passes; that needs a check relative to the column’s history. · When the contract fails, the mart is not rebuilt — it keeps the last good run’s numbers, and nothing tells the reader. Pair it with a freshness check. ·status_rawistrim(status)andcountryis tested afterupper(trim(…)), soshippedanduspass. Test the value as it arrived. · An empty batch (with its column types declared) passes all fifteen tests. Volume is not checked.