Articles
dbt Macro Interview Questions: Jinja, Parse-Time Errors, and Compiled SQL
Practice dbt macro interviews with a failing run_query example, execute guard, compiled SQL checklist, and warehouse side-effect checks.
A dbt macro can fail before a model runs, produce valid SQL with the wrong meaning, or quietly issue a warehouse query while you are “only compiling.” Those are three different failures. In an interview, the strongest answer identifies which phase owns the symptom, opens the compiled SQL when it exists, and proposes a small test that proves the repair. The official dbt Jinja and macros guide shows how Jinja renders into SQL; its execute reference separates the initial project parse from later compilation and execution.
This is a technical practice guide, not a claim about any employer's actual prompt. We will debug one original revenue-report model, including a failing run_query macro, a guarded repair, and a compiled-SQL review. For an adjacent SQL investigation, try Debug row loss after SQL joins. Adapter behavior and artifact paths can vary by dbt version and platform.
The interview fixture: a regional revenue model
Assume stg_orders has order_id, region_code, amount, and order_date. An analytics team wants one report column per active region and a total. They have written a macro that queries a dim_regions relation to discover region codes, then emits expressions such as sum(case when region_code = 'US' then amount else 0 end) as revenue_us. The reporter selects from {{ ref('stg_orders') }} and groups by order_date.
The requirements make the failure observable: the compiled query should include exactly the approved region columns, count every order in the total, and remain valid when the region lookup returns no rows. A new region should not silently produce an unsafe SQL identifier. The fixture is original; it is a practice case rather than evidence that dbt interviews use this particular schema.
An interviewer may hand you three clues: dbt compile raises a None attribute error, a subsequent attempt omits the US column, and CI unexpectedly queries the warehouse. Place each clue on a timeline: parse the graph, render the model, inspect compiled SQL, then execute the model where applicable.
Question 1: What does Jinja output, and what does SQL do afterward?
The official dbt macro guide distinguishes {{ ... }} expressions, which output rendered text, from {% ... %} statements, which control rendering without directly outputting their value. A macro is a reusable Jinja definition usually kept in macros/*.sql. The warehouse never receives {% for ... %}; it receives the SQL emitted by that loop. Consequently, a model can have valid Jinja syntax but generate invalid or logically wrong SQL.
Suppose a candidate sees {{ region_columns() }} and says, “The database runs the macro.” Ask them to identify the actual boundary. dbt renders that call into a string of SQL expressions; the database runs the resulting query. The distinction tells you whether to investigate a Jinja variable and loop or a warehouse error such as an unknown column or unsupported cast. If the compiled file has sum(case...), Jinja has already done its work for that fragment.
String arguments need care. In {{ cents_to_dollars('amount') }}, 'amount' is a Jinja string naming a SQL column. Without quotes, Jinja looks for an amount variable. Conversely, when emitting a SQL literal from a dynamic region value, you need the warehouse-appropriate quoting or escaping strategy. Do not assume Python-style repr, a bare interpolation, and SQL string quoting are interchangeable. For an interview answer, use a fixed approved set of region codes or describe the adapter-safe literal/identifier handling you would verify for the actual warehouse.
Question 2: Why can run_query fail during parsing?
The execute reference says the initial parse reads project files, identifies ref() and source(), and builds a manifest without running SQL; execute is false. A database-dependent result is therefore unavailable then. This failing macro assumes the result table already exists:
{% macro active_regions() %}
{% set sql %}
select region_code from {{ ref('dim_regions') }} where is_active = true
{% endset %}
{% set result = run_query(sql) %}
{{ return(result.columns[0].values()) }}
{% endmacro %}
On the first pass, result can be none. Reading result.columns is the immediate failure, not a malformed select in the final model. In a spoken answer, name the object and access that fail: “The parse has no result table, but the macro dereferences its first column.” That is more diagnostic than “dbt cannot run queries in macros,” which is false during the appropriate later phase.
A safer teaching version guards the database work and returns a parse-time placeholder. It also handles a query that does not return a table, which the official run_query reference says is possible for non-result statements:
{% macro active_regions() %}
{% set sql %}
select region_code from {{ ref('dim_regions') }} where is_active = true
{% endset %}
{% if execute %}
{% set result = run_query(sql) %}
{% if result is not none %}
{{ return(result.columns[0].values()) }}
{% endif %}
{% endif %}
{{ return([]) }}
{% endmacro %}
That repair is a starting point, not a complete production contract. Check whether the result has the expected column, how an empty table should render, and whether return() control flow behaves as intended for your installed dbt version. Keep dependency references visible to the parser; hiding a ref() solely inside an execution-only branch can complicate graph discovery. Here, the ref('dim_regions') appears in the SQL block outside the guard so dbt can discover it during parsing.
Question 3: Does dbt compile mean no warehouse traffic?
No. The name describes compilation of model SQL, not a blanket ban on queries from macros. The current dbt run_query documentation explicitly warns that run_query may run SQL against the warehouse with a live connection during dbt compile and, by default, dbt docs generate. A guarded if execute protects the initial parse; it does not mean “run only when the final model materializes.”
This is the interview's side-effect trap. A read-only lookup may add latency or require CI credentials. A DML statement placed in a macro could modify warehouse state in workflows the author thought were observational. If the lookup is expensive or mutating, discuss whether it belongs in compilation at all. Use a static mapping, a seeded relation, a model dependency, or an explicit operation when that better expresses the contract. If a live query remains necessary, scope its command contexts and permissions deliberately; verify the behavior in the installed dbt release rather than assuming a single flag works everywhere.
An interviewer may ask why dbt parse passed while dbt compile failed. The answer is that the first pass can use a placeholder while a subsequent phase runs the lookup against a real relation. If dim_regions is absent in the selected target, the failure has moved from None dereference to warehouse relation resolution. Show how you would inspect command logs and target selection rather than declaring the guard broken.
Question 4: How do you inspect compiled SQL without losing the source context?
The dbt Jinja guide recommends inspecting compiled SQL and comparing it side by side with the source. In dbt v1 CLI projects, dbt compile writes artifacts under target/compiled/<project>/; the current documentation also describes a compiled-SQL pane in dbt's development interface. Do not state one local path as universal across platform and version. Find the compiled output that belongs to the selected model and current target, then trace each emitted fragment back to the macro call.
For the regional model, inspect the output in this order. First, does the rendered from point at the intended stg_orders relation? Second, are region literals quoted and aliases valid for the target adapter? Third, is there a comma before and after each generated expression, including the empty-list case? Fourth, does total_revenue count all orders independently of the generated region columns? Fifth, did the macro emit any blank expression that leaves select order_date, , total_revenue? These checks find semantic and syntax errors even when the Jinja template itself parses.
A miniature test changes the lookup from ['US', 'CA'] to [] and ['US']. With no regions, the compiled query still needs a valid order_date, sum(amount) as total_revenue list; one region adds one expression and no trailing comma. Check punctuation around fixed columns outside the loop.
A compiled-output checklist for the fixture
Use this table as an answer artifact. It ties an observed symptom to the right inspection point and a concrete validation, instead of proposing “add tests” in the abstract.
| Symptom | First evidence to inspect | Repair and proof |
|---|---|---|
None attribute error before query execution | execute branch and result.columns access | Return a parse placeholder; rerun parse and compile |
Unknown dim_regions relation | Target/profile and compiled ref() destination | Confirm dependency and relation existence in selected environment |
| Missing or duplicated region column | Lookup rows, loop input, rendered select list | Compare expected codes to compiled aliases and result columns |
| SQL syntax error near comma | First and last generated expressions | Compile with zero, one, and two regions |
| Wrong total despite valid SQL | Rendered where, case, and aggregation grain | Reconcile raw orders against output by date |
| Warehouse activity in CI compile | Macro run_query calls and command logs | Remove side effect or scope it explicitly; verify with limited credentials |
Capture the command, dbt version, adapter, target, model selection, and artifact timestamp so you do not debug a stale compiled file.
Question 5: How would you make the dynamic columns safe?
Dynamic SQL has two boundaries: the data values inserted as SQL literals and the column aliases or identifiers generated from them. A region code containing a quote can break a literal unless it is escaped correctly for the warehouse. A region label containing spaces, punctuation, or a reserved word can break an alias. Even when the region table is “internal,” upstream data quality can introduce an unexpected value. A sound interview answer names a whitelist or a reviewed normalization scheme, rejects collisions, and tests the compiled query on the actual adapter.
For this fixture, assume only uppercase two-letter codes are approved and map each to a lowercase alias such as revenue_us. That constraint makes the example inspectable, but it is a fixture policy, not a built-in dbt guarantee. Confirm that two source codes cannot normalize to the same alias. If the business needs arbitrary codes, either use adapter-safe quoting through documented facilities or prefer a row-based result instead of dynamically changing the model schema. Stable schemas often make downstream contracts easier to maintain.
run_query also makes the model's shape depend on warehouse state at compile time. A new region can add a column without a code change. If that is unacceptable for dashboards, enumerate the output contract and treat changes as reviewed schema migrations.
Question 6: What tests prove the fix beyond a successful compile?
Start with parse and compilation because they localize template errors. Compile the selected model under the intended target and inspect the generated SQL for zero, one, and multiple regions. Then run a small data fixture with known orders: two US orders, one CA order, and one inactive region. Assert the date grain, the two approved columns, and a total equal to the sum of all included order amounts. If amount can be null or refunds are negative, state the intended semantics before writing expected totals.
Add a failure fixture: a non-approved region code and a missing lookup relation. Decide whether the macro should fail loudly, omit the code, or use a documented fallback; do not silently turn an exception into an empty schema. A production guard that returns [] during every execution because of a swallowed error would produce a valid but misleading report. Check logs and source data along with the compiled artifact.
Finally, test operational behavior. Compile with a warehouse role that has lookup read access but no mutation privileges. Inspect query history to establish which macro queries ran. Repeat in CI; local success with a privileged developer profile does not prove deploy safety.
How to answer this interview case in two minutes
Say: “I would first locate the failure in dbt's phases. The initial parse builds the graph without query results, so dereferencing run_query(...).columns there can fail. I would guard the warehouse-dependent work with execute, keep ref('dim_regions') discoverable, and give parsing a safe placeholder. Next I would compile the selected model and compare its output with the source, checking generated columns, aliases, commas, and aggregation grain. Then I would run a tiny zero/one/two-region fixture and reconcile totals. I would also inspect warehouse query history, because dbt compile can execute run_query even though it does not materialize this model.”
If pressed, identify the trade-off: dynamic columns are convenient but make schema depend on lookup data at compile time. A stable row-based schema or explicit mapping may be preferable when downstream consumers require a fixed contract. This answer shows phase reasoning, proof from artifacts, and production judgment; it does not rest on memorizing a Jinja keyword.