Testing & Documentation

Testing and documenting your dbt models is essential for maintaining data quality and helping others understand your data transformations.

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

You can use data tests to improve the integrity of the SQL in each model by making assertions about the results generated. Out of the box, you can test whether a specified column in a model only contains non-null values, unique values, or values that have a corresponding value in another model (for example, a customer_id for an order corresponds to an id in the customers model), and values from a specified list. You can extend data tests to suit business logic specific to your organization – any assertion that you can make about your model in the form of a select query can be turned into a data test.

Data tests return a set of failing records. Generic data tests (f.k.a. schema tests) are defined using test blocks.

Like almost everything in dbt, data tests are SQL queries. In particular, they are select statements that seek to grab "failing" records, ones that disprove your assertion. If you assert that a column is unique in a model, the test query selects for duplicates; if you assert that a column is never null, the test seeks after nulls. If the data test returns zero failing rows, it passes, and your assertion has been validated.

2.1 Singular Data Tests

The simplest way to define a data test is by writing the exact SQL that will return failing records. We call these "singular" data tests, because they're one-off assertions usable for a single purpose.

These tests are defined in .sql files, typically in your tests directory (as defined by your test-paths config). You can use Jinja (including ref and source) in the test definition, just like you can when creating models. Each .sql file contains one select statement, and it defines one data test:

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 total_amount < 0 to make the test fail.
select
    order_id,
    sum(amount) as total_amount
from {{ ref('fct_payments') }}
group by 1
having total_amount < 0

Notes:

Omit semicolons (;) at the end of the SQL statement in your singular test files, as they can cause your test to fail.
Singular tests placed in the tests directory are automatically executed when running dbt test.
Don't reference singular tests in model_name.yml, as they are not treated as generic tests or macros, and doing so will result in an error.

To add a description to a singular test in your project, add a .yml file to your tests directory:

tests/schema.yml

version: 2
data_tests:
  - name: assert_total_payment_amount_is_positive
    description: >
      Refunds have a negative amount, so the total amount should always be >= 0.
      Therefore return records where total amount < 0 to make the test fail.

2.2 Generic Data Tests

Certain data tests are generic: they can be reused over and over again. A generic data test is defined in a test block, which contains a parametrized query and accepts arguments. It might look like:

{% test not_null(model, column_name) %}

    select *
    from {{ model }}
    where {{ column_name }} is null

{% endtest %}

You'll notice that there are two arguments, model and column_name, which are then templated into the query. This is what makes the test "generic": it can be defined on as many columns as you like, across as many models as you like, and dbt will pass the values of model and column_name accordingly.

Out of the box, dbt ships with four generic data tests already defined: unique, not_null, accepted_values and relationships. Here's a full example using those tests on an orders model:

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 data 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)

To run all tests in your project, use the dbt test command:

dbt test

To run tests for a specific model:

dbt test --select model_name

To run a specific test:

dbt test --select test_name

Example Output:

$ 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

Documentation is a crucial part of any dbt project. It helps users understand the purpose and structure of your models, sources, and other resources.

4.1 Adding Documentation

You can add documentation to your dbt project in two ways:

YAML Descriptions

Add descriptions to your models and columns in your .yml files:

version: 2

models:
  - name: orders
    description: This table contains order information with one row per order.
    columns:
      - name: order_id
        description: Primary key of the orders table
        tests:
          - unique
          - not_null
      - name: customer_id
        description: Foreign key to the customers table
        tests:
          - relationships:
              to: ref('customers')
              field: id

Markdown Documentation

For more detailed documentation, you can create markdown files in your models directory:

models/orders.md

# Orders

This table contains order information with one row per order.

## Business Rules

* Orders can have multiple line items
* Orders can be in one of four statuses: placed, shipped, completed, or returned
* Returned orders are considered canceled and are not included in sales totals

## Technical Details

* This model is refreshed daily at 4 AM UTC
* Source data comes from the e-commerce platform's orders API
* Historical data is available from January 1, 2020 onwards

4.2 Generating Documentation

To generate documentation for your dbt project, run:

dbt docs generate

This command creates a website with your project's documentation. To view the documentation, run:

dbt docs serve

This will start a local server and open your browser to view the documentation.

Documentation Features:

Model, source, and column descriptions
Data lineage visualization (DAG)
Test coverage information
SQL code for each model
Search functionality

Testing & Documentation

2.1 Singular Data Tests

Notes:

2.2 Generic Data Tests

In plain English, these data tests translate to:

Example Output:

4.1 Adding Documentation

YAML Descriptions

Markdown Documentation

4.2 Generating Documentation

Documentation Features:

5.1 Testing Best Practices

5.2 Documentation Best Practices

Next Steps

Model Creation

dbt Basics