Skip to main content

Testing Macros

If you write a Python function, you write a unit test for it. If you write a dbt SQL model, you write a data test for it (not_null, unique).

But what happens when you write a complex Jinja Macro? Because a macro is just a template that generates SQL, a tiny typo in your macro can silently generate bad math across 50 different models in your project.

You must test your macros to ensure the SQL they generate is mathematically correct.

Topics Covered​

  • The difficulty of testing Jinja
  • Writing a "Dummy Model" to test macros
  • Asserting the Output

1. The Difficulty of Testing Jinja​

Let's assume our Data Engineer, Kavya, writes a custom macro to calculate the net price of an item after applying the Indian GST tax rate.

-- macros/calculate_gst.sql

{% macro calculate_gst(column_name) %}
-- Add 18% GST to the base price
({{ column_name }} * 1.18)
{% endmacro %}

How does Kavya prove this math is correct before she deploys it to the 50 models that use it?

You cannot use a standard dbt test in schema.yml because those tests run against physical tables, and the macro itself is not a physical table.

2. Writing a Dummy Test Model​

The industry best practice for testing a macro is to create a fake, temporary model (often using materialized='ephemeral') specifically designed to trigger the macro using hardcoded dummy data.

Kavya creates a new file in her testing folder:

-- models/testing/test_calculate_gst.sql

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

-- 1. Create a dummy CTE with hardcoded test values
WITH dummy_data AS (
SELECT
100 as base_price,
118 as expected_price

UNION ALL

SELECT
200 as base_price,
236 as expected_price
),

-- 2. Run the data through the macro!
test_execution AS (
SELECT
base_price,
expected_price,
{{ calculate_gst('base_price') }} as actual_price
FROM dummy_data
)

-- 3. Assert the test! If actual_price does not equal expected_price, return the failing rows.
SELECT *
FROM test_execution
WHERE actual_price != expected_price

3. Running the Test​

Now that Kavya has a model that isolates the macro, she needs to execute it.

Notice the final SELECT statement: WHERE actual_price != expected_price. If the macro works perfectly, the actual_price will exactly match the expected_price, the WHERE clause will filter out everything, and the query will return 0 rows.

If the macro has a bug (e.g., Kavya accidentally multiplied by 1.08 instead of 1.18), the query will return rows.

Because this is a .sql file, Kavya can execute it using the dbt run or dbt compile command, or she can wrap it in a singular test!

The Singular Test Approach​

To fully automate this into the CI/CD pipeline, Kavya moves the .sql file out of the models/ folder and places it in the tests/ folder.

Now, whenever she runs dbt test, dbt automatically executes the SQL. If the macro returns the wrong math, the test returns a row, dbt flags it as a failure, and the CI/CD pipeline immediately halts, preventing the bad macro from being deployed to production!

Summary​

  • Because macros are heavily reused, a single bug in a macro will poison your entire codebase.
  • You test a macro by creating a Dummy CTE with hardcoded input values and hardcoded expected output values.
  • Pass the dummy input into your macro and compare the actual output against the expected output.
  • Place this logic in the tests/ folder so it runs automatically every time you type dbt test.