tests field is used to assert properties of a column or table.
Once these tests are defined, you can validate their correctness by running
This test validates that there are no
null values present in a column.
version: 2models:- name: orderscolumns:- name: order_idtests:- not_null
This test validates that there are no duplicate values present in a field.
version: 2models:- name: orderscolumns:- name: order_idtests:- unique
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.
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
version: 2models:- name: orderscolumns:- name: statustests:- accepted_values:values: ['placed', 'shipped', 'completed', 'returned']- name: status_idtests:- accepted_values:values: [1, 2, 3, 4]quote: false
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
version: 2models:- name: peoplecolumns:- name: customer_idtests:- relationships:to: ref('customers')field: id
to argument accepts a Relation – this means you can pass it a
ref to a model (e.g.
ref('customers')), or a
The "severity" of a test can be configured by supplying the
severity configuration option in the test specification. The
severity option can be one of
warn is supplied, then dbt will log a warning for any failing tests, but the test will still be considered passing. This configuration is useful for tests in which a failure does not imply that action is required.
version: 2models:- name: orderscolumns:- name: order_idtests:- unique:severity: warn
severity level is not provided, then tests are run with the
error severity level. There is currently no way to set all tests to the "warn" severity by default.
severity config can be applied to any schema test. They can also be applied to data tests:
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: 2models:- name: orderstests:- unique:column_name: "country_code || '-' || order_id"
Advanced: define and use a custom schema test
If you define your own custom schema test, you can use that as the
version: 2models:- name: orderscolumns:- name: order_idtests:- primary_key
Check out the guide on writing a custom schema test for more information.