Pattern

Invariants as tests

A rule that must hold on every row, or between a model and its source, written as "the rows that break it".

SELECT … WHERE total <> items - discounts + shipping is a test: it passes when it returns no row (dbt runs such queries as singular tests). Between layers, compare counts: a join that only adds columns must keep rows_in = rows_out, and COUNT(*) against COUNT(DISTINCT key) at each CTE shows where the grain broke.

-- A payment never comes before its order: no row means the test passes.
SELECT p.payment_id, p.paid_at, o.ordered_at
FROM payments p
JOIN orders o ON o.order_id = p.order_id
WHERE p.paid_at < o.ordered_at;

payment_id	paid_at	ordered_at
No row

payment_id

paid_at

ordered_at

No row

Try it in Randori

Practise:A total that must add up Rows in, rows out Count every CTE