For the complete documentation index, see llms.txt. Markdown versions of documentation pages are available by appending .md to page URLs.
Skip to content

Jinja

Use Jinja templating in Vulcan SQL models: predefined and user-defined variables, for loops and if statements, and user-defined macro functions.

Vulcan supports macros from the Jinja templating system. If you're already familiar with Jinja from dbt or other tools, use it here.

Jinja works differently than Vulcan's native macros. Vulcan macros understand the semantic structure of your SQL; Jinja macros are pure string substitution. They assemble SQL text by replacing placeholders without building a semantic representation of the query.

dbt compatibility

Vulcan supports the standard Jinja function library, but not dbt-specific functions like {{ ref() }}. In a dbt project using the Vulcan adapter, dbt-specific functions work there but not in native Vulcan projects.

The basics

Jinja uses curly braces {} to mark macro code. The second character after the opening brace tells Jinja what to do:

  • {{...}} expressions: replaced with values in your rendered SQL. Use them for variables and function calls.

  • {%...%} statements: control flow and logic. Use them for if statements, for loops, and setting variables.

  • {#...#} comments: stripped out and don't appear in your final SQL.

Jinja syntax isn't valid SQL, so wrap your Jinja queries in special blocks so Vulcan processes them. For queries, use JINJA_QUERY_BEGIN; ...; JINJA_END;:

MODEL (
  name vulcan_example.full_model
);

JINJA_QUERY_BEGIN;

SELECT {{ 1 + 1 }};

JINJA_END;

For pre/post-statements (code that runs before or after your query), use JINJA_STATEMENT_BEGIN; ...; JINJA_END;:

Using Vulcan's predefined variables

Use any of Vulcan's predefined macro variables in your Jinja code. Some give you information about the Vulcan project (like runtime_stage or this_model); others are temporal (like start_ds and execution_date for incremental models).

Access them by putting the variable name (unquoted) inside curly braces:

The single quotes around the variable references are needed because start_ds and end_ds return string values. Numeric variables like start_epoch don't need quotes.

One special case: the gateway variable is a function call, so it needs parentheses: {{ gateway() }} instead of {{ gateway }}.

User-defined variables

Beyond predefined variables, create your own. Vulcan supports global variables (defined in your project config) and local variables (defined in a specific model).

Global variables

Global variables are defined in your project configuration file and usable in any model. See the Vulcan macros documentation for setup.

Access them with the {{ var() }} function. Pass the variable name (in single quotes) as the first argument and an optional default value as the second:

If the variable might not exist, provide a default:

If missing_var isn't defined, Vulcan uses 0 as the fallback value.

Gateway variables

Gateway variables work like global variables but are defined in a specific gateway's configuration. They take precedence over global variables with the same name. See the Vulcan macros documentation.

Access them like global variables, with {{ var() }}.

Blueprint variables

Blueprint variables let you create model templates. Define them in the MODEL block to generate multiple models from one template:

Use {{ blueprint_var() }} to access them, with an optional default value like {{ var() }}.

Local variables

Define variables available only in the current model with {% set ... %}:

The {% set %} statement goes after the MODEL block and before your SQL query.

Jinja variables can be strings, numbers, or complex data structures like lists, tuples, or dictionaries. They support Python methods, so you can call .upper() on strings, iterate over lists, and more.

Control flow

Jinja provides control flow operators to make your SQL dynamic.

For loops

For loops iterate over collections to generate repetitive SQL. They start with {% for ... %} and end with {% endfor %}.

This example creates indicator columns for different vehicle types:

A few things to notice:

  • The values in the list are quoted: ['car', 'truck', 'bus'].

  • When {{ vehicle_type }} is used in the CASE WHEN, it needs quotes: '{{ vehicle_type }}'.

  • When used in an identifier name like vehicle_{{ vehicle_type }}, no quotes needed.

  • Vulcan's semantic understanding automatically removes the trailing comma after the CASE WHEN line.

This renders to:

Define your lists separately when possible:

Same result, but easier to maintain.

If statements

If statements conditionally include SQL based on a condition. They start with {% if ... %} and end with {% endif %}.

The condition must evaluate to True or False. True, 1 + 1 == 2, and 'a' in ['a', 'b'] all work.

This example conditionally includes a testing column:

Since testing is True, this renders to:

User-defined macro functions

Macro functions let you reuse code across multiple models. Define them in .sql files in your project's macros directory. Put multiple functions in one file or split them across files.

Define a function with {% macro %} and {% endmacro %}:

Call it in your model with {{ print_text() }}. Vulcan replaces it with text.

Functions can take arguments:

Use it:

This renders to:

Even though you quoted the arguments in the function call, they're not quoted in the output. Vulcan's semantic understanding recognizes item_id as a column name and handles it accordingly.

To select a string literal instead of a column, use double quotes around the string in the function call:

This renders to:

The double quotes tell Vulcan "this is a string literal, not a column name." Use '"item_id"' for double quotes in the output, useful for some SQL dialects.

Mixing macro systems

Vulcan supports both Jinja and Vulcan macros. Pick one system per model. Mixing them can cause confusing behavior or errors.

Use predefined Vulcan macro variables in Jinja queries, but when passing them as arguments to a Jinja macro function, use the Jinja syntax {{ start_ds }} instead of the Vulcan @start_ds syntax. Add quotes as needed.

Last updated

Was this helpful?