dbt Custom Generic Tests

dbt ships with four built-in generic tests: not_null, unique, accepted_values, and relationships. Custom generic tests let you write additional reusable tests that work exactly like the built-ins — declare them in YAML with arguments, and dbt runs the generated SQL automatically. Write a custom generic test once and apply it to any column in any model.

Generic Test vs Singular Test

Singular test:    one SQL file, tests one specific thing in one model
Generic test:     one reusable template, applied to many models via YAML

Use singular when: the check is one-off and specific to one model
Use generic when:  the same check pattern applies across multiple models

Creating a Custom Generic Test

Custom generic tests live in the macros/ folder (or any tests/generic/ subfolder). They use a Jinja macro named test_<name>. The macro must return a SQL SELECT that returns failing rows:

Example: test_is_positive

-- macros/test_is_positive.sql

{% test is_positive(model, column_name) %}

select
    {{ column_name }}
from {{ model }}
where {{ column_name }} is not null
  and {{ column_name }} <= 0

{% endtest %}

The two required parameters are always model (the table being tested) and column_name (the column to check). dbt injects these automatically when you apply the test in YAML.

Using the Custom Test in YAML

# models/marts/schema.yml
models:
  - name: fct_orders
    columns:
      - name: amount_dollars
        tests:
          - not_null
          - is_positive        ← your custom generic test

      - name: quantity
        tests:
          - not_null
          - is_positive        ← same test reused on a different column

Custom Generic Test with Additional Arguments

Add extra parameters to make the test flexible:

-- macros/test_is_between.sql

{% test is_between(model, column_name, min_value, max_value) %}

select
    {{ column_name }}
from {{ model }}
where {{ column_name }} is not null
  and {{ column_name }} not between {{ min_value }} and {{ max_value }}

{% endtest %}

# Usage in YAML
columns:
  - name: discount_percent
    tests:
      - is_between:
          min_value: 0
          max_value: 100

  - name: rating
    tests:
      - is_between:
          min_value: 1
          max_value: 5

Custom Test: No Future Dates

-- macros/test_not_in_future.sql

{% test not_in_future(model, column_name) %}

select {{ column_name }}
from {{ model }}
where {{ column_name }} > current_date

{% endtest %}

# Usage
columns:
  - name: order_date
    tests:
      - not_null
      - not_in_future

  - name: ship_date
    tests:
      - not_in_future

Custom Test: Ratio Within Range

-- macros/test_proportion_above_threshold.sql

{% test proportion_above_threshold(model, column_name, threshold) %}

with counts as (
    select
        count(*)                                        as total_rows,
        count(case when {{ column_name }} is not null
                   then 1 end)                         as non_null_rows
    from {{ model }}
)

select total_rows
from counts
where non_null_rows::float / nullif(total_rows, 0) < {{ threshold }}

{% endtest %}

# Usage: email must be non-null for at least 80% of rows
columns:
  - name: email
    tests:
      - proportion_above_threshold:
          threshold: 0.80

Custom Test: Column Matches Another Column

-- macros/test_column_gte_other_column.sql

{% test column_gte_other_column(model, column_name, other_column) %}

select {{ column_name }}, {{ other_column }}
from {{ model }}
where {{ column_name }} < {{ other_column }}

{% endtest %}

# Usage: ship_date must be >= order_date
columns:
  - name: ship_date
    tests:
      - column_gte_other_column:
          other_column: order_date

Adding Config to a Custom Test

You can set default severity and other config inside the test macro:

{% test is_positive(model, column_name) %}
{{ config(severity='warn') }}

select {{ column_name }}
from {{ model }}
where {{ column_name }} is not null
  and {{ column_name }} <= 0

{% endtest %}

Override the config at the YAML level when applying the test:

tests:
  - is_positive:
      config:
        severity: error
        tags: ['finance']

Organising Custom Tests

macros/
  tests/
    test_is_positive.sql
    test_is_between.sql
    test_not_in_future.sql
    test_proportion_above_threshold.sql
    test_column_gte_other_column.sql

Storing test macros in a tests/ subfolder inside macros/ keeps them separate from transformation macros and makes them easy to find.

Sharing Custom Tests via Packages

If multiple dbt projects in your organisation need the same custom tests, publish them as an internal dbt package. Teams install the package with dbt deps and apply the tests in YAML immediately — the same way they use dbt_utils or dbt_expectations.