Replacing an old ERP, moving from a desktop accounting package to a web application, or consolidating two customer databases after an acquisition: each involves data migration, the job of moving data from one system to another while keeping it complete and correct. Migrations have a reputation for overrunning, and the reason is rarely the copying itself. It is the surprises hidden in old data and the assumptions nobody wrote down. This guide breaks a migration into phases that catch those surprises early.
Data migration phase 1: inventory and profiling
Start by finding out what you actually have. List every source: the main database, but also spreadsheets, attachments, the separate tool the sales team uses, and any data held only in emails. Then profile each source, meaning you measure its real contents rather than trusting documentation.
Simple SQL goes a long way:
-- How many rows, and how many are missing key fields?
SELECT COUNT(*) AS total,
SUM(email IS NULL OR email = '') AS missing_email,
SUM(gstin IS NULL) AS missing_gstin
FROM customers;
-- Which "status" values really exist?
SELECT status, COUNT(*) FROM invoices GROUP BY status ORDER BY 2 DESC;
-- Any dates that make no sense?
SELECT MIN(invoice_date), MAX(invoice_date) FROM invoices;Expect to find statuses nobody remembers, dates in the year 1900 used as placeholders, and free-text fields holding structured data. Better to find them now than on cutover night.
Phase 2: Decide scope and mapping
What moves, and what stays behind?
Not everything needs to migrate. Ten years of closed support tickets may be better archived in a read-only store than loaded into a new system. Agree with the business what is in scope, and how long archived data must remain accessible.
Build a field mapping document
For every target field, record its source, any transformation, and who signed it off. A short extract:
| Target field | Source | Rule |
|---|---|---|
customer.full_name | CUST.FNAME + CUST.LNAME | Trim spaces; join with one space |
customer.country_code | CUST.COUNTRY | Map free text ("India", "IND", "Bharat") to ISO code IN |
invoice.status | INV.STAT | P → paid, O → open, X → cancelled; anything else → exception report |
invoice.legacy_id | INV.ID | Copy as-is for traceability |
Always carry the old system's identifier into the new one. When a user says "invoice 48812 looks wrong", you need to trace it back instantly.
Phase 3: Clean what you can before moving
Fixing data in the source, or in a staging area, is cheaper than fixing it in the new system after users have started relying on it. Merge duplicate customers, standardise formats and decide what to do with orphaned records, such as invoices pointing at deleted customers. Not every issue needs fixing; some can be migrated and flagged for later review. The key is making the decision deliberately.
Phase 4: Choose a migration approach
- Big bang. Stop the old system, move everything over a weekend, start the new one. Simple to reason about, but it needs a downtime window and a reliable rollback plan.
- Phased. Move one module, region or customer group at a time. Lower risk per step, but you must run both systems in parallel and keep shared data consistent.
- Continuous sync. Copy data once, then keep replicating changes until a brief final switchover. Common for database platform moves, using replication or change data capture (CDC), a technique that streams every insert, update and delete from the source.
Write the migration as repeatable scripts, not manual steps. You will run it many times.
Phase 5: Rehearse with trial runs
Run the full migration into a test environment at least two or three times before the real thing. Each rehearsal should:
- Use a recent copy of production data, not a small sample.
- Be timed, so you know whether it fits the cutover window.
- Produce an exception report of rows that failed validation.
- Be followed by real users checking real records they know well.
Rehearsals with production data mean copies of personal information in test systems, so protect them as you would production and delete them afterwards.
Phase 6: Reconcile, then reconcile again
Reconciliation proves nothing was lost or altered. Compare counts and totals between source and target, broken down in ways that would reveal partial losses:
-- Run on both systems and compare the results
SELECT YEAR(invoice_date) AS yr,
COUNT(*) AS invoices,
SUM(amount) AS total_amount
FROM invoices
GROUP BY YEAR(invoice_date)
ORDER BY yr;Add checks for the things that matter most to the business: outstanding balances per customer, stock quantities per warehouse, open orders. For high-value records, compare row-by-row using a hash of key fields. A total that matches to the rupee is far more convincing than "the row counts look right".
Phase 7: Cutover and rollback
Write a cutover runbook with timings, owners and go/no-go checkpoints. Freeze changes in the old system, run the final load, reconcile, and only then open the new system to users. Define in advance what failure looks like and how you would roll back. Keep the old system available in read-only mode for a period afterwards; it will settle many "where did this go?" questions.
Phase 8: Watch the first weeks
Many migration issues surface only when users run month-end, generate a rare report or open an old record. Keep the migration team available, track issues in one place, and fix the scripts' root causes rather than patching records by hand. Our database management service covers migrations between database platforms, and our cloud solutions team handles moves from on-premises servers to the cloud.
Key takeaways
- Profile source data early; old systems always hold surprises.
- Document every field mapping and keep legacy IDs for traceability.
- Script the data migration, rehearse it with production-sized data, and time it.
- Reconcile counts and financial totals by period before declaring success.