Skip to main content

Generic Custom Tests

Generic tests are reusable DBT tests. You write the logic once, then apply it to many models or columns from schema.yml.

Use generic tests when the same rule should work in many places.

Examples:

  • amount should not be negative
  • price should be greater than a threshold
  • IDs should follow a pattern
  • dates should be inside an allowed range

Folder Location

Generic tests should live here:

tests/generic/

Example:

my_first_dbt_project/
├── models/
│ └── schema.yml
└── tests/
└── generic/
└── no_negative_values.sql

Example: No Negative Values

Imagine this Silver model:

File​

models/silver/silver_customer_orders.sql

{{ config(materialized='table') }}

SELECT
o.ORDER_ID,
o.CUSTOMER_ID,
o.ORDER_DATE,
o.AMOUNT,
o.STATUS,
c.NAME AS CUSTOMER_NAME,
c.COUNTRY,
c.EMAIL AS CUSTOMER_EMAIL
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_customers') }} c
ON o.CUSTOMER_ID = c.CUSTOMER_ID
WHERE o.STATUS = 'completed'

Now you want to test that amount is never negative.


Step 1: Create the Generic Test

File​

tests/generic/no_negative_values.sql

{% test no_negative_values(model, column_name) %}

SELECT *
FROM {{ model }}
WHERE {{ column_name }} < 0

{% endtest %}

The important part is this wrapper:

{% test no_negative_values(model, column_name) %}

Because this test will be called from schema.yml, DBT must know that it is a reusable generic test.


Step 2: Add It in schema.yml

File​

models/schema.yml

version: 2

models:
- name: silver_customer_orders
columns:
- name: order_id
tests:
- unique
- not_null
- name: amount
tests:
- no_negative_values

Step 3: Run the Test

dbt test --select silver_customer_orders

To run only this generic test:

dbt test --select test.no_negative_values

Why the Plain SQL Version Fails

This looks reasonable:

SELECT *
FROM {{ model }}
WHERE {{ column_name }} < 0

But it is missing the generic test wrapper.

If your YAML says:

tests:
- no_negative_values

DBT expects the file to contain:

{% test no_negative_values(model, column_name) %}

Without that wrapper, DBT cannot use it as a schema test.


Generic Test with Arguments

Sometimes the rule needs a configurable value.

File​

tests/generic/greater_than.sql

{% test greater_than(model, column_name, value) %}

SELECT *
FROM {{ model }}
WHERE {{ column_name }} <= {{ value }}

{% endtest %}

Usage in schema.yml​

columns:
- name: price
tests:
- greater_than:
value: 10

Run it:

dbt test --select test.greater_than

Failure Example: Duplicate Order IDs

File​

tests/generic/no_duplicate_order_ids.sql

{% test no_duplicate_order_ids(model) %}

SELECT *
FROM {{ model }}
WHERE order_id IN (

SELECT order_id
FROM {{ model }}
GROUP BY order_id
HAVING COUNT(*) > 1

)

{% endtest %}

Apply It​

version: 2

models:
- name: stg_orders
columns:
- name: order_id
tests:
- no_duplicate_order_ids

Run it:

dbt test --select test.no_duplicate_order_ids

If duplicate order_id values exist, the query returns rows and the test fails.


Key Takeaways

  • Generic tests are reusable.
  • Generic tests are added under tests: in schema.yml.
  • Generic test files should live in tests/generic/.
  • Generic tests must use {% test test_name(model, column_name) %}.
  • Use generic tests for rules you want to apply to many columns or models.