writing dbt tests that catch real data bugs

TL;DR

Test business invariants, not database mechanics. A test that says revenue is never negative catches real money bugs, a test that says a column exists catches nothing. Write a handful of sharp custom tests on the facts your business actually depends on, and give every test a severity that matches how bad a failure really is.

writing dbt tests that catch real data bugs

Use this when

• your test suite is green but bad data still reaches dashboards
• you are deciding which tests to add to a new mart model
• stakeholders keep finding data bugs before your tests do

Not for this skill when

• you need to debug one specific failing test, see the debugging skill
• you want generic test syntax only, check the dbt docs for that
• the problem is pipeline speed, not correctness

Steps

1. Start with the four generic tests that catch the most common breakage, on every mart model:

# models/marts/schema.yml
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: order_total
        tests: [not_null]
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'paid', 'shipped', 'cancelled']

Expected output: dbt test now guards identity, nulls, and the known value set. These four catch the majority of upstream breakage for almost no effort.

1. Add a relationships test on every foreign key your joins depend on, since orphaned keys silently drop rows:

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

Expected output: orphaned foreign keys fail the test before they silently drop rows from your joins. This is the highest-value generic test most teams skip.

1. Write a singular test for the invariant the business actually cares about, in plain SQL:

-- tests/assert_order_totals_non_negative.sql
SELECT order_id, order_total
FROM {{ ref('fct_orders') }}
WHERE order_total < 0

Expected output: any negative revenue fails loudly with the offending rows attached. Singular tests return violating rows, so they read like plain English assertions.

1. Set severity to match reality: errors for corruption, warnings for drift:

-- tests/assert_daily_volume_within_range.sql
{{ config(severity='warn') }}
SELECT order_date, COUNT(*)
FROM {{ ref('fct_orders') }}
GROUP BY 1
HAVING COUNT(*) < 100

Expected output: unusual low-volume days warn instead of blocking the pipeline. Severity warn keeps noisy-but-informative checks from holding up deploys while still surfacing them.

1. Store failures on the tests that matter so the next failure is debuggable instead of mysterious:

tests:
  +store_failures: true

Expected output: failing rows land in the audit schema as queryable tables. Debugging goes from guessing to reading rows, which is the difference this whole skill is about.

Variant phrasings

dbt custom data quality tests examples

Singular tests in the tests/ folder are the mechanism. Write one per business rule and keep each under twenty lines so they stay readable.

dbt test severity warn vs error

Error blocks the run, warn just logs. Use error for corruption like null keys, warn for drift like volume changes.

how many dbt tests is enough

Cover every primary key, every foreign key, and the three to five invariants finance would bet on. More than that usually tests the test framework instead of the data.

Why it happens

Green test suites miss real bugs because they test what is easy, not what matters. Generic null checks on a column nobody queries catch nothing, while one assertion on the metric the CEO quotes catches the bug that costs trust. Tests are only as good as the invariants they encode, so encode the ones with business consequences and skip the ceremony.

Edge cases

• Tests on views re-run the view query, which can be expensive. Test the underlying table when the view is just a filter.
• accepted_values with a long list goes stale fast. Prefer a relationships test against a dimension table.
• store_failures on huge tables writes huge audit tables. Scope singular tests to key columns.
• Tests do not run unless dbt test or dbt build executes them. A test nobody runs is documentation, not protection.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_sEcrhGpSAiTFxvddRJKXIQ