Зачем dimensional modeling

В модуле 7 мы видели: современные DWH архитектуры состоят из слоёв (raw -> staging -> core -> marts). Слой marts — это где данные оптимизированы под analytics и BI. И стандартный способ организовать marts — это dimensional modeling.

Dimensional modeling — это подход Ральфа Кимбалла (Ralph Kimball), описанный в “The Data Warehouse Toolkit” (1996, постоянные переиздания). За 30 лет это стало индустриальным стандартом для аналитических moделей.

Идея простая: разделить данные на два типа — fact и dimension — и строить из них star schemas (один fact + несколько dimensions). В этом уроке разберём fact и dimension по отдельности. В следующих — star schema, snowflake, SCD, и процесс проектирования.

Fact: то, что произошло

Fact table — это таблица, описывающая бизнес-события или измерения процессов. Каждая строка — одно событие, и оно содержит:

• Внешние ключи (foreign keys) к dimension-таблицам (когда, кто, что, где).
• Числовые меры (measures) — то, что можно сложить, посчитать, агрегировать.

Примеры fact-таблиц:

• fact_orders — заказы. Строка = один заказ. Меры: amount, qty, discount.
• fact_page_views — просмотры страниц. Строка = один просмотр. Меры: time_on_page, scroll_depth.
• fact_transactions — платежи. Строка = одна транзакция. Меры: amount.
• fact_inventory_snapshot — остатки на складе на каждый день. Строка = состояние одного SKU в один день. Меры: qty_on_hand, qty_reserved.

CREATE TABLE marts.fact_orders (
  -- Surrogate primary key
  order_key BIGINT PRIMARY KEY,

  -- Foreign keys to dimensions
  customer_key BIGINT,    -- кто
  product_key BIGINT,     -- что
  date_key INT,           -- когда (YYYYMMDD format)
  store_key BIGINT,       -- где
  promotion_key BIGINT,   -- через какую акцию

  -- Measures (numeric, additive)
  qty INT,
  unit_price NUMERIC(10, 2),
  total_amount NUMERIC(12, 2),
  discount_amount NUMERIC(10, 2),
  net_amount NUMERIC(12, 2)
);

Fact-таблицы обычно большие (миллионы-миллиарды строк) и узкие (10-30 колонок). Все non-key колонки — числа, готовые к агрегации.

Dimension: контекст

Dimension table — это таблица с описательным контекстом. Кто такой клиент, что за продукт, какая дата (день недели, месяц, праздник?). Строки в dimension-таблицах описывают сущности, а fact-таблицы их используют через foreign keys.

Примеры:

• dim_customer — клиенты. Атрибуты: имя, email, страна, segment, signup_date.
• dim_product — товары. Атрибуты: name, SKU, category, brand, price.
• dim_date — календарь. Атрибуты: full_date, year, quarter, month, day_of_week, is_holiday.
• dim_store — магазины. Атрибуты: store_name, city, country, manager.

CREATE TABLE marts.dim_customer (
  -- Surrogate key
  customer_key BIGINT PRIMARY KEY,

  -- Natural key (from source system)
  customer_id BIGINT,

  -- Descriptive attributes
  email TEXT,
  name TEXT,
  country TEXT,
  signup_date DATE,
  customer_segment TEXT,  -- 'premium', 'standard', 'churned'
  age_band TEXT,          -- '18-24', '25-34', ...
  is_active BOOLEAN,

  -- For SCD Type 2 (lesson 4)
  valid_from DATE,
  valid_to DATE,
  is_current BOOLEAN
);

Dimension-таблицы обычно маленькие (тысячи-миллионы строк) и широкие (десятки колонок). Все колонки — атрибуты для slicing и filtering.

Grain (грейн): что такое строка

Grain (грейн) — это уровень детализации fact-таблицы. Это первое и главное design decision при проектировании fact-таблицы. Если грейн неправильный — модель не работает.

Грейн ответ на вопрос: “Что представляет одна строка в этой таблице?”

Примеры разных грейнов для заказов:

• Grain = order: одна строка = один заказ. Меры: total_order_amount, items_count.
• Grain = order line item: одна строка = один товар в заказе. Меры: line_amount, qty, unit_price.
• Grain = daily store snapshot: одна строка = (store, day). Меры: daily_revenue, orders_count.
• Grain = hourly aggregation: одна строка = (date, hour). Меры: revenue_per_hour.

Каждый грейн отвечает на разные вопросы. Order grain — “сколько заказов?”. Line item grain — “какие товары больше продавали?”. Hourly — “в какие часы пиковые продажи?”.

Правило 1. Один грейн = одна fact-таблица. Не смешивай детальные events и aggregations в одной таблице.

Правило 2. Выбирай самый низкий доступный грейн. Из детального можно получить агрегацию, из агрегированного нельзя восстановить детали.

Правило 3. Грейн объясняется одной фразой. Если не можешь объяснить “одна строка = …” — модель неясная.

Типы fact-таблиц

В практике Кимбалла выделяют четыре типа fact-таблиц.

1. Transactional Fact (наиболее частый)

Одна строка = одно бизнес-событие в момент времени. Заказы, платежи, клики, события.

fact_orders (order_key, customer_key, product_key, date_key, qty, amount, ...)

Append-only обычно. Каждый день добавляются новые строки, старые не меняются. Это 80%+ fact-таблиц в типичном DWH.

2. Periodic Snapshot Fact

Одна строка = состояние процесса в фиксированный момент времени (день/неделя/месяц). Часто для inventory, account balances, KPI snapshots.

fact_inventory_daily (date_key, product_key, store_key, qty_on_hand, qty_reserved)

Каждый день создаются строки для всех (product, store) комбинаций. Размер растёт линейно. Используется когда нужно “что было в момент X” для каждого временного среза.

3. Accumulating Snapshot Fact

Одна строка = жизненный цикл одного процесса с несколькими стадиями. Каждая стадия добавляет колонки.

fact_order_lifecycle (
  order_key,
  customer_key,
  order_date_key,
  paid_date_key,
  shipped_date_key,
  delivered_date_key,
  returned_date_key,  -- NULL если не вернули
  -- метрики цикла
  payment_lag_days,
  shipping_lag_days,
  total_cycle_days
)

Строка обновляется по мере прохождения стадий. Полезно для funnel analytics, lifecycle analysis.

4. Factless Fact

Fact-таблица без числовых мер — только foreign keys. Звучит странно, но полезно для “что произошло” событий, где count важнее value.

fact_student_attendance (date_key, student_key, course_key)
-- строка существует = студент был на лекции; ничего больше не нужно

Запрос “сколько студентов посетило курс X в марте”: COUNT(*) WHERE course_key = X AND month = March.

Surrogate vs Natural keys

В fact-таблицах foreign keys ведут к dimensions. Здесь важное design decision — использовать surrogate или natural keys.

Natural key (NK) — это ID из исходной системы. customer_id = 42 пришло из Postgres. product_sku = 'ABC-123' из catalog.

Surrogate key (SK) — это синтетический integer ID, генерируемый DWH. customer_key = 1 — это первый клиент по порядку загрузки в DWH.

Почему предпочитают surrogate keys:

1. SCD Type 2. Когда атрибут клиента меняется (новая country), мы создаём новую строку dim_customer с новым customer_key. Natural key (customer_id) остаётся тем же. Это позволяет fact-таблицам указывать на конкретную версию dimension.

2. Performance. Integer JOIN быстрее, чем string или composite key JOIN.

3. Stability. Если source меняет ID format (например, переход на UUID) — surrogate key защищает downstream.

4. Integration. Если customer пришёл из Postgres и Salesforce с разными natural keys, мы можем дать ему один surrogate key.

CREATE TABLE dim_customer (
  customer_key BIGINT PRIMARY KEY,    -- surrogate (synthetic)
  customer_id BIGINT NOT NULL,         -- natural (from Postgres)
  email TEXT,
  ...
  valid_from DATE,
  valid_to DATE,
  is_current BOOLEAN
);

CREATE TABLE fact_orders (
  order_key BIGINT PRIMARY KEY,
  customer_key BIGINT,                 -- points to specific version of dim_customer
  product_key BIGINT,
  ...
);

Атрибуты dimension: широкий и денормализованный

Dimension-таблица обычно wide — много атрибутов. Каждый атрибут полезен для slicing/filtering.

-- dim_product с полной денормализацией (Kimball style)
CREATE TABLE dim_product (
  product_key BIGINT PRIMARY KEY,
  product_id BIGINT,
  sku TEXT,
  product_name TEXT,
  product_description TEXT,

  -- Иерархия категорий — денормализована inline
  category_name TEXT,
  subcategory_name TEXT,
  department_name TEXT,

  -- Brand info
  brand_name TEXT,
  brand_country TEXT,

  -- Pricing tiers
  price NUMERIC(10, 2),
  price_band TEXT,        -- 'low', 'medium', 'premium'

  -- Boolean flags для быстрых фильтров
  is_seasonal BOOLEAN,
  is_eco_friendly BOOLEAN,

  -- Pre-computed измерения
  weight_kg NUMERIC(8, 2),
  weight_band TEXT,       -- 'light', 'medium', 'heavy'

  -- SCD
  valid_from DATE,
  valid_to DATE,
  is_current BOOLEAN
);

Денормализация — это намеренный design. Иерархия categories (category -> subcategory -> department) inline. Никаких FK к dim_category. Это убирает один JOIN при запросе.

Цена — обновления. Если category переименовали, нужно обновить миллион строк dim_product. Но это происходит редко и батчем.

Real пример: query

С star schema запрос для BI становится простым:

SELECT
  c.country,
  p.category_name,
  d.year,
  d.month_name,
  SUM(f.net_amount) AS revenue,
  COUNT(*) AS orders_count,
  COUNT(DISTINCT f.customer_key) AS unique_customers
FROM marts.fact_orders f
JOIN marts.dim_customer c ON f.customer_key = c.customer_key
JOIN marts.dim_product p ON f.product_key = p.product_key
JOIN marts.dim_date d ON f.date_key = d.date_key
WHERE d.year = 2026
  AND p.brand_name = 'Nike'
GROUP BY 1, 2, 3, 4
ORDER BY revenue DESC;

Это естественный SQL для аналитика. Три JOIN, чёткая структура. BI tools (Tableau, Looker) генерируют такой SQL автоматически из drag-and-drop UI.

Аддитивность мер

Важная concept — аддитивность measure-ов. Меры в fact-таблице бывают трёх типов:

1. Fully additive — можно сложить по любому dimension. qty, amount — сумма по дню, по клиенту, по продукту, по магазину всегда даёт правильный результат.

2. Semi-additive — можно сложить по некоторым dimensions, не по другим. inventory_qty_on_hand (остаток на складе) — можно сложить по складам (общий остаток по компании), но нельзя по дням (сумма остатков за 7 дней не имеет смысла). По дням берётся среднее или последнее значение.

3. Non-additive — нельзя складывать вообще. unit_price, discount_percent — сложение не даёт meaningful результат. Используются в формулах с другими мерами.

-- Правильно: качественная агрегация
SELECT
  d.year, d.month,
  SUM(qty) AS total_qty,              -- additive
  AVG(inventory_on_hand) AS avg_stock,  -- semi-additive: AVG по дням
  SUM(amount) / SUM(qty) AS avg_unit_price  -- non-additive: считается из additive мер
FROM fact_orders
JOIN dim_date d ON ...
GROUP BY 1, 2;

Правильное design fact-таблицы: храним additive меры (qty, amount), non-additive вычисляются на лету. Это упрощает aggregations.

Антипаттерны

Антипаттерн 1: dimensions в fact. fact_orders.customer_email — это атрибут клиента, не measure. Помещение в fact ломает modeling: при изменении email нужно обновить все исторические orders. Решение — email в dim_customer.

Антипаттерн 2: smешанные грейны. Одна fact-таблица содержит и individual orders, и monthly aggregations. Невозможно понять, что значит SUM(qty). Решение — отдельные fact для каждого грейна.

Антипаттерн 3: text в fact. fact_orders.notes — длинный текст. Раздувает fact, не используется в агрегациях. Решение — text в dim_order_details или отдельной таблице.

Антипаттерн 4: множественные time dimensions. В fact одновременно order_date_key, paid_date_key, shipped_date_key — это role-playing dimension, нормально. Но каждое смотрит в один и тот же dim_date — корректно. Если же три разных date dimension (отдельные таблицы) — это антипаттерн.

Попробуй сам

1. Спроектируй fact и dimensions для блог-платформы: посты, авторы, комментарии, page views, теги. Что fact, что dimension? Какие грейны?
2. Возьми любую публичную dataset (например, NYC Taxi). Преобразуй в fact (trip events) + dimensions (date, location, vehicle_type). Реализуй на DuckDB.
3. Найди в открытом dbt project (dbt-jaffle-shop, dbt-mta) marts/ структуру. Идентифицируй fact и dimension таблицы. Посмотри их грейн.
4. Рассмотри inventory snapshot use case: магазин хочет видеть остатки на каждый день. Спроектируй periodic snapshot fact. Какие меры additive, какие нет?