Data Quality Tests That Catch Real Bugs

Nobody notices broken data until it's embarrassing

Pipelines rarely fail loudly. The job succeeds, the dashboard refreshes, the numbers render — and they're wrong. Someone in a meeting three weeks later notices that revenue for April looks light, and now you're reconstructing what an upstream system did to a column nobody was watching.

Tests are how you find out first. But most test suites end up as a wall of green checkmarks that catch nothing, because they test the wrong things. The distinction that matters is between tests that assert the shape of your data and tests that assert its meaning.

Start with the four that catch the most

If you write nothing else, write these. In dbt they're one block of YAML per model:

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'paid', 'shipped', 'cancelled', 'refunded']

Four ideas, and between them they catch a startling share of real incidents:

unique on the grain. This is the most valuable test you will ever write. A duplicated primary key silently doubles every metric downstream — and it looks like growth, which is why it survives review. The most common cause is a join that quietly became one-to-many when an upstream table gained a second row per key.

not_null on join keys. A null foreign key means rows vanish from inner joins. Your totals shrink and nothing errors.

relationships. Catches orphaned facts — orders referencing customers that don't exist. Usually a symptom of loads running out of order, or a dimension being rebuilt while facts point at the old version.

accepted_values. Catches the day an upstream engineer adds a partially_refunded status without telling anyone. Your case statement doesn't know about it, so those rows fall into the else branch and disappear from the report.

That last one is worth dwelling on. It's the only test in the set that catches a change in the world rather than a bug in your SQL, and those are the changes that hurt most, because nothing in your code changed to explain them.

Test the grain explicitly

Most models have an intended grain — one row per order, per customer per day, per session. The grain is an assumption, and unwritten assumptions rot.

For a single column, unique covers it. For a composite grain, test the combination:

models:
  - name: fct_daily_customer_revenue
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - date_day
            - customer_id

Write this test the moment you create the model, while the grain is still fresh in your head. It's the cheapest documentation you'll ever produce, and it fails loudly the first time someone's change breaks the assumption.

Then test the things that are true about your business

Generic tests verify structure. They cannot tell you the numbers are right. For that you need assertions specific to your domain, and in dbt any query that returns rows is a failing test:

-- tests/assert_no_negative_revenue.sql
select
    order_id,
    total_amount
from {{ ref('fct_orders') }}
where total_amount < 0
  and status != 'refunded'

Returns nothing, test passes. Returns rows, and each one is a concrete problem you can go look at.

A few that repeatedly earn their keep:

Reconciliation against the source. Aggregate your model and compare it to the system of record. If your revenue table disagrees with the billing system by more than rounding, you want to know today.

-- tests/assert_revenue_matches_source.sql
with modelled as (
    select date_day, sum(revenue) as amount
    from {{ ref('fct_daily_revenue') }}
    group by date_day
),
source as (
    select transaction_date as date_day, sum(amount) as amount
    from {{ source('billing', 'transactions') }}
    where status = 'settled'
    group by transaction_date
)
select
    m.date_day,
    m.amount as modelled_amount,
    s.amount as source_amount
from modelled m
join source s using (date_day)
where abs(m.amount - s.amount) > 0.01

Freshness. A pipeline that stops running is indistinguishable from a quiet week, and dashboards don't announce that their data is stale.

sources:
  - name: billing
    tables:
      - name: transactions
        loaded_at_field: created_at
        freshness:
          warn_after: {count: 6, period: hour}
          error_after: {count: 24, period: hour}

Volume. Row counts that collapse or explode usually mean a partial load or a duplicated one. Compare today against a recent baseline rather than a fixed threshold, so the test doesn't need constant retuning as you grow.

Referential completeness in the other direction. relationships checks that facts point at real dimensions. Sometimes the interesting question is the reverse — every active customer should have appeared in at least one order this quarter, and if a whole segment vanished, something upstream broke.

Severity, or everyone stops looking

A test suite where a failure blocks the build teaches people to skip the build. A suite where nothing blocks teaches people to ignore failures. You need both, deliberately assigned:

      - name: customer_id
        tests:
          - not_null:
              severity: error       # stop the pipeline
          - relationships:
              to: ref('dim_customers')
              field: customer_id
              severity: warn        # tell me, but don't block
              error_if: ">100"      # unless it's a lot

My rule of thumb: error when the data would be actively misleading — broken grain, nulls in keys, reconciliation mismatches. warn when it's a data-quality nuisance you want visibility on but which won't produce a wrong number.

Then enforce the part that actually matters: a failing test has to reach a human, in a channel they read, with the model name in the message. A test failure logged only in CI output that nobody opens is not a test. It's a diary entry.

Where to put them

Test at the boundaries, not everywhere. Two places give you most of the coverage:

At the source, test what you don't control — freshness, uniqueness of the natural key, accepted values on status fields. These catch upstream changes before they propagate.

At the mart, test what your consumers depend on — grain, non-null keys, reconciliation totals, business rules. These catch your own logic errors.

Intermediate models mostly don't need their own tests. If the source is clean and the mart is correct, the middle is doing its job, and tests there tend to duplicate coverage while slowing every run.

The honest version of the payoff

Tests will not make your data correct. They encode what you currently believe about it, and they'll tell you when reality stops matching that belief. That's a narrower promise than "data quality," and it's the one worth making.

The practical loop looks like this: every time something breaks in production, before you fix it, write the test that would have caught it. Then fix it. Over a year that produces a suite shaped by your actual failure modes rather than by a checklist — and unlike a checklist, it keeps paying attention while you're asleep.