dbt-utils package tests

Introduction

dbt-utils package tests help you check if your data models are correct and clean. They make sure your data is reliable before you use it.

When you want to check if your data columns have no missing values.

When you need to confirm that your data has unique rows or keys.

When you want to verify that your data matches expected values.

When you want to catch errors early in your data pipeline.

When you want to automate data quality checks in your dbt project.

Syntax

dbt

tests:
  - dbt_utils.not_null:
      column_name: your_column
  - dbt_utils.unique:
      column_name: your_column
  - dbt_utils.accepted_values:
      column_name: your_column
      values: ['value1', 'value2']

Use the tests key inside your model's YAML file to add tests.

Each test from dbt-utils has specific arguments like column_name or values.

Examples

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

dbt

tests:
  - dbt_utils.not_null:
      column_name: id

This test ensures that every email in the data is unique.

dbt

tests:
  - dbt_utils.unique:
      column_name: email

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

dbt

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

Sample Program

This YAML config adds dbt-utils tests to the customers model. It checks that customer_id is never empty and always unique. It also checks that status only has allowed values.

dbt

version: 2
models:
  - name: customers
    columns:
      - name: customer_id
        tests:
          - dbt_utils.not_null
          - dbt_utils.unique
      - name: status
        tests:
          - dbt_utils.accepted_values:
              values: ['active', 'inactive', 'pending']

Important Notes

dbt-utils tests are reusable and save time compared to writing custom SQL tests.

You can combine multiple tests on the same column for stronger data checks.

Tests run automatically when you run dbt test in your project.

Summary

dbt-utils package tests help keep your data clean and reliable.

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

Common tests include checking for nulls, uniqueness, and accepted values.