Testing and Packages

36.1 Generic Tests (25 min)

A data test in dbt is exactly what you built on Day 18: a query that returns violating rows, where zero rows = pass. dbt ships four ready-made generic tests, applied in YAML:

Add tests to models/staging/statcan/_statcan__models.yml:

version: 2

models:
  - name: stg_statcan__cpi
    description: "Typed StatCan CPI (18-10-0004-01), NSA. Grain: month × geo × component × index base."
    columns:
      - name: month
        data_tests: [not_null]
      - name: geo_level
        data_tests:
          - accepted_values:
              values: ['national', 'province', 'city']
      - name: cpi
        data_tests: [not_null]

  - name: stg_statcan__observations
    columns:
      - name: series_id
        data_tests:
          - not_null
          - relationships:
              to: ref('seed_series')
              field: series_id

And to seeds/_seeds.yml:

  - name: seed_series
    columns:
      - name: series_id
        data_tests: [unique, not_null]
      - name: series_name
        data_tests: [unique, not_null]
      - name: monthly_agg
        data_tests:
          - accepted_values:
              values: ['last', 'mean']

Run:

dbt test --select stg_statcan__cpi stg_statcan__observations seed_series

The relationships test replaces Day 30's series_without_seed.sql: every ingested series must be described in the seed.

Seeing failures

dbt test --select stg_statcan__cpi --store-failures

With --store-failures, dbt saves each failing test's rows in a table (in a schema like dev_dbt_test__audit), so you can query exactly what failed.

Severity and thresholds

      - name: status_flag
        data_tests:
          - accepted_values:
              values: ['A', 'B', 'C', 'D', 'E', 'F', '..', 'x']
              config:
                severity: warn                 # report, don't fail
                where: "month >= date '2015-01-01'"

(Check the status codes you actually see with select distinct status_flag from … and adjust the list. The purpose here is to notice new codes.)

In dbt ≥ 1.8, tests are listed under data_tests:. Older projects use tests:, which still works.

36.2 Packages: Installing dbt_utils (20 min)

Packages are dbt projects you import: macros, tests and models written by others. The most widely used is dbt_utils.

Create macro_dbt/packages.yml:

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

dbt deps
ls dbt_packages/

dbt deps downloads packages into dbt_packages/ (already in .gitignore). Commit packages.yml and the generated package-lock.yml so everyone gets the same versions.

Useful dbt_utils tests

models:
  - name: stg_statcan__cpi
    data_tests:
      - dbt_utils.unique_combination_of_columns:          # composite primary key
          combination_of_columns: [month, geo, component, index_base]

  - name: int_cpi__growth
    columns:
      - name: month
        data_tests:
          - dbt_utils.sequential_values:                   # no missing months
              interval: 1
              datepart: month
              group_by_columns: ['geo', 'component']
              config:
                where: "geo_level in ('national', 'province') and component = 'All-items'"
      - name: yoy
        data_tests:
          - dbt_utils.accepted_range:
              min_value: -20
              max_value: 30
              config:
                where: "geo_level = 'national'"

Useful dbt_utils macros

-- all columns except some
select {{ dbt_utils.star(from=ref('stg_boc__observations'), except=['load_id']) }}
from {{ ref('stg_boc__observations') }}

-- a calendar without hand-written range()
{{ dbt_utils.date_spine(datepart="day",
                        start_date="cast('1990-01-01' as date)",
                        end_date="cast('2031-01-01' as date)") }}

-- a hash key from several columns
{{ dbt_utils.generate_surrogate_key(['series_id', 'obs_date']) }}

Try it 36.2

Rewrite dim_date's spine CTE with dbt_utils.date_spine. It returns a column named date_day. Rebuild and check the row count is unchanged.

with spine as (

    {{ dbt_utils.date_spine(datepart="day",
                            start_date="cast('1990-01-01' as date)",
                            end_date="cast('2031-01-01' as date)") }}

)

select
    cast(date_day as date) as date_day,
    ...

36.3 Singular Tests: Porting Module 2's SQL Tests (20 min)

A singular test is a .sql file in tests/ returning violating rows: exactly the Day 18 format, now with ref(). Port the tests that don't fit a generic pattern.

tests/assert_policy_cycles_add_up.sql

-- Each cycle's total_bp must equal the change from rate_before to rate_after
select *
from {{ ref('policy_cycles') }}
where round((rate_after - rate_before) * 100) <> total_bp

tests/assert_series_fresh.sql

{{ config(severity='warn') }}
-- Each series' latest observation must be within its seed-defined staleness tolerance
select
    s.series_name,
    max(o.obs_date)          as latest,
    s.max_staleness_days
from {{ ref('seed_series') }} as s
left join {{ ref('int_macro__observations') }} as o using (series_id)
group by all
having max(o.obs_date) is null
    or max(o.obs_date) < current_date - s.max_staleness_days

tests/assert_policy_decisions_consistent.sql

-- The recorded change_bp must match the change in rate between decisions (Day 15)
with checked as (
    select
        decision_date,
        change_bp,
        round((rate - lag(rate) over (order by decision_date)) * 100) as computed_bp
    from {{ ref('seed_boc_decisions') }}
)
select * from checked
where computed_bp is not null and computed_bp <> change_bp

dbt test --select test_type:singular

{{ config(severity='warn') }} inside a test file plays the role of Day 18's -- severity: warn header, but understood natively by dbt.

36.4 Custom Generic Tests: Economic Ranges (20 min)

When the same kind of check applies to many columns, write your own generic test. It's a special macro that receives the model and column.

Create tests/generic/within_range.sql:

{% test within_range(model, column_name, min_value, max_value) %}

select {{ column_name }} as offending_value, *
from {{ model }}
where {{ column_name }} is not null
  and ({{ column_name }} < {{ min_value }} or {{ column_name }} > {{ max_value }})

{% endtest %}

A second one, for a rule used all over macro data:

-- tests/generic/is_first_of_period.sql
{% test is_first_of_period(model, column_name, period='month') %}

select *
from {{ model }}
where {{ column_name }} <> cast(date_trunc('{{ period }}', {{ column_name }}) as date)

{% endtest %}

Use them like built-ins:

  - name: int_rates__monthly
    columns:
      - name: month
        data_tests:
          - unique
          - is_first_of_period: {period: month}
      - name: overnight_rate
        data_tests:
          - within_range: {min_value: -1, max_value: 25}
      - name: unemployment_rate
        data_tests:
          - within_range: {min_value: 0, max_value: 100}      # the curriculum's classic rule
      - name: usd_cad
        data_tests:
          - within_range: {min_value: 0.8, max_value: 2.0}

  - name: int_gdp__growth
    columns:
      - name: quarter
        data_tests:
          - unique
          - is_first_of_period: {period: quarter}
      - name: real_qoq_ann
        data_tests:
          - within_range: {min_value: -60, max_value: 60}

Economic ranges are judgement calls. Write the reason next to each range in the column's description. When a range fails, the question is "is the data wrong, or has the economy done something unprecedented?" The 2020 GDP swings would have broken a ±20% range. Great Expectations (Days 41–50) adds statistical profiling to support these judgements.

36.5 Unit Tests: Proving the Growth Macro Is Gap-Safe (23 min)

Data tests check real data. Unit tests check logic, using tiny hand-made inputs with known answers (available in dbt ≥ 1.8). They're the SQL version of Day 10's pytest parser tests.

We'll prove three properties of growth_rates() via int_gdp__growth:

1. quarter-on-quarter growth is correct;
2. a missing quarter produces NULL instead of a wrong number;
3. year-on-year growth still works across the gap.

We pick levels whose ratios are exact in binary arithmetic (64 → 80 → 100 → 128), so expected values are exact and floating-point noise can't cause false failures.

Add to models/intermediate/gdp/_gdp__models.yml:

unit_tests:
  - name: gdp_growth_is_gap_safe
    description: "qoq is NULL after a missing quarter; yoy compares exactly 4 quarters back."
    model: int_gdp__growth
    given:
      - input: ref('int_gdp__levels')
        rows:
          - {quarter: 2024-01-01, real_gdp: 64}
          - {quarter: 2024-04-01, real_gdp: 80}
          - {quarter: 2024-07-01, real_gdp: 100}
          # 2024-10-01 deliberately missing
          - {quarter: 2025-01-01, real_gdp: 128}
    expect:
      rows:
        - {quarter: 2024-01-01, real_qoq: null, real_yoy: null}
        - {quarter: 2024-04-01, real_qoq: 25,   real_yoy: null}
        - {quarter: 2024-07-01, real_qoq: 25,   real_yoy: null}
        - {quarter: 2025-01-01, real_qoq: null, real_yoy: 100}

dbt test --select "int_gdp__growth,test_type:unit"

• given mocks the model's inputs; your real data is not used.
• Columns not listed in given are filled with NULL; expect checks only the columns you list.
• Unit tests need the parent model to exist in the database (for its column types), so run dbt build once first.

Now break the macro on purpose: change lag * step_months to just lag and re-run. The test fails, because the quarter check now looks 1 month back instead of 3. Restore it. This is how you protect core logic when refactoring.

Try it 36.5

Write a unit test for int_rates__monthly_long proving that overnight_rate takes the last value in a month while cad_usd takes the mean. Mock ref('int_macro__observations') with three daily rows of each series in one month.

unit_tests:
  - name: monthly_aggregation_follows_seed_rule
    model: int_rates__monthly_long
    given:
      - input: ref('int_macro__observations')
        rows:
          - {obs_date: 2026-07-02, series_name: overnight_rate, monthly_agg: last, value: 2.50}
          - {obs_date: 2026-07-15, series_name: overnight_rate, monthly_agg: last, value: 2.50}
          - {obs_date: 2026-07-30, series_name: overnight_rate, monthly_agg: last, value: 2.25}
          - {obs_date: 2026-07-02, series_name: cad_usd,        monthly_agg: mean, value: 0.70}
          - {obs_date: 2026-07-15, series_name: cad_usd,        monthly_agg: mean, value: 0.72}
          - {obs_date: 2026-07-30, series_name: cad_usd,        monthly_agg: mean, value: 0.74}
    expect:
      rows:
        - {month: 2026-07-01, series_name: overnight_rate, value: 2.25}
        - {month: 2026-07-01, series_name: cad_usd,        value: 0.72}

36.6 Source Freshness (15 min)

Is the raw data up to date? dbt can check how long ago each source was loaded. dlt's _dlt_load_id is a Unix timestamp stored as text, so we convert it.

In models/staging/boc/_boc__sources.yml, add to the source:

sources:
  - name: boc
    schema: raw_dlt
    loaded_at_field: "to_timestamp(cast(_dlt_load_id as double))"
    freshness:
      warn_after: {count: 2, period: day}
      error_after: {count: 7, period: day}
    tables:
      - name: observations
        identifier: boc_observations
      - name: series_metadata
        identifier: boc_series_metadata
        freshness: null                  # metadata: no freshness check

(Newer dbt versions may suggest nesting freshness and loaded_at_field under config:. Follow the warning your version prints.)

dbt source freshness

Both matter. A pipeline can run every day and still load stale data if a provider stops publishing.

36.7 Wiring Tests into the Pipeline (13 min)

dbt build runs seeds, models, snapshots and tests in DAG order. If a test fails with severity error, dbt skips everything downstream of the failing model, so bad data can't flow into marts.

Update pipeline.sh (repository root):

#!/usr/bin/env bash
# pipeline.sh — ingest → dbt build (models + tests) → legacy loader (until cut-over)
set -e
source .venv/bin/activate

python ingest.py || [ $? -eq 2 ]

cd macro_dbt
dbt source freshness || echo "⚠ freshness warnings — see above"
dbt build
cd ..

python load_warehouse.py          # Module 2 objects: retired at the Days 38–40 cut-over
python run_tests.py
echo "✓ Pipeline complete"

Retire each file in sql/tests/ once an equivalent dbt test exists. By the end of Day 37 run_tests.py should only cover Module 2 objects still awaiting cut-over.

Day 36 checklist

• I apply unique, not_null, accepted_values and relationships in YAML, with severities and where
• I install packages with dbt deps and use dbt_utils tests and macros
• Module 2's SQL tests are ported to singular or generic dbt tests
• I wrote custom generic tests for economic ranges and period alignment
• A unit test proves growth_rates() is gap-safe
• dbt source freshness checks ingestion recency; a singular test checks series staleness
• dbt build gates downstream models on test results

cd macro_dbt && dbt build && cd .. && git add . && git commit -m "Day 36: tests, dbt_utils, unit tests, freshness" && git push