dbt Tests for Data Quality

A dbt test is a query that should return zero rows. That is the entire mental model — dbt test runs each one, and any row returned counts as a failure. The four built-in generic tests cover a surprising amount of ground before you need to write anything custom.

The four built-in generic tests

unique and not_null check the obvious things on a single column. accepted_values checks a column only ever contains values from a defined list — catches a typo'd status like "shpped" before it reaches a dashboard filter that only knows about "shipped". relationships is referential integrity: every value in a foreign key column must exist in the referenced table's primary key, the dbt equivalent of a foreign key constraint most analytical warehouses do not enforce natively.

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

Generic tests vs. singular tests

Generic tests are parameterised and reusable — write the logic once, apply it to any column via YAML, as above. Singular tests are one-off SQL files in the tests/ directory for a specific business rule that does not generalize: "revenue should never be negative," "a subscription's end date should never be before its start date." A singular test is just a .sql file containing a query that should return no rows.

-- tests/assert_positive_revenue.sql
-- fails dbt test if this returns any rows
select order_id, amount
from {{ ref('fct_orders') }}
where amount < 0

Writing your own generic test

When a singular test's logic turns out to be needed on multiple models, promote it to a custom generic test — defined with {% test %}, parameterised by model and column_name, and usable from YAML exactly like the built-ins.

{% test not_negative(model, column_name) %}
select *
from {{ model }}
where {{ column_name }} < 0
{% endtest %}

-- usage in schema.yml:
-- columns:
--   - name: amount
--     tests: [not_negative]

Controlling severity and wiring tests into CI

Tests default to error severity, which fails the dbt test run. Setting severity: warn on a test surfaces it without blocking a build — useful for a data quality rule you are still tuning thresholds on. In CI, running dbt build (which runs models and their tests together, stopping downstream models if an upstream test fails) against a temporary schema on every pull request is what turns tests from a "run manually and hope" step into an actual quality gate.

Wrapping up

Cover primary and foreign keys with `unique`, `not_null`, and `relationships` first — that alone catches most real incidents. Reach for a singular test when a business rule is genuinely one-off, and promote it to a custom generic test only once you are copying the same logic across models. Wire `dbt build` into CI so a broken test blocks a merge instead of surfacing after it is already in production.