Jinja and Macros: Templating SQL

A Templating Language Living Inside Your SQL Files

Every .sql file in a dbt project is not plain SQL. It is a template written in Jinja, a general-purpose Python templating language, and dbt's job before ever sending anything to your warehouse is to compile that template down into ordinary SQL. You have already been using Jinja since the first model you wrote — {{ ref('stg_orders') }}is Jinja. {{ source('shopify', 'orders') }} is Jinja. Every one of those calls is a Jinja expression that dbt evaluates at compile time and replaces with a literal string before your warehouse ever sees the file.

Jinja gives you two kinds of syntax inside a SQL file. {{ }} (double curly braces) wraps an expression — something that evaluates to a value and gets substituted directly into the compiled SQL, like {{ ref('stg_orders') }} becominganalytics.stg_orders. {% %} (curly brace plus percent) wraps a statement — control flow like if, for, macro definitions, and set assignments, none of which produce output directly but which control what Jinja generates around them.

Why bother with any of this instead of writing static SQL directly? Because static SQL cannot express things that depend on runtime context — which environment you're building for, what values exist in a list, how many columns a table has, or logic that would otherwise have to be copy-pasted nearly identically across dozens of models. Jinja turns SQL from a fixed string into something that can be generated dynamically, branch on conditions, loop over data, and be reused as a callable unit — the same reasons any other codebase reaches for a real programming language instead of only ever hardcoding literal values.

-- Written in the model file (Jinja source):
select
    order_id,
    customer_id,
    total_amount
from {{ ref('stg_orders') }}
where order_date >= '{{ var("start_date") }}'

-- What dbt actually sends to the warehouse (compiled SQL), with
-- start_date set to '2026-01-01' in dbt_project.yml or --vars:
select
    order_id,
    customer_id,
    total_amount
from analytics.stg_orders
where order_date >= '2026-01-01'

Nothing about this compiled output is special or magical — it is exactly the SQL you would have written by hand if you already knew the target schema name and the exact date to filter on. Jinja is only doing the work of filling those specifics in for you, from context dbt already knows, instead of you hardcoding them and having to edit the file by hand every time either one changes.

Branching a Model's SQL Based on Runtime Context

{% if %} lets a model's compiled SQL differ depending on a condition evaluated at compile time — most commonly which environment dbt is running against, or a variable passed in from the command line. The most frequent real use is limiting how much data a development run processes, so an engineer iterating on a model locally isn't scanning a multi-terabyte production table on every save.

select
    event_id,
    user_id,
    event_type,
    event_timestamp
from {{ source('app', 'events') }}

{% if target.name == 'dev' %}
-- only scan the last 3 days of data in development, to keep
-- local iteration fast and cheap
where event_timestamp >= dateadd('day', -3, current_date)
{% endif %}

target is a Jinja object dbt exposes automatically, describing the currently active connection profile — target.name is the profile target name (dev,prod, whatever your profiles.yml defines), and it is available in every model without any setup. Compiled against a dev target, the where clause appears in the output; compiled against prod, the whole block, condition and all, simply disappears from the compiled SQL — not commented out, genuinely absent.

-- target.name == 'dev' compiles to:
select
    event_id,
    user_id,
    event_type,
    event_timestamp
from raw.app.events
where event_timestamp >= dateadd('day', -3, current_date)

-- target.name == 'prod' compiles to:
select
    event_id,
    user_id,
    event_type,
    event_timestamp
from raw.app.events
-- no WHERE clause at all -- the {% if %} block produced zero output

A second common pattern is branching on a variable passed with --vars at the command line, which is how a single model can support an ad hoc backfill mode without needing a second, nearly-duplicate model file.

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

select
    order_id,
    customer_id,
    total_amount,
    updated_at
from {{ ref('stg_orders') }}

{% if is_incremental() and not var('full_refresh_backfill', false) %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

# normal incremental run -- only new/updated rows since the last run
dbt run --select fct_orders

# ad hoc full-history backfill -- var() makes the WHERE clause disappear
dbt run --select fct_orders --full-refresh --vars '{"full_refresh_backfill": true}'

var('full_refresh_backfill', false) reads a variable namedfull_refresh_backfill if one was passed with --vars, and falls back tofalse if it wasn't — the second argument to var() is always a default, which matters because a model referencing a variable nobody ever passes should not fail to compile entirely; it should fall back to sensible default behavior.

Looping Over a List to Generate a CASE WHEN or a Column List

{% for %} loops over a Jinja list and repeats the SQL inside the loop body once per item, substituting the loop variable each time. The single most common real use is generating a CASE WHEN expression from a list of values instead of typing out onewhen line per value by hand — useful the moment that list is long, or likely to grow, or already defined once elsewhere and worth reusing rather than retyping.

{% set regions = ['northeast', 'southeast', 'midwest', 'southwest', 'west'] %}

select
    order_id,
    state_code,
    case
    {% for region in regions %}
        when state_code in (select state_code from {{ ref('dim_state_regions') }} where region = '{{ region }}')
            then '{{ region }}'
    {% endfor %}
        else 'unknown'
    end as order_region
from {{ ref('stg_orders') }}

select
    order_id,
    state_code,
    case
        when state_code in (select state_code from analytics.dim_state_regions where region = 'northeast')
            then 'northeast'
        when state_code in (select state_code from analytics.dim_state_regions where region = 'southeast')
            then 'southeast'
        when state_code in (select state_code from analytics.dim_state_regions where region = 'midwest')
            then 'midwest'
        when state_code in (select state_code from analytics.dim_state_regions where region = 'southwest')
            then 'southwest'
        when state_code in (select state_code from analytics.dim_state_regions where region = 'west')
            then 'west'
        else 'unknown'
    end as order_region
from analytics.stg_orders

Notice what changed and what didn't: adding a sixth region to the regions list adds a sixth when clause to the compiled output automatically, with zero changes to the SQL structure around the loop. Without the loop, adding a region means manually writing one morewhen line, in the right place, with the right syntax, every single time — a small task that becomes a real source of copy-paste bugs across a dozen models that all need the same list.

{% set %} — assigning a Jinja variable inside a model

{% set name = value %} assigns a Jinja variable, scoped to the file (or the macro) it's declared in. It is how the regions list above got defined before the loop used it, and it works for any Jinja value — a list, a string, a number, or the result of another Jinja expression.

{% set payment_methods = ['credit_card', 'paypal', 'gift_card', 'store_credit'] %}

select
    order_id,
    {% for method in payment_methods %}
    sum(case when payment_method = '{{ method }}' then amount_cents else 0 end) as {{ method }}_amount_cents{{ ',' if not loop.last }}
    {% endfor %}
from {{ ref('stg_payments') }}
group by order_id

loop.last is a Jinja loop variable available inside any {% for %}block, true only on the final iteration — used here specifically to avoid a trailing comma after the last generated column, which would otherwise be a SQL syntax error. Jinja also exposesloop.first, loop.index (1-based position), and loop.index0(0-based position), all useful for the same kind of "generate valid SQL punctuation around a repeated block" problem.

A Macro Is a Function That Returns SQL

Everything in Parts 02 and 03 lived inside one model file — useful, but not reusable across other models without copy-pasting the same Jinja. A macro is dbt's answer to that: a named, reusable block of Jinja, defined once in a .sql file inside themacros/ directory using {% macro name(args) %} ...{% endmacro %}, and callable from any model, any test, or any other macro via{{ name(args) }} — the exact same mechanism you've already been using withref() and source(), both of which are themselves macros, just ones dbt ships with rather than ones you write.

{% macro cents_to_dollars(column_name, decimal_places=2) %}
    round({{ column_name }} / 100.0, {{ decimal_places }})
{% endmacro %}

This macro takes a column name and an optional number of decimal places (defaulting to 2 if the caller doesn't specify one), and returns the Jinja text that, once compiled, divides that column by 100 and rounds it — the standard conversion for a monetary amount stored as an integer number of cents, which is itself a common practice specifically because integer cents avoid the floating- point rounding errors that storing dollar amounts as a decimal can introduce.

-- models/marts/fct_orders.sql
select
    order_id,
    order_total_cents,
    {{ cents_to_dollars('order_total_cents') }} as order_total_dollars
from {{ ref('stg_orders') }}

-- models/marts/fct_refunds.sql
select
    refund_id,
    refund_amount_cents,
    {{ cents_to_dollars('refund_amount_cents') }} as refund_amount_dollars
from {{ ref('stg_refunds') }}

-- models/marts/fct_payouts.sql -- overriding the default decimal_places
select
    payout_id,
    payout_amount_cents,
    {{ cents_to_dollars('payout_amount_cents', 4) }} as payout_amount_dollars
from {{ ref('stg_payouts') }}

-- fct_orders.sql compiles to:
select
    order_id,
    order_total_cents,
    round(order_total_cents / 100.0, 2) as order_total_dollars
from analytics.stg_orders

-- fct_payouts.sql compiles to (decimal_places=4 overriding the default):
select
    payout_id,
    payout_amount_cents,
    round(payout_amount_cents / 100.0, 4) as payout_amount_dollars
from analytics.stg_payouts

This is the entire payoff of a macro, stated plainly: the cents-to-dollars conversion logic exists in exactly one place. If the business ever needs to change how that conversion works — switching to banker's rounding, say, or adding a currency-aware divisor for a model with non-USD amounts — that change happens once, in macros/cents_to_dollars.sql, and every model calling the macro picks up the new behavior automatically the next time it's compiled. Without the macro, the same fix means finding and editing every model that independently wrote out its ownround(column / 100.0, 2) expression, with no guarantee every copy was found.

Macros can take multiple parameters, including ones with defaults

cents_to_dollars(column_name, decimal_places=2) already showed a default parameter value — any macro argument can have one, using ordinary Jinja/Python-styleparameter=default syntax, and callers can omit it entirely (falling back to the default) or override it positionally or by name, exactly like a function call in most general- purpose languages.

Controlling How dbt Names Custom Schemas

dbt ships with a number of macros that control its own internal behavior, and one of the most commonly customized is generate_schema_name — the macro dbt calls to decide the actual schema a model materializes into, whenever that model's config sets a customschema: value. Understanding it is a genuinely useful worked example of a macro doing real, structural work rather than just a small text-substitution helper.

By default, when a model sets {{ config(schema='marketing') }}, dbt does not simply build the model into a schema literally named marketing. It concatenatesthe target's configured schema with the custom schema name — for exampleanalytics_marketing — specifically so that two different developers running the same project against their own personal dev schemas don't collide with each other or with production by all writing to one literal schema named marketing at once.

{% macro generate_schema_name(custom_schema_name, node) %}

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ default_schema }}_{{ custom_schema_name | trim }}

    {%- endif -%}

{% endmacro %}

Many teams find this default awkward in production — they want a model configured withschema: marketing to build into a schema literally called marketing in production, not analytics_marketing, while still keeping the concatenated, collision-safe behavior for developers' own dev environments. Because generate_schema_nameis just a macro, it can be overridden by defining a macro with the exact same name in your own project's macros/ directory — dbt uses your project's version instead of its built-in default the moment one exists.

{% macro generate_schema_name(custom_schema_name, node) %}

    {%- set default_schema = target.schema -%}

    {%- if target.name == 'prod' and custom_schema_name is not none -%}

        {{ custom_schema_name | trim }}

    {%- elif custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ default_schema }}_{{ custom_schema_name | trim }}

    {%- endif -%}

{% endmacro %}

With this override in place, a model tagged schema: marketing builds into the schema literally named marketing when run against the prod target, but still falls back to the collision-safe dev_alice_marketing-style concatenation for any developer's own dev target — exactly the behavior most teams actually want, achieved entirely by overriding one built-in macro rather than by any special dbt configuration flag.

dbt compile: See the Actual SQL Before It Runs

Every code example so far has shown a "compiles to" block, and that is not incidental — it is the single most useful habit for working with Jinja-heavy models. dbt compile resolves every {{ }} and executes every {% %} block in a model, exactly asdbt run would, but stops short of actually sending anything to the warehouse. The resulting plain SQL file is written to target/compiled/<project>/models/.../model_name.sql, fully resolved and readable.

$ dbt compile --select fct_orders

Running with dbt=1.8.0
Concurrency: 4 threads (target='dev')

Compiled node 'fct_orders' is:
  target/compiled/my_project/models/marts/fct_orders.sql

This single command answers the question that otherwise takes real guesswork to answer: "what SQL is this Jinja actually going to produce?" A {% for %} loop with an off-by-one comma, an {% if %} branch that silently evaluates the wrong way for a given target, a macro call passing arguments in the wrong order — every one of these is far easier to spot by reading the compiled SQL directly than by staring at the Jinja source trying to mentally simulate what it will produce, or worse, only discovering the problem when dbt runfails with a warehouse-level syntax error that points at a line number in the compiled file, not the Jinja file you actually edited.

-- The Jinja source (a subtle bug: trailing comma on the last column)
select
    order_id,
    {% for method in payment_methods %}
    sum(case when payment_method = '{{ method }}' then amount_cents else 0 end) as {{ method }}_amount_cents,
    {% endfor %}
from {{ ref('stg_payments') }}
group by order_id

-- dbt compile reveals the actual problem immediately:
select
    order_id,
    sum(case when payment_method = 'credit_card' then amount_cents else 0 end) as credit_card_amount_cents,
    sum(case when payment_method = 'paypal' then amount_cents else 0 end) as paypal_amount_cents,
    sum(case when payment_method = 'gift_card' then amount_cents else 0 end) as gift_card_amount_cents,
from analytics.stg_payments
group by order_id

-- the trailing comma before "from" is now plainly visible --
-- dbt run would have failed with a warehouse syntax error near "from",
-- but dbt compile shows exactly why, without ever touching the warehouse

dbt compile also compiles the whole project by default if run with no--select flag, which is useful for a final check before a deploy, but scoping it to one model with --select during active development keeps the feedback loop tight — compile the one model you're editing, read the output, adjust, repeat, without waiting on the rest of the project or spending a warehouse query on a model you already know has a Jinja problem.

{%- -%} — Trimming the Blank Lines Jinja Leaves Behind

Jinja's default behavior leaves the raw whitespace and newlines surrounding every{% %} tag exactly as written in the source file, even though those tags themselves produce no SQL output. Across a file with several control blocks, this reliably produces compiled SQL riddled with blank lines and stray indentation — cosmetically ugly, and genuinely harder to read when you're using dbt compile from Part 06 to debug something, since the noise makes the actual generated SQL harder to scan.

select
    order_id
    {% if include_customer_name %}
    , customer_name
    {% endif %}
from {{ ref('stg_orders') }}

-- compiles to (note the blank lines where the tags used to be):
select
    order_id

    , customer_name

from analytics.stg_orders

Adding a hyphen to either side of a Jinja tag — {%- to trim whitespace before the tag, -%} to trim whitespace after it — tells Jinja to strip the surrounding whitespace at that spot rather than preserving it. This is purely cosmetic; it changes nothing about what the compiled SQL logically does, only how many blank lines and how much stray indentation surround it.

select
    order_id
    {%- if include_customer_name %}
    , customer_name
    {%- endif %}
from {{ ref('stg_orders') }}

-- compiles to, cleanly:
select
    order_id
    , customer_name
from analytics.stg_orders

The generate_schema_name override in Part 05 already used this throughout —{%- set default_schema = target.schema -%} and every {%- if -%} /{%- elif -%} / {%- else -%} / {%- endif -%} in that macro trims whitespace on both sides, which is exactly why that macro's compiled output is a single clean schema name with no stray blank lines or leading spaces mixed in — a real, practical concern there, since the macro's entire output gets used directly as a literal schema name in generated DDL.

When Jinja Makes SQL Harder to Read, Not Easier

Everything in this module is a genuine capability, and every capability can be overused. The specific failure mode worth naming directly: a model wrapped in so many nested loops, conditionals, and macro calls that a new team member opening the file cannot tell what SQL actually runs without mentally executing the Jinja first — effectively reverse-engineering a small interpreter by eye before they can even start reasoning about the business logic the model is supposed to express.

{% set metric_configs = get_metric_configs() %}
{% set dimensions = get_active_dimensions(exclude=['deprecated', 'internal']) %}

select
    {% for dim in dimensions %}
    {{ dim.column_expression }} as {{ dim.alias }}{{ ',' if not loop.last or metric_configs }}
    {% endfor %}
    {% for metric in metric_configs %}
    {% if metric.requires_dedup %}
    {{ dedup_aggregate(metric.column, metric.agg_type, metric.partition_keys) }} as {{ metric.alias }}{{ ',' if not loop.last }}
    {% else %}
    {{ metric.agg_type }}({{ metric.column }}) as {{ metric.alias }}{{ ',' if not loop.last }}
    {% endif %}
    {% endfor %}
from {{ get_source_model(metric_configs) }}
{% if should_apply_filters() %}
where {{ build_filter_clause() }}
{% endif %}
group by {{ generate_group_by_list(dimensions) }}

Nothing here is individually wrong — every construct is a legitimate feature from this module. Stacked together, though, none of the actual business logic is visible in the file at all: what columns this model produces, what it filters on, and how it aggregates are all deferred to other macros and functions the reader has to go find and read separately, several layers deep, before they know what this model actually does. Debugging it means running dbt compile(Part 06) just to find out what SQL exists at all — not as an optional best practice, but as the only realistic way to understand the file.

The practical fix is rarely "remove all Jinja" — it is usually "inline the SQL that is genuinely specific to this one model, and reserve macros for the pieces that are demonstrably reused elsewhere," the same discipline Part 04's cents_to_dollars example followed: one small, clearly named macro for one clearly reused calculation, called from ordinary, readable SQL around it — not a model whose entire structure is generated from configuration objects a reader has to trace through several files to reconstruct.

Macros Deserve the Same Documentation Discipline as Models

A macro that grows past a couple of lines is exactly the kind of thing a future maintainer needs explained, and dbt supports documenting macro arguments the same way it supports documenting model columns — through schema.yml, using a dedicated macros: key. This is easy to skip specifically because a macro is not a model and doesn't show up in the same places a missing model description would, but an undocumented macro carries the exact same cost described in the documentation module: whoever calls it next either has to read the macro's Jinja source directly to understand its arguments, or guess.

version: 2

macros:
  - name: cents_to_dollars
    description: >
      Converts an integer cents column into a rounded dollar amount.
      Used across every financial model that stores monetary values as
      integer cents to avoid floating-point rounding errors.
    arguments:
      - name: column_name
        type: string
        description: The column, as a string, holding the integer cents value to convert.
      - name: decimal_places
        type: integer
        description: How many decimal places to round the resulting dollar amount to. Defaults to 2.

This documentation shows up on the generated dbt docs site exactly like a model or column description would, and it is picked up automatically the next time dbt docs generateruns — no separate step, no separate tooling, just one more entry in the same YAML-driven documentation system covered in depth in the previous module.

Where macros live in a growing project

A small project can keep every macro directly in macros/ with no further structure. Once a project accumulates more than a handful of macros, most teams organize them into subdirectories by purpose — a common convention is separating generic-test macros (from the testing module), utility macros like cents_to_dollars, and dbt-internal overrides likegenerate_schema_name into their own subfolders, purely for the benefit of a human browsing the project trying to find a specific macro quickly.

macros/
  utils/
    cents_to_dollars.sql
    pivot_column_list.sql
  overrides/
    generate_schema_name.sql
  tests/generic/
    test_positive_value.sql
    test_value_within_range.sql

None of this structure changes how a macro is called — {{ cents_to_dollars(column) }}works identically no matter which subdirectory the macro's file physically lives in, since dbt discovers macros by name across the entire macros/ tree rather than by file path. The subdirectory structure exists purely for human navigability, the same reasoning that motivates organizing models/ into staging/, intermediate/, andmarts/ even though dbt itself does not require any particular models directory layout to function correctly.

A short checklist for the moment you're about to write a new macro

Before adding a new macro, a quick set of questions mostly restates Part 04's DRY reasoning and Part 08's over-templating warning as a concrete pre-flight check, worth running through before the file is even created.

• ✓Does this logic actually appear, or is it about to appear, in two or more models — not just hypothetically someday?
• ✓Would a competent SQL-only reader still understand the calling model's SQL with this logic replaced by a named macro call?
• ✓Does the macro have a clear, specific name describing what it computes, not a vague one like helper or util?
• ✓Are the macro's arguments documented in macros/schema.yml, especially any argument with a default value?
• ✓If the macro will be called often, has it been checked with dbt compile against at least one real model to confirm the generated SQL is actually correct?

A macro that clears all five is doing exactly the job Part 04 describes: removing genuine duplication while staying readable at the call site. A macro that only clears the first question — "yes it's used twice" — but fails the second is worth a second look, since Part 08's entire warning is that reuse alone doesn't justify hiding logic behind an abstraction if the result is a model nobody can read without chasing the macro down first.

Five Misconceptions About Jinja and Macros in dbt

Three Ways Jinja and Macros Show Up in Real dbt Projects

5 Interview Questions — With Complete Answers

These five questions cover the ground an interviewer actually probes when checking real dbt fluency around templating: not whether you can recite Jinja syntax, but whether you understand what dbt is doing with it at compile time, and where the line sits between reasonable reuse and an over-engineered model.

Five Mistakes Engineers Make Writing Jinja and Macros

Jinja and Macro Errors — And Exactly Why They Happen