Testing model outputs in dbt

Introduction

Testing model outputs helps you check if your data models give correct and expected results. It stops errors before they cause problems.

After creating a new data model to make sure it works right.

When updating a model to confirm changes did not break anything.

Before sharing data with others to ensure accuracy.

To catch missing or duplicate data in your outputs.

When automating data pipelines to keep data quality consistent.

Syntax

dbt

version: 2
models:
  - name: your_model_name
    tests:
      - unique:
          column_name: id
      - not_null:
          column_name: id
      - accepted_values:
          column_name: status
          values: ['active', 'inactive']

Tests are defined in your model's schema.yml file.

Common tests include unique, not_null, and accepted_values.

Examples

This tests that customer_id is unique and email is never empty.

dbt

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

This checks that order_status only has allowed values.

dbt

models:
  - name: orders
    tests:
      - accepted_values:
          column_name: order_status
          values: ['pending', 'shipped', 'delivered']

Sample Program

This example shows a sales model with tests to ensure sale_id is unique and not null, sale_amount is not null, and sale_status only contains allowed values.

dbt

version: 2
models:
  - name: sales
    description: "Sales data model"
    columns:
      - name: sale_id
        tests:
          - unique
          - not_null
      - name: sale_amount
        tests:
          - not_null
      - name: sale_status
        tests:
          - accepted_values:
              values: ['completed', 'pending', 'cancelled']

Important Notes

Tests help catch data problems early.

Use descriptive test names to understand failures quickly.

Run tests often during development and before deployment.

Summary

Testing model outputs ensures your data is accurate and reliable.

Define tests in your model's schema.yml file using simple syntax.

Common tests check uniqueness, null values, and allowed values.