Models

Overview

SQL model anatomy, references, dependencies, and the model documentation guide.

A model is a SQL file that defines one transformation step and produces a table or view in the warehouse.

Model anatomy

Every model starts with a MODEL() header followed by its query:

MODEL (
  materialized table,
  tags [marts],
  description "Order fact table",
  columns (
    order_id (type INTEGER, audits [not_null]),
  ),
);

SELECT
  o.order_id,
  o.customer_id,
  p.amount_cents
FROM __ref("stg_orders") o
JOIN __ref("stg_payments") p USING (order_id)

The header controls how SQLBuild builds, validates, and documents the model. The query remains ordinary SQL apart from SQLBuild reference and macro calls.

References

Reference	Syntax	Resolves to
Model	`__ref("name")`	Another model
Seed	`__seed("name")`	A seed CSV table
Source	`__source("name")`	An external source
Scalar UDF	`__udf("name")`	A user-defined function

Write the name as exactly one double-quoted string, with no spaces or comments inside the parentheses. __dbt_ref("package", "name") is the only call that takes two names. Compile rejects any other form, such as __ref(stg_orders), __ref('stg_orders') or __ref( /* upstream */ "stg_orders"), with a P012 error at the call that shows the corrected call:

error[P012]: __ref(stg_orders) is not a valid __ref() call
  model: fact_orders
  --> models/marts/fact_orders.sql:24:6
     |
  24 | FROM __ref(stg_orders) o
     |      ^^^^^^^^^^^^^^^^^
  = help: __ref() takes exactly one double-quoted name, with no comments or extra spaces inside the parentheses: __ref("stg_orders")

One compile reports every rejected call. When a macro returns the rejected call, the error points at the macro and shows how to pass the reference in instead, for example @py.staged_orders(__ref("stg_orders")). The same rule applies inside test and scenario files, including __assert__ CTEs.

SQLBuild discovers the dependency graph from these calls and orders selected work topologically. Among selected models, upstream models run before downstream dependents. An unselected upstream is read from its existing warehouse relation; use an upstream-expanding selector such as +fact_orders when it should also be built. Seeds use __seed(), not __ref().

See Functions for scalar UDF and table-function references.

Model guide

Materializations: views, tables, incrementals, snapshots, and custom materializations.
Schemas: inline columns, reusable schemas, inheritance, audits, and model-local extensions.
Type Enforcement: static type checks and runtime cast behavior by materialization.
Contracts: exact and open output validation, runtime guarantees, nullability, and enum-backed columns.
Hooks: SQL and Python lifecycle hooks.
Configuration: MODEL() field reference and SQL-validation controls.

For deeper execution behavior, see Incremental, Snapshots, and Audits.

Part of SQLStack