Course contents

The Analytics Engineer’s Field Guidedbt 2: make it trustworthyStep 7: Data tests

Data tests

Part 2 · dbt · step 7

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(*) against COUNT(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

Orders sent twiceupstream glitch

TUE 06:00

Build: all greenno test to catch it

TUE 09:00

Revenue ×2on the dashboard

FRIDAY

Someone asksin a meeting
3 days green · 3 days wrong
nothing failed, and that is exactly the problem

Three things went wrong, in order:

  • Upstream re-sent Monday's orders overnight, so every order landed twice. fct_orders was a plain table, rebuilt from all of raw.orders (step 4), not an incremental merge on unique_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
Run when she remembersAnd only on models she happened to touch.
Invisible to everyone elseNobody can run a check they don't know exists.
Nothing at 6amThe build passes, the data is wrong, no one is told.
Found by a stakeholderThree days late, in a meeting.
Checks in the repo
Run on every buildEvery model, every run, no one has to remember.
Visible in the codeReviewed in the pull request like any other change.
The run fails loudlyBad data stops there instead of reaching a dashboard.
Found at 06:02The run fails and Airflow alerts Alice, before anyone opens a report.

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
Check the dataRun against the real table you just built.
"Is this column ever null? ever a duplicate?"Catch bad rows in production, every run.
Unit tests
Check the logicRun against fake rows you write yourself.
"Given this input, is the output right?"Catch logic bugs before any data flows.

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.

Daily build
prod, every run
→
Data tests
real rows, just built
→
Alert on fail
bad data caught today

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.yml
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_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_customers')
                field: customer_id

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.yml, the same stg_orders entry
models:
  - name: stg_orders
    data_tests:
      - dbt_utils.expression_is_true:      # model-level: checked as written
          arguments:
            expression: "status <> 'paid' or amount_eur > 0"
    columns:
      - name: amount_eur
        data_tests:
          - dbt_utils.expression_is_true:  # column-level: becomes amount_eur >= 0
              arguments:
                expression: ">= 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.yml
sources:
  - name: raw
    database: RAW_DB
    schema: PUBLIC
    tables:
      - name: orders
        config:
          loaded_at_field: _loaded_at      # when the loader wrote each row
          freshness:                       # {...} is a mapping on one line
            warn_after: {count: 12, period: hour}
            error_after: {count: 24, period: hour}
# 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:

0 rows returned
nothing broke the rulePASS ✓
↔
that's the whole rule
1 row or more
these rows broke itFAIL ✗

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:

The rule you want
The query you write
Amounts are never negative
where amount_eur < 0
Every order reaches the mart
left join fct_orders … where f.order_id is null
A refund never exceeds the order
where refund_eur > amount_eur

The 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_is_positive.sql  (fails if any row comes back)
SELECT order_id
FROM {{ ref('stg_orders') }}
WHERE amount_eur < 0

A singular test can also span several models, which column tests can't:

-- tests/every_order_reaches_fct_orders.sql  (did the join drop anyone?)
SELECT s.order_id
FROM {{ ref('stg_orders') }} s
LEFT JOIN {{ ref('fct_orders') }} f ON f.order_id = s.order_id
WHERE f.order_id IS NULL

-- tests/daily_orders_never_double.sql   ← Alice's Tuesday, as a test
WITH daily AS (
    SELECT order_date, count(*) AS n_orders
    FROM {{ ref('stg_orders') }}
    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)

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.

tests/
your own SQL tests
├── amount_is_positive.sql
singular, one model
├── every_order_reaches_fct_orders.sql
singular, across models
└── generic/
reusable tests, attached in YAML
    └── is_positive.sql
works on any column, any model

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_is_positive.sql
{{ config(severity='warn', store_failures=true) }}

SELECT order_id FROM {{ ref('stg_orders') }} WHERE amount_eur < 0
# the same on a built-in, in models/staging/_models.yml
      - name: order_id
        data_tests:
          - unique:
              config:
                severity: warn
                store_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/is_positive.sql
{% test is_positive(model, column_name) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} < 0   -- the bad rows
{% endtest %}

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:

Layer
Catches
How
Column tests
Nulls, duplicates, bad categories.
The four built-ins, your generic tests
Business rules
Logic that must always hold across columns.
expression_is_true, or a file in tests/
Source freshness
Upstream data stopped arriving.
dbt source freshness
Unit tests
Broken SQL logic, before prod.
Given / When / Then
The data quality standard
Every mart carries a tested primary key: unique + 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_stg_orders_order_id ........ [PASS in 0.4s]
# 2 of 3 WARN 2 amount_is_positive ................ [WARN 2 in 0.5s]
# 3 of 3 FAIL 12 unique_stg_orders_order_id ....... [FAIL 12 in 0.5s]
dbt test vs dbt build
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.

Lesson quiz

  1. How does a dbt data test decide pass or fail?

    1. Its SQL returns a boolean; TRUE for every row means pass
    2. It passes when its query compiles and runs without error
    3. Its SQL selects the bad rows; zero rows returned means pass
    4. Its SQL counts the good rows; any count above zero means pass
  2. The raw orders feed died at 3am, but every model test is green. What would have caught it?

    1. A not_null test on order_id
    2. A unit test on stg_orders
    3. A relationships test on customer_id
    4. A freshness block on the source
  3. The rule is that a refund never exceeds its order. Alice adds dbt_utils.expression_is_true at model level. Which expression makes the test pass only when the rule holds on every row?

    1. refund_eur is not null
    2. refund_eur <= amount_eur
    3. refund_eur > amount_eur
    4. count(refund_eur) <= count(amount_eur)