The Data Migration Cutover
A building society is migrating 1.4 million customer records from a 1990s core banking system to a new platform over a single weekend. The legacy system stores addresses as free text across three lines, has 11,000 records with no postcode, and uses a customer number that was reused once, in 2004, for 38 accounts. The migration plan validates that every source record produces a target record and that row counts match. The programme board has been told the migration is 'low risk because counts reconcile'. The new system enforces a mandatory postcode and a unique customer identifier. The cutover window is 56 hours; the last full rehearsal took 51 hours and produced 214 rejected records that were manually corrected and not re-tested.
1. Critique the validation approach and explain what the count reconciliation does and does not prove.
Read the worked answer
Count reconciliation proves completeness of transfer - that nothing was silently dropped between extract and load. It proves nothing about accuracy, validity or semantic equivalence. A record can arrive with the wrong postcode, the wrong customer linkage, or a truncated address line and still be counted.
The 214 rejections are the critical fact. They were corrected by hand and not re-tested, which means the correction logic is unvalidated and, more importantly, that the same defects will recur in production at a scale nobody has estimated. If 214 appeared in a rehearsal, the production run should be expected to produce a comparable or larger number, and the cutover plan has no capacity reserved for that.
A sound approach adds: field-level reconciliation on key attributes (postcode presence, address line parsing success, customer-to-account linkage); a defined rule for the 11,000 missing postcodes, agreed with the business, rather than ad hoc correction; and a test that the 38 reused customer numbers are resolved by a deterministic rule before load, not discovered afterwards.
2. The 2004 customer number reuse creates a specific risk. Explain it and recommend how to handle it before cutover.
Read the worked answer
The legacy system allowed a customer number to be reassigned, so for those 38 accounts the number no longer identifies a single person. In the old system this was tolerable because staff knew the history or because the accounts were closed. In the new system, which enforces uniqueness, the migration will either reject the records, merge two different customers into one, or attach historical accounts to the wrong living person.
The last outcome is the dangerous one: it is silent, it passes count reconciliation, and it produces a data protection incident and a conduct risk, because one customer may see another's account history.
Handling: extract the full history for those 38 accounts, determine which are open, and assign new unique identifiers to the current holders with an explicit mapping table signed off by the business. Historical closed accounts should be linked to the correct historical person, not to the current holder of the number. The mapping must be retained as part of the migration audit trail, because future queries will need to explain why an identifier changed. This is a small population and should be resolved manually and completely, not by a general rule.
3. Given a 51-hour rehearsal against a 56-hour window, assess whether the cutover should proceed.
Read the worked answer
The margin is five hours, roughly 9%, on a process whose duration has not been shown to be stable. That is not a credible contingency for a first production run, especially when the rehearsal excluded the manual correction effort for 214 records - which means the 51 hours understates the real workload.
My recommendation is not to proceed on the current plan. Conditions to proceed: rerun the full rehearsal including correction of rejects, with the correction logic automated and tested, and confirm the elapsed time; agree a documented go/no-go checkpoint at, say, hour 40 with a tested rollback to the legacy system; and confirm the legacy system remains writable or at least queryable during a defined fallback period.
If the board insists on proceeding, the decision should be recorded with the residual risk explicit: a failed cutover at hour 50 leaves the society mid-migration with no tested reverse path. 'Low risk because counts reconcile' is not a risk assessment; it addresses one failure mode out of several and ignores the one the rehearsal actually demonstrated.