9. Tests and contracts

Chapter 7 gave the mechanism away: a dbt test is a query that should return zero rows. Everything else is convenience on top.

That simplicity is the reason dbt testing caught on. There is no framework to learn — if you can write a query that finds bad rows, you can write a test.

Two things called testing

Worth separating early, because dbt does both and they answer different questions.

   DATA TESTS                 UNIT TESTS
   ─────────────────────      ─────────────────────
   run against real rows      run against fixtures
   "is the data valid?"       "is the logic right?"
   run in production          run in CI
   catch bad input and        catch bugs in your
     broken loaders             SQL
   the original dbt test      added in dbt 1.8

Data tests are the ones you cannot skip. Unit tests are valuable for gnarly logic and unnecessary for a rename.

Generic tests

Four built in. One line of YAML each.

models:
  - name: customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

      - name: status
        tests:
          - accepted_values:
              values: ['active', 'churned', 'trial']

      - name: account_manager_id
        tests:
          - relationships:
              to: ref('employees')
              field: employee_id

Test	Fails when
unique	a value appears more than once
not_null	any value is null
accepted_values	a value is outside the list
relationships	a value has no match in the referenced model

relationships is referential integrity, checked after the fact. Warehouses do not enforce foreign keys; this is how you find out that four thousand orders point at customers who do not exist.

These four cover a surprising share of real data problems. The single highest-value pair is unique and not_null on every primary key, and a project with only that is meaningfully better off than one with none.

Custom generic tests

A generic test is a macro that takes model and column_name and returns a query selecting bad rows.

-- tests/generic/test_positive.sql

{% test positive(model, column_name) %}

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

{% endtest %}

      - name: amount_usd
        tests:
          - positive

Extra arguments are just macro arguments:

{% test recent(model, column_name, max_age_days=7) %}

select *
from {{ model }}
where {{ column_name }} < dateadd(day, -{{ max_age_days }}, current_date)

{% endtest %}

        tests:
          - recent:
              max_age_days: 2

Write one when you find yourself repeating the same singular test. Three copies of “amount must be positive” should be a generic test.

Singular tests

A .sql file in tests/ that selects failing rows. No macro, no YAML, no reusability — just a query about your specific data.

-- tests/assert_order_totals_match_line_items.sql

with order_totals as (
    select order_id, amount_usd from {{ ref('orders') }}
),

line_item_totals as (
    select order_id, sum(amount_usd) as amount_usd
    from {{ ref('order_line_items') }}
    group by 1
)

select
    o.order_id,
    o.amount_usd    as order_total,
    l.amount_usd    as line_item_total
from order_totals o
join line_item_totals l using (order_id)
where abs(o.amount_usd - l.amount_usd) > 0.01

This is where the interesting tests live. Generic tests check shape; singular tests check meaning. “The order total equals the sum of its line items”, “no customer is in two mutually exclusive segments”, “yesterday’s revenue is within 40% of the trailing average” — none of those are expressible as a column-level assertion, and all of them catch real bugs.

Because a singular test ref()s the models it touches, it lands in the graph in the right place automatically.

Severity

Not everything that is wrong should stop the pipeline.

      - name: shipping_address
        tests:
          - not_null:
              config:
                severity: warn

error (default) fails the run; warn prints and continues.

Thresholds make it conditional on scale:

          - not_null:
              config:
                severity: error
                error_if: ">1000"
                warn_if:  ">0"

Any nulls warn; more than a thousand fails. This is how you live with data that is imperfect but not broken — a handful of missing addresses is reality, a thousand is a loader problem.

Use warn deliberately, not as a way to quiet a noisy test. A permanently-warning test is one nobody reads, which is the same as no test with extra output. Chapter 14 names this.

store_failures

          - unique:
              config:
                store_failures: true

Instead of only counting failures, dbt writes them to a table — usually in a dbt_test__audit schema.

Turn this on for tests you will actually have to investigate. The difference between “142 rows failed” and a table you can query is the difference between a five-minute fix and an afternoon. store_failures_as (view, table, ephemeral) controls the shape.

Do not turn it on everywhere; it costs a relation per test.

Unit tests

Added in dbt 1.8. These test logic against fixtures, with no dependence on real data.

unit_tests:
  - name: test_order_size_classification
    model: orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, amount_usd: 150}
          - {order_id: 2, amount_usd: 50}
          - {order_id: 3, amount_usd: 100}
    expect:
      rows:
        - {order_id: 1, order_size: 'large'}
        - {order_id: 2, order_size: 'small'}
        - {order_id: 3, order_size: 'small'}

dbt builds the model against those fixture rows and compares the output to expect.

Note row 3. The boundary case — exactly 100 — is the reason to write the test. A > that should have been >= is invisible in production data and obvious here.

   ✓ WORTH UNIT TESTING
     boundary conditions
     complex CASE / window logic
     regex and string parsing
     date arithmetic across boundaries
     a bug you just fixed
     anything you rewrote nervously

   ✗ NOT WORTH IT
     a staging model that renames columns
     a straight join
     anything where the fixture would be
       longer than the model

Unit tests run against a warehouse and cost a query each, so select test_type:unit in CI and leave them out of production runs:

dbt test --select "test_type:unit"     # in CI
dbt build --exclude "test_type:unit"   # in production

Contracts

Chapter 3 introduced them; here is what they do at build time.

models:
  - name: customers
    config:
      contract:
        enforced: true
    columns:
      - name: customer_id
        data_type: integer
        constraints:
          - type: not_null
      - name: customer_name
        data_type: varchar(255)

With enforced: true, dbt checks the model’s actual columns and types against the YAML before persisting it, and fails if they differ.

   TEST                       CONTRACT
   ─────────────────────      ────────────────────
   runs after the build       checked at build
   checks the DATA            checks the SHAPE
   "are values valid?"        "are the columns
                               and types right?"
   a query returning rows     a schema comparison

A contract catches the rename you did not mean to publish. Enforce them on models other teams or tools depend on; skip them elsewhere, because every enforced contract is a YAML block someone must keep current.

dbt_expectations

A package porting Great Expectations’ assertions to dbt, and worth installing when the built-in four run out:

      - name: amount_usd
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 20
              max_value: 200

Distributions, row-count changes between runs, value formats, aggregate bounds. The aggregate ones catch a class of problem nothing else does — a pipeline where every individual row is valid and the total has quietly halved.

dbt_utils also ships extra tests: equal_rowcount, expression_is_true, unique_combination_of_columns, not_accepted_values.

How much testing is enough

The practical answer, in priority order:

   1. unique + not_null on every primary key
      → non-negotiable. this is the floor.

   2. freshness on every source
      → chapter 6. catches broken loaders.

   3. relationships on foreign keys that matter
      → catches orphaned rows

   4. accepted_values on every status/enum
      → catches new values nobody told you about

   5. singular tests for business invariants
      → the ones that catch real bugs

   6. unit tests on genuinely tricky logic
      → boundaries, regex, dates

   7. contracts on models others depend on

Stop before testing becomes theatre. Two hundred tests that always pass slow every run and train people to ignore output. A failing test must mean something is wrong; if a test cannot fail in a way anyone would act on, delete it.

The complementary rule: every incident becomes a test. When bad data reaches a dashboard, the fix is not only correcting the data — it is the test that would have caught it. This is how a test suite becomes valuable rather than decorative.

Running tests

dbt test                                  # everything
dbt test --select customers               # one model's tests
dbt test --select +customers              # and its ancestors'
dbt test --select "test_type:unit"        # unit only
dbt test --select "test_type:singular"    # singular only
dbt test --select "config.severity:error" # blocking only
dbt build                                 # tests interleaved

Prefer dbt build in production, per chapter 6 — it tests each node before building its children, so failures stop the branch instead of spreading.

What to take from this chapter

• A test is a query that should return zero rows. No framework, no assertions.
• Data tests check real rows; unit tests check logic against fixtures. Both matter, at different times.
• unique and not_null on every primary key is the floor. Add relationships and accepted_values where they mean something.
• Singular tests are where the valuable tests live — business invariants that no column-level check can express.
• Severity and thresholds let you tolerate imperfect data; a permanently-warning test is the same as no test.
• store_failures turns “142 rows failed” into a table you can query. Enable it on tests you will investigate.
• Unit tests catch boundary bugs that production data hides. Run them in CI, exclude them from production.
• Contracts check shape at build time; tests check data after it. Enforce contracts on models other people depend on.
• dbt_expectations adds distribution and aggregate tests — the ones that catch a total quietly halving.
• Stop before testing becomes theatre, and turn every incident into a test.

Next: documentation and lineage — docs blocks, the manifest, the catalog, exposures, and the site dbt generates from all of it.