Tests
Related reference docs
Getting started
Tests are assertions you make about your models and other resources in your dbt project (e.g. sources, seeds and snapshots). When you run dbt test
, dbt will tell you if each test in your project passes or fails.
There are two type of tests:
- schema tests (more common): applied in YAML, returns the number of records that do not pass an assertion — when this number is 0, all records pass, therefore, your test passes
- data tests: specific queries that return 0 records
Defining tests is a great way to confirm that your code is working correctly, and helps prevent regressions when your code changes.
Creating your first tests
If you're new to dbt, we recommend that you check out our Getting Started Tutorial to build your first dbt project with models and tests.
Schema tests
Schema tests are added as properties for an existing model (or source, seed, or snapshot).
These properties are added in .yml
files in the same directory as your resource.
info
If this is your first time working with adding properties to a resource, check out the docs on declaring properties.
Out of the box, dbt ships with the unique
, not_null
, accepted_values
and relationships
tests. Here's a full example:
version: 2models:- name: orderscolumns:- name: order_idtests:- unique- not_null- name: statustests:- accepted_values:values: ['placed', 'shipped', 'completed', 'returned']- name: customer_idtests:- relationships:to: ref('customers')field: id
In plain English, these tests translate to:
unique
: theorder_id
column in theorders
model should be uniquenot_null
: theorder_id
column in theorders
model should not contain null valuesaccepted_values
: thestatus
column in theorders
should be one of'placed'
,'shipped'
,'completed'
, or'returned'
relationships
: eachcustomer_id
in theorders
model exists as anid
in thecustomers
table (also known as referential integrity)
Behind the scenes, dbt constructs a select
query for each schema test. These queries return the number 0
when your assertion is true, otherwise the test fails
You can find more information about these tests, and additional configurations (including severity
and tags
) in the reference section.
You can also write your own custom schema tests to use in your dbt project — check out the guide for more information.
info
Example
To add a schema test to your project:
- Add a
.yml
file to yourmodels
directory, e.g.models/schema.yml
, with the following content (you may need to adjust thename:
values for an existing model)
version: 2models:- name: orderscolumns:- name: order_idtests:- unique- not_null
- Run the
dbt test
command:
$ dbt testFound 3 models, 2 tests, 0 snapshots, 0 analyses, 130 macros, 0 operations, 0 seed files, 0 sources17:31:05 | Concurrency: 1 threads (target='learn')17:31:05 |17:31:05 | 1 of 2 START test not_null_order_order_id..................... [RUN]17:31:06 | 1 of 2 PASS not_null_order_order_id........................... [PASS in 0.99s]17:31:06 | 2 of 2 START test unique_order_order_id....................... [RUN]17:31:07 | 2 of 2 PASS unique_order_order_id............................. [PASS in 0.79s]17:31:07 |17:31:07 | Finished running 2 tests in 7.17s.Completed successfullyDone. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2
- Check out the SQL dbt is running by either:
- dbt Cloud: checking the Details tab.
- dbt CLI: checking the
target/compiled
directory
Unique test
- Compiled SQL
- Templated SQL
select count(*) as validation_errorsfrom (selectorder_idfrom analytics.orderswhere order_id is not nullgroup by order_idhaving count(*) > 1) validation_errors
Not null test
- Compiled SQL
- Templated SQL
selectcount(*) as validation_errorsfrom analytics.orderswhere order_id is null
Data tests
A data test is a select
statement that returns 0
records when the test is successful. Note: this differs from schema tests that return the number 0
.
Data tests are defined in .sql
files, typically in your tests
directory (as defined by your test-paths
config). Each .sql
file contains one data test / one select
statement:
-- Refunds have a negative amount, so the total amount should always be >= 0.-- Therefore return records where this isn't true to make the test failselectorder_id,sum(amount) as total_amountfrom {{ ref('fct_payments' )}}group by 1having not(total_amount >= 0)
Data tests are also run by the dbt test
command. To only run data tests, run dbt test --data
.