Run a data migration safely

Goal

A shape change applied to real data: rehearsed on a copy, applied in batches, resumable if it stops, and verified when it finishes.

When you finish the migration is a file you can re-run, not an afternoon you have to survive.

Why bother

A migration is the one piece of automation where “run it again” is not free.

Without this	With this
One statement over the whole table, holding a lock	Batches, each committing
It fails at 80% and you do not know which 80%	It resumes from the last completed batch
You find out it was wrong from a user	You rehearsed it on a copy and counted
No record of what ran	The run names every batch that completed

Before you start

You need	Why
A copy of the data to rehearse against	Non-negotiable. See step 1
A key you can order by	Batching needs a stable cursor
A way to tell migrated rows from unmigrated	So a resume knows where to start

The worked example: plan was a free-text column, and it needs to become one of free, pro, enterprise, with anything unrecognised becoming free.

Step 1 — rehearse on a copy

Do this even when the change looks trivial. Especially then — a trivial change is the one nobody counts afterwards.

# restore last night's backup into a scratch target, then point the migration at it
kis flow -f restore.yaml -v target=scratch

Run the whole migration against scratch, count the result, and read a sample by hand. What you are looking for is not “did it error” but “is the answer right” — a migration that maps every value to free completes perfectly and is wrong.

See Restore from a backup for the restore itself.

Step 2 — make the change idempotent

Write the transform so that running it on already-migrated data changes nothing. That is what makes a resume safe, and it removes the need to know exactly where you stopped.

Flow
Script

- name: transform
  script:
    language: javascript
    code: |
      const VALID = ['free', 'pro', 'enterprise'];
      function main(rows) {
        return rows.map((r) => ({
          id: r.id,
          plan: VALID.includes(r.plan) ? r.plan : 'free',
        }));
      }
    args: ["{{batch}}"]
    setvar: mapped

const VALID = ['free', 'pro', 'enterprise'];

function normalise(row) {
  return { id: row.id, plan: VALID.includes(row.plan) ? row.plan : 'free' };
}

Note what makes it idempotent: a row already holding pro maps to pro. Re-running is a no-op rather than a second transformation, so an interrupted run can simply be started again.

name: migrate-plans
vars:
  api: https://data.example.internal
  batch_size: 500
  after_id: 0
tasks:
  - name: fetch
    http:
      url: "{{api}}/data/customer?filter=id:gt:{{after_id}}&limit={{batch_size}}&sort=id"
      headers:
        Authorization: "Bearer {{token}}"
      setbody: page
    next:
      go: check

  - name: check
    choice:
      rules:
        - condition: "page.items.length > 0"
          next: apply
      default: done

  - name: apply
    http:
      url: "{{api}}/data/customer/bulk"
      method: PATCH
      headers:
        Content-Type: application/json
        Authorization: "Bearer {{token}}"
      payload: "{{mapped}}"
    next:
      go: advance
      set:
        after_id: "{{page.items | last | map: 'id'}}"

  - name: advance
    print:
      message: "migrated through id {{after_id}}"
    next:
      go: fetch

  - name: done
    succeed: true

The cycle fetch → check → apply → advance → fetch is the batching. check is what ends it: an empty page routes to done instead of looping.

function main() {
  const BATCH = 500;
  let afterId = 0;
  let migrated = 0;

  for (;;) {
    const page = JSON.parse(http.get({
      url: `${api}/data/customer?filter=id:gt:${afterId}&limit=${BATCH}&sort=id`,
      headers: { Authorization: `Bearer ${token}` },
    }).body);

    if (page.items.length === 0) break;

    const mapped = page.items.map(normalise);
    const r = http.patch({
      url: `${api}/data/customer/bulk`,
      headers: { 'Content-Type': 'application/json', Authorization: `Bearer ${token}` },
      body: JSON.stringify(mapped),
    });
    if (!r.success) throw new Error(`batch after ${afterId} failed: ${r.error}`);

    afterId = page.items[page.items.length - 1].id;
    migrated += mapped.length;
    log.info(`migrated through id ${afterId} (${migrated} rows)`);
  }

  return { migrated };
}

Order by a stable key and page with id > last, not with an offset. An offset re-reads rows whose position shifted while you were writing; a keyset cursor cannot.

Step 4 — this is where the flow earns its place

Both surfaces above do the same work. They behave differently the moment something goes wrong at row 40,000 of 50,000.

	Flow	Script
On failure	The run records which nodes completed and what `after_id` held	The process ends
To continue	Resume, or re-run with `-v after_id=<last>`	Re-run, and re-derive where you were
If the machine dies	Another worker picks up from the last checkpoint	The run is lost
Visible to an operator	Yes, as a run record	Only in whatever you logged

For a migration on data that matters, write the flow. The script is the right tool for developing the transform — fast loop, real debugger — and step 2 is deliberately the piece that moves between them unchanged.

Step 5 — verify by counting, not by trusting

# every row should now hold one of the three valid values
curl -fsS "$API/data/customer?filter=plan:nin:free,pro,enterprise&limit=0" \
  -H "Authorization: Bearer $TOKEN" | jq .total     # must be 0

# and the distribution should look like the rehearsal predicted
curl -fsS "$API/data/customer?groupBy=plan&limit=0" \
  -H "Authorization: Bearer $TOKEN" | jq

The first query is the invariant. The second is the sanity check — if everything became free, the count of unmigrated rows is still zero and the migration is still wrong.

Verify

Check	Expect
Rows outside the valid set	`0`
Total row count	Unchanged from before the migration
Distribution	Within a row or two of the rehearsal
Re-run the migration	Reports zero rows changed

That last one is step 2 paying off, and it is the check most people skip.

Adapt it

Change	How
Bigger than a few million rows	A data pipeline — it streams instead of paging
The transform needs another service	Add a node; keep the batch loop unchanged
Must not run during business hours	Attach it to a schedule with the `cron:` operation
Needs a rollback	Snapshot first — a backup you took is a rollback you have

Seed a datastore with test data — the same shape, on data that does not matter yet
Restore from a backup — how to get the rehearsal copy
Flows and scripts — why failure is the dividing line