When replacing an old system, how do you migrate historical data without breaking reconciliation?

If you want a single judgement: the hard part of a migration is not whether the data can be moved, but whether you can prove, afterwards, that every record still agrees with the old one. The volume of data decides how long the move takes; the consequence of a single wrong value decides the migration approach, the depth of reconciliation and the schedule. Mix the two up and you get the usual ending — the system is live, pages open, orders can be placed, and then in the first month-end close the amounts are off by tens of thousands and nobody can say which document caused it.

So the first thing to do is not to write migration scripts but to classify the existing data: what must match document by document, what only needs the latest state to be correct, what only needs to be openable, and what should not be moved at all. Once that classification is settled, schedule, staffing and acceptance criteria have a basis — and only then can the question "how many weeks will this migration take" be answered.

One boundary first. What follows concerns a company replacing a system in active use (self-built, outsourced or SaaS) with a new custom system, where the existing data includes members, orders, stock, business documents and attachments. A brand-new system has no legacy data and does not involve this work; consolidating data across several legal entities is a different problem, because beyond migration it also involves entity authorisation and the rules on providing personal information to third parties.

1. What decides the migration approach is not data volume but the cost of one wrong value

With the same million rows, one wrong row can mean completely different things: a stock quantity off by one is found and levelled at the next stocktake; a member balance off by one produces a complaint call; a refund amount off by one can turn into a double refund. The plan should be graded by consequence, not by table size.

By that standard, existing data usually falls into four categories:

Data category	Typical content	Consequence of an error	Migration method	Acceptance method
Master data	Members and customers, products and SKUs, stores and warehouses, suppliers, price and stock snapshots	Customers not recognised, wrong prices, stock overstated or understated	Clean, then full import, keeping an old-ID to new-ID mapping	Row-count check + key-field sampling + duplicate phone and ID-number screening
Transaction records	Orders, payments and refunds, shipments, stock movements, balance and points changes	Books do not reconcile, refunds go to the wrong place, balances paid twice	Full import replayed in business-time order, amounts never recalculated	Daily and monthly amount totals + status distribution + document sampling
Attachments and files	Contracts, invoices, document photos, inspection reports, ticket images	Broken links, lost files, unauthorised access	Migrate metadata and storage paths only; put the files into object storage and verify hashes	Sample open + file count and total bytes + permission spot checks
Configuration and accounts	Roles, permissions, approval flows, dictionaries, templates, third-party accounts	Nobody has permission after go-live, approval flows break	Rebuild in the new system after manual review, not by mechanical copy	Role-permission matrix checked item by item + full runs with real accounts

Only the second category needs document-by-document reconciliation, which is the opposite of most people's first instinct — it is not "the member table is the biggest, so it is the most dangerous". Master data carries duplicate-and-merge risk: if one person registered two members with two phone numbers, both with balances and order history, a direct import produces two unrelated accounts and the customer sees only half of their records next time they log in. A machine cannot decide this; the business must supply the merge rule: merge by ID number, merge by phone number, or flag for manual handling.

Attachment risk hides in "it looks migrated". The file count matches, but the storage paths were not rewritten and every link is a 404; or, to save effort, the whole bucket is made publicly readable, so the moment the migration finishes the contracts and ID photos are on the open internet. Attachment acceptance must include two things: a sample opens with its content, and an unauthorised account is refused.

Configuration is the most underestimated of the four. Legacy permissions are usually the accumulated result of years of ticking whatever someone needed, and after ten years nobody can explain them. Copying that structure into the new system copies ten years of confusion; rebuilding it from scratch produces the opposite failure, where on day one nobody has approval rights. A workable route is to export the old role-to-person mapping, turn it into a clean matrix by hand, and then run the key flows end to end with real accounts.

2. Five semantics must be settled before any script is written

Scripts only execute; the risk lives in the semantics. If any one of the following five is unconfirmed, every later reconciliation becomes guesswork.

Amounts. The "order amount" in the old system is often a blended figure: goods plus shipping minus discount, maybe tax-inclusive, maybe not. New systems usually split it into goods, shipping, discount and tax. A direct mapping does not throw an error, but gross margin is computed wrongly and percentage-based refunds drift further. Confirm the old formula first, then decide how to split it, and write that formula into the mapping document.

Time and time zone. If the old system stores local time without a zone and the new one stores UTC, everything shifts by eight hours, daily summaries stop matching, and the symptom looks like "every record is slightly off", which is hard to localise at a glance. Establish what the old timestamps really mean (entry time, payment time, server time), define one conversion rule, and after migration sample the hourly distribution day by day.

Status enums. One "completed" in the old system can mean shipped, paid and invoiced at the same time; the new system splits it into three independent state machines. The mechanical mapping marks "shipped, not paid" orders as completed, and the finance report gains revenue that does not exist. Map statuses one to one in a written table, and when an old status cannot be reconstructed, mark it "to be confirmed" rather than guessing the closest match.

Unique keys. Using a phone number as the member key runs into changed numbers, several numbers per person and families sharing a number; using the member number runs into large numbers of blanks in the order table. Before migration, confirm the new system's uniqueness constraints and the conflict order: keep the newest, keep the largest balance, or keep all and flag them for merging.

Deletion semantics. Most "deletes" in the old system are soft deletes: the row with is_deleted=1 is still in the table. Importing only the main table and dropping that flag resurrects every deleted junk record — the member list suddenly grows by tens of thousands and the customer-service roster is unusable. Conversely, if the old system hard-deleted and the new one needs an audit trail, an archiving policy has to be added.

The output of this work is not a field-mapping sheet for developers but a document the business signs. It should state, at minimum: where each field comes from, how it converts, how blanks are handled and which values need human judgement. Then take twenty real documents and compare them field by field against screenshots from the old system — including a refund, a split order and an order that crosses a month. That is far more effective than reading ten thousand lines of script.

3. Three cutover windows, three very different prices

Every migration plan faces one question: how long the business stops on cutover day. Each of the three approaches has preconditions, and choosing the wrong one is hard to correct later.

One-shot downtime migration. Precondition: a few hours of closure are acceptable, the old system can stop writing inside the window, and the data volume fits the window. The logic is the simplest and the rollback is the clearest; retail and store-based businesses usually find a weekend night sufficient. The price is that no business can be done during the window, and without a rehearsal the real window tends to overrun.

Dual write. Both systems are written to at once. Worth considering only when not a single second of downtime is acceptable and the old system must keep serving for a while. The price is that conflict rules must be defined first: if the same member's phone number is changed in both systems, which one wins? Most projects underestimate this and end up with two inaccurate data sets and no basis for reconciliation.

Incremental catch-up. The practical choice for most projects: move the history in full first (in batches while the old system keeps running, so trading is unaffected), then synchronise incrementally, and on cutover day stop only briefly for the final catch-up and verification. The precondition is a reliable change trail in the old system — an updated timestamp, an auto-increment ID or database logs. Without it, the incremental pass silently misses records, misses them irregularly, and by the time you notice you can no longer tell which ones.

The decision order is simple: ask how long the business can stop; if it cannot stop, ask whether the old system has a usable change marker; only if neither holds should dual write be discussed, and then accept that reconciliation doubles. One more precondition is often skipped: whether the old system may be read at all. If a third-party vendor hosts it and the contract says nothing about data export, even a complete technical plan cannot be executed — that is a commercial matter and must be raised at kick-off.

4. Three layers of reconciliation; drop one and it surfaces at the first month-end close

Between "it is migrated" and "it is migrated correctly" sits reconciliation. It needs three layers, each answering a different question.

Layer one: totals. Row counts per table match the old system, or the differences can be explained (for example, excluded by the do-not-migrate list). This layer is the easiest to pass and the least convincing — it proves the quantity, not the content.

Layer two: amounts. Aggregate comparisons by day, by month and by business type: order value, cash received, refunds, discounts, balance and points movements. Compare month-crossing and year-crossing orders separately. This layer catches unit errors (cents versus yuan), duplicate imports and a whole block of missing dates, which makes it the best value for effort.

Layer three: document sampling. Sample by amount band: the largest documents, a random selection, and every boundary type — partial refunds, split orders, price changes, cross-month settlement, cancelled-but-paid. Open each one in both systems and compare fields rather than only reading totals.

The difference log needs three columns only: what differs, why, and the action taken with the person who confirmed it. The principle is equally simple: every difference is either driven to zero or accepted in writing by the business; "we will fix it after go-live" is not an option. A difference found during migration usually costs one correction script or one rerun; found a month after go-live, it costs a data repair, some customer trust, and sometimes a reconciliation statement already sent to the customer.

5. Cutover day: rollback is not "back out when something goes wrong"

Three things must be complete before cutover; if one is missing, do not cut over.

Freeze the boundary. Fix the exact timestamp when the old system stops accepting writes and tell every desk still entering documents by hand. The most common breach in practice: the warehouse keeps booking outbound notes in the old system while the front desk opens orders in the new one, and the two data sets diverge from that moment.

Full backup plus a rehearsed restore. A backup counts only if it restores into a complete environment that can be queried and can take orders. A backup without a restore rehearsal is no backup — you discover the corruption or the missing storage volume at the worst moment, and this is not a rare accident.

Rollback criteria and a decision maker. Rollback should not be decided by nerves on the night. Write the hard thresholds in advance — order-creation failure rate above a given percentage, reconciliation difference above a given amount, an unavoidable blockage in a critical flow — and name the single person entitled to call the stop. If you cannot write the thresholds, the team has not yet defined what "failed cutover" means.

One question is skipped in almost every review: the real cost of rolling back. After cutover the new system has already produced new data — new orders, new members, new payments. Returning to the old system does not bring that data back automatically; reverse synchronisation or manual re-entry must be arranged, otherwise "going back" means losing the business done since cutover. A rollback plan therefore has to say where that period's data goes, or it is only reassurance.

Keep the old system read-only for at least one full settlement cycle, a month for example: queryable, not writable. Reconciliation needs it, and so do customer service looking up old orders and finance checking invoices. Close it too early and every lookup becomes a data restore, which costs far more than one extra month.

6. What should not be moved: retention depends on data type, not on keeping everything forever

Many plans default to "move everything" on the grounds that it might be needed one day. That default inflates migration scope, storage cost and the exposure surface at the same time, and few teams ever check how often the moved data was actually used.

Retention has to be set by data type; several sets of rules must not be conflated.

Article 31 of the E-Commerce Law of the People's Republic of China requires e-commerce platform operators to record and preserve information on goods, services and transactions published on the platform, for no less than three years from the date the transaction is completed. Note that the obligated party is the platform operator; a merchant selling on its own account is not made an obligated party by this article, though it remains bound by accounting, tax and consumer-protection rules. Reading the article as "every merchant must keep every order for three years" is a common over-extension: it makes people keep what should be deleted and obscures the part that genuinely must be kept.

The Measures for the Management of Accounting Archives (Order No. 79 of the Ministry of Finance and the National Archives Administration, effective 1 January 2016) set the retention period for accounting vouchers, including source documents and bookkeeping vouchers, at 30 years, and state that these are minimum periods counted from the first day after the end of the accounting year. So whether an order must be kept long-term really asks: is it part of a source voucher, and does it correspond to an invoice and a bank record? If yes, it cannot be treated as "delete after three years".

The Personal Information Protection Law pushes from the other side: personal information must be processed for a clear and reasonable purpose, limited to the minimum scope needed, and kept for the shortest necessary period; once the purpose is achieved, cannot be achieved, or is no longer necessary, the information should be deleted proactively. Article 23 further requires that, when providing personal information to another processor, the provider informs the recipient of its name, contact details, purpose, method and the types of information, and obtains separate consent. Moving member data into a new system vendor's environment, or from one service provider to another, falls squarely within that article — it is routinely treated as "just an export" when it is in fact a compliance action.

Squeezed from both sides, the conclusion is clear: a migration plan needs two lists. One says what moves, up to which year, and how it is reconciled; the other says what does not move, why, and when it is deleted. The do-not-migrate list usually covers debug logs, raw event streams, expired sessions and abandoned carts, test data, the deletable personal information of closed accounts, and the dirty copies left by repeated imports. Moving those adds cost and exposure while carrying almost no decision value.

7. Eight migration-specific acceptance checks, kept separate from functional acceptance

Functional acceptance asks whether the new system works; migration acceptance asks whether the data in it can be trusted. Combine them in one sheet and the usual result is that most functions pass while the data problems have not even been looked at.

1. The mapping document is signed. Every field's source, transformation and blank-value handling is written down, and the business has confirmed the amount and status semantics.
2. The difference log is at zero or accepted in writing. Remaining differences have a stated cause, an owner and a date.
3. A restore rehearsal has been run once. Restoring from backup into a complete, queryable, order-taking environment, with the elapsed time recorded.
4. The permission matrix has been checked. Roles and permissions compared item by item, instead of "give admin everything and run".
5. Sampled attachments open. Not just the count: the content displays, and unauthorised accounts are refused.
6. The rollback plan and its owner are explicit. Thresholds, decision maker and reverse synchronisation of post-cutover data are all in the document.
7. The do-not-migrate list and deletion plan exist. Who deletes what, when, and how the deletion is logged.
8. The data dictionary and runbook are handed over. Table structures, field meanings, common queries and reconciliation scripts that the client team can run themselves.

Of the eight, items 3 and 6 are skipped most often and most often become "I wish we had done that" when something really goes wrong. Item 8 decides something else: whether the client can still understand this data after the team has changed a few months later.

8. How this work is scheduled here

At Boiling Water, data migration is an independent work item, not the tidying-up after development. At kick-off we inventory the data: which tables exist, which fields are still used in real operations, which are legacy fields nobody has touched for years. Then we produce the mapping document and the do-not-migrate list and confirm them item by item with the client. The first-phase scope states the migration boundary — which years move, which are only kept as a read-only lookup — because "move everything" is often not what the client needs, only what nobody dared to delete.

Technically, the migration scripts and the reconciliation scripts are written and delivered together. Before cutover we run one full rehearsal and show the difference log to the business, so problems surface before go-live rather than being found by the client afterwards. The handover includes source code, database, documentation and third-party accounts, including the export rights on the old system, and the migration scripts themselves — otherwise the next migration starts from zero. After go-live a named contact stays on to handle the leftover individual documents: merging members, entering missed orders, correcting statuses.

Two boundaries are worth stating clearly. First, accuracy depends on the real quality of the old data: duplicate members, missing amounts and scrambled statuses that already existed can only be cleaned, flagged and listed — we cannot decide on the business's behalf which record is correct, so the client must name a business owner to confirm merge and trade-off rules. Second, vendors who will not cooperate on data export are a real situation; that belongs to contract and commercial negotiation, the earlier the better, and no technical plan substitutes for the right to access the data.

If you are assessing this kind of replacement, you can look at projects we have delivered to see the shape of our delivery, and use the service overview to check whether our way of working matches your pace.

Closing

You do not need a thick plan to judge whether a migration is prepared. Three things are enough: the mapping document carries a business signature, the difference log is at zero, and the data created during rollback has somewhere to go. With all three, the migration is a controlled engineering change; with one missing, it becomes a firefight in the first settlement cycle after go-live.

Three things can be done immediately: have the old vendor or internal IT export a complete table structure and a year of volume statistics; appoint one business owner to confirm the three semantics of amounts, statuses and member uniqueness; and write the do-not-migrate list and the migration acceptance criteria into the requirements document or the contract instead of leaving them verbal.

Supplementary questions

What if the old vendor refuses to export the data?

Check the contract for data-ownership and export clauses first, then examine what is technically reachable: a database backup, back-office report exports and API reads differ greatly in completeness. If the old system is self-hosted or on your own servers, the database can usually be taken directly; if it is third-party SaaS, export capability is often limited by product features. Handle this at the start of the new project, not two weeks before development is due to finish.

Do tens of millions of orders really require a long outage?

Not necessarily. Full history can be moved in batches while the old system keeps running, leaving the cutover window for the final incremental catch-up and verification. What decides the length of the window is whether the increment can be caught up reliably and how fast the verification scripts run, not the total volume. Conversely, if the old system has no usable change timestamp, an incremental pass is not viable and the window has to be reassessed.

After migration a few documents are wrong. Can we just update the database?

Not recommended. Corrections to business documents should go through a reversal or correction flow that preserves the original record and the audit trail; editing tables directly removes traceability and leaves the next reconciliation without a baseline. Technical fixes must also produce change records that can be replayed and audited — otherwise this fix is right and nobody knows what changed next time.

Sources

• Article 31 of the E-Commerce Law of the People's Republic of China (in force 1 January 2019), National People's Congress laws and regulations database.
• Measures for the Management of Accounting Archives (Order No. 79 of the Ministry of Finance and the National Archives Administration, in force 1 January 2016), The State Council of China website.
• Article 23 of the Personal Information Protection Law of the People's Republic of China and the related provisions on minimum necessity and retention periods (in force 1 November 2021), National People's Congress laws and regulations database.
• Boiling Water's service model and delivery scope are based on public pages of its website (boilingwater.cn/intro, boilingwater.cn/cases); these are company statements, not third-party endorsement.
• The migration method, reconciliation layers, rollback checklist and acceptance items are the author's recommendations based on project experience; they are not a commitment on schedule, cost or outcome for any specific project. Statutory text checked on 13 November 2026.