Migrating fifteen years of data without losing history

Fifteen years of a buying group's life never sit inside a single database. They are spread across the legacy tool, a dozen spreadsheets, the mailboxes of three permanent staff, a network share nobody administers any more, and two cabinets of paper agreements. Migration is not about moving rows from one system to another. It is about deciding, file by file, what deserves to go on existing.

What actually exists is not in the database

The first exercise is an inventory, and it is almost always humbling. Open the legacy tool's database and you will find the member records, the supplier records, the agreements and a revenue history. Then open everything else. The tiering table that has been maintained in a spreadsheet since 2016 because the tool could never handle bracket rules. The signed addenda that only exist as attachments in the administrative manager's mailbox. The scans on the network share, filed under folder names that made sense to one person. The paper originals nobody has looked at since the last tax audit.

Ask three people in the group how many approval agreements are currently active and you will get three different answers, all defensible, because each one counts a different thing. That divergence is the real finding of the inventory. It tells you that "active agreement" is not defined anywhere, and it will have to be, before anyone writes a line of migration code.

This is why migration cannot be scheduled at the end. It has to start in week two or three, before the target model is frozen, because what you discover changes the model. A typical example: the inventory shows that a handful of members declare through subsidiaries while the bracket scale applies to the consolidated group. The "member group" concept is written down nowhere; it lives in the head of whoever runs the year-end statement. Discover that in week three and it is a modelling decision; in month eleven it is a rewrite.

Three piles: carry over, archive, drop

Every item in the inventory belongs to exactly one of three piles. Carry over: anything that feeds a live calculation, a live entitlement or a live process — active agreements, the company and supplier reference data, the revenue history needed to compute brackets and prorata, ongoing disputes. Archive: anything you might need to defend yourself with — closed statements, expired agreements, superseded addenda, ten-year-old declarations — but which feeds nothing. Drop: everything else, and there is more of it than anyone expects.

The archive pile is the one that saves the project. Archived data does not have to fit the new data model. It goes into an indexed document vault: the original PDF, a handful of searchable metadata fields, and a link from the entity it belongs to. No mapping, no cleaning, no arbitration. Every item you move from "carry over" to "archive" removes a week of work and a set of decisions nobody wants to make.

The instinct is always to carry everything over, on the grounds that storage is cheap. Storage is cheap; arbitration is not. Each carried-over record has to be mapped, deduplicated, reconciled and tested. "We'll take it all" is the single most reliable way to add a year to the schedule.

The sequence that works

1. 1

   Map the reservoirs

   For each source — database, spreadsheets, mailboxes, shares, paper — write down who feeds it, who uses it, how often, and what it holds that exists nowhere else. Two days of work, and it reorders the whole project.

2. 2

   Set a depth per object

   Not one global rule. All agreements, whatever their age. Detailed revenue for the last five financial years, annual totals beyond that. Documents into the archive by default. Write the depths down and have them approved.

3. 3

   Name the arbitrator

   One named person on the group's side, with a mandate from the board, available a couple of half-days a week, whose decisions are recorded and not reopened. Without this, the migration stalls in a fortnight.

4. 4

   Rebuild the reference data first

   Companies and suppliers before anything else. Every subsequent match depends on them. A duplicate supplier here becomes two split revenue lines, one missed bracket and one wrong statement downstream.

5. 5

   Dry run dirty, clean at source, repeat

   The first load runs on uncleaned data: its output is not a clean result but a rejection report that measures the true size of the problem. Corrections then go into the legacy system, never into the target. Five to ten cycles before go-live.

6. 6

   Reconcile with figures, then sign

   Compare totals, counts and amounts against the old system, and record the result in a short migration report signed on both sides. It is the document you will reread in two years.

The cases that hurt

None of the following is a technical problem. Every one of them is a decision with a value in euros, and every one of them will surface during the first dry run.

• Duplicate suppliers. The same company entered as "SAS Martin", "Martin SAS" and "Ets Martin", each with its own agreement and its own revenue history. Merging them changes the assessment base, and therefore the bracket.
• Companies that changed name or registration number. A relocation changes the establishment number while the parent number stays; a buy-out changes both. Does the history follow the legal entity or the member seat? The two answers give different statements.
• Member mergers. Two members merge, and their revenue is now declared together. On a tiered scale, the merged base does not earn the sum of the two previous rebates — it earns more. Someone has to decide from which financial year that applies.
• Agreements with no findable signed version. An addendum has been applied since 2021, and only the unsigned PDF survives. You can carry over the terms; you cannot manufacture the evidence. Flag it and let the group decide whether to have it re-signed.
• Scales applied by hand. A member was granted 3.2% instead of 3% on the 2019 statement because it had been agreed verbally. Nothing in the data explains it. Either the exception becomes an explicit, dated rule, or the history stops being reproducible.
• Free-text fields used as rules. A comment reading "do not invoice" in a member record that quietly drives a process, or old declarations in thousands of euros sitting next to declarations in euros. Grep the comment fields — you will find business logic in them.

Read that list again: it is a catalogue of business rules the group has been applying for years without ever writing them down. That is the underestimated return on a migration. You set out to move data and come back with the rules that govern member mergers and the definition of the assessment base. Keep a running document of every rule the migration forces you to articulate: it is worth more than the loaded database, because the database will itself be replaced one day, and the rules will not.

A named arbitrator, with the authority to decide

Every item above needs someone to say what the answer is. A developer cannot decide whether the absorbed company's revenue consolidates into the acquirer's base for the 2024 statement. Nor can a project manager, nor a supplier. It has to be someone inside the group — usually the general manager or the administrative manager — carrying an explicit mandate from the board, with time blocked in the diary for the duration of the migration.

The mandate matters more than the person. It has to include the right to make a decision that costs money, and the right to close a question for good. A migration that has to escalate three arbitrations a week to the next board meeting does not run for nine months; it runs for two years, and it is not the code that is slow.

Dry runs and reconciliation

A dry run means loading the whole dataset into a copy of the new system with no intention of keeping it. The first one is deliberately run on uncleaned data, because its output is a measurement: how many suppliers fail to match, how many declarations have no company, how many documents are orphaned. Every subsequent run measures the same things and the numbers should fall. When the rejection report is short enough to read in one sitting, you are close.

The whole thing has to be scripted and replayable end to end. The moment a step becomes "and then someone fixes the twelve odd cases by hand", the migration stops being reproducible, and the cutover becomes a gamble on one person's availability.

Reconciliation is what turns a dry run into evidence. Compare, per financial year, the total declared revenue by member and by supplier. Compare the count of active agreements at a given date. Compare the amounts of past statements, member by member. Compare document counts and total storage size, which is the cheapest way to catch a truncated file transfer. Then compare the same figures with the legacy system's own reports rather than with your own extraction, so that you are not checking your query against itself.

The tolerance on amounts that resulted in a payment is zero. Not "within 0.3%" — on a tiered scale, 0.3% of an assessment base can cross a threshold and change a rebate rate for a whole year. Every cent of divergence is either explained in writing or fixed.

What the carried-over history is actually worth

An imported figure and a computed figure are not the same object, and the system should not pretend otherwise. Mark imported records as imported: source, import date, dry-run version, the person who validated it. Give the migration a vintage, the way you give a scale a vintage. Two years from now, when a member queries a 2018 statement, the first useful question is whether that number was computed by the new engine or carried over — and the system should answer it without anyone opening a spreadsheet.

Do not recompute the history with the new engine. Running 2019 through 2026 rules produces different numbers, and different numbers reopen statements that were closed, accepted and paid. Load past results as results, not as inputs to a calculation.

And keep the originals. In a dispute or an audit, what carries weight is the signed document, not the row in the database. The database is an index onto the evidence, not the evidence itself — which is precisely why the archive pile deserves as much care as the carry-over pile.

The aim was never to bring everything across. It is to be able to answer a member's question about a 2018 statement two years after go-live, and to know immediately whether the answer lives in the database, in the archive or in the filing cabinet. A group that knows where every answer sits has migrated well, even if it left three quarters of its data behind.