dbt generic tests: a complete reference

How generic tests work

A dbt test is a SQL query that must return zero rows. Rows returned are failures. That's the entire model, and understanding it explains everything else — why tests are fast (the warehouse does the work), why they can't check things that aren't in a table, and why a custom test is just a SELECT that finds bad rows.

A generic test is a parameterised one you attach to a column or model in YAML. A singular test is a one-off .sql file. Generic tests are defined as macros that take at least model and, for column-level tests, column_name.

models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - not_null
          - unique

Run with dbt test, or as part of dbt build, which interleaves tests with models so a failing test stops dependants from being built. dbt build is almost always what you want in production — dbt run followed by dbt test will happily populate downstream tables from data that's about to fail its own tests.

not_null

Asserts no NULLs in the column.

columns:
  - name: order_id
    tests:
      - not_null

Compiles to roughly:

select order_id
from {{ model }}
where order_id is null

When it's wrong: on a genuinely optional field. This is the most common cause of a permanently-red test suite. "Populated in every row I sampled" is not the same claim as "the business guarantees this exists." A nullablediscount_code is correct behaviour, not a defect.

Note on empty strings: not_null does not catch ''. If your loader converts missing values to empty strings — many CSV paths do — the test passes on data that is functionally missing. Add an expression_is_true check, or normalise at load time.

unique

Asserts no duplicate values among non-null values.

columns:
  - name: order_id
    tests:
      - unique

When it's wrong: on a raw landing table fed by an at-least-once source. Webhooks, Kafka, and most CDC pipelines deliver duplicates by design. Uniqueness belongs on the deduplicated view, not the append-only log.

For composite keys, don't test each column separately — that asserts something much stronger and wrong. Use dbt_utils.unique_combination_of_columns (below).

accepted_values

Asserts every value is in a given set.

columns:
  - name: status
    tests:
      - accepted_values:
          values: ['paid', 'pending', 'refunded', 'cancelled']

For non-string columns, set quote: false or the comparison will fail:

- accepted_values:
    values: [1, 2, 3]
    quote: false

When it's right: when your SQL branches on the value. A CASE WHEN status IN (...) ELSE ... silently misfiles any new category, and this test is the tripwire that tells you the category appeared.

When it's wrong: on a dimension that grows normally — country codes, product categories, plan names. The test will fail on ordinary business growth, and a suite that fails for non-reasons is a suite people stop reading.

relationships

Asserts every value in this column exists in another model's column. This is referential integrity, and it is the highest-value dbt test that most projects don't have.

columns:
  - name: customer_id
    tests:
      - relationships:
          to: ref('dim_customers')
          field: customer_id

Why it matters more than the others: a fact table and a dimension table can each be internally perfect while the join between them silently drops rows. Late-arriving dimensions, a changed upstream filter, a customer type that stopped loading — none of these violate not_null or unique on either side. Only the relationship catches it, and the symptom is "revenue is down 12%" three weeks later.

On very large tables this is the most expensive built-in, since it's an anti-join. If cost is a concern, run it on a schedule rather than every build, or restrict it with where.

Config: severity, where, limit

Every test takes a config block, and using it well is the difference between a suite people act on and one they mute.

- not_null:
    config:
      severity: warn          # error (default) | warn
      error_if: ">100"        # escalate to error past a threshold
      warn_if: ">0"
      where: "created_at >= dateadd('day', -7, current_date)"
      limit: 100              # cap rows stored for debugging

severity — warn surfaces the failure without failing the build. Use it for assertions you're still validating, and for "worth a look" checks like outlier ranges. The honest use of warn is the main thing that keeps a suite trustworthy.

error_if / warn_if — threshold expressions on the failing row count. severity: warn with error_if: ">1000" means "a few is noise, a thousand is an incident." This is how you express tolerance without turning the test off.

where — scopes the test. Essential on large tables, and essential when historical data has known defects you've decided not to backfill. Better than deleting the test.

Set defaults for a whole folder in dbt_project.yml:

data_tests:
  my_project:
    staging:
      +severity: warn
    marts:
      +severity: error

That pattern — warn in staging, error in marts — matches how most teams actually think about their pipeline, and it's underused.

dbt_utils tests worth knowing

Add the package to packages.yml and run dbt deps:

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

expression_is_true

The workhorse. Asserts an arbitrary SQL expression holds for every row, and it's the one that catches actual logic bugs rather than structural ones.

# model-level: cross-column invariant
tests:
  - dbt_utils.expression_is_true:
      expression: "total_cents = subtotal_cents + tax_cents"

# column-level: the column is implicit
columns:
  - name: price_cents
    tests:
      - dbt_utils.expression_is_true:
          expression: ">= 0"

Cross-column invariants are heavily under-used. "The total equals the sum of the parts" is the kind of thing that's obviously true until a rounding change or a currency conversion makes it not.

unique_combination_of_columns

tests:
  - dbt_utils.unique_combination_of_columns:
      combination_of_columns:
        - order_id
        - line_item_seq

The correct test for a composite key. Testing unique on each column separately asserts something far stronger and wrong — it would fail on the very first order with two line items.

accepted_range

columns:
  - name: quantity
    tests:
      - dbt_utils.accepted_range:
          min_value: 1
          inclusive: true
          config:
            severity: warn

Assert the bound you know, not the one you observed. "Quantity is at least 1" is a business rule. "Quantity is between 1 and 12" is a description of last month's data, and it will fail on the first bulk order.

not_null_proportion

columns:
  - name: shipping_address
    tests:
      - dbt_utils.not_null_proportion:
          at_least: 0.75

For fields that are legitimately partial but shouldn't collapse. Catches the case where a field goes from 80% populated to 2% because an upstream integration broke — invisible to not_null, which was never going to pass anyway.

equal_rowcount / fewer_rows_than

tests:
  - dbt_utils.equal_rowcount:
      compare_model: ref('stg_orders')

Catches rows silently dropped by a join or filter in a transformation — one of the most common and least detected model bugs.

Also useful

dbt_utils.at_least_one (the column isn't entirely NULL), dbt_utils.not_constant (it isn't a single repeated value), dbt_utils.cardinality_equality (two columns share a value set), dbt_utils.sequential_values (no gaps in a sequence), dbt_utils.not_accepted_values (a denylist).

Writing a custom generic test

When the same non-trivial assertion appears a third time, make it generic. Create tests/generic/assert_positive.sql:

{% test assert_positive(model, column_name) %}

select {{ column_name }}
from {{ model }}
where {{ column_name }} <= 0

{% endtest %}

Then use it like any built-in:

columns:
  - name: price_cents
    tests:
      - assert_positive

Custom tests take arbitrary extra arguments:

{% test recent_enough(model, column_name, max_age_days=2) %}

select {{ column_name }}
from {{ model }}
where {{ column_name }} < {{ dbt.dateadd('day', -max_age_days, dbt.current_timestamp()) }}

{% endtest %}

- recent_enough:
    max_age_days: 7

Two rules that keep custom tests maintainable: return the offending rows (not a count) so failures are debuggable, and use dbt. cross-database macros like dbt.dateadd rather than warehouse-specific syntax if the project might ever move.

Singular tests

A plain .sql file in tests/ that selects failing rows. No YAML, no parameters — use it when the assertion is genuinely one-of-a-kind.

-- tests/assert_refunds_not_exceed_orders.sql
select
    o.order_id,
    o.total_cents,
    sum(r.amount_cents) as refunded_cents
from {{ ref('fct_orders') }} o
join {{ ref('fct_refunds') }} r using (order_id)
group by 1, 2
having sum(r.amount_cents) > o.total_cents

If you write the same singular test twice with different table names, that's the signal to convert it into a generic one.

Generic tests vs unit tests

dbt v1.8 added unit tests, and they answer a different question. A data test asserts things about real data currently in the warehouse. A unit test asserts that your SQL logic transforms a fixed set of inputs into an expected output, using fixtures.

unit_tests:
  - name: test_order_status_mapping
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - { order_id: 1, raw_status: 'PAID' }
    expect:
      rows:
          - { order_id: 1, status: 'paid' }

Use unit tests for gnarly CASE logic, window functions, and edge cases you can't reliably find in production data. Use data tests for everything about the data itself. They're complements — unit tests catch bugs you wrote, data tests catch bad data you were sent.

Common anti-patterns

Testing every column with everything. A suite of 400 tests where 30 are meaningful trains everyone to skim past failures. Coverage is not the metric; trust is.

Range tests from observed data. The most common source of self-inflicted alert fatigue. Assert the business rule or use severity: warn.

Uniqueness on landing tables. At-least-once delivery makes duplicates normal. Test after dedup.

Testing each column of a composite key separately. Use unique_combination_of_columns.

Muting instead of scoping. When a test fails on known-bad history, add a where clause rather than deleting it. The assertion is still true going forward and still worth enforcing.

Using dbt run in production. Without dbt build, downstream models are populated from data that hasn't passed its tests yet.

Getting a first draft

Knowing the syntax isn't the bottleneck — deciding which of your forty columns deserves which test is. That's an evidence question: which columns are genuinely never null, which are real enums rather than growing dimensions, which have inconsistent types, and how much data each conclusion rests on.

Treivu profiles a sample in your browser and proposes the tests the evidence supports, with the evidence attached, and exports a schema.yml you can paste in. Apply the same filter to every generated test before committing it: is this a constraint, or just something that was true in the sample?