Skip to main content

Store Failures

When a dbt test fails, the CLI prints a red error message stating that the test failed, and it might tell you how many rows failed (e.g., "Got 50 results, configured to fail if != 0").

But if you are managing a massive dataset for an Indian delivery company, knowing that "50 packages have a negative weight" is not enough. You need to know which 50 packages failed so you can contact the warehouse and fix them.

To do this, you use the store_failures configuration.

Topics Covered​

  • Enabling store_failures
  • Where are the failures stored?
  • Querying the Audit Table
  • Storage Cost Implications

1. Enabling store_failures​

There are two ways to tell dbt to store the failed rows as a physical table in your data warehouse.

Method 1: The CLI Flag (Ad-hoc)​

If you are debugging locally and just want to quickly see the failures for a specific test run, you can append a flag to your terminal command:

dbt test --store-failures

Method 2: The YAML Config (Production)​

If you want failures to be stored automatically every time the nightly pipeline runs, you configure it in your schema.yml or dbt_project.yml.

# models/marts/schema.yml
models:
- name: fct_deliveries
columns:
- name: package_weight_kg
tests:
- is_positive: # Custom test ensuring weight > 0
config:
store_failures: true

2. Where are the failures stored?​

By default, dbt does not store the failing rows in your target schema (e.g., BEEMS_PROD.MARTS). That would clutter your clean presentation layer!

Instead, dbt creates a dedicated audit schema. The default naming convention is: [your_target_schema]_dbt_test__audit

So, if Kavya is running dbt locally with a target schema of DEV_KAVYA, her failed tests will be stored in a schema named DEV_KAVYA_DBT_TEST__AUDIT.

Customizing the Audit Schema​

You can override this default behavior in your dbt_project.yml file to store all failures in a centralized schema, like DATA_QUALITY_ALERTS.

# dbt_project.yml
tests:
+store_failures: true
+schema: data_quality_alerts

3. Querying the Audit Table​

Once store_failures is enabled and the test fails, dbt creates a table in the audit schema named after the test.

For the example above, the table might be named: is_positive_fct_deliveries_package_weight_kg.

You can now open a Snowflake worksheet and query the exact bad data!

SELECT * 
FROM BEEMS_PROD.MARTS_DBT_TEST__AUDIT.is_positive_fct_deliveries_package_weight_kg;

Output:

package_idpackage_weight_kgcourier_name
PKG-9901-5.50Arjun
PKG-9902-2.00Priya

You can now email Arjun and Priya and ask them why their scanners are recording negative weights!

4. Storage Cost Implications​

Every time you run dbt test with store_failures: true, dbt runs a CREATE OR REPLACE TABLE command for that specific test audit table.

If your test logic is flawed and it accidentally flags 50 million rows as failures, dbt will attempt to copy 50 million rows into the audit schema! This can consume massive amounts of Snowflake compute credits and storage space.

Best Practice: Combine store_failures: true with the limit config.

tests:
- not_null:
config:
store_failures: true
limit: 100 # Only store a sample of 100 bad rows for debugging!

This ensures that even if millions of rows fail, you only pay to store a tiny sample of them for root-cause analysis.

Summary​

  • store_failures: true saves the exact rows that violated a data test into a physical table.
  • You can enable it ad-hoc via the CLI (--store-failures) or permanently in YAML.
  • By default, failures are saved in an audit schema (e.g., schema_dbt_test__audit).
  • Protect against massive storage costs by combining store_failures with a limit configuration.