A Test Is a Query

Concepts covered: Data tests

A dbt test is a SELECT that returns the rows breaking a rule. dbt sends the query, counts the rows that come back, and prints PASS when the count is 0 and FAIL followed by the count when it is not. The 4 built-in tests, and every test anyone writes later, work this one way, so reading a test comes down to reading its query and asking which rows it would return from the table as it stands today.

One query, one count

Take a model stg_orders holding 5 rows, 1 of which arrived without an order_id because the checkout service logged the row before it assigned the id. The rule is that every order carries an id. A test for that rule selects the rows that have none, and nothing else.

select
    order_id
from analytics.stg_orders
where order_id is null

Against that table the query returns 1 row, the one with the missing id, so dbt prints FAIL 1. Once the row is dropped from the model or repaired in the source, the next run of the same query returns 0 rows, which dbt prints as PASS. The number after FAIL is the count of rows the test query returned. For this query each returned row is 1 bad row of the model, so FAIL 4000 would mean 4,000 orders without an id; section 1 shows tests whose query returns 1 row per bad value, where a single returned row can stand for many bad rows.

What dbt wraps around it

dbt never pulls the failing rows back to the machine running it. It wraps the test query in an outer SELECT that counts them, so the warehouse answers with 1 row holding a number whether the test finds 0 bad rows or 10 million.

select
    count(*) as failures,
    count(*) != 0 as should_warn,
    count(*) != 0 as should_error
from (
    select
        order_id
    from analytics.stg_orders
    where order_id is null
) dbt_internal_test

For the 5-row table above, the inner query finds the 1 order without an id, and the warehouse sends back this single row:

The inner query is the test. The outer query is dbt's way of asking the warehouse for a number instead of a result set, and should_error is the flag it reads: true prints FAIL, false prints PASS. A test writes nothing to the warehouse, which is why dbt test can run against a production schema without changing a table in it.

The query describes the bad rows

The query names the rows that should not exist, which is the reverse of how most people write a first query. Written the other way round, where order_id is not null returns 4 rows from the table above and would return every row from a healthy table, so the test would fail hardest when the data is at its best. A test that keeps failing with a count equal to the model's row count is almost always selecting the good rows.

The same shape fits any rule. An order amount must be 0 or more, so the test selects orders whose amount is below 0; a shipped order must carry a shipping date, so the test selects shipped orders where that date is null. Each query reads as a sentence that begins with find me the rows where, and the rest of the sentence is the mistake.

Running every test

dbt test runs every test in the project, 1 query each, printing a line per test with the outcome and the count, and closes with a summary line that totals the outcomes. It exits with a non-zero code when any test failed, so a script that called it can tell the run went wrong without reading the log. The same lines appear inside dbt build, where each test runs as soon as its model is built, which section 3 returns to.

About this lesson

This section is part of the Testing: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.