Skip to main content

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#

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