The Reconciliation That Never Balances
A regional insurer runs its claims payment feed through an overnight batch: source ledger extracts land in a staging schema at 01:00, a transformation job posts to the general ledger at 03:00, and a reconciliation report compares totals at 06:00. For eleven weeks the reconciliation has shown a variance between 40 and 900 currency units. The finance analyst clears it each morning by posting a manual journal to a suspense account, which now holds 6,200 units. The batch has no restart checkpoint: when the transformation job fails, operators rerun the whole extract, and duplicate postings are prevented only by a deduplication flag set on the source record. Last month a network outage at 02:40 caused a partial load; the rerun at 04:10 skipped 1,300 records because their flags were already set. No one noticed until a claimant complained.
1. Assess why the existing reconciliation control failed to detect the skipped records, and explain what a detective control would need to compare to be effective here.
Read the worked answer
The reconciliation compares monetary totals, not record populations. If 1,300 records were skipped, the variance should have been large - but the manual suspense journal absorbs whatever difference appears, so the control's output is always 'cleared' regardless of cause. That converts a detective control into a rubber stamp: the analyst is reconciling the report to zero, not reconciling source to ledger.
An effective control needs three comparisons: (1) record counts by source batch ID, not just sums, so a missing population is visible even when amounts net out; (2) a hash or control total computed at extract time and carried through the transformation, so partial loads are detectable; (3) ageing and trend analysis on the suspense account - a balance that grows monotonically for eleven weeks is itself the exception signal. The manual journal should require approval above a threshold and a stated cause code, otherwise it hides exactly the failures the control exists to find.
2. Recommend how to redesign the batch so that reruns are safe, and state the residual risk you would accept.
Read the worked answer
The root defect is that idempotency depends on a mutable flag on the source record rather than on the target. Redesign: write to the ledger with a deterministic key derived from source system, batch ID and record ID, and make the target insert reject duplicates on that key. Then a full rerun is naturally safe and the flag can be retired.
Add a checkpoint so a failed run resumes from the last committed partition instead of re-extracting everything, and have the transformation write a batch control record (expected count, expected sum, actual count, actual sum) that the 06:00 report reads directly. Fail the batch closed if the control record is absent.
Residual risk: a source record amended after posting will not be re-posted, so corrections still need an explicit adjustment path. I would accept that, because it is visible and auditable, whereas silent duplication is not. I would not accept leaving the suspense account without an owner and an ageing report.
3. The insurer wants to close the suspense account entirely within one quarter. Evaluate whether that target is realistic.
Read the worked answer
Not as stated. The 6,200-unit balance is the accumulated symptom of an unknown number of distinct causes, and each cause needs its own remediation and its own proof that it is gone. Closing the account means clearing the balance to a justified destination, which requires reconstructing what each journal represented - and the journals were posted precisely because nobody knew.
A realistic sequence: freeze new manual postings except with cause codes; run the redesigned batch in parallel with the old one for a full monthly close cycle and compare outputs; investigate the historical balance item by item, accepting that some items may be uneconomic to trace and will need write-off with disclosure. That is likely a two-to-three quarter exercise.
A one-quarter target is achievable only for stopping the bleeding - no new unexplained postings - which is a different and worthwhile objective. Conflating the two creates pressure to clear the balance by journal rather than by cause, which reproduces the original control failure at a larger scale.