dbt Model & Macro Writing

The reasoning

The loop body is emitted once per item with `{{ status }}` substituted, so three iterations produce three aggregate expressions. Everything outside the tags — the `select`, the `customer_id`, the `from` and the `group by` — is emitted verbatim exactly once.

The comma is the part being tested. A comma written plainly inside the loop body gives you a trailing comma before `from`, which is a syntax error on most warehouses. `{%- if not loop.last %},{% endif %}` emits it for every iteration but the last. Jinja's loop object also gives you `loop.first`, `loop.index` (1-based) and `loop.index0`, and `loop.first` is the alternative idiom — put the comma *before* each item except the first.

The whitespace markers are doing real work here too. `{% for ... -%}` strips the newline after the tag, and `{%- if ... %}` strips the whitespace before it, which is what keeps each generated column on its own line rather than scattered across a page of blank lines.

The judgment question that follows is whether the list should be hardcoded. A literal `{% set statuses = [...] %}` is honest and reviewable: the compiled SQL is stable, and a new status in the source silently gets no column — which may be exactly what you want, since a column appearing without a code change breaks every downstream consumer that selected explicitly. Driving the list from the data requires `run_query`, and buys you automatic columns at the cost of a model whose schema changes without a commit.

The reasoning

A Jinja tag produces no output, but the newline and indentation surrounding it are ordinary template text and are emitted. So `{% if true %}` on its own line contributes an empty line to the compiled SQL. Ten tags in a model give you ten blank lines, and the SQL that was going to be your debugging artifact becomes something you scroll through.

`{%-` strips whitespace immediately before the tag and `-%}` strips it immediately after; `{{- ... -}}` does the same for expressions. Use them so that each generated fragment lands where you would have typed it.

It stops being cosmetic in three places. Inside a loop, where the accumulated whitespace multiplies by the iteration count and the output becomes genuinely unreadable. Anywhere the rendered text goes into a single-line context — a `--` comment, a hook string, a JSON config — where an injected newline changes the meaning or truncates it. And in anything hash-based: if you feed rendered SQL into a checksum or compare two compiled models, a whitespace difference is a difference.

The practical habit worth stating: the compiled file is the artifact you will debug from at 3am, so keep it readable on purpose. `dbt compile --select my_model && cat target/compiled/...` after writing any non-trivial Jinja takes ten seconds and is how you learn what your own template does.

The reasoning

`target` is available in Jinja and carries the current target's name, schema, database and threads. Wrapping a filter in `{% if target.name == 'dev' %}` gives you a model that reads a week of data locally and everything in production, from one file.

The rule that keeps it safe is that the branch may only reduce the rows scanned. Filter on a date, sample, or limit. The moment the branch changes a join, an aggregation or a column list, dev and production are two different models and your local testing proves nothing about the thing that runs at night.

Two better versions worth offering. Put the cutoff in a var — `{% if target.name == 'dev' %}where updated_at >= '{{ var("dev_start", "2026-01-01") }}'{% endif %}` — so it is tunable without editing models. Or push the limit up into the staging layer only, so every downstream model inherits a small dataset and no mart contains an environment branch at all. The second is the one I would argue for: one place to reason about, and the marts stay identical across environments.

The failure this prevents is worth naming because it is the reason people ask. Without it, engineers avoid running dbt locally, so their first real execution of a change is in CI or production. Cheap dev runs are not a convenience, they are what makes the feedback loop short enough that people test.

The reasoning

`var('name')` requires the variable to be defined somewhere or compilation fails — which is the right choice for something the run genuinely cannot proceed without. `var('name', default)` substitutes the default when it is missing, which is right for a knob with a sensible off position.

Precedence is command line over project file: `vars:` in `dbt_project.yml` sets the baseline, and `dbt run --vars '{"min_amount": 40}'` overrides it for that invocation. The CLI value is YAML, so the quoting matters — the JSON-ish form with the space after the colon is the one that works everywhere.

The pattern that makes them useful is the optional filter: `{% if var('start_date', none) %} and updated_at >= '{{ var("start_date") }}' {% endif %}`. With no var passed, the block disappears entirely and the model behaves normally. With one, you get a bounded run — which is how you backfill a date range without editing a model, and how a reprocessing job passes its window in.

Two cautions. A default of `none` is falsy and a default of `0` is falsy too, so `{% if var('min_amount', 0) %}` skips the block when someone explicitly passes zero — test against `is not none` when zero is a legitimate value. And vars are global to the run, not scoped to a model, so a name like `start_date` used by two unrelated models is one var driving both.

The reasoning

`is_incremental` without parentheses does not call anything. Jinja resolves the name to the macro object, and any object is truthy, so the condition is unconditionally true. `is_incremental()` calls it and returns the boolean you wanted.

The symptom is the giveaway. The guard's whole job is to keep `{{ this }}` out of the first run, because on a fresh environment the target relation has not been created yet. When the guard is always true, the filter compiles in and the warehouse is asked to select from a table that does not exist. The error names the model itself, which is the confusing part — people read it as dbt failing to create the model rather than as the model referring to itself.

Reading the compiled SQL settles it in seconds, and this is the habit worth demonstrating: the branch is plainly there in `target/compiled/`, referencing `analytics.main.recent_orders` on a run that is supposed to be creating it. No amount of staring at the model file shows you that.

The same mistake has a quieter twin. `{% if is_incremental() %}` on a model that is *not* materialized incremental is always false, so the filter never applies and the model silently rebuilds in full every night. No error, no warning, just a bill. Both come from the same root: the guard's value depends on things outside the file, so verify it in the compiled output rather than assuming.

The reasoning

Jinja runs first, entirely, and produces a string. `{% set cutoff = 40 %}` creates a template-local name, and `{{ cutoff }}` writes its text into the output. The compiled SQL contains the number 40 with no trace that a variable was involved. A warehouse-level variable — a session variable, a declared local, a bind parameter — exists at execution time and can change per execution.

The consequence is that Jinja cannot react to data. You cannot `{% set max_id = select max(id) from ... %}` and branch on it, because at compile time no query has run. Anything data-dependent has to go through `run_query`, which executes a query during compilation and is a genuinely different mechanism with its own parse-time caveats.

The other direction is just as important: SQL cannot see Jinja names. A `{% set %}` inside an `{% if %}` block that was not taken simply does not exist, and referencing an undefined name gives you an empty string rather than an error, which is how a filter quietly becomes `where amount >= `.

Where `{% set %}` earns its place is naming things that repeat in the template: a list to loop over, a column list, a threshold used three times, a relation built once and reused. It makes the template readable without adding anything the warehouse has to think about.

The reasoning

Macros live in `macros/`, are defined with `{% macro name(args) %}...{% endmacro %}`, take positional or keyword arguments with defaults, and are called with `{{ name(...) }}`. Whatever text sits between the tags is the return value, and it is spliced into the compiled SQL at the call site.

That the argument is a column *name* rather than a column *value* is the thing to say explicitly. You pass the string `'amount'` and the macro writes it into an expression; it never sees a row. This is why a macro cannot do anything conditional on data, and why the same macro works on a billion-row table for free.

Two practical details. Give arguments defaults where there is a sensible one, so the common call stays short. And be careful with whitespace: the newlines inside the macro body are part of the return value, which is why the compiled output has the expression on its own line — `{%- macro ... -%}` and trimming inside the body is what keeps generated SQL tidy.

The version of this that comes up in follow-ups is where to put the macro. A macro used by one model is usually better inlined; a macro used by ten is worth the indirection. And a macro that wraps a single warehouse function adds a layer for no benefit — the value is in the cases where the expression is long, repeated, or genuinely differs by adapter.

The reasoning

The cost of a macro is that the SQL in the file is no longer the SQL that runs. That is a real price: reviewers cannot review it, newcomers cannot read it, and debugging requires compiling first. You pay that price willingly for something small and mechanical, and unwillingly for a hundred lines of business rules.

The rule I use has three parts. Macro it if it is repeated *and* mechanical — a timezone cast, a surrogate key, a money conversion, a cross-database date function. Macro it if it papers over an adapter difference, because that is exactly the kind of noise SQL should not be cluttered with. And macro it if it enforces a convention that must be identical everywhere, like how you hash a key.

Do not macro it if the logic is what a stakeholder would ask you to explain. Revenue recognition, deduplication rules, a join grain — those belong in a model, where the DAG documents them, tests can attach to them and lineage points at them. Four models sharing a fact-building macro almost always means there should be one model with four thin models on top of it, which is what dbt is for.

The tell that you have crossed the line: you cannot answer 'what does this model do?' without compiling it, or a macro takes more than about three arguments, or arguments have started to control branching. At that point the macro has become a program and the DAG has stopped describing your pipeline.

The reasoning

A generic test is a macro with a special wrapper, living in `tests/generic/` or `macros/`. It receives `model` — the relation under test — and, for a column test, `column_name`. Its body is a select that returns the offending rows. dbt wraps it in a count and compares against the threshold: `select count(*) from (<your query>)`, failing when the count exceeds zero.

That inversion is the thing to say out loud, because it feels backwards the first time. You do not write an assertion that should be true; you write a query for the rows that prove it false. Every built-in test works this way — `not_null` selects rows where the column is null, `unique` selects the values with a count above one.

Once defined, it is used in YAML like any built-in: under a column's `data_tests:` key by name, and with arguments if it takes any. dbt names the resulting test node from the test, model and column, which is what shows up in the run output and in `target/compiled/` — and reading that compiled test SQL is how you check the test does what you think.

Two refinements worth mentioning. Accept a `**kwargs`-style argument for configurability — a `min_value` rather than a hardcoded zero — so one test covers a family of checks. And set `config(severity='warn')` or a `where` filter in the YAML rather than in the test body, so the same test can be strict on one model and advisory on another.

The reasoning

The collision problem first. `concat(customer_id, order_date)` on `(1, '23')` and `(12, '3')` both give `123`. Any concatenation without a separator that cannot appear in the data will eventually produce two different rows with the same key, and it will do it silently in a dimension nobody is watching.

The null problem is worse because it is invisible. On most warehouses `concat` with a null argument yields null for the whole expression, so every row with a missing component gets a null key. Your `unique` test passes — nulls do not collide — and your `not_null` test on the key fails, or worse, you never added one and downstream joins silently drop those rows.

The macro handles both: each field is cast to a string, coalesced to a fixed placeholder like `'_dbt_utils_surrogate_key_null_'`, concatenated with a separator, and hashed with MD5. The output is fixed-width regardless of input size, which matters for storage and join performance, and the components are unrecoverable, which is occasionally a privacy benefit.

Two things to get right in practice. Component order must be stable — reorder the list and every key in the table changes, which is a full-refresh event for you and a broken join for everyone downstream. And the components must be the *business* key at the grain you claim, not whatever happens to be unique today; a key built from three columns where two would do is a key that changes whenever the third does.

The reasoning

The pattern is two layers. A public macro `{% macro my_thing(x) %}{{ return(adapter.dispatch('my_thing')(x)) }}{% endmacro %}`, and then implementations named `default__my_thing`, `bigquery__my_thing`, `snowflake__my_thing`. At runtime dbt looks for an implementation matching the current adapter and falls back to `default__` when there is none.

The reason this exists rather than `{% if target.type == 'bigquery' %}` is extensibility. Dispatch has a search-path mechanism — the `dispatch` config in `dbt_project.yml` lets a project override a package's implementation for its adapter without forking the package. That is how you fix a package's Snowflake behaviour without waiting for a release.

In practice you reach for it for the things that genuinely differ: date arithmetic, string aggregation, type casting, hashing, and anything involving information schema queries. Everything else should stay in ANSI-ish SQL, because each dispatched implementation is another thing to test on another warehouse.

The trap is silent fallback. If you write only `default__` and `snowflake__`, running on Redshift quietly gets the default, which may be valid SQL that means something slightly different. When the semantics genuinely differ per warehouse rather than just the syntax, the default should raise via `exceptions.raise_compiler_error` rather than guess.

The reasoning

Doc blocks live in markdown files anywhere under your model paths. Inside, `{% docs order_status %}` ... `{% enddocs %}` defines a named block whose body is markdown, so you can use lists, tables and links rather than cramming a paragraph into a YAML string.

You reference it with `{{ doc('order_status') }}` in any `description:` — on a model, a column, a source, an exposure. dbt renders it into `catalog.json` and the docs site, and because the reference is by name, changing the block updates every place that uses it.

The practical use beyond deduplication is the long description. A YAML `description` is fine for a sentence; a doc block is where you put the paragraph explaining that `order_status` has five values, that `pending` means payment authorised but not captured, and that the mapping changed in March 2025. That is the content people actually need and that never survives in a one-line string.

Two neighbouring things worth naming. `persist_docs` pushes descriptions into the warehouse as table and column comments, so they show up in the BI tool and the information schema rather than only in the dbt docs site — which is where most consumers will actually look. And descriptions are testable indirectly: `dbt_project_evaluator` and similar packages will flag undocumented models, which is how you keep the coverage from rotting.

The reasoning

dbt compiles every model twice. The first pass exists to build the graph: dbt needs to know what every model refs before it can decide what to run, so it renders the Jinja with query execution disabled. On that pass `run_query` returns None, `adapter.get_relation` returns None, and any attribute access on the result fails.

The second pass happens when the node is actually being built, with a live connection. `execute` is a Jinja variable that is false on the parse pass and true on the execution pass, so the fix is to do the query unconditionally but guard the *use* of its result: `{% if execute %}{% set values = results.columns[0].values() %}{% else %}{% set values = [] %}{% endif %}`.

The empty list in the else branch is not a formality — it is what makes the parse pass produce syntactically valid (if useless) SQL, which is all the parser needs. Skip the else and you get an undefined name on the parse pass, which fails in a different and equally confusing way.

The wider lesson is the one to lead with in an interview: dbt cannot know your DAG without rendering your templates, and it cannot render your templates against live data without knowing your DAG. `execute` is how that circularity is broken, and every parse-time oddity — why you cannot ref a dynamically-built name, why `{{ log() }}` prints twice — comes from the same place.

The reasoning

The macro runs the query, unwraps the agate table it gets back — `results.columns[0].values()` — and returns a list. Sort it, because the warehouse gives no ordering guarantee and an unsorted list means your column order changes between runs for no reason. The model then loops over the returned list exactly as it would over a literal one.

What you have signed up for is a model whose output schema is not in version control. A new status appears in the source and your table grows a column with no commit, no review and no warning. Every downstream consumer that did `select *` now gets an extra column, and every one that selected explicitly silently ignores it. If a value disappears, the column disappears, and anything referencing it breaks.

There is also a build-order subtlety: the macro queries a relation during compilation of your model, so that relation must already exist and be current. It works because dbt compiles a node just before running it, after its parents, but it means the compiled SQL for this model depends on the state of the warehouse — two people compiling the same commit can get different SQL.

So my default is a hardcoded list with a test. `accepted_values` on the source column fails loudly when a new status appears, which turns a silent schema change into a pull request. Dynamic columns are right when the value set is genuinely open-ended and large — a per-tenant or per-metric pivot — and wrong when it is a domain of five things that changes twice a year.

The reasoning

`adapter.get_columns_in_relation` returns Column objects for a relation, which you can map to names, reject the unwanted ones, and join into a select list. It runs during compilation, so what lands in `target/compiled/` is an explicit column list — the warehouse never sees anything dynamic.

The catch is the ordering dependency. The warehouse has to be able to answer the question, which means the upstream relation must exist when your model compiles. On a fresh environment, or in CI against an empty schema, or when someone runs your model with `--select my_model` and no upstream build, the call returns nothing and you compile `select from ...` — a syntax error at best, and a silently narrow table at worst.

The second catch is the same one dynamic pivots have: your model's schema is now decided by whatever the upstream table happens to have. Adding a column upstream silently adds it downstream, which is sometimes exactly what you want in a staging passthrough and rarely what you want in a mart with a contract.

So use it where the intent is genuinely "whatever is upstream, minus these" — dropping PII columns in a staging layer, or building a passthrough over a wide vendor table. Where the mart's columns are part of an agreement with a consumer, list them, and let a schema test or a model contract tell you when the upstream changed.

The reasoning

`var` resolves from `dbt_project.yml` or `--vars` — both of which are project material, and one of which is in git. `env_var` reads the process environment at render time, which is the only one of the two that is appropriate for a secret. It takes an optional default, and without one a missing variable is a hard error, which is usually what you want for a credential.

In practice: `profiles.yml` holds `password: "{{ env_var('DBT_PASSWORD') }}"` and the actual value comes from your shell, your CI secret store, or your orchestrator's connection management. dbt Cloud manages this through environment settings, which is one of the concrete operational things you buy with it.

It has a second, less obvious use: environment-driven configuration that is not secret. `DBT_ENV_CUSTOM_ENV_`-prefixed variables get captured into run artifacts, so you can stamp a run with the git SHA or the CI job id and later work out which deploy produced a table. Some teams also drive warehouse size or thread count from the environment rather than from the target definition.

The failure to avoid is a `profiles.yml` with a real password committed to the repository, which is depressingly common in first projects. The related one is a default that silently works: `env_var('DBT_TARGET', 'prod')` means a missing variable runs against production, and the safe default is always the harmless one — or no default at all, so it fails loudly.

The reasoning

The rule of one staging model per source table is what makes the layer worth having. It means there is exactly one place where `cust_id` becomes `customer_id`, one place where the vendor's `0`/`1` becomes a boolean, and one place to change when the source schema moves. Two staging models over the same table means two conventions and a future disagreement.

What belongs: selecting the columns you actually use, renaming to your naming convention, casting types, trimming and lowercasing where the source is inconsistent, converting timestamps to a standard timezone, and simple case expressions that map source codes to readable values. Nothing that requires knowing about another table.

What does not belong: joins, aggregations, filters that encode a business rule, deduplication that involves a judgment call, and anything a stakeholder would have an opinion about. Those go in intermediate models or marts, where the DAG shows them and tests can attach to them. The one filter I do allow is removing rows that are structurally invalid — hard-deleted records, test accounts — and only if it is documented.

Two conventions that go with it. Materialize staging as views by default, because it is thin work and always-current beats stored. And name them `stg_<source>__<table>` with a double underscore, so `stg_shopify__orders` and `stg_stripe__orders` do not collide and the source is visible in every ref that uses it.

The reasoning

`pre_hook` runs before the model's statement and `post_hook` after, both as separate statements in the same run. On adapters with transactional DDL they are inside the model's transaction, so a failed hook rolls the model back — which is the property that makes a post-hook a reasonable place for something that must be true whenever the table exists.

The legitimate uses are side effects that are not part of the query: granting select to a BI role, creating an index or applying a clustering directive, inserting an audit row, invalidating a cache, calling a stored procedure that refreshes something external. Note that grants specifically have a first-class `grants` config now, which is better than a hook because dbt diffs the current grants rather than reissuing them blindly.

Hooks are Jinja, so `{{ this }}`, `{{ target }}` and macros all work inside them — which is what makes them reusable. The common pattern is a macro that takes no arguments and reads `this`, called as `post_hook="{{ log_row_count() }}"`, so the model file stays one line.

Where hooks go wrong: putting transformation logic in them, so the DAG no longer describes what happens; putting a slow statement in a post-hook that runs on every model, which multiplies across the project; and relying on `on-run-end` for something critical, because it does not run if the run failed early. If it must happen, it belongs in the orchestrator, not in a hook.

The reasoning

The `grants` config — set once on `models: marts: +grants: {select: ['bi_role']}` — is the modern answer. dbt reads the existing grants, computes the difference and applies only that, so a run is idempotent and cheap. It also merges down the tree, so a project-level default plus a model-level addition gives you both.

A `post_hook` on every model works and is what everyone did before. Its problems are that it reissues the grant on every run whether or not anything changed, that it is easy to forget on a new model, and that it is spread across fifty files instead of declared once.

`on-run-end` in `dbt_project.yml` centralises it and can loop over `results` to grant on exactly what was built. Its problem is the one that matters at 3am: it runs at the end of the *run*, so if the run fails partway, it does not run at all — and you have a freshly built table nobody can read. Anything that must be true whenever a table exists belongs closer to the table than that.

The general principle worth stating: prefer declarative config over imperative hooks wherever dbt offers it, because config is diffable, inheritable and visible in the manifest, and a hook is a string dbt executes without understanding. The same argument applies to `persist_docs`, `contract` and `on_schema_change`.

The reasoning

A Python model is a `.py` file in your models directory with a `model(dbt, session)` function that returns a DataFrame. Inside it, `dbt.ref('stg_orders')` gives you the upstream model as a DataFrame and `dbt.config(materialized='table')` sets the config. dbt ships the code to the warehouse's Python runtime and materializes the returned frame — so it is still the warehouse doing the work, and it still lands as a relation in your DAG.

Support is adapter-specific and is the first thing to check: Snowflake via Snowpark, Databricks via PySpark, BigQuery via Dataproc. Postgres, Redshift and duckdb have no Python model support at all, so this is not a portable answer.

The honest recommendation is to use them narrowly. They are right for genuinely non-SQL work — statistical fitting, an ML inference call, a library that has no SQL equivalent, complex string parsing. They are wrong for anything expressible in SQL, because you lose readability for reviewers, you lose the compiled-SQL debugging path entirely, they are slower to start, and testing them is harder.

The alternative worth naming: keep dbt to SQL and put the model training in a separate tool, writing predictions back to a table that dbt declares as a source. That keeps a clean boundary and does not tie your transformation layer to one warehouse's Python runtime. Which is better depends mostly on whether the team maintaining it is analytics engineers or ML engineers.