In this lesson, you will learn how to build your own reusable generic tests using Jinja to enforce consistent validation logic across your entire dbt project.
Why Custom Generic Tests?
While built-in tests like not_null or unique are powerful, they often don't cover specific business rules. If you find yourself writing the same "singular" test SQL over and over for different models, you are repeating code. By using Jinja macros, you can encapsulate that complex logic into a single test that can be applied to any column or model with one line of YAML.
Creating the Macro
To define a custom generic test, create a new file in your macros/ folder (e.g., test_is_even.sql). Inside, you wrap your validation query with the test Jinja tag. The macro must accept model and column_name as arguments.
The logic within the macro should return the rows that fail the test.
{% test is_even(model, column_name) %}
with validation as (
select
{{ column_name }} as val
from {{ model }}
),
validation_errors as (
select
val
from validation
where mod(val, 2) != 0
)
select * from validation_errors
{% endtest %}
Applying the Test
Once defined, dbt treats your macro like a first-class citizen. You can apply it in any schema.yml file just like a built-in test:
models:
- name: my_orders
columns:
- name: order_id
tests:
- is_even
When you run dbt test, dbt automatically injects the model and column name into your macro, executes the query, and marks the test as failed if any records are returned.
The Power of Reusability
This pattern allows you to standardize complex data quality checks—such as checking if a date falls within a specific business quarter or ensuring a specific string format—across your entire warehouse. Instead of maintaining dozens of identical singular tests, you only need to update the logic in one macro file to enforce a new rule project-wide.
Key Takeaway: Custom generic tests allow you to encapsulate complex, repeating validation logic into reusable macros, ensuring project-wide data integrity with minimal maintenance.