Unit Tests on Fixed Inputs

Concepts covered: Unit tests

A data test queries the table a model built and reports the rows that break a rule, so it finds only the faults that the warehouse's rows exercise today. When no completion in production scored exactly 70 this week, a bug on that boundary passes every data test and waits for the first learner to hit it. A unit test takes the warehouse's data out of the question: you write the input rows by hand, write the rows the model must produce from them, and dbt runs the model's SQL over your rows and compares the 2 results.

The model under test

select
    completion_id,
    user_id,
    score,
    case
        when score >= 90 then 'legendary'
        when score >= 70 then 'pass'
        else 'retry'
    end as outcome,
    score >= 70 as counts_for_streak
from {{ ref('stg_lesson_completions') }}

Two rules live here: the outcome label with its thresholds at 90 and 70, and counts_for_streak, which must agree with the label. The rows worth writing sit on each side of those boundaries, and the test holds exactly those: a 90 that is legendary by 0 points and an 89 that misses it by 1, a 70 that passes by 0 points and a 69 that misses by 1, and a 95 well inside the top band.

The test in YAML

unit_tests:
  - name: outcome_boundaries
    model: int_lesson_outcomes
    given:
      - input: ref('stg_lesson_completions')
        rows:
          - {completion_id: 1, user_id: 7, score: 95}
          - {completion_id: 2, user_id: 7, score: 70}
          - {completion_id: 3, user_id: 8, score: 69}
          - {completion_id: 4, user_id: 8, score: 90}
          - {completion_id: 5, user_id: 9, score: 89}
    expect:
      rows:
        - {completion_id: 1, outcome: legendary, counts_for_streak: true}
        - {completion_id: 2, outcome: pass, counts_for_streak: true}
        - {completion_id: 3, outcome: retry, counts_for_streak: false}
        - {completion_id: 4, outcome: legendary, counts_for_streak: true}
        - {completion_id: 5, outcome: pass, counts_for_streak: true}

unit_tests is a top-level key in any properties file, beside models. model names the model whose SQL runs. Each given entry names an input by the same ref() or source() call the model makes and lists its rows. expect lists the rows the model must return. A column left out of a given row is null, and expect compares only the columns it lists, so completed_date stays out of the inputs and user_id and score stay out of the expectations. What remains reads as the 2 rules the test checks and nothing else.

What dbt sends

dbt never reads stg_lesson_completions for this test. It renders the model with the ref() replaced by a CTE holding the given rows, each value cast to the column's type in the real staging table, wraps the model's SQL as a subquery that keeps only the expected columns, and unions it with the expected rows under a label column. The whole test is 1 query, and it creates no table.

with __dbt__cte__stg_lesson_completions as (
    select
        cast(1 as integer) as completion_id,
        cast(7 as integer) as user_id,
        cast(95 as integer) as score,
        cast(null as date) as completed_date
    union all
    select
        cast(2 as integer),
        cast(7 as integer),
        cast(70 as integer),
        cast(null as date)
    union all
    select
        cast(3 as integer),
        cast(8 as integer),
        cast(69 as integer),
        cast(null as date)
    union all
    select
        cast(4 as integer),
        cast(8 as integer),
        cast(90 as integer),
        cast(null as date)
    union all
    select
        cast(5 as integer),
        cast(9 as integer),
        cast(89 as integer),
        cast(null as date)
),
actual as (
    select
        completion_id,
        outcome,
        counts_for_streak,
        'actual' as actual_or_expected
    from (
        select
            completion_id,
            user_id,
            score,
            case
                when score >= 90 then 'legendary'
                when score >= 70 then 'pass'
                else 'retry'
            end as outcome,
            score >= 70 as counts_for_streak
        from __dbt__cte__stg_lesson_completions
    ) model_sql
),
expected as (
    select
        completion_id,
        outcome,
        counts_for_streak,
        'expected' as actual_or_expected
    from (
        select
            1 as completion_id,
            'legendary' as outcome,
            true as counts_for_streak
        union all
        select
            2,
            'pass',
            true
        union all
        select
            3,
            'retry',
            false
        union all
        select
            4,
            'legendary',
            true
        union all
        select
            5,
            'pass',
            true
    ) fixture
)
select *
from actual
union all
select *
from expected

Every actual row must match an expected row and nothing may be left over. When they differ, dbt prints the unmatched rows from both sides with their labels, which is the whole diagnosis. If the model had score > 70 on the second threshold, the output would be the 2 rows below: the row with completion_id 2 came back as retry and false, so the comparison excludes the boundary the rule includes.

Where it runs in a build

dbt test --select "test_type:unit" runs every unit test and nothing else. In dbt build, a model's unit tests run after its parents are built and before the model itself, so a failing unit test skips the model and everything downstream, the same way a failing data test does. The difference is timing: a data test fails after a bad table exists, and a unit test fails before the model has written anything. One consequence of the casts in the query above: the staging relation must exist in the warehouse so dbt can read its column types, which section 3 returns to.

select
    completion_id,
    case
        when score >= 90 then 'legendary'
        when score >= 70 then 'pass'
        else 'retry'
    end as outcome,
    score >= 70 as counts_for_streak
from {{ ref('stg_lesson_completions') }}

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.