Custom singular tests in dbt

Introduction

Custom singular tests help you check specific rules or conditions in your data. They make sure your data is correct and clean.

You want to check if a column always has unique values.

You need to confirm that a column never has null values.

You want to verify that a value in a column matches a specific condition, like being greater than zero.

You want to create a test that is not covered by built-in dbt tests.

You want to enforce business rules on your data quality.

Syntax

dbt

version: 2

models:
  - name: your_model_name
    tests:
      - your_custom_test_name:
          arg1: value1
          arg2: value2

# In tests/your_custom_test_name.sql
SELECT * FROM {{ model }}
WHERE NOT (your_condition_here)

Custom singular tests are SQL queries that return zero rows if the test passes.

Place your test SQL files inside the tests/ folder in your dbt project.

Examples

This runs a built-in unique test on the customer_id column.

dbt

version: 2

models:
  - name: customers
    tests:
      - unique_customer_id

This custom singular test checks that customer_id has no nulls.

dbt

# tests/not_null_customer_id.sql
SELECT * FROM {{ model }}
WHERE customer_id IS NULL

This runs a custom test positive_order_amount with an argument column_name.

dbt

version: 2

models:
  - name: orders
    tests:
      - positive_order_amount:
          column_name: order_amount

This test fails if any order amount is zero or negative.

dbt

# tests/positive_order_amount.sql
SELECT * FROM {{ model }}
WHERE {{ column_name }} <= 0

Sample Program

This example shows a custom singular test not_null_customer_id that checks for nulls in customer_id. The test query returns rows where customer_id is null, so the test fails if any such rows exist.

dbt

# dbt_project.yml
name: 'my_project'
version: '1.0'

# models/customers.yml
version: 2
models:
  - name: customers
    tests:
      - not_null_customer_id

# tests/not_null_customer_id.sql
SELECT * FROM {{ model }}
WHERE customer_id IS NULL

# Sample data in customers model:
# customer_id | name
# 1           | Alice
# 2           | Bob
# NULL        | Eve

# Running dbt test will run the custom test and find the NULL customer_id.

Important Notes

Custom singular tests must return zero rows to pass.

You can pass arguments to your tests via the YAML file.

Use Jinja templating to make your tests dynamic and reusable.

Summary

Custom singular tests are SQL queries that check specific data conditions.

They return zero rows if data passes the test, otherwise they fail.

You define them in tests/ and call them in your model YAML files.