Failure Thresholds

Concepts covered: Test severity and blocking

The stg_users table holds 12 rows with a null email, all accounts created in 2016 before the signup form required one, and they will stay null for as long as the accounts exist. A plain not_null test on that column fails on every run, and a test that always fails teaches the team to stop reading it, so on the day 500 new rows arrive with null emails, the failure looks like every other day's. The severity setting from the intermediate lessons chooses warn or error for the whole test. A threshold goes further and makes the outcome depend on how many rows fail.

models:
  - name: stg_users
    columns:
      - name: email
        data_tests:
          - not_null:
              config:
                warn_if: ">0"
                error_if: ">12"

warn_if and error_if each hold a comparison against the number of failing rows, written as the operator and the number dbt will paste into SQL. The default for both is != 0, which is the behavior the reader already knows: any failing row is a failure. With the values above, 1 to 12 failing rows warn and 13 or more error. The 12 known rows keep the test yellow, and the 13th row, the first new one, turns it red and stops the models behind stg_users.

The query dbt sends

The test's own SELECT is unchanged. dbt wraps it in an outer query that counts its rows and evaluates both comparisons against the count, so 1 round trip returns the count and the 2 verdicts.

select
    count(*) as failures,
    count(*) > 0 as should_warn,
    count(*) > 12 as should_error
from (
    select
        email
    from analytics.stg_users
    where email is null
) dbt_internal_test

dbt reads should_error first, then should_warn, and the first true one is the status. With severity: warn on the test, dbt skips error_if and reads only warn_if, so a warn-severity test never errors however many rows fail; the thresholds only decide which runs stay silent.

Picking the number

The number comes from a query, never from a guess: run the test's SELECT with a count over it, and set error_if to that count so the next bad row is the first error. The known rows should also be the stored failures from the intermediate lessons, so the 12 rows and their account dates sit in a table anyone can query when the warning is questioned. When a backfill repairs 8 of the 12, the threshold drops to 4 the same day, because a tolerance of 12 over 4 real defects leaves 8 new defects unseen.

Counting something other than rows

A reconciliation test compares a mart's total XP per user with the sum of completions in staging. A mismatch of 1 point on 1 user is noise from an event that arrived late, and a mismatch of 600 points on 1 user is a broken join, but the count of mismatched users is 1 in both cases. fail_calc replaces the count(*) in the wrapper with any aggregate over the test's rows, and the thresholds then compare against that figure instead.

{{ config(fail_calc = 'coalesce(sum(abs(xp_diff)), 0)', warn_if = '> 0', error_if = '> 500') }}

with mart as (
    select
        user_id,
        total_xp
    from {{ ref('fct_user_xp') }}
),
staging as (
    select
        user_id,
        sum(xp) as total_xp
    from {{ ref('stg_lesson_completions') }}
    group by user_id
)
select
    mart.user_id,
    mart.total_xp - staging.total_xp as xp_diff
from mart
join staging
    on mart.user_id = staging.user_id
where mart.total_xp <> staging.total_xp

sum over 0 rows is null, and null compared with 500 is neither true nor false, so the coalesce turns a clean run into 0 failures rather than an undefined one. The wrapper's first line becomes coalesce(sum(abs(xp_diff)), 0) as failures, and dbt reports that figure as the failure count, so a warning that reads 37 failures means 37 XP points of drift across all users. The test's name carries that meaning, since the log line will not. The 500 is measured too: the stored failures of the last 30 runs show late events never pushed the daily sum past 480 points, so 500 sits just above the worst day the known noise produced.

About this lesson

This section is part of the Testing: Advanced 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.