dbt test failed: see the failing rows, set severity and thresholds, and stop noisy tests blocking builds

#The goal

The overnight dbt build stopped with Failure in test unique_orders_order_id. Downstream models were skipped and the dashboard is stale. You need to answer three questions quickly:

1. Which rows failed, and is the data actually wrong?
2. Should this test block the build, or only warn?
3. How do you rerun just the failed part?

#Step 1: read the failure

The log line tells you the test name, the model and the row count:

Failure in test unique_orders_order_id (models/marts/_orders.yml)
  Got 3 results, configured to fail if != 0
  compiled code at target/compiled/shop/models/marts/_orders.yml/unique_orders_order_id.sql

A test is a query that returns failing rows. "Got 3 results" means three rows came back. Run the compiled SQL yourself to see them:

cat target/compiled/shop/models/marts/_orders.yml/unique_orders_order_id.sql
dbt show --select unique_orders_order_id --limit 50

dbt show works on tests in recent versions. If yours rejects it, paste the compiled SQL into the warehouse.

For a uniqueness failure, get the full duplicate rows, not just the keys:

select *
from analytics.marts.orders
where order_id in (
    select order_id from analytics.marts.orders
    group by order_id having count(*) > 1
)
order by order_id;

#Step 2: keep failing rows automatically

Rerunning SQL during an incident is slow. With store_failures, dbt writes the failing rows to a table on every run:

# dbt_project.yml
data_tests:
  +store_failures: true
  +schema: dbt_test__audit     # default suffix; tables land in <target_schema>_dbt_test__audit

Or for one run only:

dbt test --select orders --store-failures

Each test gets a table named after the test. Query it directly:

select * from analytics.marts_dbt_test__audit.unique_orders_order_id;

From 1.7, store_failures_as takes table, view or ephemeral and overrides store_failures when both are set. A view costs no storage, but it shows the current state, not the state when the test ran.

Tip

Long test names are hashed or truncated to fit identifier limits. If you can't find the table, look in the audit schema for the newest objects, or set a short alias on the test.

#Step 3: decide whether it should block

Not every failing test is a Sev1. Configure each test so the outcome matches the business impact:

# models/marts/_orders.yml (dbt 1.8+; use tests: on older versions)
models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - unique                       # duplicates break revenue: keep as error
          - not_null
      - name: customer_email
        data_tests:
          - not_null:
              config:
                severity: warn           # nice to have, never block
      - name: status
        data_tests:
          - accepted_values:
              arguments:                 # 1.10.5+; older versions put values: at this level
                values: ['placed', 'shipped', 'returned', 'cancelled']
              config:
                severity: error
                warn_if: ">0"
                error_if: ">100"         # a handful of new statuses: warn; a flood: fail
                where: "order_date >= dateadd(day, -3, current_date)"
                limit: 1000

How the thresholds work:

Both default to !=0. Conditions are compared to the number of failing rows.

where limits the test to recent data. Use it on large tables so an old, known issue doesn't fail every run, and so the test scans less.

Warning

Lowering a test to warn to "get the build green" hides the problem permanently. If duplicates in orders break revenue numbers, fix the data and keep the test as error. Record any temporary downgrade in the ticket with an expiry date.

#Step 4: fix the data, then rerun only what failed

Common causes:

After fixing, rerun the failed nodes and everything downstream that was skipped:

# Rerun from the point of failure of the previous invocation (dbt 1.6+)
dbt retry

# Or select explicitly
dbt build --select orders+
dbt build --select "result:fail+ result:error+" --state target_prev/

result: selection needs the run_results.json from the failed run in the --state folder. Copy target/ aside before you start rerunning.

Caution

Don't run dbt build --full-refresh to "clear" duplicates in an incremental model. It rebuilds the table from source, which may no longer hold the full history. Deduplicate in place with a targeted delete or a corrected merge, after taking a clone or Time Travel snapshot.

#Step 5: make tests cheaper to live with

• Put unique and not_null on every primary key. They're cheap and catch most fan-out bugs.
• Add where filters to tests on very large tables.
• Use dbt test --select "source:*" before models run, so bad raw data stops the pipeline at the start.
• Tag noisy tests and review them weekly:

dbt ls --resource-type test --select config.severity:warn

#Prevent it next time

• Turn on store_failures (or store_failures_as: view) for production so on-call starts with the rows, not the SQL.
• Give every test a deliberate severity. Use error only for things that would make a customer-facing number wrong.
• Use warn_if / error_if for tests where a small number of bad rows is normal.
• Send dbt warnings to a channel someone reads. A warning nobody sees is the same as no test.
• Review <schema>_dbt_test__audit growth and drop old tables if you use table mode.