A unique + not_null suite stopped 3 of 17 bad batches. Here is what got through.

Many dbt projects start with the same two tests on every key: unique and not_null. They are cheap, they rarely fail, and they feel like coverage. I wanted a number for how much they actually stop.

So I wrote a small tool that plants one realistic fault at a time into a copy of a dbt project’s seed data, runs dbt build, and records whether anything stopped it. It is public and runs on a laptop with DuckDB: github.com/jigonyoo/dbt-fault-drill.

The setup

The example project is deliberately ordinary: 240 orders and 60 customers loaded from CSV, two staging models, and one mart, fct_daily_revenue, that sums the amounts of orders that were not cancelled or returned, by day and currency. That sum is the number someone reads.

The same schema.yml holds two test suites. The basic suite is the starter set: unique and not_null on the keys. The contract suite adds not_null on the other order columns, a relationship from orders to customers, accepted values for status and currency, a range on amounts, a “not in the future” check on dates, and a minimum row count.

The drill plants 17 faults, each into its own throwaway copy: a duplicated row, a blank key, an amount sent in cents, a 45,000,000 amount, a status in the wrong case, last year’s file loaded again, and eleven more. One bad row in an otherwise clean batch is the case that loads without a single error, so most faults change exactly one row.

The result

Of the basic suite’s three, only two are its tests: unique caught the duplicated row and not_null caught the blank key. The third, a date written as 18/09/2026, failed because the seed’s columns are typed and DuckDB refused to load it. That is a real stop, but it came from the column type, not from a test.

What got through, and how far it moved revenue

For every fault that reached the mart, the drill also reports how far it moved SUM(revenue) against the clean run (20,174.75, summed across currencies as a raw check). These went through the basic suite:

Five more went through with a change of exactly 0.00: a lowercase currency code, a status in the wrong case, a date in 2099, an order pointing at a customer who does not exist, and last year’s file. The remaining four (a blank amount, a negative amount, the replayed order and the 10x amount below) moved it by 100 to 300 each. A total that does not move is not the same as data that is right. The rows are still there, in the wrong currency group, the wrong status, the wrong year.

The three the contract missed

These are the interesting ones, because no range or uniqueness test on a single column can see them:

• The same order ingested twice under a new key (+218.59). The order ids differ, so unique passes. What would stop it: uniqueness on a natural key or on the upstream event id, if your source carries one; this example does not, which is the point.
• One amount off by a factor of ten (30.95 became 309.50, +278.55). Still well inside any sensible range. What would stop it: comparing a row with that customer’s history, or a batch total with recent batches.
• Last year’s file loaded again (every date shifted back 365 days, total unchanged). Nothing checks freshness. What would stop it: the newest date must be within a few days of the load.

Read these numbers carefully

I wrote both the contract and the fault catalog, so 14 of 17 is not an independent benchmark; a contract written by someone who knows the faults will look good against them. The useful number is the one the drill reports on your own project. The tool also only plants faults into seed CSVs on DuckDB for now, and the revenue column is one sum: it shows faults that move a total, not rows that moved between groups.

The tool itself went through two separate AI review passes before I published it. The first review found that it read a failing on-run-end hook as a clean build, because it trusted the per-node results and ignored dbt’s exit code. A drill that reports a broken build as a pass is exactly the kind of quiet failure it exists to find. Every blocking finding from both reviews is fixed, with a regression test where one could be written.

Try it on your project

pip install "dbt-fault-drill[duckdb] @ git+https://github.com/jigonyoo/dbt-fault-drill"
dbt-fault-drill run --project-dir . --out drill.md

Map your columns to roles in a fault_drill.yml (key, amount, currency, date, category, foreign_key), name the seed to plant faults into, and leave out what you do not have; those faults are skipped and listed. The README has the full reproduce commands for every number above.

Written with AI assistance. Every number comes from the committed reports and example data in the repository.