dbt / Basic: project and model foundations / Lesson 4

Jinja, macros and execution context

Generate reviewable SQL while keeping string arguments, dependency discovery and warehouse side effects explicit.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish template-time values from SQL identifiers and literals.
Use execute-aware logic without calling compilation universally offline.

Jinja generates SQL rather than evaluating every row

Jinja runs while dbt renders project text. A macro can generate a CASE expression that the database later evaluates for each loan, but the macro itself is not a database scalar function automatically installed by its definition. Expressions use {{...}} for output and statements use {%...%} for control flow. Keep generated SQL readable enough to review. A short macro that hides several joins or target-specific casts can make lineage and correctness harder to inspect. Prefer a focused abstraction with documented argument meaning, output shape and adapter assumptions.

A quoted template string is not a quoted SQL literal

The macro receives a trusted SQL expression as a Jinja string and emits a known-value flag. Calling known_flag('returned_at') passes the text returned_at, which becomes an identifier expression in generated SQL. Calling it without Jinja quotes instead looks for a template variable. Neither form is parameter binding for untrusted user input. Arbitrary text inserted into generated SQL needs a reviewed allowlist and the correct dialect-aware quoting or literal handling. The example deliberately accepts only developer-controlled expressions and does not claim SQL injection protection for a general query builder.

jinja Illustrative technical example; logical results are reasoned, not engine-tested.

{% macro known_flag(column_expression) %}
case when {{ column_expression }} is null then 0 else 1 end
{% endmacro %}

select {{ known_flag('returned_at') }} as has_return
from {{ ref('stg_loans') }}

Conceptually emits CASE WHEN returned_at IS NULL THEN0 ELSE1 END. The macro and model would live in their appropriate project files; no Jinja or SQL engine runs here.

Parse-time availability differs from execution-time availability

During v1 project parsing, dbt discovers resources and graph relationships without query-result objects from execution. The execute context flag helps guard logic that needs a database result. When execute is true, a macro using run_query can contact the warehouse even during a compile-oriented workflow. Therefore “I only compiled” is not a universal offline guarantee. Keep guards and fallbacks explicit, and do not make business results depend on a parse fallback value. The fragment returns0 only as a parse-time fallback within the macro’s control contract, not a measured relation count.

jinja Illustrative technical example; logical results are reasoned, not engine-tested.

{% macro observed_row_count(relation) %}
  {% if not execute %}
    {{ return(0) }}
  {% endif %}
  {% set result = run_query('select count(*) as n from ' ~ relation) %}
  {{ return(result.columns[0].values()[0]) }}
{% endmacro %}

Illustrative introspective macro only. relation must be a trusted resolved Relation; execute-time use can issue SQL and is not performed.

Keep required references visible to parsing

A ref hidden inside a branch that is skipped during parsing can become an unexpected runtime dependency. Use documented dependency-declaration patterns when the graph cannot otherwise see a required relation. SQL comments can still contain rendered Jinja, while Jinja comments are removed and cannot serve the same purpose. File contexts also differ: v1 YAML properties do not support every custom macro that a SQL model supports, and CSV seed data is not a Jinja template. Check the official file-support table for the named generation instead of copying a v2-only context feature into v1.

Review the source and the generated statement together

Check balanced delimiters, matching block endings, argument types and the generated SQL’s commas, aliases and types. A textual delimiter check is weaker than dbt parsing, and dbt parsing is weaker than target execution and data assertions. Macro tests should cover absent values, empty lists and adapter branches relevant to the supported project. Avoid logging secrets or full source payloads while debugging. This course uses local string and graph reasoning only; it imports no Jinja or dbt package, invokes no run_query and reports no macro compilation or warehouse result.

Common errors and fixes

A column name becomes an undefined template variable

Why it happens: A string argument was passed without Jinja quotes.
How to fix it: Pass the intended developer-controlled string and inspect generated SQL.
Prevent it next time: Document whether macro arguments are values, identifiers or SQL expressions.

A macro dereferences a query result during parsing

Why it happens: Execution-only objects were assumed available in every phase.
How to fix it: Guard execution-dependent logic and define a safe parse contract.
Prevent it next time: Test phase assumptions separately from database behavior.

A required dependency appears only at runtime

Why it happens: Conditional templating hid ref from dependency discovery.
How to fix it: Use the supported explicit dependency pattern and review graph edges.
Prevent it next time: Include conditional macro paths in project review.

Check your understanding

1. Does known_flag("returned_at") bind a SQL value safely?

Show worked solution

No. It supplies template text used as a SQL expression. The example is restricted to trusted developer-authored expressions. Distinguish Jinja strings, database identifiers and SQL literals, and use reviewed target-aware handling if a broader generator is required.

2. Why is a parse-time return0 not evidence that a relation is empty?