> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sqlbuild.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Python Macros

> Reusable Python functions that generate SQL fragments at compile time.

Macros are Python functions that generate SQL fragments at compile time. Instead of Jinja templates, you write real Python - testable, debuggable, and composable with standard tooling.

For the full picture of how macros fit into SQLBuild's interpolation system, see [Interpolation](/concepts/interpolation).

## Defining macros

Create Python files under `macros/` in your project. Every public function in a macro file becomes a callable macro:

```python theme={null}
# macros/currency.py
def cents_to_dollars(column):
    """Convert a cents integer column to a dollars decimal."""
    return f"ROUND(CAST({column} AS DOUBLE) / 100, 2)"
```

Macros can accept any Python arguments (strings, numbers, lists, dicts, booleans) and must return a SQL string when called from SQL.

## Using macros in models

Call macros in model SQL using the `@macro_name(args)` syntax:

```sql theme={null}
MODEL (
  materialized table,
  tags [marts],
);

SELECT
  CAST(o.ordered_at AS DATE) AS revenue_date,
  COUNT(DISTINCT o.order_id) AS order_count,
  SUM(p.amount_cents) AS total_revenue_cents,
  @cents_to_dollars('SUM(p.amount_cents)') AS total_revenue_dollars
FROM __ref("stg_orders") o
INNER JOIN __ref("stg_payments") p ON o.order_id = p.order_id
GROUP BY CAST(o.ordered_at AS DATE)
```

At compile time, `@cents_to_dollars('SUM(p.amount_cents)')` expands to `ROUND(CAST(SUM(p.amount_cents) AS DOUBLE) / 100, 2)`.

## Using macros in tests

Because unit tests are written in SQL, they support macro calls. This is useful for reusable mock data generators:

```python theme={null}
# macros/test_helpers.py
def mock_orders(count=1):
    """Generate mock order rows."""
    rows = [
        f"SELECT {i} AS id, {i * 100} AS customer_id, 'completed' AS status"
        for i in range(1, count + 1)
    ]
    return " UNION ALL ".join(rows)
```

```sql theme={null}
TEST();

WITH
__source__raw__orders AS (
  @mock_orders(3)
),
__expected__stg_orders AS (
  SELECT 1 AS order_id, 100 AS customer_id, 'completed' AS status
  UNION ALL
  SELECT 2 AS order_id, 200 AS customer_id, 'completed' AS status
  UNION ALL
  SELECT 3 AS order_id, 300 AS customer_id, 'completed' AS status
)
SELECT 1
```

## Using macros in hooks

Macros are expanded inside `sql(...)` hook entries in `pre_hooks` and `post_hooks`:

```python theme={null}
# macros/permissions.py
def grant_target(target):
    return f"GRANT SELECT ON {target} TO analyst_role"
```

```sql theme={null}
MODEL (
  materialized table,
  post_hooks [sql('@grant_target(@@CTX:destination.qualified)')],
);

SELECT 1 AS id
```

Hook SQL is validated at compile time, so invalid hook SQL is caught before execution. SQL hooks also support `@@CTX:` context variables, `@@name` project variables, and `@@ENV:NAME` environment variables directly without needing a macro wrapper.

For hooks that need more than string interpolation, use `python(...)` hooks instead. See [Hooks](/concepts/models#hooks) for the full Python hook API.

## Macro context

When a macro function accepts a `ctx` parameter as its first argument, SQLBuild passes a `MacroContext` object with adapter and target information:

```python theme={null}
# macros/datetime.py
def timestamp_trunc(ctx, grain: str, expr: str) -> str:
    if ctx.adapter_name == "bigquery":
        return f"TIMESTAMP_TRUNC({expr}, {grain.upper()})"
    return f"DATE_TRUNC('{grain}', {expr})"
```

The macro context provides:

| Field                  | Description                                                                                                |
| ---------------------- | ---------------------------------------------------------------------------------------------------------- |
| `adapter_name`         | The active adapter (e.g. `duckdb`, `snowflake`)                                                            |
| `sql_analysis_enabled` | Whether SQL analysis is enabled                                                                            |
| `target_name`          | The active target name, if any                                                                             |
| `vars`                 | Effective project variables as a dict (merged from project config, target, local config, and CLI `--vars`) |

```python theme={null}
def schema_qualified(ctx, table: str) -> str:
    schema = ctx.vars.get("schema_prefix", "public")
    return f"{schema}.{table}"
```

## Macro arguments

Macro arguments use Python literal syntax. Supported types:

* **Strings:** `'value'` or `"value"`
* **Numbers:** `42`, `3.14`, `-1`
* **Booleans:** `True`, `False`
* **Lists:** `[1, 2, 3]`
* **Dicts:** `{'key': 'value'}`
* **None:** `None`
* **Nested macro calls:** `@other_macro('arg')`

Keyword arguments are supported:

```sql theme={null}
@mock_orders(count=5, status='completed')
```

### Nested macro calls in arguments

Macros can be passed as arguments to other macros. The inner macro evaluates first and its result becomes an argument to the outer macro:

```sql theme={null}
@format_column('revenue', @cents_to_dollars('SUM(amount_cents)'))
```

You can mix regular arguments with nested macro calls:

```sql theme={null}
@wrap_with_alias(@cents_to_dollars('total_cents'), 'total_dollars')
```

Inner macros used as arguments don't have to return strings - they can return any Python object that the outer macro accepts.

## Composing macros

Macro output cannot contain macro calls. Expansion is single-pass: if a macro returns SQL containing `@another_macro()`, SQLBuild raises an error. If you need composition, compose in Python:

```python theme={null}
# macros/reporting.py
from macros.currency import cents_to_dollars

def revenue_column(column, alias):
    dollars_expr = cents_to_dollars(column)
    return f"{dollars_expr} AS {alias}"
```

## Where macros are allowed

* **Model query SQL** - the SELECT statement after the MODEL() header
* **Hook strings** - `sql(...)` entries in `pre_hooks` and `post_hooks` in MODEL() config
* **Test SQL** - unit test CTE bodies
* **Audit SQL** - singular audit queries

Macros are **not allowed** in MODEL() config values (other than SQL hook entries). If a config field contains `@macro()`, SQLBuild raises a compile error.

## Discovery rules

* SQLBuild discovers all `.py` files under `macros/` recursively
* Every public function (not starting with `_`) becomes a macro
* Macro names must be unique across all macro files - duplicates raise a compile error
* Macros are loaded once at compile time, not per-model