Data migration with reconciliation: a guide for finance and billing systems

When a finance or billing system is replaced, the data migration is where most of the risk sits. Screens can be retrained and reports rebuilt. But if balances move incorrectly or contracts arrive with the wrong values, the errors flow straight into invoices, ledgers and audit findings.

The answer is not to migrate more carefully in one big attempt. It is to treat migration as a repeatable process, with reconciliation built in from the first rehearsal. This guide sets out the approach we use.

Profile the data first

Profiling is a structured look at what the source data actually contains, rather than what the documentation says it contains. For each key entity, such as customers, accounts, contracts, invoices, open items and transactions, we check:

• Volumes and how they have changed over time
• Completeness of mandatory fields
• Duplicates, near-duplicates and orphaned records
• Values that fall outside expected ranges or formats
• Codes and reference values that are no longer in use

Profiling turns vague concerns about data quality into a specific list of issues. It also shapes the rest of the plan, because the volume and nature of issues determine how much cleansing is needed and where.

Agree who owns cleansing

Data cleansing is a business responsibility, not a technical one. The delivery team can find issues, suggest fixes and build tools to help, but only the people who own the data can decide what is correct.

For each issue found in profiling, agree three things:

• The owner: a named person in the service or finance team
• The approach: fix in the source system, transform during migration, or exclude
• The deadline: tied to a specific rehearsal, so progress can be measured

Fixing at source is usually best, because the old system stays consistent and every rehearsal benefits. Transformation rules are useful for systematic issues such as formatting, but they should be documented and signed off like any other business rule.

Map every field, and every value

Mapping is the detailed specification of how each source field becomes a target field. It covers structure (which tables and fields), transformation (how values change on the way) and reference data (how old codes translate to new ones).

Value mapping is where errors hide. A status code, a tax code or a charge type that maps to the wrong target value can pass every technical test while producing incorrect results. We ask business owners to review value maps line by line, and we keep the mapping document under version control so changes are visible between rehearsals.

Rehearse, repeatedly

A migration should be run end to end several times before the real cutover. Each rehearsal uses a fresh extract from the live system and the same scripts, in the same order, that will be used on the day.

Rehearsals do three jobs:

• They find defects in mapping and transformation while there is still time to fix them
• They measure how long each step takes, so the cutover window can be planned accurately
• They let the business practise its own tasks, such as checks and sign-offs, before it matters

We expect early rehearsals to surface many issues. The goal is a clear downward trend, with the final rehearsal producing results close enough to go-live quality that the cutover feels routine.

Reconcile everything that matters

Reconciliation is how you prove the migration worked. After each rehearsal, we produce reconciliation reports comparing source and target, typically at three levels.

Counts

Record counts by entity and by key category, such as customers by type or contracts by status. Counts catch missing or duplicated records quickly.

Balances

Open balances in total, by customer and by ageing band. For finance data, balances should also reconcile to the general ledger control accounts. A difference of any amount needs an explanation.

Contract and transaction values

Total contract values, annual values and future billing schedules, compared at contract level. For transactional history, compare totals by period and type.

Every difference goes into a log with its cause and resolution. Some differences are expected, such as records deliberately excluded or values corrected during cleansing. Those should be listed and agreed, not simply noted.

Get formal sign-off

Before the final migration, the business should sign off on the approach, and after it, on the result. Sign-off is not a formality. It confirms that the people accountable for the data have reviewed the reconciliation evidence and accept it.

We recommend defining sign-off criteria early: which reports must be produced, what level of difference is acceptable for each (often none for balances), and who signs. That way the decision on the day is based on agreed evidence rather than on pressure to go live.

Decide what to migrate and what to archive

Not everything needs to move. Migrating years of closed history adds effort and risk, and old data often fits the new system poorly.

A common pattern is:

• Migrate: active customers and accounts, live contracts, open balances and items, and enough recent history to support queries and reporting
• Archive: older closed transactions and historic records, held in a searchable, read-only store

The right line depends on your retention obligations, audit needs and how often staff refer back to old records. Agree it with finance, information governance and the service, and make sure the archive is accessible before the old system is switched off.

Bringing it together

Good migration is unglamorous: profiling, ownership, careful mapping, repeated rehearsal and evidence at every step. It is also one of the best investments in a system replacement. Our data and AI and business systems teams work together on this, because the data and the configuration have to be right at the same time.

Talk to us

If you are planning a finance or billing system replacement and want a migration approach you can evidence, see our contract management, billing and invoicing solution or contact us.