dbt Generic Tests: unique, not_null, accepted_values, and relationships

dbt ships with four built-in generic tests, and between them they catch a surprisingly large fraction of the data quality bugs that actually happen in production — duplicated rows, unexpected nulls, values that don’t match an expected set, and foreign keys pointing at records that don’t exist. Understanding exactly what each one checks, and their configuration options, is worth more than most custom validation logic you’d write from scratch.

unique: No Duplicate Values

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique

This compiles to a query counting how many values appear more than once in that column, and fails if the count is above zero. It’s the single most common test in any dbt project, because a supposedly unique key that isn’t unique breaks every downstream join that assumes one row per key — silently multiplying totals through a fan-out join.

not_null: No Missing Values

columns:
  - name: customer_id
    tests:
      - not_null

Straightforward on the surface, but worth applying deliberately rather than everywhere by default. A not_null test on a genuinely optional column (a middle_name field, an end_date for a still-active subscription) will fail constantly on legitimate data — reserve it for columns that are supposed to always be populated, like primary and foreign keys.

accepted_values: Enum-Like Validation

columns:
  - name: status
    tests:
      - accepted_values:
          values: ['pending', 'shipped', 'delivered', 'cancelled', 'returned']

This catches a specific and common failure: an upstream system introduces a new status value (say, 'partially_refunded') that no downstream model or dashboard knows how to handle. Without this test, that new value flows silently through every aggregation, potentially miscategorized or dropped by a case when that doesn’t account for it. With the test, the very next dbt test run fails loudly, pointing directly at the new value.

accepted_values also supports a quote config for non-string values and a config.where clause to scope the check:

columns:
  - name: status
    tests:
      - accepted_values:
          values: ['pending', 'shipped', 'delivered']
          config:
            where: "order_date >= '2024-01-01'"

This restricts the check to recent data only — useful when historical data legitimately contains now-deprecated values you don’t want failing the test every time.

relationships: Referential Integrity

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

This is dbt’s version of a foreign key constraint — it checks that every customer_id in stg_orders actually exists in stg_customers. Most analytical warehouses don’t enforce foreign keys at the database level for performance reasons, which makes this test one of the only mechanisms actually catching orphaned records: orders referencing a customer that was deleted, or a sync issue that dropped rows from one table but not the other.

Combining Tests on the Same Column

Tests aren’t mutually exclusive — a primary key column commonly gets both unique and not_null together, since a null value and a duplicate value are both violations of what a primary key promises.

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

Testing Multiple Columns Together

A less commonly known feature: generic tests can apply to a combination of columns, not just one, using the model-level tests block instead of a column-level one.

models:
  - name: stg_order_line_items
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
            - line_item_number

This checks that the pair of order_id and line_item_number is unique together, even if neither column is unique on its own — the correct check for a table where uniqueness only makes sense at the composite-key level. Note this specific macro comes from dbt_utils, covered in dbt_utils Package, rather than being one of dbt’s four core built-ins.

Configuring Test Severity and Scope

Every generic test accepts a config block for severity thresholds (covered fully in dbt Tests) and a where clause to scope the check to a subset of rows:

columns:
  - name: discount_pct
    tests:
      - not_null:
          config:
            where: "order_date >= current_date - interval '90 days'"
            severity: warn

This only checks the last 90 days of data and treats a failure as a warning rather than a hard build stop — a realistic pattern when historical data has known gaps you’ve already decided not to fix retroactively.

Which Test to Reach for First

For a brand-new staging model, a reasonable default coverage that catches the most common real-world bugs:

Common Mistakes

Applying not_null to every column indiscriminately. This produces constant test noise on legitimately nullable fields and trains the team to ignore failures, which defeats the entire point of testing.

Forgetting relationships tests on foreign keys. Since warehouses rarely enforce this at the database level, skipping this test means orphaned foreign keys go completely undetected until a join silently drops rows or a report undercounts.

Not scoping accepted_values to avoid noise from legacy data. If historical rows contain now-retired status values, use the where config to scope the check to current data rather than disabling the test entirely.

Summary

These four generic tests, applied thoughtfully rather than blanket-applied everywhere, catch the majority of real data quality incidents before they reach a dashboard — for the cases they can’t catch, Custom Tests covers writing your own validation logic.