learning-it by example

#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.

1. In a Data Vault, what do hubs, links and satellites each store?
2. In the tiny Data Vault example, the same day-two file was loaded a second time under the same load date. How many rows did that repeat load add?
3. In a tiny SQLite Data Vault, Ben moved from York to Leeds between two daily extracts. What did the customer satellite hold for Ben afterwards?
4. In the tiny SQLite Data Vault example, how does the satellite loading code avoid adding a new row on every load?
5. How does the tiny Data Vault example produce a star schema for reporting?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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

All Data modelling videos

Related videos