> 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/build/stage-2-productize/define-models-and-logic/data-models/macros.md).

# Macros

Macros in Vulcan are Python functions that generate SQL fragments. You call them inside SQL models using the `@macro_name()` syntax, and Vulcan substitutes the call with the generated SQL before executing the query.

Use macros when the same SQL expression needs to appear in multiple models. A change to the macro updates all models that use it at once.

Vulcan supports two macro systems:

* **Vulcan Python macros**: the primary approach for custom business logic. Written in Python, called with `@macro_name()`.
* **Jinja macros**: the `{{ macro_name() }}` syntax, useful for template-style string substitution.

Both systems can also use Vulcan's pre-defined time variables.

***

## Pre-defined time variables

Vulcan injects these variables automatically when running incremental models. Use them in `WHERE` clauses to filter upstream data by the interval being processed.

| Variable        | Type                  | Description                                                              |
| --------------- | --------------------- | ------------------------------------------------------------------------ |
| `@start_ds`     | String (`YYYY-MM-DD`) | Start of the processing interval as a date string                        |
| `@end_ds`       | String (`YYYY-MM-DD`) | End of the processing interval as a date string                          |
| `@start_dt`     | ISO 8601 timestamp    | Start of the interval as a datetime                                      |
| `@end_dt`       | ISO 8601 timestamp    | End of the interval as a datetime                                        |
| `@execution_ds` | String                | Execution date string                                                    |
| `@execution_dt` | ISO 8601              | Execution datetime                                                       |
| `@this_model`   | String                | Fully-qualified name of the current model (for use in assertion queries) |

***

## Writing a Python macro

Macro files live in the `macros/` directory of your project. Each macro is a Python function decorated with `@macro()`.

`macros/orders_helpers.py` from `orders-analytics`:

```python
"""Reusable SQL macros for orders analytics models."""

from __future__ import annotations

from vulcan import SQL, macro


@macro()
def safe_ratio(evaluator, numerator: SQL, denominator: SQL, scale: int = 4) -> str:
    """Return a rounded ratio, defaulting to 0 when the denominator is zero."""
    return f"ROUND(COALESCE({numerator}::NUMERIC / NULLIF({denominator}, 0), 0), {scale})"


@macro()
def revenue_order_filter(evaluator, status_column: SQL) -> str:
    """Return the standard revenue-bearing order filter."""
    return f"{status_column} <> 'Cancelled'"
```

Two macros:

* `safe_ratio`: divides numerator by denominator, returns 0 instead of failing on division by zero, and rounds to the specified number of decimal places.
* `revenue_order_filter`: returns the SQL condition that excludes Cancelled orders. Every model that computes revenue uses this macro so the definition lives in one place.

***

## Calling a macro in a model

Call a macro inside your SELECT query using `@macro_name(arguments)`:

```sql
WHERE @revenue_order_filter(o.order_status)
```

Vulcan expands this to:

```sql
WHERE o.order_status <> 'Cancelled'
```

Multiple arguments:

```sql
@safe_ratio(om.total_revenue, om.total_orders, 2) AS avg_order_value
```

Vulcan expands this to:

```sql
ROUND(COALESCE(om.total_revenue::NUMERIC / NULLIF(om.total_orders, 0), 0), 2) AS avg_order_value
```

***

## How orders-analytics uses macros

Both macros appear in `models/silver/fct_daily_sales.sql` and `models/silver/dim_customer_profile.sql`:

```sql
FROM bronze.orders AS o
WHERE @revenue_order_filter(o.order_status)
```

```sql
@safe_ratio(COALESCE(sm.total_shipments, 0), om.total_orders, 4) AS shipment_rate
```

Without `@revenue_order_filter`, each model would need its own `WHERE order_status <> 'Cancelled'` clause. If the business rule changes (for example, to also exclude Returned orders), you update one macro and all models get the fix automatically.

Without `@safe_ratio`, each division would need an inline `COALESCE(... / NULLIF(..., 0), 0)` expression. Using a named macro makes the intent clearer and eliminates copy-paste errors.

***

## The `@this_model` variable

`@this_model` expands to the fully-qualified, versioned table name of the model being audited. Use it in assertion queries so the assertion always targets the correct physical table:

```sql
AUDIT (name rfm_score_consistency, blocking true);

SELECT
  customer_id,
  rfm_score,
  rfm_segment,
  'RFM score or segment is inconsistent' AS issue_type
FROM @this_model
WHERE recency_score NOT BETWEEN 1 AND 5
   OR frequency_score NOT BETWEEN 1 AND 5
   OR monetary_score NOT BETWEEN 1 AND 5;
```

For `INCREMENTAL_BY_TIME_RANGE` models, `@this_model` automatically adds a time range filter so the assertion only checks the processed interval, not the entire table.

***

## Using time macros in incremental models

`@start_ds`, `@end_ds`, `@start_dt`, and `@end_dt` are used in `INCREMENTAL_BY_TIME_RANGE` models to filter the query to only the interval being processed. `orders-analytics` uses `kind FULL` for all models, so these variables are not needed here. For the full reference on time variable usage with incremental models, see [Incremental by Time Range](/concepts/resources/vulcan/components/model/properties.md#incremental_by_time_range) in the Vulcan under Concepts.

***

## Jinja macros

Vulcan also supports Jinja-style macros using the `{{ macro_name(args) }}` syntax, useful for template-style string substitution. `orders-analytics` uses Python macros only. For the full Jinja macro reference, see [Jinja in the Vulcan](/concepts/resources/vulcan/components/advanced-features/macros/jinja.md) under Concepts.

***

## Tips

* Use `@revenue_order_filter()` or a similar named macro whenever the same filter condition appears in multiple models. If the rule changes, you update the macro once.
* Use `@safe_ratio()` whenever you divide two columns that might be zero or null. Division by zero will fail the model silently or produce incorrect results.
* Keep macros focused. A macro that generates 100 lines of SQL is hard to debug. Write clear SQL in the model itself and use macros only for short, reusable fragments.
* Store all macro files in `macros/` with a `__init__.py` file so they are loaded correctly.
* Use `@this_model` in assertions. Never reference the physical table name directly; Vulcan versions tables internally and the name may not be what you expect.

***


---

# 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/build/stage-2-productize/define-models-and-logic/data-models/macros.md?ask=<question>&goal=<endgoal>
```

`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.