Free lesson
Loads you can run twice
Jobs crash and get retried. Learn the property that keeps a retry from corrupting your data.
In this lesson you will
- Load a batch of new rows
- See what goes wrong when a job is retried
- Write a load that is safe to run twice
Words you'll meet
- Batch
- A group of new rows loaded together.
- Staging
- A table where new data waits before it is loaded.
- Idempotent
- Gives the same result whether it runs once or many times.
The lesson, step by step
Each step teaches one idea, then hands you a real database to try it on. The exercises run in your browser.
Step 1 of 6
The setup
Pipelines usually load data in batches.
ordersholds what was loaded so far. Today's new batch is waiting instaging_orders.Your turn: Count the rows currently in orders.
Step 2 of 6
Load the batch
INSERT INTO target SELECT ... FROM sourceappends the rows of a query to a table.A staging table is a waiting room. New data lands there first, where it can be checked, and only then moves into the main table.
Your turn: Append every row from staging_orders to orders.
Step 3 of 6
The retry
Bad news. Right after your load, the network blipped. The scheduler never heard that the job finished, so it did what schedulers do: it ran your load again.
Schedulers retry on purpose, because most failures are temporary. The catch is that a job can finish its work and still look failed, if the message saying "done" is the thing that got lost.
So a load must expect to run at least once, and sometimes more.
Your turn: Count the rows in orders now.
Step 4 of 6
Find the damage
Seven rows for five orders. Revenue reports are now wrong, and nothing raised an error.
HAVINGfilters groups the wayWHEREfilters rows. It is the classic duplicate detector:SELECT key, COUNT(*) FROM t GROUP BY key HAVING COUNT(*) > 1
Your turn: Show each order_id that appears more than once in orders, with its count.
Step 5 of 6
Idempotent
The fix is not "hope retries never happen". They always do. The fix is to write loads that are idempotent.
Say it eye-dem-PO-tent. Pressing an elevator button twice is idempotent: the elevator still comes once. Ordering a pizza twice is not.
Your turn: What should be true of an idempotent load?
Step 6 of 6
Make it safe
We rolled
ordersback to its original 3 rows. Your turn to write the load properly.The classic pattern is delete, then insert: first remove any rows this batch is about to load, then load them.
DELETE FROM orders WHERE order_id IN (SELECT order_id FROM staging_orders); INSERT INTO ...
Separate the two statements with a semicolon. We will run your whole script twice, like a retrying scheduler would.
Delete-then-insert is one of several patterns.
MERGE, also called upsert, updates a row if it exists and inserts it if not. Small tables are often simply rebuilt from scratch every run. Large ones are reloaded one day at a time.In production the delete and the insert are wrapped in a transaction, so that both happen or neither does.
Your turn: Write a load from staging_orders into orders that is safe to run twice.
Reading is the easy part.
Every step above has an exercise. Do it for real, free, no account needed.
Start the lesson