Custom Tests in dbt: Writing Business-Specific Validation Logic

The four built-in generic tests covered in Generic Tests handle uniqueness, nulls, enums, and referential integrity — but they can’t express “a completed order should never have a negative total” or “no customer should have more than one active subscription at the same time.” Rules like that are specific to your business logic, and dbt gives you two ways to write them: singular tests for one-off checks, and custom generic tests when the same logic needs to be reused across multiple models.

Singular Tests: The Simplest Custom Check

A singular test is just a SQL file in the tests/ directory that returns rows representing failures. If the query returns zero rows, the test passes. If it returns any rows, the test fails — and shows you exactly which rows violated the rule.

-- tests/assert_no_negative_completed_order_totals.sql
select
  order_id,
  total_amount
from {{ ref('stg_orders') }}
where status = 'completed'
  and total_amount < 0

Run it exactly like any other test:

dbt test --select assert_no_negative_completed_order_totals

There’s no special syntax to learn — a singular test is just “write a query that returns the rows you don’t want to exist.” This is the fastest path from “I have a business rule in my head” to “that rule is now enforced automatically on every run.”

A More Realistic Singular Test Example

-- tests/assert_single_active_subscription_per_customer.sql
select
  customer_id,
  count(*) as active_subscription_count
from {{ ref('stg_subscriptions') }}
where status = 'active'
group by customer_id
having count(*) > 1

This catches a genuinely important business invariant — a billing system bug that lets a customer end up with two simultaneously active subscriptions — that none of the four generic tests could express, because it depends on an aggregation and a business-specific rule, not a simple per-row check.

Custom Generic Tests: Reusable Across Models

When the same custom check needs to apply to more than one model or column, a singular test starts requiring copy-paste. A custom generic test, defined as a macro following a naming convention, solves this the same way built-in generic tests work.

-- macros/test_not_negative.sql
{% test not_negative(model, column_name) %}

select *
from {{ model }}
where {{ column_name }} < 0

{% endtest %}

Once defined, it’s usable in YAML exactly like unique or not_null:

models:
  - name: stg_orders
    columns:
      - name: total_amount
        tests:
          - not_negative
  - name: stg_refunds
    columns:
      - name: refund_amount
        tests:
          - not_negative

The macro is written once and reused across every column that needs the same check — the same reuse principle covered generally in dbt Macros, applied specifically to test logic.

Custom Generic Tests With Parameters

Generic tests can accept additional arguments beyond model and column_name, letting the same test logic flex across different thresholds.

-- macros/test_value_within_range.sql
{% test value_within_range(model, column_name, min_value, max_value) %}

select *
from {{ model }}
where {{ column_name }} < {{ min_value }}
   or {{ column_name }} > {{ max_value }}

{% endtest %}

columns:
  - name: discount_pct
    tests:
      - value_within_range:
          min_value: 0
          max_value: 100

This single test macro now covers any bounded-range check across the entire project, with the actual bounds specified per-column at the point of use.

Singular Tests With Configurable Severity

Custom tests support the same config options as built-in ones — severity, where scoping — either inline in the SQL file or via a corresponding YAML entry.

-- tests/assert_no_negative_completed_order_totals.sql
{{ config(severity='warn') }}

select order_id, total_amount
from {{ ref('stg_orders') }}
where status = 'completed'
  and total_amount < 0

Testing Across Multiple Models in One Singular Test

Singular tests aren’t limited to a single model — a common pattern is validating a relationship or invariant that spans two models a generic test’s model/column_name structure can’t express cleanly.

-- tests/assert_order_totals_match_line_items.sql
with order_totals as (
    select order_id, total_amount from {{ ref('stg_orders') }}
),

line_item_sums as (
    select order_id, sum(line_amount) as computed_total
    from {{ ref('stg_order_line_items') }}
    group by order_id
)

select
  o.order_id,
  o.total_amount,
  l.computed_total
from order_totals o
join line_item_sums l using (order_id)
where abs(o.total_amount - l.computed_total) > 0.01

This catches a real and common data integrity problem — an order’s stated total drifting out of sync with the sum of its line items — that no built-in test could express, since it requires joining two models and comparing a computed aggregate.

When to Reach for a Custom Test vs. a Generic One

Common Mistakes

Writing a singular test when a custom generic test would prevent duplication. If you’re about to copy a singular test file and rename it for a second model, that’s the signal to convert it to a parameterized generic test instead.

Forgetting that the query should return failing rows, not a boolean. A common early mistake is writing a query that returns true/false — dbt tests are pass/fail based on row count, not a boolean value, so the query needs to select the actual offending rows.

Not naming custom tests descriptively. tests/test1.sql tells a future reader nothing; assert_no_negative_completed_order_totals.sql documents the business rule in the filename itself.

Summary

Custom tests are where dbt’s testing framework stops being a generic data-quality tool and starts encoding your actual business rules as executable, automatically-checked assertions — the same discipline unit tests bring to application code, applied to data.