I built a small warehouse, fed it a batch of orders with twelve defects planted in it, and watched the load succeed.
Every row landed. Summed as loaded, the revenue came to $4,905,051.18. A clean batch from the same generator reports $395,751.28. Nothing in the pipeline objected to the difference.
This post is about why that happens, what stopped it, and three places where the check that stopped it is weaker than it looks — none of which I had noticed until a review of this post went looking. The code is public and runs on a laptop: github.com/jigonyoo/warehouse-quality-gate.
The setup
Two tables, customers and orders, loaded from CSV into DuckDB through dbt. A
staging layer types and trims the raw columns; one mart, fct_revenue_daily, sums
order amounts by day. That mart is the number a stakeholder reads.
A script generates two batches with a fixed seed: a clean one with 900 orders, then a sabotaged one with 901 orders and twelve planted defects. They are two separate draws from the same generator, not the same rows with edits applied, so they differ in ordinary ways too. That matters for how the numbers below should be read.
What a plain load reports
| rows loaded | revenue reported | |
|---|---|---|
| clean batch | 900 | $395,751.28 |
| sabotaged batch | 901 | $4,905,051.18 |
The plain path is just "load the CSV, sum the column". Neither load fails.
Almost all of the gap is one row. Order 401 in the sabotaged batch has an amount of
4500000.0, in a batch where no other order is above $900. Take that
row out and the sabotaged batch sums to $405,051.18 — about 2% above the clean one,
which is roughly what two draws of the same shape (plus the other, smaller defects)
should look like. One value, thousands of
times larger than its neighbours, carries $4.5 million.
It is worth being clear about why nothing complained. 4500000.0 is a valid number.
The column is numeric. The row has every field it needs. Moving rows is the load's
whole job, and it did that job.
The twelve defects
| # | Table | What arrived | Caught by |
|---|---|---|---|
| D1 | customers | the same customer_id twice — a re-sent file |
unique |
| D2 | customers | a blank email | not_null |
| D3 | customers | country USA instead of ISO-2 US |
accepted_values |
| D4 | customers | a signup date about eight months in the future | a custom "not in future" test (it compares with today's date, so from 2027-06-01 this one stops failing) |
| D5 | orders | an order for customer 99999, who does not exist | relationships |
| D6 | orders | amount -450.0 on an order with status shipped |
a custom non_negative test |
| D7 | orders | two orders in EUR in a mart that sums everything as USD | accepted_values on currency |
| D8 | orders | the 4,500,000 above |
a custom within_magnitude test |
| D9 | orders | status Delivered with a capital D |
accepted_values on the raw value |
| D10 | orders | the same order twice — double revenue recognition | unique |
| D11 | orders | an order dated before the reporting window opens | a custom within_reporting_window test |
| D12 | orders | an amount that arrived empty | not_null |
The contract is ordinary dbt: generic tests in schema.yml plus four custom tests of
five lines each. Fifteen tests in total.
What the contract reports
| tests failed | mart | |
|---|---|---|
| clean batch | 0 of 15 | built |
| sabotaged batch | 12 of 15 | skipped |
Twelve planted defects, twelve failing tests, no failures on the clean batch. I re-ran
both from a fresh clone for this post: the clean run ends PASS=22 … ERROR=0 and the
sabotaged one PASS=9 … ERROR=12 SKIP=1. The skip is the mart — dbt build does not
build fct_revenue_daily on top of staging that failed its tests.
Where the contract is weaker than it looks
A reviewer who had not written any of this — a separate AI agent, given the repo and the commands but not my conclusions — re-ran every number above and then went looking for the soft spots. Three of them are worth more than the headline.
1. The magnitude test only catches the absurd. within_magnitude fails when an
amount is above 100,000. That catches 4,500,000. It does not catch the error a
magnitude check is usually for. A real cents-for-dollars slip multiplies an amount by 100, and in this
data the largest order is under $900, so the worst such slip lands under 90,000 — and
passes. An order of $89,999 goes straight through this test. A fixed ceiling is a
check for impossible values, not for wrong ones; a unit error needs something relative,
like the value against its own history.
2. "The mart is not built" means the mart is stale. Skipping the build stops the
wrong number from being written. It does not remove the old number. After the
sabotaged run, fct_revenue_daily still holds the results of the last good run, and
nothing tells a reader that. The staging view underneath shows the bad batch in full.
Stopping the build is necessary, not sufficient: it has to be paired with a freshness
check, or the failure turns into a quiet one.
3. I broke my own rule in the same repo. D9 is there to make a point: if you
normalise a value before testing it, the test cannot see the defect. lower('Delivered')
is 'delivered', so a test on the normalised status passes, while a downstream filter
on the raw value still silently drops the row. So the orders contract tests
status_raw. Except status_raw is trim(status), not what arrived — so a status of
shipped with a trailing space passes the test, and any query that reads the source
table with an exact match still drops that row. And the customers contract
tests country after upper(trim(...)), so a lower-case us passes too — and the
orders contract does the same to currency. The same mistake three times in one small repo, one of them inside the fix for it. The rule I keep having to relearn: test what arrived, not what you made of it.
What this does not do
A contract like this stops the defects someone thought to write down. Twelve planted defects and twelve catches is a statement about these twelve.
It also says nothing about volume or freshness. An empty batch, with its column types declared so it can load at all, passes all fifteen tests and builds an empty mart. A job that never ran is not a test failure at all.
Where this went next
The contract answers "can a pipeline stop a bad batch?" The question I got more interested in is whether an agent running the load would. So I built an evaluation environment around this situation: a loader that never reports an error, batches that are wrong in several of these ways, and a score computed from what reached the warehouse rather than from what the agent says it did. It is public, with the same honesty about its limits: github.com/jigonyoo/bad-batch-gate.
Reproduce it
git clone https://github.com/jigonyoo/warehouse-quality-gate
cd warehouse-quality-gate
python3 -m venv .venv && . .venv/bin/activate
pip install dbt-core dbt-duckdb # last checked with dbt-core 1.12.5, dbt-duckdb 1.11.0
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 # what a plain load reports instead
I use AI tools while building and writing, and I check every number against a run before it goes in. The numbers here come from a fresh clone on 2026-09-30.