Testing: Intermediate

A test's severity decides what dbt does once the test's query has returned rows. The default severity is error: dbt prints FAIL with the row count, marks the test failed, skips every node downstream of the model the test guards, and ends dbt build with a nonzero exit code, so the scheduler records a failed run. The other severity is warn: dbt prints WARN with the same row count, builds every downstream node as if the test had passed, counts the test under warnings in the summary line, and exits with code 0. The query is identical under both; only what happens to its result changes.

Severity is a config on the test, so in a properties file it sits under config: beside the test it changes. Here the key tests on fct_order_lines keep the default, and the accepted_values test on shipping_region becomes a warning:

The log tells the 2 cases apart on the test's own line and again in the summary. An error reads FAIL 3 not_null_fct_order_lines_merchant_id and is followed by SKIP lines for every model behind it; a warning reads WARN 3 accepted_values_fct_order_lines_shipping_region and the models behind it show OK as usual. Under dbt test there are no models to skip, so the difference is the exit code alone, and a scheduler that triggers downstream jobs on exit code 0 will trigger them after a warning.

The question that picks a severity is what a downstream reader would see if the build continued. A null merchant_id on fct_order_lines means fct_payouts sums that line into no merchant's payout, so money leaves a real total; that test is an error, because a build that continues produces a payout table someone acts on. A shipping_region outside the 4 listed values means 1 row lands in an other bucket on a dashboard; every other row on that dashboard is still right and nobody is paid from it, so that test is a warning that tells the team a new region appeared.

Severity can also be set once in dbt_project.yml, scoped by folder, and a test's own config: overrides the folder's value. A project whose staging tests describe the quality of raw feeds, while its marts carry guarantees, writes:

The folder default replaces severity: warn written on 60 staging tests with 1 line, and it makes the policy readable in one place. It also makes an override stand out: a staging test that carries severity: error in its own config: is announcing that this one rule is a guarantee, and a comment saying why belongs next to it.

The animation below builds stg_orders and fct_orders from the same 5 raw rows, 1 of them with a null customer_id, under both severities. Switch between the versions and watch what happens to fct_orders once not_null has found that row.

Changing a test from error to warn is the quickest way to turn a red build green, and the change outlives the morning it was made. A test lowered to warn during an incident keeps warning after the incident, and 3 months later nobody remembers that it once stopped the build. Record the reason in the properties file beside the severity line, with a date, so the next engineer can decide whether the reason still holds.

The 4 generic tests each state one shape of rule about one column: its values are present, its values are distinct, its values come from a list, its values exist in another model. A rule that compares 2 columns, joins 2 models, or applies arithmetic fits none of them. A singular test is that rule written out as SQL: a .sql file in the tests/ folder whose SELECT returns the rows that break the rule. dbt runs the file like any other test, and 0 returned rows means the test passes.

On fct_order_lines, a refund can never exceed what the customer paid for the line. No generic test compares refund_amount with line_total, so the rule becomes a file named tests/assert_refund_within_line_total.sql:

dbt names the test after the file, so the log reads PASS assert_refund_within_line_total or FAIL 14 assert_refund_within_line_total. The ref() places the test in the DAG behind fct_order_lines, so it runs after that model is built and, at error severity, holds back every model that reads fct_order_lines. As with a generic test, the query dbt sends wraps the file's SELECT in a count, and the count decides the result:

The rule that most often needs a singular test is a reconciliation: a total in one model must equal the sum of its parts in another. stg_orders carries order_total from the checkout system, and fct_order_lines carries 1 line_total per line. The 2 agree for every order when the lines model is right, and they disagree when a join dropped a line or duplicated one. The test sums the lines per order, joins the sum back to the order, and keeps the orders where the figures differ:

On the morning this test fails, 3 details in the query determine how much the engineer can learn from it. The left join keeps an order whose lines were all dropped: it has no row in line_sums, so lines_total comes back null, coalesce counts it as 0, and the order is returned. An inner join would drop that order from the result, and the worst case of a lost line would pass. The SELECT list names the key and both figures, so each returned row identifies 1 order and shows the size of its disagreement; select * over a 40-column model would bury that. The tolerance of 0.01 absorbs rounding between a system that stores cents and one that stores decimals, and it is written in the test where the next engineer can read what was chosen and ask why.

A singular test takes its configs from a config() block at the top of the file, the same way a model does. The reconciliation above keeps the default severity, because a payout computed from a missing line is wrong money. A test that reports orders with a negative total, which a legacy returns process produces on purpose and which finance has agreed to tolerate while it is replaced, warns instead:

A singular test fits 1 rule in 1 place. When the same rule, refund within total, applies to 3 models, 3 copies of the file drift apart as columns are renamed, and that case is a generic test with arguments, which a later lesson writes. Until then, 1 file per rule, named for what it asserts, with assert_ as the prefix so the tests/ folder reads as a list of guarantees the project makes.

A singular test that passes on its first run has shown 1 of 2 things: the data obeys the rule, or the query cannot return rows. A join on the wrong column, a where that compares a varchar with a number, or a filter that excludes every row each produce a test that passes forever. Before trusting a new singular test, run its SELECT in dev with the condition flipped, where refund_amount <= line_total, and confirm that rows come back; then flip it back.

A failing test prints a count. FAIL 17 assert_refund_within_line_total says 17 lines broke the rule and says nothing about which 17. The compiled query sits in target/compiled/, so the first instinct is to copy it into a query editor, strip the count wrapper and run it. By then the next build may have rebuilt the model and the rows have changed. dbt can instead write the failing rows to a table in the warehouse as part of running the test, so the evidence waits for whoever investigates.

The config is store_failures: true. On 1 test it sits under config: in YAML, or in the config() block of a singular test; across a project it goes in dbt_project.yml; and for 1 run, the flag dbt test --store-failures turns it on for every test the command runs.

dbt writes each test's failing rows to a schema named after the target schema with _dbt_test__audit appended, so a build into analytics writes analytics_dbt_test__audit. The table takes the test's name: the not_null above becomes analytics_dbt_test__audit.not_null_fct_order_lines_merchant_id, and a singular test keeps its file name. Each run drops and recreates the table, so it holds the failures of the most recent run and nothing older. A test that passes still writes its table, with 0 rows, which is how a stored-failures test that ran can be told from one that never did.

What the table holds is whatever the test's inner SELECT returns, and that differs by test. not_null selects every column of each row with the null, so its audit table is a slice of the model. unique selects the duplicated value as unique_field with an n_records count, 1 row per duplicated value. A singular test writes the columns chosen in its SELECT, which is why section 1 asked for the key and the compared figures.

If all 17 rows share 1 source_system, the fix is in 1 feed. If they share 1 created_at date, a batch arrived broken. If they share nothing, the defect is in the model's own SQL.

The audit table is for people to read. Nothing can ref() it, no test reads it, and the next run replaces it, so a model built from it would stand on a table that changes under it. Storing failures also makes warnings reviewable: a severity: warn test with store_failures: true leaves its rows in the audit schema even though the build went green, and the owner of that warning can read them without rerunning anything.

Writing a table for every test on every build has a cost: a unique test over 200 million rows, with a bug that duplicates half of them, writes 100 million rows to the audit schema before it fails. store_failures_as picks the form. table is the default. view stores the test's query instead of its rows, so the failures are computed when someone looks and reflect the model as it stands then. ephemeral stores nothing, and overrides a project-wide store_failures: true for 1 test.

A view costs nothing to write but keeps no evidence. Once the model is rebuilt after a fix, the view shows today's rows, so the 17 that failed yesterday are gone. For marts where a failure has to be examined after the fact, a table keeps the evidence; for staging tests where the goal is a quick look, a view is enough. The schema config on a test moves its audit table, so schema: quality writes to analytics_quality instead of analytics_dbt_test__audit.

fct_order_lines holds 1 row per line of an order. No single column identifies a row: order_id repeats across the lines of one order, and line_number repeats across every order. The grain is the pair, and a unique test on either column alone fails on correct data. Proving 1 row per pair needs a test that sees both columns at once.

In the sample, order 1042 carries line 2 twice, and that fifth row is the defect. unique on order_id reports 1041 and 1042 as duplicates, which is the correct shape of the data. unique on line_number reports 1 and 2. Neither points at the row that is wrong.

This is the query the unique test sends, with a 2-column group in place of 1. On the sample it returns 1 row, 1042, 2, 2, and with failures stored that row names the order to look at. The test adds no column to the model and works for a key of any width; a 4-column key is a 4-column group by.

The other route changes the model. Concatenating the parts into one column gives downstream models a single join key and gives the generic tests a single column to guard. fct_order_lines builds it in its SELECT:

A concatenated key has 2 failure modes, and they are handled in different places. First, a null in either part makes the whole key null, because || with a null yields null. unique ignores nulls, so 500 lines with a missing line_number all produce a null key and unique passes over all of them. The not_null on order_line_key catches the result directly, and the not_null on each part names the cause. Second, parts that run together without a separator collide: order 1 line 12 and order 11 line 2 both become 112. The - in the model's SQL is what prevents that, keeping them apart as 1-12 and 11-2, so the separator must be a character that cannot appear in either part. Without it, the YAML would expose the collision only indirectly, as a unique failure on the key 112 even though the 2 rows behind it are different lines.

Build the key column when other models join to the grain: fct_refunds needs to point at one order line, and a single order_line_key column makes that a one-column join and a relationships test. Use the singular test when the pair is a grain nobody joins to, such as a daily snapshot keyed by merchant_id and snapshot_date, where a column no model reads buys nothing. In both cases, not_null on each part stays, because the grain is proven only when every part is present.

Grain tests come first: unique and not_null on the key of every model, including the compound keys from section 3. They catch the most common defect in a transformation, a join that fans out and doubles rows, and they catch it at the model where it happened rather than 3 models downstream in a total that is twice what it should be. They cost 2 queries per model, and a failure always means a real defect.

relationships tests at the mart layer, from a fact to each dimension it joins, catch orphan rows: an order line whose sku has no row in dim_products. A left join downstream would carry a null product name onto a report; an inner join would drop the line and shrink revenue. Either outcome is wrong, so the test belongs at error severity. At staging, the same test is rarely worth its cost, because staging mirrors the source and an orphan there is a fact about the source that the mart test will report anyway.

accepted_values pays off on a column that SQL branches on. If fct_order_lines computes case when status = 'refunded' and a new status partially_refunded appears, every such line falls through to the else without a sound. The test on status reports the new value before a report is wrong. On a column nobody branches on, such as a free-text shipping_method, the test is a list that goes stale.

not_null on every column is the usual overreach. A shipped_at is null on every unshipped order by design, a coupon_code is null on most orders, and each of those tests either fails on correct data or gets set to warn and ignored. not_null belongs on the key and on columns a downstream calculation reads without a coalesce.

The key of every model is tested in that model, even when its parent already proved the same column unique. dim_customers may select customer_id unchanged from stg_customers, but if it also joins stg_addresses and a customer has 2 addresses, the join returns that customer twice, and only the unique test on dim_customers sees it. Repetition becomes waste on a non-key column that a model copies straight through from 1 parent with no join: a not_null on email in stg_customers and again in a model that selects email from that one parent can only fail together, because the second query reads the same values the first one checked. Test such a property where a model creates or changes it: a relationship where a join happens, a value list where a case reads it.