The Merge That Matched Only 71 Percent
A benefits processor runs a nightly eligibility merge that joins a 42,000-record master file to a 6,500-record enrollment file from a regional vendor. Both files are keyed on an 8-character member ID: two uppercase letters followed by six digits. The master stores the key zero-filled (AB000123). The vendor's extract reformats a packed numeric field, so its keys are space-filled after the letters (AB 123). Last night the merge matched 1,885 of the 6,500 incoming records; the other 4,615 went to the suspense file, and no master records were reported unmatched. Every one of the 4,615 suspense keys has a numeric portion below 100000. A spot check of 200 incoming records with numeric portions above 200000 matched 200 times. The master file's own control totals balanced, and re-reading the same vendor file reproduced the identical 4,615 suspense keys.
1. Identify the most probable root cause and cite the evidence that rules out a damaged master file. Explain why the failure rate is 71 percent rather than random.
Read the worked answer
The merge compares keys as character strings, and a space is not equal to a zero. The master key AB000123 and the vendor key AB 123 represent the same member but compare unequal, so the match fails on representation, not on membership.
Evidence against a damaged master:
- Zero master records were reported unmatched, so the master side of the merge is intact.
- Re-reading the vendor file reproduced exactly 4,615 suspense keys, which indicates a deterministic rule rather than corruption.
- The master's own control totals balanced.
- The 200-record sample above 200000 matched 100 percent of the time.
Why 71 percent: any numeric portion below 100000 has at least one leading position that the vendor fills with blanks, so it cannot match a zero-filled master key. That is only 10 percent of the possible six-digit range, but enrollment IDs are issued sequentially from 000001, so most of the incoming 6,500 records sit in the low range. The observed 4,615 of 6,500 (71 percent) is the low-ID share of the incoming batch, not a random error rate.
2. Specify the correction and the pre-run controls that would have caught this before production. Address the 4,615 suspense records.
Read the worked answer
Correction: normalize the key on both sides before the merge. Either strip embedded blanks and zero-fill the numeric portion in the vendor extract, or redefine the key as a packed numeric field converted from the character value. Do not fix it by padding the master to match the vendor; that propagates a nonstandard format into the system of record.
Controls to add:
- Format edit on every incoming key: exactly two alphabetic characters, six characters after blank removal, all digits. Reject the file, not the record, if any key fails.
- Record-count gate: abort the merge if the suspense rate exceeds 2 percent of the incoming file.
- Test-data requirement: the acceptance test must include IDs with leading zeros. The prior test used high-numbered IDs, which is why it passed.
- Pre-production sample merge against 200 known-matched IDs, compared to an expected match list.
Suspense handling: correct the extract and re-run the merge rather than keying 4,615 records by hand. First confirm the failed run wrote no partial master updates; if any of the 4,615 were created as new members, they now exist twice and must be collapsed before the re-run, or the correction itself will create duplicate eligibility.
3. The vendor says it cannot change its extract format for six weeks. What is the best interim design, and what risk does it carry?
Read the worked answer
Interim design: add a normalization step between the vendor file and the merge. Read the vendor file, strip blanks from the key field, zero-fill to eight characters, and write a work file sorted on the normalized key. The merge then runs unchanged against two consistently formatted files.
The cost is one extra pass over 6,500 records, which is trivial in time and disk. The real risks are different:
- The normalization step becomes permanent by default. Put an expiry date and an owner on it, or it will still be running after the vendor fixes its extract.
- Two formats now exist in the environment, so anyone writing a new program against the vendor file will hit the same defect. Publish the normalized work file as the only supported interface.
- If normalization is applied inconsistently, a record can be normalized twice; make the routine idempotent so stripping and zero-filling an already-normalized key changes nothing.
A six-week parallel run with a daily suspense-rate check is enough to prove the fix; extending it past the vendor change adds maintenance cost with no benefit.