dbt tests are SQL queries that verify data quality assertions about your models — uniqueness, nullability, referential integrity, and custom business logic. Running tests as part of every dbt job transforms data quality from a reactive fire-fighting activity into a proactive, automated guarantee built into the transformation pipeline itself.
dbt tests are SQL assertions that verify data quality properties of your models. Each test is a query that, when it returns rows, indicates a failure condition. dbt runs tests as part of the standard dbt test command, and test failures can be configured to block downstream model execution or CI pipeline progression. The result is data quality enforcement that is built into the transformation pipeline rather than bolted on separately.
The Four Core Generic Tests
dbt ships with four generic tests that cover the most common data quality assertions:
**not_null** — asserts that a column contains no null values. Applied to primary keys, required foreign keys, and any field that downstream consumers depend on being populated.
**unique** — asserts that a column contains no duplicate values. Applied to primary keys and any field used as a join key.
**accepted_values** — asserts that all values in a column belong to a defined list. Applied to status columns, type codes, and any categorical field with a known valid set of values.
**relationships** — asserts referential integrity: all values in a column exist in a specified column of another model. Applied to foreign keys.
These four tests are configured in YAML schema files alongside model definitions:
models:
- name: orders
columns:
- name: order_id
tests:
- not_null
- unique
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped', 'delivered', 'cancelled']
- name: customer_id
tests:
- not_null
- relationships:
to: ref('customers')
field: customer_id
Singular Tests
Singular tests are SQL files in the tests/ directory that contain a query which should return zero rows for the test to pass. They are used for business-logic assertions that the generic test framework cannot express:
A singular test checking that revenue never exceeds $1M per order:
SELECT
order_id,
revenue
FROM {{ ref('orders') }}
WHERE revenue > 1000000
If any order has revenue above the threshold, the test returns those rows and fails. The query describes the failure condition rather than the success condition — a counterintuitive but consistent convention across all dbt tests.
Singular tests are the right tool for assertions about relationships between columns, cross-model consistency checks, and any condition that requires a custom query.
Custom Generic Tests
Custom generic tests extend the built-in framework with reusable assertions that apply to multiple models. A custom generic test is a SQL template (a Jinja macro) that generates a test query for a given column and configuration:
A custom generic test checking that a column value is within a specified range:
{% test in_range(model, column_name, min_val, max_val) %}
SELECT {{ column_name }}
FROM {{ model }}
WHERE {{ column_name }} < {{ min_val }}
OR {{ column_name }} > {{ max_val }}
{% endtest %}
Applied in YAML:
- name: discount_pct
tests:
- in_range:
min_val: 0
max_val: 1
The dbt-utils and dbt-expectations packages provide extensive libraries of pre-built custom generic tests — value ranges, regex patterns, recency checks, statistical assertions — that eliminate the need to write many common test patterns from scratch.
Test Severity Levels
Not all test failures are equal. dbt supports two severity levels:
**error** (default) — a failed test causes the dbt run to report failure and blocks downstream models from running if --fail-fast is set.
**warn** — a failed test reports a warning but does not block execution. The pipeline continues; the failure is logged for investigation.
Configure severity on individual tests:
- name: order_id
tests:
- not_null:
severity: error
- unique:
severity: warn
Use error severity for tests that indicate data corruption — duplicate primary keys, null required fields, invalid foreign key references. Use warn severity for soft assertions that indicate data quality degradation but do not prevent downstream use — unusual value distributions, anomalous row counts.
Storing Test Failures
dbt can store the rows that caused a test failure in a separate table using the store_failures_as configuration. This makes investigation of test failures much faster — instead of re-running the test query manually, the failure rows are available in the database for immediate inspection.
Configure at the project level:
tests:
+store_failures_as: table
Or on individual tests:
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped']
store_failures_as: view
Storing failures is especially valuable for relationships tests on large tables — seeing the specific orphaned foreign key values helps quickly identify whether the failure is a data issue or a model definition issue.
Test Coverage Strategy
A common mistake in dbt testing is applying tests too uniformly — testing every column with not_null and unique regardless of whether those assertions are meaningful. This produces a large number of tests with high false-positive rates (expected nulls causing failures) and obscures the tests that actually matter.
A more effective strategy:
**Primary keys** — always test not_null and unique. These are the highest-value tests because duplicate or missing primary keys corrupt downstream joins silently.
**Foreign keys** — test relationships for all foreign key columns. Missing relationship failures indicate data pipeline gaps that produce incorrect analytics.
**Status and type columns** — test accepted_values for any column whose valid values are known. This catches upstream data changes before they propagate to downstream analytics.
**Date columns** — test recency (using dbt-utils' recency test or a custom singular test) on key date columns in fact tables. A fact table whose maximum date is 3 days ago when it should be updated daily indicates a pipeline failure.
**Measure columns** — selectively test not_null and range assertions for critical measures. Revenue, cost, and quantity columns should not be null in completed transaction records; ranges can catch order-of-magnitude data errors.
Testing in CI/CD
Tests are most valuable when they run automatically on every code change. In a dbt CI pipeline:
1. A PR is opened with changes to one or more models.
2. The CI job runs dbt build --select "state:modified+" — building only the modified models and their downstream dependencies.
3. Test failures on modified models fail the CI job, preventing the PR from merging.
4. The PR author fixes the failing tests before the PR is approved.
The --state flag compares the current project state against the production manifest to identify which models have changed. This "slim CI" approach runs only the models affected by the PR, keeping CI build time proportional to change size rather than project size.
Our data architecture practice designs dbt testing frameworks and data quality programmes for enterprise analytics teams — contact us to discuss data quality strategy for your transformation pipeline.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →