dbt Macros and Jinja: The Patterns That Earn Their Keep, and the Clever Ones That Do Not

I once spent an afternoon trying to find where a column was defined. The model was thirty lines. Twenty-eight of them were a macro call. That macro called two more macros, one of which read a YAML file in vars and generated a CASE expression from it. The actual business rule — that orders under a dollar are excluded — was in a dictionary in dbt_project.yml, four files away from the model that depended on it.

The person who built it was smart. That was the problem. It saved maybe sixty lines of SQL across the project, and it cost every future reader an afternoon.

I still write macros. I write fewer than I used to, and I have a harder test for whether one should exist. Here is where I have landed after a few years of maintaining other people’s Jinja.

The test I apply before writing a macro

Three questions, in order:

1. Does this exact SQL appear in at least three places?
2. If the logic changed, would it need to change in all three at once?
3. Can someone reading the call site guess what the SQL looks like without opening the macro?

All three yes: write it. Any one no: paste the SQL a third time and move on.

Question two is the one people skip. Three models that happen to contain the same datediff expression are not duplication if they would change independently — that is coincidental similarity, and macro-ing it couples three things that had no business being coupled. The first time one of them needs a different rule, you get a boolean flag parameter, then a second one, and now the macro has four branches and nobody can read any of the call sites.

Question three is about the reading cost. Every macro puts a layer between the file and the query. Sometimes that layer is obviously worth it. Usually it is worth it only if the name is so clear that a reader does not need to follow it.

flowchart TD
  sql["Same SQL in 3+ places?"] -->|"no"| paste["Write the SQL"]
  sql -->|"yes"| change["Would all change together?"]
  change -->|"no"| paste
  change -->|"yes"| guess["Name makes behavior obvious?"]
  guess -->|"no"| rename["Fix the name, or write the SQL"]
  guess -->|"yes"| macro["Write the macro"]

Most candidates die at the second question. That is the point.

Macros that earn their keep

Centralizing a business definition

This is the best macro category and the one I write most. One definition, used in many places, that the business will change.

-- macros/net_revenue.sql
{% macro net_revenue(gross_col='gross_amount', refund_col='refund_amount') %}
    -- Finance definition, agreed 2026-02. Excludes tax and shipping,
    -- nets refunds in the period the refund occurred (not the sale).
    round(
        coalesce({{ gross_col }}, 0) - coalesce({{ refund_col }}, 0),
        2
    )
{% endmacro %}

Twelve lines including the comment, used in nine models. When finance changes the definition next quarter, it is one PR and the comment tells the reviewer what the old rule was. This is the whole value proposition: a single place where a contested business rule lives.

Note it takes column names rather than assuming them. That keeps it usable in models whose upstream naming differs without adding branches.

Cross-warehouse or cross-environment differences

-- macros/target_limit.sql
{% macro limit_dev(rows=1000) %}
    {#- Cap row scans in dev so nobody accidentally builds prod-sized tables.
        Applied via a folder-level hook, not pasted into every model. -#}
    {% if target.name in ('dev', 'ci') %}
        limit {{ rows }}
    {% endif %}
{% endmacro %}

This is legitimate metaprogramming because the difference genuinely is environmental, and there is no non-Jinja way to express it. Use it sparingly and never for business logic — a model whose output differs between dev and prod for a reason other than row volume is a model you cannot test.

Custom generic tests

Generic tests are macros, and they are the highest-leverage ones in most projects because they turn a one-off check into something you can apply in YAML anywhere.

-- tests/generic/test_positive_and_reasonable.sql
{% test positive_and_reasonable(model, column_name, max_value) %}

-- Returns failing rows. Any row returned means the test failed.
select
    {{ column_name }} as offending_value,
    count(*)         as occurrences
from {{ model }}
where {{ column_name }} < 0
   or {{ column_name }} > {{ max_value }}
group by 1

{% endtest %}

models:
  - name: fct_orders
    columns:
      - name: order_total
        tests:
          - positive_and_reasonable:
              max_value: 250000
              config:
                severity: warn
                warn_if: '>5'

That fits the severity discipline from my dbt testing strategy: the macro provides the mechanism, the YAML provides the policy.

Incremental predicate helpers

Watermark logic is repetitive, easy to get subtly wrong, and needs to change uniformly when you discover your late-arriving window is too short.

-- macros/incremental_window.sql
{% macro incremental_window(ts_col, lookback_days=3) %}
    {#- Standard late-arrival window. Widen here, not per model. -#}
    {% if is_incremental() %}
        where {{ ts_col }} >= (
            select coalesce(max({{ ts_col }}), '1900-01-01'::timestamp)
                   - interval '{{ lookback_days }} days'
            from {{ this }}
        )
    {% endif %}
{% endmacro %}

Used in a model:

{{ config(materialized='incremental', unique_key='event_id') }}

select
    event_id,
    user_id,
    event_type,
    occurred_at
from {{ ref('stg_events__raw') }}
{{ incremental_window('occurred_at', lookback_days=5) }}

Three properties make this a good macro: the call site reads clearly, the parameter is the thing that actually varies, and the day you learn your source can be seven days late you change one default. The trade-offs behind that window are in dbt incremental models in production.

The clever ones that make a project unmaintainable

Generating models from a config dictionary

Somebody always builds this. A dict in dbt_project.yml lists twelve entity names, a macro loops over it, and one .sql file produces twelve models via dbt_utils.union_relations and a for loop, or worse, via a codegen script committed to the repo.

The problem is not that it does not work. It is that:

• git blame on a business rule points at a loop.
• A PR that changes one entity’s behavior shows a diff in a config file with no SQL context.
• You cannot open the model file and read what runs.
• Adding one exception for one entity requires either a branch inside the loop or breaking the entity out, and the second option is admitting the abstraction was wrong.

Twelve near-identical models with twelve readable files beat one generator every time. Disk is free. Comprehension is not.

run_query at parse time

This is the most expensive habit in dbt Jinja, and it is subtle because it looks like a clever way to make a model adaptive.

{#- DO NOT DO THIS at the top level of a macro or model body -#}
{% set cols = run_query("select column_name from information_schema.columns
                         where table_name = 'events'") %}

dbt parses your entire project on every command. dbt ls, dbt parse, dbt compile, dbt build, and every IDE autocomplete that shells out to dbt. If that query is at parse time, every one of those commands opens a warehouse connection and runs it. On a project with a dozen of these, dbt ls takes ninety seconds and your CI spins up a warehouse to list model names.

Guard it. Always.

{% macro get_event_types() %}
    {#- execute is false during parsing. Return a safe default then. -#}
    {% if execute %}
        {% set results = run_query(
            "select distinct event_type from " ~ ref('stg_events__raw')
        ) %}
        {{ return(results.columns[0].values()) }}
    {% else %}
        {{ return([]) }}
    {% endif %}
{% endmacro %}

Even guarded, I use this rarely. A model whose columns depend on a query result is a model whose schema changes without a code change, which means a PR can be green and production can still break. If you need dynamic pivoting, prefer a fixed list of columns maintained in YAML with a test that fails when a new value appears — the failure is a signal, not an outage.

Deep macro chains

A macro that calls a macro that calls a macro. Each level is defensible; the stack is not. My rule is two levels from a model, and the second level has to be a well-known utility like dbt_utils.generate_surrogate_key. Beyond that, the compiled SQL in target/compiled/ becomes the only readable artifact, and if the compiled output is easier to read than the source, you have inverted the point of source code.

Overriding generate_schema_name in surprising ways

-- macros/generate_schema_name.sql
{% 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 }}
    {%- else -%}
        {#- dev and CI stay namespaced so runs never collide -#}
        {{ default_schema }}_{{ custom_schema_name | default('main') | trim }}
    {%- endif -%}
{%- endmacro %}

This particular override is fine and I use it in every project — prod gets clean schema names, dev and CI get prefixed ones so concurrent runs cannot collide. What is not fine is the version I have seen that also branches on tags, environment variables, and the day of the week. Object naming is infrastructure. When it is unpredictable, the first thing a new engineer learns is that they cannot find their own tables.

Testing macros, which almost nobody does

A macro used by forty models is more load-bearing than any single model, and it usually has zero tests. Two techniques cover most of it.

First, a singular test that asserts the macro’s output for known inputs:

-- tests/assert_net_revenue_nets_refunds.sql
-- Fails if the macro stops subtracting refunds or stops handling nulls.
with fixture as (
    select 100.00 as gross_amount, 25.00 as refund_amount, 75.00 as expected
    union all
    select 100.00, null, 100.00
    union all
    select null, 10.00, -10.00
)

select *
from fixture
where {{ net_revenue() }} != expected

Second, unit tests on the models that use the macro. A unit test with fixed inputs and expected outputs will catch a macro regression at the place it matters, and it does not care whether the logic lives in the model or a macro.

Both run in CI. Both are cheap. The macro that computes revenue deserves at least as much scrutiny as the model that displays it.

Where teams get this wrong

Writing a macro on the second occurrence. Two is a coincidence. The macro written at two occurrences is the one that grows a boolean parameter at three and a dispatch table at five.

Unguarded run_query. Every dbt command becomes a warehouse query. This shows up as “why is CI slow” long before anyone connects it to Jinja.

Macros with more than three parameters. Each parameter is a branch in someone’s head. Four parameters means the abstraction spans two different things that should be two macros.

No comment explaining the business rule. A macro named net_revenue with no note about what it excludes is worse than inlined SQL, because it looks authoritative. Put the definition and the date it was agreed in the macro body.

Jinja control flow that generates different DAGs per environment. A {% if target.name == 'prod' %} around a ref() means dev and prod have different lineage. Your CI validated a graph that does not exist in production.

Committing generated SQL. If you have a script that writes model files, the files in the repo are output, not source, and the next person will edit them by hand and lose the change on the next generation run.

FAQ

Should I use dbt_utils instead of writing my own?

Yes, for anything it covers. generate_surrogate_key, star, date_spine, union_relations, and pivot are maintained, tested, and known to every dbt engineer you will hire. A package macro has a name your team already recognizes, which is most of the reading cost gone.

Is {{ dbt_utils.star() }} a good idea in production models?

In staging, for a wide passthrough with a few exclusions, it is tolerable. In marts, no. star means the model’s output columns change silently when the source changes, so a new upstream column lands in your BI tool without a review. Explicit column lists in marts are a feature.

How do I debug a macro?

dbt compile --select my_model and read target/compiled/. That is the actual SQL. If reading it is a surprise, the macro is too clever. For a quick check of an expression, dbt run-operation with a small macro that logs the output is faster than compiling a whole model.

Where should macros live?

macros/ with one macro per file, named after the macro. Subfolders by purpose (macros/tests/, macros/finance/) once you pass about fifteen. dbt does not care about the layout; humans searching the repo do, and it is the same argument as the folder discipline in my dbt project structure guide.

Can macros hurt run performance?

Not directly — Jinja compiles before the warehouse sees anything. But macros make it easy to generate SQL you would never write by hand, and a loop that produces eighty union all branches will absolutely hurt. Read the compiled SQL for any macro that generates repeated blocks, and check it in a query profile if it is on a critical path.

Should hooks be macros?

Yes. on-run-end hooks that grant permissions or log run metadata belong in a named macro called from dbt_project.yml, not as inline SQL strings in YAML. Inline SQL in YAML gets no syntax highlighting, no linting, and no reviewer attention.

What this means for your pipelines

Jinja is a text templating language pointed at your warehouse, and it will happily let you build a framework. The projects I enjoy maintaining used it as a way to say a business rule once. The projects I dread used it to avoid typing.

Practically: write macros for contested business definitions, for custom generic tests, for genuine environment differences, and for watermark boilerplate. Guard every run_query with execute. Keep the call stack two deep. Test the macros that carry business meaning the same way you test models.

And when you are tempted by the generator that would collapse twelve model files into one loop, ask what a code review of a change to one of those twelve looks like. If the answer is “a one-line diff in a YAML dict with no SQL context,” write the twelve files. The person debugging revenue at 8 a.m. in two years will not thank you, because they will not notice — and that is exactly the outcome you want.