dim_customer_scd2 keeps the history of each customer's city (type 2 slowly-changing dimension): customer_sk (surrogate key), customer_id, city, valid_from, valid_to, is_current. The current version of a customer has valid_to NULL and is_current true.
customer_updates (customer_id, city, changed_at) is today's batch. Apply it:
- •An update whose city differs from the customer's city just before it — the current dimension row, or the previous update in this batch — creates a new version:
valid_from = changed_at, valid_to NULL, is_current true. The version it replaces is closed: valid_to = changed_at, is_current false. - •An update that repeats the city it would replace creates nothing.
- •A customer not in the dimension gets a first version.
- •New versions are numbered after the largest existing
customer_sk, in order of changed_at then customer_id (largest + 1, + 2, …).
Return the whole dimension after the batch: existing rows (with any you closed) plus the new versions.
Columns: customer_sk, customer_id, city, valid_from, valid_to, is_current. Sort by customer_id, then valid_from.