Моделирование данных: star schema, SCD, антипаттерны

В уроках 01-07 мы говорили про движки и форматы: ClickHouse, lakehouse, Parquet, Kafka, Flink. Этот урок про схему: как разложить данные так, чтобы аналитические запросы были осмысленными, быстрыми и не разваливались при эволюции бизнеса.

Аудитория: ты разрабатываешь pipeline-ы и таблицы для аналитики, заходил в OLTP-схемы и понимаешь нормальные формы. На работе встречается вопрос “почему отчёт за прошлый квартал теперь показывает другие числа” или “как показать, какой sales rep был attached к клиенту в момент сделки, если он с тех пор сменился”. Урок отвечает.

Не цель: научить писать DDL с нуля или быть Kimball-сертифицированным архитектором. Цель: дать рабочий каркас (star schema + SCD), показать, как это легализуется в современных движках (ClickHouse, Snowflake, Iceberg), и обозначить главные грабли.

0. Зачем отдельная дисциплина для аналитики

OLTP-схема (Postgres под приложение), это нормализация: каждый факт в одном месте, минимум дублирования, защита целостности через FK. Это правильно для приложения: write-патт сравнительно простой, dataset работает в строковом представлении.

OLAP-схема (DWH, lakehouse), это денормализация: один большой “factful” датасет с дублированием атрибутов. Это правильно для аналитики:

• Колоночное хранилище (см. урок 01) дешёвое в storage, дублирование не страшно.
• JOIN на 100M+ строк с несколькими измерениями, дорогой; денормализация снижает потребность в JOIN.
• Бизнес-вопросы быстро меняются (“посчитай по сегменту, который я только что выдумал”), и схема должна это переживать.

Различие 3NF (OLTP) и dimensional (OLAP), фундаментальное. Один и тот же набор сущностей моделируется по-разному в зависимости от того, кто будет читать.

Layout Row-store (Postgres) Column-store (ClickHouse)

Запрос SELECT * region + SUM(revenue) region

Виджет напоминает, что аналитический движок не считывает строки целиком, а только нужные колонки. Это позволяет dimensional-схеме иметь “wide tables” (десятки колонок per row), и они не делают запрос медленнее.

1. Kimball dimensional modeling

В 1996 году Ralph Kimball описал dimensional modeling: данные раскладываются на факты и измерения, а схема собирается в виде “звезды”.

1.1 Fact vs dimension

• Fact, измеримое событие или транзакция бизнеса. Каждая строка fact-таблицы, один такой случай: продажа, клик, авторизация, начисление зарплаты.
• Dimension, описательный контекст факта. “Кто” (customer), “что” (product), “когда” (date), “где” (location), “как” (channel).

Пример: OrderPlaced(order_id, customer_id, product_id, date, store_id, quantity, revenue).

• Факт: одна строка с количеством и выручкой.
• Измерения: customer, product, date, store.

1.2 Star schema

                           ┌──────────────┐
                           │ dim_date     │
                           │  date_key PK │
                           │  year        │
                           │  quarter     │
                           │  month       │
                           │  day         │
                           │  weekday     │
                           └──────┬───────┘
                                  │
                                  │ FK
                                  │
┌──────────────┐         ┌────────▼────────┐         ┌──────────────┐
│ dim_customer │◄────────┤  fact_orders    ├────────►│ dim_product  │
│  cust_key PK │ FK      │  order_id PK    │ FK      │  prod_key PK │
│  cust_id     │         │  cust_key       │         │  prod_id     │
│  segment     │         │  prod_key       │         │  category    │
│  region      │         │  date_key       │         │  brand       │
│  ...         │         │  store_key      │         │  unit_cost   │
└──────────────┘         │  quantity       │         └──────────────┘
                         │  revenue        │
                         │  discount       │
                         └────────┬────────┘
                                  │ FK
                                  │
                           ┌──────▼───────┐
                           │ dim_store    │
                           │  store_key PK│
                           │  store_id    │
                           │  city        │
                           │  region      │
                           └──────────────┘

В центре fact-таблица; вокруг, измерения; FK от факта в каждое измерение. Запросы ходят как:

SELECT d.year, d.quarter,
       p.category,
       SUM(f.revenue) AS revenue,
       AVG(f.discount) AS avg_discount
FROM fact_orders f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.prod_key = p.prod_key
WHERE c.segment = 'enterprise'
GROUP BY 1, 2, 3;

Звезда читается просто, потому что JOIN-ы плоские (все в один шаг от факта). На колоночном движке это особенно эффективно, можно скипать колонки и партиции по факту, dim-таблицы маленькие и кэшируются.

1.3 Snowflake schema

Если измерения сами имеют иерархию (dim_product -> dim_brand -> dim_manufacturer), её можно раскрутить в нормализованные таблицы:

fact_orders -> dim_product -> dim_brand -> dim_manufacturer

Это snowflake schema (одноимённое DWH унаследовало название). Преимущества: меньше дублирования, гибче эволюция. Минусы: больше JOIN-ов на каждый запрос.

В современной практике star schema, дефолт; snowflake применяется точечно для очень больших измерений с глубокой иерархией. Маленькие измерения с 2-3 уровнями оставляют денормализованными.

1.4 Galaxy / fact constellation

Множественные facts, разделяющие dimensions:

dim_customer ──┬── fact_orders
               └── fact_returns
               └── fact_payments

Один customer dimension, три факта. Это типичная ситуация в зрелом DWH, где разные процессы бизнеса раскладываются на свои факты, но используют общие справочники.

2. Fact tables: типы

Три канонических типа.

2.1 Transaction fact

Одна строка, одно событие бизнеса. OrderPlaced, PageView, LoginAttempt. Не модифицируется после записи. Это самый частый тип, и он ложится на event sourcing один-в-один.

CREATE TABLE fact_orders (
    order_id BIGINT,
    cust_key BIGINT,
    prod_key BIGINT,
    date_key INT,
    store_key INT,
    quantity INT,
    revenue DECIMAL(12, 2),
    discount DECIMAL(12, 2)
)
PRIMARY KEY (order_id);

Grain (зерно факта): “одна строка = один заказ”. Это первый и самый важный design-вопрос: зафиксировать grain до моделирования. Если в команде разные люди понимают разное под “продажа” (тикет vs итоговая транзакция vs payment), таблица превращается в неконсистентность.

2.2 Periodic snapshot fact

Одна строка, состояние процесса на конец периода (день, неделя, месяц). Например, остаток на счету каждого клиента на конец дня:

CREATE TABLE fact_account_daily (
    account_key BIGINT,
    date_key INT,
    balance DECIMAL(15, 2),
    open_orders INT,
    blocked_orders INT
);

Grain: “одна строка = один аккаунт за один день”. Storage растёт линейно (N клиентов × M дней), но даёт мгновенные ответы на исторические вопросы (“какой был баланс 01.05?”).

Альтернатива, считать из transaction fact в каждом запросе. Дешевле в storage, дороже в compute. Snapshot, классический trade-off “заранее посчитанный”.

2.3 Accumulating snapshot fact

Одна строка, состояние одного бизнес-процесса с обновляющимися датами. Идеально для процессов с фиксированными вехами:

CREATE TABLE fact_order_lifecycle (
    order_id BIGINT,
    cust_key BIGINT,
    placed_date_key INT,
    paid_date_key INT,        -- NULL до оплаты
    shipped_date_key INT,     -- NULL до отгрузки
    delivered_date_key INT,   -- NULL до доставки
    cancelled_date_key INT,   -- NULL если не отменён
    revenue DECIMAL(12, 2)
);

Grain: “одна строка = один заказ во всех его ветках”. При смене состояния (заказ оплачен) обновляется дата соответствующей вехи. Это mutable fact, что в OLAP-движках требует осторожности (см. ниже).

Anti-pattern: путать accumulating snapshot с transaction fact. Если заказ имеет много жизненных стадий, и тебе нужно “сколько заказов сейчас на каждой стадии”, это accumulating snapshot. Если тебе нужно “сколько было событий ‘orderPlaced’ и ‘orderShipped’ раздельно”, это два transaction fact-а.