Звезда и снежинка: как моделировать аналитическое хранилище

Прямой ответ

Звезда — схема хранилища, в которой одна таблица фактов окружена денормализованными таблицами измерений. Снежинка — вариант, где измерения дополнительно нормализованы: атрибуты вынесены в подтаблицы (например, dim_product → dim_category → dim_department). Обе схемы решают одну задачу: сделать аналитические запросы простыми и быстрыми.

Ключевые понятия:

• Таблица фактов. Хранит измеримые события: суммы, количества, длительности. Обычно узкая по ширине, но огромная по числу строк; состоит из внешних ключей измерений и числовых мер.
• Таблица измерений. Описательные атрибуты: кто, что, где, когда. Относительно небольшие, широкие, сильно денормализованные.
• Суррогатный ключ. Целочисленный ключ, не имеющий бизнес-смысла. Нужен для версионности измерений (SCD Type 2), обработки неизвестных значений и независимости от форматов источника.
• Деградированный атрибут (degenerate dimension). Атрибут без собственного измерения, живущий прямо в фактах — например, номер счёта или идентификатор транзакции.
• Конформные измерения. Одно и то же измерение (дата, клиент, товар) используется несколькими фактами с одинаковым смыслом и ключами. Это то, что позволяет строить сквозную аналитику.

Звезда против снежинки на практике:

В современных колоночных хранилищах денормализация почти бесплатна по хранению (сжатие словарями), поэтому звезда выигрывает чаще. Нормализация оправдана, когда у измерения глубокая иерархия и она реально используется в запросах, либо когда атрибуты нормализованной части меняются независимо.

Практика: проектирование от запросов

Правильный порядок — от вопросов бизнеса к схеме, а не от структуры источника.

1. Выпишите вопросы. «Выручка по регионам за день», «Средний чек по категориям», «Конверсия по каналам привлечения».
2. Определите зерно факта. Одна строка = одна позиция заказа? Один платёж? Одно событие? Зерно — самое важное решение: его нельзя изменить позже без пересборки.
3. Найдите измерения. Для каждого вопроса: по чему группируем (регион, категория, дата, канал).
4. Определите меры. Суммируемые (total), полусуммируемые (остаток на счёте — нельзя складывать по времени), несуммируемые (процент — считается из суммируемых).

CREATE TABLE fact_order_items (
  order_item_sk BIGINT PRIMARY KEY,
  date_sk       INT  NOT NULL REFERENCES dim_date,
  customer_sk   BIGINT NOT NULL REFERENCES dim_customer,
  product_sk    BIGINT NOT NULL REFERENCES dim_product,
  store_sk      INT  NOT NULL REFERENCES dim_store,
  order_no      TEXT NOT NULL,          -- деградированный атрибут
  quantity      INT  NOT NULL,
  unit_price    NUMERIC(12,2) NOT NULL,
  total         NUMERIC(14,2) NOT NULL
);
CREATE INDEX fact_oi_date_cust ON fact_order_items (date_sk, customer_sk);

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

1. Держите измерения конформными. Один dim_date на всё хранилище, с атрибутами «рабочий день», «неделя», «квартал». Дубликаты календарей — частая причина расхождений между отчётами.
2. Обрабатывайте неизвестные значения явно. Строка -1 = unknown в измерении лучше, чем NULL в фактах: запросы не теряют строки при INNER JOIN.
3. Версионируйте меняющиеся атрибуты. Атрибуты, влияющие на группировку, храните как SCD Type 2 и связывайте факты с версией.
4. Не бойтесь широких таблиц. В колоночном хранилище запрос читает только нужные колонки, поэтому денормализация не замедляет чтение, но убирает join.
5. Описывайте модели как код. Слои и тесты удобнее держать в dbt, а не в ручных SQL-скриптах.

Практический сценарий: два отчёта дают разную выручку

Контекст: финансовый отчёт и маркетинговый дашборд показывают выручку за месяц с расхождением 7 %.

1. Диагностика. Сравниваем определения: в финансовом отчёте учитываются только заказы в статусе paid и shipped, в маркетинговом — все созданные заказы, включая отменённые.
2. Вторая причина. В маркетинговом дашборде регион берётся из текущего адреса клиента (Type 1), а в финансовом — из адреса на дату заказа. Часть заказов «переехала» между регионами.
3. Исправление. (а) Фиксируем общее определение меры в семантическом слое: «выручка = сумма позиций заказов в статусах paid, shipped»; (б) переводим измерение клиента на SCD Type 2 и используем срез на дату события; (в) оба отчёта переводим на один факт и одно измерение.
4. Проверка. Контрольное сравнение за три месяца: расхождение 0 %. Добавляем автоматическую сверку двух отчётов в набор проверок качества.
5. Профилактика. Вводим правило: каждое измерение имеет единственного владельца и единое определение, а новые отчёты строятся от конформных измерений, а не от своих копий.

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

FAQ

Когда снежинка лучше звезды? Когда у измерения большая нормализуемая часть, которая меняется независимо и используется в запросах: например, иерархия категорий товара с тысячами узлов, справочник территорий с уровнями. Также снежинка оправдана в строковых СУБД, где дублирование атрибутов заметно увеличивает объём чтения.

Нужны ли суррогатные ключи, если есть стабильный бизнес-ключ? Да, если планируется версионность (SCD Type 2) или возможна смена формата бизнес-ключа при миграции источника. Суррогатный ключ делает факты независимыми от этих изменений и удешевляет join за счёт компактного целочисленного типа.

Можно ли строить витрины без измерений, «широкой таблицей»? Да, и в колоночных системах это часто быстрее. Цена — дублирование атрибутов и риск расхождений при обновлении. Практичный компромисс: звезда как источник истины, широкие витрины как производный слой с проверяемой сверкой.

Что делать с фактами без событий, например остатками? Используют периодические снимки (periodic snapshot): строка на сущность за период с мерой-остатком. Такие меры полусуммируемые: их складывают по сущностям, но не по времени — в отчётах это нужно отражать явно.

Источники: Wikipedia (star schema, snowflake schema), dbt docs — ссылки в шапке.