dbt Generic Tests

Generic tests are the most common way to add data quality checks in dbt. You declare them in YAML files alongside your model definitions. dbt generates the SQL automatically and runs it when you execute dbt test. This topic covers the four built-in generic tests in depth and shows you how to apply them across different scenarios.

How Generic Tests Work

When you add a generic test in YAML, dbt looks up a corresponding SQL template and fills in the column name and any parameters. The test runs as a SQL query. If the query returns zero rows, the test passes. If it returns any rows, those rows represent failures.

You write (YAML):
  - name: order_id
    tests:
      - unique

dbt generates (SQL):
  select order_id
  from dbt_dev.stg_orders
  where order_id is not null
  group by order_id
  having count(*) > 1

The generated SQL returns only duplicate order IDs. If none exist, zero rows come back and the test passes.

not_null

The not_null test verifies that a column contains no NULL values. Apply it to any column that must always have a value, such as primary keys, foreign keys, and mandatory business fields.

- name: stg_orders
  columns:
    - name: order_id
      tests:
        - not_null
    - name: customer_id
      tests:
        - not_null
    - name: order_date
      tests:
        - not_null

Generated SQL for not_null

select count(*) as failures
from dbt_dev.stg_orders
where order_id is null

unique

The unique test verifies that every value in a column appears only once. Apply it to primary key columns. Duplicates in a primary key column cause downstream joins to produce extra rows, which inflates metrics silently.

- name: stg_customers
  columns:
    - name: customer_id
      tests:
        - not_null
        - unique

When Primary Keys Can Duplicate

Scenario: stg_customers is built from two source systems merged with UNION ALL
Problem:  Customer 42 appears in both systems, so two rows with customer_id=42 exist
Result:   unique test fails, alerting you to the duplicate before it causes join inflation

accepted_values

The accepted_values test verifies that a column only contains values from a predefined list. Apply it to status columns, category columns, and any field with a finite set of valid options.

- name: stg_orders
  columns:
    - name: status
      tests:
        - accepted_values:
            values: ['pending', 'processing', 'completed', 'cancelled', 'refunded']

- name: stg_products
  columns:
    - name: category
      tests:
        - accepted_values:
            values: ['electronics', 'clothing', 'food', 'books', 'home']

Handling Case Sensitivity

By default, the accepted_values test is case-sensitive. If your data contains both Completed and completed, both must appear in the values list, or you must clean the column in your staging model using lower(status) before testing.

-- Cleaning in the model before testing:
select lower(status) as status from {{ source('ecommerce', 'orders') }}

-- Then test expects only lowercase:
- accepted_values:
    values: ['pending', 'completed', 'cancelled']

relationships

The relationships test verifies referential integrity between two tables. Every value in the column being tested must exist in the referenced column of another table.

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

    - name: product_id
      tests:
        - relationships:
            to: ref('stg_products')
            field: product_id

Why Referential Integrity Fails

Common causes of relationships test failures:
  1. Customer deleted from CRM but old orders still reference their ID
  2. Two systems synced at different times (orders arrived before customers)
  3. Test/sandbox records in production data
  4. Data type mismatch (integer in orders, string in customers)

Applying Multiple Tests to One Column

- name: order_id
  tests:
    - not_null          ← must have a value
    - unique            ← must not repeat

- name: customer_id
  tests:
    - not_null          ← every order must have a customer
    - relationships:    ← that customer must exist
        to: ref('stg_customers')
        field: customer_id

Applying Tests at Model Level

Some tests apply to combinations of columns rather than single columns. Define these at the model level using the tests key directly under the model name:

- name: stg_order_items
  tests:
    - unique_combination_of_columns:        ← from dbt_utils package
        combination_of_columns:
          - order_id
          - product_id
  columns:
    - name: order_id
      tests:
        - not_null

Configuring Test Behavior

Severity

- name: email
  tests:
    - not_null:
        severity: warn     ← pipeline continues even if this fails

Error if Count Exceeds Threshold

- name: status
  tests:
    - accepted_values:
        values: ['pending', 'completed', 'cancelled']
        config:
          error_if: ">=10"   ← pass if fewer than 10 failures
          warn_if: ">=1"    ← warn if any failures but fewer than 10

Running Specific Tests

# Run all tests on one model
dbt test --select stg_orders

# Run only not_null tests across entire project
dbt test --select test_type:not_null

# Run only the unique test on a specific column
dbt test --select "stg_orders,test_type:unique"

Practical Testing Strategy

Column Type        Recommended Tests
-----------        -----------------
Primary key        not_null + unique
Foreign key        not_null + relationships
Status field       accepted_values
Required field     not_null
Calculated metric  Custom singular test (Topic 16)

Apply not_null and unique to every primary key column in every model. This one habit catches the majority of data quality issues in most projects.