Implement SCD Type 2 by closing the old row and inserting a new row for every address change, using effective dates to track history.

To implement SCD Type 2 you keep a history table that records every address change. The table has columns: customer_id, address, start_date, end_date, is_current. When a new address arrives you close the current row and insert a new one. Example in PostgreSQL:

-- Assume new address data in temp table new_address(customer_id, address, effective_date)

-- 1. Close the current row

UPDATE customer_history

SET end_date = na.effective_date,

is_current = false

FROM new_address na

WHERE customer_history.customer_id = na.customer_id

AND customer_history.is_current = true

AND na.effective_date > customer_history.start_date;

-- 2. Insert the new row

INSERT INTO customer_history (customer_id, address, start_date, end_date, is_current)

SELECT na.customer_id,

   na.address,
   na.effective_date,
   NULL,
   true

FROM new_address na;

If you prefer a single statement, use a MERGE (or UPSERT) supported by your RDBMS. The key points are: never update the address in place; each change creates a new row with a new start_date; the old row gets an end_date and is marked non‑current. This pattern preserves full history and allows queries like “what was the address on 2023‑06‑01?” by filtering on start_date <= date AND (end_date IS NULL OR end_date > date).