#262 Data modelling 6.6 min
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.
Predict the three runs
Copy the code folder first. Predict how many rows the customer satellite has after each of the three loads, then run python run.py load. Run python run.py history and python run.py star too, and find which output shows that Ben kept both cities.
A hint
Asha and Cara are unchanged on day two, so they add no satellite rows.
Ben stays in York
In source.py, change Ben's city on 2026-03-02 from Leeds to York. Predict the satellite counts for the customer table, then run python run.py load and python run.py star. When I ran it, sat_customer ended at 4 instead of 5, and sales by current city read Leeds 38, York 27, Hull 20. Change Ben's city back to Leeds afterwards, because the next task expects it.
A hint
A satellite row is added only when the hash diff of name and city differs from the latest one.
Add a third day
In source.py, add a third batch to the end of the BATCHES list: ("2026-03-03", [("C3", "Cara", "York")], [("O6", "C3", 10)]). Predict what dim_customer shows for Cara and how sales by current city change, then run python run.py star. When I ran it, fact_order had 6 rows with a total of 95, and the sales were Leeds 50, York 45.
A hint
The dimension view keeps only the latest satellite row of each customer.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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)
Up next in Data modelling
More in Data modelling
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK
