dbt-testing

Authors and runs dbt data tests (generic, singular, and custom-macro), parses test failure output from run_results.json, and gates dbt build on test results. Use when the user works with a dbt project, asks about model assertions, or needs CI gates on a data pipeline.

dbt-testing

Overview

dbt distinguishes data tests (assertions on rows, e.g. unique / not_null) from unit tests (logic tests on transformations); this skill targets data tests. The YAML key was renamed from tests: to data_tests: to disambiguate; tests: remains a back-compat alias but you cannot have both on the same resource at the same time (data-tests).

When to use

The repo contains a dbt_project.yml or a models/ directory with .sql files referencing {{ ref(...) }} / {{ source(...) }}.
The user asks about generic tests (unique, not_null, accepted_values, relationships) - the four built-in generics shipped with dbt (data-tests).
The user mentions dbt build failures, schema drift, or wants assertions on data flowing through models.
A CI workflow needs a quality gate that fails the pipeline when a model ships bad data.

Authoring tests

Generic tests in `schema.yml`

Use data_tests: (canonical) - tests: is the legacy alias and cannot coexist with data_tests: on the same resource (data-tests).

version: 2
models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'shipped', 'completed', 'returned']
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('customers')
                field: id

The arguments: key under parameterized generics (accepted_values, relationships) is the canonical syntax in current dbt (data-tests).

Singular tests

Singular tests live as .sql files under tests/ (or a custom test-paths config). Each file is one select that returns failing rows - empty result set means pass (data-tests).

tests/assert_payment_total_non_negative.sql:

-- Returns rows where the aggregated payment total is negative;
-- empty result set = test passes.
select order_id, sum(amount) as total_amount
from {{ ref('fct_payments') }}
group by 1
having sum(amount) < 0

Trap: dbt requires you to omit trailing semicolons in singular test SQL - a ; at the end can cause the test to fail spuriously (data-tests). This is unlike most SQL editors which add the semicolon by default.

Custom generic tests via macros

Reusable assertions are defined as Jinja {⁠% test %} blocks that take model and column arguments and return a select of failing rows (data-tests):

{⁠% test column_value_in_range(model, column_name, min_value, max_value) %}
select *
from {{ model }}
where {{ column_name }} < {{ min_value }}
   or {{ column_name }} > {{ max_value }}
{⁠% endtest %}

Reference once defined, then call by name in data_tests:. See references/macros.md for the canonical macro shapes (range checks, conditional uniqueness, recency / freshness, external referential integrity).

Running

dbt test runs data tests defined on models, sources, snapshots, seeds, plus unit tests on SQL models (dbt-test).

# Run every data test in the project
dbt test --select test_type:data

# Run every test (data + unit) on a specific model
dbt test --select orders

# Run only singular tests
dbt test --select test_type:singular

# Run only generic tests
dbt test --select test_type:generic

# Combine selectors with comma (intersection)
dbt test --select "orders,test_type:data"

# Run tests for everything in a package
dbt test --select "my_package.*"

dbt build is the integrated command that runs models, tests, snapshots, seeds, and user-defined functions in DAG order. Critically, a failing upstream test causes downstream resources to skip - so dbt build is the right command for a pipeline that should not propagate bad data (dbt-build):

dbt build                                    # full project, DAG order
dbt build --select state:modified+           # changed nodes + descendants
dbt build --empty                            # zero-row dry run for validation
dbt build --select "test_type:unit"          # unit-test-only build subset

Severity can be relaxed from error to warn on a per-test basis to prevent a single test from cascading skips downstream (dbt-build).

Parsing failures

Both dbt test and dbt build produce run_results.json in the dbt artifacts directory. Each result entry is a node-level record with (run-results-json):

Field	Meaning
`unique_id`	Node identifier (e.g. `test.<project>.<test_name>`); maps to `manifest.json`.
`status`	dbt's interpretation of runtime success / failure / error.
`execution_time`	Total node execution duration (seconds).
`timing`	Per-phase breakdown (compile, execute).
`message`	Human-readable CLI message based on adapter output.
`failures`	Count of test failures (rows returned by the test SELECT).
`adapter_response`	Database-returned metadata (varies by warehouse adapter).
`compiled`	Whether the node compiled successfully.
`compiled_code`	The rendered SQL string (useful for debugging).
`relation_name`	Fully-qualified database object name.

A failing data test typically appears as status: "fail" with failures: <N> (rows returned). A test that errored before producing rows (e.g. a SQL compilation error) appears differently - handle both when parsing.

Quick triage with jq:

# List all failing test nodes
jq -r '.results[] | select(.status=="fail") | .unique_id + " (" + (.failures|tostring) + " failures)"' \
  target/run_results.json

# Surface the first 5 failure messages
jq -r '.results[] | select(.status=="fail") | .message' target/run_results.json | head -5

CI integration

Use dbt build (not dbt test alone) so the pipeline halts at the first failed assertion in the DAG and skips downstream nodes. Always upload target/run_results.json (and target/manifest.json) as an artifact so the failing-test list survives the run.

See references/ci-integration.md for ready-to-paste workflow snippets for GitHub Actions, GitLab CI, and Jenkins, plus the --store-failures and slim-CI patterns that scope tests to changed nodes.

References

references/macros.md - custom generic test patterns, severity overrides, conditional assertions.
references/ci-integration.md - CI workflow snippets, slim CI via state:modified+, artifact upload.
data-tests - canonical dbt docs page.
dbt-test - dbt test command reference.
dbt-build - dbt build command reference.
run-results-json - run_results.json artifact schema.

dbt CI integration

Patterns for running dbt build (preferred over standalone dbt test) in CI, with artifact upload and slim-CI scoping. All snippets use dbt build because of its DAG-aware skip-on-failure behavior - a failing upstream test causes downstream resources to skip, which is what you want in a quality gate (dbt-build).

GitHub Actions

name: dbt build

on:
  pull_request:
  push:
    branches: [main]

jobs:
  dbt-build:
    runs-on: ubuntu-latest
    env:
      DBT_PROFILES_DIR: .ci/dbt
    steps:
      - uses: actions/checkout@v5
        with:
          fetch-depth: 0          # needed for state:modified+ slim CI

      - uses: actions/setup-python@v5
        with:
          python-version: '3.12'

      - name: Install dbt + adapter
        run: pip install 'dbt-core>=1.8' 'dbt-postgres'

      - name: dbt deps
        run: dbt deps

      # Slim CI: only build/test changed nodes + their descendants.
      # Requires manifest.json from a previous run (e.g. main) at .dbt-state/.
      - name: dbt build (slim)
        run: |
          if [ -f .dbt-state/manifest.json ]; then
            dbt build --select state:modified+ --state .dbt-state/
          else
            dbt build
          fi

      - name: Upload run_results.json
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: dbt-run-results
          path: |
            target/run_results.json
            target/manifest.json
          retention-days: 14

The if: always() on the artifact upload is important - without it, run_results.json is lost on failure, which is exactly when you need it for triage.

GitLab CI

dbt-build:
  image: python:3.12
  variables:
    DBT_PROFILES_DIR: $CI_PROJECT_DIR/.ci/dbt
  before_script:
    - pip install 'dbt-core>=1.8' 'dbt-postgres'
    - dbt deps
  script:
    - dbt build
  artifacts:
    when: always
    paths:
      - target/run_results.json
      - target/manifest.json
    expire_in: 14 days

Jenkins (declarative)

pipeline {
  agent any
  environment {
    DBT_PROFILES_DIR = "${WORKSPACE}/.ci/dbt"
  }
  stages {
    stage('Install') {
      steps {
        sh 'pip install --user "dbt-core>=1.8" "dbt-postgres"'
        sh 'dbt deps'
      }
    }
    stage('Build') {
      steps {
        sh 'dbt build'
      }
    }
  }
  post {
    always {
      archiveArtifacts artifacts: 'target/run_results.json,target/manifest.json',
                       allowEmptyArchive: true
    }
  }
}

Slim CI: scoping to changed nodes

The state:modified+ selector restricts a build to nodes that changed relative to a baseline manifest.json (downloaded from a previous main run) plus their descendants (dbt-build):

# 1. On main: produce the baseline manifest and store it (e.g. S3, artifact)
dbt build
mkdir -p .dbt-state
cp target/manifest.json .dbt-state/

# 2. On PR: download .dbt-state/manifest.json, then:
dbt build --select state:modified+ --state .dbt-state/

This is the canonical pattern for a fast PR check that doesn't rebuild the entire warehouse.

Empty / dry-run validation

Use --empty (dbt 1.8+) for a zero-row build that compiles every node and checks DAG integrity without expensive data reads (dbt-build):

dbt build --empty

Pair this with the slim-CI pattern when you want a fast dependency-graph check before kicking off the data build.

Storing failures for triage

Add --store-failures to materialize the failing rows of each test into a warehouse audit table:

dbt build --store-failures

The audit tables let you triage failures with the actual offending rows in hand, instead of only the count from run_results.json.

Parsing run_results.json in CI

A small post-build step that surfaces failing tests in the run summary:

jq -r '
  .results[]
  | select(.status == "fail")
  | "FAIL: \(.unique_id) — \(.failures) failing rows — \(.message)"
' target/run_results.json

Pipe this to tee and to a job summary file ($GITHUB_STEP_SUMMARY on GitHub Actions) for a clickable failure report on the run page.

dbt custom test macros

Patterns for {⁠% test %} blocks beyond the four built-in generics. Each test is a Jinja macro that takes a model (and optional column arguments) and returns a select of failing rows - empty result set means pass (per the canonical data-tests doc).

All examples use the canonical data_tests: YAML key with arguments: for parameterized generics (data-tests).

Range check

Asserts a numeric column stays within [min_value, max_value]. Inclusive bounds.

{⁠% test column_value_in_range(model, column_name, min_value, max_value) %}
select *
from {{ model }}
where {{ column_name }} < {{ min_value }}
   or {{ column_name }} > {{ max_value }}
{⁠% endtest %}

Usage:

- name: discount_percent
  data_tests:
    - column_value_in_range:
        arguments:
          min_value: 0
          max_value: 100

Conditional uniqueness

Built-in unique enforces uniqueness across the entire column. When you need uniqueness only within a subset (e.g. unique email per active account), parameterize the predicate.

{⁠% test conditional_unique(model, column_name, where_clause) %}
select {{ column_name }}, count(*) as n
from {{ model }}
where {{ where_clause }}
group by {{ column_name }}
having count(*) > 1
{⁠% endtest %}

Usage:

- name: email
  data_tests:
    - conditional_unique:
        arguments:
          where_clause: "status = 'active'"

Recency / freshness

For sources, dbt has a built-in freshness block (different mechanism). For models, a custom test pattern is to assert the most recent row is within a target window.

{⁠% test row_recency(model, column_name, max_age_hours) %}
{# fails if max(column_name) is older than max_age_hours #}
select max({{ column_name }}) as latest
from {{ model }}
having max({{ column_name }}) < dateadd(hour, -{{ max_age_hours }}, current_timestamp)
{⁠% endtest %}

dateadd syntax varies by warehouse adapter - adjust per Snowflake / BigQuery / Postgres / Redshift dialect. Test the raw SQL in your warehouse console first.

External referential integrity

The built-in relationships test asserts a foreign key against another dbt model via ref(). For an external warehouse table not modeled by dbt, use a parameterized macro:

{⁠% test external_relationship(model, column_name, to_relation, to_column) %}
select {{ column_name }} as orphan
from {{ model }}
where {{ column_name }} is not null
  and {{ column_name }} not in (select {{ to_column }} from {{ to_relation }})
{⁠% endtest %}

Usage:

- name: external_account_id
  data_tests:
    - external_relationship:
        arguments:
          to_relation: 'analytics.dim_account_external'
          to_column: 'account_id'

Severity and warning thresholds

Any data test (built-in or custom) accepts a config: block that lets you treat the test as a warning instead of a hard failure, or set a fail/warn threshold based on the row count returned:

- name: order_id
  data_tests:
    - unique:
        config:
          severity: warn          # warn instead of error
          warn_if: ">10"          # warn if more than 10 dupes
          error_if: ">100"        # hard fail if more than 100 dupes

This is the canonical mechanism to prevent a single test from blocking downstream models in dbt build - relaxing severity to warn lets the DAG keep flowing while still surfacing the assertion in run_results.json (dbt-build).

Storing failures

Add --store-failures to dbt test (or dbt build) to persist the failing rows to a warehouse table for triage, instead of only emitting counts. Once enabled, each failing test materializes a small audit table under the configured dbt_test__audit schema.

dbt test --select state:modified+ --store-failures

Use this when the failure count alone is not enough to debug - the audit table contains the actual rows the test SELECT returned.

dbt-testing

dbt-testing

Overview

When to use

Authoring tests

Generic tests in schema.yml

Singular tests

Custom generic tests via macros

Running

Parsing failures

CI integration

References

dbt CI integration

dbt CI integration

GitHub Actions

GitLab CI

Jenkins (declarative)

Slim CI: scoping to changed nodes

Empty / dry-run validation

Storing failures for triage

Parsing run_results.json in CI

dbt custom test macros

dbt custom test macros

Range check

Conditional uniqueness

Recency / freshness

External referential integrity

Severity and warning thresholds

Storing failures

Generic tests in `schema.yml`