Star schema vs Snowflake schema для Data Engineer

Зачем спрашивают на собесе DE

Star и snowflake — базовые подходы Kimball-моделирования, любая DWH-команда строит вокруг них. На собесе DE дадут таблицу заказов и попросят: «спроектируй star schema». Кандидат должен выделить facts, dimensions, surrogate keys и обосновать выбор star vs snowflake.

Главная боль без понимания моделирования — DE загрузил данные «как пришли», аналитик пишет 7-этажные SQL с self-join, отчёт работает 5 минут. Через год команда переписывает всё на star, теряя месяцы.

Эта статья — про базовую интуицию: что выделить в факты, что в dimensions, и когда оправдана нормализация в snowflake.

Что такое fact и dimension

Fact (факт) — таблица событий бизнеса с измеримыми метриками: транзакции, клики, заказы, отгрузки. Ключевые признаки:

• Много строк (миллионы–миллиарды)
• Числовые меры: amount, quantity, duration
• Foreign keys на dimensions
• Append-only (новые строки), редкие апдейты

Dimension (измерение) — таблица контекста: кто, что, где, когда, как. Признаки:

• Меньше строк (от десятков до миллионов, обычно сильно меньше fact)
• Описательные атрибуты: имя, страна, категория
• Surrogate key
• Меняется медленно (SCD Type 1/2)

Пример: fact_orders (id, user_sk, item_sk, store_sk, time_sk, amount, quantity), dim_user, dim_item, dim_store, dim_time.

Star schema

Денормализованные dimensions, факт ссылается на каждый напрямую.

        dim_user             dim_item
            \                /
             \              /
              fact_orders
             /              \
            /                \
        dim_store         dim_time

dim_user хранит ВСЕ атрибуты пользователя плоско:

| user_sk | user_id | name | email | city | country | continent | tier |
| 100     | 1       | Anna | a@... | MSK  | RU      | Europe    | gold |

continent дублируется для каждой страны, tier — для каждого пользователя.

Плюсы:

• Простые запросы: 1 fact + N dim, минимум JOIN
• Хорошая производительность на колоночных движках (CH, BigQuery)
• Аналитики пишут запросы быстрее
• BI-инструменты (Tableau, PowerBI) лучше работают со star

Минусы:

• Дублирование атрибутов («continent» в каждой строке dim_user)
• Update сложнее (поменялся continent у RU → апдейт во всех строках с RU)
• Storage больше

Snowflake schema

Нормализованные dimensions: dim_user → dim_country → dim_continent.

                    dim_continent
                        |
                    dim_country
                        |
        dim_user        |
            \           |
             \          |
              fact_orders
             /
            /
        dim_store

dim_user хранит только country_sk:

| user_sk | user_id | name | email | city  | country_sk | tier |
| 100     | 1       | Anna | a@... | MSK   | 1          | gold |

dim_country:
| country_sk | iso | name   | continent_sk |
| 1          | RU  | Россия | 1            |

dim_continent:
| continent_sk | name   |
| 1            | Europe |

Плюсы:

• Меньше дублирования
• Update проще (continent у страны меняется → одна строка)
• Меньше storage на больших справочниках

Минусы:

• Запрос требует больше JOIN (fact → dim_user → dim_country → dim_continent)
• Хуже производительность на колоночных DWH (JOIN — дорого)
• Сложнее аналитикам

Когда что выбирать

Дефолт в современных DWH — star. Snowflake применяют точечно: для очень больших dimensions, для частых апдейтов справочников, в Data Vault для core слоя.

Galaxy / fact constellation — несколько fact-таблиц делят одни и те же dimensions. На практике star/snowflake чаще всего расширяются до galaxy.

Surrogate keys и грабли

Surrogate key (sk) — искусственный ключ dimension, не зависит от source-системы. Обычно auto-increment или хеш.

dim_user:
| user_sk (PK) | user_id (BK) | ... |
| 1            | 12345        | ... |  -- v1, valid 2024
| 2            | 12345        | ... |  -- v2 после смены email

user_id — business key из источника. user_sk — внутренний.

Зачем нужен surrogate key:

• Скорость JOIN (BIGINT < VARCHAR)
• SCD Type 2: одна строка на каждое состояние bk
• Изоляция от изменений в source (BK поменялся — sk остался)

Грабли:

• При full-refresh dim sk пересчитываются → fact ломается. Решение: детерминированный sk (hash от bk + valid_from)
• Multi-source dimensions: один и тот же клиент в CRM и e-commerce → договариваться о ключе мерджа

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

Использовать BK как PK fact. При SCD Type 2 у одного BK несколько версий. Без sk факт «прицепится» ко всем — M:N.

Денормализовать «всё». Star — не «свалить всё в одну таблицу». Если dim растёт быстрее, чем fact — что-то не так.

Snowflake «потому что нормализация». В DWH нормализация — не самоцель. Кост запросов с N JOIN съедает выигрыш в storage.

Time dimension не делать. dim_time (date_sk, year, quarter, month, week, dow, is_holiday) сильно упрощает аналитику. Не использовать EXTRACT в каждом запросе.

Junk dimension забыть. Десяток boolean-флагов, размазанных по fact, — кандидаты на junk dimension (одна dim из всех комбинаций флагов).

Не считать кардинальность dimension. Если dim_user — 50М строк, а fact — 100М, JOIN дорогой. Возможно нужна mini-dimension с самыми частыми атрибутами.

Игнорировать medallion. Bronze (raw) → Silver (cleaned) → Gold (star). Star строится в Gold, не в Bronze.

Связанные темы

• SCD типы для Data Engineer
• DWH ClickHouse на собесе DE
• Подготовка к собесу Data Engineer
• dbt на собеседовании DE
• Data Warehouse vs Database

FAQ

Что популярнее в РФ — star или snowflake?

Star. На колоночных DWH (ClickHouse, GreenPlum для аналитики, BigQuery) star почти всегда выигрывает. Snowflake встречается реже, в основном в банках и enterprise с row-store DWH.

Можно ли смешивать star и snowflake?

Да, это норма. Большинство dimensions — denormalized (star), один-два больших или часто меняющихся — normalized (snowflake). На практике большинство DWH — гибрид.

Чем factless fact отличается от обычного?

Factless fact не имеет числовых мер — только foreign keys. Используется для «событий без меры»: посещение урока студентом, клик по баннеру (если интересно только событие, не value). COUNT(*) — основная агрегация.

Когда переходить от 3НФ к star?

Когда задача — аналитика. OLTP БД — 3НФ. DWH/OLAP — star. Граница: пишут BI-инструменты или аналитики SQL — нужен star.

Как описать в ТЗ моделирование?

Список fact-таблиц с гранулярностью (уровень детализации), список dimensions с key-атрибутами, тип SCD на каждое dim, surrogate vs business key, granularity time dimension, source mapping.

Это официальная информация?

Нет. Статья основана на работах Ralph Kimball («The Data Warehouse Toolkit») и общей практике моделирования DWH. Конкретные подходы зависят от компании.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.