dbt Tests

Testing is a core part of building reliable data models in dbt. This document explores:

• The four built-in generic tests
• How and when to use them
• Creating singular tests for custom logic
• Real-world examples and expected output

What Are Generic Tests in dbt?

Generic tests are pre-built reusable macros that you can apply to models, columns, or sources using simple YAML configurations.

dbt ships with 4 built-in generic tests:

Using Generic Tests: Syntax and Examples

Example Model: dim_users

-- models/dim_users.sql
SELECT 
  id, 
  email, 
  status 
FROM {{ ref('stg_users') }}

YAML Configuration

# models/dim_users.yml
 
version: 2
 
models:
  - name: dim_users
    columns:
      - name: id
        tests:
          - not_null
          - unique
 
      - name: status
        tests:
          - accepted_values:
              values: ['active', 'inactive', 'banned']
 
      - name: email
        tests:
          - not_null

Breakdown

not_null

Ensures column has no NULL values.

✅ Valid:

❌ Invalid:

unique

Ensures column values are distinct.

✅ Valid:

❌ Invalid:

accepted_values

Ensures all values are within the allowed list.

✅ Valid:

❌ Invalid:

relationships

Ensures a column's values exist in a referenced table.

- name: orders
  columns:
    - name: user_id
      tests:
        - relationships:
            to: ref('dim_users')
            field: id

This ensures every user_id in orders exists in dim_users.id.

What Are Singular Tests?

Singular tests are custom SQL queries that return failing rows. If any rows are returned, the test fails.

Directory and Naming

Place singular tests inside the tests/ folder.

tests/
└── test_users_have_email.sql

Example Test

-- tests/test_users_have_email.sql
 
SELECT *
FROM {{ ref('dim_users') }}
WHERE email IS NULL

Add to dbt_project.yml

If you follow standard naming, no special registration is required.

When to Use Generic vs. Singular

dbt Test Output

When you run dbt test, the output will include a summary:

$ dbt test

PASS tests.test_not_null_dim_users_id ....................... PASS in 0.13s
FAIL tests.test_users_have_email ............................ FAIL in 0.22s

Failure in test test_users_have_email (tests/test_users_have_email.sql)
  Got 3 results, expected 0

Each failing test will list the violating rows.

Tips and Best Practices

• Add generic tests via YAML in the same file as your model
• Write singular tests for business rules (e.g. "users must have an email")
• Include relationships tests for foreign keys
• Use --store-failures flag to output failed rows to tables for inspection

Summary

Run All Tests

To execute all configured tests:

dbt test

To run tests for a specific model:

dbt test --select dim_users

To store failed test output as a table:

dbt test --store-failures