The Nightly Report That Sometimes Runs Twice
A finance team runs a nightly automation that pulls yesterday's transactions from an ERP export, reconciles them against a bank feed, and emails a summary. It is scheduled at 02:00. Last month the summary email arrived twice on 6 of 30 nights, and once arrived with 0 transactions even though the bank feed clearly had activity. The ERP export job is triggered by a file-drop watcher; the reconciliation step runs on a 15-minute polling schedule that checks whether an export file exists. The email step sends whenever the reconciliation output file is newer than the last-sent marker. Nobody changed the schedule recently, but the ERP vendor deployed a patch that made exports complete 20 minutes later than before.
1. Diagnose the most likely cause of the duplicate emails and the zero-transaction email, and explain how the scheduling design contributes to both symptoms.
Read the worked answer
The design has three loosely coupled triggers with no shared notion of a single run.
Duplicates. The reconciliation poller checks every 15 minutes whether an export file exists. Previously the export landed before 02:00, so the first poll after 02:00 found it and reconciled once. After the vendor patch, the export lands around 02:20. Depending on timing, the poller can find a partial file (vendor still writing), reconcile it, and then find the completed file on the next poll and reconcile again. Two output files, two 'newer than marker' checks, two emails. The email step's only guard is a timestamp comparison, which does not prevent a second run within the same night.
Zero transactions. If the poller grabs the export while it is still being written, the file may exist but be empty or truncated. Reconciliation against an empty input produces a valid-looking output with 0 rows, which passes the 'newer than marker' test and gets emailed.
Root cause is the trigger model, not the vendor. Polling for file existence is not the same as detecting file completion, and 'output is newer than a marker' is not the same as 'this run has not already sent.'
2. Propose a concrete redesign that fixes both symptoms without requiring the ERP vendor to change anything.
Read the worked answer
Move from 'poll for existence' to 'detect completion and run once.'
- Completion signal. Have the watcher wait for a stable file: either the vendor's
.done/.oksidecar if one exists, or a stability check (size unchanged across two checks N seconds apart) plus a parse validation that the file contains a header and at least the expected schema. Reject empty or headerless files and retry rather than proceeding. - Single run identity. Assign each run a deterministic key (e.g., the export's business date plus a hash of the file). Record it in a run ledger before sending. The email step checks the ledger, not a timestamp, so a second reconciliation for the same key is a no-op.
- Idempotent send. Make the email step write 'sent' to the ledger before dispatching and treat a duplicate key as already handled. If send fails, the ledger entry is marked failed and retried explicitly - not by re-running the whole pipeline.
- Explicit failure path. A zero-row reconciliation should raise an alert to a human rather than email a summary. Zero rows is almost always a data problem, not a real business day.
- Schedule slack. Since exports now land ~02:20, either move the nominal start to 02:45 or keep the poller but let it wait for the completion signal instead of acting on first sight.
The principle: the pipeline should have one unambiguous 'a run happened' event, and every downstream step should be safe to invoke twice.