The 40-Second Nightly Batch and the Cached Report
A logistics firm runs a nightly batch that reconciles shipment charges. Operations reports that the reconciliation report occasionally shows totals that disagree with the source database by 0.3–1.2%. The disagreement only appears when the batch runs past 02:00, which happens on heavy days. The report is generated by a query layer that caches aggregation results for 10 minutes to protect the database. The batch writes settlement rows in chunks of 5,000; the report query joins those rows to an external carrier-fee table. The product owner wants the report to be 'always correct' and asks the team to remove caching. A business analyst proposes instead adding a status column so the report excludes unsettled rows. The batch owner says the status column adds a state transition that the downstream audit system does not understand.
1. Identify the requirement defect that is producing this dispute, and explain what artifact should have prevented it.
Read the worked answer
The dispute is not really about caching or status columns; it is a missing, testable definition of 'correct report'. The current requirement probably says something like 'the report must show accurate settlement totals', which is unfalsifiable. Two stakeholders read it differently: the product owner reads it as 'reflects the final committed state', the analyst reads it as 'excludes work in progress'. Neither reading is written down, so each proposes a mechanism (remove cache / add status) that satisfies their private reading.
The missing artifact is a precise requirement with acceptance criteria covering timing and consistency semantics: e.g. 'For any settlement cycle that has completed, the report total must equal the sum of committed settlement rows for that cycle, measured within 5 minutes of batch completion; rows from an in-progress cycle must not appear.' That single sentence rules out the ambiguous middle ground and makes the cache question an implementation detail rather than a negotiation.
2. The team has one week. Evaluate the two proposed fixes against the stated symptoms and recommend a path.
Read the worked answer
Start from the symptom: disagreement only when the batch runs past 02:00, i.e. when the report is queried while the batch is still writing. That is a concurrency/visibility problem, not a caching-staleness problem in the ordinary sense. Removing the cache makes the report hit the live table more often, which may increase the chance of reading a partially written cycle - it does not fix the root cause and adds load during the heaviest window.
The status-column proposal addresses the real cause (distinguishing in-progress from committed rows) but breaks the downstream audit contract. That is a genuine integration constraint, not stubbornness.
Recommended path: (1) write the timing/consistency requirement above and get sign-off; (2) implement the exclusion using a mechanism that does not change the audit contract - e.g. the report reads only rows whose batch-run id is marked complete in a run-control table the audit system already ignores; (3) add a regression check that queries the report mid-batch and asserts no partial-cycle rows appear. Removing the cache can be deferred and re-evaluated only if measurement shows staleness actually contributes.
3. What verification evidence would you require before declaring the fix done?
Read the worked answer
Evidence tied to the acceptance criteria, not to 'the code changed':
- A test that runs the report during an active batch (or a simulated mid-write state) and asserts zero rows from the in-progress cycle.
- A test that runs the report after batch completion and asserts exact equality with committed totals, including the external carrier-fee join.
- A measurement showing report latency and database load during the 02:00 window are within agreed bounds, since the fix must not simply move the failure.
- Confirmation from the audit-system owner that the run-control mechanism is invisible to their contract. Without these, 'always correct' remains an opinion and the same dispute will recur on the next heavy day.