dbt Macros: From Jinja Basics to Production Patterns

Macros give dbt much of its flexibility, but they’re frequently misused. I’ve seen projects with elegant, reusable macros that save hours of development time. I’ve also seen projects where macros create more problems than they solve, hiding simple SQL behind layers of abstraction nobody can debug.

Effective macro usage comes down to understanding when and how to apply them.

This guide takes you from Jinja fundamentals through production-ready patterns. You’ll learn the mechanics first, then the essential packages that solve common problems, and finally the design principles that keep macros maintainable as your project grows.

Why macros matter in dbt

Every dbt project accumulates repetitive SQL. Currency conversions appear in a dozen models. Date truncation logic gets copied between files. Surrogate key generation follows the same pattern everywhere.

Macros let you write that logic once and reuse it. They’re Jinja templates that generate SQL at compile time. Instead of copying a five-line currency conversion into every model, you call {{ cents_to_dollars('amount') }} and let dbt write the SQL for you.

But macros aren’t always the answer. Simple, readable SQL often beats clever abstraction. You’re better served recognizing when abstraction genuinely helps rather than trying to macro-fy everything.

Jinja fundamentals for dbt

dbt uses Jinja2 as its templating language, but with an important twist: templates render twice. The first pass (parse phase) builds the DAG and determines dependencies. The second pass (execute phase) generates the actual SQL. This dual-phase execution catches many newcomers off guard.

Variables and control structures

The {% set %} tag creates variables. Combine it with loops and conditionals to generate dynamic SQL:

{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}

{% for method in payment_methods %}
    SUM(CASE WHEN payment_method = '{{ method }}' THEN amount END) AS order__{{ method }}_amount
    {{ ',' if not loop.last }}
{% endfor %}

This generates three SUM(CASE WHEN...) columns without writing each one manually. The loop.last variable handles trailing commas, a common pain point in generated SQL.

Whitespace matters for readable compiled SQL. Use {%- to strip whitespace before and -%} to strip after. The compiled SQL will be much easier to debug.

Macro anatomy

Macros live in .sql files under your macros/ directory. Here’s a simple transformation macro:

{% macro cents_to_dollars(column_name, scale=2) %}
    ROUND({{ column_name }} / 100, {{ scale }})
{% endmacro %}

Call it with {{ cents_to_dollars('amount_cents') }} or override the default precision with {{ cents_to_dollars('amount_cents', scale=4) }}.

The return() function preserves data types when you need to pass values back (dictionaries, lists, integers). Content between macro tags renders as string output: the generated SQL.

For macros that need flexibility without parameter explosion, use **kwargs:

{% macro flexible_macro(required_param, **kwargs) %}
    {% set optional = kwargs.get('optional_param', 'default') %}
    -- use optional here
{% endmacro %}

Context objects you’ll use constantly

dbt provides several objects with runtime information:

One pattern you’ll use constantly is the execute flag. It distinguishes between parse phase (DAG construction) and execute phase (SQL generation). Any macro that queries the database needs this guard:

{% macro get_column_values(table, column) %}
    {% if not execute %}
        {{ return([]) }}
    {% endif %}

    {% set query %}
        SELECT DISTINCT {{ column }} FROM {{ table }}
    {% endset %}
    {% set results = run_query(query) %}
    {{ return(results.columns[0].values()) }}
{% endmacro %}

Without the execute check, run_query() would fail during parsing when the table might not exist yet. The empty list return during parse phase keeps dbt happy; the real query runs during execution.

Essential macros every project needs

Before writing custom macros, check if a package already solves your problem. The dbt ecosystem has excellent options.

dbt-utils: your macro starter kit

The dbt-utils package (v1.3.3 as of early 2026) provides battle-tested macros for common patterns.

generate_surrogate_key creates hashed keys from business keys:

{{ dbt_utils.generate_surrogate_key(['customer_id', 'order_date']) }} AS order_key

star generates column lists while excluding specific columns:

SELECT
    {{ dbt_utils.star(from=ref('base__shopify__customers'), except=["_loaded_at", "_source"]) }}
FROM {{ ref('base__shopify__customers') }}

get_column_values enables dynamic SQL based on actual data:

{% set methods = dbt_utils.get_column_values(
    table=ref('base__shopify__payments'),
    column='payment_method',
    order_by='count(*) desc',
    max_records=50
) %}

date_spine creates continuous date sequences for time-series analysis:

{{ dbt_utils.date_spine(
    datepart="day",
    start_date="cast('2020-01-01' as date)",
    end_date="cast('2026-12-31' as date)"
) }}

Data quality with dbt-expectations

The dbt-expectations package ports Great Expectations-style tests to dbt. It’s particularly useful for validations that go beyond basic testing:

columns:
  - name: email
    tests:
      - dbt_expectations.expect_column_values_to_match_regex:
          regex: '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
  - name: created_at
    tests:
      - dbt_expectations.expect_row_values_to_have_recent_data:
          datepart: hour
          interval: 3

I cover this package in depth in my dbt-expectations guide. Use it when you need regex validation, statistical distributions, or freshness checks beyond what built-in tests provide.

Patterns worth stealing

Some patterns appear in nearly every mature dbt project. Here are two worth adopting early.

Audit columns track ETL metadata:

{% macro add_audit_columns() %}
    CURRENT_TIMESTAMP() AS _loaded_at,
    '{{ invocation_id }}' AS _dbt_invocation_id,
    '{{ target.name }}' AS _dbt_target
{% endmacro %}

Call it at the end of your select: SELECT *, {{ add_audit_columns() }} FROM ...

Environment-aware limiting speeds up development runs:

{% macro limit_data_in_dev(column_name='created_at', days=3) %}
    {% if target.name == 'dev' %}
        AND {{ column_name }} >= CURRENT_DATE() - {{ days }}
    {% endif %}
{% endmacro %}

Add it to your WHERE clauses: WHERE 1=1 {{ limit_data_in_dev() }}. In dev, you’ll query three days of data. In prod, the clause disappears.

Writing maintainable macros

Having useful macros is one thing. Keeping them maintainable as your project grows is another.

The rule of three

Wait until code appears three times before abstracting. First occurrence: write it inline. Second occurrence: note the pattern. Third occurrence: extract to a macro.

dbt’s own guidance puts it well: “Favor readability when mixing Jinja with SQL, even if it means repeating some lines.”

Over-abstraction has real costs: each layer of indirection makes debugging harder, parameter lists grow unwieldy, and changes to the macro break multiple code paths.

Design principles that scale

Single responsibility: Each macro should do one thing. A macro that converts currency, applies discounts, and adds tax is doing too much. Split it into cents_to_dollars, apply_discount, and add_tax.

Clear naming: Use verb prefixes that indicate what the macro does. get_ retrieves data, generate_ creates output, format_ transforms strings. Prefix internal helpers with underscore: _build_filter_clause.

Reasonable parameters: Aim for five to seven parameters maximum. Required parameters come first without defaults. Optional parameters have sensible defaults. When you need more flexibility, use **kwargs rather than adding parameter fifteen.

Project organization

A clean folder structure prevents macro sprawl (see also my dbt project structure guide):

macros/
├── _macros.yml              # All macro documentation
├── generate_schema_name.sql # Override macros at root
├── utils/
│   ├── string_utils.sql
│   └── date_utils.sql
├── transformations/
│   ├── finance/
│   └── marketing/
└── tests/                   # Custom generic tests

The Brooklyn Data style guide recommends one macro per file, with the filename matching the macro name. This makes macros easy to find and keeps files focused.

Document macros in _macros.yml:

macros:
  - name: cents_to_dollars
    description: |
      Converts cents integer to dollars with precision.
      ## Usage
      ```sql
      {{ cents_to_dollars('amount_cents', scale=4) }}
      ```
    arguments:
      - name: column_name
        type: column
        description: Column containing cents
      - name: scale
        type: integer
        description: Decimal places. Defaults to 2.

For testing macros, dbt 1.8+ introduced native unit tests:

unit_tests:
  - name: test_currency_conversion
    model: mrt__finance__orders
    given:
      - input: ref('base__shopify__orders')
        rows:
          - {order_id: 1, order__amount_cents: 1000}
    expect:
      rows:
        - {order_id: 1, order__amount_dollars: 10.00}

Common pitfalls and how to avoid them

A few mistakes appear repeatedly when learning macros.

Nested curlies: Jinja expressions inside other expressions don’t need double braces.

{{ my_macro({{ var('x') }}) }}  -- Wrong
{{ my_macro(var('x')) }}        -- Correct

Variable scope in loops: Variables set inside a loop don’t persist outside it. Use namespace for counters:

{% set ns = namespace(total=0) %}
{% for item in items %}
    {% set ns.total = ns.total + item %}
{% endfor %}
{{ ns.total }}  -- Works outside loop

Missing execute guards: Any macro calling run_query() or accessing graph.nodes needs an execute check. Without it, you’ll get cryptic parse-time errors about relations not existing.

Debugging blind: When macros misbehave, check target/compiled/ for the rendered SQL. Run dbt compile --select model_name during development. Use {{ log("Debug: " ~ my_variable, info=true) }} for runtime inspection. For hard failures, {{ exceptions.raise_compiler_error("Invalid: " ~ value) }} stops compilation with a clear message.

Getting started

Macros become easier with practice. Add dbt-utils and dbt-expectations to your project to get started:

packages:
  - package: dbt-labs/dbt_utils
    version: 1.3.3
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]

Run dbt deps to install, then try generate_surrogate_key and star in a few models. Building the muscle memory for calling macros helps before writing your own.

When you find yourself copying the same SQL block for the third time, that’s your signal to extract it into a macro with a clear name and proper documentation. Readable, simple macros will always serve you better than clever ones.