Skip to main content

Test Configs

By default, when you apply a test like unique or not_null to a column, dbt runs that test against every single row in the entire table.

However, sometimes you need more granular control over how and when a test executes. For example, what if you have a massive table of historical transactions, and you only want to test the data from the last 7 days to save compute costs? Or what if a column is allowed to be NULL, but only if the order status is "Cancelled"?

You can solve these edge cases using Test Configs.

Topics Covered​

  • Applying Configs to Tests
  • The where Config
  • Storing Test Failures

1. Applying Configs to Tests​

To pass configurations into a test, you must change the syntax in your schema.yml file. Instead of just listing the test name (- not_null), you convert the test into a dictionary and add a config: block underneath it.

models:
- name: fct_orders
columns:
- name: order_id
tests:
- unique:
config:
severity: error # We learned about this in the previous module

2. The where Config​

The most powerful and frequently used test config is the where config. It allows you to append a WHERE clause to the SQL query that dbt generates for the test.

Scenario A: Filtering for recent data​

Let's assume our fct_orders table contains 10 years of data. We don't want to scan 10 years of data every night just to run a not_null test. We only want to test the new rows.

models:
- name: fct_orders
columns:
- name: customer_id
tests:
- not_null:
config:
# Only test rows created in the last 3 days!
where: "created_at >= CURRENT_DATE - 3"

When dbt executes this test, the SQL will look like: SELECT * FROM fct_orders WHERE customer_id IS NULL AND created_at >= CURRENT_DATE - 3.

Scenario B: Conditional Testing​

Assume an Indian e-commerce company has a rule: "The delivery_date can be NULL if the package is still 'In Transit', but it MUST NOT be NULL if the status is 'Delivered'."

You can enforce this complex business logic using the where config!

models:
- name: fct_deliveries
columns:
- name: delivery_date
tests:
- not_null:
config:
# Only apply the not_null test to Delivered packages!
where: "status = 'DELIVERED'"

3. Storing Test Failures​

By default, when a test fails, dbt prints a red error in the terminal, but the actual failing rows are lost. If 500 rows failed the not_null test, how do you find those 500 rows to fix them?

You can use the store_failures config.

models:
- name: dim_customers
columns:
- name: email_address
tests:
- not_null:
config:
store_failures: true

If you set store_failures: true, dbt will automatically create a physical table in your data warehouse containing all the rows that failed the test! By default, dbt creates a schema called dbt_test__audit (or similar, depending on your target schema) and saves the results there.

This allows Data Engineers (like Arjun or Kavya) to write a simple SQL SELECT * FROM ... query the next morning to inspect exactly which rows violated the rule.

4. Project-Level Test Configs​

If you want to apply a configuration to every single test in your project (for example, you want store_failures: true everywhere), do not copy and paste it 500 times. Use your dbt_project.yml file!

# dbt_project.yml
tests:
beems_retail: # Your project name
+store_failures: true

Summary​

  • Test Configs allow you to modify the behavior of individual data tests.
  • Use the where config to conditionally run tests on a subset of data (e.g., recent rows only, or specific statuses).
  • Use store_failures: true to automatically save failing rows to a table in the data warehouse for debugging.
  • Apply configs globally using the tests: block in your dbt_project.yml.