# Table definitions (`tables/`)

> `tables/` holds tables written directly in ClickHouse types, as opposed to the
> `meta/` schemas that map DFE's 13 primitives onto ClickHouse. Five sets live
> here: `core/` (the landing table, the dead-letter table and the detection pair, whose columns come
> from a common-header profile), `otel/` (the telemetry tables the collector
> writes into and dfe-hyperdx reads), `internal/` (the engine's own working state,
> including the applier's migration ledger and its lock), `usage/` (the usage
> rollup a deployment is licensed on, and the views that refresh it) and `meta/`
> (the governance projection, which lands in `dfe_meta` rather than the data
> database).

# Table definitions (`tables/`)

They are here for the same reason every other schema is -- one definition, in
one repo. Owning the OTel tables is also what makes them correct on a cluster:
the exporter creates its tables over whichever connection it resolved and
senses no topology, so the same table ended up plain on one replica and
Replicated on the others, and a row written to the plain copy never
replicated.

## File shape

Same version tree as the rest of the repo -- `current` names the default
version, each version entry is a complete snapshot. A version entry carries
`table` (the DDL clauses), `columns`, and optionally `materialized_view`.

```yaml
current: "1.0.0"

versions:
  "1.0.0":
    date: "2026-08-26"
    type: model
    summary: "One row per gauge reading"
    table:
      engine: "MergeTree"
      partition_by: "toDate(_timestamp_load)"
      order_by: "ServiceName, MetricName, TimeUnix"
      index_granularity: 8192
      indexes:
        - "INDEX idx_time_minmax TimeUnix TYPE minmax GRANULARITY 1"
    columns:
      - name: "TimeUnix"
        ch_type: "DateTime"
        codec: "Delta(4), ZSTD(1)"
```

## `table` keys

| Key | Meaning |
|---|---|
| `engine` | The engine INTENT (`MergeTree`, `ReplacingMergeTree(updated_at)`). The renderer adds the `Replicated` prefix every DFE table takes, on every platform. |
| `partition_by` | Raw PARTITION BY expression. |
| `order_by` | Raw ORDER BY expression. Used when the sorting key is expressions rather than bare columns. |
| `index_granularity` | Rows per granule. 8192 for range scans, 2048 for point lookups. |
| `ttl_days` / `ttl_columns` | Retention. `ttl_columns` alone takes the deployment default (`DFE_ENGINE_CLICKHOUSE_DEFAULT_TTL_DAYS`, 90 days unless the deploy sets otherwise); `ttl_days` overrides it for this table. Omit both for a table that keeps everything. The default applies to time-series tables (`core/`, `otel/`); the engine's state tables under `internal/` and `detection_checkpoint` declare their own. |
| `indexes` | Data-skipping indexes, emitted verbatim. |
| `reader_grant` | `true` renders `GRANT SELECT` on the table to the reader role after the CREATE, as every view does. |

A TTL over a `Date` column renders without the `>= 0` guard, which 26.3 refuses
there (code 43).

A `Merge` engine, which stores no rows, renders as written with `{db}` filled
in, and takes none of the MergeTree clauses above:

```yaml
    table:
      name: "usage_events"
      engine: "Merge({db}, '^all_sources$')"
```

An ORDER BY built from bare columns is expressed on the columns instead: give
each key column an `order` and leave `order_by` out.

## `columns` keys

| Key | Meaning |
|---|---|
| `name` | Column name, verbatim. |
| `ch_type` | Exact ClickHouse type, without any `LowCardinality` wrapper. |
| `cardinality` | `low`, `high` or `unknown` (the default). `low` wraps `ch_type` in `LowCardinality(...)`. |
| `lowcardinality` | Retired -- the old boolean spelling of `cardinality: low`. |
| `order` | Position in the sorting key, from 0. |
| `default` | DEFAULT expression. |
| `materialized` | MATERIALIZED expression. Mutually exclusive with `default`. |
| `codec` | CODEC arguments, e.g. `Delta(8), ZSTD(1)`. |
| `comment` | Column comment. |
| `max_dynamic_paths` | Typed sub-paths a `JSON` column holds before the rest spill to a shared map. `inherit` reads the value from the common header's `_json`. |

A nested `LowCardinality` is part of the type and stays in `ch_type`
(`Map(LowCardinality(String), String)`); only a top-level wrapper is declared as
`cardinality: low`.

## `materialized_view`

A materialised view has no columns to reconcile, only a SELECT. `to` names the
target table, and `{db}` in `select` is substituted at apply time.

`refresh` makes it a refreshable view, `EVERY` or `AFTER` a count and a unit,
with an optional `OFFSET`. It always renders `APPEND`, and inside the
Replicated database each scheduled refresh runs on one replica. A file holding
several views lists them under `materialized_views`, and each manifest object
picks one by `key`:

```yaml
    materialized_views:
      - name: usage_minute_cores_mv
        to: usage_minute
        refresh: "EVERY 5 MINUTE"
        select: |-
          SELECT minute, metric, _org_id, value
          FROM {db}.usage_minute_cores_recent
```

```yaml
    materialized_view:
      name: otel_traces_trace_id_ts_mv
      to: otel_traces_trace_id_ts
      select: |-
        SELECT TraceId, min(Timestamp) AS Start, max(Timestamp) AS End
        FROM {db}.otel_traces
        WHERE TraceId != ''
        GROUP BY TraceId
```