Writing custom generic tests

â–¶Changelog

dbt ships with Not Null, Unique, Relationships, and Accepted Values generic tests. (These used to be called "schema tests," and you'll still see that name in some places.) Under the hood, these generic tests are defined as test blocks (like macros) in a globally accessible dbt project. You can find the source code for these tests here.

info

There are tons of generic tests defined in open source packages, such as dbt-utils and dbt-expectations — the test you're looking for might already be here!

Generic tests with standard arguments

To define your own generic tests, simply create a test block called <test_name>. All generic tests should accept one or both of the standard arguments:

  • model: The resource on which the test is defined, templated out to its relation name. (Note that the argument is always named model, even when the resource is a source, seed, or snapshot.)
  • column_name: The column on which the test is defined. Not all generic tests operate on the column level, but if they do, they should accept column_name as an argument.

Here's an example of an is_even schema test that uses both arguments:

macros/test_is_even.sql
{% test is_even(model, column_name) %}
with validation as (
select
{{ column_name }} as even_field
from {{ model }}
),
validation_errors as (
select
even_field
from validation
-- if this is true, then even_field is actually odd!
where (even_field % 2) = 1
)
select *
from validation_errors
{% endtest %}

If this select statement returns zero records, then every record in the supplied model argument is even! If a nonzero number of records is returned instead, then at least one record in model is odd, and the test has failed.

To use this generic test, specify it by name in the tests property of a model, source, snapshot, or seed:

models/<filename>.yml
version: 2
models:
- name: users
columns:
- name: favorite_number
tests:
- is_even

In the above example, users will be passed to the is_even test as the model argument, and favorite_number will be passed in as the column_name argument.

Generic tests with additional arguments

The is_even test works without needing to specify any additional arguments. Other tests, like relationships, require more than just model and column_name. If your custom tests requires more than the standard argument, include those arguments in the test signature, as field and to are included below:

macros/test_relationships.sql
{% test relationships(model, column_name, field, to) %}
with parent as (
select
{{ field }} as id
from {{ to }}
),
child as (
select
{{ column_name }} as id
from {{ model }}
)
select *
from child
where id is not null
and id not in (select id from parent)
{% endmacro %}

When calling this test from a .yml file, supply the arguments to the test in a dictionary. Note that the standard arguments (model and column_name) are provided by the context, so you do not need to define them again.

models/<filename>.yml
version: 2
models:
- name: people
columns:
- name: account_id
tests:
- relationships:
to: ref('accounts')
field: id

Generic tests with default config values

It is possible to include a config() block in a generic test definition. Values set there will set defaults for all specific instances of that generic test, unless overridden within the specific instance's .yml properties.

macros/warn_if_null.sql
{% test warn_if_odd(model, column_name) %}
{{ config(severity = 'warn') }}
select *
from {{ model }}
where ({{ column_name }} % 2) = 1
{% endtest %}

Any time the warn_if_odd test is used, it will always have warning-level severity, unless the specific test overrides that value:

models/<filename>.yml
version: 2
models:
- name: users
columns:
- name: favorite_number
tests:
- warn_if_odd # default 'warn'
- name: other_number
tests:
- warn_if_odd:
severity: error # overrides

Customizing dbt's built-in tests

To change the way a built-in generic test works—whether to add additional parameters, re-write the SQL, or for any other reason—you simply add a test block named <test_name> to your own project. dbt will favor your version over the global implementation!

macros/<filename>.yml
{% test unique(model, column_name) %}
-- whatever SQL you'd like!
{% endtest %}

Examples

Here's some additional examples of custom generic ("schema") tests from the community: