Data Vault 2.0: Hubs, Links and Satellites in SQL
Three loads into a tiny Data Vault in SQLite, and the third, a repeat, added 0 rows.
Video #262 ยท Data modelling
Steps in the video
- 0:00 Three loads into a tiny Data Vault. The third one added 0 rows.
- 0:34 The idea
- 1:09 Setup
- 1:30 Five tables
- 2:00 Hash keys, hubs and links
- 2:35 Satellites keep history
- 3:06 One load
- 3:21 A star made of views
- 3:38 Run the loads
- 4:47 What this does not show
- 5:26 Mistakes to avoid
- 5:52 My take and next steps
- 6:12 Recap
Files
- run.py (55 lines)
- source.py (11 lines)
- vault.py (78 lines)
Tested with
Python 3.12.15
What this does not show
Five made-up orders in SQLite; no speed measured, and point-in-time tables, bridges, deletes and late data are not covered; the repeat load reuses the same load date; one load per date (a second change on the same load date is ignored)
Sources
- https://docs.varigence.com/bimlflex/delivering-solutions/delivering-data-vault/data-vault-concept-hub
- https://docs.varigence.com/bimlflex/delivering-solutions/delivering-data-vault/data-vault-concept-integration-keys
- https://docs.varigence.com/bimlflex/delivering-solutions/delivering-data-vault/data-vault-concept-link
- https://docs.varigence.com/bimlflex/delivering-solutions/delivering-data-vault/data-vault-concept-satellite
- https://en.wikipedia.org/wiki/Data_vault_modeling
- https://techcommunity.microsoft.com/t5/analytics-on-azure-blog/data-vault-2-0-modelling-on-synapse-data-warehouse/ba-p/4003349
- https://www.sqlite.org/foreignkeys.html
Learning IT by Example.