#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.
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.
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.
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.
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
Related videos
- #111 Incremental Loads: Watermarks and the Late Update Trap Data engineering and pipelines
- #115 Schema Evolution Without Breaking Pipelines Data engineering and pipelines
- #263 Medallion Architecture: Bronze, Silver, Gold in Python Data modelling
- #266 Event Data Modelling: Events, Sessions, Late Arrivals Data modelling