Slowly Changing Dimension, Type 2, in SQL

The interviewer asks: "A customer moves to another city. How does dim_customer keep both cities?" The answer is a slowly changing dimension, Type 2 (SCD2): each version of a customer is its own row, with valid_from and valid_to, and the current version has valid_to NULL. A data engineer needs it because a report on February's orders must use the city each customer lived in in February; overwriting the city (Type 1) silently moves every old order to the new city.

A change takes two statements. An UPDATE closes the customer's current row (valid_to = the run date), then an INSERT adds the new version (valid_from = the run date, valid_to NULL). Warehouses often do both in one MERGE, and dbt snapshots write them for you. UPDATE … FROM joins a second table to the one you update, and its WHERE says which rows go together. On another table:

query = (
    "UPDATE products "
    "SET price = new_prices.price "
    "FROM new_prices "
    "WHERE products.product = new_prices.product "
)

The trap: closing every customer named in today's changes. Customer 3 is in them with the same city, Madrid, and customer 2 already has an old row that ended on 2026-03-10: pairing rows by id alone closes 3's row and rewrites 2's old end date. The starting code, with no WHERE at all, closes every row: 304 rows, only 3 of them current. Close only current rows (valid_to IS NULL) whose city really changed, then insert a version only for the customers left with no current row, with customer_id NOT IN (SELECT …).

The starting code builds dim_customer from customers.csv (300 customers, all valid from 2026-01-01), gives customer 2 an earlier move from Madrid to Rome on 2026-03-10, and creates changes: 1 → Porto, 2 → Cluj, 3 → Madrid. run_date is 2026-05-04. The check prints customers 1 to 3, then the row counts:

customer_id | city | valid_from | valid_to
1 | Rome | 2026-01-01 | 2026-05-04
1 | Porto | 2026-05-04 | NULL
2 | Madrid | 2026-01-01 | 2026-03-10
2 | Rome | 2026-03-10 | 2026-05-04
2 | Cluj | 2026-05-04 | NULL
3 | Madrid | 2026-01-01 | NULL
(6 rows)
all_rows | current_rows
303 | 300
(1 row)

Your task: finish the UPDATE's WHERE so it closes only the current rows whose city changed, and give the INSERT a WHERE so it adds a version only for customers who no longer have a current row.

Assignment
Follow the Coding Tutorial to finish the UPDATE that closes changed rows and the INSERT that adds their new versions.

Hint
Both tables have customer_id and city, so in the UPDATE write the table before each column: dim_customer.city. A row is current when valid_to IS NULL; the INSERT's subquery lists the customers who still have one.