Storing Failing Rows

Concepts covered: Data tests

A failing test prints a count. FAIL 17 assert_refund_within_line_total says 17 lines broke the rule and says nothing about which 17. The compiled query sits in target/compiled/, so the first instinct is to copy it into a query editor, strip the count wrapper and run it. By then the next build may have rebuilt the model and the rows have changed. dbt can instead write the failing rows to a table in the warehouse as part of running the test, so the evidence waits for whoever investigates.

The config is store_failures: true. On 1 test it sits under config: in YAML, or in the config() block of a singular test; across a project it goes in dbt_project.yml; and for 1 run, the flag dbt test --store-failures turns it on for every test the command runs.

models:
  - name: fct_order_lines
    columns:
      - name: merchant_id
        data_tests:
          - not_null:
              config:
                store_failures: true

Where the rows land

dbt writes each test's failing rows to a schema named after the target schema with _dbt_test__audit appended, so a build into analytics writes analytics_dbt_test__audit. The table takes the test's name: the not_null above becomes analytics_dbt_test__audit.not_null_fct_order_lines_merchant_id, and a singular test keeps its file name. Each run drops and recreates the table, so it holds the failures of the most recent run and nothing older. A test that passes still writes its table, with 0 rows, which is how a stored-failures test that ran can be told from one that never did.

What the table holds is whatever the test's inner SELECT returns, and that differs by test. not_null selects every column of each row with the null, so its audit table is a slice of the model. unique selects the duplicated value as unique_field with an n_records count, 1 row per duplicated value. A singular test writes the columns chosen in its SELECT, which is why section 1 asked for the key and the compared figures.

Reading the table is a query like any other, and joining it back to the model that fed the failing rows turns a count into a cause:

select
    f.order_line_key,
    f.order_id,
    s.source_system
from analytics_dbt_test__audit.not_null_fct_order_lines_merchant_id f
join analytics.stg_orders s
    on f.order_id = s.order_id

If all 17 rows share 1 source_system, the fix is in 1 feed. If they share 1 created_at date, a batch arrived broken. If they share nothing, the defect is in the model's own SQL.

The audit table is for people to read. Nothing can ref() it, no test reads it, and the next run replaces it, so a model built from it would stand on a table that changes under it. Storing failures also makes warnings reviewable: a severity: warn test with store_failures: true leaves its rows in the audit schema even though the build went green, and the owner of that warning can read them without rerunning anything.

Tables, views, or nothing

Writing a table for every test on every build has a cost: a unique test over 200 million rows, with a bug that duplicates half of them, writes 100 million rows to the audit schema before it fails. store_failures_as picks the form. table is the default. view stores the test's query instead of its rows, so the failures are computed when someone looks and reflect the model as it stands then. ephemeral stores nothing, and overrides a project-wide store_failures: true for 1 test.

data_tests:
  shop:
    +store_failures: true
    +store_failures_as: view
    marts:
      +store_failures_as: table

A view costs nothing to write but keeps no evidence. Once the model is rebuilt after a fix, the view shows today's rows, so the 17 that failed yesterday are gone. For marts where a failure has to be examined after the fact, a table keeps the evidence; for staging tests where the goal is a quick look, a view is enough. The schema config on a test moves its audit table, so schema: quality writes to analytics_quality instead of analytics_dbt_test__audit.

About this lesson

This section is part of the Testing: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.