Какие типы SCD существуют и как формируется историчность в таблицах
Что проверяют
Умеете ли вы хранить изменения измерений так, чтобы отчёт за прошлый квартал считался по тогдашним атрибутам. Типы перечислят многие, поэтому следом спрашивают, как вы загружаете SCD2 и как джойните к нему факты.
Как отвечать
Основные типы:
- Type 0: атрибут фиксируется при первой загрузке и больше не меняется, например дата регистрации;
- Type 1: перезапись, история теряется. Подходит для исправления опечаток;
- Type 2: на каждое изменение новая строка с суррогатным ключом,
valid_from,valid_toи флагомis_current. Даёт полную историю; - Type 3: колонка с предыдущим значением, хранится только одно прошлое состояние;
- Type 4: в распространённой трактовке текущие значения в основной таблице, история в отдельной;
- Type 6: гибрид 1+2+3, строки версионируются, а текущее значение продублировано во все версии.
Историчность в SCD2 строится так. Изменение ловится сравнением хэша отслеживаемых атрибутов с текущей версией (hashdiff), иначе каждая загрузка плодит пустые версии. Затем старая версия закрывается, valid_to получает момент изменения, и вставляется новая с открытым концом. Интервал удобно держать полуоткрытым, а текущей строке ставить 9999-12-31 вместо NULL, тогда условие dt >= valid_from AND dt < valid_to работает без COALESCE и без дыр на границах. Всё это делается одним MERGE в транзакции, в Spark на Delta Lake или Iceberg так же. В dbt готовый механизм это snapshots со стратегиями timestamp и check.
Факты цепляются либо по суррогатному ключу версии, проставленному при загрузке, либо point-in-time джойном по дате события внутри интервала.
Где спотыкаются
Вставляют новую версию раньше, чем закрывают старую, и UPDATE ... WHERE is_current закрывает обе строки, включая только что вставленную. Сначала закрытие, либо условие по ключу версии.
Путают Type 0 с перезаписью. Перезапись это Type 1, а Type 0 изменения просто игнорирует.
Забывают про запаздывающие данные: изменение, пришедшее задним числом, требует разрезать уже закрытый интервал, а наивная загрузка кладёт его как самую свежую версию.