How to plan a data migration when the source data is messy

Profile before you map

A field named status may contain active, Active, current, blank, and values no one remembers creating. Dates may mix formats. Email fields may contain two addresses. The schema alone does not reveal any of this.

Profile the actual records: counts, blanks, distinct values, invalid formats, duplicates, relationship gaps, and unusually long values. This turns vague concerns about “dirty data” into a finite set of decisions.

Write a transformation contract

For each destination field, name its source, transformation, default, validation rule, and owner. Also state which records will be excluded and why. A blank destination should never leave the team guessing whether data was absent, rejected, or forgotten.

• Preserve the original source identifier for traceability.
• Define precedence when two sources disagree.
• Separate safe normalization from business judgment.
• Route ambiguous matches to review instead of guessing.
• Version the mapping so a changed rule is visible.

Rehearse the cutover

Run the migration against a representative copy and record how long extraction, transformation, loading, and verification take. Test a rollback or safe rerun. If the source will continue changing, define the freeze window or incremental catch-up process.

A rehearsal should produce an exception report, not just a success message. Review samples from ordinary records and edge cases with the people who understand the business data.

Reconcile at business level

Row counts matter, but totals and relationships often matter more. Compare customer counts by status, open invoice totals, orders by month, attached documents, and parent-child links. Agree on tolerances before cutover.

Keep the source-to-destination crosswalk and the final reconciliation evidence. That record makes post-launch questions answerable and the handoff defensible.