Your dbt tests prove the data arrived. They don't prove the maths is right.

I changed one word in a dbt model. Here is what happened.

customers table:   100 rows  ->  62 rows
sum(clv):          1672.00   ->  1672.00
dbt build:         PASS=28 WARN=0 ERROR=0

Thirty-eight percent of the customers vanished. Every one of the project's 20 data tests passed. And because the customers that disappeared had never ordered anything, the revenue total came out byte-identical, so even a total revenue reconciliation would have waved it through.

The word I changed was left, in left join, to inner.

Everything below is reproducible. I ran it today against dbt-labs/jaffle_shop_duckdb at commit 36bde6cb on dbt-core 1.12.0 with dbt-duckdb 1.10.1, and every number is copied from the terminal rather than from memory.

First, in fairness

I picked jaffle_shop because it is the project every dbt developer has cloned, not because it is bad. It is a deliberately minimal teaching repo, and it is well tested for what it is: 20 data tests, all green, covering every primary key, the one foreign key, and both status enums. If you are looking for a post that dunks on dbt Labs, this is not it.

The point I am making is about a property of data tests in general. It holds in your repo too, and your repo probably has more at stake than a fake shop that sells jaffles.

What 20 passing tests actually cover

Here is every test in the project, straight from the manifest:

  customers      not_null         customer_id
  customers      unique           customer_id
  orders         accepted_values  status
  orders         not_null         amount
  orders         not_null         bank_transfer_amount
  orders         not_null         coupon_amount
  orders         not_null         credit_card_amount
  orders         not_null         customer_id
  orders         not_null         gift_card_amount
  orders         not_null         order_id
  orders         relationships    customer_id
  orders         unique           order_id
  stg_customers  not_null         customer_id
  stg_customers  unique           customer_id
  stg_orders     accepted_values  status
  stg_orders     not_null         order_id
  stg_orders     unique           order_id
  stg_payments   accepted_values  payment_method
  stg_payments   not_null         payment_id
  stg_payments   unique           payment_id

Twenty tests reads like decent coverage. Now line them up against the model that matters. customers has seven columns:

customer_id, first_name, last_name, first_order,
most_recent_order, number_of_orders, customer_lifetime_value

It has two tests. Both are on customer_id.

The other five columns have nothing. Four of them are computed: first_order is a min, most_recent_order is a max, number_of_orders is a count, and customer_lifetime_value is a sum over a join across three tables. Those four columns are the entire reason the model exists, and not one of them has an assertion attached to it.

orders looks better at a glance: nine columns, ten tests. But six of those ten are not_null, and five of the six are on money columns. not_null on a sum tells you a number showed up. It says nothing whatsoever about whether it is the right number. orders also creates an arithmetic identity by construction, since amount is the total of the four payment-method columns beside it, and no test in the project asserts that identity.

That is the shape of nearly every dbt project I have looked at, and there is a completely rational reason for it. Data tests are cheap to write when the rule is generic. "This is unique", "this is not null", "this is one of five values": you can attach those to a column in four lines of YAML without thinking hard. But there is no generic rule for "this sum is correct." The only way to assert that is to know what the answer should be, which means you need input data whose answer you already know.

So keys and enums get tested, and the maths does not.

The experiment

customers.sql ends with two left joins:

    from customers

    left join customer_orders
        on customers.customer_id = customer_orders.customer_id

    left join customer_payments
        on  customers.customer_id = customer_payments.customer_id

The left join on customer_payments is load-bearing. Jaffle shop has 100 customers and only 62 of them have ever ordered. That left join is what keeps the other 38 in the table, with nulls in their order columns.

Change it to inner join. This is the single most ordinary edit in analytics engineering. People do it while tidying up, or because an inner join is faster, or because a linter suggested it, or because an AI assistant rewrote the CTE and quietly picked a different join type.

Baseline, before the change:

customers rows:            100
customers with no orders:   38
orders rows:                99
sum(customer_lifetime_value): 1672.00

After the change:

customers rows:             62      (38 customers gone)
sum(customer_lifetime_value): 1672.00   (identical)

$ dbt build
Done. PASS=28 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=28

All 20 data tests pass. They pass because every one of them is still true. customer_id is still unique across the 62 rows that remain. It is still not null. Every orders.customer_id still resolves to a customer, because the customers who got dropped are exactly the ones with no orders. Nothing about the data is malformed. There is simply 38% less of it than there should be.

And the money total does not move, because customers who never ordered contributed zero to it. So the reconciliation check that a careful team adds on top of their dbt tests, comparing the revenue total against the source, also passes.

Downstream, your active-customer count is correct and your total customer count is wrong. Every ratio with customers in the denominator is now inflated by 61%. Conversion rate, activation rate, churn rate, revenue per customer: all of them move, all of them look plausible, and nothing anywhere goes red.

The nuance that makes this worse, not better

There is a version of this where dbt does catch it. If even one customer in the warehouse had placed an order but had no payment recorded against it, the inner join would have dropped that customer while their order survived, and the relationships test on orders.customer_id would have failed immediately.

I checked. In jaffle_shop, 62 customers have orders and all 62 have at least one payment, so that never happens and the test stays green.

Read that again, because it is the actual lesson. Whether your data tests catch this bug depends on an accident of the rows that happen to be in your warehouse today. Get a customer with an unpaid order next Tuesday and the test starts failing, on a change that shipped three weeks ago. Data tests are sampling the world. They are not checking your logic.

A unit test does not have that problem, because you choose the rows.

So why does nobody write unit tests?

dbt shipped unit tests in 1.8, and they are exactly the right tool here. You supply fixed inputs, you state the expected output, and dbt runs your SQL against them at compile time. No warehouse data involved. The assertion is about the logic.

Two years on, adoption is thin. I ran a coverage check on jaffle_shop:

$ misata dbt-unit-test --coverage

 model          unit test  inputs  columns documented
 customers         no           3         yes
 orders            no           2         yes
 stg_customers     no           1         yes
 stg_orders        no           1         yes
 stg_payments      no           1         yes

0/5 models have a unit test. 5 are ready to generate one.

The reason is not that people disagree with unit testing. It is the fixtures.

To unit test customers you need three input fixtures, for stg_customers, stg_orders and stg_payments. They cannot be arbitrary rows. Every customer_id in the orders fixture has to exist in the customers fixture, and every order_id in the payments fixture has to exist in the orders fixture. If those keys do not line up, the joins in your model return nothing, your test passes against an empty result, and you have written a test that proves nothing while looking green forever. That failure mode is worse than having no test at all, and hand-writing referentially consistent CSV across three files is exactly the kind of tedious work that people start and abandon.

Then the schema changes and all of it rots.

Generating the fixtures

This is a solvable problem, because the manifest already describes the model's inputs, and the catalog already describes the real columns and types. That is enough to build fixtures that line up.

$ misata dbt-unit-test --select customers --rows 4 --write

📖 Using target/catalog.json for the real column list and types.
  ⚠ 2 foreign key(s) were inferred from column naming because the project has
    no relationships test for them. Check them, and add relationships tests to
    make them explicit.

customers — 3 input(s):
  ✓ ref('stg_customers') — 3 column(s)
  ✓ ref('stg_orders') — 4 column(s) (customer_id → customer_id)
  ✓ ref('stg_payments') — 4 column(s) (order_id → order_id)

  ✓ tests/fixtures/customers__stg_customers.csv
  ✓ tests/fixtures/customers__stg_orders.csv
  ✓ tests/fixtures/customers__stg_payments.csv
  ✓ models/_misata_unit_test_customers.yml

The three fixtures:

# customers__stg_customers.csv
customer_id,first_name,last_name
5989,Brian,Davis
7337,Ute,Schwarz
7856,Alice,Bernard
5300,Claudia,Ortiz

# customers__stg_orders.csv
order_id,customer_id,order_date,status
3983,7856,2023-02-24,pending
7536,7856,2023-11-23,inactive
8262,7856,2021-12-09,active
9303,5300,2020-05-31,cancelled

# customers__stg_payments.csv
payment_id,order_id,payment_method,amount
5318,7536,Cash on Delivery,74.04
4219,9303,Debit Card,79.28
5991,9303,Wire Transfer,94.36
1919,9303,Apple Pay,85.27

The keys resolve. Orders belong to customers 7856 and 5300, and every payment belongs to an order that exists. The three-table join in customers.sql actually returns rows.

Look at what else came out of it, though, because this is the part that turned out to matter. Customers 5989 and 7337 have no orders at all. Nobody asked for that. It falls out of generating a realistic parent-child distribution, where not every parent gets a child, and it is precisely the row that catches a join-type bug.

Misata does not fill in the expect block. Deciding what the output should be requires knowing what the SQL is supposed to do, and a tool that guesses your expected values is a tool that certifies its own guess. So it writes the real output column names and leaves the rows to you:

    expect:
      format: csv
      rows: |
        customer_id,first_name,last_name,first_order,most_recent_order,number_of_orders,customer_lifetime_value
        # TODO: one row per expected output row

Filling those in for four customers took me about two minutes of reading the SQL. Customers with no orders get nulls. Customer 7856 has three orders, so first_order is the earliest of the three dates and number_of_orders is 3. Customer 5300 has one order with three payments against it, so their lifetime value is the sum of the three.

One small thing worth knowing: my first run failed because I wrote 258.91 for that sum, and the actual value is 258.90999999999997. dbt unit tests compare exactly, and floating point addition is floating point addition. Mildly annoying, and also a fair warning about what your warehouse is really storing.

$ dbt test --select test_type:unit
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

Does it catch the bug?

Back to the sabotage. Same one-word change, left join customer_payments becomes inner join customer_payments, and this time the unit test is in place.

########## 20 DATA TESTS ##########
Done. PASS=28 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=28

########## THE UNIT TEST ##########
1 of 1 FAIL 1 customers::test_customers .................... [FAIL 1 in 0.11s]

actual differs from expected:

@@ ,customer_id,first_name,last_name,first_order,most_recent_order,number_of_orders,...
   ,5300       ,Claudia   ,Ortiz    ,2020-05-31 ,2020-05-31       ,1               ,...
---,5989       ,Brian     ,Davis    ,null       ,null             ,null            ,...
---,7337       ,Ute       ,Schwarz  ,null       ,null             ,null            ,...
   ,7856       ,Alice     ,Bernard  ,2021-12-09 ,2023-11-23       ,3               ,...

	correct model	left changed to inner
20 data tests	pass	pass
1 generated unit test	pass	fail

The two rows marked --- are the customers who should be in the output and are not. The diff names them. It runs in a tenth of a second, on four rows of invented data, with no warehouse involved, and it would have failed in CI on the pull request rather than in a board meeting six weeks later.

Three things a real project taught the generator

I built this against jaffle_shop rather than against a schema I invented, and it found three bugs in my own tool. They are worth writing down because each one is the difference between something that demos well and something that works on your repo.

A manifest's columns is not the schema. jaffle_shop documents only the columns that carry tests. stg_customers documents customer_id, while the model actually selects three columns. Generate from that subset and you get a fixture missing columns the model reads. The fix is to prefer target/catalog.json, which is the warehouse's own answer to what the columns are, and to warn loudly when it is absent.

I also hit a nice illustration of why that matters. models/schema.yml documents a column on customers called total_order_amount. customers.sql emits customer_lifetime_value. The documented column does not exist and the real one is not documented, on main, today, in the most-cloned dbt project in the world. dbt does not complain about this, and neither would you. Trust the catalog, not the docs.

Most projects declare no data types at all. jaffle_shop declares none, so every column fell back to text, and customer_id came out full of product descriptions. An id column containing prose cannot be joined to anything, which means every fixture built from it is silently empty. Id-shaped columns now get integers.

Most projects have no relationships tests. jaffle_shop has exactly one, so there was nothing to tell the generator that stg_orders.customer_id points at customers. Without that, the fixtures do not line up. There is now narrow name-based inference for it: the column has to be id-shaped, the parent has to have that exact column, and the column's stem has to name the parent, so customer_id resolves to stg_customers. Anything inferred is reported as inferred, in a warning, every run, because a foreign key I guessed is not a foreign key you declared.

What this does not do

It generates the given, not the expect. You still have to know what your model should produce, and that is the correct division of labour: the tedious, mechanical, referentially-fussy part is automated, and the part that requires judgement stays with you.

It does not tell you which models need a unit test most. Today it lists all of them. The obvious next step is ranking by how much unasserted computation a model contains, since a model with four aggregates and two tests is a much better use of your afternoon than a staging view that renames three columns.

And it needs the upstream models to exist in the warehouse before dbt test will run, because dbt introspects each input relation to type a CSV fixture. Run dbt build once first. If you skip that you get "the relation doesn't exist", which is a confusing error for something that is only a prerequisite.

Reproduce all of it

git clone https://github.com/dbt-labs/jaffle_shop_duckdb.git
cd jaffle_shop_duckdb
pip install dbt-core dbt-duckdb misata
export DBT_PROFILES_DIR=$PWD

dbt build && dbt docs generate

misata dbt-unit-test --coverage
misata dbt-unit-test --select customers --rows 4 --write

Then change left join customer_payments to inner join customer_payments in models/customers.sql, run dbt build again, and watch 20 tests tell you everything is fine.

Misata is MIT licensed and on PyPI. If you run the coverage command against your own project and the answer is 0, I would genuinely like to know what got in the way of fixing that, because the fixtures were my guess and I would rather hear the real reason than keep guessing.

The claim I am making is narrow and I think it is true: your data tests are telling you the data arrived intact. Something still has to tell you the maths was right.