A Retried Job Double-Counted Revenue — Diagnose and Redesign

1. What happened

The first run's INSERT almost certainly completed successfully on the warehouse side — the rows were written and committed — but the connection dropped before the acknowledgment made it back to the orchestrator, so the orchestrator has no way to distinguish "the write never happened" from "the write happened, but I never heard back." It correctly (from its own local information) treated this as a failure and, per its retry policy, ran the exact same INSERT ... SELECT again two minutes later. staging.transactions_new hadn't changed (the upstream extract job runs once, before the load step, not per-attempt), so the second run selected and inserted the same rows a second time. Nothing errored because there's no primary key or unique constraint on revenue_fact (or none enforced against transaction_id) to reject the duplicate rows, and a bare INSERT ... SELECT has no built-in awareness of what a previous, possibly-successful run already wrote. This is the textbook gap between "at-least-once delivery" (the retry, which is the correct instinct on ambiguous failure) and idempotent writes (missing here) — at-least-once without idempotency gives you duplicates, not effectively-once processing.

2. Redesign

Make the write idempotent by scoping it to a well-defined, repeatable unit and replacing rather than appending. Two viable options depending on what's available:

• If staging.transactions_new always represents exactly "this hour's batch" and the target table is partitioned by load hour, do an atomic partition overwrite: delete the target partition for this hour, then insert, wrapped in a single transaction (or use a native atomic partition-replace operation if the warehouse supports one).

• More robustly, since transaction_id is a natural primary key, use a MERGE:

  MERGE INTO warehouse.revenue_fact AS target
  USING staging.transactions_new AS source
  ON target.transaction_id = source.transaction_id
  WHEN MATCHED THEN
    UPDATE SET amount = source.amount, transaction_ts = source.transaction_ts
  WHEN NOT MATCHED THEN
    INSERT (transaction_id, account_id, amount, transaction_ts)
    VALUES (source.transaction_id, source.account_id, source.amount, source.transaction_ts);
  

This is safe under an arbitrary number of retries, not just one, because each execution of the MERGE is a deterministic function of the current state of revenue_fact and the current content of staging.transactions_new — running it once, twice, or ten times in a row (as long as the staging content doesn't change between runs, which is true here) leaves revenue_fact in exactly the same end state every time. There's no accumulation effect the way there is with a bare INSERT, because matched rows are overwritten with the same values rather than appended alongside the existing ones.

3. Is the truncate-and-repopulate staging step also a risk?

Yes, independently of part 2's fix, and it's worth naming explicitly. If the upstream extract job itself fails partway — e.g., it truncates staging.transactions_new and then dies before finishing the repopulate — a subsequent retry of the load step (even with the idempotent MERGE from part 2) would run correctly against whatever partial or empty staging content exists at that moment, silently loading fewer rows than the actual hour's transactions, with no error raised. The load step being idempotent doesn't protect against its input being wrong. The staging population step needs the same idempotency discipline — write to a new staging table/partition and atomically swap it in, or otherwise ensure a failed repopulate doesn't leave transactions_new in a state that looks valid but is a truncated, empty, or partial snapshot to whatever reads it next. Idempotency has to be designed at every step that writes state a subsequent step or retry depends on, not just the final load into the fact table.