When an organization replaces a system, the new software is the visible part. The data that has to move into it is the part that most often delays go-live. Old data is messier than anyone remembers, business rules are embedded in places no one documented, and the cutover has to happen without losing anything people rely on.
Key takeaways
- Profile the real data at the start; do not plan from the schema alone.
- Build the migration as a repeatable, re-runnable process, not a one-time script.
- Reconcile at several levels: counts, totals, keys and sampled records.
- Rehearse the full cutover, including rollback, before the real one.
Why migrations go wrong
- Data quality surprises: duplicates, missing values, inconsistent codes and records that violate rules the new system enforces.
- Hidden business logic: meanings encoded in status values, special cases and reports that only experienced staff understand.
- Moving targets: the source keeps changing while you migrate.
- One-shot thinking: a script that must work perfectly the first time, on cutover weekend.
1. Profile early
Analyze the real source data in the first weeks: row counts, null rates, distinct values, key uniqueness, orphaned references, date ranges and outliers. Share the findings with business owners so they can decide how to treat the problem records. The results shape your schedule more than any estimate made from a data dictionary.
2. Define mapping and rules together with the business
Document how each source field maps to the target, which transformations apply and what happens to values that do not fit. Keep the mapping in a reviewable form, ideally as code and configuration under version control, with business sign-off on the rules that affect meaning.
3. Build a repeatable, idempotent process
Design the migration so it can be run many times against fresh extracts without producing duplicates or corrupting the target. Key every load on stable identifiers and use upsert or merge semantics so a re-run converges to the same result. Make steps restartable, so a failure in the middle can be resumed. This turns rehearsals into routine runs and makes the final cutover far less risky.
4. Quarantine, do not guess
Records that fail validation should not silently disappear or be force-fit. Route them to a quarantine area with the reason recorded, report them to the data owners and track their resolution. A clear exceptions report builds trust and prevents surprises after go-live.
5. Reconcile in layers
- Counts: records read, loaded, rejected and quarantined must add up.
- Control totals: sums of amounts, balances or quantities match between source and target.
- Key coverage: every expected identifier exists in the target, with none unexpected.
- Field-level comparison: compare sampled records, and all records for critical fields, using hashes or direct comparison.
- Business checks: reports and workflows that people use produce the same results in the new system.
Automate these checks and produce a report every run, so progress is measurable and sign-off is based on evidence.
6. Protect sensitive data in test environments
Rehearsals need realistic data, which is often sensitive. Use masked or synthetic data where you can, restrict access where you cannot, and follow the same security and retention rules as production.
7. Rehearse the cutover
Run full dress rehearsals that include extraction, transformation, load, reconciliation, application checks and the time each step takes. Define a freeze window, the go/no-go criteria, who decides and exactly how to roll back. Consider a period of parallel running, or incremental synchronization up to the cutover, to shorten the final switch.
8. Plan for after go-live
Keep the legacy system available read-only for a defined period, monitor closely and have a path to fix migrated data quickly. Archive the migration artifacts and reconciliation evidence, which are valuable for audits.
This article offers general engineering perspective. It does not describe a specific client engagement.