Условие

Какие задачи решает версионирование SCD-2 в хранилище, и какие технические трудности появляются при его реализации? Какие колонки обычно добавляют?

Решение

Что решает

SCD-2 хранит полную историю изменений атрибутов измерения: каждый раз, когда атрибут (например, тариф клиента) меняется, в dim-таблице появляется новая строка вместо перезаписи.

Это даёт две возможности:

Восстановить состояние клиента на любой момент в прошлом.
Корректно соединять с фактами «как было на момент транзакции».

Какие колонки добавляют

ALTER TABLE dim_client ADD COLUMN start_date DATE;
ALTER TABLE dim_client ADD COLUMN end_date   DATE;     -- NULL или '9999-12-31'
ALTER TABLE dim_client ADD COLUMN is_current BOOLEAN;
ALTER TABLE dim_client ADD COLUMN version    INT;      -- опционально
ALTER TABLE dim_client ADD COLUMN client_sk  BIGSERIAL PRIMARY KEY;  -- суррогатный

Бизнес-ключ (client_id) уже не уникален — уникален суррогатный.

Соединение факта с историей

SELECT f.*, d.tariff
FROM   fact_payment f
JOIN   dim_client    d
       ON f.client_id   = d.client_id
      AND f.payment_date BETWEEN d.start_date AND d.end_date;

Трудности

Рост объёма — у активного клиента десятки версий. Нужны партиции по дате и индексы по (client_id, start_date).
Late arriving facts — факт пришёл с опозданием, состояние dim уже изменилось → нужен look-up по дате факта, а не «текущей» строке.
Late arriving dimensions — событие в факте, а в dim ещё нет соответствующей версии → создают плейсхолдер.
Атомарность апдейта — закрытие старой строки и вставка новой должны быть в одной транзакции.
Сравнение «изменился ли атрибут» — нужен hash от всех бизнес-атрибутов: если совпал, новую строку не создаём.

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

Фильтр is_current = TRUE в отчётах «текущего среза» — обязателен, иначе строки задвоятся.
BETWEEN start_date AND end_date — если хранить end_date = NULL для текущей, надо (NULL >=) обходить через COALESCE(end_date, '9999-12-31') или is_current = TRUE.
Одновременное обновление двух колонок — открывают одну новую версию, а не две.

Эталонный ответ

Решает: хранение полной истории изменений измерений, корректный join с фактами «as of». Колонки: start_date, end_date, is_current, суррогатный ключ. Трудности: рост объёма, late arriving, атомарность операций обновления.

SCD-2: какие колонки добавлять и какие сложности возникают