Jinja and Macros: Advanced

Every dbt command begins by reading the project. dbt opens each file under models/, macros/, seeds/, snapshots/ and tests/ and renders the Jinja in every model; each ref() and source() the render touches gets written down as an edge. That pass is the parse, and its product is the manifest, which records every node with its config and the nodes it depends on. The warehouse stays unreachable throughout, because dbt cannot schedule a single statement until the graph exists.

Execution starts once the manifest is complete. dbt opens a connection and, for each selected node in graph order, renders the model's Jinja a second time, now with the connection live, and sends the result as SQL. The 2 renders see a different value in one variable, execute: False during the parse, True during execution. Any Jinja that needs an answer from the warehouse (a list of values, a column list, a row count) has to test that variable before it asks.

The split explains 2 things people hit early. dbt ls on a project of 2,000 models returns in seconds without opening a connection, because listing needs only the manifest. And dbt compile is slower than its name suggests: it connects, because compiling means rendering with execute set to True, and every warehouse query in the project's Jinja fires. dbt parse never connects. It catches syntax errors in Jinja or YAML, and graph errors such as a ref() to a model that does not exist. It cannot see what fails only with execute on, such as a missing table or a column list a query returns, so in CI it is a necessary check and not a sufficient one.

dbt run on this model prints both messages: the parsing line first, while the manifest is being assembled, then the rendering line when the node's turn comes. dbt parse prints only the first. Both branches leave the SQL untouched, which is the pattern to copy: a branch on execute changes what Jinja computes, never what the SQL means. The second render is the one that reaches target/compiled/, so the file there always shows the True branch, and a failure that happens only offline never appears in it.

dbt saves the parse in target/partial_parse.msgpack and reparses only files that changed, so the parsing line shows on a first parse or after an edit to the model. dbt parse --no-partial-parse forces every file through again.

flags.WHICH carries the command's name in both phases, so a macro can tell test from run and skip a grant during a test-only invocation. target.name works the same way and is the usual switch between dev and prod behaviour. Neither asks the warehouse anything, so neither needs execute. The macro below does need it, because it reads rows, and it shows where each line has to sit.

Line 2 runs in both phases and is what draws the edge from the calling model to dim_region. Moved inside the branch, it would run only under execution, after dbt had already fixed the build order, and the caller could build before dim_region had. Line 5 runs once, with a connection. The caller receives a list in both phases, so a for loop over the result renders cleanly offline and the parse completes. run_query itself is the subject of the next section.

run_query(sql) sends a query over the open connection and hands back the result as a table object. result.columns[0].values() gives the first column as a list and result.rows iterates rows; result.print_table() is for debugging, since it writes the whole thing to the log. During the parse it returns None, which is the reason every call lives behind execute. The query fires when the calling model compiles, and under dbt run a node compiles right before it runs, after every model it ref()s has been built. A model can therefore read a table that an upstream model finished 10 seconds earlier in the same invocation.

run_query wraps an older pair: a {% call statement('methods', fetch_result=True) %} block that holds the SQL, then load_result('methods') to fetch what it returned. Older projects and packages use the pair; both forms are the same round trip, and load_result returns nothing useful offline either.

A payments feed carries a method column that holds card and bank today, and later whatever the payments team launches next. The finance mart wants one amount column per method. Typing card_amount and bank_amount by hand means a code change each time a method appears. Instead, the macro below asks the warehouse which methods exist, and the model writes its column list from the answer. The animation runs that model twice, and a wallet payment lands between the runs.

Read the second compiled SQL against the first. Nothing in models/ changed, and the mart has a new column. The {% if execute %} in pay_methods() is what lets dbt parse finish: offline, the macro returns an empty list, the for loop emits nothing, and the model still parses as a valid SELECT. The {% set q %} block sits above the branch, so its source() renders in both phases and the edge to raw.payments is drawn; only run_query waits for the connection. Under dbt compile in a schema where raw.payments does not exist yet, that call fails with a missing-table error, since compile renders with execute on.

A column list that follows the data also breaks whatever depends on it. A downstream model that selects wallet_amount by name works only once a wallet payment exists, and breaks the day the method is retired and its rows deleted. A not_null test declared on card_amount in YAML fails with an unknown-column error if card payments stop. A model contract (Models: Advanced) freezes the column list and rejects the third column. So a data-driven shape belongs at the edge of the graph, where nothing downstream names its columns, or it should take its list from something you control.

adapter.get_columns_in_relation(relation) asks the warehouse for a relation's columns and returns objects with .name and .data_type. It is how a model selects every column of its upstream except the load metadata, without listing 40 names. The parse branch here falls back to *, which keeps the offline render a valid SELECT. dbt_utils.star() does the same with the guards included, and dbt_utils.get_column_values() is the packaged form of the pivot above.

Each of these queries is a round trip issued while the node compiles, one at a time. A project with 40 such models adds 40 queries to every dbt run and every dbt compile, and each result sits in memory, so run_query over a fact table is a mistake even when it works. Keep the questions to metadata-sized answers: distinct values of a low-cardinality column, a column list, a single max date.

Model hooks run on the model's connection and as part of the model's node: a pre_hook that errors fails the node, and its children skip exactly as they would behind a failed test (Testing: Beginner). The choice among the 5 is about timing. Use a model hook for work tied to one table and its build, a project hook for work tied to the invocation, and an operation, covered below, for work a person decides to run on demand.

The hook string is Jinja too, rendered when the hook runs, so {{ this }} resolves to the table the model produced. Several hooks go in a list and run in that order. A hook can also be a macro call that returns SQL, which is how one grant reaches every mart from dbt_project.yml, where a +post-hook under a folder applies to every model beneath it. If the model's own statements fail, its post hook never runs.

results holds one record per node dbt attempted: r.node.name, r.status (success, error or skipped for a model; pass, fail or warn for a test), r.execution_time in seconds, and r.message. invocation_id is the same value across every row of one run, which is what joins the audit rows back to a single invocation. The macro renders one insert with a row per node, and the hook sends that text as its statement. Offline, or when no node ran, it renders select 1 instead, because an insert with no rows is invalid SQL.

dbt run-operation create_audit_table calls the macro with execute set to True, after parsing the project and opening a connection. The invocation builds no node, so nothing is materialized and nothing enters the lineage graph. The macro has to send its own SQL with run_query or inside a statement block, because a string it returns goes nowhere. Arguments arrive through --args '{days: 7}' as a YAML mapping. It is the home for maintenance: creating a schema, dropping the tables a renamed model left behind (Models: Advanced), a one-time grant, or a repair statement someone would otherwise paste into a console and never record.

materialized='table' names a macro. When a node's turn comes, dbt hands that macro the compiled SELECT as sql, the node as model and the relation it should produce as this, and the macro decides every statement that follows. The table materialization creates a temporary table from the SELECT, renames the existing table out of the way, renames the new one into place, drops the old one and reports what now exists. view sends one create or replace view. A snapshot is a materialization with the same contract and a longer body, and a project can supply its own.

statement('main') is the name dbt watches. The rows affected and the timing it prints for the node come from the statement with that name, and fetch_result=True on a statement is what run_query uses underneath to read rows back. A materialization may send other statements under other names; only main is reported as the model.

A warehouse that stores tables as files on object storage partitions each table by a column named at creation. The built-in table macro never writes that property, and a post_hook arrives too late, because the table is already laid out. The wrapping around the SELECT has to change, which is the one job a hook cannot do. Here are the 20 lines of macros/partitioned_table.sql, grouped by what each does:

The model's compiled SELECT in target/compiled/ looks like any other model's; the create table ... with (...) wrapper appears only in target/run/, where dbt writes the statements it sent. config.require() fails the node when the materialization runs, with a compiler error naming the missing key. dbt parse and dbt compile never execute a materialization's body, so neither catches it. Notice what this version gives up: it drops the old table before creating the new one, so a reader querying between the 2 statements finds nothing. The built-in table avoids that with a temporary relation and a rename. Writing a materialization means paying again for every property the built-in already had.

default after the name makes the macro apply on any adapter. A second definition with adapter='name' wins on that adapter alone, which is how the built-ins specialize without the model knowing.

The built-in table uses 3 relations: make_intermediate_relation(target) for the new table, make_backup_relation(existing) for the old one, and the target itself for the name readers use. Create, rename, rename, drop: the gap in which this names nothing is a single rename. Copy that shape when a reader may be querying while you build, and the 20 lines above become 30.

Materializations are also the layer with the least help when they break. A wrong return value leaves dbt's relation cache out of step with the warehouse, and the next run's drop or rename fails against an object of the wrong type. A missing run_hooks(post_hooks) silently drops every post_hook in the project for models of that type. Treat a custom materialization as adapter code: a short file, a test model in the project that uses it, and a review by someone who has read the built-in it replaces.

A macro is a jump. The reader of a model sees {{ net_revenue('orders') }}, and before knowing which columns it touches they have to find the file under macros/ and render its branches in their head. SQL has no jumps: the expression sits where it is used. Every macro starts in debt, and it pays the debt off only when the SQL it replaces would have cost more to keep correct across the project.

3 of the 4 macros wrap one expression. money() saves 3 characters per call and costs a file lookup per reader; round(x / 100.0, 2) tells the reader the unit conversion and the precision without leaving the line. safe_pct() hides the nullif, which is the one thing a reader checking a division wants to see. day_bucket() is worse than either, because it compiles differently per target, so the SQL a reviewer reads in dev differs from the SQL production runs. customer_dims() is the only one worth keeping: 9 columns repeated across 12 marts that must stay identical.

The question is whether a change to this expression would have to land in several places at once, and whether those places are models in the same project. Repetition on its own does not answer it. round(x / 100.0, 2) appears in 30 models and will never change, because cents are cents, so copying it costs nothing. A refund-rate definition that finance amended twice last year, used in 6 marts, is the opposite case, and a macro with the rule in a comment is cheaper than 6 inconsistent fixes. Shapes driven by data (the pivot in Querying from Jinja) and generic tests are macros by nature, because no fixed SQL can express them.

A warehouse error points at a line in target/compiled/, and the reader maps it back to a macro call by hand. Searching the repository for refund_pct finds no model, because the column name was assembled inside a macro. The static parser from the first section gives up on any model with a macro call, so parse time grows with macro use. A change to a shared macro changes the compiled SQL of every model that calls it, so a one-line edit is a review of 40 models. Each cost is small, and a project of 400 models and 90 macros pays all 4 on every change.

When a macro does stay, its call site should read the way SQL reads. Name it for what it produces, refund_rate rather than calc, and use keyword arguments so {{ refund_rate(numerator='refunds_cents', denominator='subtotal_cents') }} says which is which. Put the business rule in a comment inside the macro, above the SQL, with the date it last changed. Keep it to one level: a macro that calls into dbt_utils is fine, and a macro that calls a macro that calls a macro is 3 files for one expression.