One-off data migrations - out of a legacy system, into a new platform, between two products after an acquisition - are treated as a scripting exercise and are really a verification exercise. Writing the transformation is straightforward. Being able to demonstrate, to a finance director who will not sign off otherwise, that every record arrived and arrived correctly, is where the time goes.
Profile the source before writing anything, because the data is never what the schema says. Count rows per table. Check for nulls in columns documented as mandatory. Look at the distinct values in every status and type field - you will find values nobody remembers introducing. Find the duplicates, the orphaned children whose parents were deleted years ago, the dates in the future and the ones in 1970. Every one of these needs a decision, and finding them during the cutover rather than during profiling is what turns a weekend into a fortnight.
Then decide explicitly what happens to bad data, in writing, with the business rather than alone. Records that fail validation can be rejected and reported, transformed to a default, or migrated as-is into a quarantine state. All three are legitimate; what is not legitimate is a script that silently skips them, which is how a migration reports success while ten thousand records never arrived.
Build reconciliation before you build the migration. Row counts per entity on both sides. Sums of every monetary column. Counts grouped by status, by date range, by customer. A sample of individual records compared field by field. This is the artefact that gets signed off, and knowing what you must prove shapes how you write the migration - it is much harder to add reconciliation to a script that has already run.
Run it repeatedly against a copy long before the real cutover. A migration you have executed once, on the night, is a migration whose runtime you are guessing at. Rehearsals tell you how long it takes on full production volume - which is the number the cutover plan depends on - and each one surfaces another category of unexpected data. Make the script idempotent so re-running it is safe, because you will re-run it many times.
For the cutover itself, write down the rollback before you write the go-live steps: what still runs, how long the old system stays available, what happens to records created in the new system if you go back, and who decides. Where the migration is large, migrate historical data ahead of time and only the recent delta during the window - it converts an eight-hour outage into a one-hour one, and the difference is usually whether you need a weekend at all.
