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_modelwhere 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_subquerywhere my_column is null
Examples
- Specific test
- One-off test
- Generic test block
- Project level
Configure a specific instance of a generic (schema) test:
models/<filename>.yml
version: 2models:- name: large_tablecolumns:- name: my_columntests:- 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 thewhere
config is not defined (ref()
orsource()
)(select * from {{ relation }} where {{ where }}) dbt_subquery
when thewhere
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