Tests

Related reference docs

• Test command
• Test properties
• Data test configurations

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

2. 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

3. 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_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

• Compiled SQL
• Templated SQL

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 (as defined by your test-paths config). 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?

Running tests on one model looks very similar to running a model: use the --models flag (or -m flag), followed by the name of the model:

dbt test --models customers

Check out the model selection syntax documentation for more operators and examples.

 One of my tests failed, how can I debug it?

To debug a failing test, find the SQL that dbt ran by:

• dbt Cloud:
  • Within the test output, click on the failed test, and then select "Details"
• dbt CLI:
  • Open the file path returned as part of the error message.
  • Navigate to the target/compiled/schema_tests directory for all compiled test queries

Copy the SQL into a query editor (in dbt Cloud, you can paste it into a new Statement), and run the query to find the records that failed.

 What tests should I add to my project?

We recommend that every model has a test on a primary key, that is, a column that is unique and not_null.

We also recommend that you test any assumptions on your source data. For example, if you believe that your payments can only be one of three payment methods, you should test that assumption regularly — a new payment method may introduce logic errors in your SQL.

In advanced dbt projects, we recommend using sources and running these source data-integrity tests against the sources rather than models.

 When should I run my tests?

You should run your tests whenever you are writing new code (to ensure you haven't broken any existing models by changing SQL), and whenever you run your transformations in production (to ensure that your assumptions about your source data are still valid).

 Is there a way to see, or store, the records from a failing test?

This is not currently possible in dbt, but something we hope to work on in the future — check out the open issue for more details.

 Can I store my data tests in a directory other than the `test` directory in my project?

By default, dbt expects your data test files to be located in the tests subdirectory of your project.

To change this, update the test-paths configuration in your dbt_project.yml file, like so:

dbt_project.yml

test-paths: ["data-tests"]

 How do I run tests on sources only?

Because the test selection syntax grew out of the model selection syntax (and pre-dates sources), the syntax here is a little unintuitive, but it is possible!

$ dbt test --models source:*

Check out the model selection syntax documentation for more operators and examples.

 Can I set test failure thresholds?

This is not currently supported in dbt natively.

Instead, consider:

• Setting the severity to warn, or:
• Writing a custom schema test that accepts a threshold argument (example)

 Can I test the uniqueness of two columns?

Yes, There's a few different options.

Consider an orders table that contains records from multiple countries, and the combination of ID and country code is unique:

order_id	country_code
1	AU
2	AU
...	...
1	US
2	US
...	...

Here are some approaches:

1. Create a unique key in the model and test that

models/orders.sql

select
  country_code || '-' || order_id as surrogate_key,
  ...

models/orders.yml

version: 2

models:
  - name: orders
    columns:
      - name: surrogate_key
        tests:
          - unique

2. Test an expression

models/orders.yml

version: 2

models:
  - name: orders
    tests:
      - unique:
          column_name: "(country_code || '-' || order_id)"

3. Use the dbt_utils.unique_combination_of_columns test

This is especially useful for large datasets since it is more performant. Check out the docs on packages for more information.

models/orders.yml

version: 2

models:
  - name: orders
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - country_code
            - order_id