Dimensional modeling: star schema, факты, измерения и SCD

Моделирование для OLTPСкоро стремится к нормализации: убрать дублирование, защитить от аномалий обновления. Аналитике нужно другое — читать много, соединять мало, отвечать на «сколько, когда, в разрезе чего». Dimensional modeling ставит в центр факты — измеримые события вроде продажи, — а вокруг них измерения: кто, что, когда, где. Звезда выглядит простой. Ошибки в ней тоже простые, но тихие: отчёт строится, сумма похожа на правду, и только сверка с правильным ответом показывает, что треть выручки посчитана дважды или приписана не тому региону.

Эта статья показывает такие ошибки счётом. Генератор строит магазин за 14 дней с событиями, которые ломают наивную модель: клиенты переезжают между регионами, товары принадлежат категориям, склад меняет остатки, один идентификатор клиента переходит к другому человеку, один клиент делает заказы раньше, чем появляется в системе. Правильный ответ генератор считает по своей полной истории; звезда строится dbt в PostgreSQL, и каждый отчёт сверяется с эталоном.

Часть серии «Data engineering на практике». Аналитический двойник OLTP-моделированияСкоро; загрузка, на которой стоит хранилище, — в статье про батч-пайплайны.

В статье

• Стенд
• История измерения: SCD1, SCD2, SCD3
• Гранулярность факта
• Какие меры можно складывать
• Ключи: поздний клиент и переиспользованный id
• Как выбирать
• Чего эти результаты не значат

Стенд

PostgreSQL 18, база dwh: схема app — источник, как его отдаёт реплика приложения, marts — звезда. dbt-core 1.12 с dbt-postgres 1.11 строит измерения клиента в трёх вариантах, измерение товара, два факта — заказа и позиции — и периодический снимок склада. Источник, как и настоящая OLTP-база, знает только текущее состояние клиента: регион и время последнего изменения. Историю строит хранилище, и делать это ему приходится день за днём: для каждого из 14 дней генератор выставляет состояние источника на конец дня, а dbt снимает его.

-- dbt/snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}
{{ config(target_schema='snapshots', unique_key='id', strategy='timestamp', updated_at='updated_at') }}
select * from {{ source('app', 'customers') }}
{% endsnapshot %}

Если снимать не каждый день, история будет грубее — изменения между снимками сольются в одно. Это свойство любого SCD2 поверх источника без истории, а не только dbt.

История измерения: SCD1, SCD2, SCD3

Клиент живёт в регионе «север», 5.09 переезжает на «юг». Его заказы до переезда — выручка севера или юга? Для отчёта «где мы продаём» — севера: там была продажа. Для отчёта «где наши клиенты сейчас» — юга. Модель измерения решает, на какой из вопросов вообще можно ответить.

• SCD1 перезаписывает регион: в измерении только текущее значение.
• SCD2 добавляет версию строки с периодом действия; факт ссылается на версию, действовавшую в момент заказа.
• SCD3 хранит текущий регион и предыдущий в одной строке.

Постановка. 40 клиентов переезжают: 30 один раз, 10 дважды. Отчёты — выручка по региону на момент заказа и по текущему региону человека. Факт заказа хранит ключ версии клиента, найденный по времени заказа:

-- dbt/models/marts/fct_order.sql
left join {{ ref('dim_customer_scd2') }} d
  on d.customer_id = o.customer_id and o.created_at >= d.valid_from and o.created_at < d.valid_to

Контроль сошёлся: SCD2 по версии на момент заказа совпадает с эталоном до копейки.

$ python3 scripts/run-scd.py asof-scd1
{"variant": "asof-scd1", "outcome": "mismatch", "detail": "отклонение итогов по регионам 13631840 из 470204870 (2.90%); заказов не в том регионе 451 на 55935320 (11.90%)"}
$ python3 scripts/run-scd.py asof-scd2
{"variant": "asof-scd2", "outcome": "match", "detail": "отклонение итогов по регионам 0 из 470204870 (0.00%); заказов не в том регионе 0 на 0 (0.00%)"}

Перезапись — не мелочь, и таблица показывает её двумя числами. Отклонение итогов по регионам — почти 3%: на столько разошлись региональные суммы с эталоном. Но по заказам не своему региону приписано 451 заказ — 11,90% выручки: клиенты переезжали в обе стороны, и встречные переносы в итогах погасили друг друга. Сверка итогов по регионам эту ошибку почти прячет — урок не только про SCD, но и про то, как агрегат маскирует ошибку атрибуции. На уровне всей компании ошибка не видна вовсе, на уровне региона это рост или падение, которых не было. SCD3 на этот вопрос отвечает так же плохо: его «текущий регион» — тот же SCD1, а «предыдущий» нельзя привязать к дате заказа — в строке нет момента смены.

Отчёт по текущему региону ошибается у обеих моделей одинаково и по причине, которая не связана с SCD: идентификатор клиента 77 с 7.09 принадлежит другому человеку. По бизнес-ключу — и через SCD1, и через текущую версию SCD2 — заказы прежнего владельца уходят в регион нового. Об этом — в разделе про ключи.

Ограничение SCD3 видно отдельно:

«Предыдущий регион» — это один шаг назад. У тех, кто переезжал дважды, регион до первого переезда потерян: на его место записан промежуточный.

Гранулярность факта

Гранулярность — что именно представляет одна строка факта. Факт заказа: одна строка — один заказ с его суммой. Факт позиции: одна строка — одна позиция заказа. Это решение принимают один раз, и почти любой отчёт потом зависит от того, не соединили ли факт с чем-то более мелким.

Постановка. Выручка по категории товара. Категория есть у позиции, а не у заказа: заказ из книги и игрушки относится к двум категориям. Правильный запрос идёт по факту позиции. Неправильный — тот, что напрашивается, когда под рукой факт заказа: соединить его с позициями, чтобы получить категорию, и сложить сумму заказа.

Контроль сошёлся: запросы на своей гранулярности совпадают с эталоном.

$ python3 scripts/run-grain.py category-order
{"variant": "category-order", "outcome": "mismatch", "detail": "итог 1412840240 против эталона 470776260 (×3.001)"}

Соединение с позициями повторяет сумму заказа столько раз, сколько в нём позиций. Завышение здесь — втрое, а не в 2,5 раза, как среднее число позиций: заказы с большим числом позиций и стоят дороже, и именно они повторяются чаще. Ошибка того же рода — count(*) по факту позиции вместо числа заказов. Защита одна: каждый отчёт строится на факте той гранулярности, на которой живёт разрез, и тест модели проверяет уникальность ключа гранулы.

Какие меры можно складывать

Мера аддитивна, если её можно складывать по всем измерениям: выручку по дням, регионам, товарам. Полуаддитивна, если по одним можно, по другим нет: остаток на складе складывается по товарам, но не по дням. Неаддитивна — доли и средние: их нельзя складывать вообще, а среднее долей — не доля целого.

Постановка. Периодический снимок склада: остаток каждого товара на конец дня. Отчёт «сколько товара на складе за неделю 08–14.09» и «доля отменённых заказов за период».

Контроль сошёлся: остаток последнего дня и доля «отменённых из всех» совпадают с эталоном.

Сумма остатков по дням — в семь с лишним раз больше остатка. Это не ошибка счёта, это бессмысленная величина, и в BI её получают одним кликом: агрегат «сумма» по умолчанию. Для полуаддитивной меры по времени берут последнее значение периода или среднее — в зависимости от вопроса. Среднее дневных долей отличается от доли за период мало, потому что дни похожи по размеру; при дне со ста заказами и дне с тремя разница будет любой.

Ключи: поздний клиент и переиспользованный id

Факт ссылается на измерение ключом. Бизнес-ключ — тот, что пришёл из источника, — удобен, но живёт по правилам источника. Суррогатный ключ версии — по правилам хранилища.

Постановка. Клиент 201 сделал 5 заказов 10.09, а запись о нём появилась в источнике только 11.09 — обычная история для данных, приходящих из разных систем. На момент заказа версии клиента нет. Три способа соединить факт с клиентом:

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

$ python3 scripts/run-keys.py late-inner
{"variant": "late-inner", "outcome": "mismatch", "detail": "потеряно выручки 571390; заказов позднего клиента в отчёте 0 из 5, регион —"}
$ python3 scripts/run-keys.py late-reassign
{"variant": "late-reassign", "outcome": "match", "detail": "потеряно выручки 0; заказов позднего клиента в отчёте 5 из 5, регион center"}

Внутреннее соединение молча выбрасывает факты, для которых нет строки измерения: выручка пропала, и ни один запрос не скажет, что её было больше. Левое соединение с «неизвестным участником» сохраняет итог — заказы видны, но без региона. Это правильное промежуточное состояние. Когда клиент появится, факт переназначают на его первую версию — и здесь есть допущение: первая известная версия описывает состояние на момент заказа. На стенде генератор так и задумал, поэтому регион «верен». В рабочей системе клиент мог сменить регион между заказом и приходом записи о нём; тогда нужна бизнес-дата, с которой действует атрибут, а до её появления факт честнее оставить на unknown.

Переиспользованный идентификатор — обратная ошибка. Клиент 77 удалён 7.09, его id получил новый человек. Отчёт «заказы клиента» через SCD1 по бизнес-ключу приписывает новому владельцу 18 заказов, из которых его — 8. SCD2 с версией на момент заказа разделяет их: новая версия началась 7.09, прежние заказы ссылаются на прежнюю. Но отчёт по бизнес-ключу и через SCD2 сольёт обоих — это показала строка «текущий регион» выше. Если источник может переиспользовать идентификаторы, хранилищу нужен свой устойчивый ключ человека, а не только версии.

Как выбирать

Чего эти результаты не значат

• Ничего о скорости. 14 дней, ~4 тысячи заказов; звезда здесь — про правильность отчётов, а не про производительность колоночных хранилищ (о ней — статья про ClickHouse).
• События подобраны. Доля переехавших, число позиций в заказе и размер дней задают величину отклонений; на других данных проценты будут другими, направление — тем же.
• Снимки ежедневные. SCD2 видит изменения с точностью до снимка; изменения между снимками сливаются.
• Версии: dbt-core 1.12.5, dbt-postgres 1.11.0, PostgreSQL 18.6.

Ссылки

• Стенд: digital-cookbook/data-engineering/dimensional — четыре оси, генератор с эталоном.
• dbt: снапшоты
• Kimball Group: техники моделирования измерений