Схема «звезда» и «снежинка»: моделирование данных для аналитики
Основы4 минРедакция Innovialabs
Коротко
В обеих схемах события с числами лежат в центральной таблице фактов, окружённой измерениями; «звезда» хранит каждое измерение одной широкой таблицей ради простых и быстрых запросов, «снежинка» дробит измерения на нормализованные подтаблицы, а BI-инструменты лучше всего работают со «звездой».
Коротко
В схеме «звезда» есть одна центральная таблица фактов (что произошло и в каких числах), напрямую связанная с плоскими таблицами измерений (кто, что, где, когда). «Снежинка» — та же идея, но измерения нормализованы в несколько связанных таблиц, например товар → категория → отдел.
Для аналитики и BI «звезда» — разумный выбор по умолчанию: меньше соединений, проще запросы, понятнее бизнес-пользователям. «Снежинка» — точечное решение для очень больших или общих измерений, а не цель сама по себе.
Кирпичики на примере розницы
Представим сеть магазинов. Вопросы: сколько продали, чего, где, когда и кому?
Таблица фактов
fact_sales хранит события и меры — числа, которые суммируют или усредняют:
| Колонка | Смысл |
|---|---|
| date_key | ссылка на dim_date |
| store_key | ссылка на dim_store |
| product_key | ссылка на dim_product |
| customer_key | ссылка на dim_customer |
| quantity | продано единиц |
| net_amount | выручка после скидок |
Таблицы измерений
Измерения хранят описательные атрибуты, по которым фильтруют и группируют: dim_product (название, бренд, категория, отдел), dim_store (город, регион, формат), dim_date (день, неделя, месяц, квартал, признак праздника), dim_customer (сегмент, уровень лояльности).
Зерно
Зерно — это то, что означает одна строка фактов. Здесь: «одна товарная позиция в одном чеке». Определите зерно первым и зафиксируйте его. Смешение зёрен — например, часть строк по позициям чека, часть по дням — даёт двойной счёт, который трудно заметить.
Суррогатные ключи
В измерениях используют суррогатные ключи: простые целые числа, сгенерированные в хранилище (product_key = 1042), а не коды из исходной системы. Они защищают модель от изменений в источниках, позволяют объединять несколько источников и делают возможным хранение истории.
Медленно меняющиеся измерения (SCD)
Атрибуты меняются: товар переезжает в другую категорию, магазин — в другой регион. Основные варианты:
- Тип 1 — перезаписать старое значение. Просто, но история переписывается: прошлые продажи теперь видны в новой категории.
- Тип 2 — добавить новую строку с новым суррогатным ключом и датами действия (
valid_from,valid_to,is_current). Старые продажи остаются привязаны к старой версии. Самый частый выбор, когда история важна. - Тип 3 — хранить колонку «предыдущее значение». Полезно только для сравнения на один шаг назад.
«Звезда» против «снежинки»
В «звезде» dim_product содержит бренд, категорию и отдел как обычные колонки. В «снежинке» dim_product ссылается на dim_category, а та — на dim_department.
| Критерий | «Звезда» | «Снежинка» |
|---|---|---|
| Соединений в запросе | Меньше | Больше |
| Простота запросов | Высокая, понятно аналитикам | Ниже, больше путей связей |
| Скорость запросов | Обычно выше | Может быть ниже из-за лишних JOIN |
| Объём хранения | Повторы текста в измерениях | Меньше дублирования |
| Поддержка общих иерархий | Обновлять много строк | Обновить одну строку |
| Удобство для BI | Естественно подходит | Работает, но часто потом «расплющивают» |
Экономия места от «снежинки» обычно невелика: измерения крошечные по сравнению с таблицами фактов, а колоночные хранилища хорошо сжимают повторяющиеся значения.
Что предпочитают BI-инструменты
Power BI, Tableau, Looker и похожие инструменты построены вокруг «звезды»: одна таблица фактов, фильтры идут от измерений к фактам. Документация Power BI прямо рекомендует для моделей схему «звезда». Со «снежинкой» инструменты тоже работают, но цепочки связей удлиняются, фильтрация ведёт себя менее предсказуемо, а аналитики путаются в множестве мелких таблиц. Часто в хранилище держат «снежинку», а для BI отдают «расплющенное» представление-«звезду».
Как выбрать
- Начинайте со «звезды». Её хватает для большинства отчётов по продажам, финансам и маркетингу.
- Выносите измерение в «снежинку», если оно очень большое, используется многими таблицами фактов с общей подиерархией или часто обновляется в одном месте.
- Документируйте зерно и правила SCD рядом с моделью.
- Сделайте полноценную
dim_dateвместо вычисления месяцев и кварталов в каждом запросе.
Частые ошибки
- Текстовые атрибуты в таблице фактов «для удобства».
- Ключи из источника вместо суррогатных — и сломанная история, когда источник переиспользует ID.
- Тип 1 везде, из-за чего прошлогодние отчёты меняются при каждом переименовании категории.
- Нормализация измерений по привычке из проектирования транзакционных баз.