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
- 0:00 Load the same file twice: append says 12 rows and 126.70. The truth is 6 and 63.35.
- 0:31 The idea
- 1:08 Setup
- 1:34 The made-up files
- 1:53 Append and upsert
- 2:24 Replace and delete insert
- 3:02 The comparison
- 3:18 Run it twice
- 4:04 A load that dies half way
- 4:35 What this does not show
- 5:05 Mistakes to avoid
- 5:36 My take and next steps
- 5:57 Recap
Files
- feed.py (17 lines)
- run.py (60 lines)
- strategies.py (43 lines)
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
- https://docs.databricks.com/aws/en/delta/selective-overwrite
- https://docs.getdbt.com/docs/build/incremental-models
- https://docs.getdbt.com/docs/build/incremental-strategy
- https://docs.python.org/3.12/library/sqlite3.html
- https://en.wikipedia.org/wiki/Idempotence
- https://www.kimballgroup.com/2007/10/subsystems-of-etl-revisited/
- https://www.sqlite.org/lang_conflict.html
- https://www.sqlite.org/lang_upsert.html
Learning IT by Example.