{"id":15209,"date":"2026-09-30T11:22:28","date_gmt":"2026-09-30T11:22:28","guid":{"rendered":"https:\/\/www.sparxitsolutions.com\/blog\/?p=15209"},"modified":"2026-09-30T11:22:28","modified_gmt":"2026-09-30T11:22:28","slug":"legacy-data-migration","status":"publish","type":"post","link":"https:\/\/www.sparxitsolutions.com\/blog\/legacy-data-migration\/","title":{"rendered":"Legacy Data Migration: How to Move Data Without Losing It"},"content":{"rendered":"

A widely quoted claim that most data migration projects fail or exceed their budgets traces back to a 2007 white paper. It has been repeatedly recycled, rounded up and reattributed, while many references omit its publication date.<\/p>\n

The current picture is less dramatic and more useful. Standish Group figures for the 2020 to 2024 cycle put 31% of IT projects as successful, 50% as challenged and 19% as outright failures. Most migrations do not collapse. They arrive late, over budget, or with quality problems found after go-live.<\/p>\n

That matters, because the real risk in a migration is not a statistic. Legacy data migration is the work of moving business records out of a system that can no longer hold them and into one that can, with the meaning, relationships and completeness of those records intact. The failure mode is rarely a dramatic collapse. It is a migration that appears to succeed, goes live, and is found six weeks later to have silently dropped a category of records nobody thought to check.<\/p>\n

Everything below is built around preventing that specific outcome: deciding what actually needs to move, moving it in a sequence you can reverse, and proving it arrived.<\/p>\n

<\/span>What is Legacy Data Migration?<\/span><\/h2>\n

Legacy data migration is the process of transferring data from an outdated system into a modern platform while preserving its accuracy, structure, relationships and auditability. It covers extraction, cleansing, mapping, transformation, loading, reconciliation and the decision about what to do with records that cannot be moved.<\/p>\n

A data migration from legacy to new system is not a copy operation, and neither is a legacy database migration. The source encodes meaning in ways the target will not accept: status flags that mean different things depending on which decade the record was created, free-text fields carrying structured data, customer identifiers that were merged during an acquisition, and business rules that exist only as code rather than as documented logic.<\/p>\n

Data migration is usually one workstream inside a wider programme, and our legacy application modernization guide<\/a> covers the application side of the same effort.<\/p>\n

The work is therefore mostly archaeology and verification. Extraction and loading are the easy parts. Understanding what the data means, deciding what it should become, and proving the result matches is where the effort goes, and where budgets are lost.<\/p>\n

<\/span>Data Migration Failure Statistics: What the Numbers Really Mean<\/span><\/h2>\n

Search this topic and you will meet the same claim in several forms: 83% of data migrations fail, or more than 80% fail or exceed budget, or 60% fail on the first attempt. These get attributed to Gartner, to analyst firms generally, or to nobody at all.<\/p>\n

The traceable origin is a Bloor Research white paper by Philip Howard, published in September 2007, which found that more than 60% of data migration projects overran on time, budget, or both. The same paper estimated industry spend on data migration at over $5 billion a year and argued the root cause was that “the techniques and disciplines of data migration are not treated seriously enough or are not well enough understood.”<\/p>\n

Two things follow. The widely quoted 83% is higher than what the original research reported, which is what happens to a number passed between marketing pages for eighteen years. And the underlying finding is now old enough that it predates cloud data platforms, modern ETL tooling and most current database engines.<\/p>\n

For something current, the broader project data is more defensible. Standish Group CHAOS figures for the 2020 to 2024 cycle, reported by Giuseppe Arcidiacono in PM World Journal in January 2026<\/a>, put 31% of IT projects as successful, 50% as challenged, and 19% as outright failures, with what the paper describes as remarkably little movement across the period.<\/p>\n

Treat that as the honest baseline. Many migration projects avoid outright failure but still face schedule, budget or data-quality problems before or after go-live, and that is the outcome the rest of this guide is written to avoid.<\/p>\n

<\/span>Legacy Data Migration Strategy: Migrate, Archive or Dispose<\/span><\/h2>\n

The single biggest cost control in any legacy data migration strategy is deciding, early and explicitly, that not all of the data is moving.<\/p>\n

Most teams default to migrating everything, because it feels safer and because nobody wants to be the person who deleted something. That default is what turns a six-month project into an eighteen-month one. Every additional record class carries mapping, transformation, testing and reconciliation effort, and much of it is data no user has touched in a decade.<\/p>\n\n\n\n\n\n\n\n\n\n
Data class<\/b><\/td>\nTest<\/b><\/td>\nDestination<\/b><\/td>\n<\/tr>\n
Active operational records<\/span><\/td>\nIn use now, or needed for live processing<\/span><\/td>\nMigrate to the new system<\/span><\/td>\n<\/tr>\n
Recent historical records<\/span><\/td>\nQueried regularly for service, reporting or support<\/span><\/td>\nMigrate, possibly in a simplified structure<\/span><\/td>\n<\/tr>\n
Retained-but-dormant records<\/span><\/td>\nNot queried, but held under a retention obligation<\/span><\/td>\nArchive in a queryable read-only store<\/span><\/td>\n<\/tr>\n
Reference and lookup data<\/span><\/td>\nCodes, hierarchies and mappings the new system needs<\/span><\/td>\nMigrate, after rationalising retired values<\/span><\/td>\n<\/tr>\n
Superseded or duplicate records<\/span><\/td>\nReplaced by a later record, or a known duplicate<\/span><\/td>\nResolve before migration, do not carry forward<\/span><\/td>\n<\/tr>\n
Data past its retention period<\/span><\/td>\nNo legal, regulatory or business reason to keep it<\/span><\/td>\nDispose under your retention policy, with sign-off<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Table: A six-class triage for legacy data. Deciding this before mapping begins is the difference between migrating what matters and migrating everything.<\/p>\n

Two rules keep this defensible. Get the retention decision signed off by whoever owns records management rather than by the project, because disposal is a governance act and not a technical one. And document the test applied to each class, so that a question six months later about why a record set was not migrated has a written answer.<\/p>\n

<\/span>Data Migration Process: How to Move Data from a Legacy System<\/span><\/h2>\n

This is the sequence for a data migration from a legacy system to a modern platform. Steps four through eight repeat per wave rather than running once. Where the target is cloud infrastructure, our guide to migrating legacy applications to the cloud<\/a> covers the platform move that runs alongside it.<\/p>\n

1. Profile the source before you design anything<\/h3>\n

Run the data, not the documentation. Count records per table, measure null rates per field, find the distinct values in every status and code column, and identify the fields whose format changes partway through the history. Documentation for systems of this age describes intent; profiling describes reality.<\/p>\n

2. Classify the data using the table above<\/h3>\n

Agree what migrates, what archives and what is disposed of, and get records management sign-off before mapping starts.<\/p>\n

3. Build the mapping specification field by field<\/h3>\n

For each target field, record the source field, the transformation rule, the handling for nulls and out-of-range values, and the named owner of that rule. This document is the migration; the code is just its implementation.<\/p>\n

4. Cleanse at source where you can<\/h3>\n

Fixing a duplicate customer record in the legacy system fixes it once. Fixing it in the transformation logic handles the issue consistently during migration, but it can leave the underlying source-data problem unresolved.<\/p>\n

5. Build the migration as repeatable, idempotent code<\/h3>\n

You will run it many times. A migration that cannot be safely rerun makes testing, recovery and failure handling much harder.<\/p>\n

6. Do a full-volume trial run into a staging environment<\/h3>\n

Sub-sampled runs hide timing problems, and timing is what forces the cutover window. A trial run that does not use production-scale volumes has not tested the thing that will go wrong.<\/p>\n

7. Reconcile the trial run before touching production<\/h3>\n

Covered in detail in the next section. This is the step most commonly compressed under schedule pressure and most commonly regretted.<\/p>\n

8. Cut over in waves, with a tested rollback per wave<\/h3>\n

Sequence by business domain rather than by table, so each wave delivers something a user can verify.<\/p>\n

9. Run a post-go-live verification period with a named owner<\/h3>\n

Agree in advance what is checked daily for the first fortnight, who checks it, and what threshold triggers a rollback rather than a fix-forward.<\/p>\n

<\/span>How to Reconcile and Validate Data from a Legacy System<\/span><\/h2>\n

A legacy system data migration is won or lost at reconciliation, and it is the part most guides reduce to “test thoroughly.” Testing confirms the process ran. Reconciliation proves the data arrived intact and still means what it meant.<\/p>\n

Define these checks before the first trial run, with a written acceptance threshold for each. A check with no agreed threshold becomes a negotiation at two in the morning on cutover weekend.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
Check<\/b><\/td>\nWhat it proves<\/b><\/td>\nTypical acceptance criterion<\/b><\/td>\n<\/tr>\n
Record counts by entity<\/span><\/td>\nNothing was silently dropped in transit<\/span><\/td>\nExact match, or every variance individually explained and signed off<\/span><\/td>\n<\/tr>\n
Control totals<\/span><\/td>\nNumeric data arrived with its values intact<\/span><\/td>\nFinancial and quantity totals match to the penny or unit<\/span><\/td>\n<\/tr>\n
Referential integrity<\/span><\/td>\nRelationships between records survived<\/span><\/td>\nZero orphaned child records against migrated parents<\/span><\/td>\n<\/tr>\n
Field-level completeness<\/span><\/td>\nMandatory data did not arrive empty<\/span><\/td>\nNull rate in target no higher than profiled null rate in source<\/span><\/td>\n<\/tr>\n
Value distribution<\/span><\/td>\nTransformations did not distort the data<\/span><\/td>\nDistribution of key coded fields matches source within an agreed tolerance<\/span><\/td>\n<\/tr>\n
Business rule equivalence<\/span><\/td>\nThe new system reaches the same answers<\/span><\/td>\nSample transactions produce identical outputs in both systems<\/span><\/td>\n<\/tr>\n
Reporting equivalence<\/span><\/td>\nDownstream numbers still tie out<\/span><\/td>\nKey operational and regulatory reports match across both systems for the same period<\/span><\/td>\n<\/tr>\n
Sample record inspection<\/span><\/td>\nThe data is correct, beyond being numerically consistent<\/span><\/td>\nA stratified sample reviewed field by field by a business user, not by the migration team<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Table: Eight reconciliation checks for a legacy data migration, with the acceptance criterion each one needs agreed in advance.<\/p>\n

Three disciplines make this work. Reconcile at every run rather than only the final one, so you see trends rather than a single verdict. Have a business owner rather than the migration team sign off the sample inspection, because the team that wrote the mapping is the least likely to notice a mapping assumption that is wrong. And keep the reconciliation output, because it is the evidence that the migration was sound when someone asks about a specific record two years from now.<\/p>\n

<\/span>What to do With Records that Cannot Migrate<\/span><\/h2>\n

Every migration of real data produces records that will not move. The target validates a field the source never populated. A customer exists on a transaction but not in the customer table. A date sits in the year 1900 because a legacy screen defaulted it. Two records claim the same unique identifier.<\/p>\n

The failure is not that these exist. It is treating them as defects to be fixed one at a time under cutover pressure, which is how a migration weekend overruns.<\/p>\n

Handle them as a designed path instead. Give every rejected record a machine-readable reason code rather than a log line, so exceptions can be counted, grouped and trended between runs, then agree the disposition per category before cutover rather than during it.<\/p>\n\n\n\n\n\n\n\n\n\n
Exception category<\/b><\/td>\nTypical example<\/b><\/td>\nDisposition<\/b><\/td>\n<\/tr>\n
Correctable at source<\/span><\/td>\nDuplicate customer records, missing mandatory field<\/span><\/td>\nFix in the legacy system and re-extract<\/span><\/td>\n<\/tr>\n
Correctable by rule<\/span><\/td>\nPlaceholder dates, retired status codes with a known successor<\/span><\/td>\nTransform under a documented default, signed off by the data owner<\/span><\/td>\n<\/tr>\n
Needs a business decision<\/span><\/td>\nTwo records claiming the same unique identifier<\/span><\/td>\nQuarantine, route to the named business owner, resolve before go-live<\/span><\/td>\n<\/tr>\n
Orphaned relationships<\/span><\/td>\nTransaction referencing a customer that no longer exists<\/span><\/td>\nMigrate with a placeholder parent, or exclude with sign-off<\/span><\/td>\n<\/tr>\n
Beyond retention<\/span><\/td>\nRecords past their retention period surfacing as errors<\/span><\/td>\nDispose under the retention policy rather than remediating<\/span><\/td>\n<\/tr>\n
Genuinely unreadable<\/span><\/td>\nCorrupt records, unrecoverable proprietary formats<\/span><\/td>\nDocument, exclude, and report the count to the data owner<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Table: Six exception categories in a legacy data migration and the disposition each needs agreed before cutover, not during it.<\/p>\n

Set an exception threshold that halts the cutover rather than hoping the number stays small. And publish exception counts after every trial run, because a category that grows between runs is telling you the source system is still changing in ways your mapping does not handle.<\/p>\n

Format obsolescence is the long-term version of the same problem. Even the US National Archives, in its digital preservation programme, performs format transformations when it receives material it cannot process, and runs media migration on a multi-year cycle. Data you cannot read is not retained data, whatever your retention schedule says.<\/p>\n

<\/span>Legacy Data Migration Best Practices<\/span><\/h2>\n

The steps above describe the sequence. These legacy data migration best practices describe the disciplines that run across all of it, and they are mostly about people rather than tooling.<\/p>\n