Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 41 ยท Tests and Documentation

Test Severity, store_failures and Selection

Control how dbt tests behave: warn vs error, thresholds, where filters, storing failing rows, and selecting tests by model, tag or test type.

Once you have dozens of tests, a plain pass/fail is not enough. Some failures should stop the pipeline; others should only raise a flag. You will want to look at the failing rows, not just count them. And you will want to run only some tests at a time. dbt has config options for all of this.

Severity: error or warn

By default a failing test has severity error: dbt test exits with a non-zero code and dbt build skips the models downstream. Set severity: warn to report the problem without blocking:

YAML
columns:
  - name: status
    data_tests:
      - accepted_values:
          arguments:
            values: ['pending', 'shipped', 'delivered', 'cancelled']
          config:
            severity: warn

Output then reads WARN 2 accepted_values_stg_orders_status instead of FAIL. Use warnings for data quality you want to watch but not enforce yet, such as a new source column that is still being cleaned up.

Thresholds: warn_if and error_if

Real data is rarely perfect. You can allow a few bad rows with warn_if and error_if, which take a SQL-like comparison on the failure count:

YAML
- not_null:
    config:
      warn_if: ">0"
      error_if: ">100"

Here one failing row warns and more than 100 errors. The defaults are warn_if: ">0" and error_if: ">0". The thresholds are evaluated against the number of failing rows, which you can simulate:

SQL Counting failures like dbt does ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The rule in the query ("delivered orders have free shipping") is made up for the demo; the point is the mapping from a failure count to a result.

Limiting the scope with where and limit

Scanning a 5-billion-row table for every test run is wasteful. The where config adds a filter to the test query, and limit caps how many failing rows are returned (the count then stops at the limit):

YAML
- unique:
    config:
      where: "ordered_at >= current_date - interval '3 days'"
      limit: 500

Only recent rows are checked, which also keeps the test about new data.

store_failures: look at the bad rows

A count tells you something broke, but not what. store_failures: true makes dbt save the failing rows into a table in your warehouse (schema <your schema>_dbt_test__audit by default):

YAML
data_tests:
  - not_null:
      config:
        store_failures: true

You can set it for every test in dbt_project.yml:

YAML
data_tests:
  +store_failures: true
  +schema: test_failures

After the run, query the table named after the test. The stored table is just the test's SELECT, so you can reproduce it:

SQL What a stored failures table contains

Press Run (or Ctrl + Enter) to see the result here.

Engineers often point a dashboard or an alert at those tables. For store_failures on the command line, use dbt test --store-failures.

Selecting tests

Selection works like selecting models, with a few extras:

Terminal
dbt test                                  # everything
dbt test --select stg_orders              # tests on one model
dbt test --select stg_orders+             # model and everything downstream
dbt test --select test_type:generic       # only generic tests
dbt test --select test_type:singular      # only singular tests
dbt test --select source:shop             # tests on a source
dbt test --select "tag:nightly"           # by tag
dbt test --select assert_order_totals_are_not_negative
dbt test --exclude tag:slow

Give tests a tag in config to group them, for example tags: ['critical'], then run only critical checks after every deploy:

YAML
- unique:
    config:
      tags: ['critical']
flowchart LR
  T["Test result"] --> C{"Failure count"}
  C -->|"under warn_if"| P["PASS"]
  C -->|"over warn_if"| W["WARN: run continues"]
  C -->|"over error_if"| E["ERROR: downstream skipped"]

Thresholds turn a count into a decision.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does severity: warn change?

    A failed test with severity warn shows WARN and does not produce a non-zero exit code for that reason.
  2. 2. What does store_failures: true do?

    The test's SELECT result is saved to an audit table so you can inspect the rows.
  3. 3. Which selector runs only tests tagged nightly?

    Tag selection uses the tag: method with --select.
  4. 4. What is the purpose of the where config on a test?

    It adds a filter to the test query, which reduces cost and focuses on new data.

Summary

  • severity, warn_if and error_if map failure counts to warn or error.
  • where and limit keep tests cheap on large tables.
  • store_failures saves the failing rows so you can debug them.
  • Select tests by model, source, type, tag or name; exclude with --exclude.

Next: Documenting Models with YAML and Doc Blocks