Course contents

The Analytics Engineer’s Field Guidedbt 2: make it trustworthyStep 8: Unit tests

Unit tests

Part 2 · dbt · step 8

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_euros macro (step 5) while adding a discount, and retyped the division one zero short: / 10.0 instead 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.

Pull request
CI, before merge
→
Unit tests
logic, on fake rows
→
Safe to merge
nothing deployed yet

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.

Unit test · Given / When / Then
GIVEN →
a fake input row
order_id: 1
amount_cents: 1000
WHEN →
the model runs
stg_orders
amount_cents / 100.0
THEN
the output must equal
order_id: 1
amount_eur: 10.00
# models/staging/_models.yml
unit_tests:                    # top level, next to models:
  - name: cents_become_euros
    model: stg_orders          # the model under test (WHEN)
    given:                     # GIVEN: these fake rows
      - input: source('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:unit

# everything: models, data tests and unit tests, in dependency order
dbt build

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_test drops that whole kind of node.

The parents must exist
dbt reads their column types to build the fake rows. A staging model's parents are sources, so they always exist. Further down, build a missing parent with 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.

Lesson quiz

  1. What does a dbt unit test run the model against?

    1. A zero-copy clone of the production database
    2. Input rows you write in the YAML given block
    3. The rows your last userland run left behind
    4. A sample of the production tables taken at run time
  2. After a refactor, every amount in stg_orders is ten times too large, yet unique and not_null pass. Why?

    1. The data tests ran before the model was rebuilt, so they only checked yesterday's rows
    2. unique and not_null only run on marts, so staging models are never checked
    3. Amounts need accepted_values, which would have flagged the change at once
    4. Data tests check the shape of rows, and a wrong value can still be unique and non-null
  3. Alice's merge request breaks the cents-to-euros maths in stg_orders, and its unit test pins the right result. What does dbt build in CI do?

    1. Builds stg_orders, then a data test flags the bad amounts
    2. Skips the unit test, since fake rows never change
    3. Fails the unit test before stg_orders is built
    4. Passes CI, and production's unit tests catch it later