Data Migration from Legacy Systems

Moving data out of a legacy system is often the highest-risk part of any modernisation project. The logic and interfaces can be rebuilt, but if the data is lost, corrupted or misaligned, the business cannot operate normally. This guide covers how to plan, validate and execute a legacy data migration without relying on optimism.

Planning Data Migration from Legacy Databases

Migration planning starts long before any data moves. The first task is understanding what actually lives in the legacy system, which is rarely as simple as reading a schema diagram. Older systems accumulate hidden logic: default values applied at the application layer rather than the database, business rules embedded in stored procedures, and data that only makes sense when read in a specific order.

A practical planning approach follows these steps:

• Profile the source data. Run queries to understand record counts, null frequencies, date ranges and distinct values in key fields. This reveals surprises early, such as a status field with thirty unexpected values or a customer table where ten per cent of records have no email address.
• Map every source field to a target field. Document the transformation rule for each column, even when it is a direct copy. Where no target exists, decide whether the data should be discarded, merged into another field or stored in a generic notes field.
• Identify dependencies between tables. Legacy databases often enforce relationships in application code rather than foreign keys. If the migration script loads child records before parents, the result will be orphaned data in the new system.
• Establish data ownership. Someone in the business must sign off on what each field means today and what it should mean in the new system. Technical teams cannot make these decisions alone.
• Agree on a migration window. Legacy systems are often slower to export data than expected. A full extract that takes forty minutes on a quiet test database may take several hours under load.

A common planning mistake is treating the migration as a purely technical task. The decisions about which historical records to keep, how to handle partial information and what constitutes a duplicate customer are business decisions that affect every downstream process.

Data Validation During Legacy Migration

Validation is not a single checkpoint at the end. It should be built into every stage: after extraction, after transformation and after loading. Each stage introduces its own class of errors.

Extract validation

Compare the row and column counts from the export against the source database. If the legacy system holds 142,000 order records, the extract file must contain 142,000 rows. Also check that the extract completed without truncation errors, which some database tools silently apply to large text fields.

Transform validation

Apply rules to detect transformation failures. If a date format conversion is applied, any row where the resulting date is null or falls outside an expected range signals a problem. If a currency field is multiplied by an exchange rate, spot-check the results against known values.

Load validation

Once data is in the target system, run the same aggregate checks: record counts, sum of monetary fields, distinct values in status columns. Then test referential integrity by checking that every child record points to a valid parent.

Practical validation also means picking a sample of real records, perhaps fifty to a hundred, and walking through them manually in both systems. Automated checks catch quantity errors; manual inspection catches subtle misinterpretations, such as a notes field being loaded into the wrong column or a yes/no flag being inverted.

Document every validation check and its result. If a stakeholder later asks whether the migration was successful, a list of passed checks is considerably more useful than a general assurance.

Handling Inconsistent Data in Legacy Systems

Legacy data is rarely clean. Years of manual entry, system changes and workarounds produce inconsistencies that must be resolved before or during migration. The main categories are:

• Formatting inconsistencies. Postcodes entered with and without spaces, phone numbers with varying prefixes, names in different cases. These are straightforward to normalise with rules, but the rules must be agreed in advance.
• Duplicate records. The same customer may appear multiple times with slight variations in spelling or address. Deduplication requires matching rules and a decision on which record becomes the master.
• Missing or default values. Fields left blank or filled with placeholder text such as "TBD" or "0000". The migration plan must specify whether these become null, a default value or trigger a manual review.
• Orphaned references. Records that point to other records which no longer exist, often because of earlier deletions that did not clean up related data.
• Contradictory data. A customer record showing one address in the main table and a different address in the order history. The business must decide which source is authoritative.

The key principle is that cleaning rules are business rules. A technical team can identify the inconsistencies, but cannot decide whether two slightly different customer records represent the same person or two different people. Build a review process into the migration timeline rather than assuming all edge cases can be resolved programmatically.

For large datasets, it is often practical to clean and migrate a subset first, review the results with the business, refine the rules and then run the full migration. This iterative approach catches misunderstandings before they affect the entire dataset.

Parallel Running: Old and New Systems Side by Side

Parallel running means operating both the legacy and new systems simultaneously, comparing outputs to verify that the new system produces correct results. It is a validation technique, not a substitute for thorough testing.

Parallel running is most useful when the new system replaces a process with clear, comparable outputs: invoice generation, stock calculations, reporting figures. If both systems produce the same invoice total for the same input data, confidence increases. If they differ, the discrepancy must be investigated before cutover.

The practical difficulties of parallel running are often underestimated:

• Data entry burden. Staff may need to enter the same transaction in both systems, which is time-consuming and error-prone. If entries are not identical, the comparison is meaningless.
• Timing differences. The two systems may apply business rules at different points, producing results that differ for legitimate reasons rather than errors. These differences must be distinguished from genuine problems.
• Duration. A common question is how long to run in parallel. A few days may miss edge cases that only occur monthly, such as billing cycles. Running for a full billing period is often necessary but costly.
• False confidence. Passing parallel running does not guarantee the new system is correct. It proves it produces the same results as the old system, which may itself contain known errors that the new system was designed to fix.

Parallel running is not always appropriate. For systems where data is primarily read rather than processed, or where the new system deliberately changes the output, other validation methods are more efficient. The decision should be based on what risk remains after testing, not on a default assumption that parallel running is always best practice.

How to Plan a Legacy Data Cutover

The cutover is the moment when the business stops using the legacy system and starts using the new one. A poorly planned cutover can cause extended downtime, data loss or a forced rollback under pressure.

Effective cutover planning addresses the following elements:

• Data freeze. Agree a point after which no changes are made in the legacy system. Any transactions entered after the freeze must be re-keyed into the new system or held in a queue. The freeze window should be as short as possible and communicated clearly to everyone who uses the system.
• Final delta migration. After the initial full migration and before cutover, a second pass captures changes made since the first extract. This requires the legacy system to support change tracking, whether through timestamps, log tables or transaction logs.
• Sequence of activities. Document the exact order of steps: stop legacy system, run final extract, transform and load, run validation checks, start new system, verify key functions. Assign each step to a specific person with a clear completion criterion.
• Communication. Staff need to know precisely when the system will be unavailable, what to do with transactions that arise during the window and how to report problems after cutover.
• Verification. Before declaring the cutover complete, run a defined set of checks: log into the new system, process a test transaction, check a known customer record, run a key report. These checks should be scripted in advance, not improvised on the day.

A realistic cutover plan includes time for things going wrong. If the validation checks fail, there should be a defined investigation period before the rollback decision is triggered. Rushing that decision under time pressure often leads to unnecessary rollbacks or, worse, proceeding with known problems.

Rollback Planning for Legacy Migrations

A rollback plan defines how to revert to the legacy system if the cutover fails. Not every migration needs a full rollback, but every migration needs a deliberate decision about whether one is feasible and what it would involve.

Rollback is straightforward in theory: switch users back to the old system. In practice, several complications arise:

• Data divergence. If users entered transactions into the new system before the problem was detected, those records do not exist in the legacy system. Reverting means either losing those transactions or manually re-keying them.
• Legacy system state. The legacy system must be left in a usable state during the migration window. If hardware was decommissioned, licences were allowed to lapse or access credentials were rotated, rollback may not be possible without significant effort.
• Time pressure. Rollback decisions are made under stress, often late at night, with incomplete information. The plan must specify clear trigger conditions rather than leaving the decision to subjective judgement.

Practical rollback planning includes:

• Defined trigger criteria. For example: rollback if more than five per cent of validation checks fail, or if the new system is unavailable for more than thirty minutes after the planned go-live time. These thresholds should be agreed before cutover day.
• Preserved legacy access. Keep the legacy system running, with current backups, until the new system has been in production for a defined period. This does not mean keeping it fully operational, but ensuring it can be brought back if needed.
• Reconciliation steps. If a rollback occurs, document how to identify and re-enter any transactions that were processed in the new system.
• Post-rollback review. A failed cutover is not the end of the project. The rollback plan should include an immediate review process to understand what went wrong and what must change before the next attempt.

Keep the decision traceable

There are situations where rollback is not a realistic option: the legacy system has already been decommissioned, the data structures have changed irreversibly, or regulatory requirements prevent reverting to the old state. In these cases, the migration plan must reflect that reality and invest more heavily in pre-cutover validation, because recovery after failure will mean fixing the new system in place rather than stepping back.