Звёздная схема и проектирование хранилищ данных
Таблицы фактов и измерений, компромиссы денормализации и моделирование OLAP
«Звёздная схема и проектирование хранилищ данных» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.
OLTP и OLAP
Вопросы о хранилищах данных начинаются с одного различия, которое на собеседовании от Вас ожидают: OLTP и OLAP.
- OLTP (транзакционная обработка): множество небольших операций чтения и записи, высокая нормализация для обеспечения целостности. На этом работает приложение.
- OLAP (аналитическая обработка): небольшое число крупных агрегирующих операций чтения по историческим данным, намеренно денормализованная структура для скорости. На этом работают отчёты и панели мониторинга.
Звёздные схемы относятся к проектированию OLAP. Их главная цель — быстрые аналитические запросы, ради чего допускается избыточность.
Факты и измерения
Звёздная схема разделяет данные на два вида таблиц:
- Таблица фактов: измеримые события или транзакции (продажа, нажатие). Она хранит числовые показатели и внешние ключи измерений.
- Таблицы измерений: описательный контекст, в разрезе которого Вы выполняете анализ (дата, товар, клиент, магазин).
Таблица фактов находится в центре, а измерения окружают её подобно лучам звезды — отсюда и название.
Структура таблицы фактов
Таблица фактов в основном состоит из внешних ключей и числовых показателей. Она высокая и узкая и постоянно растёт.
Показатели — это аддитивные числа, которые Вы агрегируете: количество, выручка, себестоимость. Гранулярность (одна строка = один ?) должна быть чётко задана; здесь одна строка — одна товарная позиция в одной продаже.
CREATE TABLE fact_sales (
sale_id BIGINT PRIMARY KEY,
date_key INT NOT NULL, -- FK to dim_date
product_key INT NOT NULL, -- FK to dim_product
customer_key INT NOT NULL, -- FK to dim_customer
store_key INT NOT NULL, -- FK to dim_store
quantity INT, -- measure
revenue DECIMAL(12,2), -- measure
cost DECIMAL(12,2) -- measure
);Структура таблицы измерений
Измерения короткие и широкие: в них много описательных столбцов, по которым Вы фильтруете и группируете данные. Они намеренно денормализованы, поэтому для запроса требуется только одно соединение на каждое измерение.
Обратите внимание: dim_product хранит категорию и бренд в одной строке, а не в отдельных таблицах. В этом и заключается смысл избыточности: во время выполнения запроса не нужны дополнительные соединения.
CREATE TABLE dim_product (
product_key INT PRIMARY KEY, -- surrogate key
product_id INT, -- natural/business key
product_name VARCHAR(100),
category VARCHAR(50), -- denormalized
brand VARCHAR(50), -- denormalized
unit_price DECIMAL(10,2)
);Запрос к звёздной схеме
Именно это даёт Вам такая структура. Типичный аналитический запрос соединяет таблицу фактов с несколькими измерениями, фильтрует данные и выполняет агрегацию. Одно соединение на измерение, без длинных цепочек.
На собеседованиях Вас просят написать именно такой запрос к звёздной схеме.
SELECT d.category,
t.year,
SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product d ON d.product_key = f.product_key
JOIN dim_date t ON t.date_key = f.date_key
WHERE t.year = 2025
GROUP BY d.category, t.year
ORDER BY total_revenue DESC;Суррогатные ключи
В измерениях используется суррогатный ключ: бессмысленный целочисленный первичный ключ (например, product_key), сгенерированный хранилищем и не связанный с естественным ключом исходной системы.
Почему это важно на собеседовании:
- Он отделяет хранилище от изменяющихся бизнес-ключей.
- Он делает таблицы фактов компактными по ширине (соединения по целым числам выполняются быстро).
- Он необходим для отслеживания истории с помощью медленно изменяющихся измерений (в следующем разделе).
Медленно изменяющиеся измерения
Излюбленная тема на собеседованиях по хранилищам: что делать, когда меняется атрибут измерения (например, клиент переезжает в другой город)? Это медленно изменяющиеся измерения (SCD):
- Тип 1: перезаписать старое значение. История не сохраняется.
- Тип 2: добавить новую строку с датами действия и признаком текущей записи. Полная история; для этого нужны суррогатные ключи.
- Тип 3: хранить столбец «предыдущее значение». Ограниченная история.
Тип 2 — наиболее часто ожидаемый ответ для отслеживания изменений во времени.
-- SCD Type 2 dimension
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY, -- surrogate
customer_id INT, -- natural key
city VARCHAR(50),
valid_from DATE,
valid_to DATE,
is_current BOOLEAN
);Звёздная схема и схема «снежинка»
Ожидайте вопроса на сравнение. Схема «снежинка» нормализует измерения во вспомогательные таблицы (товар -> категория -> отдел), тогда как звёздная схема оставляет их плоскими.
- Звёздная схема: меньше соединений, более быстрое чтение, некоторая избыточность. Предпочтительна для высокой производительности запросов.
- Схема «снежинка»: меньше места для хранения и более простое сопровождение измерений, но больше соединений в каждом запросе.
Скажите: «По умолчанию выбирайте звёздную схему для скорости запросов; схему „снежинка“ — только когда измерения велики и используются повторно».
Измерение даты
Почти каждая звёздная схема содержит отдельное измерение даты, а не необработанный столбец даты. В нём заранее вычисляются год, квартал, месяц, день недели, признаки праздников и финансовые периоды.
Благодаря этому аналитики могут группировать данные по «финансовому кварталу» или «выходному» с помощью простого соединения, а не разрозненных функций работы с датами. Упоминание измерения даты без подсказки — сильный признак того, что Вы создавали хранилища данных.
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- e.g. 20250131
full_date DATE,
year INT,
quarter INT,
month INT,
day_of_week VARCHAR(10),
is_weekend BOOLEAN,
fiscal_qtr VARCHAR(6)
);Выбор гранулярности
Самое важное решение при проектировании таблицы фактов — это гранулярность: что представляет собой одна строка. Определите её до всего остального.
- Слишком крупная гранулярность (одна строка на день и магазин) приводит к потере подробностей.
- Слишком мелкая (одна строка на отсканированный товар) приводит к взрывному росту таблицы.
Чёткое описание гранулярности, например «одна строка на товар в строке заказа», определяет, какие измерения и показатели должны входить в модель. На собеседовании обращают внимание на такую дисциплину.
Когда проводить денормализацию
Свяжите это с нормализацией. Системы OLTP нормализуют данные до третьей нормальной формы для обеспечения целостности, а хранилища намеренно денормализуют измерения ради скорости чтения.
Компромисс, который Вы должны уметь объяснить:
- Избыточные данные измерений допустимы, потому что хранилище загружается контролируемым ETL, а не непредусмотренными записями из приложения.
- Меньшее число соединений означает более быструю агрегацию по миллиардам строк таблицы фактов.
Здесь от уверенных ответов старшего уровня отличает не знание правила, а умение оценивать ситуацию.
Быстрая проверка
Вы проектируете хранилище данных о продажах и хотите сохранить полную историю городов, в которых жил клиент, включая его переезды.
Повторение: звёздная схема и проектирование хранилища
Теперь Вы можете отвечать на вопросы о моделировании хранилищ:
- OLTP нормализует данные для обеспечения целостности, а OLAP денормализует их ради скорости чтения.
- В звёздной схеме центральная таблица фактов (внешние ключи и числовые показатели) окружена плоскими измерениями.
- Используйте суррогатные ключи и отдельное измерение даты.
- Отслеживайте изменения с помощью SCD типа 2; сначала определяйте гранулярность таблицы фактов.
- Для производительности запросов предпочитайте звёздную схему схеме «снежинка».
Часто задаваемые вопросы
Урок «Звёздная схема и проектирование хранилищ данных» бесплатный?
Да — полный текст урока «Звёздная схема и проектирование хранилищ данных» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.
Чему я научусь в уроке «Звёздная схема и проектирование хранилищ данных»?
Таблицы фактов и измерений, компромиссы денормализации и моделирование OLAP Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать Coding Interview Prep?
Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.
Сколько времени занимает урок «Звёздная схема и проектирование хранилищ данных»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке Coding Interview Prep?
Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Нормализация до третьей нормальной формы
- ER-моделирование и кардинальность связей
- Звёздная схема и проектирование хранилищ данных
- Полный набор задач для пробного собеседования