Data Migration Strategies

Definition

Data migration moves information from a source system to a target with a different structure and frequently a different semantics. The technical transfer is straightforward; the mapping and the quality problems are not.

The strategies

• Big bang. All data moved in a window. Simple, high risk, requires a freeze and a tested rollback. Suitable only for small volumes or genuinely tolerant systems.
• Phased by entity or segment. Migrate customers by region, or products by category. Lower risk, and requires both systems to operate concurrently with a routing rule.
• Trickle with dual running. Both systems live, synchronised, cut over gradually. Lowest risk, highest complexity, requires reconciliation throughout.
• Migrate on access. Move a record the first time it is touched, leaving cold data behind. Elegant, and leaves a long tail that must eventually be dealt with.

What actually makes it hard

Data quality. The source contains records that violate the target's constraints: missing mandatory fields, invalid references, duplicates, values in free-text fields that should have been structured. Every one needs a decision — fix, default, reject, or migrate to a quarantine — and those decisions are business decisions, not technical ones.

Profile the source data early. Discovering the quality problem during the cutover window is the classic failure.

Semantic mapping. A status of 3 in the source means something the target expresses differently, and the mapping is frequently not one-to-one. Domain experts must own these decisions.

History. How much do you bring? Full history is expensive and frequently unnecessary; none makes reporting discontinuous. A common answer is full detail for a recent window and aggregates beyond it — decided by the business, not by engineering.

Identifiers. New identifiers break external references, links and integrations. Preserving old ones carries the source's constraints forward. A mapping table is usually required and must persist.

Reconciliation

Not optional, and not only at the end: counts by entity and by segment, financial totals, and a sample of records compared field by field. Sign-off by the business owner rather than by the migration team.

Failure scenarios

• Quality assessed during cutover, so the window is spent on decisions.
• Mapping decided by engineers without domain input.
• No reconciliation, so discrepancies are found by a customer.
• The migration tool used as the specification, so nobody can state what was transformed.
• The long tail of unmigrated records never addressed.

Interview question

"The source data contains records that violate the target's constraints. How do you handle it and who decides?"