Nightly Load Fails Only on the Third Tuesday
Your team runs a nightly pipeline that loads 14 supplier CSVs into a warehouse staging table. Each file arrives between 01:00 and 03:00 via SFTP. The job polls for files, concatenates them, and inserts. It has failed three times in eight weeks, always on the third Tuesday, always with a duplicate-key error on supplier_id + load_date. The on-call engineer reruns the job manually and it succeeds. Logs show the failing runs started at 02:55 rather than the usual 01:10, because the upstream scheduler that triggers your job was delayed by a monthly payroll batch. File counts at failure time were 14, matching expectations. The staging table has no unique constraint, but the downstream merge does. Your manager wants a fix before the next payroll cycle and has ruled out changing the upstream scheduler.
1. Diagnose the most likely root cause and explain why the manual rerun succeeds while the scheduled run fails.
Read the worked answer
The timing shift is the key signal. When the job starts at 01:10 it finishes well before the 03:00 SFTP cutoff, so it processes each file once. When payroll delays the trigger to 02:55, the job overlaps with late-arriving files. A file that lands at 02:58 is picked up by the polling loop in one pass and again in a subsequent pass if the loop re-scans the directory without tracking which filenames it has already consumed. The concatenation then contains the same supplier rows twice, and the downstream merge rejects the duplicate supplier_id + load_date pair.
The manual rerun succeeds because by the time the engineer responds, all 14 files are present and stable; a single clean pass reads each file exactly once. The failure is not about file count (14 either way) but about read-once semantics under a moving directory.
So the fix is idempotent ingestion: record processed filenames with checksums in a control table, skip anything already loaded, and make the merge an upsert keyed on supplier_id + load_date rather than an insert-then-merge. That removes the dependence on start time entirely.
2. The manager proposes simply moving the job trigger to 04:00. Evaluate this and state what you would do instead.
Read the worked answer
Moving the trigger to 04:00 buys headroom but does not fix the defect. It assumes payroll never runs longer, that no file ever arrives late, and that no one ever reruns the job concurrently. Any of those reintroduces the same duplicate-key failure, and the failure will be harder to spot because it will look like a one-off.
I would treat the schedule change as a mitigation, not a fix, and pair it with the idempotency work: a control table of processed filenames plus checksums, a merge that upserts on the natural key, and a guard that refuses to start if a prior run is still active. I would also add an alert when the job starts more than 30 minutes after its expected time, so the timing drift is visible before it causes a failure. The schedule change is cheap and worth doing, but it should ship alongside the structural fix, not instead of it.