Which dbt tests are actually worth writing

Most dbt projects have the same test suite: not_null and unique on every primary key, and nothing else. It's not wrong — those two catch a lot — but it leaves the most common real incidents uncovered while the suite still looks healthy.

A more useful question than "what can I test?" is "what would I want to have known first?"

Test the joins, not just the keys

The single highest-value test in dbt is the one people skip: relationships. A primary key being unique and non-null tells you the table is internally consistent. It says nothing about whether it still joins to anything.

- name: customer_id
  tests:
    - relationships:
        to: ref('dim_customers')
        field: customer_id

Orphaned foreign keys are how "revenue dropped 12%" happens. A late-arriving dimension, a filter change in an upstream model, a customer type that stopped being loaded — the fact table is fine, the dimension is fine, and the join silently drops rows. Neither table's own tests catch it. Only the relationship does.

If you add one category of test to an existing project, add this one, on every foreign key that feeds a metric anyone looks at.

Test enums you branch on

accepted_values earns its keep specifically when the column drives logic. If you have a CASE WHEN status IN ('paid', 'refunded') THEN ... ELSE ... anywhere, then a new status value silently lands in the ELSE and produces plausible, wrong output.

The test is functioning as a tripwire on your own assumptions — it fails when reality gains a category your code doesn't know about, which is precisely when you want to be interrupted.

Conversely, don't put accepted_values on a low-cardinality column you only ever group by. A new country code isn't a bug, and a test that fires on normal business growth trains people to ignore the suite.

Be careful with ranges

Range tests are the most common source of self-inflicted alert fatigue, and the reason is almost always the same: the range came from a sample.

An observed minimum of 0 and maximum of 48,900 in a 2,000-row sample is an observation. Asserting it as a constraint means the first genuinely large order fails your build. The sample told you what happened, not what's allowed.

Two things make range tests work. First, assert the bound you actually know — if the business rule is "price is never negative", test >= 0, not BETWEEN 499 AND 48900. Second, if you genuinely want to catch outliers rather than violations, use a warn-level severity so it surfaces without blocking:

- dbt_utils.accepted_range:
    min_value: 0
    config:
      severity: warn

The distinction is between "this must never happen" and "this is worth a look", and dbt gives you severity levels precisely so you don't have to pretend the second is the first.

not_null is a claim about the business

It's treated as boilerplate, but not_null on an optional field is one of the most common causes of a red build that everyone learns to ignore.

Before asserting it, know the difference between "this was populated in every row I looked at" and "the business guarantees this exists." order.discount_code is null in 77% of rows and that's correct. order.id is never null and never will be. The first is an observation; only the second is a constraint.

When you're unsure, the honest options are to leave it untested or to test it at warn severity for a couple of weeks and see whether it fires. Both are better than a test nobody trusts.

Where the four built-ins run out

not_null, unique, accepted_values, and relationships cover more than most people expect, because most incidents are missing rows, duplicated rows, or a broken join. Reach past them when:

• You need a multi-column uniqueness rule — dbt_utils.unique_combination_of_columns.
• You need a cross-column invariant like "total equals subtotal plus tax" — dbt_utils.expression_is_true. These catch real logic bugs and are heavily under-used.
• You need row-count or freshness assertions — dbt_utils.equal_rowcount against a source, or dbt's own source freshness, which is separate from tests and worth configuring.
• The same non-trivial assertion appears a third time — write a custom generic test. Two occurrences is a coincidence; three is a pattern.

Getting the first draft

The hard part isn't writing the YAML, it's the blank page — knowing which of your forty columns deserve which of these. That's an evidence problem, and evidence is what a profile of the data gives you: which columns are genuinely never null, which are actually low-cardinality enums, which have inconsistent types, and how much data each conclusion rests on.

Whatever produces that first draft, apply the same filter to every generated test before committing it: is this a constraint, or is it just something that happened to be true in the sample? Anything you can't answer for should be deleted or set to warn. A suite of thirty tests people trust beats a hundred they skip past.