Marcos in dbt

dbt From Basics to Mastery by Examples

In this lesson, we’ll explore macros in dbt, a powerful feature that helps you write reusable and dynamic SQL. Macros function like custom SQL functions, allowing you to avoid repetition, simplify complex logic, and improve maintainability in your dbt models.

By the end of this lesson, you will:

Understand what macros are and how they work in dbt
Learn how to create and use macros in your SQL models
See examples of macros in action, including using macros from dbt packages

What Are Macros in dbt?

Macros in dbt are reusable code blocks, similar to functions in other programming languages. They help eliminate code duplication across multiple models and are defined in .sql files, typically stored in the macros directory.

By default, the macros directory is configured as macro-paths in the dbt_project.yml file. However, you can customize this setting to use a different directory if needed.

Creating a macro

Let’s create a macro that dynamically performs currency conversion.

Step 1: Create the Macro File

Create a file named macros/convert_currency.sql and add the following code:

{% macro convert_currency(column, rate) %}
    ({{ column }} * {{ rate }})
{% endmacro %}

Step 2: Understanding the Macro

The convert_currency macro converts monetary values by multiplying a given column by an exchange rate. It accepts two parameters:

column – The name of the column containing the amount to be converted.
rate – The exchange rate to apply.

Wrapping the calculation in parentheses ensures proper arithmetic evaluation in SQL expressions.

Use a macro in the model

SELECT
    order_id,
    {{ convert_currency('total_amount', 1.1) }} AS total_in_euros
FROM orders;

After dbt compiles the query, it transforms into standard SQL:

SELECT
    order_id,
    (total_amount * 1.1) AS total_in_euros
FROM orders;

This allows the convert_currency macro to be reused across multiple models without rewriting the logic.

Use macro from package

Many useful macros are included in dbt packages, with dbt-utils being one of the most widely used.

Once a package is installed in your project, you can use its macros by prefixing them with the package name to avoid conflicts.

Example: Using a Macro from dbt-utils

SELECT *
FROM {{ dbt_utils.date_spine(
    datepart="day",
    start_date="2024-01-01",
    end_date="2024-12-31"
) }}

In this example, the date_spine macro from dbt-utils generates a series of dates between start_date and end_date.

By integrating Jinja with dbt, we can:

Write cleaner, reusable SQL
Avoid repetitive code with macros
Dynamically generate SQL queries
Enhance flexibility using variables and control structures

What Are Macros in dbt?

Creating a macro

Use a macro in the model

Use macro from package

Next