Skip to main content
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:
FieldDescription
adapter_nameThe active adapter (e.g. duckdb, snowflake)
sql_analysis_enabledWhether SQL analysis is enabled
target_nameThe active target name, if any
varsEffective 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