SQL Macros

A SQL macro is a reusable piece of SQL with typed parameters. Unlike a Python macro, it holds no logic: its body is SQL, so the compiler checks it on its own, expands it in place, and formatting, rules and column lineage see through every call.

A SQL macro is one of three forms, decided by how its body starts:

Body starts with	Form	Called
anything else	expression	where an expression belongs, such as a select item or a WHERE clause
WITH	CTE list	between the CTEs of a WITH list
SELECT	column list	as an item of a select list

Bodies are read by SQLBuild’s native parser for the adapter’s SQL analysis dialect. On an adapter whose dialect has no native parser, including a custom adapter without sql_analysis_dialect_name, every call fails with P015, which says SQL macros are unavailable for that dialect.

Create a SQL macro

Put each SQL macro in its own .sql file under macros/sql/. The file name is the macro name. Python macros live next to it in macros/python/:

my_project/
├── macros/
│   ├── python/
│   │   └── labels.py
│   └── sql/
│       ├── cents_to_dollars.sql
│       ├── clean_orders.sql
│       └── audit_columns.sql
├── models/
└── sqlbuild_project.toml

A file directly under a macros/ folder fails with D013, which names the subfolder to move it to. Folder scopes use the same layout, such as models/marts/_sqlbuild/_macros/sql/.

Rules that bound declaration folders, such as SQBRDECLARATION304 (files per folder) and SQBRDECLARATION306 (shared filename prefixes), count python/ and sql/ as separate folders. To split a full sql/ folder, move files into subfolders such as macros/sql/text/: a SQL macro is named by its file, so it keeps its name and its callers stay unchanged.

A SQL macro file starts with a MACRO header, then the body:

-- macros/sql/cents_to_dollars.sql
MACRO (
  description "Cents to dollars",
  arguments (amount_cents BIGINT, places INT CONST DEFAULT 2),
  returns DECIMAL(18, 2),
);

CAST(ROUND(@amount_cents / 100, @places) AS DECIMAL(18, 2))

returns is optional for expression macros and not allowed for the other forms. See Result types for what it does.

The description follows the same rules as every header string: write text that contains double quotes as """...""", and a wrapped description is stored folded into one flowing text (see Header strings). Quotes in the macro body never change how the header is read.

Call a SQL macro

Every macro call says its kind: @sql.name(...) calls a SQL macro and @py.name(...) a Python macro. A bare @name(...) fails with P014, which names both spellings.

Arguments are SQL, positional or named with name := value:

SELECT
  o.order_id,
  @sql.cents_to_dollars(o.fee_cents + o.tax_cents, places := 0) AS fees
FROM __ref("stg_orders") AS o

compiles to:

SELECT
  o.order_id,
  CAST(ROUND((o.fee_cents + o.tax_cents) / 100, 0) AS DECIMAL(18, 2)) AS fees
FROM analytics.stg_orders AS o

An argument is parenthesised where the body needs it, and so is an expression macro’s result where the call stands, such as before AT TIME ZONE or FILTER. Arguments resolve in the caller’s scope, so a model can pass its own private constant; the macro receives only the value.

CTE lists

A CTE-list macro inserts its CTEs, with its own names, into the caller’s WITH list at the call:

-- macros/sql/clean_orders.sql
MACRO (
  description "Deduplicated, non-deleted orders",
  arguments (src RELATION (order_id BIGINT, updated_at TIMESTAMP, is_deleted BOOLEAN)),
);

WITH live AS (SELECT * FROM @src WHERE NOT is_deleted),
ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS row_num
  FROM live
),
latest AS (SELECT * EXCLUDE (row_num) FROM ranked WHERE row_num = 1)

WITH orders_raw AS (SELECT * FROM __ref("stg_orders")),
@sql.clean_orders(orders_raw)
final AS (SELECT order_id, amount FROM latest)
SELECT * FROM final

The compiled model holds orders_raw, live, ranked, latest and final, flat and in that order, and later CTEs may read any of the macro’s CTEs. Commas around the call are optional; sqb format normalises them.

The last CTE of a CTE-list macro is its result. To call one macro more than once in a model, name each call’s CTEs with the reserved __cte argument:

WITH eu_raw AS (SELECT * FROM __ref("stg_orders_eu")),
us_raw AS (SELECT * FROM __ref("stg_orders_us")),
@sql.clean_orders(eu_raw, __cte := eu_orders)
@sql.clean_orders(us_raw, __cte := us_orders)
final AS (SELECT * FROM eu_orders UNION ALL SELECT * FROM us_orders)
SELECT * FROM final

The macro’s last CTE takes exactly the __cte name, and its other CTEs become <name>__<cte>, so this model holds eu_raw, us_raw, eu_orders__live, eu_orders__ranked, eu_orders, us_orders__live, us_orders__ranked, us_orders and final. References inside the body are renamed with them, wherever a name reads the macro’s CTE: a table alias such as FROM live AS l, or a nested WITH that defines its own live, keeps its own meaning. Arguments are never renamed, even one named like a macro CTE. Later CTEs may read the prefixed ones too.

__cte takes a bare name, is only for CTE-list calls (P017 otherwise), and is not declared in arguments. A call without it keeps the macro’s own names.

Column lists

A column-list macro inserts its columns into the caller’s select list. Every column needs a name:

-- macros/sql/audit_columns.sql
MACRO (
  description "Standard audit columns",
  arguments (src RELATION (created_at TIMESTAMP, updated_at TIMESTAMP)),
);

SELECT @src.created_at, @src.updated_at, @src.updated_at > @src.created_at AS was_updated

SELECT o.order_id, @sql.audit_columns(o) FROM orders AS o compiles to SELECT o.order_id, o.created_at, o.updated_at, o.updated_at > o.created_at AS was_updated FROM orders AS o.

Use a column list when the same ordered set of columns repeats, for example in every branch of a UNION ALL. The call takes no alias (P021): each inserted column is named in the macro.

A CTE or column name a macro inserts that the caller already defines fails with P018, which names both. Calling the same CTE-list macro twice without __cte fails the same way, and the error suggests __cte.

Parameter types

Every parameter has a type, and may have a DEFAULT:

Type	Accepts
BIGINT, VARCHAR, TIMESTAMP, …	Any SQL expression of a compatible type, evaluated per row: a column, arithmetic, a function call, a literal or a constant.
ANY	Any scalar SQL expression, whatever its type: a column, a literal, an expression or a scalar constant. Its type is not checked. Lists and relations fail with P017.
ANY NUMERIC, ANY TEXT, ANY TEMPORAL	Like ANY, but only values of one family: integer, decimal and float types; string types; or date, time and timestamp types.
<type> CONST	Only a value known at compile time: a literal, a scalar constant, an enum value, or a CONST parameter of a calling macro. A column or expression fails with P017. Scalar types only.
LIST <type>, SET <type>	An inline list or set, or a list or set constant. Always compile-time, so they take no CONST. They render like constants.
ENUM <name>	A member of that enum, such as @enum("order_status").OPEN.
RELATION (<slots>)	A CTE name, a table alias, or __ref(), __source() or __seed(), with the columns its slots read.

SQL types are written as in schema declarations and checked against the adapter’s SQL dialect the same way, including the dialect’s array types such as VARCHAR[]. A runtime array value uses the array type; LIST VARCHAR is a list known at compile time.

Use ANY when the body works for values of any type, typically because it casts the argument itself, as in CAST(@order_ref AS VARCHAR), so callers can pass a number or a string. The body is still checked, and the argument’s type counts as unknown there. ANY is not VARIANT: a VARIANT parameter says the macro takes semi-structured data, while ANY says it takes a plain value of any type. ANY takes a DEFAULT but not CONST, which needs a type to check values against, and it cannot be a LIST or SET element type.

Choosing an argument type

• A fixed type, such as BIGINT, when the body needs that type. Callers may pass any compatible value, and the body is checked with that type.
• A family, such as ANY NUMERIC, when the body works for every type of the family, such as ROUND(@value, @places). A call whose argument has a known type outside the family fails with P017 at the argument. The body is checked as if the argument had the family’s widest type (DOUBLE, VARCHAR or TIMESTAMP), so UPPER(@value) on ANY NUMERIC fails with P025 at the macro file. Pair a family with returns TYPE OF value to return the caller’s own type.
• ANY when the body works for values of any type, typically because it casts the argument itself.

Families take a DEFAULT but not CONST, and are not LIST or SET element types.

Leave CONST off unless the body needs a literal. CONST is part of the macro’s contract: callers can no longer pass a column.

Parameters in the body

Write every parameter as @name in the body, as generic audits do. A bare identifier is always plain SQL, a column, CTE or table, never the parameter:

-- macros/sql/is_status.sql
MACRO (
  description "Whether the status column matches",
  arguments (status VARCHAR),
  returns BOOLEAN,
);

status = @status

Here status is the caller’s column and @status the argument. A relation parameter is qualified directly, as @src.created_at or @src.*, or aliased, as FROM @src AS s and then s.created_at.

These fail with P022:

• an @name that names no parameter; the error lists the parameters
• a relation parameter written bare where it would read a table, such as FROM src or src.created_at; the error says src is a parameter of macro clean_orders(); write @src

Parameters may not be named sql, py, const, enum, param or __cte.

Positions that need a literal

SQL needs a literal in some positions. A parameter without CONST there fails with P023 when the macro is checked:

error[P023]: places is used where a literal is required; declare it places INT CONST

Position	Adapters
LIMIT, OFFSET, TOP and FETCH FIRST counts	all
INTERVAL and EXTRACT units	all
PIVOT ... IN (...) values	all
DATE_TRUNC unit (the first argument, or the last on BigQuery)	all
ROUND precision	DuckDB and MotherDuck (for DECIMAL values), Databricks

The positions every adapter shares are checked for every macro file. DATE_TRUNC and ROUND are checked for the project’s adapter at each call.

Relation slots

Everything a macro reads from a relation is declared in its RELATION type as a slot. A slot is either one typed column or a COLUMNS group of columns the caller chooses, so one macro works at any grain:

-- macros/sql/weekly_rates.sql
MACRO (
  description "Weekly refund rate per grain",
  arguments (weekly RELATION (keys COLUMNS, order_count BIGINT, refund_count BIGINT)),
);

WITH counted AS (
  SELECT
    s.@weekly.keys,
    s.@weekly.order_count AS orders,
    s.@weekly.refund_count AS refunds
  FROM @weekly AS s
),

rated AS (
  SELECT
    @weekly.keys,
    SUM(refunds) / NULLIF(SUM(orders), 0) AS refund_rate
  FROM counted
  GROUP BY @weekly.keys
)

A COLUMNS slot is untyped: each column passed to it keeps its own type, so the expanded SQL is fully typed.

Reading slots

• alias.@weekly.order_count reads the slot from a table alias or CTE, such as s above. For a group, the prefix applies to every column: s.@weekly.keys becomes s.product_id, s.region.
• A plain read of a group, @weekly.keys, always stands as the bare column names, product_id, region. So later CTEs, such as rated above, read the group from whatever relation they select from, without an alias. A bare name that two joined relations share fails as hand-written SQL does, with B003; read it as alias.@weekly.keys there.
• A plain read of a single-column slot always reads through the passed relation, mapped or not: @weekly.refund_count becomes weekly.refund_count, or weekly.refunds_n with weekly.refund_count := refunds_n. So the same body expands the same way for every mapping.
• Every read inside USING (...) stands unqualified, as USING needs, including USING (@weekly.week) for a column the type does not declare.
• In a column-list macro, o.@weekly.keys outputs the group’s columns under their own names.
• Rules judge a read as you wrote it. SQLCLEAN028, which rejects a query mixing qualified and unqualified columns, judges a query the macro writes whole, such as a CTE of a CTE-list macro, once, in the macro file. There every plain read counts as unqualified and every alias.@weekly... read as qualified. A step written only in plain reads, such as SELECT @weekly.keys, @weekly.refund_count AS refunds FROM @weekly, passes; to qualify, read every slot through an alias.
• Where a call’s output joins the caller’s query, as with expression and column-list macros, the query is judged at the call. A read through the relation the call passes, such as o.created_at above, fits either style; any other column counts as the macro writes it, so a bare status beside a hand-qualified o.order_id fails.

A plain group read inside a subquery, such as EXISTS (SELECT @weekly.keys FROM refunds AS r ...), could bind its bare columns to the subquery’s relations instead of the one the slot reads. The call fails with P017 when a relation of an enclosing subquery, other than the relation passed for the slot, has one of the group’s columns, or when its columns are not known. LATERAL subqueries in FROM or JOIN are checked the same way:

error[P017]: slot weekly.keys column product_id would bind to refunds in this subquery; write alias.@weekly.keys

Read the group through its alias there. A group read in a CTE, or in a derived table in FROM, reads that query’s own relations and is not checked.

alias.@weekly.name naming no slot, or alias.@weekly naming none at all, fails with P022. @weekly.name without an alias may still read a column the type does not declare, through the passed relation, as before.

Mapping slots at the call

A call maps slots with relation.slot := ..., next to its other named arguments:

WITH weekly AS (SELECT * FROM __ref("weekly_orders")),
@sql.weekly_rates(weekly, weekly.keys := (product_id), weekly.order_count := orders_placed, __cte := by_product)
@sql.weekly_rates(weekly, weekly.keys := (product_id, region), weekly.order_count := orders_placed, __cte := by_product_region)
final AS (SELECT * FROM by_product_region)
SELECT * FROM final

The first call compiles to the same SQL as a macro written for product_id, the second groups by product and region. The left of := is always a parameter path, and the right side is column names of the passed relation:

• A single-column slot takes one column name. An unmapped single-column slot reads the column of its own name, so refund_count above reads refund_count.
• A group takes a parenthesised list of column names, such as (product_id, region), and every call must map it.
• A slot reads a column as it is: compute an expression in the relation you pass.
• A macro that passes its own relation on maps the inner slots to its slot names, as in @sql.weekly_rates(weekly := @src, weekly.keys := (dims), weekly.order_count := placed), or to columns of its relation. Each inner slot keeps its kind: a group mapped to columns is still read bare, or after its alias, and mapping a group to a single-column slot of the outer relation, or the reverse, fails with P017.

Rules for groups

1. A group is pasted verbatim, comma-separated. It takes no per-item expression, indexing, counting or renaming, and groups are never combined.
2. A group stands only as a whole item of a select list, PARTITION BY, GROUP BY, ORDER BY (without ASC, DESC or NULLS) or USING (...). Anywhere else fails with P015 at the macro file.
3. Grouped columns keep their real names in the output.
4. A group is never empty: weekly.keys := () fails.
5. A relation may have several groups, such as keys COLUMNS, carried COLUMNS. Groups of one relation may not share a column, and a column that fills a single-column slot, mapped or by its own name, may not also be in a group.

Checks at the call

Slot errors fail with P017 and name the slot and the caller’s relation:

error[P017]: slot weekly.order_count needs a BIGINT column; weekly has no column orders_total

• Every mapped and grouped column must exist in the passed relation, and a single-column slot needs a compatible type: slot weekly.order_count needs a BIGINT column; weekly.region is VARCHAR, or slot weekly.keys groups column channel; weekly has no column channel. Like other argument checks, these run when the relation is a CTE or table alias; the model’s own column checks still run on the expanded SQL.
• Overlapping groups, or a grouped column that also fills a single-column slot, fail at the call.
• A grouped column named like a column the macro defines, or like a bare column such as s.week or a column read through the relation such as @weekly.region beside the group in the same select list, fails with P018, naming both: column refunds of slot weekly.keys clashes with column refunds that macro weekly_rates() defines.
• A mapped single-column slot read in a select list needs an AS in the macro, so output names don’t depend on the caller’s mapping. Groups are exempt, because they keep their real names, and so is an unmapped slot, which keeps its own name. So are reads in IN, EXISTS and scalar subqueries, whose column names never reach the macro’s output. A LATERAL subquery in FROM or JOIN is checked like a derived table, because its columns reach the output, as through l.*.

Compose macros

A SQL macro may call other SQL macros, and pass its own parameters on as @name:

-- macros/sql/net_amount.sql
MACRO (
  description "Net amount in dollars",
  arguments (gross_cents BIGINT, refund_cents BIGINT),
  returns DECIMAL(18, 2),
);

@sql.cents_to_dollars(@gross_cents - COALESCE(@refund_cents, 0))

Macros that call each other in a cycle fail with P019. SQL and Python macros never call each other (P020); compose them in the model by passing results, as in @py.bracket(@sql.cents_to_dollars(o.amount_cents)). A SQL macro and a Python macro may not share a visible name.

A body may not call __ref(), __source() or __seed() (P016): pass relations in as RELATION arguments. @const(...) and @enum(...) in a body resolve from the macro file’s folder, not from the calling model, so a macro means the same thing wherever it’s called. A constant or enum that isn’t visible from there fails with S026; to use a caller’s value, make it an argument and pass @const(...) at the call site. SQL macros a body calls resolve the same way, so a macro can call a folder-local macro beside it that its callers cannot see.

Checks

Each macro is checked on its own when the project compiles, and each call is checked against the caller’s column types, inferred like the rest of the model:

• a RELATION argument must have every declared column, each of a compatible type, and every column its slots are mapped to;
• a typed argument must have a compatible type, so @sql.cents_to_dollars(o.status) with a VARCHAR status fails.

These fail with P017, naming the argument, the expected type or column and what the caller has:

error[P017]: argument src of macro clean_orders() needs column is_deleted BOOLEAN; orders_raw has no column is_deleted
  --> models/marts/orders_clean.sql:2:1
  parameter declared here:
  --> macros/sql/clean_orders.sql:3:14
  = help: pass a relation with these columns: order_id BIGINT, updated_at TIMESTAMP, is_deleted BOOLEAN

An argument whose type the compiler cannot infer passes. So do calls inside a subquery or in a query part with UNION, INTERSECT or EXCEPT, arguments built from __ref(), macro calls or parameters of a calling macro, and calls in models with runtime placeholders; the model’s own column checks still run on the expanded SQL. All other errors also point at the call and at the macro file.

Result types

An expression macro call is typed wherever it stands, from its expanded SQL, as if the SQL were written there. returns declares the type for when that SQL’s type is unknown:

MACRO (description "Status bar", arguments (value VARCHAR), returns VARCHAR);

bar(LENGTH(@value), 0, 10)

• The declared type applies only where the expanded SQL’s type is unknown, including a list literal such as [], whose type analysis doesn’t infer. A known type stands, even a close one such as VARCHAR(100) for returns VARCHAR(5), and contract checks report a mismatch with K002.
• The type flows on as an inferred type would: through CTEs, SELECT *, subqueries, joins and each branch of a set operation, through operators and functions around the call, to contract checks (K002, K003), SQBRCONTRACT105 and models that read the column.
• returns is used only for analysis. It doesn’t appear in the generated SQL, so on its own it doesn’t change the column’s physical type. A body of 'ACTIVE' still creates VARCHAR(6) on Snowflake. For SQBRCONTRACT105, a select-list item that is one call needs no CAST at the call only when the macro body’s outermost expression is a CAST(... AS type) or ...::type to the column’s contract type, compared as the dialect does, such as CAST(LEFT(@value, 3) AS VARCHAR) for a VARCHAR column. Otherwise the rule asks for a cast in the macro or at the call, whether the macro declares a fixed returns or returns TYPE OF. A call inside a larger expression always needs an outer cast.
• Without returns, the call is typed by inference alone. When an output is one call whose type is unknown, K003 and SQBRCONTRACT105 suggest declaring returns on the macro.
• returns TYPE OF <argument> declares the type of that argument at each call, under the same rule: arguments (value ANY NUMERIC, places INT CONST DEFAULT 2), returns TYPE OF value. If the argument’s own type is unknown, so is the call’s. The argument must be a scalar argument of the macro (P015).
• A fixed returns is checked against the body at the macro file. The body is typed from the declared argument types; a known type of another family, such as UPPER(@value) with returns BIGINT, fails with P024. A body whose type cannot be inferred, for example because it reads ANY arguments, is a list literal, or calls a function whose type is unknown or only guessed, trusts returns.
• returns and every argument type must be a type the adapter’s SQL dialect accepts, including a decimal precision and scale it can hold, such as at most DECIMAL(38, s) on DuckDB (P015).
• A call where only a keyword can stand, such as the date part of EXTRACT, is typed by inference alone.
• A call inside a Python macro argument, as in @py.bracket(@sql.status_bar(o.status)), is typed by inference alone.
• returns never changes the compiled SQL.

A * REPLACE item takes its source column’s type rather than the call’s.

Tools

• sqb format formats SQL macro files, bodies of every form and the MACRO header, and normalises the commas around CTE-list and column-list calls.
• Rules and column lineage read the expanded SQL, so they see through every call. A Rule finding inside an expansion is reported at the call, marked (in SQL generated by @sql.name(...)). The model’s expanded SQL is in target/compiled/models/, or in target/compiled-failed/models/ when the Rules fail; see When Rules fail.
• Editing a macro recompiles its callers.

Test SQL macros with macro-mode unit tests, and replace one in a model test with a __macro__ mock.

See Macros for when to use a SQL macro, a Python macro, a SQL function or a generic audit.