> 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/built-in-macros.md). # Built-in macros Vulcan's macro system ships with operators that add dynamic behavior to your models. Because Vulcan macros understand SQL (they parse your query with [sqlglot](https://github.com/tobymao/sqlglot) and modify its semantic representation), these operators can generate whole clauses, iterate over lists, and reshape column sets while still producing valid SQL for your dialect. Reference an operator with the `@` prefix. This page groups the operators into three families and gives short examples for the most important ones. {% hint style="info" %} Vulcan macro operators do not accept named arguments in the Python sense. To skip an optional argument, pass later arguments by name with the `:=` keyword operator, for example `@STAR(foo, exclude := [c])`. {% endhint %} ## SQL clause operators Seven operators map to SQL clauses. Each takes a single argument evaluated by SQLGlot's SQL executor: if it is `TRUE`, the clause is generated; if `FALSE`, it is omitted. Each operator may be used only once per query (a CTE or subquery may have its own use). | Operator | Clause it generates | | ----------- | -------------------------------------------------------------------------- | | `@WITH` | common table expression `WITH` clause | | `@JOIN` | table `JOIN` clause (recognizes `INNER`, `LEFT OUTER`, `CROSS`, and so on) | | `@WHERE` | filtering `WHERE` clause | | `@GROUP_BY` | grouping `GROUP BY` clause | | `@HAVING` | group filtering `HAVING` clause | | `@ORDER_BY` | ordering `ORDER BY` clause | | `@LIMIT` | `LIMIT` clause | A single query combining several clause operators: ```sql @WITH(TRUE) all_cities AS (SELECT * FROM city) SELECT city_id, count(city_pop) AS population FROM all_cities LEFT OUTER @JOIN(TRUE) country ON all_cities.country = country.name @WHERE(TRUE) city_name = 'Toronto' @GROUP_BY(TRUE) city_id @HAVING(TRUE) population > 1000 @ORDER_BY(TRUE) population @LIMIT(TRUE) 10 ``` The argument is usually not hard-coded. Write it as a condition, optionally referencing macro variables, for example `@WHERE(@left_number < @right_number) item_id > @size`. ## Control-flow and iteration operators These operators bring loops, conditionals, and list processing to SQL. Conditions and anonymous functions are written in SQL and evaluated by SQLGlot, supporting equality (`=`, `!=`, `<>`), comparison (`<`, `>`, `<=`, `>=`), `BETWEEN`, and `IN`. | Operator | What it does | | --------- | --------------------------------------------------------------------------------------- | | `@EACH` | a `for` loop: applies an anonymous function to each item of a list | | `@IF` | includes SQL when a condition is `TRUE`, with an optional `FALSE` branch | | `@EVAL` | evaluates an expression (math or otherwise) with the SQL executor | | `@FILTER` | subsets an array to items matching a condition; feed the result to `@EACH` or `@REDUCE` | | `@REDUCE` | combines array items with a two-argument function `(x, y) -> ...` | | `@AND` | joins operands with `AND`, dropping `NULL` expressions | | `@OR` | joins operands with `OR`, dropping `NULL` expressions | `@EACH` takes a list and an anonymous function. In a `SELECT` clause it prints each result and separates them with commas. Embed the item in an identifier with `@x` (or `@{x}` mid-name): ```sql SELECT @EACH(['4','5','6'], x -> CASE WHEN favorite_number = x THEN 1 ELSE 0 END AS column_@x) FROM table ``` renders to: ```sql SELECT CASE WHEN favorite_number = '4' THEN 1 ELSE 0 END AS column_4, CASE WHEN favorite_number = '5' THEN 1 ELSE 0 END AS column_5, CASE WHEN favorite_number = '6' THEN 1 ELSE 0 END AS column_6 FROM table ``` `@IF` takes a condition, a value if `TRUE`, and an optional value if `FALSE`. With no `FALSE` branch and a false condition, it contributes nothing: ```sql SELECT col1, @IF(1 > 2, sensitive_col, nonsensitive_col) FROM table ``` Because `1 > 2` is `FALSE`, this selects `col1` and `nonsensitive_col`. `@IF` can also gate a pre/post-statement; the `@IF(...)` itself ends with a semicolon while the inner statement does not. `@EVAL` evaluates an arithmetic or other SQL expression. After macro variable substitution, the expression is computed and the result is inlined: ```sql @DEF(x, 1); SELECT @EVAL(5 + @x) -- SELECT 6 ``` `@FILTER` subsets an array to items that satisfy a condition. The result can be piped into `@EACH` or `@REDUCE`: ```sql @FILTER([1,2,3], x -> x > 1) -- [2,3] ``` `@REDUCE` combines array items step by step using a two-argument anonymous function. Each step takes the accumulated value as `x` and the next array item as `y`. Use it together with `@EACH` to build compound `WHERE` clauses: ```sql WHERE @REDUCE( @EACH([col1, col2, col3], x -> x = 1), -- builds each predicate (x, y) -> x AND y -- combines predicates with AND ) ``` ## Transformation and utility operators These operators generate column sets and common SQL patterns from your table's known columns and types. | Operator | What it does | | ------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `@STAR` | generates column selections and aliases (with optional `exclude`, `prefix`, `suffix`), casting to source types | | `@GENERATE_SURROGATE_KEY` | hashes concatenated, null-safe columns into a surrogate key (default `MD5`); optional `hash_function` argument overrides the default (e.g. `hash_function := 'SHA256'`) | | `@SAFE_ADD` | adds operands treating `NULL` as `0`; `NULL` only if all are `NULL` | | `@SAFE_SUB` | subtracts operands treating `NULL` as `0`; `NULL` only if all are `NULL` | | `@SAFE_DIV` | divides, returning `NULL` when the denominator is `0` | | `@UNION` | unions matching columns across tables (`'ALL'` default when union type is omitted, or `'DISTINCT'`); when the first argument is a boolean condition (`TRUE`/`FALSE`), the union runs normally on `TRUE` and returns only the first table on `FALSE` | | `@HAVERSINE_DISTANCE` | great-circle distance between two lat/lon points (`'mi'` default, or `'km'`) | | `@PIVOT` | pivots a column into one column per value (long to wide) | | `@DEDUPLICATE` | keeps one row per partition using a `ROW_NUMBER()` window | | `@DATE_SPINE` | builds a date series between two dates, inclusive of both ends | | `@RESOLVE_TEMPLATE` | renders physical-name components (`@{catalog_name}`, `@{schema_name}`, `@{table_name}`) into a literal or table node | `@STAR` selects and aliases columns, casting each to its source type. It takes `relation`, optional `alias`, `exclude`, `prefix`, `suffix`, and `quote_identifiers` (default `true`). `quote_identifiers` controls whether column names in the output are quoted. The `except_` argument is a deprecated alias for `exclude`; use `exclude` instead. ```sql SELECT @STAR(foo, bar, [c], 'baz_', '_qux') FROM foo AS bar -- CAST("bar"."a" AS TEXT) AS "baz_a_qux", ... (column c excluded) ``` `@PIVOT` turns distinct values into columns, aggregating with `SUM` by default: ```sql SELECT date_day, @PIVOT(status, ['cancelled', 'completed']) FROM rides GROUP BY 1 -- SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS "'cancelled'", ... ``` `@DATE_SPINE` builds a `datepart` series (`day`, `week`, `month`, `quarter`, `year`) between `start_date` and `end_date`, including both ends: ```sql WITH discount_promotion_dates AS ( @date_spine('day', '2024-01-01', '2024-01-16') ) SELECT * FROM discount_promotion_dates ``` `@DEDUPLICATE` keeps one row per partition, ordered as you specify. It renders as a `SELECT * FROM QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1` pattern: ```sql WITH raw_data AS ( @deduplicate(my_table, [id, cast(event_date as date)], ['event_date DESC', 'status ASC']) ) SELECT * FROM raw_data ``` renders to: ```sql WITH "raw_data" AS ( SELECT * FROM "my_table" AS "my_table" QUALIFY ROW_NUMBER() OVER (PARTITION BY "id", CAST("event_date" AS DATE) ORDER BY "event_date" DESC, "status" ASC) = 1 ) SELECT * FROM "raw_data" AS "raw_data" ``` `@RESOLVE_TEMPLATE` renders the physical-name components of the current model into a SQL literal or table node. Available placeholders are `@{catalog_name}`, `@{schema_name}`, and `@{table_name}`. The macro works only at the `creating` and `evaluating` runtime stages; at the `loading` stage it is a no-op. The optional `mode` argument (`'literal'` default, or `'table'`) controls the SQLGlot AST node type returned. ```sql MODEL ( name datalake.landing.customers, ... physical_properties ( location = @resolve_template('s3://warehouse-data/@{catalog_name}/prod/@{schema_name}/@{table_name}') ) ); -- WITH (location = 's3://warehouse-data/datalake/prod/vulcan__landing/landing__customers__2517971505') ``` {% hint style="info" %} `@DATE_SPINE` renders as DuckDB SQL and is transpiled to your dialect. Redshift, MySQL, and MSSQL use recursive CTEs; on MSSQL the recursion limit is about 100, so add `OPTION (MAXRECURSION 0)` for long ranges. {% endhint %} ## Typed macros When you write your own Python macro functions (in `.py` files under the `macros/` directory), every argument arrives as a SQLGlot `exp.Literal` that you must coerce by hand. Typed macros declare argument types with Python type hints, and Vulcan does the coercion for you, so the function body works with regular `str`, `int`, `list`, and so on. Why use them: * **Less boilerplate.** No manual conversion from `exp.Literal` to `str` or `int` in every macro. * **Errors caught earlier.** A wrong argument type fails at parse time, pointing at the call site instead of deep inside the macro body. * **Better IDE support.** Autocomplete and inline docs show real parameter types instead of `Any`. Define one by adding type hints to the decorated function's parameters (after the required `evaluator`): ```python from vulcan import macro from sqlglot import exp @macro() def repeat_string(evaluator, text: str, count: int): return exp.Literal.string(text * count) ``` Supported types include `str`, `int`, `float`, `bool`, `datetime.datetime`, `datetime.date`, `SQL` (the argument's SQL string), and the composites `list[T]`, `tuple[T]`, and unions `T1 | T2 | ...`. SQLGlot expression types such as `exp.Table`, `exp.Column`, `exp.Literal`, and `exp.Identifier` are also valid hints, and in fact any SQLGlot expression can be a target. {% hint style="info" %} Typed macros coerce the **inputs** for you, but your code is still responsible for coercing the **output** to the type the query's semantic representation expects, for example wrapping a string in `exp.Literal.string(...)`. {% endhint %} Coercion is best-effort: passing a string literal lets Vulcan parse and coerce it to the hinted expression type. If coercion fails, Vulcan logs a warning and passes the original expression through rather than crashing. To enforce a type, add your own `assert` on the coerced value. ## Python macro functions ### Setup Three requirements before a Python macro is usable: 1. Create a `macros/` directory in the project root. 2. Add an empty `macros/__init__.py` (created automatically by `vulcan init`). 3. Import `from vulcan import macro` at the top of each macro file. Minimal macro definition: ```python from vulcan import macro @macro() def my_macro(evaluator, arg1: int) -> str: return f"({arg1})" ``` ### Inputs and outputs Arguments arrive as SQLGlot expressions unless the parameter carries a type annotation. With a type annotation, Vulcan coerces the value to the Python type before the function body runs; without one, the raw SQLGlot expression is passed through. Return a `str` (inserted as raw SQL after SQLGlot parsing) or a SQLGlot `expression` (inserted directly into the query's semantic representation). Returning a list of strings or expressions inserts them as a comma-separated list in the output position. ### Evaluator API The `evaluator` argument is always the first parameter of every macro function. It exposes the following: | Method / attribute | Description | | --------------------------------------- | ------------------------------------------------------------- | | `evaluator.locals` | Dict of all predefined and local (`@DEF`) variables in scope. | | `evaluator.var("name")` | Access a global variable. Optional second arg is the default. | | `evaluator.this_model` | The current model's table/view node (SQLGlot). | | `evaluator.this_model_fqn` | Fully-qualified name string of the current model. | | `evaluator.runtime_stage` | Current stage: `"evaluating"`, `"creating"`, or `"loading"`. | | `evaluator.columns_to_types(model_ref)` | Dict of column→type for a referenced model. | | `evaluator.get_snapshot()` | Current snapshot's interval info. | ### Keyword and variable-length arguments To skip an optional positional argument, name subsequent arguments with the `:=` operator at the call site: ```sql @my_macro(col := users.id) ``` Variable-length arguments use `*args` in the function signature. Combine with a type annotation to coerce all items: ```python @macro() def add_args(evaluator, *args: int): return sum(args) ``` ### `metadata_only=True` For macros used exclusively in pre/post-statements that should not trigger a backfill when added, edited, or removed, set `metadata_only=True` on the decorator: ```python @macro(metadata_only=True) def print_message(evaluator, message): print(message) ``` Vulcan still detects changes and creates new snapshots but does not treat the change as breaking. ## Related docs * [Macros](/references/dataos-resources/vulcan/core-concepts/macros.md): the two macro systems, predefined variables, and user-defined variables. * [Signals](/references/dataos-resources/vulcan/core-concepts/signals.md): readiness gates that use the same typed-argument pattern as macros. * [Model and kinds](/references/dataos-resources/vulcan/core-concepts/model-and-kinds.md): where macros and statements run. --- # 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/built-in-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.