dbt Macros for Reusability

A macro in dbt is a Jinja function — reusable SQL logic parameterised like any function, callable from any model with {{ macro_name(args) }}. The moment you find yourself copy-pasting the same CASE WHEN block or the same cents-to-dollars conversion into a fifth model, that is the signal to pull it into a macro instead.

A basic macro

Macros live in .sql files under the macros/ directory, defined with {% macro %} and called with double curly braces just like any built-in Jinja function.

{% macro cents_to_dollars(column_name) %}
    ({{ column_name }} / 100.0)
{% endmacro %}

-- used in a model:
-- select order_id, {{ cents_to_dollars('amount_cents') }} as amount
-- from {{ source('raw', 'orders') }}

dbt compiles the macro call inline before sending SQL to the warehouse — there is no runtime function call, just text substitution at compile time. That matters for debugging: dbt compile shows you exactly what SQL ran, macros and all, which is the first thing to check when a model's output looks wrong.

Packages: macros you do not have to write yourself

dbt's package ecosystem, installed via packages.yml and dbt deps, ships pre-built macros for common needs. dbt_utils is close to a standard library — date_spine for generating date ranges, surrogate_key for hashing composite keys into a single column, pivot for turning rows into columns without hand-writing a dozen CASE WHENs.

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]

Macros with conditional logic and loops

Jinja inside a macro supports full control flow — {% if %}, {% for %} — which is what makes macros useful for generating repetitive SQL, like pivoting a known list of categories into columns.

{% macro pivot_status_counts(statuses) %}
  {% for status in statuses %}
    count(case when status = '{{ status }}' then 1 end) as {{ status }}_count
    {%- if not loop.last %},{% endif %}
  {% endfor %}
{% endmacro %}

-- select order_id, {{ pivot_status_counts(['pending','shipped','refunded']) }}
-- from {{ ref('stg_orders') }} group by 1

Generic tests are macros too

The tests you attach in schema.yml — unique, not_null, and any custom generic test — are themselves macros, defined with test instead of macro and returning a query that should return zero failing rows. This is worth knowing because a custom validation rule you need in three places is usually better written as a generic test macro than as a one-off singular test file.

Wrapping up

Reach for a macro the second time you copy-paste the same SQL fragment, not the fifth — duplication in dbt models is exactly as costly as duplication in application code, and Jinja's `{% if %}`/`{% for %}` gives you enough control flow to handle most repetitive SQL generation. Check dbt_utils before building anything generic yourself; most common patterns are already solved there.