where

Changelog

Definition

Filter the resource being tested (model, source, seed, or snapshot).

The where condition is templated into the test query by replacing the resource reference with a subquery. For instance, a not_null test may look like:

select *
from my_model
where my_column is null

If the where config is set to where date_column = current_date, then the test query will be updated to:

select *
from (select * from my_model where date_column = current_date) dbt_subquery
where my_column is null

Examples

Configure a specific instance of a generic (schema) test:

models/<filename>.yml
version: 2
models:
- name: large_table
columns:
- name: my_column
tests:
- accepted_values:
values: ["a", "b", "c"]
config:
where: "date_column = current_date"

Custom logic

As of v0.21, dbt defines a get_where_subquery macro.

dbt replaces {{ model }} in generic test definitions with {{ get_where_subquery(relation) }}, where relation is a ref() or source() for the resource being tested. The default implementation of this macro returns:

  • {{ relation }} when the where config is not defined (ref() or source())
  • (select * from {{ relation }} where {{ where }}) dbt_subquery when the where config is defined

You can override this behavior by:

  • Defining a custom get_where_subquery in your root project
  • Defining a custom <adapter>__get_where_subquery dispatch candidate in your package or adapter plugin