dbt unit tests

dbt unit tests validate transformation logic using static mock rows and expected outputs defined directly in YAML files. Unlike data tests that validate the contents of physical tables after models run in the warehouse, unit tests evaluate pure SQL logic in isolation before any data gets materialized. They were introduced in dbt 1.8 to test edge cases, conditional branches, and mathematical calculations without querying production tables.

How unit tests work

You define input rows for upstream ref or source calls and declare the exact output rows you expect. When dbt runs the unit test, it substitutes upstream models with temporary mock tables or inline Common Table Expressions containing your mock data. dbt then compiles your model SQL, executes it against the mock input, and asserts that the resulting rows match your expected output.

Here is an example testing customer tier categorization in models/marts/dim_customers.yml:

unit_tests:
  - name: test_customer_tier_logic
    model: dim_customers
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, customer_id: 101, order_amount: 50}
          - {order_id: 2, customer_id: 101, order_amount: 60}
          - {order_id: 3, customer_id: 102, order_amount: 20}
    expect:
      rows:
        - {customer_id: 101, tier: 'gold'}
        - {customer_id: 102, tier: 'standard'}

The model can now be verified against edge cases without querying real customer records.

Unit tests versus data tests

Data tests and unit tests serve distinct purposes in a data platform:

• Data tests run against physical warehouse tables after dbt run finishes. They check data quality characteristics like uniqueness, non-null values, accepted values, and foreign key relationships.
• Unit tests run against pure model logic before dbt run executes. They verify that specific SQL statements, such as a complicated CASE expression or window function, produce correct outputs for known inputs.

Running unit tests in CI catches syntax mistakes and logical regressions in seconds. If a developer breaks an edge case in SQL, CI fails before spending warehouse credits materializing tables.

When to write them

Do not write unit tests for simple staging views that only rename columns or cast data types. Reserve them for models with complex business logic: revenue recognition, tiered pricing, sessionization windows, and multi-condition flags.