Jinja¶

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

Jinja works differently than Vulcan's native macros. While 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() }}. If you're working with a dbt project using the Vulcan adapter, dbt-specific functions will 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: These get replaced with values in your rendered SQL. Use them for variables and function calls.
{%...%} - Statements: These control flow and logic. Use them for if statements, for loops, and setting variables.
{#...#} - Comments: These are stripped out and won't appear in your final SQL.

Since Jinja syntax isn't valid SQL, you need to wrap your Jinja queries in special blocks so Vulcan knows to process 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;:

MODEL (
  name vulcan_example.full_model
);

JINJA_STATEMENT_BEGIN;
{{ pre_hook() }}
JINJA_END;

JINJA_QUERY_BEGIN;
SELECT {{ 1 + 1 }};
JINJA_END;

JINJA_STATEMENT_BEGIN;
{{ post_hook() }}
JINJA_END;

Using Vulcan's predefined variables¶

You can use all of Vulcan's predefined macro variables in your Jinja code. Some give you information about the Vulcan project itself (like runtime_stage or this_model), while others are temporal (like start_ds and execution_date for incremental models).

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

JINJA_QUERY_BEGIN;

SELECT *
FROM table
WHERE time_column BETWEEN '{{ start_ds }}' and '{{ end_ds }}';

JINJA_END;

Notice the single quotes around the variable references? That's because start_ds and end_ds return string values. For numeric variables like start_epoch, you wouldn't need the quotes.

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

User-defined variables¶

Beyond the predefined variables, you can 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 can be used in any model. Learn more about setting them up in the Vulcan macros documentation.

Access them using the {{ var() }} function. Pass the variable name (in single quotes) as the first argument, and optionally a default value as the second:

JINJA_QUERY_BEGIN;

SELECT *
FROM table
WHERE int_variable = {{ var('int_var') }};

JINJA_END;

If the variable might not exist, provide a default:

JINJA_QUERY_BEGIN;

SELECT *
FROM table
WHERE some_value = {{ var('missing_var', 0) }};

JINJA_END;

If missing_var isn't defined, this will use 0 as the fallback value.

Gateway variables¶

Gateway variables work just like global variables, but they're defined in a specific gateway's configuration. They take precedence over global variables with the same name. Learn more in the Vulcan macros documentation.

Access them the same way as global variables using {{ var() }}.

Blueprint variables¶

Blueprint variables let you create model templates. They're defined in the MODEL block and can be used to generate multiple models from one template:

MODEL (
  name @customer.some_table,
  kind FULL,
  blueprints (
    (customer := customer1, field_a := x, field_b := y),
    (customer := customer2, field_a := z)
  )
);

JINJA_QUERY_BEGIN;
SELECT
  {{ blueprint_var('field_a') }}
  {{ blueprint_var('field_b', 'default_b') }} AS field_b
FROM {{ blueprint_var('customer') }}.some_source
JINJA_END;

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

Local variables¶

Define variables that are only available in the current model using {% set ... %}:

MODEL (
  name vulcan_example.full_model,
  kind FULL,
  cron '@daily',
  audits (assert_positive_order_ids),
);

JINJA_QUERY_BEGIN;

{% set my_col = 'num_orders' %} -- Jinja definition of variable `my_col`

SELECT
  item_id,
  count(distinct id) AS {{ my_col }}, -- Reference to Jinja variable {{ my_col }}
FROM
  vulcan_example.incremental_model
GROUP BY item_id

JINJA_END;

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

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

Control flow¶

Jinja gives you control flow operators to make your SQL dynamic.

For loops¶

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

Here's an example that creates indicator columns for different vehicle types:

JINJA_QUERY_BEGIN;

SELECT
  {% for vehicle_type in ['car', 'truck', 'bus'] %}
    CASE WHEN user_vehicle = '{{ vehicle_type }}' THEN 1 ELSE 0 END as vehicle_{{ vehicle_type }},
  {% endfor %}
FROM table

JINJA_END;

A few things to notice:

The values in the list are quoted: ['car', 'truck', 'bus']
When you use {{ vehicle_type }} in the CASE WHEN, you need quotes around it: '{{ vehicle_type }}'
When you use it in an identifier name like vehicle_{{ vehicle_type }}, no quotes needed
There's a trailing comma after the CASE WHEN line, Vulcan's semantic understanding will remove it automatically

This renders to:

SELECT
  CASE WHEN user_vehicle = 'car' THEN 1 ELSE 0 END AS vehicle_car,
  CASE WHEN user_vehicle = 'truck' THEN 1 ELSE 0 END AS vehicle_truck,
  CASE WHEN user_vehicle = 'bus' THEN 1 ELSE 0 END AS vehicle_bus
FROM table

It's usually better to define your lists separately:

JINJA_QUERY_BEGIN;

{% set vehicle_types = ['car', 'truck', 'bus'] %}

SELECT
  {% for vehicle_type in vehicle_types %}
    CASE WHEN user_vehicle = '{{ vehicle_type }}' THEN 1 ELSE 0 END as vehicle_{{ vehicle_type }},
  {% endfor %}
FROM table

JINJA_END;

Same result, but easier to maintain.

If statements¶

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

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

Here's an example that conditionally includes a testing column:

JINJA_QUERY_BEGIN;

{% set testing = True %}

SELECT
  normal_column,
  {% if testing %}
    testing_column
  {% endif %}
FROM table

JINJA_END;

Since testing is True, this renders to:

SELECT
  normal_column,
  testing_column
FROM table

User-defined macro functions¶

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

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

{% macro print_text() %}
text
{% endmacro %}

Call it in your model with {{ print_text() }}, and it gets replaced with text.

Functions can take arguments:

{% macro alias(expression, alias) %}
  {{ expression }} AS {{ alias }}
{% endmacro %}

Use it like this:

JINJA_QUERY_BEGIN;

SELECT
  item_id,
  {{ alias('item_id', 'item_id2')}}
FROM table

JINJA_END;

This renders to:

SELECT
  item_id,
  item_id AS item_id2
FROM table

Notice that even though you quoted the arguments in the function call, they're not quoted in the output. Vulcan's semantic understanding recognizes that item_id is a column name and handles it appropriately.

If you want to select a string literal instead of a column, use double quotes around the string in the function call:

JINJA_QUERY_BEGIN;

SELECT
  item_id,
  {{ alias("'item_id'", 'item_id2')}}
FROM table

JINJA_END;

This renders to:

SELECT
  item_id,
  'item_id' AS item_id2
FROM table

The double quotes tell Vulcan "this is a string literal, not a column name." You can also use '"item_id"' if you want double quotes in the output (useful for some SQL dialects).

Mixing macro systems¶

Vulcan supports both Jinja and Vulcan macros, but we strongly recommend picking one system per model. Mixing them can lead to confusing behavior or errors.

You can use predefined Vulcan macro variables in Jinja queries, but if you're passing them as arguments to a Jinja macro function, use the Jinja syntax {{ start_ds }} instead of the Vulcan @start_ds syntax. You may need to add quotes depending on what you're doing.