Hooks

SQL Hooks

Define, parameterize, compile, and invoke reusable or inline SQL lifecycle hooks.

SQL hooks submit one rendered SQL payload to the adapter before or after model materialization. Use a named SQL hook for reusable behavior and inline_sql(...) for short model-specific payloads.

For shared lifecycle ordering, failure timing, naming, and identity rules, see the Hooks overview.

Reusable SQL hooks

SQLBuild discovers .sql files recursively under hooks/sql/ and under scoped <folder>/_sqlbuild/hooks/sql/ and <folder>/_sqlbuild/_hooks/sql/ roles; see Hooks for who can call each. Each file defines exactly one hook and must start with a HOOK(...) header as its first non-whitespace content.

hooks/sql/permissions/grant_access.sql

HOOK (
  description "Grant a warehouse role access to the model relation"
);

GRANT SELECT ON @relation TO @role

The hook name is always the filename stem, so this resource is invoked as sql("grant_access", ...). Nested directories organize files but do not namespace names: hooks/sql/admin/grant_access.sql is still named grant_access.

HOOK() accepts only a required, non-empty description (Descriptions). It does not accept a name; rename the file to rename the hook. The content after the header must be non-empty and becomes the SQL payload for each invocation.

Files beginning with _ are skipped. All other .sql files under hooks/sql/ are parsed as hook resources and must have a valid HOOK() header.

Invoke a named hook

Pass the hook name and its arguments from a model’s pre_hooks or post_hooks list:

models/marts/orders.sql

MODEL (
  description "Orders",
  materialized table,
  post_hooks [
    sql(
      "grant_access",
      relation: "@@CTX:destination.qualified",
      role: "analyst_role",
    ),
  ],
);

SELECT 1 AS id

SQLBuild resource-header fields use native key value syntax, as in TEST (mode macro), SCENARIO (tags ["revenue"]), AUDIT (severity error), and HOOK (description "..."). The relation: ... and role: ... entries above intentionally retain key: value syntax because they are nested named arguments to the sql(...) hook call, not resource-header fields. The same distinction applies to named arguments passed to python(...).

SQL hook arguments

Named SQL hooks declare parameters by using them in the SQL body. Arguments are supplied as named values in sql("name", args...):

Syntax	Behavior
`@name`	Raw substitution. Strings are inserted verbatim for relations, identifiers, keywords, or SQL fragments.
`@'name'`	SQL-literal substitution. Strings are single-quoted and embedded quotes are escaped.

hooks/sql/record_access.sql

HOOK (
  description "Record access configuration"
);

INSERT INTO audit.access_log (relation_name, role_name)
VALUES (@'relation', @'role')

MODEL (
  description "Record access",
  post_hooks [
    sql(
      "record_access",
      relation: "@@CTX:destination.qualified",
      role: "O'Brien",
    ),
  ],
);

SELECT 1 AS id

For both forms, booleans render as TRUE or FALSE, numbers render directly, and null renders as NULL. Lists render as comma-separated values, applying raw or quoted behavior to each item. For example, @'roles' with roles: ["reader", "writer"] renders as 'reader', 'writer'.

Every referenced argument is required and every supplied argument must be used. Missing arguments, unused arguments, and unsupported values such as maps fail compilation. Raw string arguments are not escaped; use @'name' for data values and reserve @name for trusted SQL structure.

Inline SQL hooks

Use inline_sql("...") for SQL that is specific to one model:

MODEL (
  description "Example model",
  materialized table,
  post_hooks [
    inline_sql("GRANT SELECT ON @@CTX:destination.qualified TO analyst_role"),
  ],
);

SELECT 1 AS id

An inline hook accepts exactly one quoted SQL string and no additional arguments. That string becomes one adapter execution payload.

Compile-time context

Both named and inline SQL hooks receive the invoking model’s runtime context. They support:

Project variables such as @@audit_schema
Environment variables such as @@ENV:DEPLOY_ROLE
Hook context variables such as @@CTX:destination.qualified
Enums and constants such as @enum("role").ANALYST and @const("retention_days")
Python macros such as @py.grant_target("@@CTX:destination.qualified")

For named hooks, SQLBuild first substitutes @name and @'name' arguments into the hook body. A supplied argument such as relation: "@@CTX:destination.qualified" therefore resolves to the invoking model’s final target-overridden destination.

An inline hook uses macros, constants, and enums available to its model file. A named hook uses those available from the folder that owns its role: project-wide declarations for hooks/sql/, or the owner folder’s declarations for <folder>/_sqlbuild/hooks/sql/. See How Visibility Works.

${...} config-template syntax is not valid in SQL hooks.

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

Context components can be combined into qualified identifiers without whitespace or a Python wrapper:

HOOK (description "Create the reconstruct_book helper function");

CREATE FUNCTION @@CTX:destination.database.@@CTX:destination.schema.reconstruct_book()
RETURNS INTEGER
LANGUAGE SQL
AS 'SELECT 1'

Read models, sources, and seeds

SQL hooks reference other resources the same way model SQL does, with __ref("..."), __source("..."), and __seed("..."), written directly or passed as typed macro arguments:

MODEL (
  description "One row per order",
  materialized table,
  pre_hooks [
    inline_sql('DELETE FROM @@CTX:destination.qualified WHERE order_id IN (SELECT order_id FROM __ref("cancelled_orders"))'),
  ],
  post_hooks [sql("record_counts")],
);

SELECT order_id FROM __ref("stg_orders")

models/marts/_sqlbuild/_hooks/sql/record_counts.sql

HOOK (description "Record counts");

@py.count_rows(__ref("stg_orders"))

At plan time each reference resolves exactly like one in model SQL: adapter quoting, source deferral, --defer, and target overrides all apply. Every reference is also a hook read, with the same rules as declared Python hook reads:

The model that runs the hook waits for the resources it reads when both are in the build. Reads do not change selection; in a partial build the hook reads the existing relations.
A hook cannot read a resource built from its own model; that fails to compile with P007. Use @@CTX:destination.qualified for the hook’s own model.
Referencing an unknown model, source, or seed fails to compile.
A reference passed to a macro is a read even when the macro does not render it. A reference a macro writes into its output instead fails with P006, as in model SQL.

The model must still see a named hook under the usual scope rules.

Validation and diagnostics

SQLBuild strictly validates hook resource syntax, arguments, interpolation, macros, metadata, and non-empty payloads. It does not classify executable statement kinds or implement vendor SQL grammar. Each rendered hook payload is passed to the adapter in one execute call; whether a driver accepts multiple statements or client-side batch separators is adapter and warehouse behavior. Use separate hook entries when portable ordering between statements matters.

After expansion, Polyglot validates the complete payload using the active adapter’s analysis dialect when the model’s effective sql_analysis setting is enabled, which it is by default. The --no-sql-analysis flag disables this analysis for the invocation. Polyglot does not understand every administrative or procedural command supported by every warehouse. If it cannot parse valid vendor-specific hook SQL, disable SQL analysis for that model or invocation and let the adapter and warehouse provide the authoritative result. SQLBuild does not compensate with keyword allowlists or handwritten parser fallbacks.

Common errors include:

Missing or malformed leading HOOK(...) headers
Unsupported header keys, empty descriptions, and missing SQL bodies
Missing or unused arguments and unsupported argument values
Unknown named hooks, unquoted hook names, and bare hook strings
SQL rejected by Polyglot while optional SQL validation is enabled

Definition errors point to the hook file. Invocation and argument errors also identify the consuming model entry, such as post_hooks[1] sql("grant_access"). Runtime output preserves the authored hook index and identifies named and inline SQL entries.

Part of SQLStack