> ## Documentation Index
> Fetch the complete documentation index at: https://docs.streambuild.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Models

> Typed SQL models, references, storage settings, and graph semantics.

Each model is one SQL file with a `MODEL()` header and one outer `SELECT`. StreamBuild uses the
result to create a ClickHouse table and the materialized view that writes into it.

```sql theme={null}
MODEL (
  engine "MergeTree()",
  order_by ["order_id", "_replay_partition", "_replay_offset"],
  partition_by "toYYYYMM(event_at)",
  ttl "event_at + INTERVAL 30 DAY",
  settings (
    index_granularity 8192,
  ),
  replay_anchor auto,
);

SELECT
  CAST(order_id AS String) AS order_id,
  amount_cents::UInt64 AS amount_cents,
  event_at::DateTime64(3) AS event_at,
  _replay_partition::Int32 AS _replay_partition,
  _replay_offset::Int64 AS _replay_offset,
  _replay_timestamp::DateTime64(3) AS _replay_timestamp
FROM __source("orders")
```

The header grammar is:

* fields are whitespace-separated `key value` entries
* lists use `[...]`
* nested mappings use `(...)`
* commas are optional separators and may trail the final entry
* values containing spaces must be quoted

YAML-like `key: value` and `{...}` mappings are not accepted. `MODEL ();` uses all defaults.

## SQL contract

The outer statement must be one `SELECT`. Every outer projection must:

* be explicit rather than `*`
* have a unique alias
* declare an exact ClickHouse type with `CAST(expr AS Type) AS alias` or `expr::Type AS alias`

CTEs may contain stars, untyped expressions, and set operations. A top-level `UNION`, `INTERSECT`,
or `EXCEPT` is rejected; wrap it in a CTE and finish with a typed projection.

`order_by`, `partition_by`, and `ttl` expressions may reference only output columns.

## Header fields

| Field                     | Default                 | Purpose                                                                  |
| ------------------------- | ----------------------- | ------------------------------------------------------------------------ |
| `engine`                  | `"MergeTree()"`         | ClickHouse MergeTree-family table engine                                 |
| `order_by`                | `["_replay_timestamp"]` | Table sorting key expressions                                            |
| `partition_by`            | none                    | Partition expression                                                     |
| `ttl`                     | none                    | ClickHouse TTL expression                                                |
| `settings`                | none                    | Table settings mapping                                                   |
| `replay_anchor`           | `auto`                  | Use `replay_anchor never` to prevent this model becoming a replay anchor |
| `replay_on_change`        | inherited               | Virtual-environment change policy                                        |
| `bounded_replay_fallback` | `full`                  | Virtual-environment fallback for unseedable models                       |

`replay_on_change` and `bounded_replay_fallback` are rejected in direct mode. `replay_anchor`
remains valid because direct rebuilds also need replay roots.

## Driving references

A model has exactly one untyped driving input:

```sql theme={null}
FROM __source("orders")
```

or:

```sql theme={null}
FROM __ref("orders")
```

Source-root models use `__source`. Downstream models use `__ref`.

Additional joins and subqueries must declare their role:

```sql theme={null}
FROM __ref("orders") AS orders
LEFT JOIN __ref("region_lookup", ref_type="reference") AS regions
  ON orders.region_code = regions.region_code
```

| `ref_type`  | Meaning                                                                                |
| ----------- | -------------------------------------------------------------------------------------- |
| `reference` | A side dependency expected to be stable for replay                                     |
| `mutable`   | A side dependency whose current state may differ from historical processing-time state |

Mutable references emit a replay warning and prevent the model from being a replay anchor. Both
side-reference types participate in graph scope and ordering.

## Replay columns

Normalized replay columns are source-independent:

```text theme={null}
_replay_partition
_replay_offset
_replay_timestamp
_replay_landed_at
_replay_cursor
```

Managed Kafka landing exposes partition, offset, timestamp, and landed-at values. Adopted sources
expose the roles declared in their column mapping. Preserve the columns required by the source mode
through non-aggregate models when those outputs should remain eligible replay anchors.

Aggregate models do not need to expose post-aggregate replay columns. StreamBuild places replay
predicates on their input query instead.

## Engines and duplicate delivery

Common engines include `MergeTree`, `ReplacingMergeTree`, `SummingMergeTree`, and
`AggregatingMergeTree`, including replicated variants. Engine choice is part of replay correctness:
the replay cutoff is inclusive, so authored SQL and storage engines must tolerate duplicate delivery
where necessary.