dbt Macros: Writing Reusable SQL Logic Once

Once the same Jinja logic shows up in a third model, copy-pasting it stops being a shortcut and starts being a liability — a bug fix now has to happen in three places, and it inevitably only happens in two. A dbt macro is the fix: a reusable function, written in SQL and Jinja, that you define once and call from anywhere in your project.

Anatomy of a Macro

Macros live in .sql files under a macros/ directory and are defined with the {% macro %} block.

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
    ({{ column_name }} / 100.0)
{% endmacro %}

Calling it from any model looks like calling a function, because that’s exactly what it is:

select
  order_id,
  {{ cents_to_dollars('amount_in_cents') }} as amount_in_dollars
from {{ ref('stg_orders') }}

At compile time, dbt substitutes the macro call with its rendered output:

select
  order_id,
  (amount_in_cents / 100.0) as amount_in_dollars
from analytics.stg_orders

The macro itself never runs against the warehouse — it’s pure text generation happening before the SQL is sent anywhere.

Macros With Multiple Arguments

Real macros usually need more than one input to be genuinely reusable across different contexts.

-- macros/safe_divide.sql
{% macro safe_divide(numerator, denominator) %}
    case
      when {{ denominator }} = 0 or {{ denominator }} is null then null
      else {{ numerator }} / {{ denominator }}
    end
{% endmacro %}

select
  region,
  {{ safe_divide('total_revenue', 'total_orders') }} as avg_order_value
from {{ ref('regional_summary') }}

This single macro eliminates an entire category of division-by-zero bugs that would otherwise be re-implemented slightly differently in every model that needs a ratio.

Macros That Generate Multiple Columns

Macros aren’t limited to producing a single expression — they can generate entire blocks of SQL, which is especially useful combined with a loop.

-- macros/pivot_status_columns.sql
{% macro pivot_status_columns(statuses) %}
  {% for status in statuses %}
    sum(case when status = '{{ status }}' then 1 else 0 end) as {{ status }}_count
    {%- if not loop.last %},{% endif %}
  {% endfor %}
{% endmacro %}

select
  customer_id,
  {{ pivot_status_columns(['pending', 'shipped', 'delivered', 'cancelled']) }}
from {{ ref('stg_orders') }}
group by customer_id

This is the same pattern shown inline in Jinja Templating, extracted into a macro so it can be reused across every model that needs a similar pivot, instead of being re-written per model.

Macros That Return SQL for config()

Macros aren’t limited to producing column expressions — they can also generate values used in model configuration, which is common for standardizing materialization logic across a project.

-- macros/incremental_config.sql
{% macro standard_incremental_config(unique_key) %}
  {{ return({
      'materialized': 'incremental',
      'unique_key': unique_key,
      'incremental_strategy': 'merge'
  }) }}
{% endmacro %}

{{ config(**standard_incremental_config('order_id')) }}

select * from {{ ref('stg_orders') }}
{% if is_incremental() %}
where order_date > (select max(order_date) from {{ this }})
{% endif %}

This ensures every incremental model in the project uses the same strategy conventions without each model author needing to remember and retype the correct configuration.

Organizing Macros in a Real Project

A single macros/ folder with dozens of unrelated files gets unwieldy fast. The convention that scales:

macros/
  finance/
    cents_to_dollars.sql
    safe_divide.sql
  generic_tests/
    custom_not_null_proportion.sql
  utils/
    generate_surrogate_key.sql

Grouping by domain (finance-specific helpers) or purpose (generic tests, general utilities) makes macros discoverable — a new team member looking for “is there already a macro for X” has a reasonable folder structure to search instead of one flat pile of files.

Macros vs. Packages

Once a macro is genuinely generic — not specific to your business logic, useful to any dbt project — it’s a candidate for extraction into a shared package, the mechanism covered in dbt Packages. The most common macros of this kind (surrogate keys, date spines, relation unions) already exist in the community-maintained dbt_utils package, detailed in dbt_utils Package — it’s worth checking there before writing a generic-purpose macro from scratch.

Macros Calling Other Macros

Macros compose naturally — a higher-level macro can call several smaller ones, keeping each individual piece focused and independently testable.

-- macros/standard_staging_transform.sql
{% macro standard_staging_transform(source_relation, id_column) %}
  select
    {{ dbt_utils.generate_surrogate_key([id_column]) }} as surrogate_key,
    *
  from {{ source_relation }}
  where {{ id_column }} is not null
{% endmacro %}

select * from ( {{ standard_staging_transform(source('raw', 'orders'), 'id') }} )

This composition pattern is how larger dbt projects avoid both duplicated logic and unreadably long individual macros — each macro does one clear thing, and higher-level macros assemble them, the same decomposition principle that keeps well-structured application code maintainable.

Debugging a Macro

The same technique used for debugging Jinja in models applies directly to macros — compile the calling model and read the rendered SQL.

dbt compile --select model_using_the_macro
cat target/compiled/my_project/models/path/model_using_the_macro.sql

If a macro’s output looks wrong, this shows you exactly what text it generated, which is almost always faster than reasoning about the Jinja logic in the abstract.

Common Mistakes

Writing a macro before you have two real callers. A macro used exactly once is just indirection — wait until the same logic is genuinely needed a second time before extracting it.

Forgetting {{ return() }} when a macro needs to return a non-string value. Without it, dbt treats the macro’s output as raw text, which breaks when you need it to return a dictionary, list, or number for use in config() or Jinja logic elsewhere.

Over-parameterizing. A macro with eight optional arguments and three flags to control behavior is often harder to use correctly than two separate, simpler macros.

Summary

Macros are how a dbt project stops accumulating copy-pasted SQL as it grows. The rule of thumb worth keeping: the moment you’re about to paste the same Jinja block into a second model, that’s the signal to write a macro instead.