Deciding What to Test

Concepts covered: Data tests

Each test is a query that scans a table on every build. A project with 40 models and 10 tests each runs 400 queries after its models, and on tables of hundreds of millions of rows those queries take longer than the models did. The second cost is attention: each failure is a message someone must read, decide on and clear. Both costs are worth paying when a test's failure would change what someone does that day. A test whose failure changes nothing is a scheduled query that produces noise.

Tests worth their cost

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.

Singular tests belong where money, or a count that leaves the warehouse, depends on an invariant: payouts equal line totals minus refunds, every invoice falls inside its subscription window, no refund exceeds its line. These are the rules a business user would state as facts, and the tests make dbt restate them on each run.

Tests that cost more than they return

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.

Warnings nobody owns are the last category. A warn test that has fired on every build for 2 months has become part of the log's shape, and the morning it fires for a new reason, nobody reads it. Each warning either gets an owner and a date to fix the upstream cause, or gets deleted.

A test plan in 3 questions

For each test, ask what rows it would return if the model broke, who would read the failure, and what they would do. A test with a clear answer to all 3 keeps its place and gets the severity the answers imply. A test where the third answer is nothing is removed, the build gets faster, and the failures that remain get read. A later lesson lays out a plan per layer; these 3 questions are what that plan is made of.

Do

• Put unique and not_null on the key of every model, compound keys included
• Test relationships in marts, where the join that needs them happens
• Test accepted_values on columns a case expression branches on
• Give every warn test an owner and a date, or delete it

Avoid

• Put not_null on columns that are null by design
• Repeat a non-key test on a column copied from 1 parent with no join
• Keep a test whose failure nobody would act on

About this lesson

This section is part of the Testing: Intermediate 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.