Why data migration needs its own attention
In most system migrations, data is both the most valuable asset and the most common source of delay. Software can often be configured to a schedule. Data, by contrast, reveals its problems only when you start working with it. Moving it accurately involves four linked activities: scoping, mapping, cleansing and validation.
These steps apply whether you are moving between two versions of the same product, between different products, or merging several sources. The detailed work differs, but the logic is similar.
Step 1: Decide what to migrate
Not everything needs to move. Migrating all history can increase cost, extend cutover time and bring old errors into a new system. Common choices include:
- Migrate in full for records that are active or legally required to be live.
- Migrate a summary for older history that is only needed for reporting.
- Archive without migrating for data that must be kept but is rarely used.
- Leave behind for data that has no continuing value and no retention requirement.
Retention decisions should be made with the people responsible for records and legal obligations, using any existing data retention schedules. This article is general information, not legal advice, so check obligations with qualified advisers.
Step 2: Map the fields
A data mapping is a document that says, for each piece of data in the target, where it comes from in the source and how it is transformed. A simple mapping line might state:
| Source field | Target field | Rule |
|---|---|---|
| Customer_Name | Account name | Trim spaces, convert to standard capitalization |
| Status (A, I, X) | Status (Active, Inactive, Closed) | Map A to Active, I to Inactive, X to Closed |
| Phone_1, Phone_2 | Primary phone, Secondary phone | Copy as is, flag blanks |
(The fields above are illustrative.)
Good mappings cover:
- Direct copies, where nothing changes.
- Transformations, such as format changes, splitting or combining fields.
- Code translations, where lists of values differ between systems.
- Defaults, for target fields that have no source.
- Unmapped source fields, with a recorded decision to drop each one.
Have business users, not only technical staff, review the mapping. They are the ones who know that “status X” really means something specific. Where several systems feed one target, agree a single set of definitions first, which is where data governance and master data management practices come in.
Step 3: Profile and cleanse
Data profiling means examining the actual data for patterns and problems. Typical findings correspond to common data quality issues:
- Duplicate records.
- Missing required values.
- Inconsistent formats, such as dates written in different ways.
- Values outside permitted ranges.
- Orphaned records that refer to something that no longer exists.
- Free text where the target expects a fixed list.
Cleansing can happen in the source before extraction, in a staging area during transformation, or after load. Cleansing in the source is often best because the fix then persists, but it depends on whether the old system will keep running and who has time. Whichever you choose, keep a record of what was changed and why.
Decide in advance who may approve judgement calls, such as whether two similar records are the same customer. Clear data stewardship helps here, since without named stewards these decisions tend to stall or be made inconsistently.
Step 4: Extract, transform and load in trials
Run the whole data process several times before the real cutover:
- Early trial. Expect errors; the purpose is to find them.
- Further trials. Fix issues, measure how long the process takes and refine.
- Dress rehearsal. Run the final process under realistic conditions and timing, including the steps that will be done on the cutover weekend.
Repeated runs also show whether the process is repeatable. Anything done manually should be documented, since the person who did it in trial may not be available on the day.
Step 5: Validate and reconcile
Validation asks whether the data in the target is complete and correct. Common checks include:
- Record counts. Do the number of records in the source and target agree, allowing for deliberate exclusions?
- Control totals. Do key numeric fields, such as balances or quantities, sum to the same figure?
- Sample comparisons. Pick records at random and compare them field by field.
- Business rule checks. Are there any records that break the new system’s rules?
- Relationship checks. Do linked records, such as orders and customers, still connect?
- User review. Do the people who know the data recognize it?
A simple, illustrative reconciliation: suppose the old system holds 10,000 customer records, and 400 are deliberately excluded as inactive for over a decade. You would expect 9,600 in the new system. If you see 9,590, something needs explaining, even though the discrepancy is small. Explaining small differences matters because they can point to systematic problems.
Record the results of each check and the people who accepted any remaining differences.
Step 6: Plan for what happens after load
Even with careful validation, issues appear after go-live. Plan to:
- Keep the source data in a protected, read-only state for a set period.
- Provide a way for users to report suspected data errors.
- Assign who fixes them and how quickly.
- Decide how long the old data must be held before decommissioning.
Common mistakes
- Starting data work too late, as it tends to take longer than expected.
- Treating mapping as a technical task with no business input.
- Skipping the cleansing and importing known problems.
- Validating only counts, not meaning.
- Not securing extracts, which may contain sensitive data. Follow your organization’s security rules, and ask qualified security staff how to handle copies.
Data migration is rarely glamorous, but careful work here is what lets people trust the new system on the first day.