Schema Versioning

Moose lets you declare schema versioning directly in your table definition:

• Set version to create a new physical ClickHouse table while keeping the logical table name stable in code
• Generate reviewed backfill SQL in migrations/plan.yaml when the old and new versions are equivalent enough for a straight copy

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 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 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"] });

Migration plan

When you bump version, Moose plans a new physical table instead of rewriting the existing one. Run moose generate migration --save to write those reviewed operations to migrations/plan.yaml.

For this version: "0.0" to version: "0.1" change, the plan can include:

• CreateTable for the new version: "0.1" physical table, such as aircraft_tracking_data_0_1, with the updated sort key.
• Optional RawSql INSERT INTO ... SELECT ... to copy rows from the version: "0.0" physical table into the version: "0.1" physical table when Moose detects a straight-copy backfill.

For command options and connection modes, see Planned Migrations and the moose generate migration CLI reference.

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 `514-demos-planes`.`aircraft_tracking_data_0_1` from `514-demos-planes`.`aircraft_tracking_data_0_0`      sql:        - |          INSERT INTO `514-demos-planes`.`aircraft_tracking_data_0_1` (`icao24`, `callsign`, `longitude`, `latitude`)          SELECT `icao24`, `callsign`, `longitude`, `latitude`          FROM `514-demos-planes`.`aircraft_tracking_data_0_0`

Notes

• A version bump creates a new physical table. Moose does not rewrite the old table in place or create a long-lived materialized view between versions.
• If the same branch also adds other tables, views, or materialized views, they appear in the same plan alongside the versioned-table backfill. Review the whole file, not just the RawSql block.
• If the old and new tables are not equivalent enough for a straight INSERT INTO ... SELECT ..., Moose will not offer the generated backfill you want. Edit plan.yaml and provide the exact SQL you want to run.
• If you decline the generated backfill prompt, the plan can still create the new versioned table. You can backfill it later outside the migration, or add your own RawSql operation when you are ready.
• Very large backfills can take time. Review the generated SQL and apply it during a low-traffic window if needed.
• After applying the reviewed plan, update readers and writers to the new table version before you remove the old one.
• Cleanup is a separate reviewed migration, not an automatic side effect of the version bump.

Related

• Modeling Tables
• Planned Migrations
• Applying Migrations