Unit tests: is the SQL right?
Fake rows in, expected rows out, checked before your code is ever merged.
The Thursday the tests all passed
A month after Tuesday, Alice's data tests were in place. Then came a Thursday.
- She tidied up the
to_eurosmacro (step 5) while adding a discount, and retyped the division one zero short:/ 10.0instead of/ 100.0. - Every data test passed. No nulls, no duplicates, order ids unique, statuses valid.
- The rows looked perfect. Every amount was simply ten times too big.
Data tests couldn't catch that, and it isn't their job: they check that nothing is missing or repeated. A wrong number that's unique and not null passes them all.
What Alice needed was a test on the SQL itself, run on rows she makes up. She runs it in her userland like any test. Then CI (the checks the platform team wired to every pull request) runs it again before merge. Opening the pull request is all it takes.
Given, When, Then
The cleanest way to write one is the Given-When-Then pattern from software testing. dbt's YAML follows it, except that the model (When) comes first:
- Given: a fake input row that you define.
- When: the model runs on it.
- Then: the output must equal what you expect.
No real rows are read, so the result never changes.
amount_cents: 1000
amount_cents / 100.0
amount_eur: 10.00
# models/staging/ unit_tests: # top level, next to models: - name: cents__ models. yml become_ model: stg_orders # the model under test (WHEN) given: # GIVEN: these fake rows - input: source(euros 'raw', 'orders') rows: - {order_id: 1, amount_cents: 1000} expect: # THEN: the output must equal this rows: - {order_id: 1, amount_eur: 10.00}
Every ref() or source() the model reads needs its own input, written without {{ }} like any YAML key that expects a model (step 7). List only the columns the test cares about: dbt fills the rest with null. expect compares only the columns you list, cast to the column's type, so 10 and 10.00 match. Unit tests need dbt 1.8+.
How does it run without building anything? dbt takes the model's SQL, swaps each ref() and source() for your fake rows, runs that query in the warehouse, and compares the result with expect. No table is created. A failure prints both sets of rows, differences marked.
Where it pays off
The payoff is in the edge cases. Write a Given row with a null, a negative amount, a boundary value, and pin the Then. If someone later "optimises" the model and breaks the cents-to-euros maths, this fails in CI, before any real row.
Running them: CI, before the merge
# unit tests only (test_type: is a selector, like tag: or path:) dbt test --select test_type: # everything: models, data tests and unit tests, in dependency order dbt buildunit
In CI, dbt build on the pull request is the whole safety net. A model's unit tests run before it's built, so broken logic never creates a table. Its data tests then check the rows the build produced. Production skips unit tests: the fake rows give the same answer every time. dbt build --exclude-resource-type unit_ drops that whole kind of node.
dbt run --select <parent> --empty: zero rows, almost no compute. It replaces the table with an empty one, so skip it when the parent is already built.
