dbt unit tests with input fixtures

TL;DR

Define unit_tests: on the model with given inputs (mocked rows per upstream input) and expect rows (the required output). dbt runs the model SQL against the fixtures, so logic bugs surface in seconds without touching real data.

dbt unit tests with input fixtures

Use this when

• You want to test transformation logic, not data quality
• Tests must run fast without warehouse data
• A unit test fails and the fixture looks wrong

Not for this skill when

• You are testing data already in production (use dbt tests)
• Seeds infer wrong column types
• You are choosing snapshot strategies

Steps

1. Write the test in the model's YAML file:

models:
  - name: order_totals
    unit_tests:
      - name: sums_line_items_per_order
        given:
          - input: ref('stg_line_items')
            rows:
              - {order_id: 1, amount: 10.00}
              - {order_id: 1, amount: 20.00}
              - {order_id: 2, amount: 5.00}
        expect:
          rows:
            - {order_id: 1, total: 30.00}
            - {order_id: 2, total: 5.00}

Expected output: the contract. given mocks every input the model reads; expect states the exact output rows.

1. Run just the unit tests:

dbt test --select test_type:unit

Expected output: pass/fail per unit test with a diff of expected vs actual rows on failure. Failures show the mismatched rows, which is usually enough to spot the bug.

1. Mock ALL inputs the model touches. The common failure:

If the model refs stg_orders and stg_line_items but the test only
mocks stg_line_items, the test errors on the missing input.
Every ref, source, and macro input needs a given block.

Expected output: complete fixtures. Audit the model's refs when a test errors on setup rather than on logic.

1. Test the edge cases that break in production, not just the happy path:

given:
  - input: ref('stg_line_items')
    rows:
      - {order_id: 1, amount: null}      # null handling
      - {order_id: 2, amount: -5.00}     # negative handling
      - {order_id: 3, amount: 0.00}      # zero handling

Expected output: fixtures covering nulls, negatives, zeros, and empty inputs. Unit tests earn their keep on exactly these rows.

1. Know what unit tests do not cover:

Unit tests verify SQL logic against fixtures. They do not verify:
- that upstream models produce the fixture shape (use data tests),
- performance on real volumes,
- warehouse-specific function behavior differences.
Pair them with dbt data tests on the real tables.

Expected output: a testing strategy with both layers, not a false sense of coverage from one.

Variant phrasings

dbt unit test given expect example

Step 1 is the canonical shape. given per input, expect for the model output.

dbt unit test failing fixture

The fixture rows do not match what the model actually reads (wrong columns, missing input). Compare the model's refs against the given blocks (step 3).

dbt unit tests vs data tests

Unit tests mock inputs and test logic; data tests (unique, not_null, custom) run against real built tables. Use both.

Why it happens

dbt compiles the model SQL and runs it against the fixture rows as ephemeral tables. This isolates the transformation logic from data availability, so tests run in CI in seconds and catch logic regressions before the warehouse is even involved.

Edge cases

• Fixture column types follow the mock, not the real table; type-sensitive logic (e.g. integer division) can behave differently.
• Macros with side effects or adapter-specific SQL may not work in the unit-test context.
• overrides exist for env vars and macros when the model needs them; without overrides those tests error.
• Keep fixtures small; a 500-row fixture is a data test wearing a costume.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstpgF-mCCWgHU5rtWFkHYvQ