dbt Macro Testing Patterns

Macro bugs silently affect every model that calls the macro. A wrong rounding calculation in cents_to_dollars corrupts every financial figure in the project. dbt provides two testing mechanisms for macros and a debugging workflow when things go wrong.

Integration Test Models

Before dbt 1.8’s native unit tests, the standard approach was integration test models: SQL models that exercise a macro with known inputs and verify the output. These still work, and they’re useful for macros where you want to test against real warehouse behavior.

Create a model in models/tests/ (or a similarly isolated folder configured to a separate schema):

-- models/tests/test_cents_to_dollars.sql
{{ config(materialized='table', schema='dbt_tests') }}

WITH test_data AS (
    SELECT 1000 AS amount_cents, 10.00 AS expected
    UNION ALL
    SELECT 999  AS amount_cents, 9.99 AS expected
    UNION ALL
    SELECT 1    AS amount_cents, 0.01 AS expected
    UNION ALL
    SELECT 0    AS amount_cents, 0.00 AS expected
)

SELECT
    amount_cents,
    {{ cents_to_dollars('amount_cents') }} AS actual,
    expected,
    {{ cents_to_dollars('amount_cents') }} = expected AS passed
FROM test_data

Run it with dbt build --select test_cents_to_dollars and query the output table to verify all passed values are true. You can also add a singular test that fails if any rows have passed = false:

-- tests/assert_cents_to_dollars_correct.sql
SELECT *
FROM {{ ref('test_cents_to_dollars') }}
WHERE passed = false

The strength of integration tests is that they run the actual compiled SQL against your warehouse. They catch warehouse-specific issues — type coercion differences, floating point behavior on your specific database — that mocked unit tests wouldn’t surface.

The weakness is that they require warehouse access and create real tables. They’re slower to run and require cleanup. For testing pure SQL logic, native unit tests are cleaner.

Native Unit Tests (dbt 1.8+)

dbt 1.8 introduced unit tests that let you test macro behavior in context, as part of a real model transformation, using mocked inputs:

unit_tests:
  - name: test_cents_to_dollars_conversion
    model: mrt__finance__orders
    given:
      - input: ref('base__shopify__orders')
        rows:
          - {order_id: 1, amount_cents: 1000}
          - {order_id: 2, amount_cents: 50}
          - {order_id: 3, amount_cents: 1}
    expect:
      rows:
        - {order_id: 1, order__amount_dollars: 10.00}
        - {order_id: 2, order__amount_dollars: 0.50}
        - {order_id: 3, order__amount_dollars: 0.01}

This tests cents_to_dollars in the context it actually appears in production — not in isolation, but as part of the model transformation that uses it. The given block defines mock input rows. The expect block defines what the model’s output should look like. dbt builds the model using only the mocked inputs and compares the result.

The macro gets tested as it’s actually used, which catches integration problems that isolated testing might miss. If the model passes the mocked inputs through a different column name, that’s a bug you’ll catch here.

For unit tests focused on macro behavior, keep the mock data minimal and representative. Include edge cases: zero values, the maximum expected scale, any null handling the macro is supposed to provide. See Unit Tests vs Data Tests in dbt for guidance on which models justify the investment in writing unit test fixtures.

The Compile-and-Inspect Workflow

When a macro produces unexpected output, dbt compile is your fastest debugging tool. Running:

dbt compile --select model_name

produces the fully-rendered SQL in target/compiled/[project]/[path]/model_name.sql. This is what actually gets sent to your warehouse.

Macros fail in two ways: compilation errors (Jinja syntax, wrong number of arguments, undefined variables) and logic errors (the SQL compiles but produces wrong results). For logic errors, reading the compiled output tells you exactly what SQL your macro generated. If it looks wrong, the macro is wrong.

For example, if cents_to_dollars was generating ROUND(amount_cents / 100, 2) instead of ROUND(amount_cents / 100.0, 2), you’d catch it immediately in the compiled output — the integer division would be visible right there in the SQL.

During development, compile before you run. It’s faster to catch macro issues at compile time than to wait for the warehouse to execute and return an error.

What to Test and When

Test every macro that implements business logic — currency conversion, discount calculation, key generation. These are macros where a silent error has business consequences.

Skip testing for simple structural macros that have no logic to verify — add_audit_columns that just appends fixed metadata columns, for instance. The test would just verify that the columns are added, which you’ll see immediately in the first model that uses it.

For macros that are well-scoped to a single responsibility, tests are easier to write: you only need to cover the one thing the macro does. A macro with parameter explosion and conditional branches requires an exponentially larger test matrix. This is another argument for keeping macros focused.

Run unit tests in CI, not production. They use mocked data and are irrelevant to production data health. Exclude them from production runs:

dbt build --exclude-resource-type unit_test

Integration test models can stay in development environments, or in a separate CI step that runs them against a development warehouse with real (but limited) data.