# Moose / Olap / Schema Versioning Documentation – TypeScript

## Included Files
1. moose/olap/schema-versioning/schema-versioning.mdx

## Schema Versioning with Materialized Views

Source: moose/olap/schema-versioning/schema-versioning.mdx

Use table versions and materialized views to migrate breaking schema changes safely

# Table Versioning & Blue/Green Migrations

## Overview

Changing a table's storage layout (engine or sorting key) in ClickHouse requires a full table rewrite. Doing it in-place can block or slow concurrent reads and writes due to heavy merges and metadata changes, creating real risk for production workloads. Blue/Green avoids this by creating a new versioned table and migrating data live via a materialized view, so traffic continues uninterrupted.

**When to use it**: 
- Change the **table engine** (e.g., MergeTree → ReplacingMergeTree)
- Update **ORDER BY fields** (sorting keys) to better match query patterns
- Reshape **primary keys** or perform type changes that require a rewrite

**How Moose does it**:
1. Define a new table with the same logical name and a bumped `version`, setting the new  `orderByFields` and/or `engine` ([Table modeling](/moose/olap/model-table)).
2. Create a [Materialized view](/moose/olap/model-materialized-view) that selects from the old table and writes to the new one; Moose backfills once and keeps the view live for new inserts.
3. Later on, cut over readers/writers to the new export and clean up old resources ([Applying migrations](/moose/olap/apply-migrations)).

Setting `config.version` on an `OlapTable` changes only the underlying table name (suffixes dots with underscores). Your code still refers to the logical table you exported.

## High-level workflow

## Example: change sorting key (ORDER BY)

Assume the original `events` table orders by `id` only. We want to update the
sorting key to optimize reads by ordering on `id, createdAt`.

### Original table (version 0.0)

```ts filename="app/tables/events.ts" copy

interface EventV0 {
  id: string;
  name: string;
  createdAt: Date;
}

);
```

### New table (bump to version 0.1)

Create a new table with the same logical name, but set `version: "0.1"` and update the ordering to `id, createdAt`. Moose will create `events_0_1` in ClickHouse.

```ts filename="app/tables/events_v01.ts" copy

interface EventV1 {
  id: Key<string>;
  name: string;
  createdAt: Date;
}

);
```

### Create the materialized view to migrate data

Create a materialized view that:
- SELECTs from the old table (`events_v0`)
- copies fields 1:1 to the new table
- writes into the versioned target table (`events_v1`)

Pass the versioned `OlapTable` instance as `targetTable`. If you only pass a `tableName`, Moose will create an unversioned target.

```ts filename="app/views/migrate_events_to_v01.ts" copy

);
```

What happens when you export this view:
- Moose creates the versioned table if needed
- Moose creates the MATERIALIZED VIEW and immediately runs a one-time backfill (`INSERT INTO ... SELECT ...`)
- ClickHouse keeps the view active: any new inserts into `events` automatically flow into `events_0_1`

## Cutover and cleanup

- Update readers to query the new table export (`eventsV1`).
- Update writers/streams to produce to the new table if applicable.
- After verifying parity and retention windows, drop the old table and the migration view.

## Notes and tips

- Use semantic versions like `0.1`, `1.0`, `1.1`. Moose will render `events_1_1` as the physical name.
- Keep the migration view simple and deterministic. If you need complex transforms, prefer explicit SQL in the `selectStatement`.
- Very large backfills can take time. Consider deploying during low-traffic windows.