Developing Custom Schema Tests with Jinja

In this lesson, you will learn how to go beyond built-in tests by using Jinja to write custom schema tests that enforce complex, project-specific business rules.

Why Custom Schema Tests?

While dbt’s built-in tests (like unique or not_null) are great for structural integrity, they fall short when your business logic requires multi-column validation or domain-specific thresholds. By defining a custom test in the tests/generic/ directory, you can create a reusable assertion that works just like a native dbt test.

Writing Your First Custom Test

A custom schema test is essentially a macro that returns a SQL query. If that query returns any rows, the test fails.

Imagine you are working for an e-commerce company. You need to ensure that an order_total is never less than the shipping_cost. You can define a custom test called test_order_is_profitable.sql inside your tests/generic/ folder:

{% test order_is_profitable(model, column_name_total, column_name_shipping) %}

with validation_errors as (
    select *
    from {{ model }}
    where {{ column_name_total }} < {{ column_name_shipping }}
)

select * from validation_errors

{% endtest %}

Applying the Test

Once the macro is defined, you can apply it to your model in your schema.yml file just like any other test. Because it is written as a generic test, you can pass the specific columns you want to check as arguments:

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - order_is_profitable:
              column_name_total: order_total
              column_name_shipping: shipping_cost

When you run dbt test, dbt injects your model's data into the macro, executes the query, and flags any records that violate your profitability logic. This allows you to encapsulate complex, cross-column business rules into a clean, repeatable testing interface. By mastering these Jinja-based tests, you transform your test suite from a simple sanity check into a powerful engine for data quality and governance.

Key Takeaway: Custom schema tests allow you to turn complex SQL validation logic into reusable, configurable tests that enforce your unique business requirements across your entire dbt project.