Models (SQL Transformations)

A model is a named SELECT that CH-UI builds into a ClickHouse view or table. Models reference each other with $ref(model_name); CH-UI turns those references into a dependency graph (DAG) and runs models in dependency order, by hand or on a cron schedule.

Available on all plans.

Overview

Each model has:

• A name: a valid ClickHouse identifier, unique per connection. It is also the name of the view or table the model builds.
• A SQL body: one SELECT, optionally with $ref(other_model) references.
• A materialization: view or table.
• A target database: where the view or table is created.

Models belong to a ClickHouse connection. Every run, manual or scheduled, executes through the tunnel with the connection’s model background account.

Creating a model

Open Models in the sidebar and click New model. Pick a name, a target database and a materialization; the model starts with SELECT 1 AS id and opens in an editor tab. Write the real query there and click Save. Run saves pending edits first, then builds the model and its upstream dependencies.

Field	Description	Default
Name	^[a-zA-Z_][a-zA-Z0-9_]*$	Required
Description	Free text	Empty
Target database	ClickHouse database for the output	default
Materialization	view or table	view
SQL body	The SELECT, with optional $ref(name)	Required
Table engine	Engine for table models	MergeTree
ORDER BY	For MergeTree-family engines	tuple()

Any other materialization is rejected with an error, whether it comes from the UI, the API, Brain or a GitHub frontmatter block.

Materializations

Type	What a run does
view	CREATE OR REPLACE VIEW name AS <select>. ClickHouse replaces the view atomically.
table	Builds CREATE TABLE name__chui_tmp ENGINE = <engine> [ORDER BY ...] AS <select>, then swaps it in for the old table. The table is rebuilt in full on every run.

Table rebuilds are safe

A table rebuild never drops the current table before the new one exists:

1. The new data is built into <name>__chui_tmp.
2. If that fails, the temporary table is dropped and the previous table is left untouched.
3. If it succeeds, the two are swapped: EXCHANGE TABLES when the database engine is Atomic (the default), otherwise RENAME the old table to <name>__chui_old, rename the new one into place and drop the old one.

Engines

MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree and Memory are offered in the editor. ORDER BY is only sent for MergeTree-family engines; an empty ORDER BY becomes tuple(). Engines that take no ORDER BY (Memory, the Log family) are created without one.

Ownership marker

Every view and table a model builds gets the ClickHouse comment ch-ui model <model id>. Before replacing or dropping an existing object with the model’s name, CH-UI reads system.tables and refuses when the object is not marked as this model’s:

a table with this name already exists and was not created by this model

So a model named like a table you made by hand never touches that table. Rename the model, or drop the table yourself.

Views and tables built by CH-UI versions from before the marker have no comment. Such an object is treated as the model’s own only when its kind (view or table) matches the model and the model has a successful run on record; the next run adds the marker.

The model background account needs CREATE TABLE, CREATE VIEW and DROP TABLE, DROP VIEW on the target database (plus SELECT on what the models read). ALTER MODIFY COMMENT is recommended: CH-UI marks the objects it builds with a comment. Without it, runs still work and ownership falls back to the model’s run history.

References with $ref()

Write $ref(model_name) wherever the SQL reads another model:

SELECT
  a.user_id,
  a.total_orders,
  b.lifetime_value
FROM $ref(user_orders) a
JOIN $ref(user_revenue) b ON a.user_id = b.user_id

At run time each reference becomes the referenced model’s fully qualified name:

FROM `analytics`.`user_orders` a
JOIN `analytics`.`user_revenue` b ON a.user_id = b.user_id

Rules:

• $ref(name) becomes `target_database`.`name` of the referenced model.
• References inside comments (--, /* */), string literals and quoted identifiers are ignored. The SQL that runs keeps your comments and literals exactly as written.
• Each distinct reference is one dependency edge in the DAG.
• A reference to a model that does not exist fails that model at run time and shows up in validation.

Tables that are not models are read with plain SQL (FROM raw.events); they are not part of the DAG.

DAG and validation

CH-UI extracts the references from every model, builds the dependency graph and sorts it with Kahn’s algorithm. A cycle is reported as an error.

Check	Error
Unknown reference	references unknown model "foo" via $ref()
Self-reference	cannot reference itself via $ref(model_name)
Circular dependency	cycle detected in model dependencies

Run validation from the UI or with GET /api/models/validate. DAG in the page header draws the graph.

Running models

Runs are synchronous: the request returns when the run has finished, with its status. One run per connection at a time; a second one gets a model run is already in progress for this connection. Each statement has a 5-minute timeout.

Action	Endpoint	Builds
Run all (page header)	POST /api/models/run	Every model of the connection, in dependency order
Run (pipeline row)	POST /api/models/pipelines/{anchorId}/run	The models of one pipeline
Run (editor)	POST /api/models/{id}/run	The model plus all its upstream dependencies

The response is {"run_id": "...", "status": "success" | "partial" | "error"}, with an error summary when a model failed. An unknown model id returns 404 and records nothing.

Per model, a run:

1. Skips the model if an upstream dependency failed.
2. Resolves $ref().
3. Builds the view or table as described above.
4. Records status, elapsed time, the executed statement and any error.

If a model fails, its downstream models are skipped. The run is success when everything built, partial when some models built and some failed, and error otherwise.

Pipelines

A pipeline is a group of models connected through references, directly or indirectly. CH-UI treats the graph as undirected and finds the connected components; each one is a pipeline.

Pipeline 1: raw_events → cleaned_events → daily_aggregates
Pipeline 2: user_signups → user_cohorts

Each pipeline is identified by an anchor model: the model its schedule is stored on, or, for a pipeline without a schedule, the first model in dependency order.

Scheduling

Each pipeline can have one cron schedule. The scheduler checks for due schedules every 30 seconds.

Click the schedule button on a pipeline row to set it:

Field	Description
Cron	5-field cron expression (minute hour day-of-month month day-of-week)
State	Pause stops the schedule without deleting it; Resume starts it again from the next due time

0 9 * * *     # Daily at 9:00
0 */6 * * *   # Every 6 hours
30 2 * * 0    # Sundays at 2:30
0 0 1 * *     # First of the month at midnight

A scheduled run behaves like Run on the pipeline, with triggered_by set to scheduler. The schedule records the outcome: success, or error with a summary when any model failed or was skipped.

API: PUT /api/models/schedule/{anchorId} with {"cron": "0 * * * *", "enabled": false} saves a paused schedule; omit enabled to keep the current state. DELETE /api/models/schedule/{anchorId} returns 404 when the pipeline has no schedule.

Credentials come from the model background account. If none are available, or the worker is disabled, the run fails. Disabled blocks manual runs too.

Run history

History in the page header lists recent runs with their status and counts. Expand a run for per-model results; SQL on a result shows the statement that ran.

Run status	Meaning
running	In progress
success	All models built
partial	Some built, some failed
error	All failed or were skipped

Result status	Meaning
pending	Not started yet
running	Building
success	Built
error	Failed (see the error)
skipped	An upstream dependency failed

If CH-UI stops while a run is in progress, the run and its unfinished models are marked error with interrupted by restart when it starts again.

GitHub

Models can be imported from .sql files in a GitHub repository and kept in step with it. The sync is one-way: the repository is the source of truth, and GitHub-managed models are read-only in CH-UI. See GitHub Sync.

Planned

These are not available today:

• incremental and materialized_view materializations
• dbt-style templating ({{ ref() }}, {{ source() }}, {{ this }}, {{ config() }}) and sources.yml
• Data tests on model columns
• Pushing model changes from CH-UI back to GitHub
• PARTITION BY for table models

GitHub SyncImport models from a repository and keep them in sync

Background accountsThe ClickHouse account model runs use