Constraints & tests

Profiling measures what's there. Tests declare what must be there — and fail loudly when reality disagrees. The mental shift that matters: your beliefs about the data ('order_id is unique', 'every sku exists in the catalog') are currently undocumented assumptions. Every one of them will eventually be violated by an upstream change. A test is an assumption converted into an alarm.

The five test families (this taxonomy covers ~everything)

• Uniqueness — the declared key is actually a key: count(*) = count(DISTINCT order_id). The test that catches double-loads, source re-sends, and wrong-key MERGEs. If you write only one test per table, write this one.
• Presence (not-null) — required fields are populated. Tie thresholds to profiling evidence: email in POS data is 30% null by design, so the test is 'null rate ≤ 35%', not 'never null'. Tests that ignore reality get deleted; tests calibrated to it get trusted.
• Referential integrity — every foreign reference resolves: every fact sku exists in dim_product; after Module 4, every customer_ref resolves through the crosswalk. Orphaned facts are how revenue silently drops out of category reports.
• Accepted values — enums stay inside the contract: status IN ('completed','cancelled','refunded'), channel IN ('web','pos'). Cheap to write, and the first thing to fire when a source deploys 'just a small change.'
• Business sanity — domain rules with no schema expression: qty > 0, unit_price BETWEEN 0.5 AND 500, order_date not in the future. These encode judgment, which is why they're the tests AI can't write for you.

-- Each returns a violation count; 0 = pass. A runner script
-- (next lab) executes the suite and fails the pipeline on any nonzero.

-- uniqueness
SELECT count(*) - count(DISTINCT order_id) FROM fct_order_lines;

-- referential integrity
SELECT count(*) FROM fct_order_lines f
LEFT JOIN dim_product p USING (sku) WHERE p.sku IS NULL;

-- accepted values
SELECT count(*) FROM fct_order_lines
WHERE status NOT IN ('completed', 'cancelled', 'refunded');

Where tests run: the gate

Tests earn their keep by blocking, not observing. The pattern is a quality gate: pipeline loads → suite runs → any failure stops the run before the mart is exposed (or, in stricter setups, loads into a quarantine schema that never faces consumers). A dashboard of failing tests that everyone ignores is decoration. A gate that stops bad data from reaching the CEO's dashboard — and the AI agents querying the warehouse — is infrastructure.