Fixtures and Overrides

Concepts covered: Unit tests

The dict rows in section 0 are 1 of 3 formats a given or expect block accepts, and the choice is about reading, since each format becomes the same CTE. Dict rows suit a handful of rows with a handful of columns. CSV suits a wide input, where 1 header line names the columns once and each row is a line of values. SQL suits rows that are easier to compute than to type, such as the same row repeated with 30 consecutive dates.

unit_tests:
  - name: outcome_boundaries_csv
    model: int_lesson_outcomes
    given:
      - input: ref('stg_lesson_completions')
        format: csv
        rows: |
          completion_id,user_id,score
          1,7,95
          2,7,70
          3,8,69
          4,8,90
          5,9,89
    expect:
      format: csv
      fixture: outcome_boundaries_expected

fixture names a file, tests/fixtures/outcome_boundaries_expected.csv, laid out as a header line and rows, so 1 file can serve several tests of the same model. With format: sql, rows holds a SELECT whose result is the input, and that SELECT may stack rows with union all or compute them from a sequence.

given:
  - input: ref('stg_lesson_completions')
    format: sql
    rows: |
      select 1 as completion_id, 7 as user_id, 95 as score
      union all
      select 2, 7, 70

A model with 2 inputs

A join reads 2 relations, and the unit test gives each one. Rows that should find a partner share a key, rows that should drop out do not, and the expected rows state which survived. An input the model reads from a source is given by its source() call in the same way. An input given as rows: [] tests what the model does with nothing, which is where a missing coalesce or an inner join written where a left join belongs shows itself.

select
    u.user_id,
    u.country,
    coalesce(sum(c.xp), 0) as total_xp
from {{ ref('stg_users') }} as u
left join {{ ref('stg_lesson_completions') }} as c
    on c.user_id = u.user_id
group by u.user_id, u.country

unit_tests:
  - name: users_without_completions_keep_zero
    model: int_user_xp
    given:
      - input: ref('stg_users')
        rows:
          - {user_id: 7, country: BR}
          - {user_id: 8, country: BR}
      - input: ref('stg_lesson_completions')
        rows:
          - {completion_id: 1, user_id: 7, xp: 10}
          - {completion_id: 2, user_id: 7, xp: 15}
    expect:
      rows:
        - {user_id: 7, total_xp: 25}
        - {user_id: 8, total_xp: 0}

Change the left join to an inner join and user 8 disappears from the actual rows while the expected row for user 8 stays, and the test prints both. Remove the coalesce and user 8 comes back with a null total against an expected 0. Each of the 2 defects a join can carry has 1 row in this test that names it.

Pinning the clock

A model that reads the current time gives a different answer each day, and a unit test with a fixed expectation would pass this week and fail next week. {{ dbt.current_timestamp() }} is a function dbt ships that renders to the warehouse's current time, and a unit test can replace what it renders with a literal under overrides.

select
    user_id,
    max(completed_date) as last_completed_date,
    max(completed_date) >= date_add('day', -7, cast({{ dbt.current_timestamp() }} as date)) as is_active
from {{ ref('stg_lesson_completions') }}
group by user_id

unit_tests:
  - name: active_means_7_days
    model: int_user_activity
    given:
      - input: ref('stg_lesson_completions')
        rows:
          - {completion_id: 1, user_id: 7, completed_date: 2026-03-03}
          - {completion_id: 2, user_id: 8, completed_date: 2026-03-02}
    overrides:
      macros:
        dbt.current_timestamp: "timestamp '2026-03-10 00:00:00'"
    expect:
      rows:
        - {user_id: 7, is_active: true}
        - {user_id: 8, is_active: false}

The override's value is text pasted where the call stood, so it must be a SQL expression the warehouse accepts, here a timestamp literal. With the clock held at 10 March, 7 days back is 3 March: user 7, who finished a lesson that day, is active, and user 8, 1 day earlier, is not. The test now states the rule, inclusive at exactly 7 days, in a form that passes on any date it runs, and the row for user 7 is the one that fails if someone later writes the comparison as strictly greater.

A given replaces

• A ref() or source() the model reads
• With rows you wrote, cast to the input's real column types
• Required for every input the model reads

An override replaces

• A function call inside the model, such as the current time
• With a literal pasted in its place
• Optional, only where the output would depend on the run

What a unit test cannot replace is the model's own SQL, which is the point: everything around the logic is held still so the logic alone decides the result.

About this lesson

This section is part of the Testing: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.