Data-preserving structural migrations

A sorting-key or partition-key change can produce a generated DROP TABLE / CREATE TABLE plan. Applying that plan discards the old rows. This recipe replaces that proposal with reviewed SQL migrations: copy → validate → atomic swap, keeping the original table for recovery.

The example continues the existing-database adoption walkthrough through its first ingest_source change. It uses the same local analytics database, events table with 1,000 unique IDs, ordinary event_counts view, Bun project, and exported credentials. Finish that guide first; status, drift, check, and generate --dryrun should all be clean. The shell snippets use rg (ripgrep) to locate each newly generated migration; alternatively set the variable to the exact file path printed by generate.

Choose the right backfill mechanism

Goal	Mechanism
Populate a materialized view’s destination from historical source rows	The backfill plugin’s MV replay, with its plan/checkpoint workflow
Replay data within one supported table/engine	The backfill plugin’s table replay, after reviewing its engine and duplicate-protection requirements
Transform source rows into a replacement table and change its layout	A tracked SQL migration, as below

The plugin’s same-table replay is not a general source-to-shadow-table transformer. Completing and resuming an already finished backfill proves a no-op; it does not prove interrupted chunks cannot duplicate data. For large workloads, test retry and engine semantics separately.

Preconditions and intended result

This recipe is for a single-node Atomic database with writers paused. Pause ingestion jobs, application writes, and mutations before the copy, and keep them paused through verification or rollback. Allow enough disk for both copies. Unique immutable id values make the copy retryable. The example has an ordinary view; materialized-view dependencies, replicated/distributed tables, concurrent ingestion, and zero-downtime cutovers need a separate design.

The final events table will:

• partition by toYYYYMM(occurred_at);
• sort and use a primary key of (user_id, occurred_at, id);
• store lowerUTF8(event_type) for historical rows;
• preserve every other field and retain the original rows/layout as events_previous.

Update your writers/readers to accept lowercase event types before resuming them. This example hardcodes analytics in both schema and SQL; for another target, change every database-qualified reference deliberately. A connection database alone does not rewrite SQL.

Check the starting state:

bunx chkit query "SELECT name, engine FROM system.databases WHERE name = 'analytics'" --json
bunx chkit query "SELECT name, sorting_key, partition_key FROM system.tables WHERE database = 'analytics' AND name IN ('events', 'events_previous') ORDER BY name" --json
bunx chkit query "SELECT count() AS rows, uniqExact(id) AS ids FROM analytics.events" --json

Expect Atomic, only events with sorting key id and no partition key, and both counts 1000. The migration asserts these conditions again. Confirm your deployed application is compatible with the intended transformation before proceeding.

1. Copy and validate in a tracked migration

Keep the current schema definitions unchanged. Scaffold a manual migration; --empty does not advance the snapshot:

bunx chkit generate --empty --name copy_events
COPY_MIGRATION=$(rg --files chkit/migrations | rg '_copy_events.sql$')

Replace that unapplied file’s contents with:

cat > "$COPY_MIGRATION" <<'SQL'
-- chkit-migration-format: v1
-- log: Writers must remain paused until cutover verification completes.

-- operation: validate_source key=table:analytics.events risk=caution
SELECT throwIf(
  (SELECT count() FROM system.databases WHERE name = 'analytics' AND engine = 'Atomic') != 1
  OR (SELECT count() FROM system.tables WHERE database = 'analytics' AND name = 'events'
      AND sorting_key = 'id' AND partition_key = '') != 1
  OR (SELECT count() FROM analytics.events) != 1000
  OR (SELECT uniqExact(id) FROM analytics.events) != 1000,
  'Expected the original Atomic database, table layout, and 1000 unique IDs'
);

-- operation: create_table key=table:analytics.events_previous risk=safe
CREATE TABLE IF NOT EXISTS analytics.events_previous (
  id UInt64,
  user_id UInt64,
  event_type String,
  occurred_at DateTime64(3),
  properties String DEFAULT '{}',
  ingest_source String DEFAULT 'legacy'
) ENGINE = MergeTree
PARTITION BY toYYYYMM(occurred_at)
PRIMARY KEY (user_id, occurred_at, id)
ORDER BY (user_id, occurred_at, id);

-- operation: load_table_data key=table:analytics.events_previous risk=caution
INSERT INTO analytics.events_previous
  (id, user_id, event_type, occurred_at, properties, ingest_source)
SELECT id, user_id, lowerUTF8(event_type), occurred_at, properties, ingest_source
FROM analytics.events
WHERE id NOT IN (SELECT id FROM analytics.events_previous);

-- operation: validate_data key=table:analytics.events_previous risk=caution
SELECT throwIf(
  (SELECT count() FROM analytics.events_previous) != 1000
  OR (SELECT uniqExact(id) FROM analytics.events_previous) != 1000
  OR (SELECT count() FROM (
    SELECT id, user_id, event_type, occurred_at, properties, ingest_source, sum(delta) AS balance
    FROM (
      SELECT id, user_id, lowerUTF8(event_type) AS event_type, occurred_at, properties,
             ingest_source, toInt64(1) AS delta FROM analytics.events
      UNION ALL
      SELECT id, user_id, event_type, occurred_at, properties,
             ingest_source, toInt64(-1) AS delta FROM analytics.events_previous
    )
    GROUP BY id, user_id, event_type, occurred_at, properties, ingest_source
    HAVING balance != 0
  )) != 0,
  'Copy differs from the exact expected transformed row multiset'
);
SQL

The explicit column list preserves millisecond timestamps and all other original values. With writers paused and unique IDs, the missing-ID filter can continue a partial insert without copying those IDs again. The exact multiset comparison catches duplicates, missing rows, or altered fields; a matching row count or checksum alone would be weaker.

Review and apply:

cat "$COPY_MIGRATION"
bunx chkit migrate --json
bunx chkit migrate --apply
bunx chkit status --json

Do not cut over unless the copy migration succeeded. There is temporarily an extra live table not yet in the snapshot, so a live drift check at this intermediate point is expected to report it. The name events_previous currently holds the new layout; after the swap it holds the original.

2. Describe the final state, then replace the destructive proposal

Replace the practice fixture’s src/db/schema/pulled.ts with the final definitions below. In a real project retain every other managed definition; do not overwrite unrelated tables/views.

import { schema, table, view } from '@chkit/core'

const columns = [
  { name: 'id', type: 'UInt64' },
  { name: 'user_id', type: 'UInt64' },
  { name: 'event_type', type: 'String' },
  { name: 'occurred_at', type: 'DateTime64(3)' },
  { name: 'properties', type: 'String', default: '{}' },
  { name: 'ingest_source', type: 'String', default: 'legacy' },
]

const events = table({
  database: 'analytics', name: 'events', engine: 'MergeTree()', columns,
  partitionBy: 'toYYYYMM(occurred_at)',
  primaryKey: ['user_id', 'occurred_at', 'id'],
  orderBy: ['user_id', 'occurred_at', 'id'],
  settings: { index_granularity: '8192' },
})

// After EXCHANGE this name retains the original layout and original values.
const previous = table({
  database: 'analytics', name: 'events_previous', engine: 'MergeTree()', columns,
  primaryKey: ['id'], orderBy: ['id'],
  settings: { index_granularity: '8192' },
})

const counts = view({
  database: 'analytics', name: 'event_counts',
  as: 'SELECT event_type, count() AS total FROM analytics.events GROUP BY event_type',
})

export default schema(events, previous, counts)

Generate the schema transition. This updates snapshot.json to the final definitions, but the emitted SQL must be replaced before applying it:

bunx chkit generate --name cutover_events
CUTOVER_MIGRATION=$(rg --files chkit/migrations | rg '_cutover_events.sql$')
cat "$CUTOVER_MIGRATION"

Expect a destructive proposal for the key change. Do not apply it. Replace the whole unapplied migration, including obsolete operation markers, with the guarded swap below. Do not edit the already applied copy migration.

cat > "$CUTOVER_MIGRATION" <<'SQL'
-- chkit-migration-format: v1
-- log: Reviewed atomic cutover; original data is retained as events_previous.

-- operation: validate_cutover key=table:analytics.events risk=caution
SELECT throwIf(
  (SELECT count() FROM system.tables WHERE database = 'analytics' AND name = 'events'
      AND sorting_key = 'id' AND partition_key = '') != 1
  OR (SELECT count() FROM system.tables WHERE database = 'analytics' AND name = 'events_previous'
      AND sorting_key = 'user_id, occurred_at, id'
      AND partition_key = 'toYYYYMM(occurred_at)') != 1
  OR (SELECT count() FROM analytics.events) != 1000
  OR (SELECT count() FROM analytics.events_previous) != 1000,
  'Expected unswapped tables; inspect live state before retrying a cutover'
);

-- operation: exchange_tables key=table:analytics.events risk=caution
EXCHANGE TABLES analytics.events AND analytics.events_previous;
SQL

Keep writers paused between the successful exact copy validation and this swap. Review the final file and pending plan, then apply:

cat "$CUTOVER_MIGRATION"
bunx chkit migrate --json
bunx chkit migrate --apply

EXCHANGE TABLES swaps both names atomically in an Atomic database. Retaining the backup in the schema keeps it managed and avoids unexplained drift. generate already recorded the intended final definitions; no manual snapshot editing, snapshot rebuild, or journal manipulation is needed.

3. Verify before resuming writers

bunx chkit status --json
bunx chkit drift --json
bunx chkit check --json
bunx chkit generate --dryrun --json
bunx chkit query "SELECT name, sorting_key, partition_key FROM system.tables WHERE database = 'analytics' AND name IN ('events', 'events_previous') ORDER BY name" --json
bunx chkit query "SELECT count() AS rows, uniqExact(id) AS ids, countIf(event_type != lowerUTF8(event_type)) AS non_normalized FROM analytics.events" --json
bunx chkit query "SELECT * FROM analytics.event_counts ORDER BY event_type" --json

Expect no pending migrations, no drift, a passing check, zero generated operations, the new keys on events, and the old keys on events_previous. Row/ID counts are 1000, non_normalized is 0, and the view has pageview: 500 and signup: 500.

Verify the exact transformed row multiset again after the swap:

bunx chkit query "
SELECT throwIf((SELECT count() FROM (
  SELECT id, user_id, event_type, occurred_at, properties, ingest_source, sum(delta) AS balance
  FROM (
    SELECT id, user_id, lowerUTF8(event_type) AS event_type, occurred_at, properties,
           ingest_source, toInt64(1) AS delta FROM analytics.events_previous
    UNION ALL
    SELECT id, user_id, event_type, occurred_at, properties,
           ingest_source, toInt64(-1) AS delta FROM analytics.events
  )
  GROUP BY id, user_id, event_type, occurred_at, properties, ingest_source
  HAVING balance != 0
)) != 0, 'Post-cutover rows differ from the transformed original')" --json

The query must succeed. Exercise application reads with writers still paused, then resume only compatible writers.

Commit the final schema, both reviewed migrations, and the updated snapshot together. Another environment must run the same committed migration sequence against the same original schema/data assumptions; do not regenerate on deployment. The hardcoded 1000 assertions deliberately make this practice recipe fail on a different dataset—adapt and review them before using it elsewhere.

Recovery and retries

Migrations are not a transaction across statements. If any command fails, inspect status, both table layouts, row contents, and chkit’s operation recovery output before acting. Never assume a failed command means no statement ran.

• Before cutover: keep writers paused. The original events remains authoritative. Fix the cause and review failed-migration recovery; do not change an already applied file. A partial copy must pass the exact validation before proceeding.
• An uncertain swap: do not rerun migrate --apply until you have inspected both live layouts and operation/journal state. A lost acknowledgement can leave the swap executed but unrecorded, and automatic recovery may skip a previously completed guard. Repeating EXCHANGE then swaps the tables back. The guard protects a fresh execution; it is not a guarantee against replaying an uncertain swap. Follow the failed-migration recovery procedure for the observed state before resuming deployment.
• Rollback before writers resume: after a fully acknowledged, journaled cutover, use the new tracked rollback below. An uncertain/failed cutover must first be reconciled using the failed-migration recovery procedure; do not layer a rollback onto ambiguous journal state.
• After writers resume: the backup is stale. A simple swap would lose new writes; design a reconciliation/backfill before rollback.

Keep the backup until your recovery window closes. Removing it later is a separate reviewed schema change/migration that drops only events_previous; it is destructive and requires the appropriate explicit approval/CLI flag. The backup doubles storage until then.

Optional: tracked rollback before writes resume

Use this only after the cutover migration is recorded as applied, with writers still paused. Change the two table declarations in pulled.ts to the following; retain the existing columns, counts, and export default schema(events, previous, counts):

const events = table({
  database: 'analytics', name: 'events', engine: 'MergeTree()', columns,
  primaryKey: ['id'], orderBy: ['id'],
  settings: { index_granularity: '8192' },
})

const previous = table({
  database: 'analytics', name: 'events_previous', engine: 'MergeTree()', columns,
  partitionBy: 'toYYYYMM(occurred_at)',
  primaryKey: ['user_id', 'occurred_at', 'id'],
  orderBy: ['user_id', 'occurred_at', 'id'],
  settings: { index_granularity: '8192' },
})

Generate the corresponding snapshot transition, then replace its destructive SQL with the reviewed reverse swap. These are new files; the copy and cutover files remain immutable.

bunx chkit generate --name rollback_events
ROLLBACK_MIGRATION=$(rg --files chkit/migrations | rg '_rollback_events.sql$')
cat "$ROLLBACK_MIGRATION"
cat > "$ROLLBACK_MIGRATION" <<'SQL'
-- chkit-migration-format: v1
-- log: Rollback before writers resume; restores original rows and layout.

-- operation: validate_rollback key=table:analytics.events risk=caution
SELECT throwIf(
  (SELECT count() FROM system.tables WHERE database = 'analytics' AND name = 'events'
      AND sorting_key = 'user_id, occurred_at, id'
      AND partition_key = 'toYYYYMM(occurred_at)') != 1
  OR (SELECT count() FROM system.tables WHERE database = 'analytics' AND name = 'events_previous'
      AND sorting_key = 'id' AND partition_key = '') != 1
  OR (SELECT count() FROM analytics.events) != 1000
  OR (SELECT count() FROM analytics.events_previous) != 1000,
  'Expected completed cutover and unchanged backup; inspect before rollback'
);

-- operation: exchange_tables key=table:analytics.events risk=caution
EXCHANGE TABLES analytics.events AND analytics.events_previous;
SQL
cat "$ROLLBACK_MIGRATION"
bunx chkit migrate --json
bunx chkit migrate --apply
bunx chkit status --json
bunx chkit drift --json
bunx chkit check --json
bunx chkit generate --dryrun --json
bunx chkit query "SELECT name, sorting_key, partition_key FROM system.tables WHERE database = 'analytics' AND name IN ('events', 'events_previous') ORDER BY name" --json
bunx chkit query "SELECT * FROM analytics.event_counts ORDER BY event_type" --json

Expect events back on the original unpartitioned id layout and the view back to PageView: 500 and Signup: 500, with clean chkit checks. Compare the original-column rows against the adoption guide’s saved originals before resuming compatible writers. If rollback’s acknowledgement is uncertain, the same no-blind-retry rule applies.