dbt Singular Tests

Singular tests are custom SQL files that check complex data quality rules that the four built-in generic tests cannot express. You write a SELECT statement that returns failing rows. If the query returns any rows, the test fails. If it returns zero rows, the test passes. Singular tests live in the tests/ folder of your project.

When to Use Singular Tests

Use singular tests for business rules that involve multiple columns, multiple tables, or mathematical constraints. Generic tests cover simple column-level checks. Singular tests cover everything else.

Generic tests handle:              Singular tests handle:
------------------------           ---------------------------
Column is not null                 Revenue must be positive
Column values are unique           Refund amount cannot exceed order amount
Values match an allowed list       Each order must have at least one line item
Foreign key exists                 Shipping date must be after order date

Writing Your First Singular Test

Create a SQL file inside the tests/ folder. The file name becomes the test name. Write a SELECT that returns only the rows that fail the check:

-- tests/assert_order_amount_positive.sql

-- This test fails if any order has a zero or negative amount
select
    order_id,
    amount_dollars
from {{ ref('stg_orders') }}
where amount_dollars <= 0

Running dbt test executes this SQL. If any orders have zero or negative amounts, those rows come back and dbt reports a test failure with the row count.

More Singular Test Examples

Test: Refund Cannot Exceed Order Total

-- tests/assert_refund_lte_order_amount.sql

select
    r.refund_id,
    r.order_id,
    r.refund_amount,
    o.amount_dollars
from {{ ref('stg_refunds') }} r
join {{ ref('stg_orders') }}  o on r.order_id = o.order_id
where r.refund_amount > o.amount_dollars

Test: Shipping Date After Order Date

-- tests/assert_ship_date_after_order_date.sql

select
    order_id,
    order_date,
    ship_date
from {{ ref('stg_orders') }}
where ship_date < order_date
  and ship_date is not null

Test: Every Order Has At Least One Line Item

-- tests/assert_order_has_line_items.sql

select
    o.order_id
from {{ ref('stg_orders') }} o
left join {{ ref('stg_order_items') }} li
  on o.order_id = li.order_id
where li.order_id is null

Test: Revenue Matches Sum of Line Items

-- tests/assert_order_total_matches_line_items.sql

select
    o.order_id,
    o.amount_dollars                         as header_total,
    sum(li.quantity * li.unit_price)         as calculated_total,
    abs(o.amount_dollars - sum(li.quantity * li.unit_price)) as discrepancy
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} li on o.order_id = li.order_id
group by o.order_id, o.amount_dollars
having abs(o.amount_dollars - sum(li.quantity * li.unit_price)) > 0.01

Running Singular Tests

# Run all tests including singular tests
dbt test

# Run only singular tests
dbt test --select test_type:singular

# Run a specific singular test by file name
dbt test --select assert_order_amount_positive

Adding Configuration to Singular Tests

You can add a config block at the top of a singular test file to control severity, tags, and other settings:

-- tests/assert_refund_lte_order_amount.sql
{{ config(severity='warn', tags=['finance']) }}

select
    r.refund_id,
    r.refund_amount,
    o.amount_dollars
from {{ ref('stg_refunds') }} r
join {{ ref('stg_orders') }}  o on r.order_id = o.order_id
where r.refund_amount > o.amount_dollars

Singular Tests vs Generic Tests: Decision Guide

Scenario                                    Test Type
----------------------------------------    ----------
Column must not be null                     Generic (not_null)
Column values must be unique                Generic (unique)
Column values must match a list             Generic (accepted_values)
Foreign key must exist in another table     Generic (relationships)
Two columns have an invalid relationship    Singular
Aggregated value must match another         Singular
Cross-table business rule                   Singular
Time-sequence constraint                    Singular

Organizing Singular Tests

As your project grows, organize singular test files into subfolders:

tests/
  finance/
    assert_refund_lte_order_amount.sql
    assert_revenue_is_positive.sql
  fulfillment/
    assert_ship_date_after_order_date.sql
    assert_order_has_line_items.sql
  integrity/
    assert_order_total_matches_line_items.sql

Tag tests by category so you can run them in groups:

{{ config(tags=['finance']) }}

# Run all finance tests
dbt test --select tag:finance

Debugging a Failing Singular Test

When a singular test fails, dbt tells you how many rows failed. Run the test SQL directly in your warehouse to see the actual failing rows:

-- Run the test SQL manually in your warehouse:
select
    order_id,
    amount_dollars
from dbt_dev.stg_orders
where amount_dollars <= 0

-- Returns:
order_id | amount_dollars
---------|---------------
1047     | 0.00
2198     | -5.99

Now you see exactly which orders have invalid amounts and can trace the problem to its source.