Testing: Advanced

Two rules live here: the outcome label with its thresholds at 90 and 70, and counts_for_streak, which must agree with the label. The rows worth writing sit on each side of those boundaries, and the test holds exactly those: a 90 that is legendary by 0 points and an 89 that misses it by 1, a 70 that passes by 0 points and a 69 that misses by 1, and a 95 well inside the top band.

unit_tests is a top-level key in any properties file, beside models. model names the model whose SQL runs. Each given entry names an input by the same ref() or source() call the model makes and lists its rows. expect lists the rows the model must return. A column left out of a given row is null, and expect compares only the columns it lists, so completed_date stays out of the inputs and user_id and score stay out of the expectations. What remains reads as the 2 rules the test checks and nothing else.

dbt never reads stg_lesson_completions for this test. It renders the model with the ref() replaced by a CTE holding the given rows, each value cast to the column's type in the real staging table, wraps the model's SQL as a subquery that keeps only the expected columns, and unions it with the expected rows under a label column. The whole test is 1 query, and it creates no table.

Every actual row must match an expected row and nothing may be left over. When they differ, dbt prints the unmatched rows from both sides with their labels, which is the whole diagnosis. If the model had score > 70 on the second threshold, the output would be the 2 rows below: the row with completion_id 2 came back as retry and false, so the comparison excludes the boundary the rule includes.

dbt test --select "test_type:unit" runs every unit test and nothing else. In dbt build, a model's unit tests run after its parents are built and before the model itself, so a failing unit test skips the model and everything downstream, the same way a failing data test does. The difference is timing: a data test fails after a bad table exists, and a unit test fails before the model has written anything. One consequence of the casts in the query above: the staging relation must exist in the warehouse so dbt can read its column types, which section 3 returns to.

fixture names a file, tests/fixtures/outcome_boundaries_expected.csv, laid out as a header line and rows, so 1 file can serve several tests of the same model. With format: sql, rows holds a SELECT whose result is the input, and that SELECT may stack rows with union all or compute them from a sequence.

A join reads 2 relations, and the unit test gives each one. Rows that should find a partner share a key, rows that should drop out do not, and the expected rows state which survived. An input the model reads from a source is given by its source() call in the same way. An input given as rows: [] tests what the model does with nothing, which is where a missing coalesce or an inner join written where a left join belongs shows itself.

A model that reads the current time gives a different answer each day, and a unit test with a fixed expectation would pass this week and fail next week. {{ dbt.current_timestamp() }} is a function dbt ships that renders to the warehouse's current time, and a unit test can replace what it renders with a literal under overrides.

What a unit test cannot replace is the model's own SQL, which is the point: everything around the logic is held still so the logic alone decides the result.

The stg_users table holds 12 rows with a null email, all accounts created in 2016 before the signup form required one, and they will stay null for as long as the accounts exist. A plain not_null test on that column fails on every run, and a test that always fails teaches the team to stop reading it, so on the day 500 new rows arrive with null emails, the failure looks like every other day's. The severity setting from the intermediate lessons chooses warn or error for the whole test. A threshold goes further and makes the outcome depend on how many rows fail.

warn_if and error_if each hold a comparison against the number of failing rows, written as the operator and the number dbt will paste into SQL. The default for both is != 0, which is the behavior the reader already knows: any failing row is a failure. With the values above, 1 to 12 failing rows warn and 13 or more error. The 12 known rows keep the test yellow, and the 13th row, the first new one, turns it red and stops the models behind stg_users.

dbt reads should_error first, then should_warn, and the first true one is the status. With severity: warn on the test, dbt skips error_if and reads only warn_if, so a warn-severity test never errors however many rows fail; the thresholds only decide which runs stay silent.

A reconciliation test compares a mart's total XP per user with the sum of completions in staging. A mismatch of 1 point on 1 user is noise from an event that arrived late, and a mismatch of 600 points on 1 user is a broken join, but the count of mismatched users is 1 in both cases. fail_calc replaces the count(*) in the wrapper with any aggregate over the test's rows, and the thresholds then compare against that figure instead.

sum over 0 rows is null, and null compared with 500 is neither true nor false, so the coalesce turns a clean run into 0 failures rather than an undefined one. The wrapper's first line becomes coalesce(sum(abs(xp_diff)), 0) as failures, and dbt reports that figure as the failure count, so a warning that reads 37 failures means 37 XP points of drift across all users. The test's name carries that meaning, since the log line will not. The 500 is measured too: the stored failures of the last 30 runs show late events never pushed the daily sum past 480 points, so 500 sits just above the worst day the known noise produced.

Every check so far ran on rows, written by hand or read from the warehouse. A project can also be built on no rows at all. dbt run --empty and dbt build --empty render every ref() and source() as a subquery that returns 0 rows, then create every model, view or table, with the warehouse running each statement over an empty input.

A teammate renames score to points in stg_lesson_completions and opens a change with the staging model updated and nothing else. A full build reaches the failure only after building, on its real rows, every model scheduled ahead of int_lesson_outcomes, then fails there on the missing column. The empty build fails at the same model with the same message from the warehouse, that column score cannot be resolved, after reading 0 rows, and reports every model downstream as skipped. The cost is the warehouse's planning time per statement, a few seconds each, which is why the check fits before every merge where a full build does not.

Section 0 noted that a unit test casts its given rows to the input's real column types, which means the input relation must exist. On a fresh schema nothing does, and the unit test cannot run. dbt run --empty creates every relation with the right columns and 0 rows, and dbt test --select "test_type:unit" then runs every unit test on its fixtures, so a change can be checked for shape and for logic without reading a production row. dbt build --empty does both in 1 command and also runs the data tests, which pass on empty tables and so prove nothing there; those run against real data in the scheduled build.

A failing test skips every model downstream of it, so the test that fires first on a fault is the one whose message gets read. If a null user_id enters from the source and the first test to see it sits on the mart, the alert names fct_user_xp, and the investigation starts 3 models away from the fault. The plan below places each test at the layer where its fault can first appear, and at no layer after that.

A source test on raw.lesson_completions finds 12 rows with a null user_id today, the app's known gap for deleted accounts, so its threshold warns at 12 and errors at 13. The staging model drops those rows with a filter, and its own not_null test errors on any null, because after staging a null user_id means the filter broke. The 2 tests read the same column and answer different questions: the source test measures the upstream system, the staging test guards the project's first promise. A mart test on the same column would fire only after both had passed, and would add a third alert to a fault already named.

Staging has 1 kind of logic, a cast or a mapping per column, and 1 unit test per staging model with a row per mapped value covers it. Intermediate models hold the rules people argue about, the outcome thresholds, the 7-day activity window, the join that keeps users with no completions, and each rule gets a unit test named for the rule with 1 row per boundary. A relationships test from int_lesson_outcomes.user_id to stg_users belongs here too, since an orphan between keys joined in intermediate first changes a result there; a mart's own joins to dimensions carry their own relationships tests.

A mart's readers are dashboards and other teams, so the tests here guard what they read. A contract fixes the columns and types, relationships from each fact key to its dimension proves every row can be joined, and 1 singular test per measure reconciles it back to staging with fail_calc measuring drift rather than counting rows. Row-level rules stay out: a mart that re-tests not_null on a key proves only that staging's test already ran, and costs a scan of the largest table in the project to prove it.