skip to content

How do you decide how much Jinja templating a dbt project should allow in its models?

level: principalimportance: should knowfreq 38%

answer

  1. the reviewer reads one thing, the warehouse runs another
  2. what a macro emits versus what it decides
  3. one file changed, forty models affected
  4. can you predict the compiled SQL from the call site

basics

~20 s

Allow Jinja that removes duplication without hiding what a model does: shared expressions, environment switches, config. Ban templating that decides a model's grain, joins or output columns, because reviewers then read a template while the warehouse runs something else.

solid answer

~50 s

Set the rule by what a reviewer can verify. Macros that factor out a repeated **expression** — a surrogate key, a currency conversion, a standard cast, a grant — pay for themselves and read fine at the call site. Templating that determines a model's **shape** — its grain, its join set, its output columns — moves the truth into `target/compiled/`, so git diffs stop reflecting SQL changes and reviewers approve something they have not read. My practical line: no Jinja that changes the output schema silently, no run-time introspection when a seed would do, macros named for what they emit, and compiled SQL attached to review for anything macro-heavy. Then back it with dbt's YAML unit tests on the models that depend on a shared macro, so a macro change fails CI rather than a dashboard.

code

sql · 5 lines
sql
-- Fine: the macro emits an expression, the model's shape is visible
select
    order_id,
    {{ cents_to_dollars('amount_cents') }} as amount
from {{ ref('stg_orders') }}

go deeper

for a junior

Take away one habit: before you approve or ship a templated model, read its compiled SQL and check it says what you meant.

for a middle

Be able to explain concretely why a heavily templated model is harder to review and debug, and name the pattern you would use instead for a data-dependent column list.

for a senior

Argue the line with examples: expression-level macros are fine, structure-level templating is not, and back it with unit tests and compiled-SQL review on the models that matter.

for a principal

Own the policy and its enforcement — written guidance, a CI compile-diff job, a testing requirement for shared macros, and a migration plan that fixes offenders as they change rather than in one sweep.

## Why this is a real decision, not a style preference Jinja is unusually powerful for a transformation tool: full loops, conditionals, variables, and macros that can emit arbitrary SQL text. Every project therefore chooses, implicitly or explicitly, how much of its logic lives in the template layer versus the SQL layer. The choice has three concrete consequences. **The reviewed artifact is not the executed artifact.** A pull request shows a template. What runs is in `target/compiled/`. For a model with one `{{ cents_to_dollars('amount') }}` call, the gap is trivial. For a model that loops over a variable list to build its join set, the reviewer genuinely cannot tell what SQL will run. **A macro is an API with a large blast radius.** A macro used by forty models is a coupling point across forty models. Changing it is a schema-affecting change to all of them, but the diff touches one file, and nothing about the review process signals the scope. **Debuggability degrades non-linearly.** One layer of templating is easy to reason about. A macro that calls a macro that loops over a list produced by a third macro is a program, and you are debugging it through generated SQL with no stack trace. ## A workable set of rules What I have found holds up: 1. **Macros for expressions, not for structure.** A macro may produce a scalar expression, a predicate, a column list. It should not produce the FROM clause, the join graph, or decide the grain. If reading the model no longer tells you what one row is, the templating went too far. 2. **No silent schema changes.** Any construct where the output column set depends on data — introspective pivots, `star()` over a volatile relation — needs an explicit owner and a test. Prefer a seed or a small dimension model that lists the values, so a new value arrives as a reviewed pull request. 3. **Environment switches are named and few.** `{% if target.name != 'prod' %}` to limit dev scan volume is fine and should look identical in every model; a growing set of environment branches means production and development are diverging in ways nobody tested. 4. **Introspective queries are opt-in.** `run_query` at compile time makes the project uncompilable when upstream relations do not exist, which breaks fresh environments and slim CI runs. Allow it deliberately, not by habit. 5. **Compiled SQL in review for macro-heavy changes.** Either attach it, or run a CI job that compiles the project and posts a diff of the compiled output. This is the single highest-leverage control, because it closes the gap between what was reviewed and what runs. 6. **Name macros for what they emit.** `cents_to_dollars` reads at the call site; `apply_logic` does not. A reader should not need to open the macro file to understand the model. ## The safety net Macros have no automatic tests. Two mechanisms help: - **Unit tests on the models that use them.** dbt 1.8 added YAML-defined unit tests, where you supply fixture rows for a model's inputs and assert the expected output rows. A model that exercises a shared macro becomes a regression test for that macro, run in CI on every change. - **Data tests as invariants.** Uniqueness, not-null and relationship tests on the outputs of macro-heavy models catch the class of bug where a templated join silently fans out. They are coarse but they fire. Both belong to the testing topic in detail; the point here is that a templating policy without a test policy is a preference, not a control. ## The counterweight It is possible to be too restrictive. A project that bans macros entirely ends up with the same 12-line surrogate-key expression copy-pasted into thirty models, drifting subtly, with a null-handling bug fixed in nineteen of them. That is a worse failure than a hard-to-read template, because it is invisible. The goal is not less Jinja; it is Jinja whose effect a reader can predict from the call site. A useful test when reviewing: **can you predict the compiled SQL from reading the model file?** If yes, the templating is doing its job. If you have to open two macro files and reason about a loop, you have crossed the line — either simplify, or accept the cost consciously and add the tests and review controls that make it survivable. ## How to introduce the policy Don't retrofit by rewriting. Write it down in the project's contributing guide with two or three examples of allowed and disallowed patterns, apply it to new work, and take on existing offenders when they next need to change. Pair it with the CI compile-diff job, which does most of the enforcement without anyone having to police style in review comments.

  • What CI control closes the gap between reviewed template and executed SQL?
    A job that runs `dbt compile` on the pull request branch and on the base, then posts the diff of `target/compiled/`. Reviewers see the SQL that will actually run, and a one-line macro change that alters forty models becomes visible as forty changed files rather than one.
  • How do you give a widely-used macro a regression net?
    Write dbt unit tests, available from dbt 1.8, on a couple of models that exercise it: supply fixture input rows in YAML and assert the expected output rows. A change to the macro then fails CI with a concrete row-level diff instead of surfacing as a wrong number on a dashboard weeks later.
  • When is copy-pasted SQL genuinely better than a macro?
    When the duplication is coincidental rather than structural — two models that happen to compute something similar today for unrelated reasons. Factoring those together couples them, and the next change has to satisfy both. Macros should encode a shared rule, not a shared appearance.

It is the same tradeoff as code generation anywhere: generating the boring parts saves real effort, but once the generator decides the program's structure, you are reviewing the generator and shipping something nobody read.

saying these in an interview costs you the question

  • Treats templating volume as a matter of personal style only
  • Reviews the template and never looks at compiled output
  • Factors coincidental duplication into a shared macro
  • Bans macros outright and accepts copy-paste drift instead
  • Cannot name any test that would catch a bad macro change

context

resolving links…