About tests property
- Models
- Sources
- Seeds
- Snapshots
- Analyses
version: 2
models:
- name: <model_name>
tests:
- <test_name>:
<argument_name>: <argument_value>
config:
<test_config>: <config-value>
columns:
- name: <column_name>
tests:
- <test_name>
- <test_name>:
<argument_name>: <argument_value>
config:
<test_config>: <config-value>
version: 2
sources:
- name: <source_name>
tables:
- name: <table_name>
tests:
- <test_name>
- <test_name>:
<argument_name>: <argument_value>
config:
<test_config>: <config-value>
columns:
- name: <column_name>
tests:
- <test_name>
- <test_name>:
<argument_name>: <argument_value>
config:
<test_config>: <config-value>
version: 2
seeds:
- name: <seed_name>
tests:
- <test_name>
- <test_name>:
<argument_name>: <argument_value>
config:
<test_config>: <config-value>
columns:
- name: <column_name>
tests:
- <test_name>
- <test_name>:
<argument_name>: <argument_value>
config:
<test_config>: <config-value>
version: 2
snapshots:
- name: <snapshot_name>
tests:
- <test_name>
- <test_name>:
<argument_name>: <argument_value>
config:
<test_config>: <config-value>
columns:
- name: <column_name>
tests:
- <test_name>
- <test_name>:
<argument_name>: <argument_value>
config:
<test_config>: <config-value>
This feature is not implemented for analyses.
Related documentation
Description
The tests
property defines assertions about a column, table, or view. The property contains a list of generic tests, referenced by name, which can include the four built-in generic tests available in dbt. For example, you can add tests that ensure a column contains no duplicates and zero null values. Any arguments or configurations passed to those tests should be nested below the test name.
Once these tests are defined, you can validate their correctness by running dbt test
.
Out-of-the-box tests
There are four generic tests that are available out of the box, for everyone using dbt.
not_null
This test validates that there are no null
values present in a column.
version: 2
models:
- name: orders
columns:
- name: order_id
tests:
- not_null
unique
This test validates that there are no duplicate values present in a field.
The config and where clause are optional.
version: 2
models:
- name: orders
columns:
- name: order_id
tests:
- unique:
config:
where: "order_id > 21"
accepted_values
This test validates that all of the values in a column are present in a supplied list of values
. If any values other than those provided in the list are present, then the test will fail.
The accepted_values
test supports an optional quote
parameter which, by default, will single-quote the list of accepted values in the test query. To test non-strings (like integers or boolean values) explicitly set the quote
config to false
.
version: 2
models:
- name: orders
columns:
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'returned']
- name: status_id
tests:
- accepted_values:
values: [1, 2, 3, 4]
quote: false
relationships
This test validates that all of the records in a child table have a corresponding record in a parent table. This property is referred to as "referential integrity".
The following example tests that every order's customer_id
maps back to a valid customer
.
version: 2
models:
- name: orders
columns:
- name: customer_id
tests:
- relationships:
to: ref('customers')
field: id
The to
argument accepts a Relation – this means you can pass it a ref
to a model (e.g. ref('customers')
), or a source
(e.g. source('jaffle_shop', 'customers')
).
Additional examples
Testing an expression
Some tests require multiple columns, so it doesn't make sense to nest them under the columns:
key. In this case you can apply the test to the model (or source, seed or snapshot) instead:
version: 2
models:
- name: orders
tests:
- unique:
column_name: "country_code || '-' || order_id"
Define and use a custom generic test
If you've defined your own custom generic test, you can use that as the test_name
:
version: 2
models:
- name: orders
columns:
- name: order_id
tests:
- primary_key # name of my custom generic test
Check out the guide on writing a custom generic test for more information.