dbt Unit Tests

Unit tests in dbt let you validate the SQL logic inside a model by providing known input data and asserting the exact output you expect. Unlike data tests that check live warehouse data, unit tests run against small, hardcoded datasets you define yourself. They test your business logic in isolation — fast, predictable, and independent of production data.

Unit Tests vs Data Tests

Data tests (generic + singular):
  Run against real warehouse data
  Check: "Is today's data valid?"
  Example: order_id is not null in fct_orders

Unit tests:
  Run against mock input data you define
  Check: "Does my SQL logic produce the right answer?"
  Example: given this set of orders, does the revenue calculation
           produce exactly $1,234.56?

Why Unit Tests Matter

Consider a model that calculates order tier based on amount:

-- models/fct_orders.sql
select
    order_id,
    amount_dollars,
    case
        when amount_dollars >= 500 then 'high'
        when amount_dollars >= 100 then 'medium'
        else 'low'
    end as order_tier
from {{ ref('stg_orders') }}

A data test only checks that order_tier is not null. A unit test checks that an order with $499.99 is labelled "medium", not "high". The data test cannot catch boundary errors; the unit test catches them immediately.

Defining a Unit Test

Unit tests are defined in YAML files inside your models/ folder, under the model they test:

# models/marts/schema.yml
version: 2

unit_tests:
  - name: test_order_tier_boundaries
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, amount_dollars: 49.99}
          - {order_id: 2, amount_dollars: 100.00}
          - {order_id: 3, amount_dollars: 499.99}
          - {order_id: 4, amount_dollars: 500.00}
          - {order_id: 5, amount_dollars: 1500.00}
    expect:
      rows:
        - {order_id: 1, order_tier: 'low'}
        - {order_id: 2, order_tier: 'medium'}
        - {order_id: 3, order_tier: 'medium'}
        - {order_id: 4, order_tier: 'high'}
        - {order_id: 5, order_tier: 'high'}

Running Unit Tests

# Run all unit tests in the project
dbt test --select test_type:unit

# Run unit tests for a specific model
dbt test --select fct_orders,test_type:unit

# Run all tests (data + unit) for a model
dbt test --select fct_orders

Unit Test Output

$ dbt test --select fct_orders,test_type:unit

1 of 1 START unit_test fct_orders::test_order_tier_boundaries .... [RUN]
1 of 1 PASS  unit_test fct_orders::test_order_tier_boundaries .... [PASS in 0.3s]

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

When a unit test fails, dbt shows a diff between the expected and actual output:

FAIL unit_test fct_orders::test_order_tier_boundaries

Rows in actual output but not expected:
  {order_id: 4, order_tier: 'medium'}   ← expected 'high'

Rows in expected output but not actual:
  {order_id: 4, order_tier: 'high'}

Mocking Multiple Inputs

When a model joins multiple upstream models, mock each one in the given block:

unit_tests:
  - name: test_revenue_by_region
    model: fct_revenue_by_region
    given:
      - input: ref('fct_orders')
        rows:
          - {order_id: 1, customer_id: 10, amount_dollars: 200.00}
          - {order_id: 2, customer_id: 20, amount_dollars: 300.00}
      - input: ref('dim_customers')
        rows:
          - {customer_id: 10, region: 'North America'}
          - {customer_id: 20, region: 'Europe'}
    expect:
      rows:
        - {region: 'North America', total_revenue: 200.00}
        - {region: 'Europe',        total_revenue: 300.00}

Mocking Source Tables

unit_tests:
  - name: test_stg_orders_filters_cancelled
    model: stg_orders
    given:
      - input: source('ecommerce', 'orders')
        rows:
          - {id: 1, status: 'completed', amount_cents: 10000}
          - {id: 2, status: 'cancelled', amount_cents: 5000}
    expect:
      rows:
        - {order_id: 1, status: 'completed', amount_dollars: 100.00}
        # order 2 is cancelled and must not appear in the output

Testing Edge Cases with Unit Tests

Edge Case                         Unit Test Scenario
---------------------------       ---------------------------
Null handling                     Input with NULL values, check output
Division by zero                  Denominator = 0, check for null result
Boundary conditions               Values at exact threshold (e.g. 100.00)
Empty input                       given: rows: []  expect: rows: []
Duplicate keys in join            Two rows with same ID in joined table
Date arithmetic                   Specific date values across month boundary

Unit Tests in CI

Include unit tests in your CI pipeline alongside data tests:

# CI job commands
dbt deps
dbt build --select state:modified+
# dbt build already includes dbt test which runs unit tests

Unit tests run without connecting to a warehouse — they execute locally using DuckDB under the hood. This makes CI fast even when warehouse connections are slow or have costs per query.

When to Write Unit Tests

• Any CASE WHEN logic with multiple branches or boundary conditions
• Date arithmetic (date differences, date truncation, fiscal period logic)
• Revenue calculations, tax computations, or discount logic
• Complex conditional aggregations
• Any model where a logic error would cause wrong numbers in a report