SQL Models

What it is#

The vocabulary is dbt's on purpose: if you have written a dbt project you already know what a model, a ref, a materialization and a not_null test are. What is deliberately absent is Jinja, macros, seeds and packages. The gap this closes is ordered transformation, not a templating language.

Find it under Data & BI → SQL Models.

Why this and not a materialized view#

A materialized view already turns one query into one table on a schedule. What it cannot do is the thing every warehouse team actually has: a set of tables that depend on each other. Two behaviours follow from the graph, and neither is available without it.

• Order. A model is built only after everything it refs. A fact rebuilt on one schedule while its staging table is rebuilt on another leaves the two disagreeing for however long the gap is.
• Propagation. When a model fails — or an error test on it fails — everything downstream is skipped, not built. A fact silently rebuilt from a staging table you already know is broken is the failure mode this exists to prevent.

A model and a materialized view cannot claim the same schema.table. Saving a model over an existing view's target is refused by name.

Writing a model#

select
  order_id,
  customer_id,
  cast(created_at as date) as order_date,
  amount
from analytics.raw_orders
where amount is not null

Saved as stg_orders into a schema you own, that becomes the table <schema>.stg_orders — the model's name is its table name, so there is one way to refer to it and no chance of the ref name and the physical name drifting apart. Then a model that reads it:

select
  order_date,
  count(*) as orders,
  sum(amount) as revenue
from ref('stg_orders')
group by 1

• ref('stg_orders') is replaced with the quoted table before the query runs, and the dependency is recorded. Nothing else is templated.
• A ref inside a comment is not a dependency. A commented-out ref that still imposed a build order would refuse projects that are actually fine.
• A cycle is refused when you save, by name, with the loop spelled out — while the project still builds, rather than at the next scheduled build when nothing runs.
• Names are lower case letters, digits and underscores, starting with a letter or underscore.

Table or view#

A build lands as one DuckLake commit, so readers see the previous table or the new one and never a half-built one. A failed build leaves the previous table in place: stale data someone can see and diagnose beats no data at all.

Tests#

Tests run after a model builds, against the table it just wrote.

• error — the model is marked failed and everything downstream is skipped. Use it for anything that would make a dependant wrong.
• warn — the failure is recorded on the build and the build carries on.

Building#

• Build all builds every active model, in order.
• Build this and what it reads builds one model with its ancestors — dbt's +model. Rebuilding a fact without the staging table it reads would leave the two disagreeing.
• A schedule on a model (hourly, daily, weekly, or a cron expression with a timezone) does the same thing on its own. Several due models for one owner become one build over the union of their ancestors, so a shared staging table is built once per sweep rather than once per dependant.
• Pausing a model stops it being rebuilt, not being read: its table stays on disk and ref() still resolves to it.
• Deleting a model deletes the definition. The table stays, because a dashboard or an agent may still be reading it — drop it from the Lakehouse page if you want it gone. Models that still ref a deleted one are named when you delete it.

Scheduled builds ride the same sweep as every other schedule on the platform, with the same compare-and-set claim, so every replica behind a load balancer can run it without building twice.

After it builds: naming what the columns mean#

A model produces a table. It does not say that net_usd summed is “revenue”, that only completed orders count, or that nobody outside Finance may see the margin. That is the semantic layer, and the two are meant to be used together.

• Build the model. It writes analytics.fct_orders.
• Press Define metrics on this on the model, or on the table in the Lakehouse page.
• The semantic editor opens on that table. Name the metrics and dimensions once.
• Dashboards, the AI Analyst, agents through the metric_query tool and the /api/v1/metrics HTTP API then all compute them the same way.

The lakehouse is reached as a warehouse connection whose provider is the built-in lakehouse, so this needs one connection row the first time. The button offers to create it, and asks only for a name.

Governance#

Everything a model does, it does as its owner. A schedule has no session behind it, so the owner's grants are the only correct authority.

• Where it can write. Only a lakehouse schema the owner owns. A mounted data lake is read-only and is refused, at save and again at every build.
• What it can read. Re-checked on every build against the owner's current grants, not against what they had when the model was saved.
• What it can be. A model must be a SELECT. A definition edited into a write is refused by the same classifier the SQL workbench uses.
• Audit. sql_model.build for every build with the trigger and each model's outcome, sql_model.pause and sql_model.resume, and the table's own row trigger for every change to a definition, its schedule or its tests.
• Lineage. Model-to-model edges are written on every build and appear in the Data Catalog lineage panel beside crawled and ETL edges. They are replaced wholesale each build: a stale edge is worse than a missing one, because a stale graph is believed.

Troubleshooting#