learning-it by example

Back to the video

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.

Video #112 ยท Data engineering and pipelines

Steps in the video

  1. 0:00 Load the same file twice: append says 12 rows and 126.70. The truth is 6 and 63.35.
  2. 0:31 The idea
  3. 1:08 Setup
  4. 1:34 The made-up files
  5. 1:53 Append and upsert
  6. 2:24 Replace and delete insert
  7. 3:02 The comparison
  8. 3:18 Run it twice
  9. 4:04 A load that dies half way
  10. 4:35 What this does not show
  11. 5:05 Mistakes to avoid
  12. 5:36 My take and next steps
  13. 5:57 Recap

Files

  • feed.py (17 lines)
  • run.py (60 lines)
  • strategies.py (43 lines)

Download the code (zip)

Tested with

Python 3.12.15

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.

Sources

Learning IT by Example.