Data tests: is the data any good?
The checks that run in production, on every build, on the rows you actually shipped.
A model that builds isn't a model that's right. dbt will happily create a table full of duplicates, nulls where a join failed, or yesterday's data frozen in place, and report a green run either way.
A data test is what turns "it built" into "the numbers are trustworthy". It's a query that looks for rows that shouldn't exist, declared next to the model and run on every build. Built-in tests cover most columns. Business rules, source freshness and tests you write in SQL cover the rest.
Alice learned all of that in one bad week.
The Tuesday that changed how Alice tests
For months, Alice tested by hand. Every time a build finished she opened a Snowflake worksheet and ran the same three queries, from memory:
COUNT(*)againstCOUNT(DISTINCT order_id), to catch duplicated orders.WHERE customer_id IS NULL, to see whether the join had quietly dropped anyone.- Today's row count next to yesterday's, to see whether the load had gone silent.
Ten minutes, and she knew where she stood. She was good at it, which matters here, because what went wrong wasn't carelessness.
Then came a Tuesday. Alice was in a workshop all morning, so the 6am production build (Airflow runs it, part 3) went ahead without her:
TUE 03:00
TUE 06:00
TUE 09:00
FRIDAY
Three things went wrong, in order:
- Upstream re-sent Monday's orders overnight, so every order landed twice.
fct_orderswas a plain table, rebuilt from all ofraw.orders(step 4), not an incremental merge onunique_key. So both copies went in. - The build ran green anyway, because nothing in the project checked for duplicates. That check lived in Alice's head.
- Nobody noticed until the head of sales asked, three days later, why Monday had spiked.
What actually changed
Not the queries. Those were fine. What changed is where they live: out of Alice's head, into a YAML file next to the model, where dbt runs them on every single build.
Checks in her head
Checks in the repo
The duplicate check that would have caught that Tuesday is two lines of YAML, and you'll write it before the end of this lesson.
Two kinds of test, two different questions
dbt has two kinds of test. One looks at rows, the other at SQL. Mixing them up is why testing feels complicated.
Data tests
Unit tests
This lesson is data tests, the kind that would have saved Tuesday. Unit tests get the next one to themselves.
How a data test works
A data test runs on the rows that were just built. You run it in your userland while you work, CI runs it on your pull request, and production runs it on every build.
It looks for bad rows in the table itself. The rule is dead simple: zero bad rows means it passes. Here are the four built-ins on one model, in the same _models.yml that holds its description (step 2):
# models/staging/ models: - name: stg_orders description: "One row per order, amounts in EUR." columns: - name: order_id data_tests: - unique # no two rows share an order_id - not_null # order_id is never empty - name: status data_tests: - accepted__ models. yml values: # only values from this list arguments: values: ['paid', 'refunded'] - name: customer_id data_tests: - relationships: # every value exists in another model arguments: to: ref('stg_ field: customer_idcustomers')
Run dbt test --select stg_orders on step 2's trial data and accepted_values fails at once: order 2 is 'pending'. That's the test doing its job. If pending is a real status, add it to the list. If not, fix the data upstream.
Two syntax notes. Test inputs go under arguments:, which needs dbt 1.10.5 or later (check with dbt --version). And to: ref('stg_customers') has no {{ }}: in SQL you always wrap ref(), but a YAML key that expects a model takes the bare call.
Business rules: expression_is_true
Column tests can't say "a paid order is never free": that rule needs two columns. dbt_utils.expression_is_true can: a reusable test from the step 6 package (run dbt deps first, or dbt won't find it). It passes when the expression holds for every row:
# models/staging/ models: - name: stg_orders data_tests: - dbt__ models. yml, the same stg_orders entry utils. arguments: expression: "status <> 'paid' or amount_eur > 0" columns: - name: amount_eur data_tests: - dbt_expression_ is_ true: # model-level: checked as written utils. arguments: expression: ">= 0"expression_ is_ true: # column-level: becomes amount_eur >= 0
Source freshness: catch a broken pipe before the dashboard does
A test on your models can't see that the raw feed died at 3am. A freshness block on the source can: it warns or errors when the newest row is too old.
# models/staging/ sources: - name: raw database: RAW_DB schema: PUBLIC tables: - name: orders config: loaded__ sources. yml at_ freshness: # {...} is a mapping on one line warn_after: {count: 12, period: hour} error_after: {count: 24, period: hour}field: _loaded_at # when the loader wrote each row
# not part of dbt build: the scheduled job runs it first, on its own dbt source freshness
_loaded_at isn't a business column. Most loaders stamp each row with the time it landed, each under its own name (Fivetran writes _fivetran_synced), so use yours. No such column? On Snowflake, leave loaded_at_field out and dbt reads the table's last-modified time from metadata. On dbt 1.9 or older, both keys sit directly on the table, without config:.
Custom tests: the whole trick, in one rule
When the built-ins can't express a rule, you write your own. The mechanism first, because it's simpler than people expect.
A data test is a SELECT that returns the rows you don't want to exist. dbt runs it and counts what comes back:
So you never write "check that amounts are positive". You write the opposite: show me the negative amounts. If the query finds any, the test has found a problem. Three examples of the same flip:
where amount_eur < 0left join fct_orders … where f.order_id is nullwhere refund_eur > amount_eurThe built-in unique and not_null work exactly this way too. not_null is nothing more than select * from model where column is null, wrapped up so you can call it by name. Anything you can write in SQL, you can test.
Two ways to package one of these. A single file for a one-off rule, or a macro when the same rule keeps coming back.
1. Singular tests, one file per rule
Drop a .sql file in tests/, write the query that finds the bad rows, and you're done. No YAML, no macro, no registration. The filename becomes the test name.
-- tests/amount_ SELECT order_id FROM {{ ref(is_ positive. sql (fails if any row comes back) 'stg_ WHERE amount_eur < 0orders') }}
A singular test can also span several models, which column tests can't:
-- tests/every_ SELECT s.order_id FROM {{ ref(order_ reaches_ fct_ orders. sql (did the join drop anyone?) 'stg_ s LEFT JOIN {{ ref(orders') }} 'fct_ f ON f.order_id = s.order_id WHERE f.order_id IS NULL -- tests/orders') }} daily_ WITH daily AS ( SELECT order_date, count(*) AS n_orders FROM {{ ref(orders_ never_ double. sql ← Alice's Tuesday, as a test 'stg_ GROUP BY order_date ) SELECT order_date, n_orders FROM daily QUALIFY n_orders > 1.8 * avg(n_orders) OVER () -- near double the usual day -- a real sale day can trip it too: make it a warning (below)orders') }}
Why QUALIFY and not HAVING: a window function like avg(...) OVER () isn't allowed in HAVING. QUALIFY is the clause that filters on window results, in Snowflake and in DuckDB (a free database that runs on your laptop, step 15).
Use ref() and source() inside them exactly as you would in a model. That's not decoration: it puts each test in the right place in the graph. So dbt build (run plus test, end of this lesson) runs a test right after its model, and stops before anything downstream is built on bad rows.
Tuning a test: warn instead of fail, and keep the bad rows
Not every violation should stop the pipeline, and when one does you'll want to see the offending rows rather than guess. Both are config, and both work on singular tests and built-ins alike:
-- tests/amount_ {{ config(is_ positive. sql severity= SELECT order_id FROM {{ ref('warn', store_ failures= true) }} 'stg_ WHERE amount_eur < 0orders') }}
# the same on a built-in, in models/staging/ - name: order_id data_tests: - unique: config: severity: warn store__ models. yml failures: true
severity='warn' logs the failure in yellow and lets the run continue: right for a rule you're still calibrating. store_failures=true saves the failing rows in a table named after the test, in a schema ending in _dbt_test__audit. The run log prints the query to read them, so instead of "42 rows failed" you get the 42 rows.
2. Generic tests, written once and reused
When the same rule keeps coming back on different columns, promote it. You wrap the query in a {% test %} block: a macro that dbt treats as a test, kept in tests/generic/ or macros/. It takes two standard arguments, model and column_name. Write once, apply to fifty columns:
-- tests/generic/ {% test is_is_ positive. sql positive( SELECT * FROM {{ model }} WHERE {{ column_name }} < 0 -- the bad rows {% endtest %}model, column_name) %}
The part that looks like magic isn't. When you attach is_positive to a column in YAML, dbt fills both arguments for you. model becomes the tested model's full name, like USERLAND.DBT_ALICE.STG_ORDERS. column_name becomes the column you attached it to.
# then attach it like any built-in, on any column, in any model - name: amount_eur data_tests: - is_positive
Where each kind of check sits
Put together, a production project layers four kinds of check. Three look at the data: column tests, business rules and source freshness (its own command). The fourth, unit tests, looks at the logic and gets the next lesson:
expression_is_true, or a file in tests/dbt source freshnessunique + not_null, or dbt_utils.unique_combination_of_columns for a two-column key. Every source you don't control carries a freshness block, and every cross-column business rule is an expression_is_true. Beyond that, don't test everything: add tests for the columns other models join on and for any status or category with a known set of values, and stop. A test you'd never act on is just a slower run.Running them: dbt test vs dbt build
# run every test in the project dbt test # just this model's tests dbt test --select stg_orders # build + test together, skipping whatever sits downstream of a failed test dbt build # what a run prints, trimmed: # 1 of 3 PASS not_null_ # 2 of 3 WARN 2 amount_stg_ orders_ order_ id ........ [PASS in 0.4s] is_ # 3 of 3 FAIL 12 unique_positive . . . . . . . . . . . . . . . . [WARN 2 in 0.5s] stg_ orders_ order_ id ....... [FAIL 12 in 0.5s]
dbt test only tests what's already in the warehouse. Edit a model, skip dbt run, and you're testing the old table. dbt build avoids that trap. It runs and tests model by model, in dependency order. When a test fails, it skips everything downstream of that model. Other branches still build, and a warn test skips nothing. In CI and production, build is the one you want.Next lesson: the failure a data test can't see. Every row unique, nothing null, and every number wrong.

