Dbt Testing Patterns

Production engineering guide for dbt testing patterns covering patterns, implementation strategies, and operational best practices.

This page describes specific patterns for testing dbt models and their dependencies using dbt's built-in testing capabilities and standard SQL practices. These patterns help ensure model correctness, data quality, and maintainability in dbt projects.

Testing for Data Quality Issues

Null Value Validation

Use not_null tests to ensure columns do not contain null values where they should:

-- In models/my_model.sql
select
  id,
  name,
  email
from raw_data.users

-- In tests/my_model_not_null.yml
version: 2
tests:
  - model: my_model
    name: test_not_null_email
    description: "Email field must not be null"
    config:
      severity: error
    columns:
      - name: email
        tests:
          - not_null

-- Run with:
dbt test --select my_model

The not_null test fails silently when run on a model that doesn't exist, producing no output or error message. The test does not validate against the actual model definition; it tests only the column's nullability in the materialized table.

Uniqueness Validation

Test for unique values using unique tests:

-- In models/my_model.sql
select
  id,
  email
from raw_data.users

-- In tests/my_model_unique.yml
version: 2
tests:
  - model: my_model
    name: test_unique_id
    description: "ID field must be unique"
    config:
      severity: error
    columns:
      - name: id
        tests:
          - unique

-- Run with:
dbt test --select my_model

When a unique test fails, it reports the count of duplicate values but does not list which specific rows are duplicated. This makes debugging harder.

Range Validation

Use custom SQL tests to validate numeric ranges:

-- In tests/valid_age_range.sql
select *
from {{ ref('my_model') }}
where age < 0 or age > 150

-- Run with:
dbt test --select valid_age_range

The valid_age_range test returns a row count but does not show which specific values are out of range. If the test runs on a model that has been filtered by a macro, it will test against the filtered result set, not the full source.

Referential Integrity

Test foreign key relationships:

-- In tests/valid_foreign_key.sql
select *
from {{ ref('orders') }} as o
left join {{ ref('customers') }} as c on o.customer_id = c.id
where o.customer_id is not null and c.id is null

-- Run with:
dbt test --select valid_foreign_key

This pattern works across any SQL-based dbt project. When the foreign key relationship changes, tests must be updated manually to reflect new constraints. The test does not validate referential integrity for all models in a project automatically.

Testing Data Consistency

Data Type Validation

Use accepted_values to ensure column values match expected categories:

-- In models/my_model.sql
select
  id,
  status
from raw_data.orders

-- In tests/my_model_accepted_values.yml
version: 2
tests:
  - model: my_model
    name: test_status_accepted_values
    description: "Status must be one of approved, pending, rejected"
    config:
      severity: error
    columns:
      - name: status
        tests:
          - accepted_values:
              values: ['approved', 'pending', 'rejected']

-- Run with:
dbt test --select my_model

The accepted_values test does not validate that all values in the column are in the list; it tests only whether any value is not in the list. It produces no output when all values are accepted.

String Pattern Validation

Test for expected string formats using regexp:

-- In tests/valid_email_format.sql
select *
from {{ ref('my_model') }}
where email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

-- Run with:
dbt test --select valid_email_format

The regexp test does not validate against the actual regular expression syntax used in the database. If the regex pattern is invalid, the test will fail but show no specific error about the pattern.

Row Count Validation

Validate that a model contains expected row counts:

-- In tests/expected_row_count.sql
select count(*) as row_count
from {{ ref('my_model') }}
having count(*) < 1000

-- Run with:
dbt test --select expected_row_count

This pattern requires knowing the exact expected row count at test creation time. If the source data changes, tests must be updated manually.

Testing Model Dependencies

Dependency Chain Validation

Ensure that model dependencies are correctly built before testing:

# In dbt_project.yml
model-paths: ["models"]
test-paths: ["tests"]
config-version: 2

# In tests/dependency_validation.yml
version: 2
tests:
  - model: my_model
    name: test_dependency_chain
    description: "Model must be built after its dependencies"
    config:
      severity: error
    depends_on:
      - ref('users')
      - ref('orders')

The depends_on clause does not validate that models are in the correct build order. It only documents the relationship for test reporting.

Cross-Model Validation

Test relationships between different models:

-- In tests/consistency_check.sql
select
  m1.id,
  m1.value,
  m2.related_value
from {{ ref('model_one') }} as m1
join {{ ref('model_two') }} as m2 on m1.id = m2.id
where m1.value != m2.related_value

-- Run with:
dbt test --select consistency_check

This pattern does not automatically detect when dependencies change. If model_one changes, the consistency test must be manually updated to reflect new expected relationships.

Testing Data Freshness

Timestamp Validation

Test that data has recent updates:

-- In tests/recent_data.sql
select *
from {{ ref('my_model') }}
where created_at < current_timestamp - interval '1 day'

-- Run with:
dbt test --select recent_data

The timestamp test does not validate against the actual source table's last modified time. It only tests against when the model was materialized.

Data Age Validation

Check that data is within acceptable age limits:

-- In tests/data_age.sql
select *
from {{ ref('my_model') }}
where updated_at < current_timestamp - interval '7 days'

-- Run with:
dbt test --select data_age

This pattern requires that models have an updated_at timestamp. If a model does not contain this field, the test fails with no clear indication of what's wrong.

Testing for Data Drift

Schema Change Validation

Detect when model schemas change unexpectedly:

-- In tests/schema_change.sql
select count(*) as schema_changes
from (
  select *
  from {{ ref('my_model') }}
  where 1=0  -- Empty result set
) as empty_table

-- Run with:
dbt test --select schema_change

This pattern requires that the schema validation test be updated when column definitions change. The test does not automatically detect schema changes.

Data Volume Change Detection

Monitor for sudden increases or decreases in data volume:

-- In tests/volume_change.sql
select count(*) as current_count
from {{ ref('my_model') }}
having count(*) > (select count(*) * 1.5 from {{ ref('my_model') }} where created_at < current_timestamp - interval '1 week')

-- Run with:
dbt test --select volume_change

The volume change test does not account for expected seasonal variations. It treats any change as a potential issue, even when it's normal.

Testing Patterns That Silently Fail

Test Configuration Errors

Tests configured with invalid severity levels silently default to warn:

# Invalid severity level
version: 2
tests:
  - model: my_model
    name: test_invalid_severity
    config:
      severity: invalid_value  # This is ignored

The configuration parser ignores unknown severity values and defaults to warning-level tests. No error message is shown.

Test Selection Issues

Using --select with non-existent model names silently skips tests:

# This command will not report errors for missing models
dbt test --select non_existent_model

The command completes without error but does not run any tests for the specified model name.

Test Failure Reporting

Test failures do not always show the actual SQL that failed:

# Example output when test fails
12:34:56  Running test my_test
12:34:57  Test my_test failed

The output does not include the specific SQL query or the row data that caused the failure.

Model Validation Issues

Tests do not validate that referenced models exist in the project:

# This references a model that may not exist
version: 2
tests:
  - model: non_existent_model
    name: test_nonexistent

If non_existent_model is not defined, the test runs without error but tests nothing meaningful. The test output does not indicate this mismatch.

Data Type Mismatch Handling

Tests that compare data types do not fail when types don't match:

-- This test may pass even with type mismatches
select *
from {{ ref('my_model') }}
where id = 'string_value'  -- Comparing integer to string

The query executes successfully but may produce unexpected results due to implicit type conversion.

Test Coverage Gaps

Tests that validate only a subset of data do not report when the subset is too small:

-- This test validates only the first 100 rows
select *
from (
  select *
  from {{ ref('my_model') }}
  limit 100
) as sample
where some_condition = false

The test passes even if the validation condition fails in the full dataset, but this is not reported in the output.

This page was rewritten on 16 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.