DT7: dbt: transformations as tested code

Lesson 2 of 5 · reading 15–25 minutes; practice and project time is on the module page

Tests that run on every build

🎯You'll be able to

Add generic tests (unique, not null, accepted values, relationships) and singular tests to a dbt project, and read a failing test.

🏠The everyday picture

A factory checks each batch at several stations as it moves down the line, not only at the end. A fault is caught where it starts.

📘What it really is

Generic tests are declared in .yml next to a column:
unique and not_null: the grain and required columns;
accepted_values: a column holds only known values (status: delivered, cancelled, refunded);
relationships: every value exists in another model (every customer_key in dim_customers).
Singular tests are SQL files in tests/ that return the rows that break a rule. SabziSathi's assert_daily_revenue_reconciles.sql returns any day where the mart's revenue differs from the fact table's.
A test passes when it returns zero rows. A failing test prints how many rows broke it.
Severity: error stops the build; warn reports and carries on. Use warn for things to look at, error for things that make numbers wrong.
Test the grain of every fact, the keys between facts and dimensions, and the business rules that numbers depend on.
Packages such as dbt_utils and dbt_expectations add more tests (ranges, row counts, combinations); the kit doesn't use them, to keep installation small.

📝A worked example

Input: in a copy of the solution, change the accepted values for fct_order_lines.status to only ['delivered', 'cancelled'], then run dbt test --profiles-dir . --select fct_order_lines.

Expected output: FAIL 1 accepted_values_fct_order_lines_status__delivered__cancelled with Got 1 result, configured to fail if != 0 (the one unexpected value is refunded), and Done. PASS=11 WARN=0 ERROR=1.

🌍Where it's used

Every dbt project: unique and not-null tests on primary keys at a minimum.
Finance: singular tests that reconcile marts with the ledger.
CI: tests run on every pull request before merging (Lesson 5).

📝Case scenario

An airline's loyalty team added a join to a booking model. It quietly turned some bookings into two rows, and points were double-counted in a report to partners. A unique test on the booking id would have failed on the pull request that introduced the join.

🖱️Try it

Add a singular test to your Project 2 warehouse: no order line may have net_amount_paise larger than amount_paise. Run dbt build. Success: the test runs and passes, and you can make it fail by changing the rule.

⚠️Common mistakes

No test on the grain. Sign: duplicates appear after a join change. Fix: unique on the grain column of every fact.
Everything as a warning. Sign: builds pass with broken numbers. Fix: error for rules that make figures wrong.
Tests only at the end. Sign: a mart fails and the cause is three models back. Fix: test sources, staging and marts.

🛠️For your project

Project 2 test 2 checks a unique test exists on the fact table's grain; tests 5 and 6 rely on relationships being right.

✅Quick check

When does a dbt test pass?

Show the answer

When its query returns no rows.

What does a relationships test check?

Show the answer

Every value in a column exists in another model's column.