Data Tests, Unit Tests, and the Grader Behind the Curtain

You have already written these tests

Level 4's data quality lesson taught the four generic tests and the rule that a test passes at zero rows. Level 5's write-audit-publish lesson taught the gate above them: stage a batch, check freshness, volume, and null rate, publish only on a pass. Neither of those is repeated here. This lesson adds the layer that sits on top, and it starts by telling you something about this course that has been true the whole time.

The curtain pull

Every workspace exercise you have completed in this course was graded by hidden assertion queries. Each one is written to return the rows that are wrong, and it passes when it returns nothing. Here is one, unedited, from the previous lesson:

SELECT customer_id
FROM fct_customer_revenue
GROUP BY customer_id
HAVING COUNT(*) > 1

That is not a grading trick invented for a learning platform. It is a dbt unique test, and it is exactly what runs in production every time someone merges a model. A test in this workflow is a SELECT that returns violating rows, and zero rows means pass. The convention you have been graded against since your first workspace build is the industry convention, which is why it never needed explaining: you were already doing the job.

Four tests, one violation list

The four generic tests are declared as YAML in a real project, but every one of them compiles down to a query:

Table

Every generic test is a query that returns offending rows. Zero rows returned is a pass.

test	declared as	compiles to	passes when
unique	unique: payment_id	GROUP BY payment_id HAVING COUNT(*) > 1	no key repeats
not_null	not_null: amount_usd	WHERE amount_usd IS NULL	no NULLs
accepted_values	values: [card, cash]	WHERE method NOT IN (...)	no stray value
relationships	to: ref('orders')	NOT EXISTS matching parent row	no orphans

A test runner does not report four booleans. It reports which rows failed which test, because that is the only output an on-call engineer can act on at 3am. So the useful artifact is the four queries stacked into one list of (key, violation) pairs with UNION ALL, which is what the Apply builds.

Be careful with the relationships test. WHERE order_id NOT IN (SELECT order_id FROM orders) looks equivalent to NOT EXISTS and is not: if the parent table holds a single NULL order_id, the NOT IN comparison evaluates to NULL for every row and the test returns nothing at all. It passes silently and forever. NOT EXISTS has no such hole. The orders table in this lesson holds exactly one such NULL, so you can watch both versions run and see them disagree.

Data test versus unit test

dbt 1.8 added unit tests, and the distinction is now standard interview vocabulary. It is not a naming quibble, it is two different questions:

• A data test runs on real data, on every run, and asks "is today's data sane". It catches a corrupted upstream feed, a new payment method nobody told you about, an orphaned foreign key.
• A unit test runs on a fixed fixture in CI, before the model ever touches production, and asks "is my SQL logic correct". You hand-write a small input table, hand-write the output you know is correct, and assert your model turns one into the other.

Only the second catches a boundary bug, because clean data passes every data test while your date filter quietly drops the last day of the month. The classic version of that bug is a timestamp column compared against a bare date string: the comparison is still valid SQL, every generic test stays green, and a slice of real rows silently vanishes. This lesson's fixture is built to spring exactly that class of bug, and finding which rows it catches you on is the work.

Two ecosystem names worth one line each: Great Expectations is used for validation at the ingestion boundary, and Soda for trailing-window anomaly monitoring. Neither replaces the tests above.

Common mistake: writing a test that returns a count instead of the offending rows. SELECT COUNT(*) FROM stg_payments WHERE amount_usd IS NULL always returns exactly one row, so under the zero-rows convention it always fails, and under a "check the number" convention it tells you nothing about which payment to go look at.

Interview nuance: "how would you test this pipeline" is a two-layer answer. Data tests on every run for data sanity, unit tests on fixtures in CI for logic, and each layer catches what the other structurally cannot. Candidates who name only data tests have never had correct-looking logic break on data that passed every check.

On a real platform this differs. Here you write the violation query by hand and the fixture is a seeded table. In a real project you declare unique, not_null, accepted_values, and relationships in a YAML file beside the model, the tool compiles each one into a query, and dbt test fails the build on any non-zero result. Unit tests are declared with given and expect blocks of inline rows, run under dbt build, and never touch the warehouse's real tables. Severity is configurable per test (warn versus error), which is the ledger Level 10 builds.