Skip to main content

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.

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.

​
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 pre_hook and post_hook strings: 
# macros/permissions.py
def grant_target(target):
    return f"GRANT SELECT ON {target} TO analyst_role"

MODEL (
  materialized table,
  post_hook ['@grant_target(${CTX:target.qualified})'],
);

SELECT 1 AS id
Hook SQL is validated at compile time using SQLGlot, so invalid hook SQL is caught before execution.

​
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}"

​
Compilation order

SQLBuild processes SQL in this order:
  1. Project variables (@name without parentheses) are substituted first
  2. Macro calls (@name(args)) are expanded second
  3. SQLGlot validation runs against the fully expanded SQL
  4. Context templates (${CTX:...}) in hooks and config are resolved during config compilation
This means:
  • Macros see already-substituted variable values in the SQL
  • Macros can emit ${CTX:...} references that will be resolved later
  • SQLGlot validates the final expanded SQL, catching syntax errors from both vars and macros

​
Where macros are allowed

  • Model query SQL - the SELECT statement after the MODEL() header
  • Hook strings - pre_hook and post_hook values in MODEL() config
  • Test SQL - unit test CTE bodies
  • Audit SQL - singular audit queries
Macros are not allowed in MODEL() config values (other than hooks). If a config field contains @macro(), SQLBuild raises a compile error.

​
Project variables

Project variables are simple string substitutions defined in sqlbuild_project.yml or per-environment. They use the @name syntax (without parentheses) and are distinct from macros: 
# sqlbuild_project.yml
vars:
  schema_prefix: analytics

environments:
  prod:
    vars:
      schema_prefix: prod_analytics

SELECT * FROM @schema_prefix.customers
Variables and macros share the @ namespace. If a variable name collides with a macro name, SQLBuild raises an error at compile time. Variables can also be set in sqlbuild_local.yml for developer-specific overrides.

​
Context templates

Hooks and config can reference context variables using ${CTX:...} templates:
TemplateValue
${CTX:target.qualified}Fully qualified target relation name
${CTX:target.schema}Target schema
${CTX:target.name}Target relation name
${CTX:model_name}Model name
${CTX:environment}Current environment name
${CTX:run_id}Current run ID
Context templates are resolved during config compilation, after macro expansion. Macros can emit ${CTX:...} references in hook strings that will be resolved at that stage.

​
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