Planned Migrations

Migration planning is a new way to have more fine-grained control over HOW database schema changes are applied to your database when you deploy your code into production.

Why planned migrations?

Most database migrations are designed under the assumption that your code is the sole owner of the database schema. In OLAP databases, we have to be more careful and assume that schema changes can happen at any time:

• The database schema is shared with other services (e.g. Change Data Capture services like ClickPipes)
• Other users (e.g. analysts) of the database may have credentials that let them change the schema

This is why the plan is generated from the remote environment, and validated against the live state of the database at the time of deployment. If it detects a drift, it will abort the deployment and require you to regenerate the plan, to make sure you are not dropping data unintentionally.

What this does

• Generates an ordered set of ClickHouse operations and writes them to ./migrations/plan.yaml
• Saves two validation snapshots for drift detection:
  • ./migrations/remote_state.json (state when plan was created)
  • ./migrations/local_infra_map.json (desired state from your local code)
• When enabled, validates state and executes the exact reviewed operations

Prerequisites

[features]
olap = true
ddl_plan = true

Generating a Plan

Once done editing your code in your feature branch, you can generate a plan that diffs your local code against your live remote database:

moose generate migration --url https://<remote-env> --token <api-token> --save

Outputs:

./migrations/plan.yaml
./migrations/remote_state.json
./migrations/local_infra_map.json

What each file contains:

• remote_state.json: The state of the remote database when the plan was generated.
• local_infra_map.json: The state of the local code when the plan was generated.
• plan.yaml: The plan to apply to the remote database based on the diff between the two states.

Review and edit the plan

Open plan.yaml in your PR. Operations are ordered (teardown first, then setup) to avoid dependency issues. Review like regular code. You can also edit the plan to override the default assumptions Moose makes.

# Drop a deprecated column
- DropTableColumn:
    table: "events"
    column_name: "deprecated_field"
 
# Rename a column to match code
- RenameTableColumn:
    table: "events"
    before_column_name: "createdAt"
    after_column_name: "created_at"
 
# Add a new nullable column after created_at
- AddTableColumn:
    table: "events"
    column:
      name: "status"
      data_type: "String"
      required: false
      unique: false
      primary_key: false
      default: null
      annotations: []
      comment: null
    after_column: "created_at"
 
# Change a column type to Nullable(Float64)
- ModifyTableColumn:
    table: "events"
    before_column:
      name: "value"
      data_type: "Float64"
      required: false
      unique: false
      primary_key: false
      default: null
      annotations: []
      comment: null
    after_column:
      name: "value"
      data_type:
        Nullable:
          nullable: "Float64"
      required: false
      unique: false
      primary_key: false
      default: null
      annotations: []
      comment: null
 
# Create a simple view via raw SQL
- RawSql:
    sql:
      - "CREATE VIEW IF NOT EXISTS `events_by_user` AS SELECT user_id, count() AS c FROM events GROUP BY user_id"
    description: "Creating view events_by_user"

When to edit the plan

There are two main reasons to edit the plan:

1. To “override” the default assumptions Moose makes when it cannot infer the intent of your schema changes, such as renaming a column instead of dropping and adding.
2. To add new operations that are not covered by the default assumptions, such as adding a backfill operation to a new column.

Rename a column instead of drop/add

When you rename a column, Moose will default to dropping and adding the column. However, you can override this by using the RenameTableColumn operation:

import { OlapTable } from "@514-labs/moose-lib";
 
interface SourceSchema {
  created_at: Date;
  color: string;
}
 
interface TargetSchema {
 
  createdAt: Date;
  color: string;
}
 
export const sourceTable = new OlapTable<SourceSchema>("source_table");
 
export const targetTable = new OlapTable<TargetSchema>("target_table");
 
 

- DropTableColumn:
    table: source_table
    column_name: created_at
- AddTableColumn:
    table: source_table
    column:
      name: createdAt
      data_type: DateTime
      required: true
      unique: false
      primary_key: false
      default: null
      annotations: []
    after_column: color

In the plan, you can override this by using the RenameTableColumn operation:

created_at: 2025-08-20T05:35:31.668353Z
  - RenameTableColumn:
      table: source_table
      before_column_name: created_at
      after_column_name: createdAt

Add a backfill operation to a new column

When you add a new column, Moose will default to backfilling the column based on the value in the default field.

If your field is a DateTime, you can edit the plan to set the default value to the current timestamp:

- AddTableColumn:
    table: "source_table"
    column:
      name: "created_at"
      data_type: "DateTime"
      required: false
      unique: false
      default: NOW ## Specify the default value to the current timestamp

You can also override the the default behavior by using the RawSql operation to define your own custom backfill logic:

- AddTableColumn:
    table: "source_table"
    column:
      name: "created_at"
      data_type: "DateTime"
      required: false
      unique: false
      default: null
- RawSql:
    sql:
      - "UPDATE events SET created_at = toDateTime(created_at_ms / 1000) WHERE created_at IS NULL"
    description: "Backfill created_at from created_at_ms"

Apply the plan when deploying to production

When you deploy your code to production, Moose will first validate that the snapshot of your database that was taken when you generated the plan is still the same as the current database state. If it is not, Moose will abort the deployment. If it is, Moose will execute the plan in plan.yaml against your production database.

Execution rules:

• If current tables in your live production database differ from remote_state.json, Moose aborts (remote drift since planning).
• If desired tables in your local code differ from local_infra_map.json, Moose aborts (code changed since planning).
• If both match, plan.yaml operations are executed in order against ClickHouse.

Troubleshooting

• Failure to connect to remote database? Make sure you have your admin API key setup correctly
• Plan rejected due to drift: Re-generate a plan against the current remote, review, and retry.
• No execution: Ensure ddl_plan = true and ./migrations/plan.yaml exists.
• OLAP disabled: Ensure [features].olap = true.