Unit testing dbt models

Introduction

Testing dbt models helps you check if your data transformations work correctly. It finds mistakes early so your data is reliable.

When you create a new dbt model and want to make sure it returns correct data.

When you change an existing model and want to confirm it still works as expected.

When you want to catch data quality issues before they affect reports or dashboards.

When you want to automate checks to save time and avoid manual errors.

Syntax

dbt

version: 2
models:
  - name: your_model_name
    tests:
      - unique:
          column_name: id
      - not_null:
          column_name: id
      - relationships:
          to: ref('other_model')
          field: id

This syntax is written in YAML inside your dbt project under a schema.yml file.

Tests like unique and not_null check basic data quality.

Examples

This example tests that the customers model has unique and non-null values in its primary key.

dbt

models:
  - name: customers
    tests:
      - unique:
          column_name: customer_id
      - not_null:
          column_name: customer_id

This test checks that every customer_id in orders exists in the customers model.

dbt

models:
  - name: orders
    tests:
      - relationships:
          to: ref('customers')
          field: customer_id

This test ensures the category column in products only contains allowed values.

dbt

models:
  - name: products
    tests:
      - accepted_values:
          column_name: category
          values: ['electronics', 'furniture', 'clothing']

Sample Program

This schema.yml file defines tests for the sales model. It checks that sale_id is unique and not null, and that customer_id matches an existing customer.

dbt

version: 2
models:
  - name: sales
    columns:
      - name: sale_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: customer_id

Important Notes

dbt tests run automatically with dbt test command.

You can write custom tests if built-in ones don't cover your needs.

Keep tests small and focused on one thing for easier debugging.

Summary

Testing in dbt checks your data models for correctness and quality.

Tests are defined in YAML files using simple syntax like unique and not_null.

Running dbt test helps catch errors early and keeps data trustworthy.