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: 2
models:
- name: orders
columns:
- name: order_id
tests:
- unique
- not_null
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'returned']
- name: customer_id
tests:
- relationships:
to: ref('customers')
field: id

In plain English, these tests translate to:

  • unique: the order_id column in the orders model should be unique
  • not_null: the order_id column in the orders model should not contain null values
  • accepted_values: the status column in the orders should be one of 'placed', 'shipped', 'completed', or 'returned'
  • relationships: each customer_id in the orders model exists as an id in the customers 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

We've open sourced some useful schema tests in dbt-utils — skip ahead to the docs on packages to learn more!

Example

To add a schema test to your project:

  1. Add a .yml file to your models directory, e.g. models/schema.yml, with the following content (you may need to adjust the name: values for an existing model)
models/schema.yml
version: 2
models:
- name: orders
columns:
- name: order_id
tests:
- unique
- not_null
  1. Run the dbt test command:
$ dbt test
Found 3 models, 2 tests, 0 snapshots, 0 analyses, 130 macros, 0 operations, 0 seed files, 0 sources
17: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 successfully
Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2
  1. Check out the SQL dbt is running by either:
    • dbt Cloud: checking the Details tab.
    • dbt CLI: checking the target/compiled directory

Unique test

select count(*) as validation_errors
from (
select
order_id
from analytics.orders
where order_id is not null
group by order_id
having count(*) > 1
) validation_errors

Not null test

select
count(*) as validation_errors
from analytics.orders
where 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. Each .sql file contains one data test / one select statement:

tests/assert_total_payment_amount_is_positive.sql
-- 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 fail
select
order_id,
sum(amount) as total_amount
from {{ ref('fct_payments' )}}
group by 1
having not(total_amount >= 0)

Data tests are also run by the dbt test command. To only run data tests, run dbt test --data.

FAQs

 How do I test one model at a time?
 One of my tests failed, how can I debug it?
 What tests should I add to my project?
 When should I run my tests?
 Is there a way to see, or store, the records from a failing test?
 Can I store my data tests in a directory other than the `test` directory in my project?
 How do I run tests on sources only?
 Can I set test failure thresholds?
 Can I test the uniqueness of two columns?