Use for any question or task about testing dbt models, including adding tests to a model's YAML and bad data such as duplicate IDs that a passing dbt build missed: generic, singular and custom-macro tests, model assertions, reading failures from run_results.json and gating dbt build on the results in CI.
89%
Does it follow best practices?
Run evals on this skill
Adds up to 20 points to the overall score
Low
Low-risk findings worth noting
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).
dbt_project.yml or a models/ directory with .sql
files referencing {{ ref(...) }} / {{ source(...) }}.unique, not_null, accepted_values,
relationships) - the four built-in generics shipped with dbt
(data-tests).schema.ymlUse 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: idThe arguments: key under parameterized generics (accepted_values,
relationships) is the canonical syntax in current dbt (data-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) < 0Trap: 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.
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).
dbt build still lets throughCheck these before calling a model "tested". Each one passes silently by design, so a model can ship bad data with every test green.
| Gap | Why the tests pass | Fix |
|---|---|---|
NULLs in an accepted_values column | The test "validates that all of the non-null values in a column are present" in the list, so NULLs are never checked (data-test-props) | Add not_null beside it. Map unknown codes to a value the list rejects (else status_code), not to NULL, so a new code fails a test |
| NULL foreign keys | relationships "automatically excludes NULL values from validation" (data-test-props) | Add not_null; if NULL is legitimate (guest checkout), map it to an explicit key or flag and test that |
| A source that stopped loading | dbt build "does not include source freshness checks" (freshness) | Configure freshness on the source and run dbt source freshness as its own CI step before dbt build |
| Late rows in an incremental model | A filter on the event's own timestamp (where viewed_at > (select max(viewed_at) from {{ this }})) drops rows that arrive late; data tests on the target never see the missing rows | Filter on the load timestamp (or a lookback window deduplicated by unique_key) and prove it with an incremental-mode unit test (below) |
Source freshness, with both keys under config: (freshness moved to config
in v1.9, loaded_at_field in v1.10) (freshness-config):
sources:
- name: shop_raw
config:
loaded_at_field: _loaded_at
freshness:
warn_after: {count: 12, period: hour}
error_after: {count: 24, period: hour}
tables:
- name: ordersdbt source freshness # fails the job when error_after is exceeded
dbt buildUnit tests check transformation logic on fixed input rows (unit-tests).
For an incremental model, test both modes by overriding the is_incremental
macro, and give this as an input holding the rows already in the table.
The expect rows are "the result of the materialization (what will be
merged/inserted), not the resulting model itself" (unit-tests), so list
only the rows the model's SELECT returns in that run:
unit_tests:
- name: fct_page_views_includes_late_arrivals
model: fct_page_views
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_page_views')
rows:
- {view_id: 10, viewed_at: 2026-03-02 08:00:00, loaded_at: 2026-03-02 08:01:00}
- {view_id: 11, viewed_at: 2026-02-27 17:30:00, loaded_at: 2026-03-02 09:00:00} # arrived late
- input: this
rows:
- {view_id: 10, viewed_at: 2026-03-02 08:00:00, loaded_at: 2026-03-02 08:01:00}
expect:
rows: # only what this run inserts or merges
- {view_id: 11, viewed_at: 2026-02-27 17:30:00, loaded_at: 2026-03-02 09:00:00}Run unit tests in development and CI only. dbt Labs "strongly recommends only running unit tests in development or CI environments" because their inputs are static; exclude them from production builds (unit-tests):
dbt build --exclude-resource-type unit_test # production job
dbt test --select "test_type:unit" # CI or local, on demanddbt 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 subsetSeverity can be relaxed from error to warn on a per-test basis to
prevent a single test from cascading skips downstream (dbt-build).
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 -5Use 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/macros.md - custom generic test
patterns, severity overrides, conditional assertions.references/ci-integration.md - CI
workflow snippets, slim CI via state:modified+, artifact upload.dbt test command reference.dbt build command reference.run_results.json artifact schema.accepted_values and relationships treat NULLs.dbt build does not run it).freshness and loaded_at_field under config:.