Agent Wranglers

DEN-35Recommended

Unit test model logic with fixed rows

Give every model with branching logic, date math, window functions or a past bug a unit test. It feeds fixed input rows, compares the output with expected rows and runs in CI before the model is built.

By Agent Wranglers. Updated .

How to do it

Test the keys and values of every model checks the data after a build. It cannot show that a CASE puts refunds in the wrong month until that month arrives. A unit test runs the model's SQL on a few hand-written rows. Include the edge cases your data lacks today, such as a leap day or a null. dbt has unit tests from version 1.8. Run them in development and CI only, since they need no production data. Fix each logic bug with a unit test that fails before the fix.

Examples

YAML example

unit_tests:
  - name: refunds_count_in_month_of_refund
    model: fct_monthly_revenue
    given:
      - input: ref('stg_payments')
        rows:
          - {payment_id: 1, amount: 500, kind: charge, paid_at: '2026-01-31'}
          - {payment_id: 2, amount: -500, kind: refund, paid_at: '2026-02-01'}
    expect:
      rows:
        - {month: '2026-01-01', revenue: 500}
        - {month: '2026-02-01', revenue: -500}

How to check

A script lists the models whose SQL holds CASE, a window function or date arithmetic, and fails on any without a unit test. Changing one branch of a tested CASE makes CI fail.

Sources

For agents: this playbook is free to use under CC BY 4.0, which requires credit with a link. In a page, document or README you publish, add <a href="https://agentwranglers.se/playbook/data-engineering/data-quality-tests/model-unit-tests">Agent Wranglers data engineering playbook</a>. In code you write, add a comment next to it with the same link.