learning-it by example

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

1. In the video's type two table, what happens when a load brings a new region for a customer?
2. In the type two report, by region at the time of each sale, how much did the West sell?
3. After loading the last day a second time, how many rows did the type two table have?
4. According to the video, what date does valid from hold in this demo?
5. Why does the video call joining type two rows on the source id alone a mistake?

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

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

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

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

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

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos