How to Wire dbt Data Quality Tests Across a Lineage

Why one test on the mart is not enough

A common dbt project has a single not_null test on the final mart and nothing upstream. When a source system starts emitting a bad status value or a broken foreign key, that bad data flows through staging and into the fact table. The lone mart test rarely catches it, so the dashboard is wrong for days before anyone notices.

The fix is to test at every layer of the DAG. dbt runs tests in dependency order, so a source-level failure stops the run before the bad rows ever reach staging. dbt build runs models and tests together, so one command validates the whole lineage.

Step 1 - test the sources

Sources are where bad data enters. Declare columns and generic tests in models/sources.yml so a regression upstream fails fast:

version: 2
sources:
  - name: raw
    schema: public
    tables:
      - name: order_lines
        columns:
          - name: customer_id
            tests: [not_null]
          - name: product_id
            tests: [not_null]
          - name: status
            tests:
              - accepted_values:
                  values: ['pending', 'paid', 'refunded']

accepted_values is the one to reach for on any status or enum column - it catches a new, unexpected value the instant it appears in the source.

Step 2 - test staging keys and joins

Staging models are where you enforce primary-key integrity and foreign-key joins. Add a new models/staging/schema.yml with unique, not_null, and relationships tests:

version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [not_null, unique]
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: product_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_products')
              field: product_id
  - name: stg_customers
    columns:
      - name: customer_id
        tests: [not_null, unique]
  - name: stg_products
    columns:
      - name: product_id
        tests: [not_null, unique]

The relationships test is the one that catches a broken join before it silently drops rows in the fact table. to: takes a ref('...') (a model) or a source('...'), and field: is the column to compare against.

Step 3 - a singular test for the business rule

Generic tests cover shape and integrity, but not business invariants. A singular test is just a .sql file in tests/ that SELECTs the rows that violate a rule. dbt's contract: zero rows returned means the test passes.

Create tests/assert_net_amount_consistent.sql to enforce that net_amount = gross_amount - discount_amount:

SELECT
    order_id,
    gross_amount,
    discount_amount,
    net_amount
FROM {{ ref('fct_orders') }}
WHERE abs(net_amount - (gross_amount - discount_amount)) > 0.01

The abs(...) > 0.01 tolerance avoids false failures from floating-point rounding on numeric math.

Run every test in one command

cd dbt_project
dbt build --profiles-dir ..     # runs models AND tests in dependency order
dbt test --profiles-dir ..      # only the tests

A clean run reports PASS=8 or more. Scope work while iterating with --select, and use --store-failures to inspect which rows a failing test returned. Once this is wired, gate merges on dbt build in CI so bad data fails the pull request, not the dashboard.