Моделирование: звезда, SCD, Data Vault

Модель данных в хранилище решает, насколько легко аналитику ответить на вопрос и насколько больно инженеру добавить новый источник. На собесе почти всегда просят нарисовать звезду и объяснить SCD 2.

Факты и измерения

Подход Ральфа Кимбалла (dimensional modeling):

• Факт: событие или измерение процесса с числовыми мерами: продажа (сумма, количество), визит, платёж. Таблица фактов длинная и узкая.
• Измерение: контекст факта: кто, что, где, когда. Клиент, товар, магазин, дата. Таблица измерения короткая и широкая, с описательными атрибутами.

Первый и главный шаг: гранулярность (grain) факта, то есть что означает одна строка. «Одна строка = одна позиция в чеке» или «одна строка = чек целиком». Смешивать нельзя: если в одной таблице и позиции, и итоги чека, суммы задвоятся.

Типы фактов:

• Транзакционные: одна строка на событие.
• Периодические снимки: состояние на конец периода (остаток на складе на конец дня).
• Накопительные снимки: одна строка на процесс с датами этапов (заказ: создан, оплачен, доставлен), строка обновляется.

Меры бывают аддитивные (сумма продаж складывается по всем измерениям), полуаддитивные (остаток можно складывать по складам, но не по дням) и неаддитивные (доли и средние, их пересчитывают из компонентов).

Звезда и снежинка

Звезда: факт в центре, вокруг денормализованные измерения, каждое соединено с фактом одним ключом. Простые запросы, мало JOIN, хорошо работает в колоночных базах.

Снежинка: измерения нормализованы (товар → категория → департамент отдельными таблицами). Меньше дублирования, но больше JOIN. В аналитике обычно предпочитают звезду.

Суррогатные ключи: в измерении свой целочисленный ключ (customer_sk), а ключ источника (customer_id) хранится атрибутом. Нужны, чтобы хранить несколько версий одной сущности (SCD 2) и не зависеть от ключей разных источников.

Conformed dimensions: общие измерения (дата, клиент), которые используют разные факты. Позволяют сравнивать продажи и возвраты в одном отчёте.

SCD: медленно меняющиеся измерения

Клиент переехал из Казани в Москву. Как отразить это в измерении?

• SCD 0: значение не меняется никогда (дата регистрации).
• SCD 1: перезаписать. История теряется: старые продажи «переедут» в Москву.
• SCD 2: закрыть старую версию и добавить новую. Колонки valid_from, valid_to (для текущей версии далёкая дата или NULL) и часто is_current. Факт ссылается на суррогатный ключ версии, действовавшей в момент события.
• SCD 3: колонка с предыдущим значением (prev_city). Хранит только один шаг истории.
• SCD 4: текущие значения в основной таблице, история в отдельной.
• SCD 6: комбинация 1, 2 и 3: версии как в SCD 2 плюс колонка текущего значения во всех версиях.

Типичная загрузка SCD 2 из снимка источника:

1. Найти изменившиеся записи: сравнить отслеживаемые атрибуты (или их хеш) с текущей версией.
2. Закрыть текущие версии изменившихся: valid_to = дата загрузки, is_current = false.
3. Вставить новые версии для изменившихся и новых записей.

Важно сделать загрузку идемпотентной: повторный запуск за тот же день не должен плодить версии.

Point-in-time JOIN: факт соединяют с версией измерения, действовавшей на дату факта: fact.date >= dim.valid_from AND fact.date < dim.valid_to. Удобнее всего проставлять суррогатный ключ версии в факт при загрузке.

Data Vault кратко

Data Vault (Дэн Линстедт) рассчитан на интеграцию многих источников и частые изменения:

• Hub: бизнес-ключ сущности (номер клиента) и служебные поля.
• Link: связь между хабами (клиент и заказ).
• Satellite: описательные атрибуты с историей, привязанные к хабу или линку.

Плюсы: новые источники добавляются новыми сателлитами без переделки, загрузка параллельная, полная история и аудит. Минусы: много таблиц и JOIN, для отчётов поверх строят звёзды-витрины. Часто Data Vault используют как core-слой, а Кимбалла как слой витрин.

Частые ошибки

• Не зафиксировать гранулярность факта и задвоить суммы.
• Ссылаться из факта на натуральный ключ при SCD 2: непонятно, какая версия.
• Сравнивать в SCD 2 все колонки, включая технические, и плодить версии без изменений.
• Усреднять неаддитивные меры (средний чек по дням вместо пересчёта из сумм и количеств).

Что спросят на собесе

• Факт и измерение, гранулярность, типы фактов.
• Звезда против снежинки, зачем суррогатные ключи.
• Типы SCD, как загрузить SCD 2 и как сделать point-in-time JOIN.
• Что такое Data Vault и когда он оправдан.