Free lesson

Loads you can run twice

Jobs crash and get retried. Learn the property that keeps a retry from corrupting your data.

8 min6 steps

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.
Start the lesson

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.

  1. Step 1 of 6

    The setup

    Pipelines usually load data in batches. orders holds what was loaded so far. Today's new batch is waiting in staging_orders.

    Your turn: Count the rows currently in orders.

  2. Step 2 of 6

    Load the batch

    INSERT INTO target SELECT ... FROM source appends 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.

  3. 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.

  4. Step 4 of 6

    Find the damage

    Seven rows for five orders. Revenue reports are now wrong, and nothing raised an error.

    HAVING filters groups the way WHERE filters 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.

  5. 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?

  6. Step 6 of 6

    Make it safe

    We rolled orders back 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