Напиши SQL, реализующий SCD Type 2 для измерения клиента при изменении его атрибута (например, города)?

Короткий ответ

Для изменившегося клиента установите у текущей версии valid_to и is_current = false, затем вставьте новую открытую версию с новым значением.

Как это работает подробнее

Периоды действия обычно задают как полуинтервалы: старая версия действует до момента изменения, а новая — начиная с него. Это исключает пересечение границ при временном соединении с фактами.

• Сравнение. Проверяйте только текущую строку и отслеживаемые бизнес-атрибуты.
• Атомарность. Закрытие и вставка должны завершиться одной транзакцией.

Пример кода

scd_type_2.sqlКопировать

BEGIN;
UPDATE dim_customer
SET valid_to = CURRENT_TIMESTAMP, is_current = false
WHERE customer_id = 'C-1029'
  AND is_current = true
  AND city IS DISTINCT FROM 'Moscow';
INSERT INTO dim_customer (customer_id, city, valid_from, valid_to, is_current)
SELECT 'C-1029', 'Moscow', CURRENT_TIMESTAMP, NULL, true
WHERE NOT EXISTS (
  SELECT 1 FROM dim_customer
  WHERE customer_id = 'C-1029' AND is_current = true AND city = 'Moscow'
);
COMMIT;

Что использовать на практике

• Добавьте ограничение или тест: не более одной текущей версии на natural key.
• Проверяйте отсутствие пересечений периодов и используйте один timestamp изменения для обоих шагов.

Подводные камни

• Граница периода. Несогласованные valid_to и valid_from создают разрыв или пересечение.
• SCD-churn. Техническое поле, меняющееся каждый запуск, не должно создавать новую бизнес-версию.