DataLearn DE-101: Star vs Snowflake schema — какая модель подходит

Условие

В DWH-моделировании по Кимбаллу есть две классические схемы — Star и Snowflake. Объясните разницу, нарисуйте пример для интернет-магазина (orders, customers, products, categories, dates). Какую выберете для BI-отчётов и почему?

Решение

Подход

Star schema — одна fact-таблица в центре, размерности (dim-таблицы) денормализованы.

            dim_customer
                 │
dim_date ─── fact_orders ─── dim_product (с denormalized category_name, category_path)
                 │
            dim_store

Snowflake schema — те же dim-таблицы, но нормализованы (категории → отдельная таблица).

                            dim_customer
                                 │
dim_date ─────── fact_orders ─── dim_product ─── dim_category ─── dim_dept
                                 │
                            dim_store ─── dim_region

Сравнение

Star для магазина

-- fact_orders (grain: одна строка = одна позиция в заказе)
CREATE TABLE fact_orders (
    order_line_id   BIGINT PRIMARY KEY,
    order_id        BIGINT,
    date_key        INT  REFERENCES dim_date(date_key),
    customer_key    INT  REFERENCES dim_customer(customer_key),
    product_key     INT  REFERENCES dim_product(product_key),
    quantity        INT,
    unit_price      NUMERIC(10, 2),
    discount        NUMERIC(10, 2),
    line_amount     NUMERIC(12, 2)
);
 
CREATE TABLE dim_product (
    product_key     INT PRIMARY KEY,
    sku             VARCHAR(50),
    product_name    VARCHAR(255),
    brand           VARCHAR(100),
    category_l1     VARCHAR(100),    -- денормализованная иерархия
    category_l2     VARCHAR(100),
    category_l3     VARCHAR(100),
    price_segment   VARCHAR(20)
);
 
CREATE TABLE dim_customer (
    customer_key    INT PRIMARY KEY,
    customer_id     VARCHAR(50),
    full_name       VARCHAR(255),
    email_hash      VARCHAR(64),
    city            VARCHAR(100),
    region          VARCHAR(100),    -- денормализованная гео
    country         VARCHAR(100),
    segment         VARCHAR(50),
    valid_from      DATE,            -- SCD-2 if нужны исторические срезы
    valid_to        DATE,
    is_current      BOOLEAN
);

Запрос для отчёта

SELECT d.year, d.quarter, p.category_l1, SUM(f.line_amount) AS revenue
FROM fact_orders f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year = 2025
GROUP BY d.year, d.quarter, p.category_l1
ORDER BY revenue DESC;

— всего 2 JOIN'а. Tableau/Power BI любят такую структуру.

Выбор

Для BI — Star почти всегда. Snowflake оправдан, если:

• размерность гигантская и хранение дорогое;
• иерархия меняется часто и важна нормализация;
• DWH строго нормализован по корпоративному стандарту.

Подводные камни

1. Grain fact-таблицы — самое важное. Один заказ или одна позиция? Меняется навсегда.
2. SCD (Slowly Changing Dimensions) — клиент сменил город. Type 1 (overwrite), Type 2 (история через valid_from/valid_to), Type 3 (старое значение в отдельной колонке).
3. Surrogate keys (product_key) vs natural (sku): surrogate стабильнее при ребрендингах и SCD-2.
4. NULL в dim: используйте «Unknown» dim_row с key = -1, чтобы LEFT JOIN не порождал NULL'ы.
5. Date dimension — отдельная dim с year/quarter/month/dow/holiday_flag сильно ускоряет запросы (нет EXTRACT в WHERE).
6. Денормализация = риск рассинхронизации: если категорию переименовали — обновить надо во всех строках dim_product.

Эталонный ответ

Star schema — центральная fact + денормализованные dim'ы; мало JOIN'ов, быстрая, понятная BI-аналитикам. Snowflake — нормализованные dim'ы с иерархиями; экономит место, но даёт больше JOIN'ов.

Для BI выбираем Star: ускоряет запросы, проще читается бизнесом. Snowflake — только если размерности огромные и нормализация критична.