A daily job re-ran after a retry and that date's revenue doubled — find the design flaw, not the bad row
A daily job loads yesterday's revenue into a mart. After a transient failure the orchestrator retried it, and that date's revenue came out doubled. The bug is not a bad row — it is the load design. Find the flaw and make the load idempotent.
-- runs daily for :run_date
INSERT INTO revenue_daily (order_date, revenue)
SELECT order_date, SUM(amount) AS revenue
FROM orders
WHERE order_date = :run_date
GROUP BY order_date;The retry ran this a second time for the same :run_date. Fix the load so a re-run leaves revenue_daily unchanged.
The load is append-only — a second INSERT for the same :run_date double-counts the date. Make it idempotent by replacing the date each run: DELETE the :run_date rows then INSERT, or MERGE — so a re-run overwrites instead of appending.
- ✗Blaming a bad row instead of the append-only load design
- ✗Trying to cancel the doubling with AVG instead of replacing the date
- ✗Deduplicating at read time instead of making the write idempotent
- →Why does DELETE-then-INSERT of the date partition make the load idempotent?
- →How would a unique constraint on order_date have surfaced this bug sooner?
The bug is the load design, not the data — the INSERT appends rows. A second run for the same :run_date adds another aggregated row and the date doubles. Make the load idempotent by replacing the target date's rows instead of appending:
-- idempotent: replace the date partition each run
BEGIN;
DELETE FROM revenue_daily WHERE order_date = :run_date;
INSERT INTO revenue_daily (order_date, revenue)
SELECT order_date, SUM(amount) AS revenue
FROM orders
WHERE order_date = :run_date
GROUP BY order_date;
COMMIT;Wrapped in one transaction, DELETE + INSERT make a re-run replace the date rather than append — the output for a date becomes a pure function of the input. Portable equivalents are MERGE (upsert on order_date) or INSERT OVERWRITE of the partition. AVG or a read-time SELECT DISTINCT only mask the symptom: the write stays non-idempotent and the next retry doubles it again.
