Star Schema

Star Schema — звёздная схема¶

Звёздная схема — самый распространённый способ организации аналитического хранилища данных. Если ты работал с SQL-запросами по нескольким таблицам, но не понимал, почему аналитические базы устроены иначе — эта статья объяснит логику.

Подход описан Ральфом Кимбаллом (Ralph Kimball) в 1996 году и с тех пор остаётся стандартом де-факто для BI и аналитики.

Зачем нужна звёздная схема¶

В транзакционной (OLTP) базе таблицы нормализованы: данные не дублируются, связи через внешние ключи, всё аккуратно. Но для аналитики это неудобно:

Запросы требуют множества JOIN — медленно и сложно
Бизнес-пользователи не понимают структуру из 30 таблиц
BI-инструменты ожидают простую модель «факт + измерения»

Кулинарная аналогия

Представь OLTP-базу как склад с ингредиентами: всё рассортировано по полочкам, ничего не повторяется. А Star Schema — это уже сервировка: готовое блюдо на тарелке, где всё рядом и понятно.

Звёздная схема решает эти проблемы: одна таблица фактов в центре и несколько таблиц измерений вокруг неё, как лучи звезды.

Основные компоненты¶

Таблица фактов (fact table)¶

Содержит числовые метрики бизнес-процесса и внешние ключи на таблицы измерений.

Характеристики:

Каждая строка — одно событие или транзакция
Колонки-метрики: сумма, количество, цена, длительность
Колонки-ключи: ссылки на измерения (FK)
Обычно самая большая таблица в хранилище

Таблицы измерений (dimension tables)¶

Содержат описательные атрибуты — контекст для фактов.

Характеристики:

Относительно немного строк (десятки-тысячи, не миллионы)
Много колонок с текстовыми описаниями
Суррогатный ключ (обычно _key или _sk) — не бизнес-ключ
Атрибуты для фильтрации, группировки, подписей в отчётах

Суррогатный vs натуральный ключ

В измерениях используют суррогатный ключ (целое число, автоинкремент), а не бизнес-ключ из источника. Это позволяет отслеживать историю изменений (см. SCD) и не зависеть от формата ключей в системе-источнике.

Практический пример: интернет-магазин¶

Возьмём знакомый набор данных из раздела SQL — пользователи, товары, заказы — и спроектируем из него звёздную схему.

DDL: таблицы измерений¶

SQL

-- Измерение: клиенты
CREATE TABLE dim_customer (
    customer_key  SERIAL PRIMARY KEY,
    customer_id   INT NOT NULL,          -- натуральный ключ из OLTP
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(200),
    registered_at DATE,
    city          VARCHAR(100),
    segment       VARCHAR(50)            -- VIP, Regular, New
);

-- Измерение: товары
CREATE TABLE dim_product (
    product_key   SERIAL PRIMARY KEY,
    product_id    INT NOT NULL,          -- натуральный ключ из OLTP
    name          VARCHAR(200) NOT NULL,
    category      VARCHAR(100),
    subcategory   VARCHAR(100),
    brand         VARCHAR(100),
    unit_price    NUMERIC(10,2)
);

-- Измерение: дата (календарь)
CREATE TABLE dim_date (
    date_key      INT PRIMARY KEY,       -- формат YYYYMMDD
    full_date     DATE NOT NULL,
    year          SMALLINT NOT NULL,
    quarter       SMALLINT NOT NULL,
    month         SMALLINT NOT NULL,
    month_name    VARCHAR(20) NOT NULL,
    day_of_week   SMALLINT NOT NULL,
    day_name      VARCHAR(20) NOT NULL,
    is_weekend    BOOLEAN NOT NULL,
    is_holiday    BOOLEAN DEFAULT FALSE
);

Зачем отдельная таблица дат?

Таблица dim_date — классика Кимбалла. Она позволяет фильтровать и группировать факты по любому срезу календаря (квартал, день недели, праздник/будень) без вычислений в каждом запросе.

DDL: таблица фактов¶

SQL

-- Факт: продажи (одна строка = одна позиция в заказе)
CREATE TABLE fact_sales (
    customer_key  INT NOT NULL REFERENCES dim_customer(customer_key),
    product_key   INT NOT NULL REFERENCES dim_product(product_key),
    date_key      INT NOT NULL REFERENCES dim_date(date_key),
    order_id      INT NOT NULL,          -- дегенеративное измерение
    quantity      INT NOT NULL,
    unit_price    NUMERIC(10,2) NOT NULL,
    total_amount  NUMERIC(12,2) NOT NULL, -- quantity * unit_price
    discount      NUMERIC(10,2) DEFAULT 0
);

Дегенеративное измерение

order_id не ссылается на отдельную таблицу измерений — он хранится прямо в факте. Это и есть дегенеративное измерение (degenerate dimension): атрибут, у которого нет собственных описательных полей.

Загрузка тестовых данных¶

SQL

-- Заполняем измерение дат (январь 2026)
INSERT INTO dim_date (date_key, full_date, year, quarter, month, month_name,
                      day_of_week, day_name, is_weekend)
VALUES
    (20260120, '2026-01-20', 2026, 1, 1, 'Январь', 2, 'Вторник',   FALSE),
    (20260122, '2026-01-22', 2026, 1, 1, 'Январь', 4, 'Четверг',   FALSE),
    (20260123, '2026-01-23', 2026, 1, 1, 'Январь', 5, 'Пятница',   FALSE);

-- Заполняем измерение клиентов
INSERT INTO dim_customer (customer_key, customer_id, name, email, registered_at, segment)
VALUES
    (1, 1, 'Анна',  'anna@example.com',  '2026-01-10', 'Regular'),
    (2, 2, 'Борис', 'boris@example.com', '2026-01-11', 'New');

-- Заполняем измерение товаров
INSERT INTO dim_product (product_key, product_id, name, category, unit_price)
VALUES
    (1, 1, 'Клавиатура',  'Периферия',    49.90),
    (2, 2, 'Мышь',        'Периферия',    19.90),
    (3, 3, 'Монитор',     'Мониторы',    199.00),
    (4, 4, 'Кабель USB-C','Аксессуары',    9.90);

-- Заполняем таблицу фактов
INSERT INTO fact_sales (customer_key, product_key, date_key, order_id,
                        quantity, unit_price, total_amount)
VALUES
    (1, 1, 20260120, 1001, 1, 49.90, 49.90),
    (1, 2, 20260120, 1001, 2, 19.90, 39.80),
    (1, 3, 20260122, 1002, 1, 199.00, 199.00),
    (2, 4, 20260123, 1003, 3, 9.90, 29.70),
    (2, 2, 20260123, 1003, 1, 19.90, 19.90);

Аналитические запросы¶

Теперь видно, как Star Schema упрощает аналитику — все запросы читаются одинаково: FROM fact JOIN dim.

Продажи по категориям товаров¶

SQL

SELECT
    p.category,
    SUM(f.total_amount) AS revenue,
    SUM(f.quantity)      AS items_sold
FROM fact_sales   f
JOIN dim_product  p ON f.product_key = p.product_key
GROUP BY p.category
ORDER BY revenue DESC;

Результат:

category	revenue	items_sold
Мониторы	199.00	1
Периферия	89.70	3
Аксессуары	29.70	3

Продажи по клиентам и дням недели¶

SQL

SELECT
    c.name       AS customer,
    d.day_name   AS weekday,
    SUM(f.total_amount) AS revenue
FROM fact_sales    f
JOIN dim_customer  c ON f.customer_key = c.customer_key
JOIN dim_date      d ON f.date_key     = d.date_key
GROUP BY c.name, d.day_name
ORDER BY revenue DESC;

Средний чек по сегменту клиентов¶

SQL

SELECT
    c.segment,
    COUNT(DISTINCT f.order_id) AS orders,
    ROUND(SUM(f.total_amount) / COUNT(DISTINCT f.order_id), 2) AS avg_check
FROM fact_sales    f
JOIN dim_customer  c ON f.customer_key = c.customer_key
GROUP BY c.segment;

Типы таблиц фактов¶

Не все факты одинаковы. Кимбалл выделяет три типа:

Тип	Зернистость	Пример
Transaction	Одна строка = одно событие	Продажа, клик, платёж
Periodic snapshot	Одна строка = итог за период	Остатки на складе на конец дня
Accumulating snapshot	Одна строка = весь жизненный цикл	Заказ от создания до доставки

Пример: accumulating snapshot для заказа

SQL

CREATE TABLE fact_order_pipeline (
    order_key       INT PRIMARY KEY,
    customer_key    INT REFERENCES dim_customer(customer_key),
    created_date_key INT REFERENCES dim_date(date_key),
    paid_date_key    INT REFERENCES dim_date(date_key),
    shipped_date_key INT REFERENCES dim_date(date_key),
    total_amount    NUMERIC(12,2),
    current_status  VARCHAR(20)
);

Каждая колонка *_date_key заполняется по мере прохождения этапа. Пока заказ не оплачен, paid_date_key = NULL.

Преимущества и ограничения¶

Преимущества¶

Простота — бизнес-пользователи и BI-инструменты легко работают с моделью
Производительность — меньше JOIN, чем в нормализованной схеме
Гибкость запросов — любая комбинация измерений без перестройки модели
Стандарт индустрии — поддерживается всеми BI-платформами (Tableau, Power BI, Metabase)

Ограничения¶

Избыточность данных — атрибуты измерений дублируются (денормализация)
Сложность обновлений — изменение атрибута клиента затрагивает измерение (решается через SCD)
Не подходит для OLTP — это модель только для чтения и аналитики
Жёсткая структура — добавление нового бизнес-процесса требует новой таблицы фактов

Что запомнить¶

Star Schema = таблица фактов (числа) + таблицы измерений (описания)
Факт — это событие бизнеса, измерение — его контекст
Суррогатные ключи в измерениях отвязывают хранилище от систем-источников
Запросы всегда читаются одинаково: FROM fact JOIN dim1 JOIN dim2 ... GROUP BY
Для отслеживания изменений в измерениях используют SCD — об этом следующая статья

Проверь себя¶

Источники¶

The Data Warehouse Toolkit — классика Кимбалла, описание star schema и dimensional modeling
Star Schema — PostgreSQL Wiki — реализация звёздной схемы в PostgreSQL
Dimensional Modeling Techniques — обзор всех техник моделирования от Kimball Group