Interpolation - SQLBuild

SQLBuild uses two syntax layers for dynamic content:

@ syntax is for any executable SQL - model queries, SQL hooks, tests, audits, and inline source expressions
${...} syntax is for config values - project TOML config, MODEL() header fields (excluding SQL hooks), and source/seed YAML declarations

The rule is simple: if it’s any SQL that will be executed, it uses @. If it’s a config value, it uses ${...}. These layers never mix.

Syntax reference

Syntax	Where	Resolved
`@macro(args)`	Model SQL, SQL hooks, tests, audits, inline source expressions	Compile time - expands to macro return value
`@@name`	Model SQL, SQL hooks, tests, audits, inline source expressions	Compile time - project variable substitution
`@@ENV:NAME`	Model SQL, SQL hooks, tests, audits, inline source expressions	Compile time - environment variable
`@@CTX:name`	SQL hooks only	Compile time - destination relation, target, run ID
`@@@name`	Model SQL	Preserved for runtime (custom materializations)
`@name` / `@'name'`	Generic audit SQL only	Audit engine parameter
`${CTX:...}`	TOML/YAML config values	Config compilation
`${ENV:...}`	TOML/YAML config values	Config compilation

@@CTX: is intentionally SQL-hook-only. Model SQL describes a relation’s data and should not reference its own destination identity. SQL hooks are the operational SQL layer where destination context is useful - grants, logging, post-materialization DDL. Python hooks access the same information through ctx.destination on the HookContext object (see Hooks).

Project variables

Project variables use @@name syntax in SQL and are defined in sqlbuild_project.toml or per-target:

# sqlbuild_project.toml
[vars]
schema_prefix = "analytics"

[targets.prod.vars]
schema_prefix = "prod_analytics"

SELECT * FROM @@schema_prefix.customers

Variables can also be set in sqlbuild_local.toml for developer-specific overrides, or passed via the CLI using --vars with a JSON object:

sqb build --vars '{"schema_prefix": "staging_analytics"}'

CLI vars take precedence over local and project config vars.

JSON vars and nested values

--vars accepts full JSON objects. Values can be strings, numbers, booleans, null, arrays, or nested objects:

sqb build --vars '{"schema_prefix": "staging", "grants": {"primary_role": "analyst"}, "enabled": true}'

In SQL interpolation (@@name), only top-level scalar values can be used directly. null renders as an empty string. If a variable resolves to an array or object, SQLBuild raises a clear error suggesting you use a macro instead. For nested or complex values, use ctx.vars in a macro:

def grant_role(ctx):
    return ctx.vars["grants"]["primary_role"]

The macro context receives the full native JSON structure including nested dicts, lists, booleans, numbers, and None.

Environment variables

Environment variables use @@ENV:NAME syntax to inject values from the shell environment:

SELECT *
FROM @@schema_prefix.customers
WHERE source_system = '@@ENV:SOURCE_SYSTEM'

If the environment variable is not set, SQLBuild raises a compile error.

Context variables

Context variables provide access to the current model’s destination relation, active target, and run metadata. In SQL hooks (@@CTX: syntax):

post_hooks [sql('GRANT SELECT ON @@CTX:destination.qualified TO analyst_role')],

In TOML/YAML config values (${CTX:...} syntax):

[targets.prod]
schema = "${CTX:destination.schema}"

Available context variables:

Variable	Value
`destination.qualified`	Fully qualified destination relation name
`destination.schema`	Destination schema
`destination.database`	Destination database
`destination.table`	Destination relation name
`model.name`	Model name
`model.database`	Model database
`model.schema`	Model schema
`model.alias`	Model alias
`run.target`	Active target name
`run.id`	Current run ID

In macros, the MacroContext object is passed as the first argument when a macro function accepts a ctx parameter:

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 adapter_name, sql_analysis_enabled, target_name, and vars.

Deferred placeholders

Custom materializations can define runtime placeholders using @@@name syntax. These are preserved through compilation and resolved by the materialization at execution time:

WHERE CAST(ordered_at AS DATE) >= CAST(@@@partition_start AS DATE)
  AND CAST(ordered_at AS DATE) < CAST(@@@partition_end AS DATE)

Audit parameters

Generic audit SQL uses @name (single @, no parentheses) for audit-engine placeholders. These are resolved by the audit engine, not the compiler:

SELECT @column
FROM @relation
WHERE @column IS NULL

This is distinct from @@name (project variables) and @macro() (macro calls), so there is no ambiguity. See Audits for details on generic audit parameters.

Compilation order

SQLBuild processes authored SQL in this order:

Config templates (${CTX:...}, ${ENV:...}) in TOML/YAML config values are resolved during config compilation
Project variables (@@name), environment variables (@@ENV:NAME), and context variables (@@CTX:name in SQL hooks) are substituted
Macro calls (@name(args)) are expanded
SQL analysis validation runs against the fully expanded SQL

This means:

Config templates resolve first, before any SQL processing
Macros see already-substituted variable values in the SQL
@@CTX:destination.qualified in SQL hooks sees the final target-overridden destination name because hooks are expanded after destination naming is fully resolved
SQL analysis validates the final expanded SQL, catching syntax errors from both vars and macros

​Syntax reference

​Project variables

​JSON vars and nested values

​Environment variables

​Context variables

​Deferred placeholders

​Audit parameters

​Compilation order