Testing Data with dbt

A dbt test is a query that returns the rows that should not exist. If it returns none, the test passes. That definition explains everything about how they behave.

The four generic tests

# models/staging/_staging.yml
version: 2

models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: status
        data_tests:
          - accepted_values:
              values: ['completed', 'returned', 'shipped']

dbt test --select stg_orders

11:02:14  Running with dbt=1.9.1
11:02:14  Found 5 models, 2 seeds, 5 data tests, 1 source, 431 macros
11:02:14
11:02:14  Concurrency: 4 threads (target='dev')
11:02:14
11:02:14  1 of 5 START test accepted_values_stg_orders_status__completed__returned__shipped  [RUN]
11:02:14  2 of 5 START test not_null_stg_orders_customer_id .............. [RUN]
11:02:14  3 of 5 START test not_null_stg_orders_order_id ................. [RUN]
11:02:14  4 of 5 START test relationships_stg_orders_customer_id__customer_id__ref_stg_customers_  [RUN]
11:02:14  1 of 5 PASS accepted_values_stg_orders_status__completed__returned__shipped  [PASS in 0.04s]
11:02:14  2 of 5 PASS not_null_stg_orders_customer_id .................... [PASS in 0.03s]
11:02:14  3 of 5 PASS not_null_stg_orders_order_id ....................... [PASS in 0.03s]
11:02:14  4 of 5 FAIL 1 relationships_stg_orders_customer_id__customer_id__ref_stg_customers_  [FAIL 1 in 0.04s]
11:02:14  5 of 5 START test unique_stg_orders_order_id ................... [RUN]
11:02:14  5 of 5 PASS unique_stg_orders_order_id ......................... [PASS in 0.03s]
11:02:14
11:02:14  Finished running 5 data tests in 0 hours 0 minutes and 0.21 seconds (0.21s).
11:02:14
11:02:14  Completed with 1 error and 0 warnings:
11:02:14
11:02:14  Failure in test relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ (models/staging/_staging.yml)
11:02:14    Got 1 result, configured to fail if != 0
11:02:14
11:02:14    compiled code at target/compiled/bookshop/models/staging/_staging.yml/relationships_stg_orders_customer_id__customer_id__ref_stg_customers_.sql
11:02:14
11:02:14  Done. PASS=4 WARN=0 ERROR=1 SKIP=0 TOTAL=5

There it is — the orphan order from lesson 2, the one whose customer does not exist and which silently disappeared from customer_orders. Nobody had to notice it.

Seeing which row failed

The message points at compiled SQL. Read it:

cat target/compiled/bookshop/models/staging/_staging.yml/relationships_stg_orders_customer_id__customer_id__ref_stg_customers_.sql

with child as (
    select customer_id as from_field
    from "bookshop"."main"."stg_orders"
    where customer_id is not null
),
parent as (
    select customer_id as to_field
    from "bookshop"."main"."stg_customers"
)
select from_field
from child
left join parent
    on child.from_field = parent.to_field
where parent.to_field is null

Run it and you get the offending value:

duckdb bookshop.duckdb -c "$(cat target/compiled/.../relationships_....sql)"

┌────────────┐
│ from_field │
│   int64    │
├────────────┤
│          9 │
└────────────┘

Customer 9. Every dbt test is a query like this, which means no test is a black box.

For repeat offenders, have dbt keep the failures as a table:

dbt test --select stg_orders --store-failures

11:08:52  4 of 5 FAIL 1 relationships_stg_orders_customer_id__customer_id__ref_stg_customers_  [FAIL 1 in 0.06s]
11:08:52
11:08:52  Completed with 1 error and 0 warnings:
11:08:52    Got 1 result, configured to fail if != 0
11:08:52    See the failures in dbt_test__audit.relationships_stg_orders_customer_i_2e0f7c
11:08:52
11:08:52  Done. PASS=4 WARN=0 ERROR=1 SKIP=0 TOTAL=5

The failing rows are now a real table an analyst can query and join against — far more useful than a number in a CI log.

Severity, when the source is imperfect

      - name: customer_id
        data_tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id
              config:
                severity: warn

11:12:07  4 of 5 WARN 1 relationships_stg_orders_customer_id__customer_id__ref_stg_customers_  [WARN 1 in 0.04s]
11:12:07
11:12:07  Completed with 1 warning:
11:12:07    Got 1 result, configured to warn if != 0
11:12:07
11:12:07  Done. PASS=4 WARN=1 ERROR=0 SKIP=0 TOTAL=5

Exit code zero, problem still visible. Better than deleting the test, which is the usual alternative.

Thresholds are the more honest version — “some orphans are expected, fifty are not”:

              config:
                severity: error
                error_if: '>50'
                warn_if: '>0'

11:14:33  4 of 5 WARN 1 relationships_stg_orders_customer_id__customer_id__ref_stg_customers_  [WARN 1 in 0.04s]

11:14:33  Done. PASS=4 WARN=1 ERROR=0 SKIP=0 TOTAL=5

Singular tests

For a rule that is specific to one model, write the query yourself. Any .sql file in tests/ that returns rows is a failing test.

-- tests/assert_order_amounts_are_positive.sql
select
    order_id,
    amount
from {{ ref('stg_orders') }}
where amount <= 0

-- tests/assert_daily_revenue_reconciles.sql
with from_orders as (
    select sum(amount) as total from {{ ref('stg_orders') }}
),
from_mart as (
    select sum(revenue) as total from {{ ref('daily_revenue') }}
)
select
    from_orders.total as orders_total,
    from_mart.total   as mart_total
from from_orders
cross join from_mart
where abs(from_orders.total - from_mart.total) > 0.01

dbt test --select test_type:singular

11:19:20  Found 5 models, 2 seeds, 7 data tests, 1 source, 431 macros
11:19:20
11:19:20  1 of 2 START test assert_daily_revenue_reconciles .............. [RUN]
11:19:20  2 of 2 START test assert_order_amounts_are_positive ............ [RUN]
11:19:20  2 of 2 PASS assert_order_amounts_are_positive .................. [PASS in 0.03s]
11:19:20  1 of 2 FAIL 1 assert_daily_revenue_reconciles .................. [FAIL 1 in 0.04s]
11:19:20
11:19:20  Completed with 1 error and 0 warnings:
11:19:20    Got 1 result, configured to fail if != 0
11:19:20
11:19:20  Done. PASS=1 WARN=0 ERROR=1 SKIP=0 TOTAL=2

The reconciliation test caught the same orphan from the other side: daily_revenue inner joins to customers, so its total is £19.99 short of the orders total. Reconciliation tests between a source and a mart are the highest-value tests in most projects — they catch join bugs that no column-level test can see.

Writing a reusable test

If you write the same singular test twice, make it generic. A test macro takes model and column_name and returns failing rows:

-- macros/test_not_negative.sql
{% test not_negative(model, column_name) %}

select {{ column_name }}
from {{ model }}
where {{ column_name }} < 0

{% endtest %}

      - name: amount
        data_tests:
          - not_negative

11:24:47  1 of 1 START test not_negative_stg_orders_amount ............... [RUN]
11:24:47  1 of 1 PASS not_negative_stg_orders_amount ..................... [PASS in 0.03s]

11:24:47  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

The dbt_utils package ships a few dozen of these — expression_is_true, accepted_range, equal_rowcount, unique_combination_of_columns. Install it before writing your own:

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: 1.3.0

dbt deps

11:26:10  Installing dbt-labs/dbt_utils
11:26:11  Installed from version 1.3.0
11:26:11  Up to date!

dbt build, and why it matters

dbt run then dbt test builds every mart first and tells you afterwards that staging was broken. dbt build interleaves them:

dbt build

11:31:02  Found 5 models, 2 seeds, 7 data tests, 1 source, 431 macros
11:31:02
11:31:02  1 of 14 START seed file main.raw_customers ..................... [RUN]
11:31:02  1 of 14 OK loaded seed file main.raw_customers ................ [INSERT 5 in 0.08s]
11:31:02  2 of 14 START seed file main.raw_orders ....................... [RUN]
11:31:02  2 of 14 OK loaded seed file main.raw_orders ................... [INSERT 6 in 0.06s]
11:31:02  3 of 14 START sql view model main.stg_customers ............... [RUN]
11:31:02  3 of 14 OK created sql view model main.stg_customers .......... [OK in 0.04s]
11:31:02  4 of 14 START sql view model main.stg_orders .................. [RUN]
11:31:02  4 of 14 OK created sql view model main.stg_orders ............. [OK in 0.04s]
11:31:02  5 of 14 START test not_null_stg_orders_order_id ............... [RUN]
11:31:02  5 of 14 PASS not_null_stg_orders_order_id ..................... [PASS in 0.03s]
11:31:02  6 of 14 START test relationships_stg_orders_customer_id__customer_id__ref_stg_customers_  [RUN]
11:31:02  6 of 14 FAIL 1 relationships_stg_orders_customer_id__customer_id__ref_stg_customers_  [FAIL 1 in 0.04s]
11:31:03  9 of 14 SKIP relationship error_if_not_null ................... [SKIP]
11:31:03  10 of 14 SKIP sql table model main.customer_orders ............ [SKIP]
11:31:03  11 of 14 SKIP sql table model main.daily_revenue ............. [SKIP]
11:31:03
11:31:03  Completed with 1 error and 0 warnings:
11:31:03
11:31:03  Done. PASS=8 WARN=0 ERROR=1 SKIP=3 TOTAL=14

SKIP=3. The marts were never rebuilt, so yesterday’s correct customer_orders is still sitting in the warehouse rather than being overwritten with data derived from a broken staging model. That single behaviour is the reason to use build in production and run only while developing.

Practice

  - name: customer_orders
    columns:
      - name: customer_id
        data_tests: [unique, not_null]

11:38:14  1 of 2 START test not_null_customer_orders_customer_id ......... [RUN]
11:38:14  2 of 2 START test unique_customer_orders_customer_id ........... [RUN]
11:38:14  1 of 2 PASS not_null_customer_orders_customer_id .............. [PASS in 0.03s]
11:38:14  2 of 2 PASS unique_customer_orders_customer_id ................ [PASS in 0.03s]

11:38:14  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

11:40:52  1 of 5 FAIL 1 accepted_values_stg_orders_status__completed__returned__shipped  [FAIL 1 in 0.04s]

11:40:52  Completed with 1 error and 0 warnings:
11:40:52    Got 1 result, configured to fail if != 0

11:40:52  Done. PASS=4 WARN=0 ERROR=1 SKIP=0 TOTAL=5

-- tests/assert_lifetime_value_not_negative.sql
select customer_id, lifetime_value
from {{ ref('customer_orders') }}
where lifetime_value < 0

11:43:30  1 of 1 START test assert_lifetime_value_not_negative ........... [RUN]
11:43:30  1 of 1 PASS assert_lifetime_value_not_negative ................. [PASS in 0.03s]

11:43:30  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

# dbt run && dbt test
11:46:02  Done. PASS=4 WARN=0 ERROR=0 SKIP=0 TOTAL=4      ← all marts rebuilt
11:46:03  Done. PASS=6 WARN=0 ERROR=1 SKIP=0 TOTAL=7      ← then told they were wrong

# dbt build
11:47:11  Done. PASS=8 WARN=0 ERROR=1 SKIP=3 TOTAL=14     ← marts never touched

Next: materializations — view, table, ephemeral, incremental, and when each is right.