Changing Dimensions: Keep History (Type 2) in Python

A slowly changing dimension is a dimension whose rows change now and then, like a customer who moves to another city. Overwriting the old value (Type 1) is simple, but every old order moves with it and last year's report changes. A Type 2 dimension keeps history instead: the old row stays, and the new version becomes a new row. Data engineers need this because a report about March must give the same numbers next year, whatever the customers did since.

Two dates say when each row was true: valid_from, its first day, and valid_to, the first day it is no longer true (NULL while it still is). Each version gets its own surrogate key, while the source's id stays the same for all of them. For example, if the Lamp moved from the category home to gifts on 2026-06-01, dim_product would keep two rows for it:

product_key | product | category | valid_from | valid_to
2           | Lamp    | home     | 2026-01-01 | 2026-06-01
9           | Lamp    | gifts    | 2026-06-01 | NULL

Now a fact row needs its date to find the right version: the date must be on or after valid_from, and before valid_to unless valid_to is NULL. A Lamp sold on 2026-03-10 meets key 2 (home); one sold on 2026-08-01 meets key 9 (gifts). The NULL needs its own test, because a comparison with NULL is never true.

The starting code builds dim_customer (customer_key, customer_id, city, valid_from, valid_to: every customer valid from 2026-01-01, with no end) and fact_order_lines (the paid lines). Customer 160 moves from Cluj to Rome on 2026-07-01. UPDATE … SET … WHERE … changes a column in the rows the WHERE picks; INSERT INTO … VALUES (…) adds one row, its values in the table's column order. query lists customer 160's orders once each (SELECT DISTINCT, since an order can have several lines) with the version of the customer each one meets. The check prints customer 160's rows of dim_customer, then query:

customer_key | customer_id | city | valid_from | valid_to
160 | 160 | Cluj | 2026-01-01 | 2026-07-01
301 | 160 | Rome | 2026-07-01 | NULL
(2 rows)
order_id | date | customer_key | city
1462 | 2026-02-21 | 160 | Cluj
1464 | 2026-03-04 | 160 | Cluj
1463 | 2026-09-28 | 301 | Rome
1465 | 2026-10-04 | 301 | Rome
(4 rows)

Your task: close customer 160's Cluj row on 2026-07-01, insert the Rome row with key 301, and add the two date conditions to the join in query, so every order meets exactly one version of its customer.

Assignment
Follow the Coding Tutorial to close the old row, insert the new one, and join each order line to the version of its customer that was true on its date.

Hint
valid_to is the first day a row is no longer true, so compare the date with it using <, and with valid_from using >=. Keep c.valid_to IS NULL OR … in brackets so the OR doesn't swallow the other conditions.