dbt / Intermediate: history and controlled workflows / Lesson 12

Unit tests and fixture boundaries

Isolate loan-state logic with small inputs while keeping warehouse integration, data tests and incremental merge verification distinct.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Define given inputs and expected output for a model.
Explain fixture, adapter and execution limits of native unit tests.

A unit fixture isolates one transformation question

A unit test can supply small controlled inputs for dependencies and compare the model’s output with an expected relation. This is different from a data test scanning a built relation for counterexamples. The library example asks only whether a validated missing return time yields open and a known return yields returned. It does not test feed delivery, all source types or a database transaction. Keep the input contract explicit so fixture simplification does not silently remove the defect the real pipeline faces. Native unit-test support and restrictions depend on the named generation and adapter.

Make the tested SQL small and unambiguous

The model int_loan_state selects only loan_id and a derived state from stg_loans. The preceding staging contract must already distinguish legitimate NULL from invalid parsing. A unit fixture for this model can focus on the two referenced fields, subject to the adapter’s schema and relation requirements. The Jinja fragment is not compiled or executed here. An expected two-row result is logical test data, not proof that the target database evaluated CASE or inferred the same types as a local parser.

jinja Illustrative technical example; logical results are reasoned, not engine-tested.

select loan_id,
 case when returned_at is null then 'open' else 'returned' end as loan_state
from {{ ref('stg_loans') }}

An illustrative int_loan_state model. Its expected behavior assumes the staged timestamp-presence meaning is already valid.

Write explicit inputs and expected rows

The YAML defines one controlled open loan and one returned loan. The timestamp string is synthetic fixture data whose conversion still depends on the target context. Expected rows contain only the model’s intended output. In a real authorized environment, parent relations or type metadata can still be required even when data rows are mocked. This is why native dbt unit tests are not automatically a pure Python offline test suite. No unit-test command, temporary relation or warehouse query is created by this course; the configuration is reviewed as text only.

yaml Illustrative technical example; logical results are reasoned, not engine-tested.

unit_tests:
  - name: loan_state_from_return_presence
    model: int_loan_state
    given:
      - input: ref('stg_loans')
        rows:
          - {loan_id: L1, returned_at: null}
          - {loan_id: L2, returned_at: '2026-10-02 10:00:00'}
    expect:
      rows:
        - {loan_id: L1, loan_state: open}
        - {loan_id: L2, loan_state: returned}

A v1.12-style unit-test fixture. It does not establish adapter execution, timestamp typing or parent relation availability.

Exercise incremental and macro branches separately

Incremental unit tests can require an explicit is_incremental override and a fixture for this when the model reads its target. The expected output normally describes rows produced by the model query, not the final state after a warehouse merge. Test initial and incremental paths, late changes and duplicate candidate keys as separate questions. Macro or variable overrides should reproduce the intended branch rather than bypass business rules until the test passes. SQL fixture formats and Jinja support differ from ordinary model files; do not assume a fixture renders every ref or macro embedded in arbitrary SQL.

Combine unit logic, data assertions and integration evidence

Keep a matrix of valid, missing, malformed, duplicate and boundary inputs, mapping each to the appropriate test layer. A small local standard-library model can validate the stated CASE-like mapping and expected identities, but cannot certify dbt rendering or SQL type behavior. Data tests then check built populations, while an authorized integration run verifies adapter and target behavior. Recovery tests address partial publication and retries separately. This course performs local fixture reasoning only, with no dbt package import, unit runner, database connection or claimed warehouse assertion. Avoid redundant tests that merely reproduce the implementation without challenging a meaningful contract.

Common errors and fixes

Unit-test expected rows are treated as the final incremental target

Why it happens: Model-query output was confused with adapter merge results.
How to fix it: Test candidate output and target transition as separate layers.
Prevent it next time: Label fixture scope explicitly in test names and review.

A mocked source removes an invalid-type boundary

Why it happens: Fixture simplification assumed parsing already succeeded without testing that contract elsewhere.
How to fix it: Keep parser and transformation tests with distinct edge cases.
Prevent it next time: Document what every mocked field is assumed to guarantee.

Native dbt unit tests are called warehouse-free by definition

Why it happens: Fixture isolation was confused with the framework’s execution requirements.
How to fix it: Review adapter support, parent metadata and target access before authorized execution.
Prevent it next time: Separate local models from actual dbt test evidence.

Check your understanding

1. What expected states follow L1 with NULL and L2 with a known valid return?

Show worked solution

L1 is open and L2 is returned under the stated staging contract. If NULL came from a failed parse, that assumption would be violated and needs a separate rejection test. The fixture establishes intended logic, not a successful warehouse unit-test run.

2. Does an incremental unit fixture alone prove idempotent replay after a commit timeout?