In this lesson, you will learn how to encapsulate repetitive SQL snippets into custom macros to clean up your dbt models and ensure your transformation logic remains consistent across your entire project.
Why Macros Matter
In a typical ELT pipeline, you often find yourself repeating the same complex logic—like calculating a business-specific tax rate or formatting a standardized date string—across dozens of different models. If that business logic changes, you are forced to hunt down and update every single model manually. Macros act as "functions" for your SQL, allowing you to write the logic once and reference it everywhere.
Defining Your First Macro
To create a macro, you add a file ending in .sql inside your project’s macros/ directory. Within that file, you use Jinja syntax to define the logic.
For example, imagine you need to calculate a "cents-to-dollars" conversion across your financial models. Instead of writing amount / 100 repeatedly, define this in macros/convert_cents_to_dollars.sql:
{% macro convert_cents_to_dollars(column_name) %}
({{ column_name }} / 100.0)
{% endmacro %}
Applying the Macro in a Model
Once defined, you can call your macro inside any model using the {{ ... }} syntax. This makes your SQL much more readable and keeps your logic centralized:
-- models/finance/stg_payments.sql
select
payment_id,
{{ convert_cents_to_dollars('amount_in_cents') }} as amount_in_dollars
from {{ ref('raw_payments') }}
The Power of Maintenance
If your business rule changes—perhaps you need to account for a new currency conversion factor—you simply update the logic inside the macro file. Every model that references convert_cents_to_dollars will automatically inherit the change the next time you run dbt. By shifting from copy-pasting code to calling modular macros, you reduce the risk of human error and make your pipeline significantly easier to scale and debug as your project grows.
Key Takeaway: Custom macros turn repetitive SQL snippets into centralized, reusable functions, ensuring your business logic remains consistent and easy to update across your entire data warehouse.