Built-in tests (unique, not_null, accepted_values, relationships) in dbt

Introduction

Built-in tests help check your data for common problems automatically. They make sure your data is clean and reliable.

When you want to make sure each row in a column is different (unique).

When you need to confirm there are no missing values in important columns (not_null).

When you want to check if values in a column are only from a list of allowed options (accepted_values).

When you want to verify that values in one table match values in another table (relationships).

Syntax

dbt

tests:
  - unique:
      column_name: your_column
  - not_null:
      column_name: your_column
  - accepted_values:
      column_name: your_column
      values: [allowed_value1, allowed_value2]
  - relationships:
      column_name: your_column
      to:
        table: other_table
        column: other_column

Each test is defined under the tests key in your model's YAML file.

You specify the column to test and any extra parameters like allowed values or related tables.

Examples

This test checks that the id column has no duplicate values.

dbt

tests:
  - unique:
      column_name: id

This test ensures the email column has no missing values.

dbt

tests:
  - not_null:
      column_name: email

This test checks that status only contains these three allowed values.

dbt

tests:
  - accepted_values:
      column_name: status
      values: ['active', 'inactive', 'pending']

This test verifies that every user_id in this table exists in the users.id column.

dbt

tests:
  - relationships:
      column_name: user_id
      to:
        table: users
        column: id

Sample Program

This YAML config adds built-in tests to the orders model. It checks that order_id is unique and not null, customer_id is not null and exists in the customers table, and status only has allowed values.

dbt

version: 2
models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to:
                table: customers
                column: id
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled']

Important Notes

Built-in tests are easy to add and help catch data issues early.

Tests run automatically when you run dbt test.

Failing tests mean you should check your data or logic.

Summary

Built-in tests check common data quality issues like uniqueness and missing values.

You add tests in your model YAML files under the tests section.

Running dbt test runs all tests and shows if your data is healthy.