A bad batch loaded with zero errors. One row added $4.5 million.

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.