Skip to main content

Macros in dbt

Learning objectives​

By the end of this lesson, you will be able to:

  • Explain what a dbt macro is and why teams use one.
  • Distinguish a macro from a model.
  • Create a macro in the macros/ directory.
  • Predict the SQL produced when dbt compiles a macro call.

1. The problem macros solve​

Suppose several staging models receive customer statuses in different formats. Without a macro, every model may repeat the same CASE expression:

CASE
WHEN UPPER(TRIM(customer_status)) IN ('ACTIVE', 'A', 'ENABLED')
THEN 'ACTIVE'
WHEN UPPER(TRIM(customer_status)) IN ('INACTIVE', 'I', 'DISABLED')
THEN 'INACTIVE'
ELSE 'UNKNOWN'
END

Repeated logic is difficult to review and easy to change inconsistently. A macro lets us define the rule once and reuse it:

{{ standardize_status('customer_status') }}

The macro does not store data by itself. It generates a SQL expression that becomes part of a model.

2. Macro, model, and compiled SQL​

ConceptResponsibilityTypical command
MacroReusable Jinja logic that generates text or SQLdbt run-operation or a model run
ModelA SQL statement that creates a relation in the warehousedbt run
Compiled SQLThe SQL after dbt evaluates Jinjadbt compile

A model can call a macro, but a macro is not automatically a table or view. dbt processes Jinja first, then sends the resulting SQL to Snowflake.

Jinja macro call
|
v
Rendered SQL
|
v
dbt model executed in Snowflake

3. Where macros live​

Macros normally live in the project-level macros/ directory:

my_dbt_project/
├── dbt_project.yml
├── models/
├── macros/
│ └── standardize_status.sql
└── tests/

A macro file can contain one or more macros. Keeping one business-purpose macro per file makes the project easier to navigate.

4. Create a first macro​

Create this file in the macros/ directory:

macros/greet.sql

{% macro greet(name) %}
'Hello ' || '{{ name }}'
{% endmacro %}

This macro accepts one parameter, name, and generates a SQL string expression.

Now create this model:

models/greet_model.sql

{{ config(materialized='view') }}

SELECT {{ greet('dbt learner') }} AS greeting

The Jinja expression is {{ ... }} because it outputs a value. The macro declaration uses {% ... %} because it defines logic rather than directly displaying a value. When dbt compiles the model, the macro call becomes:

SELECT 'Hello ' || 'dbt learner' AS greeting

5. Compile and run the model​

Run these commands from the directory that contains dbt_project.yml:

dbt compile --select greet_model
dbt run --select greet_model

The model creates a view in the target schema. Validate it in Snowflake:

SELECT greeting
FROM <YOUR_DBT_DATABASE>.<YOUR_DBT_SCHEMA>.GREET_MODEL;

Expected result:

GREETING
Hello dbt learner

Snowflake uses || for string concatenation. The exact SQL syntax can vary by adapter, which is one reason to keep warehouse-specific details in reusable code.

6. Parameters make macros reusable​

Parameters allow the same macro to work with different inputs:

Create this file in the macros/ directory:

macros/filter_by_date.sql

{% macro filter_by_date(column_name, start_date, end_date) %}
{{ column_name }} BETWEEN '{{ start_date }}' AND '{{ end_date }}'
{% endmacro %}

Now create this model:

models/orders_2025.sql

{{ config(materialized='view') }}

SELECT *
FROM {{ ref('orders') }}
WHERE {{ filter_by_date('order_date', '2025-01-01', '2025-12-31') }}

This model assumes that another dbt model named orders already exists. The ref('orders') function creates a dependency and resolves the correct database, schema, and relation name for the current target.

7. Compile and run the date-filtered model​

Run these commands from the directory that contains dbt_project.yml:

dbt compile --select orders_2025
dbt run --select orders_2025

The macro call compiles into ordinary SQL:

SELECT *
FROM <YOUR_DBT_DATABASE>.<YOUR_DBT_SCHEMA>.ORDERS
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'

Validate the resulting view in Snowflake:

SELECT
order_id,
order_date
FROM <YOUR_DBT_DATABASE>.<YOUR_DBT_SCHEMA>.ORDERS_2025
ORDER BY order_date;

Every returned order has an order_date from January 1, 2025 through December 31, 2025.

The quoted strings passed to the macro are inserted into the generated SQL. They are date values in this example, while column_name is inserted as a SQL identifier. Only pass trusted, developer-controlled column expressions to this macro.

8. A useful rule of thumb​

Create a macro when all of these are true:

  • The logic is repeated or likely to be repeated.
  • The logic has a clear input and output.
  • One definition will make reviews and changes safer.

Do not hide a one-line expression behind a macro merely to make the SQL look clever. A macro should make the project easier to understand.

Check your understanding​

  1. Does a macro create a table by itself?
  2. Which command shows the SQL generated from a macro call?
  3. Why is standardize_status('customer_status') more maintainable than copying the same CASE expression into five models?

Answers​

  1. No. A model or an operational macro must execute the generated SQL.
  2. dbt compile.
  3. The business rule has one source of truth, so updates are consistent.

Summary​

  • A dbt macro is reusable Jinja logic stored in macros/.
  • A macro generates text or SQL; a model builds a database relation.
  • Parameters let one macro work across columns and models.
  • dbt evaluates Jinja before Snowflake receives the compiled SQL.

In the next lesson, you will execute a macro directly with dbt run-operation.