dbt Tests Explained: Automated Validation for Every Model

A data pipeline that runs without errors and a data pipeline that produces correct data are two different claims, and it’s entirely possible to have the first without the second. A dbt run can complete successfully while quietly producing duplicate rows, unexpected nulls, or a join that fans out and silently inflates every downstream number. dbt’s testing framework exists to catch exactly that gap — validating not just that models built, but that what they built is actually correct.

The Two Kinds of dbt Tests

dbt supports two fundamentally different testing approaches, and knowing when to reach for each one matters:

Generic tests are covered in depth in Generic Tests and custom SQL-based validation in Custom Tests — this article covers how the testing system works as a whole.

Attaching Tests in YAML

The most common way tests enter a project is directly alongside a model’s column documentation.

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled']
      - name: customer_id
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id

Four tests, four lines each — no separate test file to write, no SQL to hand-craft, because these are all built-in generic tests dbt ships out of the box.

Running Tests

# Run every test in the project
dbt test

# Run tests for a specific model only
dbt test --select stg_orders

# Run tests for a model and everything downstream of it
dbt test --select stg_orders+

Output tells you exactly which test failed and, critically, how many rows failed it — not just a pass/fail boolean.

1 of 4 START test not_null_stg_orders_order_id ......... [RUN]
1 of 4 PASS not_null_stg_orders_order_id ................ [PASS in 0.42s]
2 of 4 START test unique_stg_orders_order_id ............ [RUN]
2 of 4 FAIL 3 unique_stg_orders_order_id ................ [FAIL 3 in 0.51s]

A FAIL 3 means three rows violated uniqueness — dbt runs the test as an actual SQL query counting failing rows, not a black-box check, which means you can inspect exactly which rows failed.

Inspecting Which Rows Actually Failed

Every test compiles to a query you can run directly. Finding the failing rows for debugging is one command away:

dbt show --select unique_stg_orders_order_id

Or manually, by looking at the compiled test SQL under target/compiled/ and running the underlying query yourself — tests in dbt are transparent SQL, not opaque validations, which makes debugging a failure a normal SQL investigation rather than guesswork.

Test Severity: warn vs error

Not every failed test should block a deployment. dbt supports a severity config that distinguishes “something’s off, worth investigating” from “stop the pipeline.”

columns:
  - name: discount_pct
    tests:
      - not_null:
          config:
            severity: warn

A warn-severity test failure is logged and shown clearly in output, but doesn’t fail the overall dbt test command — useful for known, tolerated data quality issues you’re actively tracking but haven’t fully fixed yet. Combined with error_if and warn_if thresholds, you can even set a tolerance:

columns:
  - name: discount_pct
    tests:
      - not_null:
          config:
            severity: warn
            warn_if: ">10"
            error_if: ">100"

This only warns if more than 10 rows fail, and only hard-fails if more than 100 do — a realistic middle ground between “zero tolerance” and “no monitoring at all.”

Tests as Part of dbt build

Running dbt run followed by dbt test as two separate steps means bad data can briefly exist in a downstream table before the test catches it. dbt build, covered in dbt build, interleaves building and testing per model in dependency order, catching a failure before it propagates further down the DAG.

dbt build --select stg_orders+

If stg_orders fails its tests, dbt build stops before building anything downstream of it — a meaningfully safer default than running the full DAG and only discovering the problem afterward.

Tests on Sources, Not Just Models

Tests aren’t limited to models you build — they can (and should) be attached to sources too, validating the raw data itself before your transformations even touch it. This is explored fully in Source Testing, and it’s often more valuable than testing your own models, since it catches upstream problems at the earliest possible point.

Common Mistakes

Only testing final mart models. A test failure three layers downstream is much harder to trace back to its root cause than the same test attached at the staging layer, where the bad data first entered the project.

Treating every failure as equally severe. Blanket error severity on every test means the team either fixes everything immediately or starts ignoring failed builds altogether. Calibrated warn/error thresholds keep the signal meaningful.

Not running tests in CI. A model that passes tests locally but was never tested against the CI/staging build can still ship a broken assumption to production undetected — testing needs to be part of the pipeline, not just a local habit.

Summary

dbt tests turn “I think this data is correct” into a verifiable, automated, re-runnable claim. A project without meaningful test coverage isn’t actually more reliable than one with it — it just hasn’t found its bugs yet.