The report that was wrong every Monday
You have done this today
A small shop emails a sales report every Monday. Every few weeks the totals come out too high.
What happens behind the screen
Nobody changed the code. What changed is that the job crashed halfway one night and was started again, and the rows it had already saved were saved a second time.
Step through the pipeline and see where the double count appears.
The job reads the day's orders from the source.
The idea in plain words
ETL means extract, transform, load. The lesson sits in the last step. A job that can run twice and leave the same result is called idempotent.
Two common ways to get there: an upsert, which updates a row if its key exists and inserts it otherwise, or loading into a staging table and swapping it in as one step.
An upsert instead of a blind insert
def load_sales(conn, rows):
# 'order_id' is unique, so running this twice cannot double count.
conn.executemany(
"""
INSERT INTO sales (order_id, amount, sold_on)
VALUES (?, ?, ?)
ON CONFLICT(order_id) DO UPDATE SET
amount = excluded.amount,
sold_on = excluded.sold_on
""",
rows,
)
conn.commit()If an interviewer asks
"A nightly pipeline crashed and was re-run. How do you make sure data is not duplicated?"
You could say
I would make the load idempotent. Either an upsert keyed on a unique id, or load into a staging table and swap it in atomically, so re-running a failed job leaves the same result.
Check yourself
What does it mean for a job to be idempotent?
Which load step is safe to re-run?
Was this clear?
Up next
How an app knows what you will like next
Vectors and similarity, with nothing harder than a ruler.