This is one migration, followed from the first check to the last. The setting is invented: a regional finance company is moving its customer and loan records from a fifteen-year-old system into a new platform. The old system has 1,250,000 customer records. Your job is to say, with evidence, whether the new platform holds the same truth.
Before anything moves: agree what “the same” means
The migration is not a copy. There is a mapping document that says which old field becomes which new field, and which rules apply on the way: records with invalid status codes are rejected, exact duplicate customers are merged, a single “full name” field is split into first and last, phone numbers are stored in the +91 format. Your tests are derived from this document. If it does not exist, writing it comes first, because you cannot test a transformation nobody has described.
Also decide the reconciliation rule for every table: which counts must match exactly, which sums must match to the paisa, and which differences are allowed and explained.
Check one: the counts, with arithmetic
The migration log says 1,200 records were rejected and 350 duplicates were merged. So the target should hold:
1,250,000 − 1,200 − 350 = 1,248,450 customers.
You count the target and get 1,248,442. Eight rows are unaccounted for. This is exactly why you do the sum rather than glance at two numbers that look close. Eight is small, and every one of them might be a real customer with a loan.
To find them, compare keys, not counts. Take the list of customer ids from the source, remove rejected and merged ones, and compare it against target ids. The difference turns out to be eight customers whose names contain an apostrophe or a character outside the old encoding, and whose rows failed on load without raising an alert. Tamil and Hindi names entered years ago in a legacy character set are classic suspects; so are very long addresses and values that overflow a narrower column in the new schema.
The lesson from this step is general: a count match proves little, because errors can cancel out (one missing, one duplicated), but a count mismatch proves something is wrong, and a key comparison tells you which rows.
Check two: totals and checksums
Row counts say nothing about values. For financial columns, compare aggregates: total outstanding principal, number of active loans per branch, sum of EMI amounts. Run the same grouping on both sides and compare the results line by line. Money columns are where rounding changes hide, such as a decimal type with two places in the old system and a floating type in the new one; a difference of a few paise on one row is invisible until you add a million of them.
For text-heavy tables, compute a hash per row from a defined set of columns on both sides and compare hashes. Matching hashes mean identical content for those columns; a mismatch sends you to look at that row. Remember that the hash must be computed on the value after the intended transformation, or every row will differ for a good reason.
Check three: fields that quietly changed shape
Even when counts and sums agree, individual fields can be wrong. Sample deliberately rather than randomly only. Include the awkward ones:
- Dates: day-month order swapped, the old “00/00/0000” placeholder turned into a real date, time zones shifting a date of birth by a day.
- Nulls versus empty strings: the old system stored blank, the new one stores nothing, and a report that filters on “has email” now returns a different set.
- Trailing spaces and case: a PAN stored with a space may fail a new validation, or may match a duplicate rule it previously escaped.
- Code values: old status “A” meaning active, mapped to “ACTIVE”, with one rare old code (say “R”) that has no mapping and falls to a default.
- Names and addresses: truncation at a new field length, lost diacritics, joint account holders reduced to one.
For each category, pick a handful of real records, ideally the oldest, the largest and the strangest, and compare source to target field by field against the mapping. Then check relationships: every loan must still point to an existing customer, and there should be no orphans, no loans linked to the wrong person because ids were regenerated.
Check four: does the new system behave on the migrated data?
Data can be perfect and still break the application. Run business flows on migrated customers: generate a statement, calculate a foreclosure amount, post a payment, run the month-end interest job. Compare the results with the old system for the same customers on the same date. A mismatch here often exposes a rule in the old code that nobody documented, which is a finding about the mapping, not about the data.
Rehearse, then cut over
Do not do the real migration for the first time on the real night. Run full rehearsals on production-sized copies, with masked data if the environment is outside the production boundary, and time them. If the load takes nine hours and the downtime window is six, you have found that out cheaply. Rehearse the rollback too: restoring the old system, and what happens to transactions that arrived after cutover.
Most large migrations also need a delta: changes made in the source after the bulk extract. Test that the incremental load picks up updates and deletions, not only new rows. On cutover night, repeat the count and total reconciliation as a short, scripted checklist, and keep the reconciliation outputs. If an auditor or the business head asks next year how you know the data moved correctly, those saved numbers are the answer.
Figures and names in the examples are illustrative.
Leave a Reply