Slowly Changing Dimensions in SQL: Type 1 vs Type 2
The same seven sales put 105 pounds in the West with type 1, and 40 with type 2.
Video #110 ยท Data modelling
Steps in the video
- 0:00 Same sales, two histories: West sold 105, or 40
- 0:29 The idea
- 0:59 Setup
- 1:20 What the source sends
- 1:49 Two customer tables
- 2:19 Type one: overwrite
- 2:42 Type two: close the old row, add a new one
- 3:14 Facts find the right version
- 3:39 Run type one
- 5:11 What this does not show
- 5:35 Mistakes to avoid
- 6:10 My take and next steps
- 6:31 Recap
Files
- run.py (58 lines)
- scd.py (63 lines)
- source.py (14 lines)
Tested with
Python 3.12.15
What this does not show
Three made-up customers and seven sales in SQLite; type 1 and type 2 only, with no late-arriving changes and no speed measured.
Sources
- https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/dimension-surrogate-key/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-0/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-1/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-3/
- https://www.sqlite.org/lang_upsert.html
Learning IT by Example.