Data-Quality Assertions

A load that "succeeds" can still be wrong

A loader can finish with a green checkmark and still hand you garbage: a customer_code that appears twice, a NULL email where every row needs one, an order that points at a customer who was never inserted. Nothing threw an error, so nothing stopped it, and the bad rows flow straight into a dashboard where someone trusts them. Data-quality (DQ) tests are the guardrail. They run right after the load and fail the pipeline before corrupt data reaches a mart.

The zero-rows assertion

Every DQ test in dbt is built on one idea: write a query that returns the rows that violate your expectation. If the query returns any rows, the test fails. Healthy data returns zero rows and passes.

This flips your instinct. Do not query the good rows and hope the count looks big. Query the bad rows and require exactly zero. That is unambiguous and threshold-free: zero orphans, zero duplicates, zero unexpected values, or the load is broken.

The dbt four

Four tests cover most real defects. Each is a "violations = 0" query.

-- not_null: a required column is missing
SELECT customer_key FROM dim_customer WHERE email IS NULL;

-- unique: a key appears more than once
SELECT customer_key FROM dim_customer
GROUP BY customer_key HAVING COUNT(*) > 1;

-- accepted_values: a value outside the allowed set (NULL counts as a violation)
SELECT customer_key FROM dim_customer
WHERE status NOT IN ('active','churned','prospect')
   OR status IS NULL;

-- relationships: a fact row with no matching dimension (the orphan anti-join)
SELECT f.customer_key
FROM fact_sales f
LEFT JOIN dim_customer d ON d.customer_key = f.customer_key
WHERE d.customer_key IS NULL;

The last one is the anti-join from Level 2 repurposed as referential integrity, the highest-value check in a star schema. Walk it: the LEFT JOIN keeps every fact row, a missing dimension leaves d.customer_key as NULL, and the WHERE keeps only those unmatched rows.

Worked example, matching the seeds in your exercises:

-- dim_customer keys: 1, 2
-- fact_sales   keys: 1, 2, 99   (99 has no dimension row)
SELECT f.customer_key
FROM fact_sales f
LEFT JOIN dim_customer d ON d.customer_key = f.customer_key
WHERE d.customer_key IS NULL;
-- customer_key
-- 99

One row comes back, so the test fails and names key 99 as the orphan. On clean data the result is empty, which is the pass state your Apply writes into orphan_facts.

Pitfalls

• Asserting the happy path. SELECT COUNT(*) FROM dim_customer WHERE email IS NOT NULL and checking it is "large enough" is fragile and needs an arbitrary threshold. Count the bad rows and require zero instead.
• NULL swallowed by NOT IN. status NOT IN ('active','churned','prospect') returns nothing for a NULL status, because NULL compared to any value is UNKNOWN, not TRUE, and WHERE keeps only TRUE rows. The null silently passes. When a null is also invalid, add OR status IS NULL, exactly as the accepted_values query above does.

Interview nuance: interviewers ask why the orphan check uses LEFT JOIN ... WHERE d.customer_key IS NULL instead of WHERE f.customer_key NOT IN (SELECT customer_key FROM dim_customer). If the dimension holds even one NULL customer_key, the NOT IN version returns zero rows for every fact and hides real orphans, because x NOT IN (..., NULL) evaluates to UNKNOWN. The anti-join is NULL-safe: it matches keys with = in the ON clause and is unaffected by nulls elsewhere in the column. Same intent, one silently wrong.