dbt Macros & Jinja Deep Dive: Custom Materializations, Hooks & Adapter Dispatch

dbt macros are the compile-time superpower that separates a senior analytics engineer's dbt project from a junior one — the difference between 500 lines of copy-pasted SELECTs and a 50-line macro that renders portable, testable, adapter-aware SQL across Snowflake, BigQuery, Postgres, and Databricks. Every serious dbt codebase eventually reaches for macros the moment the same window-function pattern shows up in the fourth model, the moment a hard-coded warehouse name breaks a promotion path, or the moment a governance requirement demands a GRANT on every table without a hand-written post-hook per model. The interview signal is not "have you used {{ ref() }}" — every dbt user has — it's whether you can walk an interviewer through the compile-vs-execute distinction, the adapter.dispatch routing model, and the {% materialization %} skeleton without stumbling on {% if execute %}.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "explain what {% if execute %} actually gates" or "how would you write a dbt custom materialization that supports soft-deletes?" or "walk me through adapter.dispatch and why it's not just a Python decorator with extra steps." It walks through the Jinja compile-time engine and its context vocabulary (target, var, env_var, this, execute, run_query, statement), the adapter.dispatch pattern that keeps packages portable across warehouses via default__macro / snowflake__macro / bigquery__macro implementations, the {% materialization %} skeleton with its pre/main/post lifecycle, the hook system (pre_hook, post_hook, on-run-start, on-run-end) and the idempotency contract, and the production patterns senior interviewers probe — dbt jinja loop discipline, dbt run_query introspection, dbt macro test unit tests. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the tuning axis with the optimization practice library →.

---

On this page

• Why Jinja is the senior-dbt-engineer skill
• Macros + context variables
• Adapter dispatch — multi-warehouse macros
• Custom materializations
• Pre-hooks, post-hooks, and run_query
• Cheat sheet — dbt macro recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Jinja is the senior-dbt-engineer skill

dbt jinja is not templating — it's the compile-time engine that lets one SQL file target N warehouses, N environments, and N business rules

The one-sentence invariant: dbt is a Jinja compiler wired to a warehouse driver — the .sql files you write are Jinja templates that render to concrete SQL at compile time, and everything senior analytics engineers do with macros, dispatch, and hooks is a consequence of that compile-time contract. Junior dbt users treat {{ ref('orders') }} as magic and never look under the hood. Senior users know that ref is a macro, that macros run during the parse-and-compile phase before any SQL touches the warehouse, that execute is False during parse and True during run, and that the entire dbt object graph (graph, model, this, target) is a Python-side context that Jinja renders SQL against.

The four axes interviewers actually probe.

• Context. The vocabulary Jinja renders against — target, var, env_var, this, execute, graph, model. Naming which context vars exist and what each returns is the first senior-signal check. target.type is how you write portable code; this is how you write incremental logic; execute is how you gate run_query calls that would fail during parse.
• Dispatch. adapter.dispatch('macro_name', 'namespace') is dbt's answer to portability across Snowflake, BigQuery, Postgres, and Databricks. One macro name at the caller, N implementations (default__x, snowflake__x, bigquery__x) chosen at runtime by target.type. Every serious dbt package (dbt_utils, dbt_expectations) is built on dispatch.
• Materialization. {% materialization %} blocks are the lifecycle-aware macros that define how dbt turns a compiled SELECT into a persistent object. Built-ins are table, view, incremental, ephemeral, snapshot; custom materializations are how teams add merge_delete, zero_copy_clone, iceberg_table, or any other warehouse-specific pattern that doesn't map to the five built-ins.
• Hooks. Pre-hook, post-hook, on-run-start, on-run-end — SQL that runs at defined points in the lifecycle. Grants, vacuums, freshness snapshots, Slack notifications. The senior signal is knowing which hook fires where, and enforcing idempotency because dbt may re-run any hook on retry.

What junior vs senior answers look like.

• Junior: "Macros are like functions in Jinja that let you reuse SQL." — technically true, misses the compile-time distinction.
• Senior: "Macros are Jinja functions that run during the parse-compile phase; the execute flag tells them whether they're in the parse pass (where run_query returns None) or the run pass (where it hits the warehouse). Adapter dispatch layers on top so one macro name maps to snowflake__x or bigquery__x based on target.type. Materializations are lifecycle-aware macros that define how the compiled SELECT becomes a persistent object; hooks are user-defined SQL wired to the pre/post/on-run points of that lifecycle." — required senior answer.

Compile-time vs run-time — the mental model.

• Parse phase. dbt reads every .sql file, invokes Jinja to render it. ref and source are resolved to fully-qualified relations; var values are substituted. execute is False. Anything that would query the warehouse (run_query, statement) is skipped or returns None.
• Compile phase. Same as parse, but the DAG is now built and dbt can honour --select filters. Compiled SQL is written to target/compiled/.
• Run phase. dbt walks the DAG, and for each node runs the materialization macro. execute is now True; run_query hits the warehouse; the materialization macro {% call statement %} sends SQL to the adapter.
• The gotcha. Any Jinja that calls run_query unguarded will run during parse and fail (or return None), then run again during execution. The {% if execute %} guard is the fix: only during the run pass does the introspection run.

2026 reality — dbt-fusion changes the engine, not the contract.

• The Rust engine. dbt Labs' 2026 dbt-fusion release moves the compiler from Python-Jinja to Rust-Jinja for 30-100× faster parse times.
• Macro contract preserved. Every macro you wrote against classic dbt renders identically on fusion. adapter.dispatch still works. {% materialization %} still works. Hooks still fire in the same order.
• What breaks. Python-specific Jinja tricks (calling dbt_utils.pretty_time() that itself imports datetime) may need a fusion-compatible replacement. Package maintainers ship fusion-safe versions; end-user code is largely unaffected.
• Interview implication. Senior candidates should mention fusion as the compile-engine story but not conflate it with the macro semantics — the macro contract is unchanged.

What interviewers listen for.

• Do you name the parse vs run distinction without prompting? — senior signal.
• Do you mention execute gating run_query unprompted? — senior signal.
• Do you frame dispatch as "one caller, N implementations chosen by target.type" rather than as "warehouse-if-else"? — required answer.
• Do you distinguish materializations from macros as "macros with a lifecycle contract"? — required answer.

Worked example — compile-time surprise

Detailed explanation. A team ships a macro latest_partition_date(table_ref) that calls run_query to fetch the max partition date from the target warehouse and injects it as a literal into the compiled SQL. On the second dbt compile, they hit Compilation Error: run_query returned None because they forgot the execute guard. Walk an interviewer through why the error surfaces, what run_query does during parse, and the two-line fix.

• The intent. Pre-compute a filter constant at compile time so the warehouse never scans partitions before the last known ingest.
• The bug. During parse, execute is False; run_query returns None. The macro tries to unwrap None.columns[0].values()[0] and crashes.
• The fix. Wrap the imperative introspection in {% if execute %} and provide a safe fallback for the parse pass.

Question. Rewrite the latest_partition_date macro so it (a) compiles cleanly during parse, (b) executes the introspection query only during the run phase, and (c) falls back to a safe placeholder during compile.

Input.

Component	Behaviour
execute during parse	False — run_query returns None
execute during run	True — run_query hits the warehouse
Target warehouse	Snowflake
Compiled SQL requirement	Must have a real date literal at run time

Code.

-- macros/latest_partition_date.sql
{% macro latest_partition_date(table_ref) %}
  {%- if execute %}
    {%- set query -%}
      SELECT MAX(event_date) AS d FROM {{ table_ref }}
    {%- endset -%}
    {%- set results = run_query(query) -%}
    {%- if results and results.columns[0].values() | length > 0 -%}
      {%- set latest = results.columns[0].values()[0] -%}
      {{ return("DATE '" ~ latest ~ "'") }}
    {%- else -%}
      {{ return("DATE '1970-01-01'") }}
    {%- endif -%}
  {%- else -%}
    {{ return("DATE '1970-01-01'") }}
  {%- endif %}
{% endmacro %}

-- models/events_recent.sql
SELECT *
FROM   {{ ref('events') }}
WHERE  event_date >= {{ latest_partition_date(ref('events_landed')) }}

Step-by-step explanation.

1. During parse, dbt loads every model and expands macros to check that the SQL is syntactically well-formed. execute is False, so the macro takes the else-branch and returns "DATE '1970-01-01'". The compiled parse-time SQL is well-formed and dbt continues.
2. During run, dbt walks the DAG and re-renders each model. execute is now True, so the macro takes the if-branch, sends SELECT MAX(event_date) AS d FROM analytics.events_landed to Snowflake, and receives back a real date (say 2026-06-22). The compiled SQL is now WHERE event_date >= DATE '2026-06-22'.
3. run_query returns an agate.Table. results.columns[0].values() is the list of values in the first column; [0] is the first row. If the table is empty (no rows in events_landed yet), the length check catches it and the macro returns the safe fallback.
4. The return() inside a macro is a special dbt affordance — regular Jinja macros don't return values; dbt's return() short-circuits and hands the value back to the caller as a string.
5. The {%- ... -%} (minus-signs) trim whitespace so the compiled SQL is clean; without them, extra blank lines litter the compiled .sql file.

Output.

Phase	execute	run_query returns	Compiled SQL fragment
Parse	False	(not called)	WHERE event_date >= DATE '1970-01-01'
Compile	False	(not called)	WHERE event_date >= DATE '1970-01-01'
Run	True	agate.Table([('2026-06-22',)])	WHERE event_date >= DATE '2026-06-22'

Rule of thumb. Every macro that touches run_query, statement, or the warehouse must be wrapped in {% if execute %} with a safe compile-time fallback. Without the guard, dbt compile fails or emits garbage SQL; with it, the macro is idempotent across every dbt phase.

Worked example — the target context variable

Detailed explanation. A team runs the same dbt project against three targets: dev (individual developer's Snowflake), ci (CI/CD Snowflake), prod (production Snowflake). Model materializations differ per target — dev uses view for cheap iteration, ci uses table for reproducible tests, prod uses incremental. The trick is to read target.name inside a macro and pick the materialization dynamically.

• The context. target is a dictionary exposing the active profiles.yml target — target.name, target.type, target.schema, target.database, etc.
• The pattern. A macro env_materialization(default) reads target.name and returns the right materialization string; config(materialized=env_materialization('table')) at the top of every model.
• The win. The same model file behaves correctly across dev, ci, and prod with no per-environment forks.

Question. Write the env_materialization(default) macro and show how a model uses it.

Input.

Target	target.name	Desired materialization
Developer laptop	dev	view
CI pipeline	ci	table
Production	prod	incremental

Code.

-- macros/env_materialization.sql
{% macro env_materialization(default='table') %}
  {%- if target.name == 'dev' -%}
    view
  {%- elif target.name == 'ci' -%}
    table
  {%- elif target.name == 'prod' -%}
    incremental
  {%- else -%}
    {{ default }}
  {%- endif -%}
{% endmacro %}

-- models/marts/fct_orders.sql
{{
  config(
    materialized = env_materialization('table'),
    unique_key   = 'order_id',
    on_schema_change = 'sync_all_columns'
  )
}}

SELECT
  order_id,
  customer_id,
  order_total,
  order_ts
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE order_ts > (SELECT COALESCE(MAX(order_ts), '1970-01-01') FROM {{ this }})
{% endif %}

Step-by-step explanation.

1. env_materialization inspects target.name. This is a plain Jinja if/elif chain; it runs during compile-time and returns a string.
2. In the model, config(materialized=env_materialization('table')) calls the macro; the return value (view, table, or incremental) becomes the model's materialization.
3. The {% if is_incremental() %} block is another compile-time gate. is_incremental() returns True only when (a) the materialization is incremental AND (b) the target table already exists AND (c) --full-refresh was not passed. On dev and ci targets, is_incremental() is always False — the WHERE clause vanishes from the compiled SQL.
4. this is another context variable — the fully-qualified relation for the current model. During incremental runs, {{ this }} renders to prod_db.prod_schema.fct_orders (or whatever the current target's schema/db is).
5. Across all three targets, the same .sql file compiles to three different SQL statements: CREATE VIEW ... AS SELECT ... on dev, CREATE TABLE ... AS SELECT ... on CI, and an incremental MERGE on prod.

Output.

Target	Rendered materialization	Rendered WHERE clause	Wall-clock time on 100M rows
dev	view	(none)	5 s (view creation)
ci	table	(none)	4 min (full CTAS)
prod	incremental	WHERE order_ts > (SELECT COALESCE(MAX(order_ts), '1970-01-01') FROM prod.fct_orders)	30 s (delta only)

Rule of thumb. Use target.name (not target.schema or target.database) to gate materialization choices — the schema/db can be overridden per model, but the target name is the profile-level environment identifier. Wrap the macro so every model reads the same policy; never hard-code materialized='table' in production models where dev speed matters.

Worked example — var and env_var for build-time knobs

Detailed explanation. A team wants a single dbt run command that accepts a lookback_days parameter — 7 days in CI (fast), 90 days in prod (full recovery window). The idiomatic dbt pattern is var('lookback_days', 30) with a default. For secrets (a Slack webhook URL for post-hook notifications), env_var('SLACK_WEBHOOK') reads from the process environment, keeping secrets out of the codebase.

• var. Reads from dbt_project.yml vars: section or from the CLI (dbt run --vars '{lookback_days: 90}').
• env_var. Reads from OS environment; supports a second argument as default.
• The pattern. var for build-time knobs the user might tune; env_var for secrets and CI-injected values.

Question. Refactor a model to accept lookback_days as a var, and add a post-hook that sends a Slack notification using an env_var webhook URL. Show the CLI invocations.

Input.

Parameter	Source	Default
lookback_days	var, from CLI or dbt_project.yml	30
SLACK_WEBHOOK	env_var, from OS environment	'' (empty; skip if unset)

Code.

# dbt_project.yml
vars:
  lookback_days: 30

-- models/fct_events_recent.sql
{{
  config(
    materialized = 'table',
    post_hook = [
      "{{ slack_notify('fct_events_recent rebuilt') }}"
    ]
  )
}}

SELECT *
FROM   {{ ref('events') }}
WHERE  event_ts >= CURRENT_DATE - INTERVAL '{{ var("lookback_days", 30) }} days'

-- macros/slack_notify.sql
{% macro slack_notify(message) %}
  {%- set webhook = env_var('SLACK_WEBHOOK', '') -%}
  {%- if webhook == '' -%}
    -- SLACK_WEBHOOK not set; skipping notification
    SELECT 1
  {%- else -%}
    -- Emit a warehouse-agnostic no-op with a comment so the post-hook succeeds;
    -- real notification fires via a Python step outside dbt.
    SELECT '{{ webhook }}' AS webhook_url, '{{ message }}' AS payload WHERE 1=0
  {%- endif -%}
{% endmacro %}

Step-by-step explanation.

1. var('lookback_days', 30) reads the value from dbt_project.yml (vars: lookback_days: 30) at compile time; if the CLI passes --vars '{lookback_days: 90}', the CLI wins.
2. env_var('SLACK_WEBHOOK', '') reads from the OS process environment. In CI, the pipeline injects the secret; on a developer laptop it's unset and the macro's fallback path fires (SELECT 1 — a no-op that lets dbt continue).
3. Post-hooks run as SQL after the model materializes. The slack_notify macro returns SQL — either a no-op SELECT 1 or a placeholder SELECT that references the webhook. The real webhook POST happens via a dbt Python run_started hook or via a companion Airflow task; the SQL post-hook just marks that the model completed.
4. The CLI invocations: dbt run --vars '{lookback_days: 7}' (CI, fast rebuild), dbt run --vars '{lookback_days: 90}' (prod, full window). The compiled SQL differs only in the INTERVAL '7 days' vs INTERVAL '90 days' literal.
5. Secrets never live in dbt_project.yml or in the compiled SQL. env_var at compile time reads the environment; the value never persists to a compiled artefact if the code is careful (return only the intent, not the secret value).

Output.

CLI	Rendered WHERE	Rendered post-hook
dbt run (dev, no vars)	WHERE event_ts >= CURRENT_DATE - INTERVAL '30 days'	SELECT 1 (SLACK_WEBHOOK unset)
dbt run --vars '{lookback_days: 7}' (CI)	WHERE event_ts >= CURRENT_DATE - INTERVAL '7 days'	SELECT 1 (SLACK_WEBHOOK unset in CI-lite)
dbt run --vars '{lookback_days: 90}' + SLACK_WEBHOOK=https://...	WHERE event_ts >= CURRENT_DATE - INTERVAL '90 days'	Placeholder SELECT with webhook

Rule of thumb. Use var for values a developer might tune from run to run; use env_var for secrets and CI-injected environment values. Never inline a secret into a compiled .sql artefact — the target/compiled/ directory ends up in Git logs, CI artifacts, and blob storage.

Senior interview question on the Jinja compile model

A senior interviewer often opens with: "Walk me through what happens when I run dbt run on a single model that uses {{ ref('upstream') }}, a call to a macro that runs run_query, and a {% if is_incremental() %} block. Name every phase and what the Jinja engine does at each."

Solution Using the parse → compile → run walkthrough

Phase 1 — Parse (execute = False)
  - dbt reads models/marts/fct_events_recent.sql
  - Jinja renders the file: {{ ref('events') }} resolves to prod.events;
    {{ latest_partition_date(...) }} macro runs; its {% if execute %} branch
    is False, so it returns the fallback DATE '1970-01-01'.
  - is_incremental() returns False (table doesn't exist yet during parse).
  - dbt builds the DAG: fct_events_recent depends on events.
  - Compiled SQL is written to target/compiled/... but not yet run.

Phase 2 — Compile (execute = False, DAG-aware)
  - Same as parse, but honours --select and --exclude flags.
  - Compiled SQL identical to parse phase for this node.

Phase 3 — Run (execute = True)
  - dbt walks the DAG in topological order.
  - For fct_events_recent, dbt invokes the materialization macro
    (table / view / incremental depending on config).
  - The materialization macro calls Jinja to re-render the model
    with execute = True.
  - latest_partition_date macro now takes the if-execute branch,
    calls run_query to Snowflake, receives '2026-06-22', returns
    DATE '2026-06-22'.
  - is_incremental() returns True (table exists, not --full-refresh).
  - Compiled SQL now has the real WHERE clause + the incremental
    merge template.
  - Materialization macro sends the compiled SQL to the warehouse
    via {% call statement('main') %}.
  - Adapter returns success; dbt records the run result.

Step-by-step trace.

Phase	execute	is_incremental()	Rendered SQL fragment	Warehouse action
Parse	False	False	WHERE event_ts >= DATE '1970-01-01'	none
Compile	False	False	WHERE event_ts >= DATE '1970-01-01'	none
Run (first time)	True	False (no existing table)	CREATE TABLE ... AS SELECT ... WHERE event_ts >= DATE '2026-06-22'	CTAS
Run (subsequent)	True	True	MERGE INTO fct_events_recent USING (SELECT ... WHERE event_ts > (SELECT MAX ...)) ...	MERGE

After the walkthrough, an interviewer can see the candidate distinguishes parse (dumb) from run (warehouse-aware), understands why execute gates warehouse-touching calls, and knows the materialization macro is the entry point for the run phase. This is the difference between "I've written a few macros" and "I run a dbt team."

Output:

Layer	What happens
Parse	Jinja renders; no warehouse calls; DAG built
Compile	Same but DAG-aware; artefacts to target/compiled/
Run	execute=True; materialization macro drives lifecycle; warehouse SQL sent
Post-run	Hooks fire; run results captured; dbt run returns exit code

Why this works — concept by concept:

• Compile-time engine — Jinja renders the file before any SQL touches the warehouse; senior candidates lead with this distinction, not with the surface syntax.
• execute flag — the single most important context variable; gates every warehouse-touching call so the parse phase doesn't fail on introspection queries.
• is_incremental() sentinel — returns True only in the intersection of (materialization is incremental) AND (relation exists) AND (not --full-refresh). Every incremental model relies on it to gate the delta WHERE clause.
• Materialization as entry point — the run phase entry point is the materialization macro, not the model file. Understanding this reframes hooks, {% call statement %}, and the pre/main/post lifecycle.
• Cost — parse is O(models); run is O(models × warehouse latency). The compile-time engine is cheap; the warehouse round-trips are the expensive part. Well-designed macros minimise run_query calls to keep compile time under a few seconds.

SQL
Topic — sql
SQL macro and templating problems for dbt engineers

Practice →

ETL Topic — etl ETL problems on incremental models and compile-time gates

Practice →

---

2. Macros + context variables

{% macro %} is the reusable building block — pair it with the context vocabulary (target, this, var, env_var, execute, run_query) and you've written half of dbt-utils

The mental model in one line: a macro is a Jinja function that returns a string of SQL; the context is the dictionary of values (target, this, var, execute, run_query, graph, model) it renders against; and every senior dbt pattern — dispatch, materializations, hooks — is a macro that consumes some subset of that context. Learn the context first; the syntax is trivial after that.

Macro syntax in full.

• Definition. {% macro macro_name(arg1, arg2='default') %} ... body ... {% endmacro %}. The body is whatever text Jinja renders; can include control flow, set variables, other macro calls.
• Invocation. {{ macro_name(arg1, arg2) }} (in a model or another macro) or {% do macro_name(...) %} when you want to call for side effects without inserting the return string.
• Return value. By default a macro's return value is the rendered body (as a string). Use {{ return(value) }} inside the macro to return a non-string Python value (list, dict, integer) — dbt handles this via a monkey-patched Jinja environment.
• Namespacing. Macros live in macros/ and are called by their file's basename or by {{ dbt_utils.some_macro() }} when installed via a package.

The context vocabulary — the eight variables you must know.

• target. Active profile target — target.name, target.type, target.schema, target.database, target.threads, target.user.
• var(name, default). Compile-time variable from dbt_project.yml or CLI. Default is optional but idiomatic; without it, a missing var errors at parse time.
• env_var(name, default). OS environment variable, evaluated at parse time. Use for secrets and CI-injected values.
• this. The current model's relation object — this.database, this.schema, this.identifier, this.include(database=false) for shorter renders.
• ref(name). Resolves a model name to a fully-qualified relation. The most-used macro in dbt; senior candidates know ref accepts a version arg (for dbt-core model versioning) and can cross-project.
• source(source_name, table_name). Same as ref but for external tables declared in sources.yml.
• execute. Boolean — True during the run phase, False during parse and compile.
• run_query(sql). Sends sql to the warehouse and returns an agate.Table. Returns None when execute is False.

Control flow — the four constructs you'll actually use.

• {% if / elif / else / endif %}. Standard Jinja conditionals. Frequently gates execute or checks target.type.
• {% for x in list %} ... {% endfor %}. Loops. The dbt jinja loop idiom for for column in columns renders identical logic across N columns.
• {% set x = value %}. Assigns a variable within the current scope. Use {% set x %}...{% endset %} for multi-line block-set (useful for building up a query string).
• {% do expr %}. Evaluates an expression for side effects (e.g. {% do list.append(x) %}) without emitting output.

The execute flag — the compile-vs-run gate in detail.

• What it is. A boolean context variable. True during the run phase; False during parse and compile.
• Why it matters. run_query, statement, and adapter method calls (adapter.get_relation(...)) all return None or fail during parse. Guard them.
• The idiom. {% if execute %} ... imperative introspection ... {% endif %}. Provide a compile-time fallback in the else branch.
• The anti-pattern. Calling run_query unguarded, then wondering why dbt compile fails with AttributeError: 'NoneType' object has no attribute 'columns'.

Common interview probes on macros + context.

• "What does execute do?" — parse-vs-run gate.
• "What's the difference between var and env_var?" — compile-time value vs OS env; var for knobs, env_var for secrets.
• "What is this and when do you use it?" — current model relation, used inside incremental WHERE clauses.
• "Why would you write {% do log('hello') %} vs {{ log('hello') }}?" — do for side effects only; {{ }} inserts the return value into the compiled SQL.

Worked example — pivot_columns macro over a dbt jinja loop

Detailed explanation. A common analytics task: given a list of category values, produce a wide table with one column per category. Writing this by hand for 20 categories is 20 lines of SUM(CASE WHEN ...). A macro that takes a column, a list of values, and an aggregate function generalises the pattern in 10 lines.

• The signature. pivot(column, values, agg='SUM', then_value=1, else_value=0).
• The loop. {% for v in values %} — emits one agg(CASE WHEN column = 'v' THEN then_value ELSE else_value END) AS "v" per value.
• The senior detail. Handle escaping, empty lists, and the trailing-comma problem via {{ ',' if not loop.last else '' }} or loop.last conditional.

Question. Write the pivot macro and use it to pivot orders by status.

Input.

Column	Sample values
order_status	pending, paid, shipped, cancelled

Code.

-- macros/pivot.sql
{% macro pivot(column, values, agg='SUM', then_value=1, else_value=0, quote_values=true) %}
  {%- for v in values -%}
    {%- set label = v ~ '' -%}
    {%- if quote_values -%}
      {%- set literal = "'" ~ v ~ "'" -%}
    {%- else -%}
      {%- set literal = v ~ '' -%}
    {%- endif -%}
    {{ agg }}(
      CASE WHEN {{ column }} = {{ literal }} THEN {{ then_value }} ELSE {{ else_value }} END
    ) AS "{{ label }}"
    {%- if not loop.last -%},{%- endif %}
  {%- endfor -%}
{% endmacro %}

-- models/orders_pivot.sql
{{
  config(materialized='table')
}}

SELECT
  customer_id,
  {{ pivot('order_status', ['pending', 'paid', 'shipped', 'cancelled']) }}
FROM {{ ref('orders') }}
GROUP BY 1

Step-by-step explanation.

1. {% for v in values %} iterates over the list ['pending', 'paid', 'shipped', 'cancelled']. Each iteration exposes loop.last (true on the final iteration), which we use to suppress the trailing comma.
2. {% set literal = "'" ~ v ~ "'" %} builds the SQL literal 'pending'. The ~ is Jinja's string concatenation operator (not +, which would fail for mixed types).
3. The rendered fragment for one iteration is SUM(CASE WHEN order_status = 'pending' THEN 1 ELSE 0 END) AS "pending". Repeated four times with different values, we get a valid four-column select list.
4. {%- ... -%} (minus-signs) trim whitespace so the compiled SQL is dense. Without them, Jinja emits blank lines between every case expression, which is ugly but not incorrect.
5. The quote_values parameter lets callers pivot over integers (values=[1,2,3]) without quoting. This is the "generalise for the second use case" senior habit — one parameter that unlocks a whole class of inputs.

Output.

Compiled SQL fragment
SELECT customer_id, SUM(CASE WHEN order_status = 'pending' THEN 1 ELSE 0 END) AS "pending", SUM(CASE WHEN order_status = 'paid' THEN 1 ELSE 0 END) AS "paid", SUM(CASE WHEN order_status = 'shipped' THEN 1 ELSE 0 END) AS "shipped", SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) AS "cancelled" FROM prod_db.prod_schema.orders GROUP BY 1

Rule of thumb. Any time you're about to write the same CASE WHEN structure for the fourth column, stop and write a macro. dbt_utils.pivot already exists and handles the edge cases; write your own only when the built-in doesn't fit the shape you need.

Worked example — generate_schema_name for environment isolation

Detailed explanation. By default, dbt writes tables to target.schema. In a multi-developer environment, that's fine for dev (each developer has their own schema), but in prod you often want to split by folder — marts models go to analytics, staging models go to staging, etc. dbt exposes a special generate_schema_name macro that you override at project level to customise the naming rule.

• The special macro. generate_schema_name(custom_schema_name, node) — called by dbt for every model to compute the schema.
• The default. If custom_schema_name is null, use target.schema; otherwise concatenate.
• The senior override. Use custom_schema_name directly on prod (so marts models land in analytics_marts), append to target.schema on dev (so dev.dbt_alice.analytics_marts avoids collision).

Question. Write the override that (a) uses custom_schema_name verbatim on prod and (b) prefixes it with dbt_$USER_ on dev.

Input.

Target	Model	custom_schema_name (from config)	Desired schema
prod	marts/fct_orders	marts	marts
prod	staging/stg_orders	staging	staging
dev (Alice)	marts/fct_orders	marts	dbt_alice_marts
dev (Alice)	staging/stg_orders	staging	dbt_alice_staging

Code.

-- macros/get_custom_schema.sql
-- Override dbt's built-in generate_schema_name
{% macro generate_schema_name(custom_schema_name, node) -%}
  {%- set default_schema = target.schema -%}
  {%- if target.name == 'prod' -%}
    {#- On prod, custom schema wins verbatim -#}
    {%- if custom_schema_name is none -%}
      {{ default_schema }}
    {%- else -%}
      {{ custom_schema_name | trim }}
    {%- endif -%}
  {%- else -%}
    {#- On non-prod, prefix with target.schema so devs isolate -#}
    {%- if custom_schema_name is none -%}
      {{ default_schema }}
    {%- else -%}
      {{ default_schema }}_{{ custom_schema_name | trim }}
    {%- endif -%}
  {%- endif -%}
{%- endmacro %}

# dbt_project.yml — assign schemas per folder
models:
  my_project:
    staging:
      +schema: staging
    marts:
      +schema: marts

Step-by-step explanation.

1. generate_schema_name is the special-name macro dbt calls once per node to determine the schema. Both arguments come from dbt: custom_schema_name is the value of +schema: in dbt_project.yml (or null if unset); node is the whole model context.
2. On target.name == 'prod', the macro returns custom_schema_name verbatim — so staging/stg_orders lands in staging, marts/fct_orders in marts. The prod schemas are stable, human-readable names.
3. On any other target (dev, ci), the macro prefixes with target.schema — which each developer sets to their own name in profiles.yml. Alice sets schema: dbt_alice; her marts models land in dbt_alice_marts; Bob's land in dbt_bob_marts. No collision.
4. The {#- ... -#} are Jinja comments — trimmed comments that don't emit whitespace. Use them liberally in macros; future-you will thank you.
5. custom_schema_name | trim handles YAML files that indent with trailing whitespace. The | is Jinja's filter operator; trim is the built-in string trim.

Output.

Target	Model	custom_schema_name	Rendered schema
prod	marts/fct_orders	marts	marts
prod	staging/stg_orders	staging	staging
dev (Alice)	marts/fct_orders	marts	dbt_alice_marts
dev (Alice)	staging/stg_orders	staging	dbt_alice_staging
ci	marts/fct_orders	marts	dbt_ci_marts (or whatever target.schema is set to in CI profile)

Rule of thumb. generate_schema_name is the single most-overridden macro in dbt projects. Ship this override in every new project on day one; retrofitting later means renaming tables across every downstream consumer.

Worked example — run_query for compile-time introspection

Detailed explanation. A team wants to UNION ALL events across N tables named events_2020, events_2021, ..., events_2026. The naive approach is to hard-code the list in a macro; the senior approach queries the warehouse's information_schema at compile time to discover which tables exist and emits the union dynamically. This is the dbt run_query pattern.

• The pattern. run_query sends SQL to the warehouse and returns an agate.Table. Extract column values, iterate in Jinja, emit SQL.
• The gotcha. Guard with {% if execute %} — during parse, run_query returns None.
• The win. New yearly tables get picked up automatically without a code change.

Question. Write a macro union_yearly_events(schema_name, table_prefix) that discovers tables at compile time and emits a UNION ALL.

Input.

Setup	Value
Schema	raw
Table prefix	events_
Discovered tables (at compile time)	events_2020, events_2021, events_2022, events_2023, events_2024, events_2025, events_2026

Code.

-- macros/union_yearly_events.sql
{% macro union_yearly_events(schema_name, table_prefix) %}
  {%- set tables = [] -%}
  {%- if execute -%}
    {%- set discovery %}
      SELECT table_name
      FROM   information_schema.tables
      WHERE  table_schema = '{{ schema_name }}'
        AND  table_name LIKE '{{ table_prefix }}%'
      ORDER  BY table_name
    {% endset -%}
    {%- set results = run_query(discovery) -%}
    {%- for row in results.rows -%}
      {%- do tables.append(row[0]) -%}
    {%- endfor -%}
  {%- endif -%}

  {%- if tables | length == 0 -%}
    -- No tables discovered; emit an empty relation with the expected schema.
    SELECT CAST(NULL AS TIMESTAMP) AS event_ts,
           CAST(NULL AS VARCHAR)   AS event_name,
           CAST(NULL AS VARCHAR)   AS user_id
    WHERE 1 = 0
  {%- else -%}
    {%- for t in tables -%}
      SELECT event_ts, event_name, user_id
      FROM   {{ schema_name }}.{{ t }}
      {%- if not loop.last %}
      UNION ALL
      {%- endif %}
    {%- endfor -%}
  {%- endif -%}
{% endmacro %}

-- models/events_all_years.sql
{{ config(materialized='view') }}
{{ union_yearly_events('raw', 'events_') }}

Step-by-step explanation.

1. The macro starts with set tables = [] — an empty list that will accumulate discovered table names.
2. The {% if execute %} guard is critical. During parse, run_query returns None; without the guard, the next line's results.rows would raise AttributeError. The guard lets parse complete with tables = [], and the fallback empty-relation branch fires.
3. During run (execute = True), run_query sends the discovery query to the warehouse. results.rows is an iterable of tuples; row[0] is the first column (table_name).
4. {% do tables.append(row[0]) %} uses do to mutate tables without emitting output. After the loop, tables = ['events_2020', 'events_2021', ..., 'events_2026'].
5. The output loop emits one SELECT ... FROM schema.table per discovered name, joined by UNION ALL. loop.last suppresses the trailing UNION ALL on the final iteration. The compiled SQL is a valid 7-way union.

Output.

Phase	tables	Rendered SQL
Parse	[] (empty)	Empty-relation fallback
Run	['events_2020', ..., 'events_2026']	SELECT ... FROM raw.events_2020 UNION ALL ... UNION ALL SELECT ... FROM raw.events_2026

Rule of thumb. run_query is the escape hatch for "give me a macro that adapts to the warehouse state." Guard with {% if execute %}, provide a compile-time fallback, and never leak warehouse errors from the introspection into the compile phase.

Senior interview question on macros + context

A senior interviewer might ask: "Write a macro generate_scheduled_query_hint(model_name) that reads the current model name via this, reads a warehouse_hint variable per model from vars, checks target.type to decide whether to emit a Snowflake RESOURCE_MONITOR hint or a BigQuery LABELS clause, and falls back to no hint on other adapters. Walk me through the whole macro."

Solution Using target-type dispatch inline with context variables

-- macros/generate_scheduled_query_hint.sql
{% macro generate_scheduled_query_hint() %}
  {#- Read per-model hints from vars; default to empty -#}
  {%- set hints = var('warehouse_hints', {}) -%}
  {%- set model_id = this.identifier -%}
  {%- set hint = hints.get(model_id, none) -%}

  {%- if hint is none -%}
    -- no warehouse hint configured for {{ model_id }}
  {%- elif target.type == 'snowflake' -%}
    {#- Snowflake: emit an ALTER WAREHOUSE resource-monitor comment -#}
    /*+ RESOURCE_MONITOR('{{ hint.resource_monitor | default("SCHEDULED_QUERIES") }}') */
    -- warehouse={{ hint.warehouse | default(target.warehouse) }}
  {%- elif target.type == 'bigquery' -%}
    {#- BigQuery: emit a LABELS clause in a SET statement -#}
    -- SET @@dataset_labels = ('{{ hint.label_key }}', '{{ hint.label_value }}');
  {%- elif target.type == 'databricks' -%}
    {#- Databricks: SET the runtime cluster tag -#}
    -- SET spark.databricks.clusterUsageTags.jobId = '{{ hint.job_id }}';
  {%- else -%}
    -- adapter {{ target.type }} has no warehouse-hint mapping
  {%- endif -%}
{% endmacro %}

-- models/marts/fct_daily_revenue.sql
{{ generate_scheduled_query_hint() }}
{{ config(materialized='table') }}

SELECT
  order_date,
  SUM(order_total) AS revenue
FROM   {{ ref('stg_orders') }}
GROUP  BY 1

# dbt_project.yml — per-model hints
vars:
  warehouse_hints:
    fct_daily_revenue:
      resource_monitor: PROD_SCHEDULED
      warehouse:        ANALYTICS_WH_L
      label_key:        cost_center
      label_value:      finance
      job_id:           daily-revenue-rollup

Step-by-step trace.

Step	Value	Action
Read hints	{'fct_daily_revenue': {...}}	via var('warehouse_hints', {})
Read model_id	'fct_daily_revenue'	via this.identifier
Lookup	hint = {'resource_monitor': 'PROD_SCHEDULED', ...}	via hints.get(model_id, none)
Dispatch	target.type == 'snowflake'	true on prod
Emit	/*+ RESOURCE_MONITOR('PROD_SCHEDULED') */	compile-time SQL comment

At run time on Snowflake, the compiled model file begins with the resource-monitor hint; the same file on a BigQuery target emits a labels comment; on Postgres (no hint mapping) it emits an inert comment. The model author writes one file; the macro handles the warehouse-specific rendering.

Output:

target.type	Rendered leading line
snowflake	/*+ RESOURCE_MONITOR('PROD_SCHEDULED') */\n-- warehouse=ANALYTICS_WH_L
bigquery	-- SET @@dataset_labels = ('cost_center', 'finance');
databricks	-- SET spark.databricks.clusterUsageTags.jobId = 'daily-revenue-rollup';
postgres	-- adapter postgres has no warehouse-hint mapping

Why this works — concept by concept:

• this for self-reference — this.identifier returns the current model's identifier. The macro doesn't need the caller to pass the name.
• var for config — the per-model hints live in dbt_project.yml as a dict; the macro consumes them without any per-model wiring.
• target.type for portability — the same macro renders correctly on Snowflake, BigQuery, Databricks, and falls through gracefully on unmapped adapters. This is the "poor man's dispatch" pattern; the next section shows the full adapter.dispatch version.
• Compile-time inertness — the emitted content is a SQL comment, not an active statement. Warehouses ignore comments; the hint is metadata for the query planner or a human reader.
• Cost — one macro invocation per model per run. var and this lookups are O(1); the entire macro is compile-time only, no warehouse round-trip.

SQL
Topic — sql
SQL macro composition problems

Practice →

Optimization Topic — optimization Optimization problems on Jinja loop and context patterns

Practice →

---

3. Adapter dispatch — multi-warehouse macros

adapter.dispatch is the one macro name → N implementations router that keeps packages portable across Snowflake, BigQuery, Postgres, and Databricks

The mental model in one line: adapter.dispatch('macro_name', 'namespace') looks up {target.type}__macro_name in the specified namespace, falls back to default__macro_name if the adapter-specific version doesn't exist, and returns the resolved macro to the caller — one caller writes {{ my_ns.my_macro() }}; the runtime picks the right implementation based on target.type. Every serious dbt package uses dispatch. Without it, packages ship one warehouse and force users to fork.

The dispatch signature.

• adapter.dispatch(macro_name, macro_namespace). The full form. Returns a callable that, when invoked, runs the resolved implementation.
• The lookup rule. Look for {target.type}__{macro_name} in macro_namespace. If missing, look for default__{macro_name}. If both missing, raise an error.
• The invocation pattern. {% set my_macro = adapter.dispatch('my_macro', 'my_package') %}\n{{ my_macro(arg1, arg2) }} — but almost always shortened via the wrapper pattern below.

The wrapper pattern — one macro that hides the dispatch.

• The idiom. Write a public wrapper macro my_macro(args) that calls adapter.dispatch(...)(args). The caller writes {{ my_macro(...) }}; the implementations are named default__my_macro, snowflake__my_macro, etc.
• Why. Callers shouldn't have to write the dispatch boilerplate every time. The wrapper is the public API; the xxx__my_macro variants are the private implementations.
• Namespace safety. Passing macro_namespace explicitly (usually the package name) prevents implementations from being accidentally overridden by a downstream project.

The default__x fallback.

• The purpose. Provide an implementation that works on the widest set of adapters (usually Postgres-flavoured ANSI SQL).
• When to override. Only for warehouses that need dialect-specific SQL — Snowflake's MERGE ... USING SYSTEM$MERGE_STATEMENT_METADATA, BigQuery's MERGE syntax quirks, Databricks' MERGE INTO semantics.
• The senior discipline. Write default__x first, make it work everywhere it can; add adapter-specific implementations only when there's a measurable difference in behaviour or performance.

dispatch and the search order.

• dispatch: in dbt_project.yml. Users can override the search order: dispatch: [{macro_namespace: 'dbt_utils', search_order: ['my_project', 'dbt_utils']}] tells dbt to look in the local project first, then the package. This is how a project shadows a package macro.
• Local overrides. A project can override dbt_utils.get_column_values for one warehouse by defining macros/dispatch/dbt_utils/snowflake__get_column_values.sql in the local project.
• Interview probe. "How would you patch a dbt_utils macro for one adapter without forking the package?" — answer: local override + dispatch: search order.

Common interview probes on adapter dispatch.

• "Walk me through what happens when I call {{ dbt_utils.pivot(...) }} on Snowflake." — dispatch lookup for snowflake__pivot; falls back to default__pivot.
• "Why not just use {% if target.type == 'snowflake' %} inside the macro?" — dispatch is composable, package-friendly, and lets users override without forking; inline if-else does not.
• "What is the search order and how do I override it?" — dispatch: in dbt_project.yml sets namespace search precedence.
• "Can I have default__ fail and only implement snowflake__ and bigquery__?" — yes, dispatch raises on unmatched adapter with no default; this is a valid design for warehouse-only macros.

Worked example — pivot_wide for Snowflake and Postgres

Detailed explanation. A team wants a pivot_wide macro that produces a wide-format table from a long-format input. Snowflake has a native PIVOT operator; Postgres does not (it has crosstab in the tablefunc extension but that requires typed placeholders). The macro dispatches to snowflake__pivot_wide (native PIVOT) and default__pivot_wide (SUM(CASE WHEN...)) fallback.

• The public wrapper. pivot_wide(source_ref, key_column, value_column, pivot_column, pivot_values).
• The Snowflake variant. Uses PIVOT (SUM(value) FOR pivot_column IN (...)).
• The default variant. Emits SUM(CASE WHEN...) — the pattern from the previous section.

Question. Write the three macros (wrapper + two implementations) and show the compiled SQL on both warehouses.

Input.

Parameter	Value
source_ref	ref('stg_orders')
key_column	customer_id
value_column	order_total
pivot_column	order_status
pivot_values	['pending', 'paid', 'shipped', 'cancelled']

Code.

-- macros/pivot_wide.sql — public wrapper (namespace = current project)
{% macro pivot_wide(source_ref, key_column, value_column, pivot_column, pivot_values) %}
  {%- set impl = adapter.dispatch('pivot_wide', 'my_project') -%}
  {{ impl(source_ref, key_column, value_column, pivot_column, pivot_values) }}
{% endmacro %}

-- macros/default__pivot_wide.sql — fallback (SUM CASE WHEN)
{% macro default__pivot_wide(source_ref, key_column, value_column, pivot_column, pivot_values) %}
  SELECT
    {{ key_column }},
    {%- for v in pivot_values %}
    SUM(CASE WHEN {{ pivot_column }} = '{{ v }}' THEN {{ value_column }} ELSE 0 END) AS "{{ v }}"
    {%- if not loop.last -%},{%- endif -%}
    {%- endfor %}
  FROM {{ source_ref }}
  GROUP BY 1
{% endmacro %}

-- macros/snowflake__pivot_wide.sql — native PIVOT
{% macro snowflake__pivot_wide(source_ref, key_column, value_column, pivot_column, pivot_values) %}
  SELECT *
  FROM {{ source_ref }}
  PIVOT (
    SUM({{ value_column }})
    FOR {{ pivot_column }} IN (
      {%- for v in pivot_values -%}
        '{{ v }}'{% if not loop.last %}, {% endif %}
      {%- endfor -%}
    )
  ) AS pivoted
{% endmacro %}

-- models/orders_wide.sql
{{ config(materialized='table') }}
{{
  pivot_wide(
    source_ref    = ref('stg_orders'),
    key_column    = 'customer_id',
    value_column  = 'order_total',
    pivot_column  = 'order_status',
    pivot_values  = ['pending', 'paid', 'shipped', 'cancelled']
  )
}}

Step-by-step explanation.

1. The public pivot_wide macro is a two-liner: look up the implementation via adapter.dispatch('pivot_wide', 'my_project') and invoke it. Callers only ever see this wrapper.
2. On Snowflake (target.type == 'snowflake'), dispatch resolves to snowflake__pivot_wide. That macro emits the native PIVOT syntax — one round-trip to the warehouse, uses the query planner's built-in pivot optimisation.
3. On any other adapter (Postgres, DuckDB, Redshift, Databricks), dispatch falls back to default__pivot_wide. That macro emits SUM(CASE WHEN...) — portable ANSI SQL, works everywhere.
4. The namespace argument ('my_project') prevents accidental collision. If two packages both defined pivot_wide, they'd namespace them differently.
5. The senior discipline: default__x is written first and tested on Postgres locally; snowflake__x is added when profiling shows the native PIVOT is materially faster or more readable on prod.

Output.

target.type	Rendered SQL
snowflake	SELECT * FROM stg_orders PIVOT (SUM(order_total) FOR order_status IN ('pending', 'paid', 'shipped', 'cancelled')) AS pivoted
postgres	SELECT customer_id, SUM(CASE WHEN order_status = 'pending' THEN order_total ELSE 0 END) AS "pending", ... FROM stg_orders GROUP BY 1
bigquery	(falls back to default) — same as postgres

Rule of thumb. Write default__x for the widest adapter set first; add {warehouse}__x implementations only when the dialect difference is meaningful. Never write per-adapter branches inside a single macro — dispatch is the composable pattern.

Worked example — overriding dbt_utils.get_column_values for BigQuery

Detailed explanation. dbt_utils.get_column_values returns a list of distinct values in a column. On BigQuery, the default implementation uses SELECT DISTINCT, which under BigQuery's execution model can be expensive on very wide columns. A team wants to override just the BigQuery path to use APPROX_QUANTILES for the top-N most common values — without forking dbt_utils.

• The dispatch: search order. dispatch: in dbt_project.yml tells dbt to look in my_project before dbt_utils for macros in the dbt_utils namespace.
• The local override. macros/dbt_utils/bigquery__get_column_values.sql in the local project.
• The win. No fork, no vendoring; upgrade dbt_utils normally; the BigQuery override rides on top.

Question. Configure dispatch: and write the BigQuery override.

Input.

Component	Value
Package to override	dbt_utils
Macro	get_column_values
Adapter to override	bigquery
Other adapters	fall through to dbt_utils.default__get_column_values

Code.

# dbt_project.yml — tell dbt to search my_project first for dbt_utils macros
dispatch:
  - macro_namespace: dbt_utils
    search_order:    ['my_project', 'dbt_utils']

-- macros/dbt_utils/bigquery__get_column_values.sql
-- Override dbt_utils.get_column_values for BigQuery only.
-- Uses APPROX_TOP_COUNT for a much cheaper distinct-values query on wide tables.
{% macro bigquery__get_column_values(table, column, max_records=none, default=none, order_by='count(*) desc') %}
  {%- if not execute -%}
    {{ return(default) }}
  {%- endif -%}

  {%- set query -%}
    SELECT approx_top_count.value AS value
    FROM   UNNEST((
             SELECT APPROX_TOP_COUNT({{ column }}, {{ max_records or 100 }})
             FROM   {{ table }}
             WHERE  {{ column }} IS NOT NULL
           )) AS approx_top_count
    ORDER  BY approx_top_count.count DESC
  {%- endset -%}

  {%- set results = run_query(query) -%}
  {%- set values = results.columns[0].values() -%}
  {{ return(values) }}
{% endmacro %}

Step-by-step explanation.

1. dispatch: in dbt_project.yml reorders the search: for any macro whose namespace is dbt_utils, look in my_project first, then dbt_utils. This is the override lever.
2. The override file lives at macros/dbt_utils/bigquery__get_column_values.sql. The file path is convention; dbt discovers macros anywhere under macros/ but organising by namespace helps humans.
3. The macro checks execute first — during parse, return the default value the caller passed in. This is the same compile-time guard pattern from Section 2.
4. During run, the macro runs APPROX_TOP_COUNT — BigQuery's built-in approximate top-N aggregate. On a 1B-row table with a wide column, APPROX_TOP_COUNT is 10-100× cheaper than SELECT DISTINCT.
5. Non-BigQuery targets (Snowflake, Postgres) never see this file. They resolve to dbt_utils.default__get_column_values via the normal dispatch search — same as before the override.

Output.

target.type	Which implementation runs	Warehouse cost on 1B rows
bigquery	my_project.bigquery__get_column_values (approx top-N)	1 slot-hour
snowflake	dbt_utils.default__get_column_values (SELECT DISTINCT)	(unchanged)
postgres	dbt_utils.default__get_column_values	(unchanged)

Rule of thumb. Never fork a dbt package to patch one adapter. dispatch: + a namespaced local override is the composable pattern; upgrades to the package remain painless.

Worked example — testing adapter.dispatch locally

Detailed explanation. A senior habit: write unit tests for macros before shipping them. The dbt-unit-testing package (or dbt 1.8+'s built-in unit_tests:) lets you assert that a macro renders the expected SQL for a given input, including the dispatch path. Show how to test that pivot_wide dispatches to snowflake__pivot_wide when the target is Snowflake.

• The test. Compile a model that calls the wrapper macro; assert the compiled SQL matches the Snowflake native PIVOT syntax.
• The pattern. dbt 1.8+ unit_tests: schema in a YAML file next to the model.
• The senior habit. Every non-trivial macro ships with at least one unit test.

Question. Write the unit test for pivot_wide and show the expected assertions.

Input.

Test scenario	Given	Expect
Snowflake target	mock stg_orders (customer_id, order_total, order_status)	PIVOT (SUM(order_total) FOR order_status IN (...)) syntax
Postgres target	same input	SUM(CASE WHEN ...) syntax

Code.

# models/orders_wide.yml — unit test
unit_tests:
  - name: test_pivot_wide_snowflake
    model: orders_wide
    given:
      - input: ref('stg_orders')
        format: dict
        rows:
          - {customer_id: 1, order_total: 100.00, order_status: 'paid'}
          - {customer_id: 1, order_total:  50.00, order_status: 'pending'}
          - {customer_id: 2, order_total: 200.00, order_status: 'paid'}
    expect:
      format: dict
      rows:
        - {customer_id: 1, pending:  50.00, paid: 100.00, shipped: null, cancelled: null}
        - {customer_id: 2, pending: null,   paid: 200.00, shipped: null, cancelled: null}

# Run just this unit test
dbt test --select "unit_test:test_pivot_wide_snowflake" --target snowflake_dev

# Compile-only check that the macro dispatches correctly
dbt compile --select orders_wide --target snowflake_dev
grep -q "PIVOT" target/compiled/my_project/models/orders_wide.sql
echo $?   # 0 == found; PIVOT was emitted (Snowflake path)

dbt compile --select orders_wide --target postgres_dev
grep -q "SUM(CASE WHEN" target/compiled/my_project/models/orders_wide.sql
echo $?   # 0 == found; SUM(CASE WHEN) was emitted (default path)

Step-by-step explanation.

1. The unit_tests: YAML block declares one test — test_pivot_wide_snowflake. given: injects mock rows for the upstream stg_orders; expect: asserts the model's output.
2. dbt test runs the model against the mock rows, then compares actual vs expected. Rows are compared cell-by-cell; nulls match nulls.
3. The dbt compile + grep combo is a lighter-weight test: compile the model and grep the compiled SQL for the expected syntax. PIVOT appears only in the Snowflake path; SUM(CASE WHEN appears only in the default path.
4. The compile check is fast (no warehouse round-trip) and can run in CI as a lint step. Full unit tests exercise the dispatch AND the SQL semantics but require a warehouse target.
5. The senior discipline: every macro ships with (a) a compile-check assertion in CI and (b) a unit test for at least the happy path. Broken dispatch is caught on push, not on the next production deploy.

Output.

Test	Result	Signal
Compile check — Snowflake	PIVOT present in target/compiled/	Snowflake dispatch works
Compile check — Postgres	SUM(CASE WHEN present in target/compiled/	Default fallback works
Full unit test — Snowflake	2/2 rows match	Semantic correctness verified

Rule of thumb. For any macro with more than one implementation, ship a compile-check in CI and a unit test. Broken dispatch is a silent bug; regressions caught at push are 10× cheaper than regressions caught at production run.

Senior interview question on adapter dispatch

A senior interviewer might ask: "Design a date_diff_business_days macro that returns the number of business days (Mon-Fri) between two dates. Ship it as a package. The macro must have a default implementation, Snowflake-native optimization, and a BigQuery variant. Walk me through the full package layout and dispatch design."

Solution Using a dispatched package with three implementations

Package layout — dbt_business_time/
├── dbt_project.yml
└── macros/
    ├── date_diff_business_days.sql          (public wrapper)
    ├── default__date_diff_business_days.sql (portable fallback)
    ├── snowflake__date_diff_business_days.sql
    └── bigquery__date_diff_business_days.sql

# dbt_business_time/dbt_project.yml
name: 'dbt_business_time'
version: '1.0.0'
config-version: 2

-- macros/date_diff_business_days.sql — public wrapper
{% macro date_diff_business_days(start_date, end_date) %}
  {{ return(adapter.dispatch('date_diff_business_days', 'dbt_business_time')(start_date, end_date)) }}
{% endmacro %}

-- macros/default__date_diff_business_days.sql — portable ANSI fallback
{% macro default__date_diff_business_days(start_date, end_date) %}
  (
    (CAST({{ end_date }} AS DATE) - CAST({{ start_date }} AS DATE))
    - (
        (CAST({{ end_date }} AS DATE) - CAST({{ start_date }} AS DATE)) / 7 * 2
      )
    - CASE WHEN EXTRACT(DOW FROM CAST({{ start_date }} AS DATE)) = 0 THEN 1 ELSE 0 END
    - CASE WHEN EXTRACT(DOW FROM CAST({{ end_date   }} AS DATE)) = 6 THEN 1 ELSE 0 END
  )
{% endmacro %}

-- macros/snowflake__date_diff_business_days.sql
{% macro snowflake__date_diff_business_days(start_date, end_date) %}
  DATEDIFF('day', {{ start_date }}, {{ end_date }})
    - 2 * FLOOR(DATEDIFF('day', {{ start_date }}, {{ end_date }}) / 7)
    - CASE
        WHEN DAYOFWEEK({{ start_date }}) = 0 THEN 1
        WHEN DAYOFWEEK({{ start_date }}) > DAYOFWEEK({{ end_date }}) THEN 2
        ELSE 0
      END
{% endmacro %}

-- macros/bigquery__date_diff_business_days.sql
{% macro bigquery__date_diff_business_days(start_date, end_date) %}
  DATE_DIFF({{ end_date }}, {{ start_date }}, DAY)
    - 2 * DIV(DATE_DIFF({{ end_date }}, {{ start_date }}, DAY), 7)
    - CASE
        WHEN EXTRACT(DAYOFWEEK FROM {{ start_date }}) = 1 THEN 1
        WHEN EXTRACT(DAYOFWEEK FROM {{ start_date }}) > EXTRACT(DAYOFWEEK FROM {{ end_date }}) THEN 2
        ELSE 0
      END
{% endmacro %}

# consumer's dbt_project.yml
packages:
  - package: myorg/dbt_business_time
    version: 1.0.0

Step-by-step trace.

Step	Value	Why
Consumer calls	{{ dbt_business_time.date_diff_business_days(a, b) }}	package-qualified invocation
Wrapper runs	adapter.dispatch('date_diff_business_days', 'dbt_business_time')	dispatch lookup
Lookup order	snowflake__ → default__ (if Snowflake)	search within dbt_business_time namespace
Emit	Snowflake DATEDIFF - 2*FLOOR - CASE	native DAYOFWEEK used
Fall-through (Postgres)	default__ implementation	ANSI-portable EXTRACT(DOW) form

The consumer writes one call. Snowflake gets the native path; BigQuery gets its own; anything else (Postgres, DuckDB, Redshift) falls back to the ANSI default. Adding a fourth warehouse is one new file — no wrapper change.

Output:

target.type	Rendered fragment
snowflake	DATEDIFF('day', a, b) - 2*FLOOR(DATEDIFF('day',a,b)/7) - CASE WHEN DAYOFWEEK(a)=0 THEN 1 WHEN DAYOFWEEK(a) > DAYOFWEEK(b) THEN 2 ELSE 0 END
bigquery	DATE_DIFF(b, a, DAY) - 2*DIV(DATE_DIFF(b,a,DAY),7) - CASE WHEN EXTRACT(DAYOFWEEK FROM a)=1 THEN 1 ... END
postgres	(CAST(b AS DATE) - CAST(a AS DATE)) - ((CAST(b AS DATE) - CAST(a AS DATE))/7*2) - CASE WHEN EXTRACT(DOW FROM CAST(a AS DATE))=0 THEN 1 ELSE 0 END - CASE WHEN EXTRACT(DOW FROM CAST(b AS DATE))=6 THEN 1 ELSE 0 END

Why this works — concept by concept:

• Package as namespace — dbt_business_time is the macro_namespace. Every implementation is looked up within this namespace, so downstream consumers can shadow it via dispatch: in their own project.
• Wrapper macro — the public API is one line; the caller never sees the dispatch boilerplate. This is the same pattern dbt_utils uses.
• `default` written first__ — ANSI-portable SQL is the baseline. Adapter-specific files exist only when the dialect gap is worth the added surface area.
• Adding a fourth warehouse — Databricks support is one new file: macros/databricks__date_diff_business_days.sql. No change to the wrapper, no change to consumers.
• Cost — dispatch itself is O(1) — one dictionary lookup per macro call at compile time. The cost lives in the emitted SQL; every warehouse's implementation is the same asymptotic O(1) per row (constant math on two dates).

SQL
Topic — sql
SQL adapter and dialect-portability problems

Practice →

ETL Topic — etl ETL problems on portable warehouse patterns

Practice →

---

4. Custom materializations

{% materialization %} is the lifecycle-aware macro that defines how compiled SQL becomes a persistent object — write one when the built-ins (table, view, incremental, ephemeral, snapshot) can't fit

The mental model in one line: a dbt custom materialization is a {% materialization name, adapter %} block that owns the full lifecycle for a model — pre-hooks fire, {% call statement('main') %} sends the compiled SELECT to the warehouse, post-hooks fire, and the materialization returns the list of relations dbt should register in the run graph. Built-ins cover 95% of use cases; you write your own for soft-delete, iceberg-table, zero-copy-clone, or any other pattern that needs custom pre/main/post SQL.

The five built-ins in one sentence each.

• table. Drop and recreate as CREATE TABLE AS SELECT (CTAS). Simple, expensive on very large models.
• view. Drop and recreate as CREATE VIEW. Cheap; no persistence; query cost on every SELECT.
• incremental. Insert or MERGE new rows; requires unique_key and is_incremental() guard. The workhorse for large fact tables.
• ephemeral. No warehouse object at all; the compiled SQL is inlined as a CTE into any downstream model that refs it. Zero cost, no visibility.
• snapshot. SCD Type 2 — captures history of a source table by adding dbt_valid_from / dbt_valid_to columns. Runs via dbt snapshot, not dbt run.

The materialization skeleton.

• {% materialization name, adapter %}. Declares the materialization. adapter can be default or a specific type (snowflake, bigquery). Multiple {% materialization name, X %} blocks for the same name give you per-adapter implementations, similar to dispatch.
• config.get(...). Read model-level config (unique_key, partition_by, cluster_by, etc.).
• pre_hooks / post_hooks. Automatically extracted from the model's config and run at the right time by the runtime.
• {% call statement('main') %} ... {% endcall %}. The main SQL statement — the compiled SELECT wrapped in whatever DDL/DML the materialization needs (CREATE TABLE AS, MERGE, INSERT).
• {{ return({'relations': [relation]}) }}. Return the list of relations dbt should register. Almost always a single-element list.

The lifecycle — pre / main / post in detail.

• Pre. run_hooks(pre_hooks) fires user-defined pre-hooks. Materialization may add its own pre-work (e.g. CREATE TEMP TABLE for merge staging).
• Main. The compiled SELECT executes. For table, wrapped in CTAS; for incremental, wrapped in MERGE or INSERT; for view, wrapped in CREATE OR REPLACE VIEW.
• Post. run_hooks(post_hooks) fires. Materialization may do its own cleanup (swap tables, drop temp, run ANALYZE).
• Return. The materialization returns a dict; dbt uses it to update the run graph and the run results.

When to write a custom materialization.

• Soft-deletes. merge_delete — insert new rows, mark deleted rows as is_deleted = true, never physically delete.
• Zero-copy clones. Snowflake zero-copy — CREATE TABLE ... CLONE source — for fast dev environments.
• Table format targeting. iceberg_table, delta_table, hudi_table — writing to open table formats via the warehouse's SQL DDL.
• Compliance patterns. retention_partitioned_table — CTAS + attach a retention policy in one atomic operation.
• When NOT to write one. If a post_hook on incremental will do — use that. Custom materializations are power tools; the operational cost (testing, dispatching per adapter, upgrading with dbt-core) is real.

Common interview probes on custom materializations.

• "Walk me through the materialization lifecycle." — pre-hooks → main SQL → post-hooks → return relations.
• "When would you write a custom materialization instead of a post-hook?" — when the main SQL itself needs to change, not just wrap around it.
• "What's {% call statement('main') %}?" — the block that sends the compiled SQL to the adapter and captures the run result.
• "How does a materialization pick up the pre/post hooks from the model config?" — config.get('pre_hook') and config.get('post_hook') are auto-populated; the runtime helper run_hooks(...) fires them.

Worked example — a minimal insert_overwrite materialization

Detailed explanation. A team wants a materialization that behaves like Spark's INSERT OVERWRITE PARTITION — delete the target partition(s), insert the new rows, atomic per partition. Snowflake and BigQuery don't have this primitive; the team writes a custom materialization that does DELETE + INSERT wrapped in a transaction.

• The idea. Read a partition_by config; DELETE rows matching the current run's partition values; INSERT the new rows.
• The atomicity. Wrap DELETE + INSERT in a transaction (Snowflake) or a scripting block (BigQuery via BEGIN TRANSACTION).
• The trick. Compute the partition value list from the incoming SELECT via run_query before the delete.

Question. Write a minimal insert_overwrite_partition materialization for Snowflake.

Input.

Config	Value
partition_by	event_date
Target table	analytics.daily_events
New rows	2 days' worth (say 2026-06-21 and 2026-06-22)

Code.

-- macros/insert_overwrite_partition.sql
{% materialization insert_overwrite_partition, adapter='snowflake' %}

  {%- set target_relation = this -%}
  {%- set partition_column = config.require('partition_by') -%}
  {%- set full_refresh = flags.FULL_REFRESH -%}

  -- Fire user pre-hooks
  {{ run_hooks(pre_hooks) }}

  -- Ensure the target exists (first run or --full-refresh)
  {%- set target_exists = load_relation(target_relation) is not none -%}

  {%- if not target_exists or full_refresh -%}
    -- Behave like a normal table materialization on first run / full refresh
    {%- call statement('main') -%}
      CREATE OR REPLACE TABLE {{ target_relation }} AS ({{ sql }})
    {%- endcall -%}
  {%- else -%}
    -- Insert-overwrite path
    {%- set tmp_relation = make_temp_relation(target_relation) -%}

    -- Stage the incoming rows into a temp
    {%- call statement('stage') -%}
      CREATE OR REPLACE TEMPORARY TABLE {{ tmp_relation }} AS ({{ sql }})
    {%- endcall -%}

    -- Discover the affected partitions from the temp
    {%- set part_query -%}
      SELECT DISTINCT {{ partition_column }} FROM {{ tmp_relation }}
    {%- endset -%}
    {%- set part_results = run_query(part_query) -%}
    {%- set partitions = [] -%}
    {%- for row in part_results.rows -%}
      {%- do partitions.append(row[0]) -%}
    {%- endfor -%}

    -- Atomic delete + insert per partition
    {%- call statement('main') -%}
      BEGIN;
      DELETE FROM {{ target_relation }}
      WHERE {{ partition_column }} IN (
        {%- for p in partitions %}'{{ p }}'{% if not loop.last %},{% endif %}{% endfor -%}
      );
      INSERT INTO {{ target_relation }}
      SELECT * FROM {{ tmp_relation }};
      COMMIT;
    {%- endcall -%}
  {%- endif -%}

  -- Fire user post-hooks
  {{ run_hooks(post_hooks) }}

  -- Register the relation with dbt's run graph
  {{ return({'relations': [target_relation]}) }}

{% endmaterialization %}

-- models/daily_events.sql
{{
  config(
    materialized  = 'insert_overwrite_partition',
    partition_by  = 'event_date'
  )
}}
SELECT event_date, user_id, event_name, payload
FROM   {{ ref('stg_events') }}
WHERE  event_date >= CURRENT_DATE - INTERVAL '2 days'

Step-by-step explanation.

1. {% materialization insert_overwrite_partition, adapter='snowflake' %} declares the materialization. It's Snowflake-only; a BigQuery user would need a second block with the BigQuery-native INSERT OVERWRITE PARTITION syntax.
2. config.require('partition_by') reads the required config from the model. If the model author forgets partition_by='event_date', dbt errors at compile time with a clear message.
3. flags.FULL_REFRESH is the dbt-injected flag from --full-refresh. On full refresh, we fall back to CREATE OR REPLACE TABLE — same as the built-in table materialization.
4. In the normal path, we stage the incoming rows into a Snowflake temp table, discover the distinct partition values via run_query, then run DELETE + INSERT inside a single BEGIN/COMMIT block. The transaction guarantees atomicity — either both statements succeed or both roll back.
5. run_hooks(pre_hooks) and run_hooks(post_hooks) fire the user-defined hooks. return({'relations': [target_relation]}) tells dbt one relation was materialized; dbt uses this to build the DAG state after the run.

Output.

Phase	SQL sent to Snowflake
First run	CREATE OR REPLACE TABLE analytics.daily_events AS (SELECT ...)
Second run (incremental)	CREATE OR REPLACE TEMPORARY TABLE dbt_tmp AS (SELECT ...)
Partition discovery	SELECT DISTINCT event_date FROM dbt_tmp → returns ['2026-06-21', '2026-06-22']
Atomic overwrite	BEGIN; DELETE FROM analytics.daily_events WHERE event_date IN ('2026-06-21', '2026-06-22'); INSERT INTO analytics.daily_events SELECT * FROM dbt_tmp; COMMIT;

Rule of thumb. Custom materializations are the right tool when the main-SQL structure itself needs to change — DELETE + INSERT + COMMIT, or a two-phase MERGE + rename. If the change is just "add SQL after the main statement," use a post_hook.

Worked example — merge_delete for soft-delete workloads

Detailed explanation. A team wants to build a fact table that never physically deletes rows — instead, deleted source rows are marked with is_deleted = TRUE and a deleted_at timestamp. The source system emits full snapshots; the materialization diffs incoming vs existing and marks the difference. This is beyond what the built-in incremental materialization does.

• The requirement. Insert new rows; update is_deleted = TRUE, deleted_at = current_run_ts for rows in the target that aren't in the incoming snapshot.
• The constraint. All in one MERGE statement for atomicity; support Snowflake's MERGE ... WHEN MATCHED syntax.
• The audit trail. The deleted_at column becomes the primary evidence for compliance audits.

Question. Write the merge_delete materialization for Snowflake and use it in a fact model.

Input.

Config	Value
unique_key	event_id
soft_delete_timestamp_col	deleted_at
Target table	compliance.events_history

Code.

-- macros/merge_delete.sql
{% materialization merge_delete, adapter='snowflake' %}

  {%- set target_relation = this -%}
  {%- set unique_key = config.require('unique_key') -%}
  {%- set deleted_col = config.get('soft_delete_timestamp_col', 'deleted_at') -%}
  {%- set full_refresh = flags.FULL_REFRESH -%}

  {{ run_hooks(pre_hooks) }}

  {%- set target_exists = load_relation(target_relation) is not none -%}

  {%- if not target_exists or full_refresh -%}
    {%- call statement('main') -%}
      CREATE OR REPLACE TABLE {{ target_relation }} AS (
        SELECT *, CAST(NULL AS TIMESTAMP) AS {{ deleted_col }} FROM ({{ sql }}) src
      )
    {%- endcall -%}
  {%- else -%}
    {%- set tmp_relation = make_temp_relation(target_relation) -%}
    {%- call statement('stage') -%}
      CREATE OR REPLACE TEMPORARY TABLE {{ tmp_relation }} AS ({{ sql }})
    {%- endcall -%}

    {%- call statement('main') -%}
      MERGE INTO {{ target_relation }} AS T
      USING (
        -- All incoming rows
        SELECT *, FALSE AS __src_present FROM {{ tmp_relation }}
        UNION ALL
        -- Existing rows that are NOT in the incoming snapshot (candidates for soft-delete)
        SELECT T2.*, TRUE AS __src_present
        FROM   {{ target_relation }} T2
        LEFT JOIN {{ tmp_relation }} S
          ON T2.{{ unique_key }} = S.{{ unique_key }}
        WHERE  S.{{ unique_key }} IS NULL
          AND  T2.{{ deleted_col }} IS NULL
      ) AS S
      ON T.{{ unique_key }} = S.{{ unique_key }}
      WHEN MATCHED AND S.__src_present = TRUE THEN UPDATE
        SET {{ deleted_col }} = CURRENT_TIMESTAMP
      WHEN NOT MATCHED THEN INSERT (*)
        VALUES (S.*)
    {%- endcall -%}
  {%- endif -%}

  {{ run_hooks(post_hooks) }}
  {{ return({'relations': [target_relation]}) }}

{% endmaterialization %}

-- models/marts/events_history.sql
{{
  config(
    materialized = 'merge_delete',
    unique_key   = 'event_id',
    soft_delete_timestamp_col = 'deleted_at'
  )
}}
SELECT event_id, user_id, event_name, event_ts
FROM   {{ ref('stg_events_snapshot') }}

Step-by-step explanation.

1. The materialization reads two configs: unique_key (required — errors at compile if missing) and soft_delete_timestamp_col (defaults to deleted_at if not set).
2. First-run path: CREATE OR REPLACE TABLE ... AS SELECT *, NULL::TIMESTAMP AS deleted_at FROM (...). The target has all columns from the SELECT plus the tombstone column.
3. Incremental path: stage incoming rows into a temp; MERGE against the target with a two-arm USING clause. Arm 1 (all incoming rows) drives INSERTs and UPDATEs on the tombstone timestamp for actively-present rows. Arm 2 (existing rows not present in the snapshot) drives the soft-delete UPDATEs.
4. The MERGE's WHEN MATCHED AND S.__src_present = TRUE THEN UPDATE SET deleted_col = CURRENT_TIMESTAMP sets the tombstone only when the row appears in the "absent-from-source" arm. The trick is the __src_present boolean marking which arm each USING-row came from.
5. WHEN NOT MATCHED THEN INSERT (*) handles the new rows in the incoming snapshot that don't exist yet in the target. Together, the two arms give us "insert-new + soft-delete-missing" in a single atomic MERGE.

Output.

Scenario	Effect on target table
New row in source	INSERT with deleted_at = NULL
Existing row in source	no change
Row previously in target, missing from source	UPDATE deleted_at = CURRENT_TIMESTAMP
Row previously soft-deleted, back in source	no change (only rows with deleted_at IS NULL are candidates for soft-delete)

Rule of thumb. Soft-delete materializations are the compliance-friendly answer to "never lose an event." Ship one custom materialization; every downstream model reads the same tombstone convention.

Worked example — dispatching a materialization across adapters

Detailed explanation. The merge_delete above is Snowflake-only. For a BigQuery variant, you don't dispatch inside the materialization body — you write a second {% materialization merge_delete, adapter='bigquery' %} block. dbt's materialization lookup follows the same adapter-specific → default pattern as adapter.dispatch.

• The two blocks. {% materialization merge_delete, adapter='snowflake' %} and {% materialization merge_delete, adapter='bigquery' %} in separate files.
• The runtime. dbt picks the one that matches target.type. If no adapter-specific version exists, dbt falls back to {% materialization merge_delete, adapter='default' %}.
• The senior discipline. Write default first (portable SQL), add adapter-specific only when needed. Ship the whole thing as a package.

Question. Sketch the BigQuery variant and show how dbt picks between them.

Input.

Warehouse	Adapter	Merge syntax
Snowflake	snowflake	MERGE INTO ... USING (SELECT ...) ON ... WHEN MATCHED ...
BigQuery	bigquery	same syntax; different function names for CURRENT_TIMESTAMP
Postgres	postgres	no native MERGE (pre-15); use ANSI UPSERT via ON CONFLICT

Code.

-- macros/merge_delete_bigquery.sql
{% materialization merge_delete, adapter='bigquery' %}
  {%- set target_relation = this -%}
  {%- set unique_key = config.require('unique_key') -%}
  {%- set deleted_col = config.get('soft_delete_timestamp_col', 'deleted_at') -%}
  {%- set full_refresh = flags.FULL_REFRESH -%}

  {{ run_hooks(pre_hooks) }}
  {%- set target_exists = load_relation(target_relation) is not none -%}

  {%- if not target_exists or full_refresh -%}
    {%- call statement('main') -%}
      CREATE OR REPLACE TABLE {{ target_relation }} AS
      SELECT *, CAST(NULL AS TIMESTAMP) AS {{ deleted_col }} FROM ({{ sql }})
    {%- endcall -%}
  {%- else -%}
    {%- set tmp_relation = make_temp_relation(target_relation) -%}
    {%- call statement('stage') -%}
      CREATE OR REPLACE TABLE {{ tmp_relation }} AS ({{ sql }})
    {%- endcall -%}

    {%- call statement('main') -%}
      MERGE INTO {{ target_relation }} T
      USING (
        SELECT *, FALSE AS __src_absent FROM {{ tmp_relation }}
        UNION ALL
        SELECT T2.*, TRUE AS __src_absent
        FROM   {{ target_relation }} T2
        LEFT JOIN {{ tmp_relation }} S USING ({{ unique_key }})
        WHERE  S.{{ unique_key }} IS NULL AND T2.{{ deleted_col }} IS NULL
      ) S ON T.{{ unique_key }} = S.{{ unique_key }}
      WHEN MATCHED AND S.__src_absent THEN UPDATE
        SET {{ deleted_col }} = CURRENT_TIMESTAMP()
      WHEN NOT MATCHED THEN INSERT ROW
    {%- endcall -%}
  {%- endif -%}

  {{ run_hooks(post_hooks) }}
  {{ return({'relations': [target_relation]}) }}
{% endmaterialization %}

Step-by-step explanation.

1. dbt's materialization lookup walks a table keyed by (name, adapter_type). {% materialization merge_delete, adapter='snowflake' %} registers under (merge_delete, snowflake); bigquery under (merge_delete, bigquery).
2. At run time, dbt reads target.type (say bigquery), looks up (merge_delete, bigquery), finds the second file, invokes it. If BigQuery variant didn't exist, dbt would look for (merge_delete, default); if that didn't exist either, it would error.
3. The BigQuery variant differs from Snowflake mostly in CURRENT_TIMESTAMP() (parens required) and INSERT ROW (BigQuery MERGE quirk). The overall structure is identical.
4. A single package can ship all three implementations (default, snowflake, bigquery); consumers install once and get the right one automatically.
5. Testing: unit tests per adapter run on their respective warehouse target. CI matrix builds ensure both files render correctly.

Output.

target.type	Which materialization block runs
snowflake	merge_delete_snowflake.sql — MERGE with CURRENT_TIMESTAMP
bigquery	merge_delete_bigquery.sql — MERGE with CURRENT_TIMESTAMP() + INSERT ROW
postgres	(unregistered) — dbt errors: "Materialization merge_delete not found for adapter postgres"

Rule of thumb. Materialization adapter dispatch uses the same "look for (name, adapter) first, fall back to (name, default)" pattern as adapter.dispatch. Ship a default implementation whenever possible; add adapter-specific ones only for dialect gaps.

Senior interview question on custom materializations

A senior interviewer might ask: "Design a iceberg_table materialization that writes to Apache Iceberg tables via Snowflake's Iceberg SQL DDL. It must (a) create the table if missing, (b) support partition-by columns, (c) run VACUUM after every full-refresh, and (d) work as a drop-in replacement for table. Walk through the whole materialization."

Solution Using a full-lifecycle iceberg_table materialization

-- macros/iceberg_table.sql
{% materialization iceberg_table, adapter='snowflake' %}

  {%- set target_relation = this -%}
  {%- set partition_by = config.get('partition_by', []) -%}
  {%- set external_volume = config.require('external_volume') -%}
  {%- set catalog = config.require('catalog') -%}
  {%- set base_location = config.get('base_location', target_relation.identifier) -%}
  {%- set full_refresh = flags.FULL_REFRESH -%}

  -- (a) Pre-hooks
  {{ run_hooks(pre_hooks) }}

  {%- set target_exists = load_relation(target_relation) is not none -%}

  {%- if not target_exists -%}
    -- First run: CREATE ICEBERG TABLE ... AS
    {%- call statement('main') -%}
      CREATE ICEBERG TABLE {{ target_relation }}
      EXTERNAL_VOLUME = '{{ external_volume }}'
      CATALOG         = '{{ catalog }}'
      BASE_LOCATION   = '{{ base_location }}'
      {%- if partition_by | length > 0 %}
      PARTITION BY ({{ partition_by | join(', ') }})
      {%- endif %}
      AS ({{ sql }})
    {%- endcall -%}
  {%- elif full_refresh -%}
    -- Full refresh: drop and recreate; then vacuum
    {%- call statement('drop') -%}
      DROP ICEBERG TABLE IF EXISTS {{ target_relation }}
    {%- endcall -%}
    {%- call statement('main') -%}
      CREATE ICEBERG TABLE {{ target_relation }}
      EXTERNAL_VOLUME = '{{ external_volume }}'
      CATALOG         = '{{ catalog }}'
      BASE_LOCATION   = '{{ base_location }}'
      {%- if partition_by | length > 0 %}
      PARTITION BY ({{ partition_by | join(', ') }})
      {%- endif %}
      AS ({{ sql }})
    {%- endcall -%}
    -- (c) VACUUM after full refresh
    {%- call statement('vacuum') -%}
      ALTER ICEBERG TABLE {{ target_relation }} VACUUM
    {%- endcall -%}
  {%- else -%}
    -- Incremental path: INSERT OVERWRITE via MERGE-style REPLACE
    {%- set tmp_relation = make_temp_relation(target_relation) -%}
    {%- call statement('stage') -%}
      CREATE OR REPLACE TEMPORARY TABLE {{ tmp_relation }} AS ({{ sql }})
    {%- endcall -%}
    {%- call statement('main') -%}
      INSERT INTO {{ target_relation }} SELECT * FROM {{ tmp_relation }}
    {%- endcall -%}
  {%- endif -%}

  -- (a) Post-hooks
  {{ run_hooks(post_hooks) }}

  {{ return({'relations': [target_relation]}) }}

{% endmaterialization %}

-- models/marts/events_iceberg.sql
{{
  config(
    materialized     = 'iceberg_table',
    external_volume  = 'ANALYTICS_EXT_VOL',
    catalog          = 'SNOWFLAKE',
    partition_by     = ['event_date'],
    post_hook        = ["GRANT SELECT ON {{ this }} TO ROLE analyst"]
  )
}}
SELECT event_id, event_date, user_id, event_name, payload
FROM   {{ ref('stg_events') }}

Step-by-step trace.

Phase	Condition	SQL emitted
Pre-hooks	always	user pre-hooks
Main (first run)	not target_exists	CREATE ICEBERG TABLE ... AS SELECT ...
Main (full refresh)	full_refresh	DROP + CREATE + VACUUM
Main (incremental)	else	staged temp + INSERT
Post-hooks	always	GRANT SELECT ... TO ROLE analyst
Return	always	{'relations': [target_relation]}

At run time the materialization picks the right branch based on target existence and the --full-refresh flag. The compile is single-pass; every branch renders valid Snowflake Iceberg DDL. The result registers with dbt's run graph identically to a built-in table materialization; downstream consumers ref() it without knowing the storage layer changed.

Output:

Run	Behaviour	Emitted SQL sample
First dbt run	CREATE + INSERT	CREATE ICEBERG TABLE analytics.events_iceberg EXTERNAL_VOLUME='ANALYTICS_EXT_VOL' CATALOG='SNOWFLAKE' PARTITION BY (event_date) AS SELECT ...
Subsequent dbt run	append	INSERT INTO analytics.events_iceberg SELECT * FROM dbt_tmp
dbt run --full-refresh	DROP + CREATE + VACUUM	DROP, then CREATE, then ALTER ICEBERG TABLE ... VACUUM
Post-hook	always	GRANT SELECT ON analytics.events_iceberg TO ROLE analyst

Why this works — concept by concept:

• Full lifecycle ownership — the materialization owns pre-hooks, main SQL branching, post-hooks, and the return. dbt treats it as opaque; the internal logic is entirely under the author's control.
• Config-driven branching — partition_by, external_volume, and catalog all come from the model's config(). The materialization has zero hard-coded values; every knob is exposed to the model author.
• Adapter registration — declared as adapter='snowflake' so it only fires on Snowflake targets. A Databricks Iceberg variant would be a separate {% materialization iceberg_table, adapter='databricks' %} file.
• Post-hook composition — the user's post_hook = ["GRANT SELECT ..."] fires after the materialization's own vacuum, so grants land on the finalised table. Ordering is: materialization pre → user pre-hooks → materialization main → user post-hooks → materialization post.
• Cost — one materialization definition per adapter per feature. Snowflake Iceberg + Databricks Iceberg + Postgres (unsupported, no default file) = 2 files. The runtime cost per model is dominated by the Iceberg DDL, not by the Jinja overhead.

SQL
Topic — sql
SQL DDL and materialization design problems

Practice →

Optimization Topic — optimization Optimization problems on incremental and MERGE patterns

Practice →

---

5. Pre-hooks, post-hooks, and run_query

Hooks are the run's event bus — on-run-start, pre_hook, post_hook, on-run-end fire around the run graph and let you attach grants, vacuums, notifications, and audits without touching model SQL

The mental model in one line: hooks are user-defined SQL wired to four lifecycle points — on-run-start (once at run start), pre_hook (before each model), post_hook (after each model), on-run-end (once at run finish) — and the senior discipline is (a) idempotency, because dbt may re-run a hook on retry, (b) execute guarding for anything that queries the warehouse, and (c) never side-effecting outside the run's transaction boundary in a way that can't be undone.

The four hook types.

• on-run-start. Project-level; runs once before any model runs. Use for: opening a run-metadata row, setting session-level GUCs, starting an audit transaction.
• pre_hook. Model-level; runs before each model. Use for: setting model-specific session variables, taking a lock, creating a temp resource the model depends on.
• post_hook. Model-level; runs after each model. Use for: granting SELECT, running ANALYZE / VACUUM, upserting a freshness marker.
• on-run-end. Project-level; runs once after all models run. Use for: closing the run-metadata row, notifying Slack, running a cross-cutting materialization refresh.

Where each is configured.

• Project-level (on-run-start, on-run-end). In dbt_project.yml at the top level, or via on-run-start: / on-run-end: keys.
• Model-level (pre_hook, post_hook). In the model's config() block, or in dbt_project.yml under models: for a folder-wide default.
• Multiple hooks. All four keys accept a list; hooks in the list run in order.

The run_query companion — imperative introspection.

• What it is. run_query(sql) — sends SQL to the warehouse (during execute), returns an agate.Table.
• Difference from {% call statement %}. statement is used inside a materialization; run_query is used inside a macro or hook. Both hit the warehouse; run_query returns rows to Jinja, statement doesn't.
• When to use. For any hook that needs to inspect the warehouse state before deciding what SQL to emit.

Idempotency — the non-negotiable hook rule.

• Why. dbt may re-run hooks on retry or after a failed model in the same run. Non-idempotent hooks (e.g. INSERT INTO audit_log) produce duplicate rows on retry.
• The pattern. Use CREATE OR REPLACE, MERGE, INSERT ... ON CONFLICT DO NOTHING, or DELETE + INSERT. Never a bare INSERT in a hook that could re-run.
• The on-run-end exception. On-run-end fires once per successful run; a bare INSERT there is fine for a "run completed" audit row. But if the run partially fails and is retried, you get one row per retry — usually the desired behaviour.

Hook order — the exact sequence in a run.

• 1. on-run-start fires once, in list order.
• 2. For each model (topological order): pre_hook (list order) → materialization main → post_hook (list order).
• 3. on-run-end fires once after all models complete, in list order.
• The interviewer probe. "In what order do on-run-start, pre_hook, materialization, post_hook, and on-run-end fire?" — say the four steps above.

Common interview probes on hooks.

• "How would you GRANT SELECT to a role on every model?" — post_hook at project level under models: +post-hook: [...].
• "How would you notify Slack when a dbt run completes?" — on-run-end with a run_query that emits a UDF or a Python step.
• "What breaks if a pre_hook isn't idempotent?" — a retry inserts duplicates; every hook must be safe to re-execute.
• "What's the difference between pre_hook and on-run-start?" — model-level vs run-level; pre_hook fires once per model, on-run-start fires once per whole run.

Worked example — GRANT SELECT via project-level post_hook

Detailed explanation. Governance requires every table produced by dbt to have SELECT granted to the analyst and reporter roles. Writing a post_hook per model is 500 lines of duplication; instead, one project-level post_hook fires on every model automatically.

• Where. dbt_project.yml under models: — a top-level +post-hook: inherits down the folder tree.
• The macro. Wrap the grant SQL in a macro so you can vary it per adapter via dispatch if needed.
• The idempotency. GRANT SELECT is idempotent (Postgres, Snowflake, BigQuery all treat it as "ensure grant exists"), so retries are safe.

Question. Configure the project-level post_hook and write the underlying grant macro.

Input.

Roles to grant to	Adapters
analyst, reporter	Snowflake, BigQuery, Postgres

Code.

# dbt_project.yml
models:
  my_project:
    +post-hook:
      - "{{ grant_select(this) }}"

-- macros/grant_select.sql
{% macro grant_select(relation) %}
  {%- set roles = ['analyst', 'reporter'] -%}
  {%- if target.type == 'snowflake' -%}
    {%- for role in roles %}
      GRANT SELECT ON {{ relation }} TO ROLE {{ role }};
    {%- endfor -%}
  {%- elif target.type == 'bigquery' -%}
    {%- for role in roles %}
      GRANT `roles/bigquery.dataViewer` ON {{ relation.database }}.{{ relation.schema }}.{{ relation.identifier }}
        TO "group:{{ role }}@company.com";
    {%- endfor -%}
  {%- elif target.type == 'postgres' -%}
    {%- for role in roles %}
      GRANT SELECT ON {{ relation }} TO {{ role }};
    {%- endfor -%}
  {%- else -%}
    -- No grant mapping for adapter {{ target.type }}; skipping
    SELECT 1
  {%- endif -%}
{% endmacro %}

Step-by-step explanation.

1. models: my_project: +post-hook: [...] sets a default post-hook for every model under my_project/. Individual models can add more; the project-level one always runs.
2. {{ grant_select(this) }} invokes the macro with the current model's relation object. this inside a hook expression resolves to the model that just ran.
3. The macro dispatches on target.type. Snowflake uses GRANT ... TO ROLE; BigQuery uses IAM-style GRANTs; Postgres uses simple GRANTs. Each is idempotent.
4. The for role in roles loop emits one GRANT per role — two per model per adapter. dbt sends the list of statements to the adapter; each runs separately.
5. Adding a new role is a one-line change in the macro. Adding a new adapter is a new elif branch. The 500-line-per-model alternative is never revisited.

Output.

Adapter	Emitted post_hook SQL for analytics.fct_orders
snowflake	GRANT SELECT ON analytics.fct_orders TO ROLE analyst; GRANT SELECT ON analytics.fct_orders TO ROLE reporter;
bigquery	GRANT roles/bigquery.dataViewer ON prod.analytics.fct_orders TO "group:[e-mail]"; ...
postgres	GRANT SELECT ON analytics.fct_orders TO analyst; GRANT SELECT ON analytics.fct_orders TO reporter;

Rule of thumb. Any policy that should apply to every model (grants, tags, vacuum, comment) belongs in a project-level +post-hook: — never in per-model configs. The one-macro-many-models pattern is what makes dbt scale to hundreds of models.

Worked example — audit log via on-run-start + on-run-end

Detailed explanation. A team needs a compliance audit log — one row per dbt run capturing the run id, start time, end time, invocation user, invocation reason. on-run-start inserts the row (open state); on-run-end updates the row with the end time and status.

• The table. audit.dbt_runs with run_id, started_at, ended_at, invocation_id, invocation_reason, status.
• The insert. Fires once per run at on-run-start.
• The update. Fires once per run at on-run-end.
• The invocation id. invocation_id context variable — a UUID unique to this dbt process invocation.

Question. Wire the hooks in dbt_project.yml and write the two macros.

Input.

Column	Source
invocation_id	dbt context (invocation_id)
started_at	on-run-start current_timestamp
ended_at	on-run-end current_timestamp
invocation_reason	var, injected by CI (--vars '{invocation_reason: nightly}')
status	on-run-end — success if all models succeeded; error otherwise

Code.

# dbt_project.yml
on-run-start:
  - "{{ audit_run_start() }}"

on-run-end:
  - "{{ audit_run_end(results) }}"

-- macros/audit_run_start.sql
{% macro audit_run_start() %}
  {%- if execute -%}
    INSERT INTO audit.dbt_runs (invocation_id, started_at, invocation_reason, status)
    VALUES (
      '{{ invocation_id }}',
      CURRENT_TIMESTAMP,
      '{{ var("invocation_reason", "adhoc") }}',
      'running'
    );
  {%- endif -%}
{% endmacro %}

-- macros/audit_run_end.sql
{% macro audit_run_end(results) %}
  {%- if execute -%}
    {%- set failed = results | selectattr('status', 'equalto', 'error') | list | length -%}
    {%- set errored = results | selectattr('status', 'equalto', 'fail')  | list | length -%}
    {%- set status = 'error' if (failed + errored) > 0 else 'success' -%}

    UPDATE audit.dbt_runs
    SET    ended_at = CURRENT_TIMESTAMP,
           status   = '{{ status }}',
           models_run = {{ results | length }},
           models_failed = {{ failed + errored }}
    WHERE  invocation_id = '{{ invocation_id }}';
  {%- endif -%}
{% endmacro %}

Step-by-step explanation.

1. on-run-start: ["{{ audit_run_start() }}"] fires the macro once at run start. The macro emits an INSERT against audit.dbt_runs with the invocation id, start time, and reason.
2. invocation_id is a dbt context variable — a UUID assigned per dbt CLI invocation. Used as the natural key so the on-run-end update knows which row to touch.
3. on-run-end: ["{{ audit_run_end(results) }}"] fires after all models complete. results is a special context var only available in on-run-end — a list of per-model run results with .status and .node.
4. The macro inspects results to compute the run-level status: error if any model errored, success otherwise. The Jinja selectattr filter picks results with status == 'error'; length counts them.
5. The UPDATE sets ended_at, status, models_run, and models_failed on the row previously inserted by on-run-start. The invocation id ties them together.

Output.

Time	Table state
Before run	(no row)
t+0 (on-run-start)	INSERT ... VALUES ('abc-123', 2026-06-22 10:00:00, 'nightly', 'running');
t+3m (on-run-end, success)	UPDATE ... SET ended_at=2026-06-22 10:03:00, status='success', models_run=45, models_failed=0 WHERE invocation_id='abc-123';
t+3m (on-run-end, failure)	UPDATE ... SET ended_at=2026-06-22 10:03:00, status='error', models_run=45, models_failed=2 WHERE invocation_id='abc-123';

Rule of thumb. on-run-start + on-run-end is the paired hook idiom for run-level bookkeeping. The invocation_id context variable is the run's natural key — use it wherever you need to correlate hooks, log lines, and warehouse rows.

Worked example — run_query inside a pre_hook for adaptive vacuum

Detailed explanation. A team wants an adaptive pre_hook that runs VACUUM on the target only if the last vacuum was more than 6 hours ago. The hook queries pg_stat_user_tables (Postgres) to check the last-vacuum time and emits the VACUUM statement only when needed.

• The pattern. run_query returns the last vacuum time; Jinja compares it to now; the hook emits either VACUUM or a no-op.
• The idempotency. VACUUM is idempotent; running it twice is wasteful but not incorrect.
• The senior tightener. The 6-hour threshold is a var — CI overrides it to 0 (always vacuum in CI for reproducibility).

Question. Write the adaptive-vacuum pre_hook macro and wire it to a model.

Input.

Parameter	Value
Threshold	6 hours (default), 0 in CI
Adapter	Postgres
Model	analytics.fct_orders

Code.

-- macros/adaptive_vacuum.sql
{% macro adaptive_vacuum(relation) %}
  {%- if not execute -%}
    SELECT 1  -- parse-phase no-op
  {%- else -%}
    {%- set threshold = var('vacuum_threshold_hours', 6) -%}
    {%- set query -%}
      SELECT COALESCE(EXTRACT(EPOCH FROM (now() - last_vacuum)) / 3600, 999) AS hours_since_vacuum
      FROM   pg_stat_user_tables
      WHERE  schemaname = '{{ relation.schema }}'
        AND  relname    = '{{ relation.identifier }}'
    {%- endset -%}
    {%- set r = run_query(query) -%}
    {%- if r and r.rows | length > 0 and r.rows[0][0] > threshold -%}
      VACUUM ANALYZE {{ relation }};
    {%- else -%}
      -- last vacuum was < {{ threshold }} hours ago; skipping
      SELECT 1
    {%- endif -%}
  {%- endif -%}
{% endmacro %}

-- models/marts/fct_orders.sql
{{
  config(
    materialized = 'incremental',
    unique_key   = 'order_id',
    pre_hook     = ["{{ adaptive_vacuum(this) }}"]
  )
}}

SELECT order_id, customer_id, order_total, order_ts
FROM   {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE order_ts > (SELECT COALESCE(MAX(order_ts), '1970-01-01') FROM {{ this }})
{% endif %}

Step-by-step explanation.

1. The pre_hook fires before the model's main SQL runs. The macro checks execute — parse-phase becomes a no-op SELECT 1.
2. During run, the introspection query hits pg_stat_user_tables for the current model's schema/identifier. Returns the age of the last vacuum in hours (or 999 if never vacuumed).
3. Jinja compares the age to the threshold var (6 hours default, 0 in CI where we want deterministic vacuum-every-run behaviour).
4. If age > threshold, emit VACUUM ANALYZE {{ relation }}; else emit a no-op SELECT 1. The hook always emits something — an empty hook confuses dbt.
5. The overall effect: on a nightly run, the vacuum fires only if the table hasn't been vacuumed since 4 AM; on-demand runs during the day skip the vacuum entirely. Warehouse cost drops without governance/hygiene degrading.

Output.

Scenario	Rendered pre_hook SQL
Last vacuum 8 hours ago	VACUUM ANALYZE analytics.fct_orders;
Last vacuum 2 hours ago	-- last vacuum was < 6 hours ago; skipping\nSELECT 1
First run (never vacuumed)	VACUUM ANALYZE analytics.fct_orders; (999 > 6)
CI (threshold=0)	Always vacuums

Rule of thumb. run_query in a hook is the escape hatch for "do X only when the warehouse says Y." Guard with execute, emit a safe no-op on skip, and cache the introspection result if you'd otherwise call it in a hot loop.

Senior interview question on hooks + run_query

A senior interviewer might ask: "Design a hook system that (a) fires a Slack notification on any model failure, (b) attaches a Snowflake TAG to every model with the git commit SHA, (c) runs an aggregate freshness check at on-run-end that fails the run if any source is older than 6 hours. Walk me through the full hook wiring."

Solution Using a three-hook composition (Slack + tag + freshness)

# dbt_project.yml
on-run-start:
  - "{{ audit_run_start() }}"
on-run-end:
  - "{{ audit_run_end(results) }}"
  - "{{ freshness_gate() }}"

models:
  my_project:
    +pre-hook:
      - "{{ tag_with_commit(this) }}"
    +post-hook:
      - "{{ grant_select(this) }}"

-- macros/tag_with_commit.sql
{% macro tag_with_commit(relation) %}
  {%- if not execute -%}
    SELECT 1
  {%- else -%}
    {%- set sha = env_var('GIT_COMMIT_SHA', 'unknown') -%}
    {%- if target.type == 'snowflake' -%}
      ALTER TABLE {{ relation }} SET TAG git_commit = '{{ sha }}';
    {%- else -%}
      COMMENT ON TABLE {{ relation }} IS 'built by commit {{ sha }}';
    {%- endif -%}
  {%- endif -%}
{% endmacro %}

-- macros/freshness_gate.sql
{% macro freshness_gate() %}
  {%- if not execute -%}
    SELECT 1
  {%- else -%}
    {%- set query -%}
      SELECT source_name, table_name, EXTRACT(EPOCH FROM (now() - max_loaded_at)) / 3600 AS hours_old
      FROM   audit.source_freshness
      WHERE  EXTRACT(EPOCH FROM (now() - max_loaded_at)) / 3600 > 6
    {%- endset -%}
    {%- set r = run_query(query) -%}
    {%- if r and r.rows | length > 0 -%}
      {%- do log("STALE SOURCES DETECTED:", info=true) -%}
      {%- for row in r.rows -%}
        {%- do log("  " ~ row[0] ~ "." ~ row[1] ~ " is " ~ row[2] ~ " hours old", info=true) -%}
      {%- endfor -%}
      {{ exceptions.raise_compiler_error("Freshness gate failed: " ~ (r.rows | length) ~ " stale sources") }}
    {%- endif -%}
    SELECT 1
  {%- endif -%}
{% endmacro %}

-- macros/notify_on_failure.sql (called from audit_run_end)
{% macro notify_on_failure(results) %}
  {%- set failed = results | selectattr('status', 'equalto', 'error') | list -%}
  {%- if failed | length > 0 -%}
    {#- Emit a marker row that a Python step outside dbt watches -#}
    INSERT INTO audit.slack_queue (invocation_id, message)
    VALUES ('{{ invocation_id }}',
            'dbt run failed: {{ failed | length }} models errored');
  {%- endif -%}
{% endmacro %}

Step-by-step trace.

Hook	When	Purpose
on-run-start	once at start	insert audit row (running)
pre-hook (per model)	before each model	ALTER TABLE ... SET TAG git_commit = '<sha>'
materialization main	per model	CTAS / MERGE / VIEW
post-hook (per model)	after each model	GRANT SELECT ... (per-adapter dispatch)
on-run-end #1	once after all models	update audit row + queue Slack marker
on-run-end #2	once after all models	freshness_gate() — raises if stale sources

The run-level ordering is deterministic. Every model is tagged with the commit SHA on Snowflake or receives a comment on other adapters. Every model is granted to analyst and reporter. The run-end freshness gate raises a compiler error if any source has been stale for > 6 hours, causing the whole run to fail visibly rather than silently proceeding on stale data.

Output:

Surface	Effect
Every Snowflake table	TAG git_commit = 'a1b2c3'
Every non-Snowflake table	COMMENT 'built by commit a1b2c3'
Every table	GRANT SELECT TO analyst, reporter
audit.dbt_runs	one row per run with status, models_run, models_failed
audit.slack_queue	one row per failing run (Python watcher POSTs to Slack)
Stale sources	dbt run exits non-zero with clear error listing each stale source

Why this works — concept by concept:

• Project-level hooks for defaults — +pre-hook and +post-hook under models: inherit down every folder. One line applies to hundreds of models.
• on-run-start / on-run-end for run-level — the audit row is a run-level concept, not a per-model one; it belongs in the run-level hooks, not in per-model post_hooks.
• execute guarding — every macro that calls run_query or emits warehouse-touching SQL is wrapped in {% if execute %} with a parse-safe no-op fallback.
• exceptions.raise_compiler_error — the freshness gate uses dbt's exceptions.raise_compiler_error to fail the run loudly. Without it, the run "succeeds" with stale data downstream.
• Cost — hooks add one to two SQL statements per model per run. On a project with 300 models, that's 600 extra statements per nightly run — negligible against the model runs themselves. Freshness gate is one query at run end. Slack marker is one INSERT per failing run.

SQL
Topic — sql
SQL hook and lifecycle problems

Practice →

ETL
Topic — etl
ETL problems on freshness and orchestration hooks

Practice →

---

Cheat sheet — dbt macro recipes

• Macro definition 5-line template. {% macro name(arg1, arg2='default') %} body with {{ arg1 }} and control flow {% endmacro %}. Invoke with {{ name(...) }} (emit return value) or {% do name(...) %} (call for side effects).
• The execute guard. Every macro that calls run_query, statement, or adapter.* must wrap the imperative branch in {% if execute %} with a compile-time fallback (usually {{ return(default) }} or a placeholder literal).
• The context vocabulary. target (active profile), this (current model relation), var(name, default) (compile-time knob), env_var(name, default) (OS env), execute (True during run), run_query(sql) (returns agate.Table), ref(name) / source(src, tbl) (relation resolvers), is_incremental() (True when incremental + relation exists + not full-refresh).
• adapter.dispatch pattern. {% macro my_macro(args) %}{{ return(adapter.dispatch('my_macro', 'my_ns')(args)) }}{% endmacro %} — wrapper. Implementations: default__my_macro, snowflake__my_macro, bigquery__my_macro. Search order overridable via dispatch: in dbt_project.yml.
• Dispatch search override. dispatch: [{macro_namespace: dbt_utils, search_order: [my_project, dbt_utils]}] — makes the local project shadow a package macro without forking.
• Custom materialization skeleton. {% materialization name, adapter='snowflake' %} → {{ run_hooks(pre_hooks) }} → branch on target-exists / full_refresh → {% call statement('main') %} ... {% endcall %} → {{ run_hooks(post_hooks) }} → {{ return({'relations': [target_relation]}) }} → {% endmaterialization %}.
• Materialization lifecycle order. on-run-start → (pre-hooks → materialization main → post-hooks) per model in DAG order → on-run-end.
• pre_hook / post_hook config. Per-model: config(pre_hook=[...], post_hook=[...]). Project-level: models: my_project: +pre-hook: [...] +post-hook: [...]. Both accept lists; hooks run in list order.
• Idempotency. Every hook must be safe to re-execute. Use CREATE OR REPLACE, MERGE, INSERT ... ON CONFLICT DO NOTHING, or DELETE + INSERT. Never a bare INSERT in a hook that could re-run.
• run_query fetch + iterate. {% set r = run_query(sql) %}{% for row in r.rows %}{% do list.append(row[0]) %}{% endfor %} — the standard imperative pattern. r.columns[0].values() returns a column as a list; r.rows returns row tuples.
• generate_schema_name override. Ship on day one. Prod: custom_schema_name verbatim. Dev/CI: target.schema ~ '_' ~ custom_schema_name for per-developer isolation.
• Unit-test a macro. dbt 1.8+ unit_tests: YAML block with given: (mock inputs via format: dict) and expect: (expected outputs). Or compile + grep in CI for a fast lint. Every non-trivial macro ships with at least one.
• invocation_id for run correlation. Every dbt process has a UUID; use it as the natural key for run-level audit rows, log lines, and cross-warehouse joins.
• Freshness gate via exceptions.raise_compiler_error. In on-run-end, run a run_query that returns stale sources; raise a compiler error if any exist. The run fails loudly rather than silently proceeding on stale data.
• Grant policy as project post_hook. models: my_project: +post-hook: ["{{ grant_select(this) }}"] — one line, one macro, hundreds of tables granted correctly across adapters via dispatch.

Frequently asked questions

What are dbt macros and when do I need to write one?

dbt macros are Jinja functions that render SQL at compile time — they're how dbt turns generic templates into concrete warehouse SQL. You write one the moment you'd otherwise copy-paste the same SQL pattern into a third model. Common triggers: pivoting a long table by category values, computing a business-days difference, generating a schema-name policy, wrapping a warehouse-specific hint. Every serious dbt project has 5-20 project-local macros plus a few from dbt_utils. The senior signal is not "have you written macros" — everyone has — it's whether you (a) use adapter.dispatch for portability, (b) guard run_query with {% if execute %}, (c) ship unit tests, and (d) namespace correctly. A macro that skips those four is a maintenance liability; a macro that respects them is durable infrastructure for the whole team.

Jinja compile vs execute — what's the difference?

Jinja renders every .sql file in dbt across three phases: parse (build the DAG, resolve ref(), execute False), compile (identical rendering but honouring --select filters), and run (walk the DAG in topological order, execute = True, invoke the materialization macro which sends SQL to the warehouse). The execute flag is False during parse/compile and True during run. Anything that queries the warehouse (run_query, adapter.get_relation, statement) returns None or errors during parse — you must guard it with {% if execute %} ... {% endif %} and provide a compile-safe fallback. Miss the guard and dbt compile fails with AttributeError: 'NoneType' object has no attribute ... or emits garbage SQL. This is the single most common macro bug junior authors ship; senior authors write the guard as reflex.

adapter.dispatch — when do I need it?

Any time your macro's SQL differs across warehouses. adapter.dispatch('my_macro', 'my_ns') looks up {target.type}__my_macro in my_ns and falls back to default__my_macro if the adapter-specific version is missing. The canonical pattern: one public wrapper macro that just calls adapter.dispatch(...), plus one default__my_macro (portable ANSI-ish SQL) plus per-warehouse variants (snowflake__my_macro, bigquery__my_macro) where the dialect gap justifies it. Avoid inline {% if target.type == 'snowflake' %} branches inside a single macro — they're not composable, packages can't override them, and adding a fourth warehouse requires editing every macro. Dispatch is what makes packages like dbt_utils portable across every dbt adapter without forks. Ship it in every reusable macro from day one.

Can I write a dbt custom materialization?

Yes — {% materialization name, adapter='snowflake' %} blocks define custom lifecycle-aware macros that own how a model's compiled SELECT becomes a persistent object. Write one when the built-ins (table, view, incremental, ephemeral, snapshot) can't fit — soft-delete tombstones, Iceberg / Delta table creation, insert-overwrite-partition semantics, zero-copy clone patterns. The skeleton is: read config, fire run_hooks(pre_hooks), branch on target existence and --full-refresh, wrap the compiled SQL in {% call statement('main') %} ... {% endcall %}, fire run_hooks(post_hooks), return {'relations': [target_relation]}. Custom materializations are power tools — the operational cost (per-adapter dispatch, unit tests, dbt-core upgrade compatibility) is real. Reach for a post_hook first; escalate to a custom materialization only when the main SQL structure itself needs to change (not just what runs around it).

pre_hook vs post_hook vs on-run-end — which do I use?

Model-level pre_hook fires before each model; use for session-level SET, taking a lock, staging a temp resource the model needs. Model-level post_hook fires after each model; use for GRANTs, ANALYZE/VACUUM, upserting a freshness marker. Project-level on-run-start fires once at run start (before any model); use for opening a run-metadata row or setting a session GUC that spans all models. Project-level on-run-end fires once at run end (after all models); use for closing the metadata row, notifying Slack, running a cross-cutting freshness gate. Every hook must be idempotent — dbt may retry a hook on failure. CREATE OR REPLACE, MERGE, INSERT ... ON CONFLICT DO NOTHING are safe; bare INSERT isn't. The senior signal is naming which hook fires where and enforcing idempotency without prompting.

How do I unit-test a dbt macro?

Two layers. Compile check — run dbt compile --select some_model_that_uses_the_macro, then grep the compiled SQL in target/compiled/... for the expected fragment. Fast, warehouse-free, ideal for CI lint step. Perfect for verifying adapter.dispatch picked the right implementation per target. Semantic test — dbt 1.8+ unit_tests: YAML block or the dbt-unit-testing package. Declare given: (mock upstream rows via format: dict or CSV) and expect: (expected model output); dbt test --select unit_test:... runs the model against the mocks and compares actual vs expected row-by-row. The senior discipline: every non-trivial macro ships with at least (a) a compile-check assertion in CI per adapter and (b) a unit test for the happy path plus one edge case. Broken dispatch or a regressed macro is caught on push, not on the next production run.

Practice on PipeCode

• Drill the SQL practice library → for the macro-composition, window-function, and Jinja-loop problems that senior dbt interviewers love.
• Rehearse on the ETL practice library → for the incremental-model, insert-overwrite, and freshness-gate problems that motivate materializations and hooks.
• Sharpen the tuning axis with the optimization practice library → for the compile-time introspection, dispatch-decision, and materialization-choice problems.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the macro + dispatch + materialization intuition against real graded inputs.

Lock in dbt macro muscle memory

Docs explain the syntax. PipeCode drills explain the decision — when `adapter.dispatch` beats an inline `if`, when a custom materialization is warranted, when a `pre_hook` is safer than a manual DDL step. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior analytics engineers actually face.

Practice SQL problems →
Practice ETL problems →