Introduction

Data Engineering at FreeAgent has a mission to ensure our colleagues and customers have reliable, accurate, and secure access to the data they need. Our migration to Dagster, DBT, and DLT is a key part of that. However, it has raised numerous questions, including how we test DBT Macros. This post dives into how we’re tackling this by leveraging pytest and Jinja for more efficient unit testing of DBT macro logic.

Before diving into testing Macros, let’s first see what they are (from the DBT documentation):

“Macros in Jinja are pieces of code that can be reused multiple times – they are analogous to “functions” in other programming languages, and are extremely useful if you find yourself repeating code across multiple models.”

The first Macro we wrote involved date logic, which took two dates and generated a predicate to filter data between them. Due to the complex date math and wide reuse, it needed comprehensive tests. We wanted to test our business logic, and test happy and sad paths through the code.  This was the precise moment we were hit with the questions  “How do we test DBT Macros?!”

Given that Macros are “analogous to functions”, a natural fit to test them would be via a unit test. Therefore, we set about looking for a solution that would let us do that.

---

Existing DBT testing mechanisms

There are a number of different ways to test your DBT project depending on what you need:

• Data tests
• Unit tests
• Integration tests

Data and unit testing models are built into DBT, and are both very powerful. Data Checks run on your production data sets once they’ve been transformed to ensure the correctness of the data (e.g. uniqueness test, null checking, etc.). Unit tests allow you to test your model logic, given an expected input and output. These are run in development or in CI. They require a database connection, the tables to be created in your database, and have a number of other caveats.

A popular way to test DBT packages is through integration tests  see dbt_utils package as an example. This is where you create a separate project for your test, and create specific testing models that use your macros, then add data tests against your test model.

We also discovered a DBT package called dbt_unittest, which sounded more inline with what we wanted: a unit test approach which specifically targeted testing macros (opposed to models). This was really nice because we could test the macros like we would in other languages. However, the tests themselves were written in macros, and therefore required similar dependencies to production (e.g. a DBT project and database connection). It also wouldn’t allow us to test the exception path through the code.

There’s nice things about all of these options. However, none of them fitted what we wanted.

---

Using Pytest and Jinja

So, what do we need?

• Something that can test our business logic by calling a function and asserting conditions on the output
• In our test case, we don’t need a database (we wouldn’t use one in other projects to test date calculations)
• Testing error paths (i.e., do we hit exceptions as expected)

We wondered if we could take the unit test approach, but run the macros in our Python test suite. This would allow us to run the tests without a database, and test the exception path. To do this we create a Jinja environment with added extensions, and inject the available Python modules, then load in our macros into the environment. We then expose the macros so developers can call it, like they would any other function. See below for an example of how we’ve incorporated it into our test suite. 

# tests/src/dbt_project/macros/test_my_macro.py
import pytest

class TestMyMacro:
    def test_basic_functionality(self) -> None:
        """
        load_macro is a function we have defined in our conftest.py to handle setting up the jinja
        environment returns the specified macro as a Callable. You provide the name of which macro is under
        test. In this case, "my_macro" is the name of the macro we want to test.
        """
        my_macro = load_macro("my_macro")
        
	# we can then call the macro like any other function, providing an input value and are returned the result
        result = my_macro("input_value")

        # now we check if what we have is what we want
        assert result == "expected_output"

    def test_error_path(self) -> None:
        my_macro = load_macro("my_macro")
	 
	 # This time we call the macro with values that would raise an error, and ensure our error path is working as expected
	 with pytest.raises(ValueError, match="invalid input etc."):
	     my_macro("bad_value")

The downside here is that we’re not using DBT directly, so the behaviour may differ, but it’s normal for unit tests to mock calls to external systems, and something we can live with. The upside is that we can now easily test our macros with a broad range of inputs, and without a database. Finally, there’s a huge performance advantage. We replicated 15 tests for 1 macro using the DBT unit test method, dbt_unittest package, and our mechanism and compared the time it took to run them. DBT unit testing took 22s, dbt_unittest package took 3.7, and ours took 0.67.

This mechanism doesn’t replace the use or need for comprehensive DBT Data or Unit tests.  Instead, it provides an accompaniment to them to sufficiently test macros in a unit testable way.

---

Summary

This post explored an approach to testing DBT macros, by using unit testing with Pytest and Jinja. By creating a custom Jinja environment within a Python test suite, we can efficiently test macro logic and error paths without needing a database connection, offering significant performance advantages over existing DBT testing methods. This mechanism complements, rather than replaces, other comprehensive testing strategies for DBT projects.

How do you test your DBT macros? Do you prefer to test them through your models in DBT? Does our approach sound useful? We’d love to know!