#110 Data modelling 6.9 min
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.
Change one sale amount
Copy the code folder first. In source.py change the amount of the sale on 2026-05-12 to 50. Predict which regions change on each of the three lines of python run.py report, and what the totals do. Run it and compare.
A hint
Look at which customer made that sale and which region that customer is in on that day.
A sale on the change day
In source.py change the date of the sale on 2026-02-20 to 2026-03-01, the day Ana moves to the South. Predict which region the type 2 report puts it in, and whether the type 1 report changes. Run python run.py report to check.
A hint
Read the AS_OF query in scd.py and see whether the end date comparison is strict.
Ben moves region
Add a fourth tuple to SNAPSHOTS in source.py with the load day 2026-06-01 and the same customers as the last snapshot, but with Ben in the West. Predict how many type 2 rows there will be and which old row gets closed. Predict what happens to the South line in the type 1 report. Run python run.py type2 and python run.py report.
A hint
Closing a version sets valid_to to the load day and is_current to 0. Type 1 only keeps the latest region for each customer.
Remove the no-change check
In load_type2 in scd.py comment out the two lines that skip a customer whose name and region are unchanged. Predict how many type 2 rows there are after three loads and after loading the last day again. Run python run.py rerun and compare. Then put the lines back.
A hint
Without the check, every customer in every load gets a new version, even when nothing changed.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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.
