learning-it by example

#112 Data engineering and pipelines 6.5 min

Idempotent Loads: Append, Upsert, Replace, Delete Insert

Loading the same day of sales twice with a plain append doubled it from 63.35 to 126.70; three other strategies stayed at 6 rows.

1. What does it mean for a data load to be idempotent?
2. A day of 6 made-up sales is loaded twice with a plain append. What does the day hold afterwards?
3. A corrected file arrives with one sale fixed and one sale cancelled. Which load strategy matches the corrected file exactly (5 rows, 62.35)?
4. A load commits row by row and the connection drops after 3 of 6 rows, then the load is simply run again. What does append end up with?
5. Why does a load that uses the with block on the connection survive a crash half way?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Run the three comparisons

    Copy the code folder first. Run python run.py twice, then python run.py corrected, then python run.py crash. For each one, find the line for append and the line for expected and write down the difference in rows and in day total.

    A hint

    Append is the strategy that moves away from expected in the twice case: it adds the same rows again.

  2. Move the crash point

    In run.py, find the crash entry in MODES. The last number in it, 3, is how many rows are handed over before the connection is lost. Change it to 5 and predict the append row count after the rerun and the rows left after the crash for upsert. Then run python run.py crash to check (the title line of the output still says 3 of 6 rows; ignore it). Run on this machine, append shows 5 rows after the crash and 11 after the rerun, and upsert shows 0 after the crash.

    A hint

    Append commits every row as it goes. The others use one transaction.

  3. Take out the delete

    In strategies.py, remove the line inside delete_insert that runs the DELETE. Before you run anything, predict what python run.py twice will print for delete_insert. Then run it. On this machine delete_insert shows 6 rows after run 1 and 12 after run 2, with a day total of 126.70.

    A hint

    Without the delete, the function is an append with a transaction around it.

  4. Add a column the file does not send

    Add a note column to the CREATE TABLE line in strategies.py, name the columns in the INSERT and INSERT OR REPLACE statements so the extra column does not break them, and give the table one sale with a note already filled in. Then compare what insert or replace and upsert do to that note when the same sale is loaded again. This one is a prediction task: read the video's limits section first, then write down which strategy you expect to lose the note and why, and then test your guess.

    A hint

    Replace deletes the old row first, then inserts the new one. The upsert only updates the columns it names.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

Six made-up sales in SQLite, one process at a time, no timings; every file holds the whole day, which delete insert needs; replace and upsert would differ on a column the file does not send or a sale that changes its day, and this run has neither.

Up next in Data engineering and pipelines

More in Data engineering and pipelines

All Data engineering and pipelines videos

Related videos