Passing Arguments to Parameterized Macros

In this lesson, you will learn how to make your dbt macros dynamic by passing arguments, enabling you to reuse the same logic across different tables and column names.

Moving Beyond Static Macros

In our previous look at custom macros, we created static blocks of code. While helpful, static macros are limited because they usually perform the exact same task every time. By adding arguments to your macros, you turn a rigid piece of SQL into a flexible function. This allows you to define the logic once and inject specific table names or column identifiers whenever you call the macro.

Creating a Parameterized Macro

Imagine you frequently need to calculate a "tax-inclusive" price for different tables. Instead of writing the math repeatedly, you can define a macro that accepts the column name as an argument.

First, define the macro in your macros/ folder (e.g., calculate_tax.sql):

{% macro calculate_tax(column_name, tax_rate) %}
    ({{ column_name }} * (1 + {{ tax_rate }}))
{% endmacro %}

In this example, column_name and tax_rate act as placeholders. When you call this in a model, dbt replaces those placeholders with the specific values you provide.

Using the Macro in a Model

Now, inside your model SQL files, you can call the macro like a function, passing in the arguments specific to that model:

select
    item_name,
    {{ calculate_tax('price', 0.15) }} as price_with_tax,
    {{ calculate_tax('wholesale_cost', 0.10) }} as cost_with_tax
from {{ ref('stg_sales') }}

When dbt compiles this, it injects the arguments into the SQL template. This approach drastically reduces code duplication and ensures that if your tax calculation logic ever changes (e.g., a rate update), you only need to update the macro in one place, and every model using it will be updated automatically.

By parameterizing your macros, you shift from hard-coding specific columns to building a reusable toolkit that adapts to the schema of any table in your warehouse.

Key Takeaway: Passing arguments to macros allows you to decouple your business logic from specific column names, making your pipeline code modular, DRY (Don't Repeat Yourself), and significantly easier to maintain.