Eligibility system migration: dual-write reconciliation and out-of-scope consumers
A state agency is replacing a 22-year-old COBOL eligibility system with a new Java service. The legacy database holds 4.1 million active cases; the new schema normalises households differently. The cutover plan runs six weeks of dual-write, with the legacy nightly batch and the new service both writing, plus a nightly reconciliation comparing aggregate totals by programme. In week two the reconciliation shows 0.3% of cases with different monthly benefit amounts, about 12,300 cases. The vendor's lead says the differences are rounding and proposes ignoring anything under $1.00. Two of the largest counties run their own nightly extracts that read directly from the legacy tables; they are not in the reconciliation scope and have not been told about the schema change. The go-live date is fixed by a federal certification deadline and cannot move without losing a year of funding.
1. How would you decide whether the 0.3% discrepancy is acceptable? Specify the analysis and the data you would demand.
Read the worked answer
A percentage is a count, not a risk. Start by classifying rather than tolerating. Draw a stratified random sample of 300 to 400 discrepant cases, stratified by programme, household composition, and whether the case was touched by dual-write on the day in question, and replay each from source data. Expect three buckets: (a) intentional rule differences where the new system applies a newer policy interpretation, (b) deterministic mapping defects in the schema transform, and (c) nondeterministic ordering effects in the dual-write.
Only (a) is a business decision. (b) is a defect irrespective of dollar value. The $1.00 tolerance is the wrong instrument twice over: benefit amounts recur monthly, so 12,300 cases at $0.60 is roughly $88,000 a year and it scales with caseload, and a systematic mapping error will not stay small as volumes grow. Check the sign of the discrepancies as well; skew in one direction indicates a logic bias, not rounding.
Reconcile at case level with deterministic replay, not only nightly aggregate totals. Totals can match while individual cases are wrong in offsetting directions, which is exactly the failure an aggregate check is blind to. Finally, set the acceptance criteria before more data is seen, and publish them.
2. What is the risk created by the two county extracts, and what would you do about them?
Read the worked answer
The counties read the legacy tables directly, which makes them consumers of the very schema the project intends to change, and they are excluded from the reconciliation. That means the project's definition of consistent covers only two of at least three writers and readers, so a green reconciliation is not evidence of correctness.
Technically, direct table reads mean any legacy schema change breaks them silently, at 2 a.m., inside a benefits calculation. Options: freeze the legacy schema and expose a read-only compatibility view so the counties keep working unchanged, or migrate them to the new system's interfaces before cutover. Either way, add them to reconciliation: compare each county's nightly extract against the same golden dataset used for the primary check.
Contractually and professionally, they need notice. The counties are making eligibility decisions on data produced by a system that is mid-migration. A short written notice with the change schedule, the compatibility view, and a named contact is cheap; discovering a broken extract after go-live is not. The compatibility view is deliberately temporary and should have an owner and a removal date tied to the last county migration.
3. The go-live date is fixed by a funding-linked federal deadline. What does a sound go/no-go recommendation look like?
Read the worked answer
Set the criteria in writing before looking at the latest data, then report against them. Reasonable criteria for this system: zero unexplained class (b) discrepancies across a defined number of consecutive nightly runs; one hundred percent of production consumers, counties included, inside reconciliation; a full parallel run spanning month-end batch processing, since end-of-month is where the legacy logic is densest; and a rehearsed rollback with a measured restore time and a tested data-freeze procedure.
A federal deadline is a schedule constraint, not a quality argument, and it does not change what the reconciliation found. If criteria are unmet, the correct output is a written recommendation to defer with the specific technical reason, the residual risk of proceeding, and the residual risk of deferring (including the funding consequence). That lets the accountable executive make an informed decision rather than an uninformed one.
What you should not do is adjust the tolerance until the data passes, or narrow scope until the failing cases fall outside it. Both produce a green report and an incorrect benefit payment. If the decision is to proceed anyway, record the recommendation, the decision, and who accepted the residual risk.