Хранилище данных моделируют вокруг двух типов таблиц: факты (что произошло — заказ, платёж, клик) и измерения (в каком контексте это произошло — какой клиент, какой товар, какая дата). Схема звезда — это одна таблица фактов в центре и денормализованные измерения вокруг неё. Снежинка — те же измерения, но разбитые на нормализованные под-таблицы. Звезда проще и быстрее для отчётов, снежинка экономит место и строже к консистентности. В аналитических хранилищах в подавляющем большинстве случаев выигрывает звезда — и ниже я разберу почему, а заодно покажу, где начинаются грабли.
Что такое факты и измерения, и как их различать?
Первое, что делаешь при проектировании хранилища, — раскладываешь данные на факты и измерения. Это не формальность, а способ мышления.
Факт — это событие бизнеса, которое можно посчитать. Заказ на 3200 рублей, платёж, отгрузка, показ баннера, сессия. У факта почти всегда есть числовые меры (amount, quantity, discount) и ссылки на контекст. Фактов много, они постоянно приезжают, таблица растёт линейно во времени.
Измерение — это контекст, в разрезе которого ты смотришь на факты. Клиент, товар, магазин, дата, канал привлечения. Измерений мало по объёму строк, но у них много атрибутов: у клиента есть город, сегмент, дата регистрации, тариф.
Простой тест: если тянет написать SUM() или COUNT() по колонке — это мера, и она живёт в факте. Если тянет написать GROUP BY по колонке — это атрибут измерения. Выручка по городам — SUM(amount) из факта, сгруппированная по city из измерения клиента.
SELECT d.city,
SUM(f.amount) AS revenue,
COUNT(*) AS orders_cnt
FROM fact_orders f
JOIN dim_customer d ON d.customer_key = f.customer_key
GROUP BY d.city;Вот здесь fact_orders — факт, dim_customer — измерение, и весь смысл модели в том, чтобы такие запросы писались вот настолько буднично.
Как выглядит схема звезда на конкретных таблицах?
Звезда — это буквально картинка: факт в центре, измерения-лучи по кругу, каждое соединяется с фактом одним ключом. Никаких промежуточных таблиц между фактом и измерением.
CREATE TABLE dim_customer (
customer_key BIGINT PRIMARY KEY, -- surrogate key
customer_id BIGINT, -- натуральный ключ из источника
city TEXT,
segment TEXT,
signup_date DATE
);
CREATE TABLE dim_product (
product_key BIGINT PRIMARY KEY,
product_id BIGINT,
product_name TEXT,
category TEXT, -- денормализовано прямо сюда
brand TEXT
);
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- 20260711
full_date DATE,
year INT,
month INT,
weekday INT,
is_weekend BOOLEAN
);
CREATE TABLE fact_orders (
order_key BIGINT PRIMARY KEY,
customer_key BIGINT REFERENCES dim_customer,
product_key BIGINT REFERENCES dim_product,
date_key INT REFERENCES dim_date,
amount NUMERIC(12,2),
quantity INT,
discount NUMERIC(12,2)
);Обрати внимание: category и brand лежат прямо внутри dim_product. Данные повторяются — у сотни товаров одного бренда бренд написан сотню раз. Это осознанная денормализация, и в этом вся идея звезды: ради простоты и скорости чтения мы миримся с избыточностью.
Если хочется потренировать такие джойны на живой базе, это ровно тот материал, который гоняют в SQL-тренажёре — фактовая таблица плюс несколько измерений и куча GROUP BY.
Чем снежинка отличается от звезды и когда она оправдана?
Снежинка получается, когда измерение раскладывают на нормализованные части. Вместо одной dim_product с колонками category и brand появляются отдельные dim_category и dim_brand, а dim_product ссылается на них ключами.
CREATE TABLE dim_brand (
brand_key BIGINT PRIMARY KEY,
brand_name TEXT
);
CREATE TABLE dim_category (
category_key BIGINT PRIMARY KEY,
category_name TEXT
);
CREATE TABLE dim_product (
product_key BIGINT PRIMARY KEY,
product_id BIGINT,
product_name TEXT,
category_key BIGINT REFERENCES dim_category,
brand_key BIGINT REFERENCES dim_brand
);Теперь бренд хранится один раз, и если он переименовался, ты правишь одну строку. Красиво? С точки зрения чистоты данных — да. С точки зрения аналитика, который хочет выручку по брендам, — теперь надо джойнить не два стола, а четыре:
SELECT b.brand_name,
SUM(f.amount) AS revenue
FROM fact_orders f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_brand b ON b.brand_key = p.brand_key
GROUP BY b.brand_name;Снежинка оправдана, когда у измерения реально огромные текстовые атрибуты, которые дублируются на миллионах строк и раздувают хранилище, или когда одна и та же справочная сущность (регион, валюта, юр.лицо) переиспользуется десятком измерений и её надо вести в одном месте. Во всех остальных случаях лишние джойны не стоят экономии на диске — диск дешёвый, а время аналитика нет.
Зачем в измерениях нужны surrogate keys?
Surrogate key (суррогатный ключ) — это искусственный числовой идентификатор строки измерения, который живёт только внутри хранилища и не имеет бизнес-смысла. Именно customer_key, а не customer_id из CRM. И вот зачем он нужен.
Во-первых, источники ненадёжны. Сегодня клиент приходит с целочисленным id, завтра систему меняют и приезжают UUID, а послезавтра два источника дают пересекающиеся id для разных людей. Суррогатный ключ развязывает хранилище от этого хаоса: снаружи что угодно, внутри — стабильный BIGINT.
Во-вторых, суррогатный ключ — это единственный способ нормально хранить историю. Если клиент переехал из Казани в Москву, а тебе надо, чтобы старые заказы остались привязаны к «Казани», нельзя обновлять строку на месте по натуральному ключу. Заводишь новую версию строки с тем же customer_id, но новым customer_key — и факты старых заказов продолжают указывать на старую версию. Это прямой мост к SCD, о котором ниже.
В-третьих, целочисленный ключ — это быстрые джойны и компактный факт. Хранить в миллиардной фактовой таблице узкий BIGINT вместо длинной строки email — ощутимая экономия и по месту, и по скорости.
Что такое гранулярность факта и почему это первое решение?
Гранулярность (grain) — это ответ на вопрос «одна строка факта = что именно?». И это решение принимаешь до того, как напишешь первую CREATE TABLE, потому что переделать грануляр потом — это переписать половину хранилища.
Грануляр fact_orders может быть «одна строка = один заказ» или «одна строка = одна позиция в заказе». Это разные миры. Если строка — заказ, ты не сможешь посчитать выручку по товарам, потому что в одном заказе их несколько. Если строка — позиция, то amount — это сумма по позиции, а сумму заказа надо агрегировать.
Правило простое: бери самую детальную гранулярность, которую позволяет источник. Из детальных строк ты всегда соберёшь агрегат наверх, а вот из агрегата разложить обратно на детали уже нельзя — данные потеряны. Строку «выручка за день по городу» невозможно разбить на конкретные заказы.
И ещё: все меры в факте должны соответствовать заявленному грануляру. Нельзя в факт с гранулярностью «позиция заказа» положить delivery_cost, который относится ко всему заказу, — иначе при суммировании доставка задублируется на каждую позицию и выручка поедет. Такие ошибки на собеседованиях любят подсовывать, и разбор похожих ловушек есть в банке вопросов по аналитике.
Кимбалл или Инмон — чей подход выбрать?
Это два классических взгляда на то, как строить хранилище целиком, и разница у них не в синтаксисе, а в порядке действий.
Ральф Кимбалл предлагает подход снизу вверх: строишь витрины (data marts) под конкретные бизнес-процессы — продажи, маркетинг, поддержка — сразу в виде звёзд, а общие измерения (клиент, дата, товар) делаешь одинаковыми во всех витринах. Их называют conformed dimensions — согласованные измерения. Это быстро даёт ценность: одна витрина готова — бизнес уже что-то считает.
Билл Инмон предлагает подход сверху вниз: сначала строишь единое корпоративное хранилище в третьей нормальной форме — строго нормализованное, как OLTP-база, единый источник правды для всей компании. И только потом из него нарезаешь витрины-звёзды под конкретные отделы.
На практике сегодня чаще встречается гибрид: сырой слой хранят близко к нормализованному (staging, raw), а витрины наверху делают по Кимбаллу — звёздами. Для аналитика важно понимать: когда ты пишешь отчёты, ты почти всегда работаешь именно с кимбалловским слоем звёзд, даже если под капотом инмоновское ядро. Разбор того, как эти слои складываются в реальные проекты, хорошо ложится на практические кейсы.
Почему BI-инструменты так любят звезду?
Потому что звезда — это ровно та форма, под которую заточены все инструменты отчётности, и вот по каким причинам.
Первое — предсказуемые джойны. В звезде путь от факта к любому атрибуту — ровно один джойн. BI-движок строит запрос механически: факт плюс нужные измерения, и всё. В снежинке движку надо знать цепочку джойнов через промежуточные таблицы, планы запросов усложняются, а автогенерация SQL начинает спотыкаться.
Второе — понятная семантика для пользователя. В интерфейсе BI бизнес-пользователь видит «меры» (то, что суммируется) и «измерения» (то, по чему группируется). Звезда мапится на это один в один: меры из факта, атрибуты из измерений. Снежинка размазывает атрибуты одного измерения по нескольким таблицам, и пользователю уже надо знать внутреннюю кухню.
Третье — скорость. Меньше джойнов — меньше работы оптимизатору, лучше кэшируется, проще партиционировать факт по date_key. Колоночные аналитические СУБД и вовсе жмут денормализованные широкие измерения отлично, так что аргумент снежинки «экономим место» во многом теряет силу.
Именно поэтому базовые продуктовые метрики вроде DAU и retention почти всегда считают поверх фактов событий с измерением даты и пользователя — это классическая звезда, и запрос на неё пишется в три строки.
Как схема звезда связана со SCD и историей изменений?
SCD (Slowly Changing Dimensions, медленно меняющиеся измерения) — это про то, как в измерении хранить историю атрибутов, и звезда с суррогатными ключами создана ровно под это.
Атрибуты измерений со временем меняются: клиент меняет город, товар переходит в другую категорию, менеджер — отдел. Вопрос: что делать со старыми фактами? Есть три частых стратегии.
SCD Type 1 — просто перезаписать. Старое значение теряется, у всех заказов клиента задним числом становится новый город. Просто, но история переписывается.
SCD Type 2 — завести новую версию строки. Старую закрываем, новую открываем, факты до переезда указывают на старую версию, после — на новую. Это самый честный вариант, и именно ради него нужен суррогатный ключ: customer_id один, а customer_key у версий разные.
CREATE TABLE dim_customer (
customer_key BIGINT PRIMARY KEY, -- разный у каждой версии
customer_id BIGINT, -- одинаковый у всех версий
city TEXT,
valid_from DATE,
valid_to DATE, -- NULL = текущая версия
is_current BOOLEAN
);Теперь «выручка по городам за прошлый год» покажет город на момент заказа, а не сегодняшний, — потому что факт держится за конкретную историческую версию через customer_key. Без суррогатного ключа так не сделаешь.
SCD Type 3 — хранить одно предыдущее значение в отдельной колонке (current_city, previous_city). Компромисс на случай, когда нужен только «до и после».
Понимание SCD Type 2 — это тот навык, который отличает человека, который «умеет писать SELECT», от того, кто умеет строить хранилище. На собеседованиях на дата-инженера и аналитика вопрос про SCD почти дежурный.
С чего начать моделировать своё первое хранилище?
Порядок, который я советую держать в голове: сначала определяешь бизнес-процесс (продажи, платежи, регистрации), потом фиксируешь гранулярность факта на самом детальном уровне, потом выписываешь измерения вокруг него, добавляешь суррогатные ключи и только в конце решаешь, где нужна снежинка, а где хватит плоской звезды. По умолчанию — звезда; снежинку достаёшь, когда для неё есть конкретная причина.
Лучший способ уложить это в голову — не читать, а писать запросы поверх готовой звезды: собрать выручку по разрезам, посчитать когорты, прикрутить SCD Type 2 и проверить, что исторические срезы бьются. Потренироваться на джойнах факт-измерение можно в SQL-тренажёре и на подборке задач, базовую теорию джойнов и группировок освежить в SQL-справочнике, а собрать всё в систему — пройдя курс по SQL с нуля.
Если хочешь двигаться по структурированному пути — от моделирования DWH до разбора реальных собесов с дата-инженерами, — Pro открывает все задачи, кейсы и безлимитные AI-собеседования. Но и на бесплатном уровне звезду со снежинкой вполне можно распробовать на практике — начни с пары запросов в тренажёре.