At-Least-Once Delivery and a Monthly GL Suspense Account
A specialty retailer ingests point-of-sale events from 1,840 stores. Store systems batch to SFTP every 15 minutes; a Kafka feed carries order and refund events with at-least-once delivery; a third feed brings supplier invoices nightly. The warehouse loads into a fact table keyed on order_line_id and event_timestamp. On peak days, duplicate order lines reach 0.9%, refunds occasionally post before the original sale, and a store that loses connectivity replays up to 11 hours of files. The nightly reconciliation to the general ledger is off by €180k–€420k per month; finance writes the difference to a suspense account. The ETL lead wants to 'just dedupe in the final view.' A migration to a new orchestration tool is scheduled in ten weeks, so any fix must survive the migration.
1. Design an idempotency contract for both the SFTP files and the Kafka feed. Explain why deduplicating in the final view is not sufficient.
Read the worked answer
Deduplicating in the final view hides the symptom while leaving the raw and staging layers wrong. Duplicates still inflate joins, break control totals, and reach any consumer that does not use that view. It also makes reconciliation impossible, because the warehouse cannot distinguish a legitimate repeated line from a redelivery.
Design an explicit idempotency contract at ingestion:
- Stable business key. For POS lines, use store, register, transaction, line, and business date. For Kafka events, use a producer-assigned
event_idplus the business key. The business key, not event_timestamp, defines identity; timestamps are attributes. - Ingestion ledger. For SFTP, record file name, checksum, store, and processing status. Reject or skip exact replays, and allow a controlled reprocess that supersedes rather than appends. For Kafka, use producer idempotence and commit offsets after the sink write.
- Write pattern. Land raw data immutably with ingestion metadata, then merge into staging on the business key using a deterministic rule such as latest ingest sequence. Publish only from the deduplicated staging layer.
- Unique constraints and tests. Enforce uniqueness where possible, and add a daily test that duplicate business keys in the published fact equal zero.
Make the contract tool-neutral by expressing keys, dedupe rules, and tests as metadata and code that travel with the migration. The new orchestrator should inherit the contract, not redefine it.
2. Refunds sometimes arrive before the original sale, and a disconnected store can replay 11 hours of files. Propose how to handle ordering, late data, and reprocessing without corrupting finance figures.
Read the worked answer
Arrival order is not business order. Model the event stream as facts with both event time and ingest time, and let downstream logic use event time for business meaning and ingest time for audit. Do not assume that a refund before its sale is invalid; it may be a legitimate correction or a delayed feed.
For late data, define a grace window per feed. During the window, merge late events on the business key and recompute affected aggregates. After the window, accept the event but flag the relevant daily or monthly aggregate for restatement. Keep a state machine for sale and refund status so that a refund without a known sale remains pending rather than being dropped. When the sale arrives, resolve the pair and update the status.
For the 11-hour replay, treat reprocessing as a first-class operation: identify the affected stores and time range, reprocess from raw into staging, then republish with the same business keys. Record the reprocessing event and its impact in a control log. Finance should see a stable close: freeze the month-end snapshot, compare it to the next run, and report any late-event impact above an agreed threshold.
This approach survives the orchestration migration because it depends on event-time semantics and business keys, not on a particular scheduler. It also gives audit a defensible explanation for every restatement.
3. Design the reconciliation to the general ledger and explain what should happen to the €180k–€420k monthly suspense balance.
Read the worked answer
The suspense balance is a symptom of unmatched transactions, not a budget line. Build a reconciliation chain with four checkpoints: store control totals, raw ingestion, published warehouse facts, and the general ledger. At each checkpoint, compare counts and amounts and store the result as evidence.
At transaction level, match on business key and amount with explicit tolerances. Classify unmatched items into causes: duplicate ingestion, timing cut-off, missing store file, refund without sale, price or tax mismatch, or genuine ledger error. Assign owners and ageing service levels. Auto-match the high-confidence population; route the remainder to an exception queue.
For the suspense account, set a materiality threshold and an ageing policy. Every residual balance should have an identified item or a documented investigation. Post adjustments only under finance approval, and reverse or clear items as they match. Do not use the suspense account to absorb unexplained differences at close.
Track metrics: match rate, unmatched value and count, ageing, top recurring causes, and the value cleared each period. Target a measurable reduction in the unexplained balance over two closes, with root-cause fixes such as file-arrival monitoring and duplicate prevention.
Codify the reconciliation rules and thresholds as shared metadata and automated tests so the new orchestration tool runs the same controls rather than a rebuilt approximation.