Most dbt test suites I've seen are a wall of not_null checks that pass forever. The useful ones are a handful of tests that fail when the data changes shape.
My short list:
- uniqueness on the grain. If a model is one row per order, test that. When it fails, you have a fan-out join.
- relationships on foreign keys. Cheap, and they catch the missing row before the dashboard does.
- freshness on sources. A stale source that nobody notices is worse than a failed pipeline.
-
one singular test per business rule. "Refund amount must be ≤ original amount" is worth more than 40
not_nullchecks.
The pattern: test the invariants you'd otherwise discover in a meeting.
I used to think more tests meant more confidence. Now I think fewer, sharper tests mean people actually read the failures.
What's your one singular test you'd never remove?
Top comments (1)
Agree on grain and relationships over blanket not_null. The other test that has caught real bugs for us is a known-answer test: a small seed where you decide the result beforehand (say March refunds total exactly 1,240.00), plus a singular test asserting the mart returns that number. It catches the join and filter mistakes schema tests can't see. dbt unit tests cover the tiny version; for bigger fixtures I generate seeds from a fixed random seed so the expected values never drift.