dbt Unit Testing in 2026: A Practical Guide to Testing SQL Models Before They Hit Production

Updated: July 31, 2026

dbt unit tests let you assert that a SQL model produces an exact expected output for a set of mocked inputs, without touching your warehouse data. The feature was introduced in dbt Core 1.8 and has become the default way to test complex transformation logic in 2026. Unlike data tests (which check assertions against real production rows after a model runs), unit tests fabricate inputs, execute the model in isolation, and diff the result against a hand-written expectation. Honestly, in my experience running dbt in production, they're the single change that catches the most silent logic bugs before a backfill turns into a page.

• Native dbt unit tests ship in dbt Core 1.8+ and require zero extra packages. The older dbt-unit-testing community package by EqualExperts is now deprecated in favor of core.
• Unit tests use a given/expect block in YAML with three fixture formats: dict (inline), csv, and sql. Dict is best for small edge cases; csv is best for larger tables you want to diff cleanly.
• Unit tests run only during dbt build, dbt test --select "test_type:unit", or explicit selection. They do NOT run during a plain dbt run.
• Incremental models require overrides.macros.is_incremental: true to test the incremental branch, and you must also mock the this relation.
• Unit tests execute against the target warehouse, so they cost cloud credits. Pin them to a cheap adapter (DuckDB) in CI to keep the bill sane.
• Every model with non-trivial SQL (window functions, joins with edge cases, CASE logic, JSON parsing) should have at least one unit test. Trivial SELECT * passthroughs do not.

What are dbt unit tests?

A dbt unit test is a YAML-defined assertion that pins a model's SQL output to a specific expected result when given a specific set of mocked inputs. The engine substitutes your ref() and source() calls with the fixtures you provide, compiles the model, executes it against your warehouse, and diffs the result (row by row, column by column) against the expected output. If a single value differs, the test fails and prints the diff.

This matters because most production dbt bugs aren't "wrong schema" or "null in a NOT NULL column". Those are what schema.yml data tests catch. Production bugs are things like: a LEFT JOIN silently duplicates rows when a user has two email addresses; a window function's PARTITION BY misses a tenant column and leaks data across accounts; a CASE branch swallows a NULL and quietly returns the wrong revenue for a subset of customers.

Data tests can't reliably catch those, because the buggy SQL still returns "valid-looking" rows. The row count is right, the types are right, no nulls appear. Only a unit test that says "given these three input rows, the output MUST be exactly these two rows with these exact values" catches the logic error.

The dbt Labs team shipped unit tests in Core 1.8 in May 2024, after years of the community relying on the third-party dbt-unit-testing package by EqualExperts. That package is now deprecated, and new projects should use the core feature exclusively. As of dbt 1.10 in 2026 (and the newer Fusion Engine rewrite), unit tests support all major adapters including Snowflake, BigQuery, Postgres, Databricks, Redshift, and DuckDB.

Unit tests vs data tests: the difference

This is the single most common source of confusion when teams first adopt unit tests, so it's worth being precise. Both live in dbt, both use YAML, both fail your build if they fail. But they answer completely different questions.

Dimension	Data tests	Unit tests
What it tests	Assertions about production data (uniqueness, not-null, referential integrity, custom SQL predicates)	That a model's SQL logic produces the expected output for a mocked input
Inputs	Real rows from the warehouse	Fabricated fixtures in YAML/CSV/SQL
When to run	After each model build against real data	Before deploy, in CI, without touching production
Catches	Broken source data, upstream contract violations	SQL logic bugs, edge cases, join fanouts
Failure signal	"Your data is bad right now"	"Your code will be bad the next time this input appears"
Runs during	dbt test, dbt build	dbt build, dbt test --select test_type:unit
Cost per run	Scales with row count	Trivial (tests execute against 3-20 mocked rows)

You need both. Data tests protect you from the outside world changing: a source system starts emitting duplicate customer IDs, a Kafka schema evolves, someone re-runs a backfill twice. Unit tests protect you from yourself. You refactor a case statement, add a new metric, or "simplify" a window function and quietly change the semantics. When I've triaged a broken dbt DAG at 3am, the split is roughly 60/40: sixty percent upstream data problems (data tests would've caught it), forty percent someone's PR from last week (unit tests would have caught it in CI).

Writing your first dbt unit test

The mental model is straightforward. Pick a model, list the refs and sources it depends on, provide a small handful of rows for each, and declare what the model's output should be. Here's a realistic example, a staging model that dedupes customer events, keeping the most recent per customer_id.

The model, in models/staging/stg_customer_events.sql:

-- staging model: keep the latest event per customer
with ranked as (
    select
        customer_id,
        event_type,
        event_at,
        row_number() over (
            partition by customer_id
            order by event_at desc
        ) as rn
    from {{ ref('raw_customer_events') }}
    where event_at is not null
)
select
    customer_id,
    event_type,
    event_at
from ranked
where rn = 1

The unit test, added to the same directory in models/staging/_stg_customer_events.yml:

unit_tests:
  - name: test_stg_customer_events_keeps_latest
    description: Keeps only the most recent event per customer_id
    model: stg_customer_events
    given:
      - input: ref('raw_customer_events')
        rows:
          - {customer_id: 1, event_type: 'signup',   event_at: '2026-07-30 09:00:00'}
          - {customer_id: 1, event_type: 'purchase', event_at: '2026-07-31 12:00:00'}
          - {customer_id: 2, event_type: 'signup',   event_at: '2026-07-31 08:00:00'}
          - {customer_id: 3, event_type: 'purchase', event_at: null}
    expect:
      rows:
        - {customer_id: 1, event_type: 'purchase', event_at: '2026-07-31 12:00:00'}
        - {customer_id: 2, event_type: 'signup',   event_at: '2026-07-31 08:00:00'}

Notice three things. First, customer 3's row is dropped entirely, because the model filters event_at is not null and this test proves it. Second, customer 1's older signup row is dropped by the row_number logic; the test pins that behavior in place. Third, the test declares intent in the description, so future-you (or a reviewer) can read the YAML and understand what this model promises, without ever opening the SQL.

Run it with:

dbt test --select "stg_customer_events,test_type:unit"

If the model changes and a customer 3 row starts appearing, the test fails with a clean diff and CI blocks the merge. That's the entire pitch.

Fixture formats: dict, csv, and sql

dbt supports three ways to specify the rows in a given or expect block, and each has a real use case. Pick badly and your test file becomes unreadable.

Dict format (default, best for small tests)

Rows are inline in YAML as a list of dicts. Best when you have three to eight rows and want the test to be self-documenting on the PR diff. This is what the example above uses.

CSV format (best for larger tables)

When a fixture needs 20+ rows, inline dicts turn into a wall of text. Move it to a CSV file under tests/fixtures/:

given:
  - input: ref('raw_customer_events')
    format: csv
    fixture: raw_customer_events_seed  # tests/fixtures/raw_customer_events_seed.csv

The CSV lives in tests/fixtures/raw_customer_events_seed.csv and has a normal header row. Diffs are readable in Git, and you can regenerate the file from real (anonymized) data using dbt show or a quick Python script.

SQL format (best for computed fixtures)

When you need a fixture that's trivially derivable, say "1000 rows with sequential IDs", inline SQL beats hand-writing rows:

given:
  - input: ref('numbers')
    format: sql
    rows: |
      select generate_series as customer_id
      from generate_series(1, 1000)

Use SQL fixtures sparingly. They shift the fixture's semantics from data into code, which defeats part of the "human-readable test" value. In my own projects I use dict for 80% of tests, CSV for 15%, and SQL for the rare 5% where the input is genuinely a computed sequence.

How do you test incremental models in dbt?

Incremental models are the single hardest thing to unit test correctly, and they're also the models where you most need the tests. A bad merge key can silently double-count revenue for weeks before anyone notices. There are two branches to test: the full-refresh path and the incremental path.

The trick is overrides. dbt lets you override the is_incremental() macro per test, so you can force the model to compile either as an initial run or as an incremental append/merge:

unit_tests:
  - name: test_orders_incremental_merges_by_order_id
    model: fct_orders
    overrides:
      macros:
        is_incremental: true  # force the incremental branch
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, status: 'shipped',   updated_at: '2026-07-31 10:00:00'}
          - {order_id: 2, status: 'refunded',  updated_at: '2026-07-31 11:00:00'}
      - input: this  # mock the model's existing state
        rows:
          - {order_id: 1, status: 'pending',   updated_at: '2026-07-30 09:00:00'}
    expect:
      rows:
        - {order_id: 1, status: 'shipped',   updated_at: '2026-07-31 10:00:00'}
        - {order_id: 2, status: 'refunded',  updated_at: '2026-07-31 11:00:00'}

The key line is input: this. That mocks the model's current table state, which the merge SQL reads from. If you forget it, the model compiles against an empty this and your merge conditions never fire, giving a false pass. I hit this exact bug shipping a fact_orders migration last year, and it's one of those silent full-refresh traps I've walked into more than once. Unit tests for incrementals are non-negotiable in my pipelines now.

Testing dbt macros without a wrapper model

Macros are shared code, so bugs in them multiply. In older dbt, testing a macro meant writing a throwaway model that called it and asserting on the output. Clunky. Since 1.8 you can test macros directly by wrapping them in a tiny inline model in the unit test itself:

{% macro cents_to_dollars(cents_col) %}
    round({{ cents_col }} / 100.0, 2)
{% endmacro %}

Create a minimal wrapper model, e.g. models/utils/test_helper_cents.sql:

select {{ cents_to_dollars('amount_cents') }} as amount_usd
from {{ ref('some_source') }}

Then unit-test it:

unit_tests:
  - name: test_cents_to_dollars_rounds_correctly
    model: test_helper_cents
    given:
      - input: ref('some_source')
        rows:
          - {amount_cents: 12345}
          - {amount_cents: 0}
          - {amount_cents: 1}
    expect:
      rows:
        - {amount_usd: 123.45}
        - {amount_usd: 0.00}
        - {amount_usd: 0.01}

The wrapper model is trivial and stays in the project. If a teammate changes cents_to_dollars to divide by 1000 instead of 100 (a real bug I've fixed in review), this test fails immediately. For an alternative approach that leans on Python-side validation, see our guide to data validation with Pandera. Pandera schemas complement dbt unit tests when a pipeline crosses the SQL/Python boundary.

Running dbt unit tests in CI

Unit tests are only useful if they run automatically on every PR. Here's the pattern I use in production on GitHub Actions. It runs unit tests against a local DuckDB target for speed and cost reasons, and never touches the real warehouse:

name: dbt unit tests
on:
  pull_request:
    paths: ['**/*.sql', '**/*.yml']

jobs:
  unit-tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.12'
      - name: Install dbt + DuckDB adapter
        run: |
          pip install "dbt-core>=1.10" "dbt-duckdb>=1.10"
      - name: Compile + run unit tests
        env:
          DBT_PROFILES_DIR: ./.dbt-ci
        run: |
          dbt deps
          dbt parse
          dbt test --select "test_type:unit" --target ci

The .dbt-ci/profiles.yml points at DuckDB with an in-memory database. No credentials, no warehouse cost, and the whole suite runs in under 30 seconds even for a project with 200+ models. This works because unit tests are just SQL executed against mocked rows; the actual warehouse dialect only matters if you use adapter-specific functions. When you do (Snowflake's FLATTEN, BigQuery's UNNEST), fall back to a warehouse-specific target in CI and accept the small credit cost.

For teams comparing this workflow to SQLMesh's approach, which runs equivalent tests without needing a live warehouse at all, see our SQLMesh vs dbt comparison. The tests-in-CI story is one of the sharpest differences between the two tools.

Selecting tests intelligently in CI

On a large project, running every unit test on every PR is wasteful. Use dbt's state-based selection to run only tests for models that changed:

dbt test --select "state:modified+,test_type:unit" --state ./prod-manifest

The + selects downstream models too. A change to a staging model should also re-test any mart that depends on it. Store prod-manifest/manifest.json as a CI artifact updated on each production deploy.

Common gotchas and how to fix them

Here are the traps I've watched teams (including mine) fall into during the first six months of adopting unit tests.

1. Ephemeral models fail with "relation does not exist"

Ephemeral models are inlined as CTEs and don't materialize as tables, so mocking them as inputs works differently. Reference them normally in given (dbt handles the CTE substitution), but do not try to unit-test the ephemeral model itself if it's a simple passthrough. Test the downstream table model instead.

2. JSON and struct columns need string encoding in fixtures

In dict fixtures, JSON columns must be provided as strings, not as YAML dicts, because dbt serializes rows into a UNION ALL SELECT statement:

# Wrong -- YAML parses as a dict, warehouse chokes
- {user_id: 1, metadata: {plan: 'pro'}}

# Right -- string that the warehouse can parse as JSON
- {user_id: 1, metadata: '{"plan": "pro"}'}

3. Timestamps must be strings, not YAML timestamps

Quote timestamps so YAML doesn't parse them into Python datetime objects that dbt then has to re-serialize into warehouse dialect. Always '2026-07-31 12:00:00', never bare 2026-07-31 12:00:00.

4. Column order in expect must match the model output

dbt diffs column names, not positions, so this is only a problem if you rely on a specific order for some downstream consumer. If you do (unusual), assert it with a data test instead. Unit tests are for logic, data tests are for shape.

5. Tests don't run under dbt run

A team-wide "why isn't my test firing" question, always. Unit tests run under dbt build, dbt test, and explicit --select test_type:unit. A plain dbt run skips them entirely by design; run is materialization only.

Where to start if you have zero unit tests today

Don't try to backfill unit tests for a mature dbt project all at once. It's demoralizing, and the coverage number isn't what matters. Instead, add tests in this order:

1. Every incremental model, right now. These fail silently and produce wrong numbers. One test per incremental model, covering the incremental branch, pays for itself the first time it catches a merge-key regression.
2. Every model with a window function. PARTITION BY mistakes are the #2 cause of "wrong numbers in the dashboard" tickets I've seen.
3. Every model with more than two joins. Fanouts from many-to-many joins are silent. Row count still looks reasonable, but a subset of users get double-counted.
4. Every macro shared across three or more models. Bugs in shared macros scale with usage.
5. Every CASE statement encoding business logic. "Is this customer active?" logic drifts constantly, and each drift needs a test.

If you cover those five categories in a typical dbt project, you'll have somewhere between 30 and 100 unit tests. Enough to catch nearly every logic regression a reviewer misses, without inflating the test suite into an unmaintainable pile.

Frequently Asked Questions

Does dbt Core support unit testing?

Yes. Native unit tests shipped in dbt Core 1.8 in May 2024 and are stable through 1.10 in 2026. No community packages are required, and the older dbt-unit-testing package by EqualExperts is now deprecated in favor of the core feature.

What is the difference between dbt unit tests and data tests?

Data tests run assertions (uniqueness, not-null, custom predicates) against real production data after models build. Unit tests execute a model against mocked input rows and assert the exact output; they never touch production data. Data tests catch upstream data problems, and unit tests catch SQL logic bugs.

How do you test a dbt macro?

Wrap the macro in a small model that calls it, then write a unit test against that model. dbt doesn't support testing macros directly without a wrapper model, but wrappers are trivial (one SELECT with the macro applied) and they let you reuse the same fixture patterns you use elsewhere.

Can dbt unit tests run in CI without a warehouse?

Yes, using the dbt-duckdb adapter. Point a CI-only target at an in-memory DuckDB database and run dbt test --select test_type:unit. This works for any SQL that uses standard functions; adapter-specific SQL (Snowflake's FLATTEN, BigQuery's UNNEST) still needs a real warehouse target in CI.

Why does my dbt unit test say the model has no rows?

Most often you forgot to mock every input the model references. dbt substitutes only the refs you list in given, and any unmocked ref returns empty. Check that every ref() and source() in the compiled SQL has a matching input: entry. For incremental models, also mock input: this.

How often should dbt unit tests run?

On every pull request in CI, and as part of dbt build in production. Unit tests are cheap enough to run on every commit; the whole suite for a 200-model project typically finishes in under a minute on DuckDB. Don't gate them behind manual triggers, since the value is in catching regressions before merge.