Fintech Development

Zero-Downtime Transaction Table Migration Prompt

Write a data migration plan for moving a legacy transaction table to a new schema in production with zero downtime and no data loss.

Anselm Fowel / Public collection27 Sept 2026

Your prompt

The prompt

You are a fintech engineer who has migrated a live transaction table under real write traffic without downtime, and knows the migration itself is often riskier than the schema problem it's meant to fix.

My legacy transaction table is in [DATABASE], currently has [SCHEMA PROBLEM: NO PROPER CURRENCY HANDLING / MISSING IDEMPOTENCY KEYS / DENORMALIZED IN A WAY THAT BLOCKS RECONCILIATION], holds roughly [ROW COUNT] rows, and receives [WRITE VOLUME] writes per second in production serving [DOWNSTREAM CONSUMERS].

First, design the dual-write phase: how the application writes to both the old and new schema simultaneously, whether that's done in application code or via change-data-capture/triggers, and how you guarantee this dual-write itself doesn't introduce a window where the two tables disagree.

Next, design the backfill of historical data from the old table into the new schema while dual-writes are live, addressing how you avoid backfilling a row that's concurrently being dual-written (double-processing it) and how you track backfill progress so it's resumable if interrupted.

Then design the verification phase: how you programmatically confirm the new table is a complete and correct mirror of the old one before cutting over, including row counts, checksum or hash comparisons per record, and specifically reconciling financial totals (sum of amounts) between old and new as the final sanity check given a subtle bug here means money math is now wrong. Also design the cutover and rollback plan: how reads are switched to the new table (gradually or all at once), what signal triggers a rollback to the old table as source of truth, and how long dual-writes continue after cutover as a safety net before the old table is decommissioned.

Finally, output the migration plan as a phased table (Phase, Actions, Duration, Verification Step, Rollback Trigger), followed by the verification query logic in [SQL/LANGUAGE] for the checksum and financial-total reconciliation step, and a go/no-go checklist for the cutover phase specifically.

2,052 characters

Your prompt

Recent articles