When a Stage Runs Twice

Concepts covered: Idempotency

Stages fail, and the usual response is to run them again. Take a cut-down day of 4 orders whose load into silver dies at 02:04 on a Snowflake network timeout, followed by a successful rerun at 02:31. Whether that rerun repairs the day or corrupts it depends entirely on how the step writes.

This load appends: it runs INSERT INTO curated_orders with every row in the window. The first attempt wrote orders 101 to 103 before the connection dropped, and the rerun inserted all 4 orders, including the 3 already present. No step errored, so no alert fired. SUM(amount) for the day now returns 269.00 against true revenue of 143.50.

The query below reads the damaged table and recovers the truth by keeping 1 copy per order_id, the newest by loaded_at. Watch orders 101 to 103: each appears twice, and ROW_NUMBER() gives the rerun's copy 1 and the original copy 2.

SELECT order_id, amount, loaded_at
FROM (
  SELECT order_id, amount, loaded_at,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) AS rn
  FROM curated_orders
) AS ranked
WHERE rn = 1;

The filter rn = 1 drops 3 rows. What remains is 4 orders summing to 143.50. That repairs 1 read, while every other query against curated_orders still sees 7 rows. The durable fix belongs in the write.

Idempotent writes

A step is idempotent when running it once or 5 times leaves its destination in the same state. Idempotency is what makes a retry safe, and every pipeline gets retried eventually. Orchestrators retry failed tasks on their own, and engineers rerun steps by hand or as part of a backfill. Either of 2 write patterns provides it.

Overwrite the partition
  • The run owns 1 window, such as order_date = '2024-03-14'
  • It deletes that window's rows, then inserts, in 1 transaction
  • A rerun replaces the day wholesale
  • Suits batch loads with a clean date boundary
Merge on a key
  • MERGE matches incoming rows on order_id
  • Matching rows are updated and new rows inserted
  • A rerun sets rows to values they already hold
  • Suits loads where rows arrive across many dates

Both patterns depend on the run knowing its window from its schedule rather than from the clock. A step filtering created_at >= now() - interval '1 day' covers a different window every time it starts, so a rerun at 09:00 on 15 March skips the first 9 hours of the 14th and pulls in 9 hours of the 15th. Orchestrators pass each run a logical date, the start of the window it owns, and the step should filter on that.

Empty and partial inputs

Overwrite has an edge of its own. If extraction returns 0 rows because a credential expired, the overwrite deletes the day's 6,200 good rows and inserts nothing in their place, yet the task still reports success. An empty window is sometimes real, such as a shop closed for a holiday, so the step needs a rule: compare the incoming count with the same weekday last week and stop the load when it falls by more than 80%.

The case below arrived as a finance ticket. From the evidence you can work out which stage did the damage and how it writes, and that points straight at the fix.

Finance reports that the dashboard shows $71,900 of revenue for Tuesday 12 March, nearly double a normal weekday. No task failed overnight, and Wednesday looks normal. What change makes the next rerun safe?

The append load wrote 5,320 orders twice, and gold faithfully summed the doubled silver. Rerunning was the correct response from the on-call engineer, and the write pattern is what turned it into damage.

About this lesson

This section is part of the Pipeline Anatomy: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.