Migrating off a legacy system without losing history

Replacing an old system is usually a software decision. Moving its data is a trust decision. On the first Monday after go-live, the controller will pull an open receivables report, a program manager will look up a donor’s giving history, and someone in operations will search for an order from three years ago. If the answers differ from what the old system said, people stop believing the new one.

This article is for operations and finance leads and IT managers at organizations of roughly 50 to 500 people who are leaving an aging ERP, a CRM, a homegrown database, a set of Access files or a shared spreadsheet estate. It describes how we approach that work and draws on vendor implementation guidance and open-format documentation. It does not report results from any client migration.

Decide what moves and what stays as history

Not every record belongs in the new system. Microsoft’s Dynamics 365 implementation guidance ^[1] separates configuration data, such as currencies and tax codes, from migration data. It describes migration data as master records like customers, products and vendors, plus open transactions such as sales orders, purchase orders, stock on hand and open balances.

That split is a good default. Load the records people will act on after cutover. Closed invoices from 2017, retired product lines and lapsed contacts can usually stay out of the new application, as long as they remain easy to reach.

Retention rules set the floor for how long that history must stay available. The IRS ^[2] gives periods that run from three years for most income tax records, to at least four years for employment tax records, to seven years for a bad debt deduction, and it notes that insurers or creditors may require longer. Grant agreements, contracts and sector regulators can add their own requirements. Write the retention period for each record type into the migration plan before anyone decides what to leave behind.

Keep the archive queryable

History left behind still has to be usable. A backup of the old database answers “do we still have it?” and fails at “what did we bill this customer in 2019?” once the software that reads it is gone.

We prefer to export retained history into open formats that many tools can read. Apache Parquet ^[3] is an open source, column-oriented file format with a published specification and readers in many languages and analytics tools. A tool like DuckDB ^[4] can query a Parquet file directly with SQL, so a finance analyst can answer a historical question without a running copy of the old ERP.

When the archive is large or will keep changing, Apache Iceberg ^[5] adds a table layer on top of data files. Its documentation lists schema evolution and time travel, which lets a query run against an exact earlier snapshot of a table. That matters when an auditor asks what a report showed on a specific date.

Export lookup tables, code lists and attachments alongside the main records. A Parquet file full of status code 4 is only useful if the meaning of status 4 travels with it.

Map fields with the people who use them

A field mapping written only by IT tends to be accurate about types and wrong about meaning. The column called region might hold sales territory in one era and shipping zone in another. A spreadsheet tab may carry a color code that only one bookkeeper understands.

Build the mapping in working sessions with the people who enter and report on the data. For each source field, record the target field, the transformation, who approved it and any values that have no home in the new system. Microsoft’s guidance names a data steward role that provides definitions and rules for data, and that person should sign off on each table.

Vendor import tools shape the mapping too. Business Central’s default configuration package ^[6] exports to an Excel workbook with one worksheet per table, and Microsoft warns that moved, changed or deleted columns will stop the worksheet from importing. Blob fields cannot travel through that Excel route at all. Know these limits before the mapping is final.

Profile and clean before the first load

Before loading anything, measure the source. Count rows per table. Check how often each field is empty, how many distinct values it holds and whether keys are actually unique. Look for customers entered three times with slightly different names, dates stored as text and amounts with stray currency symbols.

Oracle’s NetSuite CSV import guidelines ^[7] tell users to scrub data before importing it. Cleanup is far cheaper in a staging copy than in a live ERP, where every bad record can spawn transactions that point to it.

Clean in a repeatable script or pipeline rather than by hand-editing exports. You will run the load more than once, and each run should apply the same fixes in the same order. Keep a log of every rule, such as merging duplicate vendors or defaulting a missing payment term, so the finance team can see what changed and why.

Reconcile before anyone signs off

Reconciliation is how you prove nothing was lost or altered. Compare the old and new systems at each of these levels.

Record counts per table and per meaningful slice, such as customers by status or invoices by year.
Control totals for the numbers finance cares about, including open receivables, open payables, inventory value and general ledger balances by account.
Open items listed one by one, so every unpaid invoice and open order in the old system can be matched to its counterpart in the new one.

Agree on tolerances in advance. A rounding difference of a few cents from currency conversion may be acceptable; a missing invoice never is. Save each reconciliation as a dated report that finance signs, because it becomes the evidence that the opening position in the new system was correct.

Rehearse the load and plan the cutover

Run full trial loads into a test environment. Microsoft’s guidance recommends testing data migration at least once in both system integration testing and user acceptance testing environments. Each trial should end with the same reconciliation you will use at go-live.

Trial loads also surface tool limits. The NetSuite Import Assistant, for example, accepts up to 25,000 records per uploaded file ^[8], so larger tables need to be split and sequenced.

Microsoft’s cutover guidance ^[9] describes a cutover window that is often under 48 hours, during which neither the old nor the new system is available. It advises against scheduling cutover during a book close, and it calls for a plan that lists each task in order with an owner, a backup owner, verification and sign-off steps, and a rollback plan. It also recommends rehearsing the full cutover several times with the same data, tools and people. Name who makes the go or no-go call and what evidence they need to see.

Keep the old system read-only for a while

After cutover, switch the old system to read-only instead of turning it off. Users will need to check how something used to look, and the reconciliation team will need to trace odd balances back to their source. Removing write access prevents the worst outcome, which is staff quietly entering new transactions in the system you are retiring.

Set a firm end date for this period, often after the first month-end or quarter-end close in the new system. Before decommissioning, confirm that the open-format archive answers the questions people have actually been asking the old system, and that licenses, backups and access are closed out on purpose.

Write down where every number came from

The W3C PROV overview ^[10] defines provenance as information about the entities, activities and people involved in producing a piece of data, which can be used to judge its quality and trustworthiness. For a migration, that means recording which system and extract each table came from, when it was taken, which transformations ran and who approved the result.

Pair the provenance record with a data dictionary that defines every field in the new system and in the archive, including code values and their meanings. Together they let someone who joins in two years understand the numbers without interviewing the people who did the migration.

We have done this kind of work on public data. HIFLD Next, which we engineered with Public Environmental Data Partners, republishes more than 400 federal infrastructure datasets ^[11] from a preserved snapshot, with the original metadata kept visible, versioning and both legacy and modern file formats. When FEMA’s Future Risk Index was removed in 2025, the underlying data we had preserved let the index be restored to public view ^[12]. In both cases, the source of each dataset stayed visible to the people using it.

When not to migrate yet

Some migrations should wait. If nobody can say what the key fields mean, the first job is documentation. If finance cannot reconcile the old system to its own reports today, that discrepancy will follow the data into the new system and become harder to explain.

Timing matters as well. Avoid running a migration alongside a year-end close, an audit or a funding report. Be cautious about rebuilding the data warehouse and every report in the same window as the system change, because that leaves no stable reference to reconcile against.

Sometimes the right answer is to archive the old system to open formats now and defer the new application until requirements settle. That keeps the history safe without forcing a rushed choice of platform. If you are planning a move off a legacy system, our data migration and publishing work starts with one system or dataset and builds the plan for the rest from what that first move teaches us.

Sources

This article reflects how we approach migrations, drawn from public vendor documentation and open standards. It does not describe results from a client ERP or CRM migration, and retention periods should be confirmed with your own accountant or counsel.