Macros and Jinja

Macros are reusable SQL snippets, parameterized with Jinja. They're how you avoid copy-pasting SQL across 30 models.

Anatomy

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(cents_column, decimal_places=2) %}
    ({{ cents_column }}::numeric / 100.0)::numeric(10, {{ decimal_places }})
{% endmacro %}

Use anywhere:

SELECT order_id, {{ cents_to_dollars('amount_cents') }} AS amount_usd
FROM {{ ref('stg_orders') }}

Jinja syntax

• {% ... %} — control flow (if, for, set, macro).
• {{ ... }} — print expression.
• {# ... #} — comment.

Common patterns:

{% if target.name == 'prod' %}
    WHERE created_at < CURRENT_DATE
{% endif %}

{% for col in ['amount', 'tax', 'tip'] %}
    SUM({{ col }}) AS total_{{ col }}{% if not loop.last %},{% endif %}
{% endfor %}

{% set start_date = '2024-01-01' %}

When to write a macro

YES:

• Used in 3+ models.
• Encapsulates non-obvious transformation.
• Generates repetitive SQL.
• Wraps a database-specific quirk.

NO:

• One-off.
• Adds Jinja complexity for trivial substitution.
• Hides business logic from the model itself.

Built-in context variables

• {{ this }} — current model's table.
• {{ target }} — current target (target.name, target.schema, target.database).
• {{ ref('model') }} — resolved table name.
• {{ source('source', 'table') }} — resolved source.
• {{ env_var('NAME') }} — environment variable.
• {{ var('var_name') }} — project variable from dbt_project.yml.

Project variables

# dbt_project.yml
vars:
  start_date: '2024-01-01'

WHERE order_placed_at >= '{{ var("start_date") }}'

dbt_utils first

Don't reinvent. Common ones:

• dbt_utils.generate_surrogate_key(['col1', 'col2']) — hash-based surrogate keys.
• dbt_utils.pivot(...) — pivot a column into columns.
• dbt_utils.star(from=ref('upstream'), except=['col']) — SELECT * minus columns.
• dbt_utils.union_relations([ref('a'), ref('b')]) — UNION ALL with schema reconciliation.

Common mistakes

1. Macros for one-shot use.
2. Hiding business logic where reviewers won't see it.
3. Macros without tests.
4. Reimplementing dbt_utils.
5. Over-using {% if target.name %} to branch logic.

Takeaway

Macros for plumbing and code generation. dbt_utils first. Keep business logic visible in models.