Snowflake Schema

Snowflake Schema — схема снежинки¶

Схема снежинки — это вариант звёздной схемы, в котором таблицы измерений дополнительно нормализованы. Если в Star Schema измерение — одна плоская таблица, то в Snowflake оно разбито на несколько связанных таблиц.

Разберёмся, когда это оправданно, а когда только добавляет сложность.

В чём отличие от Star Schema¶

В звёздной схеме измерение dim_product содержит все атрибуты в одной таблице:

SQL

-- Star Schema: всё в одной таблице
SELECT product_key, name, category, subcategory, brand
FROM dim_product;

product_key	name	category	subcategory	brand
1	Клавиатура	Периферия	Устройства ввода	Logitech
2	Мышь	Периферия	Устройства ввода	Logitech
3	Монитор	Мониторы	Дисплеи	Samsung

Видно, что category = 'Периферия' и subcategory = 'Устройства ввода' повторяются. В Snowflake Schema эти атрибуты выносятся в отдельные таблицы.

Аналогия

Star Schema — это рецепт, где все ингредиенты перечислены списком. Snowflake — это рецепт со ссылками: «тесто — см. базовый рецепт на стр. 12». Компактнее, но нужно листать.

Структура Snowflake¶

DDL: нормализованные измерения¶

SQL

-- Справочник категорий
CREATE TABLE dim_category (
    category_key  SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

-- Справочник подкатегорий
CREATE TABLE dim_subcategory (
    subcategory_key SERIAL PRIMARY KEY,
    subcategory_name VARCHAR(100) NOT NULL,
    category_key     INT NOT NULL REFERENCES dim_category(category_key)
);

-- Справочник брендов
CREATE TABLE dim_brand (
    brand_key   SERIAL PRIMARY KEY,
    brand_name  VARCHAR(100) NOT NULL,
    country     VARCHAR(100)
);

-- Измерение товаров (нормализованное)
CREATE TABLE dim_product_sf (
    product_key     SERIAL PRIMARY KEY,
    product_id      INT NOT NULL,
    name            VARCHAR(200) NOT NULL,
    subcategory_key INT REFERENCES dim_subcategory(subcategory_key),
    brand_key       INT REFERENCES dim_brand(brand_key),
    unit_price      NUMERIC(10,2)
);

Загрузка данных¶

SQL

INSERT INTO dim_category (category_key, category_name)
VALUES (1, 'Периферия'), (2, 'Мониторы'), (3, 'Аксессуары');

INSERT INTO dim_subcategory (subcategory_key, subcategory_name, category_key)
VALUES (1, 'Устройства ввода', 1), (2, 'Дисплеи', 2), (3, 'Кабели', 3);

INSERT INTO dim_brand (brand_key, brand_name, country)
VALUES (1, 'Logitech', 'Швейцария'), (2, 'Samsung', 'Южная Корея');

INSERT INTO dim_product_sf (product_key, product_id, name, subcategory_key,
                            brand_key, unit_price)
VALUES
    (1, 1, 'Клавиатура',   1, 1, 49.90),
    (2, 2, 'Мышь',         1, 1, 19.90),
    (3, 3, 'Монитор',      2, 2, 199.00),
    (4, 4, 'Кабель USB-C', 3, NULL, 9.90);

Запросы: Star vs Snowflake¶

Star Schema — один JOIN¶

SQL

-- Продажи по категориям (Star Schema)
SELECT
    p.category,
    SUM(f.total_amount) AS revenue
FROM fact_sales  f
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.category;

Snowflake Schema — цепочка JOIN¶

SQL

-- Продажи по категориям (Snowflake Schema)
SELECT
    c.category_name,
    SUM(f.total_amount) AS revenue
FROM fact_sales       f
JOIN dim_product_sf   p  ON f.product_key     = p.product_key
JOIN dim_subcategory  sc ON p.subcategory_key  = sc.subcategory_key
JOIN dim_category     c  ON sc.category_key    = c.category_key
GROUP BY c.category_name;

Результат одинаковый, но во втором случае нужно три JOIN вместо одного.

Запрос с фильтром по бренду и стране¶

SQL

-- Продажи швейцарских брендов
SELECT
    b.brand_name,
    b.country,
    SUM(f.total_amount) AS revenue
FROM fact_sales      f
JOIN dim_product_sf  p ON f.product_key = p.product_key
JOIN dim_brand       b ON p.brand_key   = b.brand_key
WHERE b.country = 'Швейцария'
GROUP BY b.brand_name, b.country;

Когда Snowflake оправдан¶

Критерий	Star Schema	Snowflake Schema
Простота запросов	Проще — меньше JOIN	Сложнее — цепочки JOIN
Экономия места	Данные дублируются	Меньше дублирования
BI-инструменты	Работают «из коробки»	Могут потребовать настройки
Обновление справочников	Обновить все строки измерения	Обновить одну строку справочника
Производительность	Быстрее на чтение	Больше JOIN = больше работы

На практике

Большинство современных хранилищ (Snowflake DWH, BigQuery, ClickHouse) оптимизированы под денормализованные структуры. Star Schema обычно предпочтительнее. Snowflake Schema применяют, когда справочники большие и часто обновляются — например, каталог из 100 000 товаров с иерархией из 5 уровней.

Гибридный подход¶

На практике редко используют чистую Star или чистую Snowflake. Чаще — гибрид:

Основные измерения (клиент, дата) — денормализованные (Star)
Справочники с глубокой иерархией (география, товарная категория) — нормализованные (Snowflake)

SQL

-- Гибрид: dim_customer — плоская, dim_product — со ссылкой на категорию
SELECT
    c.segment,
    cat.category_name,
    SUM(f.total_amount) AS revenue
FROM fact_sales      f
JOIN dim_customer    c   ON f.customer_key    = c.customer_key    -- Star
JOIN dim_product_sf  p   ON f.product_key     = p.product_key     -- Snowflake
JOIN dim_subcategory sc  ON p.subcategory_key  = sc.subcategory_key
JOIN dim_category    cat ON sc.category_key    = cat.category_key
GROUP BY c.segment, cat.category_name;

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

Snowflake Schema = Star Schema + нормализация измерений
Измерения разбиваются на связанные таблицы-справочники
Экономит место, упрощает обновление справочников
Но увеличивает количество JOIN и усложняет запросы
На практике чаще используют гибрид: плоские измерения + нормализованные справочники
Для BI-инструментов Star Schema обычно удобнее