What is Macro in dbt: Definition and Usage Explained

In dbt, a macro is a reusable block of SQL or Jinja code that you can call in your models or other macros to avoid repetition. Macros help you write cleaner, more maintainable code by letting you define logic once and use it many times.

How It Works

Think of a macro in dbt like a recipe you write once and use whenever you want to cook the same dish. Instead of rewriting the same SQL code multiple times, you create a macro that holds that code. Then, you just call the macro wherever you need it.

Under the hood, dbt uses Jinja, a templating language, to process macros. When dbt runs, it replaces the macro calls with the actual SQL code defined inside the macro. This makes your project cleaner and easier to update because changing the macro updates all places where it is used.

Example

This example shows a simple macro that returns a SQL expression to calculate a 7-day moving average. You can call this macro in any model to reuse the logic.

sql

{% macro moving_average(column) %}
  AVG({{ column }}) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
{% endmacro %}

-- Usage in a model:
SELECT
  date,
  {{ moving_average('sales') }} AS sales_7_day_avg
FROM sales_data

When to Use

Use macros in dbt when you have SQL logic that repeats across multiple models or when you want to simplify complex expressions. For example, if you calculate the same metric in many places, a macro lets you write it once and keep it consistent.

Macros are also helpful for dynamic SQL generation, like building filters or conditional logic based on parameters. This saves time and reduces errors in your data transformations.

Key Points

• Macros are reusable blocks of SQL or Jinja code in dbt.
• They help avoid repeating code and make projects easier to maintain.
• Macros use Jinja templating to insert code dynamically.
• You define macros in macros/ folders and call them in models or other macros.
• They are useful for repeated calculations, dynamic SQL, and simplifying complex logic.

Key Takeaways

A macro in dbt is a reusable piece of SQL or Jinja code to avoid repetition.

Macros make your dbt project cleaner and easier to maintain by centralizing logic.

You define macros using Jinja syntax and call them in your models or other macros.

Use macros for repeated calculations, dynamic SQL, or complex expressions.

Changing a macro updates all places where it is used, saving time and reducing errors.