A dbt Testing Strategy That Catches Regressions, Not 4,000 Warnings

The worst dbt project I ever inherited had 4,212 tests. Green runs were rare enough that nobody expected them. Failures were routed to a Slack channel with 40,000 unread messages. The suite had perfect coverage and zero authority.

The best one had about 600 tests on 900 models. When it went red, someone stopped what they were doing. That is the only metric that matters: does a failure change someone’s behavior in the next hour?

This post is the strategy I use to get there — which tests earn a place, how severity turns a suite into a policy, and where unit tests and dbt-expectations fit without turning schema.yml into a second codebase.

flowchart TD
  logic[SQL logic] --> unit["Unit tests (fixed inputs)"]
  rows[Production rows] --> generic["Generic tests (keys and grain)"]
  shape[Distributions and shape] --> expect["dbt-expectations (warn first)"]
  unit --> ci[Fails CI]
  generic --> ci
  expect --> triage[Triage queue]

Three layers, two destinations. Only two layers get to block a deploy.

Decide what a failure means before you write the test

Every test has exactly one job: fail a deploy, or inform a human. A test that does neither is a query you pay to run twice a day.

Deploy-blocking tests protect invariants. If order_id is not unique, every join downstream fan-outs and every revenue number is wrong. That test runs at error severity and CI does not merge over it.

Informing tests describe drift. Row counts dipped, a category showed up that was not there last week, null rates crept up. Those are real signals, but blocking a deploy over them punishes the wrong person — the model author did not cause the source drift. They run at warn and land in a triage queue, the same split I use for Python-side quality checks.

Write the destination down. If your team cannot say which channel a given test failure lands in and who reads it, the test is noise the moment it is merged.

Generic tests: keys and grain, ruthlessly

Every mart gets unique and not_null on its primary key. That is non-negotiable and it is also nearly the whole list. The grain of the table is the contract, and these two tests are the contract enforcement — the same idea as data contracts between teams, applied inside one project.

models:
  - name: fct_orders
    description: One row per order. Grain enforced below.
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
              config:
                severity: warn
                warn_if: '>0'
                error_if: '>500'
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'returned', 'cancelled']
              config:
                severity: warn

Notice what is at error and what is not. Uniqueness of the key: error. A dangling customer_id: warn until it crosses 500 rows, because late-arriving dimensions are a fact of life in this pipeline and a handful of orphans at 6 a.m. self-heals by 7. The error_if threshold is the line where “expected lag” becomes “the customer loader is down.”

What I do not do is put not_null on all 40 columns of a mart. Most columns are allowed to be null, and testing them anyway trains people that red is normal. If null-rate drift matters for a column, that is a shape test, and it goes in the warn layer.

Staging models get lighter treatment: unique and not_null on the source’s natural key, nothing else. The first-project layout applies — staging is 1:1 with sources, so testing staging is really testing the source, and you cannot fix a source with a dbt test.

Unit tests: logic without production data

Since dbt 1.8, unit tests are native. They run your model’s SQL against fixture rows you define, at compile time, before any production data is touched. This is where business logic lives — the CASE ladder that classifies orders, the window function that picks the latest record, the date math around fiscal years.

unit_tests:
  - name: test_order_status_precedence
    description: A refund after shipment must classify as returned, not shipped.
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, shipped_at: '2026-01-05', refunded_at: '2026-01-09'}
          - {order_id: 2, shipped_at: '2026-01-05', refunded_at: null}
          - {order_id: 3, shipped_at: null, refunded_at: null}
      - input: ref('stg_customers')
        rows:
          - {customer_id: 10}
    expect:
      rows:
        - {order_id: 1, status: 'returned'}
        - {order_id: 2, status: 'shipped'}
        - {order_id: 3, status: 'placed'}

The refund-after-shipment case is exactly the row that will never reliably exist in your dev schema and always exists in prod. A data test cannot pin it down; a unit test locks it forever. When someone refactors that CASE statement in six months, CI catches the regression in seconds, not in a Monday revenue review.

Keep unit tests on models with real logic. A staging model that renames columns does not need one. An incremental model with a watermark and a dedup window absolutely does — test the merge logic with fixtures for “late row,” “duplicate row,” and “row that updates an existing key.”

dbt-expectations: shape tests, warn first

dbt-expectations covers what generic tests cannot: distributions, row counts, freshness relative to peers, regex shape. Every one of these starts at warn. Shape drifts for legitimate reasons — marketing launches, seasonality, a new country. The test’s job is to make a human look, not to block a deploy.

models:
  - name: fct_orders
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000
          row_condition: "ordered_at >= dateadd('day', -1, current_date)"
          config:
            severity: warn
      - dbt_expectations.expect_column_values_to_be_between:
          column_name: amount
          min_value: 0
          max_value: 100000
          config:
            severity: warn
            warn_if: '>10'
            error_if: '>1000'

The warn_if and error_if pattern shows up again. Ten weird amounts is a ticket. A thousand is a broken upstream cast, and that one may block.

store_failures: from red to rows

A failing test without the failing rows is a scavenger hunt. Turn on store_failures_as: table for anything a human will triage, and the failing rows land in a dedicated schema you can query directly.

# dbt_project.yml
data_tests:
  my_project:
    +store_failures_as: table
    +schema: test_failures

flags:
  require_batched_execution_for_custom_microbatch_strategy: false

Now the on-call flow is: alert fires, open dev_test_failures.unique_fct_orders_order_id, see the 17 duplicated keys, join them back to the loader’s run IDs, find the retry that double-wrote. Ten minutes instead of an hour of re-deriving the failure query by hand.

One warning: failure tables accumulate. Put a drop-schema step in your weekly maintenance job, or the schema becomes an archive of every bad day you ever had.

flowchart LR
  fail[Test fails] --> stored["Failing rows stored as table"]
  stored --> query[On-call queries rows]
  query --> cause["Root cause (loader, source, logic)"]
  cause --> fix[Fix plus new unit test]

Every triaged failure should end with a unit test, or you will triage it again.

Run tests where they can act

Tests in CI run on modified models plus children — Slim CI with state and defer makes this cheap even on large projects. Tests in prod run right after the models they cover, not in a giant dbt test at the end of the night. A key-uniqueness failure at step 12 of 40 should stop step 13, because everything after it is computing on a fan-out.

Practically: dbt build instead of dbt run then dbt test. Build interleaves tests with models and skips downstream models when an upstream error-severity test fails. That single command choice enforces more discipline than any policy document.

Pitfalls

Testing every column because the YAML is right there. Coverage metrics reward this. On-call rotations pay for it. Test keys, grain, and contracts; warn on shape.

Severity: error on source drift. If the fix lives in another team’s loader, an error-severity test blocks your deploy for their bug. Warn, threshold, escalate.

Unit tests against live refs. Fixtures exist so the test is hermetic. If your unit test only passes when dev has the right rows, it is a flaky data test wearing a unit test costume.

A warn channel nobody owns. Warnings need a rota and a weekly sweep, or they are write-only. The 40,000-unread channel started as a good idea.

store_failures on multi-billion-row tests. The failure table is built with a SELECT of every failing row. A test that fails catastrophically will try to materialize the catastrophe. Set limit in the test config for the big ones.

FAQ

How many tests should a 900-model project have? Mine has roughly 600, and about 50 are unit tests. The ratio that matters is failures-to-action: if less than half your failures in a month led someone to change something, you have too many tests or the wrong ones.

Should staging models have tests at all? Keys only, and source freshness at the source level. Anything deeper is testing the upstream system, which you cannot fix from dbt. Push that conversation into a data contract instead.

Unit tests or dbt-expectations for business logic? Unit tests. Expectations sample production reality; unit tests pin logic against inputs you control. Logic bugs deserve deterministic tests.

Do warn-severity tests fail CI? No. dbt build exits zero on warnings by default. If you want warnings visible in CI, parse run_results.json and post them to the PR — visible, but not blocking.

Is 100% of marts under test a reasonable goal? Keys and grain on 100% of marts, yes. Everything else is proportional to blast radius. fct_revenue earns shape tests and unit tests. A convenience mart for one dashboard earns its key tests and nothing more.

What this means for data engineers

A test suite is a policy about what wakes people up. Write the policy first: errors block deploys and page, warnings get a rota and thresholds, everything else is a dashboard.

Put generic tests on keys and grain. Put unit tests on logic. Put dbt-expectations on shape, at warn, with error_if lines you chose on purpose. Store failures as tables so triage starts at the rows, not at a red X.

Then delete every test that has failed five times without anyone acting. It was never a test. It was a subscription to a feeling.