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.

SQLBuild uses two syntax layers for dynamic content:
  • @ syntax is for any executable SQL - model queries, hooks, tests, audits, and inline source expressions
  • ${...} syntax is for config values - TOML/YAML project config, MODEL() header fields (excluding 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

SyntaxWhereResolved
@macro(args)Model SQL, hooks, tests, audits, inline source expressionsCompile time - expands to macro return value
@@nameModel SQL, hooks, tests, audits, inline source expressionsCompile time - project variable substitution
@@ENV:NAMEModel SQL, hooks, tests, audits, inline source expressionsCompile time - environment variable
@@CTX:nameHooks onlyCompile time - target relation, environment, run ID
@@@nameModel SQLPreserved for runtime (custom materializations)
@name / @'name'Generic audit SQL onlyAudit engine parameter
${CTX:...}TOML/YAML config valuesConfig compilation
${ENV:...}TOML/YAML config valuesConfig compilation
@@CTX: is intentionally hook-only. Model SQL describes a relation’s data and should not reference its own target identity. Hooks are the operational SQL layer where target context is useful - grants, logging, post-materialization DDL.

​
Project variables

Project variables use @@name syntax in SQL and are defined in sqlbuild_project.toml or per-environment: 
# sqlbuild_project.toml
[vars]
schema_prefix = "analytics"

[environments.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 target relation, environment, and run metadata. In hooks (@@CTX: syntax): 
post_hook ['GRANT SELECT ON @@CTX:target.qualified TO analyst_role'],
In TOML/YAML config values (${CTX:...} syntax): 
[environments.prod]
schema = "${CTX:target.schema}"
Available context variables:
VariableValue
target.qualifiedFully qualified target relation name
target.schemaTarget schema
target.nameTarget relation name
model_nameModel name
environmentCurrent environment name
run_idCurrent 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, sqlglot_enabled, environment_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:
  1. Config templates (${CTX:...}, ${ENV:...}) in TOML/YAML config values are resolved during config compilation
  2. Project variables (@@name), environment variables (@@ENV:NAME), and context variables (@@CTX:name in hooks) are substituted
  3. Macro calls (@name(args)) are expanded
  4. SQLGlot 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:target.qualified in hooks sees the final environment-overridden target name because hooks are expanded after target naming is fully resolved
  • SQLGlot validates the final expanded SQL, catching syntax errors from both vars and macros