Store test failures for analysis in dbt

Introduction

We store test failures to understand what went wrong in our data. This helps us fix problems and keep data trustworthy.

When you want to track which data rows fail quality checks over time.

When you need to share test failure details with your team for debugging.

When you want to analyze patterns in data errors to improve data pipelines.

When you want to keep a history of test failures for audits or reports.

Syntax

dbt

config(
  store_failures = true
)

select * from {{ ref('your_model') }}

Use store_failures = true inside the config() block of your test.

This tells dbt to save the rows that fail the test into a table for later analysis.

Examples

This example shows how to enable failure storage for a unique test on the customers model.

dbt

version: 2

models:
  - name: customers
    tests:
      - unique:
          config:
            store_failures: true

This test checks for null order_id values and stores failing rows.

dbt

select * from {{ ref('orders') }}
where order_id is null

-- config to store failures
{{ config(
  store_failures=true
) }}

Sample Program

This test finds rows in the orders table where order_id is missing. Because store_failures=true, dbt saves these rows in a separate table for review.

dbt

-- In your dbt test file (e.g., tests/not_null_order_id.sql)

{{ config(
  severity='error',
  store_failures=true
) }}

select * from {{ ref('orders') }}
where order_id is null

Important Notes

Stored failure tables are created in your target schema with a prefix like dbt_test.

Remember to clean up old failure tables if you run many tests to save space.

Failure tables help you see exactly which data rows caused the test to fail.

Summary

Use store_failures=true in your test config to save failing rows.

Stored failures help analyze and fix data quality issues.

Failure tables are saved in your database for easy access and review.