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.
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 forifstatements,forloops, 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 theCASE 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 WHENline.
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?