DEV Community

Mira Lund
Mira Lund

Posted on

dbt tests that actually catch something

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_null checks.

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 (3)

Collapse
 
rasinmuhammed profile image
Muhammed Rasin O M •

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.

Collapse
 
refaeldakar profile image
Refael Dakar •

mine is a row-count band on the fact table against the same weekday last week (say within 15%). uniqueness, not_null and relationships all pass happily when a join quietly drops a chunk of rows or a source stops loading, because a smaller table is still a valid table. the band is the only test I have seen catch both a bad join and a late source in one shot.

Collapse
 
ontomato profile image
ontomato •

The uniqueness on grain bit is what stuck with me here.

From my experience the not_null walls rarely catch a join that quietly multiplies rows. What worked for me was naming the grain first, like one row per order, and then treating uniqueness there as the real alarm. When that test fails i want the failure to open the duplicated order ids, so the room talks about fan out instead of another green check. To be honest that one singular test has saved more trust than the long suites ever did.