Idempotency, Retries & Exactly-Once

The shop runs a daily job. Each morning it copies one day of orders into the warehouse table orders. Then it writes one row for that day into daily_revenue.

daily_revenue holds the money from paid orders, one row per day. The shop has six orders:

SELECT * FROM orders;

On 2026-01-10 only order 102 is paid. So daily_revenue for that day must be 25.00. Keep that number in mind.

Jobs fail halfway. A scheduler is the tool that starts jobs on time.

When a job fails, the scheduler runs it again. That second run is called a retry.

If the job is not safe to run twice, a retry can double the money. No error appears. In this chapter you will learn:

1. Why jobs run twice, and what goes wrong.
2. What idempotent means, and how to make the daily job idempotent.
3. The three delivery promises: at-most-once, at-least-once and exactly-once.
4. How to remove repeated messages by key.
5. Whether a stream consumer commits its offset before or after it writes.
6. Where exactly-once really lives.

Recap: a retry runs a failed job again. In the Python chapters, you retried only temporary errors, with backoff.

Now see what a retry does when a job has two steps.

Now look at a retry that goes wrong. The job runs for 2026-01-10.

The warehouse already holds order 101 and the revenue row for 2026-01-03. This job only appends rows:

-- Step 1: copy the day's orders
INSERT INTO orders (order_id, customer_id, status, amount, order_date)
SELECT order_id, customer_id, status, amount, order_date
FROM source_orders
WHERE order_date = DATE '2026-01-10';

-- Step 2: write the day's paid revenue
INSERT INTO daily_revenue (order_date, paid_revenue)
SELECT order_date, SUM(amount)
FROM orders
WHERE order_date = DATE '2026-01-10' AND status = 'paid'
GROUP BY order_date;

The retry copied order 102 a second time. Step 2 then added 25.00 and 25.00.

The result is 50.00, but the right answer is 25.00. The retry ended with no error, so nobody is warned.

Recap from chapter 1: idempotent means two runs leave the same result as one run. Chapter 1 ran a delete-insert twice. Now the first run crashes in the middle.

Think of a light switch. A toggle switch is not safe to repeat: press it twice and the light is off again. A switch you set to ON is safe: press it twice and the light is still on.

The daily job must act like "set to ON", not like "toggle".

For the daily job, "set to ON" means: replace everything for that date. This is a delete-insert of one partition. A partition is one slice of a table, here the rows of one day.

Put all four steps in one transaction (BEGIN ... COMMIT, from chapter 1). Here is the safe job:

BEGIN;

DELETE FROM orders WHERE order_date = DATE '2026-01-10';
INSERT INTO orders (order_id, customer_id, status, amount, order_date)
SELECT order_id, customer_id, status, amount, order_date
FROM source_orders
WHERE order_date = DATE '2026-01-10';

DELETE FROM daily_revenue WHERE order_date = DATE '2026-01-10';
INSERT INTO daily_revenue (order_date, paid_revenue)
SELECT order_date, SUM(amount)
FROM orders
WHERE order_date = DATE '2026-01-10' AND status = 'paid'
GROUP BY order_date;

COMMIT;

The crash left nothing behind, because the database rolled the transaction back. The retry deleted the day, loaded order 102 once, and wrote 25.00. The manual rerun changed nothing.

Without the transaction, a crash after the first DELETE leaves the day empty. Readers see no revenue until the retry finishes.

The same input every time

A retry must also read the same input. The job must not ask the clock which day to load. A retry runs later, so the clock gives a different day.

Use the logical date instead. This is the day the run is for, not the day it runs.

The scheduler passes it to the job. Compare the two filters:

-- Not safe: the day depends on when the job runs
WHERE order_date = CURRENT_DATE - 1

-- Safe: the scheduler passes the run's logical date
WHERE order_date = DATE '2026-01-10'

With the clock, the rerun loads the wrong day. 2026-01-10 stays unfixed.

Do not use NOW() for loaded values either. A retry gets a different time, so the rows change.

Now the shop sends each paid order to a consumer as a message. A message is one small piece of data, such as "order 102 paid 25.00". A consumer is a program that reads messages and writes them to the warehouse.

The consumer replies "got it" for each message. This reply is an acknowledgement, or ack.

Sometimes the message is lost. Sometimes the ack is lost.

The sender sees only silence. It cannot tell the two cases apart:

• The message was lost. The consumer never saw order 102.
• The ack was lost. The consumer saw order 102 and wrote it.

The sender must choose what to do after silence. Each choice is a delivery promise:

You cannot remove both risks with delivery alone. The sender never knows which case happened. Exactly-once needs more help from the rest of the system.

For money data, a lost order is worse than a repeated order. So most pipelines choose at-least-once.

Then they make repeats harmless. The next two sections show how.

At-least-once means the same message can arrive twice. You need a way to say "I already have this".

The answer is a key: a value that names exactly one thing. In the shop, order_id names one order.

You write by key with MERGE, from the loading chapter. Here order 102 is already in orders, and it arrives again:

MERGE INTO orders AS t
USING incoming_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET status = s.status, amount = s.amount
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, status, amount, order_date)
  VALUES (s.order_id, s.customer_id, s.status, s.amount, s.order_date);

Order 102 already existed, so MERGE updated it to the same values. Nothing changed.

Order 103 was new, so MERGE inserted it. A second run of the batch gives the same three rows.

Some messages have no natural key. Then the sender creates an idempotency key: a unique id made once per action and sent again with every retry. Payment services, for example Stripe, accept one so a retried charge is billed once.

Some jobs keep a list of keys they have seen, in memory. Memory is small, so the list keeps only recent keys.

A copy that arrives later is missed. Writing by key into the target table has no such limit.

A stream keeps messages in order. Each message has an offset: its position number, starting at 0.

To commit an offset means to save it, like a bookmark. This is not the SQL COMMIT of a transaction. After a crash, the consumer restarts after the last saved offset.

Here are three paid orders on a stream:

The sink is the table the consumer writes to. Here it is paid_orders. For each message, the consumer does two things: it writes the row to paid_orders, and it commits the offset.

The order matters.

Say the consumer crashes while it handles offset 1, order 102. The correct total is 40.00 + 25.00 + 15.50 = 80.50:

In row 1, offset 1 was already committed, so the consumer restarted at offset 2. Order 102 was never written. In rows 2 and 3, the last commit was offset 0, so the consumer read order 102 again.

So commit-then-write is at-most-once: it can lose a message. Write-then-commit is at-least-once: it can repeat one. Write-then-commit by key gives the effect once.

Commit after you write, and write by key. Nothing warns you about a lost row, so it is hard to find and fix. A repeated row is harmless when paid_orders ignores keys it already has.

Kafka, a popular stream tool, saves the next offset to read. The idea is the same.

INSERT INTO paid_orders (order_id, amount)
VALUES (102, 25.00)
ON CONFLICT (order_id) DO NOTHING;

The replayed order 102 hit the same key, so the database skipped it. The consumer then commits offset 1 and moves on.

Exactly-once is not a setting you switch on. It is a property of the whole path: the source, the job and the sink. It holds only when each part does its share.

If one part fails its share, the result is wrong. A job that writes with plain INSERT duplicates rows, even when the source is perfect. A job that commits too early loses orders, even when the sink is perfect.

Some systems promise exactly-once inside their own system. Kafka transactions and Flink checkpoints (two popular streaming tools) are examples.

When you write to a database outside, that database must also handle repeats. One way is to save the offset in the same transaction as the rows.

Then the rows and the offset are saved together, or not at all. Chapter 15 shows how Kafka and Flink do this.

Engineers call the result effectively-once. A message may arrive twice, but its effect happens once.

Check these points before you move on:

• Retries are normal. A job must be safe to run twice.
• Idempotent means two runs leave the same result as one. Use delete-insert of the day in one transaction, or write by key.
• Use the run's logical date, never the clock.
• At-most-once can lose data. At-least-once can repeat data. Choose at-least-once and make repeats harmless.
• Commit the offset after the write.
• Exactly-once is a property of source, job and sink together.

Test yourself. Cover the answers first:

1. You run the daily load for 2026-01-10 twice. What does daily_revenue show for that day?
2. The consumer commits offset 1, then crashes before it writes order 102. What is the total in paid_orders at the end?

Answers: (1) 25.00, both times. (2) 55.50, because order 102 is lost.

Each scenario is a design task on the Pipeline Builder, the practice canvas where you draw a pipeline. The graded check looks at your design and tells you which requirement it missed.

Start with the first two. They ask you to make a repeat safe.