Schema Versioning

Moose lets you declare schema versioning directly in your table definition. When you bump the version field on an OlapTable, Moose creates a new physical ClickHouse table and interactively walks you through data migration decisions:

• Backfill — copy existing data from the old table into the new one
• Retain or drop — keep the old table (marked EXTERNALLY_MANAGED) or drop it after migration

This works in both moose dev (local development) and moose generate migration (planned migrations for production).

When to use schema versioning

• 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 full rewrite
• Keep the old physical table in place until you finish cutover and later cleanup

Configuration

Set version on the OlapTable config:

export const aircraftTrackingData = new OlapTable<AircraftTrackingData>(  "aircraft_tracking_data",  { version: "0.1" });

Version bump workflow

When you bump the version field, Moose detects the old versioned table as removed and the new one as added, and recognizes this as a version bump rather than an independent drop + create. It then prompts you for two decisions:

1. Backfill data

If the old and new tables have compatible schemas (same insertable columns, types, and nullability), Moose offers to generate an INSERT INTO ... SELECT query to copy data:

Backfill data from `aircraft_tracking_data_0_0` into `aircraft_tracking_data_0_1`? [Y/n]

Accept to include the backfill in the migration. Decline if you want to handle data migration separately or the data isn't needed.

2. Keep or drop the old table

After the backfill decision, Moose asks whether to keep the old table:

Keep old table `aircraft_tracking_data_0_0`? (will be marked EXTERNALLY_MANAGED) [y/N]

Version changes

Bumping the version field on the OlapTable config creates a new physical table while keeping the logical table name stable in code.

import { OlapTable } from "@514labs/moose-lib"; interface AircraftTrackingData {  icao24: string;  callsign: string;  longitude: number;  latitude: number;} export const aircraftTrackingData = new OlapTable<AircraftTrackingData>(  "aircraft_tracking_data",- { version: "0.0", orderByFields: ["icao24"] }+ { version: "0.1", orderByFields: ["icao24", "callsign"] });

Using version bumps in moose dev

During local development, moose dev detects version bumps on startup and on every file-save cycle. You get the same interactive prompts:

Moose executes the operations in the correct order: create the new table, run the backfill, then drop the old table (if not retained). Other schema changes (new views, materialized views, etc.) are applied alongside the version bump in the same cycle.

Using version bumps in planned migrations

Run moose generate migration --save to produce a reviewed migration plan that includes the version bump operations:

The resulting plan.yaml includes correctly-ordered operations:

created_at: 2026-04-09T12:00:00Zoperations:  - CreateTable:      table:        name: aircraft_tracking_data_0_1        columns:          # generated column definitions omitted for brevity        order_by:          - icao24          - callsign        engine: MergeTree        version: "0.1"  - RawSql:      description: >-        Backfill `aircraft_tracking_data_0_1`        from `aircraft_tracking_data_0_0`      sql:        - |          INSERT INTO `my_db`.`aircraft_tracking_data_0_1`            (`icao24`, `callsign`, `longitude`, `latitude`)          SELECT `icao24`, `callsign`, `longitude`, `latitude`          FROM `my_db`.`aircraft_tracking_data_0_0`  - DropTable:      table: aircraft_tracking_data_0_0

If you chose to keep the old table instead, the DropTable operation would be absent and Moose would generate a retained table file in your source directory.

CI/CD and non-interactive usage

In CI/CD pipelines or non-interactive environments, use confirmation flags to control version bump behavior:

Without these flags, a non-interactive terminal causes the CLI to exit with an error asking you to re-run with --yes-all.

Operation ordering

Moose ensures version bump operations execute in the correct order, regardless of other schema changes happening in the same migration:

1. Create the new versioned table
2. Run backfill INSERT INTO ... SELECT (if accepted)
3. Drop the old table (if not retained)

Other non-version-bump changes (new tables, views, column alterations) are interleaved correctly — the new versioned table is available before dependent views or materialized views are created.

Notes

• A version bump creates a new physical table. Moose does not rewrite the old table in place.
• If the old and new tables are not schema-compatible for a straight INSERT INTO ... SELECT, Moose will not offer auto-generated backfill. You can edit plan.yaml and provide custom SQL.
• If you decline the backfill prompt, the plan still creates the new versioned table. You can backfill later outside the migration.
• Very large backfills can take time. Review the generated SQL and apply it during a low-traffic window if needed.
• When keeping the old table, the generated EXTERNALLY_MANAGED file ensures Moose still tracks the table for queries, views, and type-safe access, but won't try to drop or alter it.

Related

• Modeling Tables
• Externally Managed Lifecycle
• Planned Migrations
• Automatic Migrations
• Applying Migrations