Testing Dbt Macros

You do test, right?

Not a Medium member? No problem. Read free here.

Sure, dbt has data-quality tests built in, but that isn’t enough to create reliable data pipelines. We need to validate our code. Let me repeat. We need to validate our code. Fortunately for us, there are simple patterns for ensuring the macros we write do exactly what we expect them to do. Let’s dive in with a scenario.

The Scenario

Here at Moon Walks, the premier online shoe retailer for men who only walk outside during full moons, we process mounds of data every night. To ensure idempotency and efficiency, we need to specify specific date ranges in our models and tests. Our macros appear to work, but sometimes logic errors are found. How can we ensure our macros provide the intended results?

Seems reasonable, right? Of course there would be a niche market for men who only walk outside during full moons. It’s 2024 after all. The macro below looks pretty good to me. It takes a Python date object and a date logic as arguments and returns a string representing a date in SQL. Look at the code and tell me if it is correct. Here’s a hint. There is one flaw.

/macros/get_model_start_date.sql

{% macro get_model_start_date(run_at_date, date_logic='run_at_day') %}

    {# This is like a Python import statement. Makes calling these functions shorter. #}
    {% set date = modules.datetime.date %}
    {% set timedelta = modules.datetime.timedelta %}

    {# Define any date logics used by your company  #}

    {% if date_logic == 'run_at_day' %}
        {% set start_date = run_at_date %}

    {% elif date_logic == 'run_at_week' %}
        {# Get the beginning of the week, which is always Sunday #}
        {% set start_date = run_at_date - timedelta(days = run_at_date.weekday() ) %}

    {% elif date_logic == 'run_at_month' %}
         {# Build a date using year and month from execution date  #}
        {% set start_date = date(run_at_date.year, run_at_date.month, 1) %}

    {% else %}
        {{ exceptions.raise_compiler_error('get_start_dates: Invalid date logic: ' + date_logic) }}
    {% endif %}

    {% set model_start_date  = "'" + start_date.strftime('%Y-%m-%d') + "'" %}
    {% do return(model_start_date) %}
{% endmacro %}

Why Test Macros

Macros are code blocks that will often be reused in multiple contexts. When we utilize a macro in our project, we need to be…