Case 07 · Idempotency

The job ran twice. Now yesterday earned double.

Nobody typed a wrong number. Nobody broke anything. The same job simply ran a second time, which jobs do every day. If you only learn one thing from this whole series, learn this one.

Start here

Your jobs will fail. Often.

Nobody tells you this on day one. Pipelines break all the time. It is not bad luck. It is the weather.

A file turns up late. The partner sent it at 03:40 instead of 01:00.

A machine runs out of memory. Halfway through, it stops.

The network drops for two seconds. That is enough.

Someone upstream changes something. They did not know you existed.

So jobs get run again. Constantly. The scheduler retries on its own. You run last night’s job by hand because a number looked odd. A teammate runs it once more, just to be safe.

It will run twice. That is not the question.

What matters is what happens next. Does the table end up correct? Or does yesterday get counted a second time, with nothing on fire and nobody warned?

The idea, in one picture

Setting an alarm, or pushing it earlier.

Here are two things you can do to your alarm clock.

Safe to repeat

“Set it to 6:00”

6:00
6:00
6:00

Do this once, or ten times. The alarm still says 6:00. The tenth time changes nothing.

Not safe to repeat

“Make it ten minutes earlier”

5:50
5:40
5:30 …

Do this ten times by mistake and you are up at 4:20. Each go builds on the last one.

That is the whole idea. Some steps land in the same place however many times you do them. Others stack up.

A step that always lands in the same place has a name. People call it idempotent. It is an ugly word for a simple thing: run it again and nothing changes.

You do not have to like the word. You do have to know which of your steps are which.

Try it

Five orders. One job. Press run as many times as you like.

This is yesterday’s sales going into a table. Pick how the job writes, then run it. Then run it again. Then crash it halfway and let it retry, which is what really happens at night.

how the job writes

what the job reads · 29 june

the table · empty

nothing written yet

0rows in the table

₹0revenue it reports

₹500what really happened

Press Run the job once. Then press it again and watch the table.

The scary part

Nothing goes red.

Look again at what happened with add the rows.

No error. No warning. The job said it worked. It worked perfectly. It followed your instructions, twice.

Yesterday now shows double. Next week someone opens that number in a meeting and makes a decision with it. Nobody in the room has any reason to doubt it.

A job that fails loudly is a good day. This is the other kind.

And notice the crash case. The job wrote three rows, died, and ran again from the top. Now the table has eight rows for a day that had five orders. Not double. Just wrong, in a way that is very hard to spot later.

Fix one

Match on the order id.

Instead of writing a new row every time, the job checks first.

Look for this order id. Found it? Write over it. Not there? Add it.

That is it. Press run a second time and every order finds itself already sitting in the table. Each one gets written over with exactly what it already said. The table stays five rows long.

Add the rows

Never looks first

insert A-1 → new row
insert A-1 → new row
insert A-1 → new row

Three rows for one order. Nothing ever looked to see if it was there already.

Match on the id

Looks first, every time

A-1 there? no  → add it
A-1 there? yes → write over it
A-1 there? yes → write over it

One row for one order, forever. You can run it a hundred times.

Different tools give this different names. You will see it written as a merge, or an upsert, which is just “update or insert” squashed into one word.

Fix two

Throw the day away, then write it again.

This one is simpler, and most daily jobs use it.

Before you write 29 June, you delete everything already in the table for 29 June. Then you write the whole day fresh.

Run it again and the same thing happens. Delete that day. Write that day. The table ends up identical every single time.

Why people like it. There is nothing clever to get wrong. You replace a whole, clearly marked slice of the table.

What you need for it. The table has to be split into slices you can point at — usually one per day. If you cannot say “delete 29 June”, you cannot do this.

Where it beats the other fix. If yesterday had six orders and one got cancelled, matching on the id updates five and leaves the sixth sitting there forever. Replacing the day removes it properly.

That last point is worth keeping. Matching on the id fixes rows that changed. Only replacing the slice gets rid of rows that should no longer exist.

The thing that breaks both fixes

The id must not change between runs.

Both fixes depend on one quiet assumption. The job must be able to recognise a row it has seen before.

So where does the id come from? If you make it up fresh each run, nothing is ever recognised.

Breaks everything

Made up at run time

id = random()
id = now()
id = row_number()

Same order, new id, every run. The table thinks it is a brand new order. You are back to duplicates, with a fix in place that looks like it should have worked.

Works

Taken from the data

id = order_id
id = order_id + date
id = customer + product + day

The same order gives the same id today, tomorrow and next year. That is what lets the job know it is the same thing.

The rule is short. Build the id out of the order itself. Never out of the time or a random number.

This is a common bug and a nasty one, because the pipeline looks correct. Somebody did add a merge. It just has nothing stable to match on.

One more trap

Never add today to yesterday’s total.

This one hides in reporting jobs. Both of these look like they do the same thing. Press run a few times and watch them come apart.

adds to what was there

₹0

total = total + today

works it out again

₹0

total = sum of every day

Both start at zero. Press run.

The one on the left is not wrong on the first run. It is only wrong on the second. And it is wrong quietly, by exactly one day’s worth, every time anybody re-runs anything.

So the habit is simple. Work the answer out from the raw data every time. Never from the last answer. It feels wasteful. It is what makes a second run safe.

The hard case

You cannot un-send an email.

Rows you can overwrite. An email has already left. So what happens when the job that sends “your order has shipped” runs twice? Press run a few times, then switch the check on and press it again.

before sending, look at the sent list

what Asha received

0times the job ran

0emails she got

—how she feels

Press run. Then press it again, the way a retry would.

The fix is a small table of what you have already done. Before sending, you look: have I already sent this one? If yes, skip it. If no, send it and write it down.

Payment companies do exactly this, and they make you help them. You hand over a key with your request, and if the same key turns up again they return the first answer instead of charging the card twice.

Same idea, same reason. Something in the world has already happened, and you cannot take it back, so you have to remember that you did it.

Three mix-ups

Words that sound like this one.

“Is this just exactly-once?” Not quite. Exactly-once is a promise a messaging system makes about delivery. Being safe to re-run is something your own code does. The useful bit: you rarely get a real promise of exactly-once, so instead you accept that things may arrive twice and write code that does not care. That combination gets you the same result, with far less magic.

“Update is safe, insert is not.” Mostly true and a good instinct. Setting a value to 60 stays 60 however often you do it. Adding a row adds another row. If you have done web work, it is the same reason a PUT is repeatable and a POST is not.

“Our tool handles it.” Check. A very common setup appends new rows by default, which is the unsafe one. The safe setting is usually there, usually one word, and usually not switched on. Worth ten minutes of your afternoon.

A fair question

Where in the pipeline does this go?

Somebody asked this under one of these articles and it deserves an answer, because “everywhere” is not useful.

Where the data lands first. Write one file per batch, with the batch in the file name. Run it again and you overwrite that same file. Never write files with a timestamp in the name, or you end up with four copies of the same morning and no way to tell.

In the middle, where you clean and join. This is where replacing the day belongs. Each run rebuilds a whole day from what is underneath it.

At the end, where people read it. Totals get worked out from the source, never added to. This is the running-total trap from earlier.

Anything that touches the outside world. Emails, payments, messages to other systems. These need the list of what you already did.

The short version: every step that writes something needs an answer to this. Not one layer. All of them.

If you get asked

“Your job dies at 3am. What then?”

This comes up constantly, because the answer shows whether you have run anything real.

Say what you are aiming for. One run or ten runs, the table should look identical afterwards.

Say what goes wrong without it. A job that just adds rows gets retried and counts the day twice. Nothing errors. Nobody finds out for a week.

Say your two fixes. Match on the order id, or delete the day and write it again. Then add the bit most people miss: the id has to come from the data, not from the clock.

Then go one further. Mention that totals get worked out again rather than added to. And mention emails and payments, where you keep a list of what has already gone out.

That last part is what makes the answer sound like experience rather than revision.

Two questions

Did it land?

A job adds yesterday’s 500 orders to a table. It dies after 300 rows, and the scheduler starts it again. What is in the table now?

You added a merge, but rows still double on every run. What would you check first?

Your playbook · Case 07

Eight things to keep

It will run twiceSchedulers retry. People re-run. Build for that, because it is normal, not rare.
Safe means nothing changesRun it once or ten times and the table looks the same. That is the whole idea.
Adding rows is the unsafe oneA plain append counts the day again every time, with no error and no warning.
Match on the id, or replace the dayTwo fixes. Matching updates what changed. Replacing also removes what should be gone.
Build the id from the dataNever from the clock, never random. If the id changes each run, nothing can be recognised.
Work totals out again, do not add to themAdding today to yesterday is only wrong on the second run, which is the run you will not notice.
Keep a list for things you cannot undoEmails, payments, messages out. Check the list before you act, and write it down after.
Then a failure is boringNothing to clean up by hand at 2am. Start it again and it lands right.

Your review

How was this case?

Loading ratings…

Next · Case 08

They added a column. Everything broke.

Case 08 is about adding and removing columns without taking down everybody who reads them. And what to do when somebody upstream does it to you without warning.

Open Case 08 →← Case 06