Most broken dashboards are not caused by a failed pipeline. The job ran, the tables refreshed, and the numbers were quietly wrong: a duplicated order, a status code nobody mapped, a join that dropped every customer created yesterday. Data quality testing is how you catch those problems before your stakeholders do.
If you use dbt, you already have a capable testing framework built in. This guide walks through the three kinds of test dbt offers, generic data tests, singular data tests and unit tests, and shows how to combine them with severity thresholds, stored failures and CI so that tests actually protect production rather than just generating noise.
Data tests vs unit tests
It helps to be clear about what each type of test checks, because they answer different questions:
- Data tests run a query against the data that has been built in your warehouse and fail if it returns any rows. They tell you whether the data is right today.
- Unit tests, added in dbt Core 1.8, run a model’s SQL against small, hard-coded input rows and compare the result with expected output. They tell you whether the logic is right, independent of what is in production.
You need both. A perfectly written model can still produce bad output from bad source data, and perfectly clean source data will not save you from a CASE statement with a missing branch. In dbt 1.8 the YAML key for data tests was also renamed from tests: to data_tests: to make the distinction clearer; the old key still works, but new projects should use the new one.
Generic data tests: the first line of defence
dbt ships with four generic tests: unique, not_null, accepted_values and relationships. They are declared in YAML next to the model and cover a surprising share of real-world issues.
# models/marts/_marts.yml
version: 2
models:
- name: fct_orders
columns:
- name: order_id
data_tests:
- unique
- not_null
- name: customer_id
data_tests:
- not_null
- relationships:
to: ref('dim_customers')
field: customer_id
- name: order_status
data_tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'returned', 'cancelled']Under the hood each test compiles to a SELECT that returns offending rows. The unique test, for example, groups by the column and returns any value that appears more than once. Because they are just SQL, you can inspect the compiled query in target/compiled/ when a test fails and run it yourself to see exactly which rows are the problem.
Reach for packages before writing your own
The dbt_utils package adds widely used generic tests such as unique_combination_of_columns, expression_is_true, accepted_range and recency. If you are coming from Great Expectations, the community dbt_expectations package ports many of its expectations, such as row-count and distribution checks, to dbt macros. A composite key test is a good example of why packages are worth it:
models:
- name: fct_order_lines
data_tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns:
- order_id
- line_numberSingular data tests for business rules
Some rules do not fit a reusable pattern. A singular test is simply a .sql file in the tests/ directory that returns the rows breaking the rule. If it returns zero rows, it passes.
-- tests/assert_payments_match_order_totals.sql
-- Every completed order should be fully paid, allowing for rounding.
with orders as (
select order_id, order_total
from {{ ref('fct_orders') }}
where order_status = 'completed'
),
payments as (
select order_id, sum(amount) as paid_total
from {{ ref('fct_payments') }}
group by order_id
)
select
o.order_id,
o.order_total,
coalesce(p.paid_total, 0) as paid_total
from orders o
left join payments p on o.order_id = p.order_id
where abs(o.order_total - coalesce(p.paid_total, 0)) > 0.01Singular tests are where reconciliation checks live: totals between layers, counts between source and target, and rules your finance or operations teams care about. If you find yourself copying the same singular test across models, turn it into a custom generic test with a {% test %} block in tests/generic/ or macros/.
Unit tests: checking the logic, not the data
Unit tests are declared in YAML under unit_tests:. You name the model, provide given rows for each ref() or source() it depends on, and state the expected output. dbt builds the inputs as small fixtures, runs the model’s SQL against them and diffs the result.
# models/marts/_marts_unit_tests.yml
unit_tests:
- name: order_status_is_normalised
description: "Raw status codes map to the canonical status values."
model: fct_orders
given:
- input: ref('stg_orders')
rows:
- {order_id: 1, customer_id: 10, raw_status: 'SHP', order_total: 50.00}
- {order_id: 2, customer_id: 11, raw_status: 'RTN', order_total: 20.00}
- {order_id: 3, customer_id: 12, raw_status: 'xx', order_total: 5.00}
- input: ref('dim_customers')
rows:
- {customer_id: 10}
- {customer_id: 11}
- {customer_id: 12}
expect:
rows:
- {order_id: 1, order_status: 'shipped'}
- {order_id: 2, order_status: 'returned'}
- {order_id: 3, order_status: 'unknown'}You only need to supply the columns the test actually uses; dbt fills the rest with nulls. That keeps fixtures short and readable. Good candidates for unit tests are models with tricky CASE logic, regular expressions, date arithmetic, window functions and anything that has caused an incident before.
Testing incremental models
Incremental logic is notoriously easy to get wrong and hard to test against real data. Unit tests let you override is_incremental() and supply the current state of the target table through this:
unit_tests:
- name: incremental_only_picks_up_new_events
model: fct_events
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_events')
rows:
- {event_id: 1, event_ts: '2026-10-01 09:00:00'}
- {event_id: 2, event_ts: '2026-10-02 09:00:00'}
- input: this
rows:
- {event_id: 1, event_ts: '2026-10-01 09:00:00'}
expect:
rows:
- {event_id: 2, event_ts: '2026-10-02 09:00:00'}Note that the expected rows describe what the model’s SELECT returns for this run, not the final merged table. Unit tests are best run in development and CI rather than on every production build; you can exclude them with --exclude-resource-type unit_test or select them on their own with --select test_type:unit.
Choosing the right test
| Test type | Checks | Where it lives | Best for |
|---|---|---|---|
| Generic data test | Built data | YAML on the model or column | Keys, nulls, allowed values, referential integrity |
| Singular data test | Built data | tests/*.sql | One-off business rules and reconciliations |
| Unit test | Model logic with fixture rows | YAML under unit_tests: | Complex transformations, incremental logic, regressions |
| Source freshness | Load recency | sources: YAML | Detecting stalled ingestion before models run |
Making tests useful in production
Severity and thresholds
Not every failure should stop the pipeline. Use severity, warn_if and error_if to express tolerance, and where to scope a test to recent data on very large tables:
- name: email
data_tests:
- not_null:
config:
severity: error
warn_if: ">0"
error_if: ">100"
where: "created_at >= current_date - 7"Store failures for faster triage
Setting store_failures: true (per test or project-wide in dbt_project.yml) writes the failing rows to a table in an audit schema. When a test fails at 3am, whoever is on call can query that table directly instead of rerunning compiled SQL.
Use dbt build, not run then test
dbt build runs models, tests, seeds and snapshots in DAG order, and if a test on an upstream model fails with severity error, the downstream models are skipped. That stops bad data from propagating into your marts. Running dbt run followed by dbt test builds everything first and only tells you afterwards.
Run tests in CI
In a pull request job, build and test only what changed and its downstream dependencies, deferring to production artefacts for everything else:
dbt build --select state:modified+ --defer --state ./prod-artifactsThis keeps CI fast while still running unit tests and data tests on the models that the change can affect. If you orchestrate dbt with Airflow, the same dbt build command slots straight into a task; the hands-on guide to integrating dbt, Snowflake and Airflow shows that setup end to end.
Common pitfalls
- Testing everything with unique and not_null and stopping there. These catch structural issues but say nothing about whether the numbers are right. Add at least one reconciliation test per important mart.
- Alert fatigue. Dozens of permanently failing warnings teach people to ignore the test run. Fix, raise the threshold, or delete tests that nobody acts on.
- Expensive tests on huge tables. A
uniquetest on a multi-billion-row fact table can cost more than the model. Usewhereto test recent partitions, or test the key at the staging layer instead. - Testing only the marts. Problems are cheaper to diagnose at the source. Add freshness and basic key tests on sources and staging models.
- Unit test fixtures that mirror the code. If you derive expected rows by reading the SQL, you will copy its bugs. Write the expected output from the business requirement.
- Forgetting relationships tests on late-arriving data. A fact row can legitimately arrive before its dimension. Scope the test with
whereor set it to warn rather than fail.
Conclusion and key takeaways
dbt gives you most of what you need for data quality without adding another tool. Start with generic tests on keys and sources, add singular tests for the rules your business actually depends on, and use unit tests to lock down complex logic before it reaches production. If you are new to dbt itself, the overview of what dbt is and the problems it solves is a good starting point, and there is more on pipeline design on the blog.
- Data tests check the data in your warehouse; unit tests (dbt 1.8+) check model logic against fixture rows.
- Use
data_tests:in YAML and lean ondbt_utilsanddbt_expectationsbefore writing custom tests. - Put reconciliation and business rules in singular tests under
tests/. - Control noise with
severity,warn_if,error_ifandwhere, and store failures for triage. - Prefer
dbt buildso failing tests block downstream models, and runstate:modified+in CI.
