How to Create Macro in dbt: Simple Guide with Examples

In dbt, you create a macro by defining a reusable Jinja function inside a .sql file within the macros folder. Use the {% macro macro_name(params) %} and {% endmacro %} tags to define it, then call it in your models with {{ macro_name(args) }}.

Syntax

A dbt macro is defined using Jinja templating syntax. You start with {% macro macro_name(params) %} to declare the macro and end with {% endmacro %}. Inside, you write SQL or Jinja code that can use the parameters. You call the macro in your models or other macros using {{ macro_name(args) }}.

• macro_name: The name you give your macro.
• params: Optional parameters to customize the macro.
• args: Arguments passed when calling the macro.

jinja

{% macro example_macro(name) %}
  select '{{ name }}' as greeting
{% endmacro %}

Example

This example shows a macro that returns a greeting message using a name parameter. You can call this macro in a model to generate a simple SQL query.

jinja + sql

{% macro greet(name) %}
  select 'Hello, {{ name }}!' as message
{% endmacro %}

-- Usage in a model file (e.g., models/greet_model.sql):
{{ greet('dbt user') }}

Output

message
Hello, dbt user!

Common Pitfalls

Common mistakes when creating macros in dbt include:

• Not placing the macro file inside the macros folder, so dbt cannot find it.
• Forgetting to use {% endmacro %} to close the macro block.
• Using incorrect Jinja syntax inside the macro.
• Calling the macro without the correct number or type of arguments.

Always test your macro by calling it in a model to ensure it works as expected.

jinja

{% raw %}
-- Wrong: missing endmacro
{% macro bad_macro() %}
  select 1

-- Right:
{% macro good_macro() %}
  select 1
{% endmacro %}
{% endraw %}

Quick Reference

Concept	Description
{% macro name(params) %} ... {% endmacro %}	Defines a macro with optional parameters
{{ macro_name(args) }}	Calls a macro with arguments
Place macro files in macros/	dbt looks for macros in this folder
Use Jinja syntax	Macros use Jinja templating language
Test macros in models	Call macros in models to verify output

Key Takeaways

Define macros inside the macros/ folder using {% macro %} and {% endmacro %} tags.

Use parameters to make macros flexible and reusable.

Call macros in models with {{ macro_name(args) }} syntax.

Always close macros properly to avoid syntax errors.

Test macros by running models that use them to ensure correctness.