Data Migration Is the Project, Not a Workstream

A new enterprise system can be configured correctly and still fail because it begins with incomplete customers, duplicated suppliers, incoherent units, unreconciled balances or open transactions in impossible states. Migration is not moving rows between schemas. It decides what the organisation believes about its products, counterparties, assets and obligations at a cut-off point, then proves that the target represents those beliefs faithfully. This work exercises almost every important design decision and often uncovers policies that were implicit in legacy code or spreadsheets. It belongs on the critical path from discovery onwards, with business ownership equal to its technical engineering.

Profile before promising the mapping

Source documentation is rarely an accurate description of source data. Profile actual values, nulls, uniqueness, lengths, encoding, date ranges, relationships and frequency distributions. Compare nominal reference codes with what transactions use, and identify records whose meaning depends on application logic rather than stored fields. Profiling should be repeatable against dated extracts so trends and remediation can be measured. Protect sensitive fields in development and restrict access to extracts. The output is not just a defect list; it is a set of design questions. If one supplier identifier represents several legal entities, the target model and business ownership must resolve that meaning before transformation code can be correct.

Define scope by business state

“Migrate orders” is not a scope definition. Specify historical range, open and closed status, cancelled records, attachments, audit history and dependencies. Decide whether open transactions will be reconstructed at line level, loaded as balances or completed in the legacy system. Historical information may remain in a read-only archive if users can access it appropriately and retention requirements are met. Every object needs a system of record, cut-off rule and acceptance criteria. Scope should also cover data created during the project, because cleansing an early extract does not stop new duplicates entering the source. Freeze windows and delta strategies must reflect real operational timing.

• Named business owner and authoritative source for every migrated object.
• Documented mapping, default, rejection and transformation rules.
• Source and target control totals at record and monetary levels.
• Exception workflow with reason, owner and approved disposition.
• Retention and access plan for history that will not enter the target.

Make mappings executable and reviewable

Mapping workbooks are useful for discussion but should not become an unversioned source of production logic. Implement transformations in code or governed tooling with version control, tests and deterministic inputs. Reference mappings need effective dates and ownership. Defaults must be explicit: replacing an unknown tax code with a common one can create a balanced load and a false liability. Reject records when required meaning is absent, then route exceptions for business resolution. Preserve source identifiers and lineage so a target record can be traced to extract, rule version and source row. Idempotent loading allows a rehearsal to be rerun without creating duplicate customers, assets or journals.

Reconciliation proves meaning, not movement

Row counts catch omissions but cannot prove financial or operational correctness. Reconcile totals by relevant dimensions: legal entity, currency, account, warehouse, status and ageing bucket. General-ledger opening balances must bridge to an approved source trial balance, while receivable and payable control accounts must agree to migrated open items. Inventory requires quantities, values and units to align. Hashes or field-level comparisons help for unchanged attributes; transformed attributes need rule-based checks. Explain every difference through a signed exception, not a balancing plug. Reconciliation queries and reports should be automated, repeatable and retained as cutover evidence. If totals only exist in somebody’s spreadsheet, the result is difficult to reproduce or review.

Dry runs are production rehearsals

A migration rehearsal should use the full pipeline, representative volume, production-like security and the intended cutover sequence. Measure extraction, transformation, load, validation and reconciliation times separately. Record defects and rerun after fixes rather than patching the target by hand. Later rehearsals should include delta capture, integration restart, role access, reports and downstream jobs. Business users need to validate recognisable records and execute end-to-end scenarios on migrated data. Each run should reduce unexplained exceptions and timing uncertainty. The purpose is not to demonstrate that a loader executes; it is to prove that people, tools and controls can reach an accepted state within the operational window.

Cutover needs control and reversibility

The cutover plan should define source freezes, final extracts, checksums, dependencies, decision points, communications and authority to stop. Credentials and infrastructure must be prepared without embedding secrets in scripts. Automate restartable stages and record their status so a failure does not require guessing what completed. Establish go or no-go criteria based on reconciliations and critical scenarios, not elapsed effort. Rollback may mean restoring the source, extending a freeze or containing selected processes; the feasible option must be designed and tested in advance. After launch, reconcile deltas and monitor interfaces, duplicates and opening transactions until normal controls take over.

Data quality needs a disposition, not a slogan

Not every source defect should be cleansed in place and not every historical oddity should be imported. Define disposition categories: correct at source, transform under an approved rule, merge with retained lineage, exclude under policy or migrate as an explicit exception. Source correction is preferable when downstream systems also benefit, but it may be impossible during a freeze or where the legacy application cannot represent the target value. Transformation rules should never invent material business meaning. If an owner cannot determine the correct legal entity or amount, the record needs an exception process rather than a convenient default. Measure quality against target use. A missing telephone number may be tolerable for an inactive archival customer but critical for an active delivery workflow; a duplicate supplier can create payment risk regardless of row-count accuracy. Publish exception populations by owner, age and effect on critical scenarios. Sampling helps assess free text and attachments that simple rules cannot judge. Once data enters the target, continue controls for records created through interfaces and user entry, otherwise migration cleansing creates a temporary high point followed by immediate decay. The programme should leave behind validation, stewardship and monitoring that sustain the definitions established during conversion, rather than a one-off clean extract with no operating owner.

Every quality issue needs an approved disposition: correct at source, transform by rule, merge with retained lineage, exclude under policy or carry as an explicit exception. Transformation must not invent a material amount, entity or tax meaning merely to make a load pass. Keep original and corrected values with the rule and approval so later questions remain answerable. Measure quality against target use, since a field can be optional for an archive but essential for an active workflow. After launch, continue validation and stewardship for newly entered records; otherwise a clean conversion is followed immediately by the same uncontrolled behaviour that damaged the source.

Organise the programme around data evidence

Begin profiling during discovery, assign owners while target processes are being designed and deliver the first end-to-end conversion early. Track mapping decisions, data remediation and target configuration as connected dependencies. Require automated reconciliation from every rehearsal and make unresolved differences visible in governance. Use migrated data in testing and training so it receives operational scrutiny. When migration drives the programme rhythm, it reveals whether the target design can represent the business, whether owners can make necessary decisions and whether cutover is physically achievable. That is why data migration is not one workstream among many. It is the repeated integration test for the entire transformation.