Testing

dbt Unit Testing: Mock Input Data and Test SQL Logic in Isolation

HelpMeTest

HelpMeTest

— 3 min read
dbt Unit Testing: Mock Input Data and Test SQL Logic in Isolation

dbt's schema tests (not_null, unique, relationships) verify data quality — they check the output of your models. But they can't verify whether your SQL logic is correct. A model that produces the wrong discount calculation or the wrong attribution logic might pass all schema tests while producing completely wrong numbers.

dbt 1.8 introduced unit testing: a way to define input data inline, run your SQL against it, and assert the exact output. This is test-driven development for SQL transformations.

What Unit Tests Cover

Schema tests ask: "Does the output satisfy these constraints?" Unit tests ask: "Given this input, does the transformation produce this exact output?"

Schema test: order_id is not null ✓
Unit test: given order {amount: 100, discount: 0.2}, final_amount should be 80 ✓

Unit tests are essential for:

  • Discount and pricing logic
  • Attribution models
  • Date/time calculations
  • Conditional business rules
  • Complex CASE WHEN chains

Setting Up a Unit Test

Unit tests live in your tests/ directory or alongside models in .yml files:

# models/marts/_orders_tests.yml
version: 2

unit_tests:
  - name: test_discount_applied_correctly
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, amount: 100.00, discount_pct: 0.0}
          - {order_id: 2, amount: 100.00, discount_pct: 0.2}
          - {order_id: 3, amount: 100.00, discount_pct: 1.0}
    expect:
      rows:
        - {order_id: 1, final_amount: 100.00}
        - {order_id: 2, final_amount: 80.00}
        - {order_id: 3, final_amount: 0.00}

Run it:

dbt test --select fct_orders,test_type:unit

# Output:
# 12:00:01 | Running with dbt=1.8.0
# 12:00:02 | Concurrency: 1 threads (target='dev')
# 12:00:03 | 1 of 1 START unit_test fct_orders::test_discount_applied_correctly ...
# 12:00:04 | 1 of 1 PASS unit_test fct_orders::test_discount_applied_correctly [PASS in 0.45s]

Testing Multi-Input Models

Most models join multiple sources. Mock each input separately:

unit_tests:
  - name: test_order_revenue_with_returns
    model: fct_order_revenue
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, customer_id: 10, amount: 150.00, status: 'completed'}
          - {order_id: 2, customer_id: 10, amount: 75.00, status: 'completed'}
          - {order_id: 3, customer_id: 20, amount: 200.00, status: 'completed'}
      
      - input: ref('stg_returns')
        rows:
          - {order_id: 1, returned_amount: 50.00}
          # order_id 2 and 3 have no returns
    
    expect:
      rows:
        - {customer_id: 10, gross_revenue: 225.00, returned_amount: 50.00, net_revenue: 175.00}
        - {customer_id: 20, gross_revenue: 200.00, returned_amount: 0.00, net_revenue: 200.00}

Mocking Sources

If your model reads from a source() instead of ref(), mock the source:

unit_tests:
  - name: test_event_attribution
    model: fct_attribution
    given:
      - input: source('raw', 'events')
        rows:
          - {event_id: 'e1', user_id: 'u1', event_type: 'view', created_at: '2026-01-01 10:00:00'}
          - {event_id: 'e2', user_id: 'u1', event_type: 'click', created_at: '2026-01-01 10:05:00'}
          - {event_id: 'e3', user_id: 'u1', event_type: 'purchase', created_at: '2026-01-01 10:10:00'}
      
      - input: source('raw', 'sessions')
        rows:
          - {session_id: 's1', user_id: 'u1', channel: 'paid_search', started_at: '2026-01-01 09:55:00'}
    
    expect:
      rows:
        - {user_id: 'u1', converting_channel: 'paid_search', time_to_convert_minutes: 15}

Testing Edge Cases

Unit tests shine for edge cases that are hard to construct in real data:

unit_tests:
  - name: test_zero_amount_order_handling
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, amount: 0.00, discount_pct: 0.5}
    expect:
      rows:
        - {order_id: 1, final_amount: 0.00}
  
  - name: test_null_discount_treated_as_zero
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, amount: 100.00, discount_pct: null}
    expect:
      rows:
        - {order_id: 1, final_amount: 100.00}
  
  - name: test_overflow_discount_capped_at_100pct
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, amount: 100.00, discount_pct: 1.5}
    expect:
      rows:
        - {order_id: 1, final_amount: 0.00}

Overriding Variables and Config

Unit tests can override dbt variables and model config:

unit_tests:
  - name: test_date_spine_for_custom_range
    model: fct_daily_metrics
    overrides:
      macros:
        is_incremental: false  # force full refresh mode
      vars:
        start_date: '2026-01-01'
        end_date: '2026-01-03'
    given:
      - input: ref('stg_events')
        rows:
          - {event_date: '2026-01-01', events: 100}
          - {event_date: '2026-01-02', events: 150}
          # Jan 3 missing — should be filled with 0
    expect:
      rows:
        - {metric_date: '2026-01-01', event_count: 100}
        - {metric_date: '2026-01-02', event_count: 150}
        - {metric_date: '2026-01-03', event_count: 0}

Partial Column Assertions

Sometimes you only care about specific columns. Use include or exclude to scope assertions:

unit_tests:
  - name: test_tax_calculation_only
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, amount: 100.00, tax_rate: 0.08, discount_pct: 0.0}
    expect:
      # Only assert on tax_amount — ignore other columns
      columns:
        include:
          - order_id
          - tax_amount
      rows:
        - {order_id: 1, tax_amount: 8.00}

CI Integration

# .github/workflows/dbt-unit-tests.yml
name: dbt Unit Tests
on: [pull_request]

jobs:
  unit-tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      
      - name: Install dbt
        run: pip install dbt-bigquery>=1.8.0
      
      - name: Run unit tests only (fast — no warehouse queries)
        run: |
          dbt deps
          # Unit tests run locally without warehouse — very fast
          dbt test --select test_type:unit
      
      - name: Run schema tests against CI dataset
        run: dbt test --select test_type:generic --target ci

Unit tests run without connecting to the warehouse — they execute SQL locally using DuckDB. This makes them extremely fast (milliseconds) and suitable for every pull request without warehouse costs.

TDD Workflow for SQL

  1. Write the unit test first (it will fail — the model doesn't exist yet)
  2. Write the model SQL to make the test pass
  3. Add edge case tests for null handling, boundaries, division by zero
  4. Add schema tests for production data quality

This workflow catches the classic data engineering bug: the model looks right, produces plausible numbers, but has a subtle off-by-one in date ranges or a wrong join condition that schema tests can't catch.

Read more

Start now free