Testing: Beginner

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.

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 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.

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.

dbt ships 4 generic tests. not_null and unique need only a column, while accepted_values and relationships take inputs as well. Each is a query template with a model and a column filled in, and each reports 1 kind of mistake that raw data makes often. Read the query behind a test and you know which rows it reports and how dbt counts them; the same query shows which bad rows it lets past.

unique groups the column and keeps every group holding more than 1 row. It catches a loader that replayed a batch, and a join that matched several rows on the far side and multiplied each order.

The clause where order_id is not null means nulls never form a duplicate group, so a column with 3 null ids passes unique. Reporting those is not_null's job, which is why the 2 tests are declared together on an id column. The count is the number of duplicated values rather than rows, so 1 id that appears 3 times prints FAIL 1.

accepted_values groups the column by its values and keeps the values absent from the list you give it. It catches a new status the application started sending, a casing change such as Shipped, and a typo that reached the source.

Here 2 of the 5 orders in stg_orders arrived with a status the list does not hold, 1 recased as Shipped and 1 missing entirely. The grouped result above becomes 1 returned row, Shipped with its capital letter, printed as FAIL 1. The null status passes this test: null not in (...) evaluates to neither true nor false, and the where clause drops the row. The count is the number of distinct unexpected values, so 500 rows of cancelled also print FAIL 1, which says 1 new value exists and nothing about how many rows carry it.

relationships takes each value in a column and looks for it in a column of another model, keeping the values that find no match. It catches an order whose customer was deleted, a customer created after the orders loaded, and a customers model that filtered out rows the orders still reference.

The left join keeps every order and attaches a customer when one exists; the final where keeps the orders where nothing attached, which above is orders 103 and 105 from customer 4. Nulls are removed in child, so an order with no customer id at all passes relationships and is not_null's to report. The count is child rows, so those 2 orders print FAIL 2, even though they share 1 missing customer.

Tests on a column are declared in a YAML properties file in the models folder, under the model's name and then the column's name. dbt reads every .yml file under the project's model paths when it parses the project, so the file's name is yours to pick and 1 file can describe several models. What ties a block to a model is the name under models:, which must match the model's file name without .sql.

Tests under data_tests: come in 2 shapes. A test with nothing to configure, such as unique, is written as its bare name, and not_null is written the same way. A test that needs input is a mapping whose single key is the test name, with the inputs beneath arguments:. accepted_values needs its list, and relationships needs the model to search and the column there. The to: value uses ref() just as a model would, so dbt draws an edge from stg_customers to the test and builds that model before the test runs.

Each entry under data_tests: becomes a node with a name dbt assembles from the test, the model, the column and, where present, the arguments. The file above yields 5 test nodes, named as follows.

Those names are what the build prints, so a line reading FAIL 2 not_null_stg_orders_customer_id is enough to tell which rule failed and on which model column, without opening the file. The argument values are part of the name, which is why adding cancelled to the accepted list produces a test node with a new name and the old one disappears from the project.

After the file is saved, dbt test parses it, compiles each of the 5 nodes into the query the previous section showed, sends them in turn and prints 5 lines. The accepted list travels into the not in clause. For relationships, to: and field: become the parent query, and in every template the column name is pasted wherever order_id or status stood.

description is text dbt stores with the model or column it describes. People read it and dbt can generate documentation from it, but it changes no SQL. The columns: list is also a declaration rather than a check. dbt does not compare it with the warehouse when it parses, so a column that exists only in the YAML surfaces when a test on it runs and the warehouse answers that the column does not exist. That outcome prints as ERROR, meaning the query itself broke, which is different from FAIL, meaning the query ran and returned rows.

Put a not_null test on 3 versions of the same column and the 3 outcomes separate cleanly. The typo never reaches the rows at all: the warehouse refuses the query, so there is no count to report, and dbt prints ERROR with the warehouse's message in the log.

A project with many models usually keeps 1 properties file per folder of models, each named for what the folder holds, because a single file for every model in the project grows past what anyone will read.

dbt build runs each model and then the tests declared on it before it moves to anything that reads from that model. A test that fails stops the build from running the models downstream of the tested model, and dbt marks each of them SKIP. Models elsewhere in the DAG, with no path leading back to the failed test, keep building, so 1 bad column holds back only the part of the project that depends on it.

In the first run, 4 raw rows build the stg_orders view, both tests on order_id pass, and order_totals builds 3 rows, with customer 1 at 2 orders and 100. The loader then delivers order 103 a second time. The second build creates stg_orders again and runs unique_stg_orders_order_id, which gets 1 row back and prints FAIL 1, so dbt skips order_totals and the table still holds the 3 rows from the first run. Once the duplicate is removed, the third build passes and rebuilds order_totals to the same 3 rows. Switch to the project without tests and the second build writes customer 1 as 3 orders and 160, which is the wrong figure a payouts report would have read.

The summary counts a failed test under ERROR, which is also where a model whose SQL broke would land, so ERROR=1 on its own does not say which happened; the per-node lines do. SKIP is a status of its own: dbt sent no SQL for that node and wrote nothing, which leaves whatever the node held before. The build then exits with a non-zero code, and a later dbt build starts from the beginning, because dbt keeps no memory of which nodes were skipped.

order_totals is a table, so SKIP leaves the rows of the last good build in place and a dashboard that reads it keeps showing 100 for customer 1. Had order_totals been a view, SKIP would have left its definition in place too, but a view recomputes on every read from the current stg_orders, which now includes the duplicate, so anyone querying it would have seen 160 regardless. The skip protects stored rows; it cannot protect a view from its own upstream. That is a reason to materialize the models people read directly as tables when their inputs are tested.

Siblings carry on. Had the project also held a stg_customers model and a customer_count table built from it alone, both would build on the same run, because no edge connects them to the failed test. The build prints OK for them and SKIP for order_totals in the same log.

The 2 routes send the same test query and print the same FAIL line. The difference is timing: dbt run finishes every model first, so a downstream table is already overwritten by the time dbt test reports the duplicate, and the test becomes a record of damage done. dbt build interleaves tests with models, so a failure arrives before the next model reads the bad rows.

Tests check the rows a model holds. They say nothing about whether new rows are still arriving: a raw table that stopped loading 3 days ago passes unique and not_null as well as it ever did, and every model built from it serves data 3 days old without a single FAIL. Source freshness is the check for that, and it runs against the raw table dbt reads, never against a model.

loaded_at_field names the column the loader stamps with the time it wrote the row. The freshness block sets 2 windows: past warn_after dbt prints WARN, past error_after it prints ERROR. Set in the source's config: block, both apply to every table under it; a table can carry its own config: to override them, which is why customers, loaded once a night, gets windows measured in days while orders keeps the hourly ones.

dbt source freshness sends that query for each source table with a freshness config, subtracts the newest load time from the time of the query, and compares the age with the 2 windows. It touches no model and writes no table. dbt build never runs it either, so a project that wants both checks runs 2 commands, and freshness usually goes first because a stale source makes building from it pointless.

The command prints 1 line per table showing the status and how long the query took, laid out like a test line. A WARN leaves the exit code at 0, so a calling script carries on; an ERROR sets it non-zero, the same signal a failed test gives. The same age that prints WARN for orders would print PASS for customers, because the table's own windows replace the source's.

Suppose the orders loader dies at 22:00 on 2026-11-01 and the morning script runs at 08:00 on 2026-11-03, freshness first. raw.orders is 34 hours old, past its 24-hour error_after, so it prints ERROR and the command exits non-zero, which stops the script before the build. raw.customers prints PASS because it loaded at 01:00, 7 hours earlier. Had the build run anyway, it would have found nothing to object to: the raw rows are unchanged, every test returns the same 0 rows as the day before, and order_totals is rebuilt to the same totals it held 34 hours ago. The freshness ERROR is the only line in either log that reports the orders stopped arriving.

The column must say when the row was loaded, not what the row is about. order_date records when a customer bought, so a loader that died on Sunday night leaves Saturday's orders as the newest rows and the check reports stale, which is right, while a quiet Sunday with no sales reports stale too, which is wrong. A column the loader stamps at write time, named something like _loaded_at, separates those 2 cases: it advances with every batch whether the batch held 1 order or 1 million.

A table with years of history makes the max scan everything. A filter: under freshness adds a where clause to the query so the scan stays within recent rows, and the comparison with the windows works as before.