1. you need to close out modified or deleted records
2. add new rows (inserted at the source)

-- Step 1: Close out records that changed (updates and deletes)

MERGE INTO west_division.retail_data.customers_type2 AS target
USING (
  SELECT DISTINCT customer_id, _commit_timestamp
  FROM table_changes('east_division_shared.retail.customers', 2, 5)
  WHERE _change_type IN ('update_postimage', 'delete')
  ORDER BY _commit_timestamp
) AS source
ON target.customer_id = source.customer_id AND target.is_current = true
WHEN MATCHED THEN
  UPDATE SET
    end_date = source._commit_timestamp,
    is_current = false;

-- Step 2: Insert new versions (inserts and updates)
INSERT INTO west_division.retail_data.customers_type2
SELECT
  customer_id, customer_name, email, country, signup_date, customer_segment,
  _commit_timestamp as start_date,
  NULL as end_date,
  true as is_current
FROM table_changes('east_division_shared.retail.customers', 2, 5)
WHERE _change_type IN ('insert', 'update_postimage')
ORDER BY _commit_timestamp;