← вся теориятеория к собесу · DWH и моделирование данных

SCD и историчность измерений

SCD: история измерений

Slowly Changing Dimensions отвечают на вопрос, что делать, когда атрибут измерения меняется - клиент переехал, товар сменил категорию. Тип SCD (slowly changing dimension) решает, увидим ли мы историю или перепишем прошлое. Собес любит SCD2 и джойн по интервалу.

Стержень: SCD2 хранит версии строк, и факт джойнится к версии, действовавшей на дату события, а не к текущей.

// Формулировки: «чем SCD1 отличается от SCD2?», «как джойнить факт к SCD2?», «что такое late arriving dimension?».

Типы SCD

SCD1 (slowly changing dimension type 1) - перезаписать значение: истории нет, вся статистика пересчитывается «как сейчас». Годится для опечаток и несущественных атрибутов.

SCD2 (slowly changing dimension type 2) - новая версия строки при изменении, с интервалом valid_from/valid_to (или флагом is_current). Факты джойнятся к версии, актуальной на момент события, и исторические отчёты остаются честными. Это стандарт DWH (data warehouse).

// SCD3 (slowly changing dimension type 3) - колонка «предыдущее значение»: один шаг истории для редких сравнений «до/после», полноценную историю не заменяет. Тип выбирают по атрибуту, а не по таблице: в одном измерении адрес может быть SCD2, а исправление имени - SCD1.

SCD1 / SCD2 / SCD3: перезапись / версии строк / колонка прошлого значения
valid_from / valid_to: интервал действия версии записи

Механика и point-in-time join

SCD2 механически: при изменении заводится новый суррогатный ключ на новую версию, старая строка закрывается датой (valid_to). Факт хранит ключ той версии, что действовала на дату события.

Поэтому джойнят факт к измерению по бизнес-ключу И интервалу это point-in-time join. Джойн по is_current=true вместо интервала пересчитает всю историю задним числом: прошлогодние продажи «переедут» в новый регион клиента.

// Осторожно с интервалами: дырки или нахлёсты версий ломают BETWEEN - он находит две версии или ни одной. Границы интервалов должны стыковаться встык и покрывать всю ось времени.

-- факт к версии, живой на дату события
JOIN dim_customer d
  ON f.customer_bk = d.customer_bk
 AND f.event_date
     BETWEEN d.valid_from AND d.valid_to

point-in-time join: джойн факта к версии, действовавшей на дату события
is_current: флаг актуальной версии для запросов «как сейчас»

Опоздавшие данные

Late arriving - данные пришли не по порядку. Факт пришёл раньше, чем измерение о нём узнало: подставляют заглушку-строку (inferred member) и обогащают её позже, когда измерение подтянется.

Изменение измерения пришло с опозданием: приходится пересобирать интервалы версий задним числом, аккуратно двигая valid_from/valid_to.

// Не продумать late arriving - значит либо ронять загрузку на отсутствующем ключе, либо молча терять факты. Это проектное решение, а не край, который «может, не случится».

inferred member: заглушка измерения для факта, пришедшего раньше
late arriving data: события/изменения, пришедшие не по порядку времени

Как отвечать: «Как устроен SCD2 и как джойнить к нему факт?»

SCD2 сохраняет историю измерения версиями строк. Когда атрибут меняется - клиент переехал в другой регион - я не перезаписываю строку, а закрываю старую версию датой valid_to и завожу новую с новым суррогатным ключом и своим valid_from. У каждой версии свой интервал действия, плюс обычно флаг is_current для быстрых запросов «как сейчас». Факт при загрузке ссылается на ключ той версии, что была актуальна на дату события. Соответственно джойню факт к измерению не по is_current, а по бизнес-ключу и попаданию даты события в интервал версии - point-in-time join через BETWEEN valid_from и valid_to. Если джойнить к текущей версии, вся история перепишется задним числом: старые продажи припишутся новому региону. И слежу, чтобы интервалы стыковались без дыр и нахлёстов, иначе BETWEEN вернёт ноль или две версии.

Почему это сильный ответ: механика версий, правильный джойн по интервалу с явным контрпримером (is_current переписывает историю) и предупреждение про стыковку интервалов - полное практическое понимание.

На чём валят

−SCD1 на атрибуте сегментации - прошлогодние продажи «переехали» в новый регион.
−Джойн всех фактов к is_current=true - исторические отчёты пересчитались задним числом.
−Дырки или нахлёсты интервалов версий: BETWEEN находит две версии или ни одной.
−SCD2 на атрибуте, меняющемся ежедневно - взрыв версий; подумай про мини-измерение.
−Не продумать late arriving - падение загрузки или потерянные факты.

Проверьте себя

Пять вопросов из банка по этой подтеме. Всего их 11, остальные разбираются в тренажёре.

#scd_history1 / 5
Чем SCD Type 2 отличается от Type 1?
A)SCD Type 2 перезаписывает значение на месте, а Type 1 хранит всю его историю строками
B)Type 1 затирает значение (нет истории); Type 2 добавляет строку с валидностью
C)Между SCD Type 1 и Type 2 нет разницы, это одно и то же
D)Type 2 применяется к факт-таблицам, а Type 1 — к измерениям
показать ответ и разбор
+B)Type 1 затирает значение (нет истории); Type 2 добавляет строку с валидностью
// разбор: Type 1 перезаписывает атрибут на месте — история теряется, остаётся только текущее значение. Type 2 при изменении добавляет новую строку-версию с полями valid_from/valid_to (или current-флагом) и новым суррогатным ключом, сохраняя всю историю. Выбор зависит от того, нужен ли исторически точный контекст фактов.
#scd_history2 / 5
Почему факт нельзя джойнить с SCD2-измерением просто по натуральному ключу?
A)Джойнить по натуральному ключу можно — это самый надёжный из способов
B)SCD2-измерение вообще не получится джойнить с факт-таблицей в этом случае из-за неизбежного расхождения нескольких версий строк измерения
C)Нужен суррогатный ключ версии, валидной на момент события, иначе не тот атрибут
D)Достаточно взять текущую (current) версию строки измерения
показать ответ и разбор
+C)Нужен суррогатный ключ версии, валидной на момент события, иначе не тот атрибут
// разбор: В SCD2 у одного натурального ключа много строк-версий, поэтому джойн по нему даёт фан-аут (задвоение фактов). Правильно — point-in-time join по суррогатному ключу той версии, что была валидна на дату события, либо заранее проставленный в факте суррогат. Иначе к факту подтянется исторически неверный атрибут.
#scd_history3 / 5
Чем SCD Type 3 отличается от Type 2 и когда он уместен?
A)Type 3 хранит лишь предыдущее значение в отдельной колонке — только одна ступень истории
B)Тип Type 3 хранит абсолютно всю полную историю изменений отдельными строками, точь-в-точь как Type 2, вообще без отличий
C)Type 3 полностью затирает старое значение, вообще не сохраняя никакой истории изменений
D)Type 3 применяется исключительно к факт-таблицам, но почти редко к измерениям
показать ответ и разбор
+A)Type 3 хранит лишь предыдущее значение в отдельной колонке — только одна ступень истории
// разбор: SCD Type 3 добавляет к атрибуту колонку «предыдущее значение» — хранится текущее и одно прошлое, но не вся история. Он уместен, когда бизнесу нужно сравнить «сейчас vs как было раньше» (например, прежний и текущий регион клиента), а полная историчность (Type 2, строка-версия на каждое изменение) избыточна.
#scd_history4 / 5
Чем SCD Type 4 отличается от Type 2?
A)Type 4 не хранит истории изменений, оставляя лишь текущее значение
B)Type 4 перезаписывает старое значение на месте без сохранения предыдущего, как это делает Type 1
C)Type 4 добавляет к строке ещё одну колонку с прошлым значением, ограничиваясь одной версией назад
D)Type 4 — текущее в dim + история отдельной таблицей
показать ответ и разбор
+D)Type 4 — текущее в dim + история отдельной таблицей
// разбор: SCD Type 2 хранит все версии строки в самом измерении (флаги current/даты). Type 4 разделяет: основное измерение содержит только актуальную версию (быстрые джойны для текущих отчётов), а полная история изменений живёт в отдельной history/mini-таблице. Плюс — компактное «горячее» измерение; минус — за историей идут в другую таблицу. Уместен, когда 99% запросов про «сейчас», а история нужна изредка.
#scd_history5 / 5
Что такое гибридный SCD Type 6 и что он совмещает?
A)Комбинацию 1+2+3: версии строк (Type 2) плюс колонка «текущее значение» в каждой версии для сквозной аналитики
B)Type 6 хранит ровно шесть последних версий каждой строки измерения, удаляя все более старые
C)Type 6 отказывается от версионирования и перезаписывает значения на месте
D)Type 6 применим к факт-таблицам и к измерениям отношения не имеет
показать ответ и разбор
+A)Комбинацию 1+2+3: версии строк (Type 2) плюс колонка «текущее значение» в каждой версии для сквозной аналитики
// разбор: Type 6 (1+2+3) держит полную историю версиями как Type 2, но в каждой исторической строке дополнительно есть колонка «current_value», которую при изменении обновляют во всех версиях (поведение Type 1). Это позволяет одним запросом смотреть факт и «как было тогда» (историческое значение версии), и «как есть сейчас» (current-колонка) — например, продажи по прежнему и по текущему региону менеджера. Цена — сложнее загрузка (обновление current во всех версиях).

дальше

Теорию прочитали. Навык ставится повторением

В Сеньорчике эта подтема идёт в ежедневных сессиях: движок возвращает её, пока ответы не станут уверенными, и ведёт прогресс отдельно по каждой подтеме. Теория внутри тоже бесплатна, лимит только на количество вопросов в день.

Открыть тренажёр Вся тема: DWH и моделирование данных