dbt Testing That Actually Catches Bugs

Every dbt project starts with the same four tests: not_null, unique, accepted_values, relationships. They're a fine baseline. They're also the tests that will pass while your pipeline quietly produces wrong numbers for three weeks.

Good dbt testing is about encoding your business assumptions as assertions — so that when a source changes behaviour, you find out before your stakeholders do.

The problem with generic tests alone

Generic tests check structural integrity. They tell you a column exists and has values. They don't tell you whether those values make sense.

A not_null test on revenue passes whether the value is 49.99 or 4999.00. A unique test on user_id passes even if you're joining on the wrong grain and producing duplicate rows that cancel out. These are real bugs I've seen survive in production.

Singular tests for business logic

For anything that encodes a business rule, write a singular test — a SQL file in tests/ that returns rows only when something is wrong.

sql
-- tests/revenue_not_negative.sql
-- Fails if any order has negative revenue after refund logic

select
    order_id,
    net_revenue
from {{ ref('fct_orders') }}
where net_revenue < 0

sql
-- tests/session_ends_after_start.sql

select
    session_id,
    started_at,
    ended_at
from {{ ref('fct_sessions') }}
where ended_at < started_at

These are zero-setup, self-documenting, and they fail loudly when the data violates the contract.

dbt-expectations for richer assertions

The dbt-expectations package ports Great Expectations assertions into dbt. It's worth adding to any serious project.

yaml
# models/staging/schema.yml
models:
  - name: stg_orders
    columns:
      - name: amount_usd
        tests:
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 40
              max_value: 200
              group_by: [country_code]

The expect_column_mean_to_be_between test is particularly useful — it catches distribution shifts that row-level tests miss entirely.

Testing freshness as a first-class concern

Late data is wrong data. Model freshness tests belong in sources.yml, not as an afterthought.

yaml
sources:
  - name: salesforce
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 24, period: hour}
    loaded_at_field: _fivetran_synced
    tables:
      - name: opportunity
      - name: account

For derived models where freshness depends on multiple upstream sources, I write a singular test that compares max(updated_at) against current_timestamp() with an explicit threshold.

Severity levels matter

Not every test failure should halt your pipeline. dbt lets you set severity: warn for tests that should alert but not block.

yaml
- name: conversion_rate
  tests:
    - dbt_expectations.expect_column_values_to_be_between:
        min_value: 0.001
        max_value: 0.5
        severity: warn  # Alert the team, don't stop the run

I use error for anything that would cause incorrect numbers downstream, and warn for anomalies that need investigation but don't invalidate the data entirely.

A test coverage heuristic

There's no magic number for test coverage in dbt, but I aim for:

Every primary key: not_null + unique
Every foreign key: relationships
Every metric-defining column: at least one business-logic singular test
Every source table: freshness test
At least one distribution test per fact table

The goal isn't 100% test coverage — it's ensuring that when something breaks, you find out before a stakeholder files a ticket.

#The problem with generic tests alone

#Singular tests for business logic

#dbt-expectations for richer assertions

#Testing freshness as a first-class concern