> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/dataos-resources/vulcan/core-concepts/macros.md). # Macros SQL is declarative: you describe what you want, not how to get it. That is clear, but SQL has no built-in variables or control flow, so queries cannot adapt to context. Macros fill that gap. Instead of hardcoding a date, use a variable that resolves at runtime. Instead of repeating logic, use a function that generates SQL. Vulcan supports two macro systems, and both can use the same predefined variables: * **Vulcan macros** are built specifically for SQL. They analyze your query, build a semantic representation, and modify it, so they can distinguish a column name from a string literal and understand query structure. * **Jinja macros** are the popular templating system. They do pure string substitution. Use Jinja if you already know it. ## Predefined variables Vulcan provides predefined variables, available in both systems. Most relate to time, since time-based logic is common in data models. Reference a variable with the `@` prefix (Vulcan) or inside curly braces (Jinja). Instead of editing a date every day: ```sql SELECT * FROM table WHERE my_date > '2023-01-01' ``` use a variable that resolves automatically: ```sql SELECT * FROM table WHERE my_date > @execution_ds ``` ### Temporal variables Time variables combine a prefix and a postfix, like `@start_ds` or `@execution_epoch`. All time-related variables use UTC. Prefixes name the period: `start` (beginning of this run's interval, inclusive), `end` (end of the interval, inclusive), and `execution` (the timestamp when execution started). Postfixes name the format: | Postfix | Format | | -------- | ----------------------------------------------- | | `dt` | datetime, becomes a SQL `TIMESTAMP` | | `dtntz` | datetime, becomes `TIMESTAMP WITHOUT TIME ZONE` | | `date` | date object, becomes a SQL `DATE` | | `ds` | date string `'YYYY-MM-DD'` | | `ts` | datetime string `'YYYY-MM-DD HH:MM:SS'` | | `tstz` | datetime string with timezone | | `hour` | integer 0-23 | | `epoch` | integer seconds since Unix epoch | | `millis` | integer milliseconds since Unix epoch | So you have `@start_ds`, `@end_ds`, `@execution_ds`, `@start_ts`, `@start_epoch`, and so on for each prefix and postfix combination. ### Runtime variables | Variable | What it gives you | | ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `@runtime_stage` | The current stage: `'loading'`, `'creating'`, `'evaluating'`, `'promoting'`, `'demoting'`, `'auditing'`, or `'testing'`. | | `@gateway` | The name of the current gateway. | | `@this_model` | The physical table the model's view selects from. In on-virtual-update statements, it is the qualified view name. Use it in [generic audits](/references/dataos-resources/vulcan/core-concepts/assertions.md#generic-parameterized-audits). | | `@model_kind_name` | The current model kind, such as `'FULL'` or `'INCREMENTAL_BY_TIME_RANGE'`. | In `before_all` and `after_all` hooks, `@this_env`, `@schemas`, and `@views` are also available. {% hint style="info" %} **Curly braces change the role.** `@variable` produces a value (a string literal); `@{variable}` produces a SQL identifier, with quotes. Use `@{variable}` to interpolate into an identifier name like `@{schema}_table`, and `@variable` for plain value substitution. {% endhint %} ### `@variable` vs `@{variable}` — value vs identifier | Syntax | Renders as | Use for | | -------- | ------------------------------- | -------------------------------------------------------------- | | `@var` | `'col'` — quoted string literal | Value comparisons, string data | | `@{var}` | `col` — unquoted SQL identifier | Schema names, table references, column names in `MODEL` blocks | ```sql -- @my_variable = 'col' SELECT @my_variable AS the_column; -- renders: SELECT 'col' AS the_column SELECT @{my_variable} AS the_column; -- renders: SELECT col AS the_column ``` ## Vulcan macros Vulcan macros understand SQL. Rather than swapping strings, they parse your query with a SQL dialect, classify each macro reference, substitute values, execute any macro functions, and modify the query's semantic representation. This lets them treat a value as a column reference or a string literal correctly, which matters when you embed a variable into an identifier in the `MODEL` block. ### User-defined variables Vulcan supports four kinds of user-defined variables. Global and gateway variables are defined in the project config and usable in any model; blueprint and local variables are defined in a specific model and scoped to it. The most specific definition wins (local overrides gateway, gateway overrides global). Define global variables under the `variables` key in `config.yaml`: ```yaml variables: warehouse_schema: analytics refresh_window_days: 7 ``` #### Global variable types Global variables support `int`, `float`, `bool`, `str`, and lists or dicts of those types: ```yaml variables: int_var: 1 float_var: 2.0 bool_var: true str_var: "cat" list_var: [1, 2, 3] dict_var: key1: 1 key2: 2 ``` Access them with `@VAR_NAME` or `@VAR('var_name', default)`: ```sql SELECT * FROM @{warehouse_schema}.orders WHERE order_date > CURRENT_DATE - @VAR('refresh_window_days', 7) ``` #### `@VAR()` with a default `@VAR('name', default)` provides a fallback when the variable might be absent from the config: ```sql WHERE env = @VAR('env', 'dev') -- uses 'dev' if env is not defined ``` #### Gateway variables Gateway variables are defined under a specific gateway's `variables` key and take precedence over root-level `variables` with the same name: ```yaml gateways: my_gateway: variables: warehouse_schema: analytics_prod ``` Access them the same way as global variables — `@warehouse_schema` or `@VAR('warehouse_schema')`. When the model runs against `my_gateway`, the gateway value wins. #### Blueprint variables Blueprint variables turn one model into a template, stamping out one physical model per blueprint entry. Use `@{variable}` in the model `name` and list entries in the `blueprints` property: ```sql MODEL ( name @{category}.metrics, kind FULL, blueprints ( (category := retail, threshold := 100), (category := wholesale, threshold := 500) ) ); SELECT order_id, @BLUEPRINT_VAR('threshold', 0) AS min_threshold FROM @{category}.orders ``` `@BLUEPRINT_VAR('name', default)` accesses a blueprint value inside the model body, with an optional fallback. #### Local variables (`@DEF`) `@DEF` defines a variable scoped to the current model. Three requirements: 1. The `MODEL` statement must end with `;`. 2. All `@DEF` calls must come after `MODEL` and before the SQL query. 3. Each `@DEF` call must end with `;`. ```sql MODEL ( name vulcan_example.full_model, kind FULL, ); -- (1) semi-colon required @DEF(size, 1); -- (2) after MODEL, (3) ends with ; SELECT item_id, count(distinct id) AS num_orders FROM vulcan_example.incremental_model WHERE item_size > @size GROUP BY item_id ``` #### Inline macro functions Define single-use lambda-style functions with `@DEF`. Single-arg form: ```sql @DEF(rank_to_int, x -> case when left(x,1)='A' then 1 when left(x,1)='B' then 2 else 3 end); SELECT id, @rank_to_int(cust_rank) AS rank_int FROM some.model ``` Multi-arg form uses parentheses around the argument list: ```sql @DEF(pythag, (x, y) -> sqrt(pow(x, 2) + pow(y, 2))); SELECT sideA, sideB, @pythag(sideA, sideB) AS sideC FROM some.triangle ``` Macro functions can call each other (nested): ```sql @DEF(area, r -> pi() * r * r); @DEF(container_volume, (r, h) -> @area(r) * h); SELECT container_id, @container_volume(cont_di / 2, cont_hi) AS volume FROM inventory ``` You can also write macro functions in Python (in the `macros/` directory) for logic that string substitution alone cannot express. See [Execution hooks](/references/dataos-resources/vulcan/configuration.md#execution-hooks) for a worked example that returns SQL from a Python macro. ### Built-in macro operators Vulcan ships built-in operators, each prefixed with `@`, that generate SQL dynamically: SQL-clause operators like `@WHERE` and `@JOIN`, control-flow operators like `@EACH` and `@IF`, and transformation operators like `@STAR`, `@PIVOT`, and `@DATE_SPINE`. See [Built-in macro operators](/references/dataos-resources/vulcan/core-concepts/built-in-macros.md) for the full reference and for writing typed Python macros. ## Jinja macros If you know Jinja from dbt or other tools, use it here. Jinja does pure string substitution: it does not build a semantic representation of your query. Jinja syntax is not valid SQL, so wrap Jinja in special blocks. Use `JINJA_QUERY_BEGIN; ...; JINJA_END;` for the model query and `JINJA_STATEMENT_BEGIN; ...; JINJA_END;` for pre and post-statements: ```sql MODEL ( name vulcan_example.full_model ); JINJA_QUERY_BEGIN; SELECT * FROM table WHERE time_column BETWEEN '{{ start_ds }}' AND '{{ end_ds }}'; JINJA_END; ``` Use `JINJA_STATEMENT_BEGIN` for pre/post-statements that need conditional logic: ```sql JINJA_STATEMENT_BEGIN; {% if runtime_stage == 'evaluating' %} ALTER TABLE vulcan_example.full_model ALTER item_id TYPE VARCHAR; {% endif %} JINJA_END; ``` Reference predefined variables by name inside curly braces. String variables like `start_ds` need single quotes; numeric variables like `start_epoch` do not. The `gateway` variable is a function call: `{{ gateway() }}`. Access user-defined global variables with `{{ var('name', default) }}`. {% hint style="info" %} 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; in native Vulcan projects, they do not. {% endhint %} ### Jinja control flow Jinja uses three brace types, distinguished by the character after the opening brace: `{{ ... }}` expressions render a value, `{% ... %}` statements run control flow, and `{# ... #}` comments are stripped from the output. Common statements: * `{% set my_col = 'num_orders' %}` defines a local variable, referenced later as `{{ my_col }}`. * `{% for x in items %} ... {% endfor %}` loops to generate repetitive SQL; Vulcan removes the trailing comma automatically. * `{% if cond %} ... {% endif %}` conditionally includes SQL when `cond` is `True`. * `{% macro name(args) %} ... {% endmacro %}` (in the `macros/` directory) defines a reusable function, called with `{{ name(args) }}`. {% hint style="info" %} **Define lists separately.** Declare a list with `{% set %}` before the loop rather than inline. This makes the list easier to maintain: ```sql {% set vehicle_types = ['car', 'truck', 'bus'] %} {% for vt in vehicle_types %} CASE WHEN user_vehicle = '{{ vt }}' THEN 1 ELSE 0 END AS vehicle_{{ vt }}, {% endfor %} ``` {% endhint %} {% hint style="info" %} **Quoting decides the role.** A quoted argument like `alias('item_id', ...)` is treated as a column. To emit a string literal, wrap it in double quotes inside the call: `"'item_id'"`. To emit a double-quoted identifier (for case-sensitive dialects), use `'"item_id"'`. {% endhint %} {% hint style="warning" %} **Pick one macro system per model.** Mixing Vulcan macros and Jinja in the same file can cause unexpected behavior. Choose one and use it consistently throughout the model. {% endhint %} ## Related docs * [Model and kinds](/references/dataos-resources/vulcan/core-concepts/model-and-kinds.md): where macros run, including statements. * [Assertions](/references/dataos-resources/vulcan/core-concepts/assertions.md): generic audits that use `@this_model`, `@column`, and parameters. * [Signals](/references/dataos-resources/vulcan/core-concepts/signals.md): readiness gates that use the same typed-argument pattern as macros. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://v2.dataos.info/references/dataos-resources/vulcan/core-concepts/macros.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.