clickhouse-migration-deep-dive

name	clickhouse-migration-deep-dive
description	Execute ClickHouse schema migrations — ALTER TABLE operations, data migration between engines, versioned migration runners, and zero-downtime schema changes. Use when modifying ClickHouse schemas, migrating data between tables, or implementing versioned migration workflows. Trigger with "clickhouse migration", "clickhouse ALTER TABLE", "clickhouse schema change", "migrate clickhouse", "clickhouse add column", or "clickhouse schema migration".
allowed-tools	Read, Write, Edit, Bash(npm:*), Bash(node:*), Bash(kubectl:*)
version	1.7.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","database","analytics","clickhouse","olap"]
compatibility	Designed for Claude Code

ClickHouse Migration Deep Dive

Overview

Plan and execute ClickHouse schema migrations: column changes, engine migrations, ORDER BY modifications, and versioned migration runners. ClickHouse ALTER operations behave unlike PostgreSQL/MySQL — most are asynchronous mutations that rewrite data parts in the background, and some changes (ORDER BY, engine) require full table recreation. This skill walks the safe path for each.

Prerequisites

• ClickHouse admin access
• Backup of production data (see clickhouse-prod-checklist)
• Test environment for validation

Instructions

Follow these steps in order. SQL and runner code for each step live in the linked reference files — keep them open while you work.

Step 1: Classify the operation

Decide whether your change is lightweight (instant, metadata only) or a heavyweight mutation (rewrites parts in the background):

-- Lightweight (instant): ADD COLUMN, RENAME COLUMN, COMMENT COLUMN
ALTER TABLE events ADD COLUMN country LowCardinality(String) DEFAULT '';

-- Heavyweight (mutation): MODIFY COLUMN, DROP COLUMN, DELETE, UPDATE
ALTER TABLE events MODIFY COLUMN properties String CODEC(ZSTD(3));

-- Always monitor mutation progress
SELECT database, table, mutation_id, is_done, parts_to_do
FROM system.mutations WHERE NOT is_done ORDER BY create_time;

Step 2: Run column operations

Use Edit/Write to author the ALTER TABLE statements, then apply them. Add/modify/drop columns, set materialized defaults, and attach codecs. Full DDL semantics and every column-operation variant: DDL & column operations.

Step 3: Recreate the table for ORDER BY / engine changes

ClickHouse has no MODIFY ORDER BY and no in-place engine change. Create a new table, INSERT ... SELECT the data, then atomically to swap. Full create → copy → swap → verify → drop recipe for both cases: .