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:inschema.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.