Python Macros

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.

Defining macros

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

# 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:

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:

# 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)

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:

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

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 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:

# 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`)

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:

@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:

@format_column('revenue', @cents_to_dollars('SUM(amount_cents)'))

You can mix regular arguments with nested macro calls:

@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:

# 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

Getting Started

dbt Compatibility

Concepts

Python Nodes

Virtual Environments (Alpha)

Integrations

CLI Reference

Defining macros

Using macros in models

Using macros in tests

Using macros in hooks

Macro context

Macro arguments

Nested macro calls in arguments

Composing macros

Where macros are allowed

Discovery rules

​Defining macros

​Using macros in models

​Using macros in tests

​Using macros in hooks

​Macro context

​Macro arguments

​Nested macro calls in arguments

​Composing macros

​Where macros are allowed

​Discovery rules

Defining macros

Using macros in models

Using macros in tests

Using macros in hooks

Macro context

Macro arguments

Nested macro calls in arguments

Composing macros

Where macros are allowed

Discovery rules