Snowflaking и outrigger dimensions — когда оправдано

В star schema каждая dimension — это одна плоская денормализованная таблица. У dim_product в одной строке лежит всё: название товара, бренд, категория, отдел, поставщик. Категория повторяется во всех строках товаров этой категории, бренд — во всех строках бренда. Это сознательная избыточность ради скорости: чтобы получить «выручку по категориям», достаточно одного JOIN fct_sales с dim_product.

Snowflaking — это нормализация dimension: вынос повторяющихся атрибутов в отдельные связанные таблицы. dim_product ссылается на dim_category, та — на dim_department. Star schema превращается в snowflake schema (схему-снежинку): лучи звезды ветвятся. Kimball в общем случае snowflaking не рекомендует — и важно понимать, почему, и в каких редких случаях исключение оправдано.

Как выглядит snowflaking

Возьмём денормализованную dim_product из star schema:

dim_product (star schema, денормализованная)
 product_key | product_name      | category   | department  | brand
-------------+-------------------+------------+-------------+--------
 4071        | Кофе молотый 250г | Кофе       | Бакалея     | Lavazza
 4072        | Кофе зерно 1кг    | Кофе       | Бакалея     | Lavazza
 4073        | Чай чёрный 100п   | Чай        | Бакалея     | Greenfield

category и department повторяются. Snowflaking выносит их в отдельные таблицы:

dim_product (snowflaked)          dim_category              dim_department
 product_key | name | category_key  category_key | name | dept_key   dept_key | name
-------------+------+-------------  -------------+------+---------   ---------+--------
 4071        | ...  | 10            10 | Кофе  | 1                  1 | Бакалея
 4072        | ...  | 10            11 | Чай   | 1
 4073        | ...  | 11

Теперь категория хранится один раз в dim_category, отдел — один раз в dim_department. dim_product ссылается на dim_category через category_key, dim_category — на dim_department через dept_key.

Заметьте, что snowflaking — это применение к dimension того самого процесса нормализации, который вы изучали раньше для OLTP-схем. Повторяющееся значение category вынесено в отдельную таблицу, и теперь dim_product хранит вместо текста «Кофе» компактную ссылку category_key = 10. Формально снежинка «чище» с точки зрения теории нормализации: устранена избыточность, transitive-зависимость product -> category -> department разнесена по таблицам. Вопрос не в том, можно ли так сделать — можно. Вопрос в том, нужно ли это в аналитическом warehouse, и ответ Kimball — почти всегда нет. Дальше разберём, почему теоретически «более правильная» схема на практике проигрывает плоской.

Почему Kimball против snowflaking

У snowflaking есть очевидный плюс: экономия места. Категория «Кофе» хранится в dim_category одной строкой, а не повторяется в тысяче строк dim_product. Звучит хорошо — но в аналитическом warehouse этот плюс почти ничего не стоит, а минусы реальны.

Минус 1 — больше JOIN. Чтобы получить «выручку по отделам», в star schema нужен один JOIN (fct_sales + dim_product). В snowflake — три (fct_sales + dim_product + dim_category + dim_department). Каждый JOIN — это работа оптимизатора и обращение к ещё одной таблице. Аналитические запросы становятся длиннее и медленнее.

Минус 2 — экономия места мнимая. Dimension-таблицы малы по сравнению с fact-таблицами. dim_product — это, скажем, 100 тысяч строк; fct_sales — миллиарды. Сэкономить несколько мегабайт на dimension, когда fact-таблица занимает терабайты, — это оптимизация не там. Колоночные warehouse вдобавок отлично сжимают повторяющиеся значения (dictionary encoding): тысяча повторов слова «Кофе» в колонке хранится почти даром.

Минус 3 — сложнее для аналитика. Star schema интуитивна: fact в центре, dimensions вокруг, один уровень. Snowflake заставляет аналитика помнить цепочку таблиц и порядок их соединения. BI-инструменты тоже хуже работают со snowflake — многие из них рассчитаны именно на звезду.

Вокруг минуса 2 стоит задержаться, потому что именно он чаще всего понимается неверно. Интуиция «убрать дублирование = сэкономить» верна для OLTP, но в колоночном аналитическом warehouse она почти не работает. Колоночные движки хранят каждый столбец отдельно и сжимают его. Для столбца category, где слово «Кофе» повторяется тысячу раз подряд, применяется dictionary encoding: движок заводит словарь уникальных значений (Кофе -> 0, Чай -> 1) и хранит столбец как последовательность крошечных кодов, а не как тысячу копий строки. Повтор значения, который в строковом хранилище стоил бы места, в колоночном сжимается почти до нуля. То есть та избыточность, которую snowflaking устраняет ценой лишних JOIN, в современном warehouse и так устраняется — бесплатно, на уровне физического хранения. Snowflaking платит JOIN-ами за экономию, которой и без него уже добился движок.

Чтобы соотнести это с тем, что вы знаете о нормализации: нормализация защищает от аномалий обновления, потому что в OLTP данные меняются хаотично и многими пользователями одновременно. Dimension в warehouse меняется иначе — её перестраивает один контролируемый ETL-процесс по расписанию. Аномалии обновления, от которых спасает нормализация, в этом сценарии просто не возникают. Поэтому главный аргумент за нормализацию здесь не действует, а минусы (лишние JOIN) остаются. Это и есть причина, по которой денормализованная dimension — правильный дефолт аналитического моделирования.

Когда snowflaking всё-таки оправдан

Kimball не запрещает snowflaking абсолютно — он говорит «избегайте в общем случае». Есть ситуации, где нормализация фрагмента dimension оправдана.

Случай 1 — очень большой низкокардинальный текстовый атрибут. Если у dimension есть атрибут с длинным текстом (например, развёрнутое описание категории на абзац) и при этом мало различных значений, повтор этого длинного текста в каждой строке dimension реально стоит места. Вынос его в маленькую справочную таблицу оправдан.

Случай 2 — атрибут-группа, разделяемая несколькими dimensions. Если одна и та же иерархия (например, географическая: страна-регион-город) нужна сразу нескольким dimensions, имеет смысл сделать её отдельной нормализованной структурой и переиспользовать, а не дублировать в каждой dimension.

Случай 3 — атрибут со своими атрибутами. Если у «бренда» есть собственные осмысленные свойства (страна бренда, год основания, владелец), и эти свойства сами по себе предмет анализа, бренд заслуживает отдельной таблицы.

Outrigger dimension — нормализация под контролем

Когда нормализуют не всю dimension, а выносят один конкретный фрагмент в отдельную таблицу, на которую dimension ссылается, этот вынесенный фрагмент называют outrigger dimension. Outrigger — это dimension, на которую ссылается другая dimension (а не fact-таблица напрямую).

Самый канонический пример — date как outrigger внутри другой dimension. Допустим, в dim_customer есть атрибут «дата первой покупки». Можно хранить его просто как date. Но если по этой дате хочется тех же разрезов, что и по обычной date-dimension (год первой покупки, квартал, был ли это выходной), удобно сослаться на dim_date:

CREATE TABLE dim_customer (
    customer_key        INT PRIMARY KEY,
    customer_name       VARCHAR(120),
    segment             VARCHAR(20),
    first_purchase_date_key INT REFERENCES dim_date(date_key)  -- outrigger
);

Здесь dim_date выступает outrigger-dimension для dim_customer: dimension ссылается на dimension. Запрос «сколько клиентов сделали первую покупку в каждом квартале» использует этот outrigger:

SELECT fp.year, fp.quarter, COUNT(*) AS new_customers
FROM dim_customer c
JOIN dim_date fp ON fp.date_key = c.first_purchase_date_key
GROUP BY fp.year, fp.quarter
ORDER BY fp.year, fp.quarter;

 year | quarter | new_customers
------+---------+--------------
 2025 |    3    |      1840
 2025 |    4    |      2210
 2026 |    1    |      2755

Kimball советует применять outrigger ограниченно и осознанно: один уровень, ради реального удобства (как переиспользование date-атрибутов), а не как привычку всё нормализовать. Outrigger — это контролируемое, точечное исключение, а не возврат к полностью нормализованной схеме.

Почему именно один уровень? Потому что каждый дополнительный уровень outrigger-цепочки — это ещё один JOIN на пути от fact-таблицы к атрибуту, и снежинка возвращается со всеми своими минусами. Один уровень (dim_customer -> dim_date) добавляет ровно один JOIN и обычно оправдан конкретной пользой: вместо самописной обработки даты первой покупки переиспользуется готовая, уже наполненная dim_date со всеми календарными атрибутами и fiscal-периодами. Два, три уровня outrigger — это уже полноценный snowflaking, и тогда работают все аргументы против него. Граница «один уровень, ради явной выгоды» — это и есть то, что отделяет аккуратный outrigger от расползания снежинки.

Полезно держать в голове практический критерий выбора. Прежде чем что-либо выносить из dimension, спросите: эта группа атрибутов разделяется несколькими dimensions или нужна только здесь? Если только здесь — почти наверняка оставьте плоско, в самой dimension. Если разделяется (как dim_date, нужная и dim_customer, и fct_sales, и многим другим) — вынос в отдельную таблицу и переиспользование оправданы. Само по себе наличие повтора внутри одной dimension — не повод нормализовать: колоночное сжатие с этим повтором справится. Повод появляется тогда, когда вынесенная структура реально переиспользуется или когда у неё есть собственные атрибуты, которые сами по себе предмет анализа.

Попробуй сам

Дана денормализованная dim_store: store_key, store_name, city, region, country, opening_date. Атрибуты city, region, country образуют географическую иерархию и повторяются у многих магазинов.

1. Нарисуйте star-вариант (всё в одной таблице) и оцените: сколько JOIN нужно для запроса «выручка по странам».
2. Нарисуйте snowflake-вариант: вынесите city -> region -> country в отдельные таблицы. Сколько JOIN теперь?
3. Превратите opening_date в outrigger на dim_date. Напишите запрос «число магазинов, открытых по годам».
4. Решите для своего случая: стоит ли нормализовать гео-иерархию? Учтите, что та же иерархия может понадобиться dim_customer и dim_supplier.

Проверьте себя: если гео-иерархия нужна только dim_store и больше нигде — почти наверняка снежинка здесь не оправдана.