A Test Is a Query That Should Return Nothing
A generic test is a parameterized anti-query — SQL written to find what should not exist. dbt compiles it, counts the rows it returns, and compares that count to a threshold; the comparison, not the rows themselves, is the entire verdict. Hold onto that one sentence — every trap in this chapter is a consequence of it.
stg_episodes carries 9 physical rows behind 8 distinct episode_id values — that gap is this chapter’s opening beat, and every count from here on respects it. A dbt test is not a constraint the warehouse enforces; it is a SELECT dbt expects to return zero rows, wrapped in a counting query that turns “zero rows” into a pass/fail verdict.
A handful of new tests ship on this project by the end of the chapter. The honest fact this section teaches first: having more tests is not the same as having coverage. The map below starts exactly where most real projects start — pick a node, then find out what one missing test actually costs.
A generic test is a parameterized anti-query — SQL written to find what should not exist. dbt compiles it, counts the rows it returns, and compares that count to a threshold; the comparison, not the rows themselves, is the entire verdict. Hold onto that one sentence — every trap in this chapter is a consequence of it.
Every staging model and most marts already carry a uniqueness test on their key — the easy 80% every team ships in week one. That’s also why “we have tests” and “we have coverage” get used interchangeably when they describe completely different things: one counts YAML lines, the other counts what could go wrong that nothing would catch. This chapter spends its first widget making that gap visible before naming a single built-in.
- Data test
- dbt’s term for what this chapter covers — a compiled SELECT whose row count decides pass/warn/fail.
- Anti-query
- A query written to find what should not exist, not what you want — every built-in and every singular test in this chapter is one.
- Generic test
- A parameterized anti-query — a macro taking model and column_name — reusable across every column and model it is pointed at.
- Coverage
- Which columns, relationships, and value ranges a project actually asserts — not how many data_tests: lines exist in YAML.
Reading “every model has a test” as “the project is tested.” A uniqueness test on a primary key says nothing about completeness, referential integrity, accepted values, or plausibility — a project can pass every test it has and still ship silently wrong numbers on data no test ever looked at.
Before adding a tenth test, ask which data-quality dimension the project has zero coverage on — not which model has zero tests.
Read a green dbt build the same way you’d read a passing suite with 40% code coverage: real, and incomplete.
Treat the coverage map as a living artifact, not a one-time audit — a new mart with no test on its grain is invisible until you go looking.
The four built-ins this chapter opens with cover uniqueness, completeness (wherever you point not_null), and part of validity (value lists, ranges) — and nothing of accuracy. The six dimensions data quality gets scored against, and what closes that last gap, live in the Data Pipeline KB.
Data quality & validation — the six dimensions (Data Pipeline KB)Coverage is a property of the DAG and the defect classes tested, not a count of data_tests: lines — the next seven sections close specific, named gaps in it, on purpose.