SCD: типы медленно меняющихся измерений и их реализация

Прямой ответ

Slowly changing dimension (SCD) — способ хранить атрибуты сущностей, которые меняются со временем: адрес клиента, тариф, категория товара, менеджер по счёту. Проблема в том, что исторические факты (заказы, платежи) должны сохранять те значения, которые действовали на момент события, а не текущие.

Типы SCD:

• Type 0 — неизменяемые. Значение фиксируется при создании и не меняется. Пример: дата рождения, дата регистрации.
• Type 1 — перезапись. Новое значение заменяет старое, история теряется. Просто и дёшево; подходит, когда история не нужна (исправление опечатки в названии).
• Type 2 — история версий. Каждая версия хранится отдельной строкой с суррогатным ключом и границами действия (valid_from, valid_to, флаг актуальности). Даёт полную историю; требует аккуратности в joins.
• Type 3 — ограниченная история. Добавляются колонки «текущее» и «предыдущее» значение. Глубина истории фиксирована (обычно одно изменение).
• Type 4 — история в отдельной таблице. Текущие значения в основной таблице, изменения — в таблице аудита. Удобно, когда история нужна редко.

Как выбирать:

Ключевое следствие для модели: в схеме «звезда» факты соединяются с измерением по суррогатному ключу версии, а не по бизнес-ключу, — иначе история теряется. Подробнее о построении модели — в статье про звезду и снежинку.

Практика: реализация Type 2

Схема таблицы:

CREATE TABLE dim_customer (
  customer_sk   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id   BIGINT NOT NULL,      -- бизнес-ключ
  full_name     TEXT,
  city          TEXT,
  tariff        TEXT,
  valid_from    DATE NOT NULL,
  valid_to      DATE NOT NULL DEFAULT '9999-12-31',
  is_current    BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE INDEX dim_customer_cur_idx ON dim_customer (customer_id) WHERE is_current;

Применение изменений (PostgreSQL 15+, MERGE):

MERGE INTO dim_customer AS d
USING staging_customer AS s
   ON d.customer_id = s.customer_id AND d.is_current
WHEN MATCHED AND (d.city, d.tariff) IS DISTINCT FROM (s.city, s.tariff) THEN UPDATE
  SET valid_to = CURRENT_DATE - 1, is_current = FALSE
WHEN NOT MATCHED THEN INSERT (customer_id, full_name, city, tariff, valid_from)
  VALUES (s.customer_id, s.full_name, s.city, s.tariff, CURRENT_DATE);

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

dbt snapshot реализует ту же логику декларативно: указываете стратегию (timestamp или check), колонки для сравнения и получаете таблицу с dbt_valid_from/dbt_valid_to — см. dbt-трансформации.

Запрос «срез на дату»:

SELECT o.order_id, o.total, c.city, c.tariff
FROM fact_orders o
JOIN dim_customer c
  ON c.customer_sk = o.customer_sk;   -- суррогатный ключ версии

Рекомендации:

1. Не смешивайте Type 1 и Type 2 в одной таблице без флага. Иначе неясно, какие атрибуты историчны, а какие перезаписываются.
2. Следите за «поздними фактами». Событие за прошлый месяц может прийти сегодня; если измерение уже обновилось, факт попадёт в неверную версию. Такие случаи требуют явной политики.
3. Индексируйте актуальную версию частично. Частичный индекс WHERE is_current ускоряет самый распространённый запрос и не раздувается историей.
4. Документируйте происхождение версий. Кто и когда изменил атрибут — полезно при разборе расхождений; см. data lineage.

Практический сценарий: выручка по регионам «переехала» задним числом

Контекст: отчёт по регионам за прошлый квартал; после обновления адресов клиентов цифры за прошлые месяцы изменились.

1. Симптом. Сравнение с прошлой выгрузкой показывает расхождение в 4 % по региону, хотя новых заказов не было.
2. Причина. Измерение клиента построено как Type 1: адрес перезаписан, и исторические заказы «переехали» в новый регион.
3. Решение. Переводим измерение на Type 2: храним версии адресов, факты связываем с версией, действовавшей на дату заказа. Пересчитываем исторические факты, сопоставляя order_date с интервалом valid_from/valid_to.
4. Проверка. Контрольный запрос: сумма по регионам за квартал совпадает с зафиксированной ранее выгрузкой; новые заказы попадают в текущую версию.
5. Закрепление. Вносим правило в стандарт слоя данных: атрибуты, влияющие на группировку отчётов, хранятся как Type 2; перезапись допускается только для атрибутов, не участвующих в аналитике.

Результат: история стабилизирована, повторные пересчёты не меняют прошлое. Вывод: тип SCD — это не техническая деталь, а договорённость с бизнесом о том, что означает «правильная цифра за прошлый период».

Как выбрать между Type 2 и Type 4. Если история участвует в аналитике — нужны срезы на дату, пересчёт показателей за прошлые периоды — выбирайте Type 2: версии лежат рядом с данными и соединяются одним условием. Если история нужна только для аудита и расследований, дешевле Type 4: основная таблица остаётся компактной и быстрой, а журнал изменений не влияет на скорость отчётов. Смешивать подходы в одной таблице можно, но тогда для каждого атрибута должно быть явно записано, к какому типу он относится.

Проверка после пересчёта истории. После изменения типа SCD обязательно сверяйте контрольные суммы: суммы мер по периодам до и после пересборки должны совпадать, а число актуальных версий на бизнес-ключ — равняться единице. Без такой сверки легко «починить» историю так, что изменятся прошлые отчёты.

FAQ

Что делать, если атрибутов много, а меняется один? Для Type 2 вся строка версии дублируется, поэтому широкие таблицы растут быстро. Варианты: хранить историю только по действительно меняющимся атрибутам (вынеся стабильные в Type 1-часть) или использовать вертикальную схему «атрибут — значение — период».

Как обрабатывать удаления сущностей? Удаление источника обычно отражают как закрытие текущей версии (valid_to = дата удаления, is_current = FALSE), а не как физическое удаление строк: исторические факты должны оставаться объяснимыми.

Нужен ли SCD, если есть CDC? CDC даёт поток изменений — сырьё для истории. SCD — способ представить эту историю в аналитической модели. Обычно CDC заполняет слой raw, а SCD-логика реализуется при построении измерения.

Как проверить корректность SCD-таблицы? Три проверки: для каждого бизнес-ключа ровно одна строка с is_current = TRUE; интервалы версий не пересекаются; нет разрывов между valid_to предыдущей и valid_from следующей версии. Такие проверки добавляют в набор тестов качества данных.

Источники: Wikipedia (SCD), dbt docs (snapshots), PostgreSQL docs (MERGE) — ссылки в шапке.