Periodic snapshot: одна строка на сущность за период
Прошлый урок разобрал transaction fact table — строку на каждое событие. Этот тип отлично отвечает на вопрос «какие события происходили». Но есть класс вопросов, на который он отвечает плохо: вопросы про состояние. «Каков остаток на складе на конец каждого дня?» «Какой баланс счёта на конец месяца?» «Сколько активных подписок было на конец недели?»
Для таких вопросов Кимбалл вводит второй тип — periodic snapshot fact table (таблица фактов периодических снимков). Этот урок объясняет, что это за тип, в чём его зерно, чем он отличается от транзакционного и почему его measures ведут себя особым образом при суммировании.
Зерно: сущность за регулярный период
Зерно periodic snapshot формулируется так: одна строка — это состояние одной сущности за один регулярный интервал времени.
Ключевые слова — «состояние», «регулярный интервал». Транзакционная таблица записывала событие; периодический снимок записывает не событие, а снимок состояния — каким было положение дел на конец периода. И делает это регулярно: каждый день, каждую неделю, каждый месяц — по расписанию, независимо от того, происходили события или нет.
Классический пример — ежедневный снимок остатков на складе:
-- Periodic snapshot: grain = остаток одного товара на одном складе на конец одного дня
CREATE TABLE fct_inventory_daily (
-- foreign keys: какой день, какой товар, какой склад
date_key INT NOT NULL REFERENCES dim_date(date_key),
product_key INT NOT NULL REFERENCES dim_product(product_key),
warehouse_key INT NOT NULL REFERENCES dim_warehouse(warehouse_key),
-- measures: состояние на конец дня
quantity_on_hand INT NOT NULL, -- остаток штук
inventory_value NUMERIC(14,2) NOT NULL, -- стоимость остатка
units_received INT NOT NULL, -- поступило за день
units_shipped INT NOT NULL -- отгружено за день
);Каждая строка fct_inventory_daily говорит: «на конец такого-то дня на таком-то складе было столько-то штук такого-то товара на такую-то сумму». Снимок снимается каждый день — для каждой пары «товар-склад» появляется по одной строке за каждый день, всегда.
Плотная, а не разреженная
Главное отличие periodic snapshot от transaction fact table — плотность. Транзакционная таблица разрежена: строка только там, где было событие. Периодический снимок плотный (dense): строка есть для каждой сущности за каждый период, всегда — независимо от того, происходило ли что-нибудь.
Разберём на примере. Товар лежит на складе неделю, и за эту неделю с ним ничего не происходило — ни поступлений, ни отгрузок. В транзакционной таблице движений за эту неделю не было бы ни одной строки. В периодическом снимке за эту неделю будет семь строк — по одной на каждый день, и в каждой quantity_on_hand показывает остаток, units_received = 0, units_shipped = 0. Состояние «лежит и ничего не происходит» — это тоже состояние, и снимок его фиксирует.
Отсюда и название «плотная»: таблица заполнена сплошь, для каждого сочетания «сущность-период» строка есть гарантированно. Это даёт предсказуемость размера: число строк periodic snapshot равно числу сущностей умножить на число периодов. 50 000 товаров на 10 складах за 365 дней — это 50000 x 10 x 365 примерно 182 млн строк в год, и это число известно заранее, оно не зависит от активности.
Плотность — это и сила, и цена. Сила: на вопрос про состояние снимок отвечает мгновенно и единообразно. «Остаток товара X на складе Y на дату Z» — это одна строка по трём ключам, прямой доступ. «Лежит и не двигается» в снимке видно так же явно, как и активное движение. Цена: снимок хранит строки даже там, где ничего не менялось, — место расходуется на «состояние покоя».
Semi-additive measures: остаток нельзя складывать по времени
У periodic snapshot есть особенность, которую обязательно понимать: его measures состояния — semi-additive (полуаддитивные). Это значит, что их можно суммировать по одним разрезам, но нельзя суммировать по времени.
Разберём на остатке. quantity_on_hand — остаток на конец дня. Можно ли его суммировать?
По разрезу «склад» — можно. «Сколько всего штук товара X на конец 4 июля по всем складам» — это SUM(quantity_on_hand) по складам за один день. Корректно: складываем остатки разных мест на один момент.
По разрезу «время» — нельзя. «Сколько штук товара X было за неделю» через SUM(quantity_on_hand) по семи дням — бессмысленно. Если на складе всю неделю лежало 100 штук, такая сумма даст 700 — но 700 штук не существует, это одни и те же 100 штук, посчитанные семь раз. Остаток — это не поток, который накапливается, а уровень, который держится. Складывать уровни за разные моменты времени нельзя.
Что с остатком по времени делать корректно: брать значение на конкретный момент (остаток на конец недели — это строка последнего дня) или усреднять (AVG(quantity_on_hand) — средний остаток за неделю). Сумма по времени — запрещена; последнее значение или среднее — допустимы.
-- ПРАВИЛЬНО: суммируем остаток по складам на ОДИН день
SELECT SUM(quantity_on_hand) AS total_on_hand
FROM fct_inventory_daily
WHERE product_key = 4471 AND date_key = 20250704;
-- total_on_hand | 100 (остатки всех складов на этот день — корректно)
-- НЕПРАВИЛЬНО: суммируем остаток по дням недели
SELECT SUM(quantity_on_hand) AS wrong
FROM fct_inventory_daily
WHERE product_key = 4471 AND warehouse_key = 7
AND date_key BETWEEN 20250701 AND 20250707;
-- wrong | 700 (БЕССМЫСЛЕННО: те же 100 штук посчитаны 7 раз)
-- ПРАВИЛЬНО для времени: средний остаток за неделю
SELECT AVG(quantity_on_hand) AS avg_on_hand
FROM fct_inventory_daily
WHERE product_key = 4471 AND warehouse_key = 7
AND date_key BETWEEN 20250701 AND 20250707;
-- avg_on_hand | 100 (средний остаток — корректно)Заметьте при этом: units_received и units_shipped в той же таблице — это потоки за день, и они полностью аддитивны, в том числе по времени. «Сколько поступило за неделю» — это честная SUM(units_received) по семи дням. В одной снимок-таблице могут жить и semi-additive measures состояния (остаток), и additive measures потоков (поступило, отгружено). Подробная классификация additive / semi-additive / non-additive — отдельный урок этого модуля; здесь главное запомнить: measures состояния в periodic snapshot не складываются по времени.
Складывание остатков, балансов и других уровней по времени — одна из самых частых ошибок в аналитике. SUM по такому measure через несколько дат не падает, а тихо возвращает завышенное в разы число. Признак semi-additive measure — он отвечает на вопрос «сколько ЕСТЬ сейчас», а не «сколько произошло». «Есть» по времени не складывается; «произошло» складывается.
Transaction и periodic snapshot — дополняют друг друга
Важно понять: периодический снимок не заменяет транзакционную таблицу — они дополняют друг друга, отвечая на разные вопросы.
Тот же склад можно (и часто нужно) моделировать обоими типами сразу. fct_inventory_movements — транзакционная: строка на каждое движение (поступление, отгрузка), отвечает на «какие движения товара были, по какой причине, кем». fct_inventory_daily — периодический снимок: строка на остаток каждого товара на конец каждого дня, отвечает на «каков был остаток на дату».
Можно ли посчитать остаток из одной транзакционной таблицы? Теоретически да — просуммировать все поступления минус все отгрузки от начала времён до нужной даты. Но это дорого (скан всей истории движений на каждый запрос остатка) и неудобно. Периодический снимок предвычисляет это состояние и хранит готовым — остаток на любую дату достаётся одной строкой. Снимок — это, по сути, материализованное, регулярно обновляемое состояние, чтобы не пересчитывать его из потока событий каждый раз.
| Свойство | Transaction fact table | Periodic snapshot fact table |
|---|---|---|
| Зерно | одно событие | состояние сущности за период |
| Что записывает | событие произошло | снимок положения дел |
| Плотность | разрежена (строка на событие) | плотная (строка на сущность за период) |
| Размер | зависит от числа событий | сущности умножить на периоды, известен заранее |
| Когда появляется строка | когда случилось событие | каждый период, по расписанию |
| Отвечает на вопрос | какие события происходили | каким было состояние на момент |
| Measures состояния | — | semi-additive (не складываются по времени) |
Где periodic snapshot — правильный выбор
Periodic snapshot подходит, когда бизнес-вопрос про состояние на регулярные моменты времени, а не про отдельные события:
- Остатки на складе на конец каждого дня.
- Баланс счёта на конец каждого дня или месяца.
- Число активных подписок / пользователей на конец каждой недели.
- Стоимость портфеля на конец каждого торгового дня.
- Заполненность отеля на каждую ночь.
Общее: измеряется уровень, который существует и держится во времени, и его снимают регулярно. Если же вопрос про дискретные события («какие платежи прошли») — это transaction fact table. Если про прохождение процесса через вехи — accumulating snapshot, следующий урок.
Попробуй сам
Возьмите банковское приложение. Нужна аналитика по счетам клиентов.
Спроектируйте periodic snapshot fct_account_balance_daily. Сформулируйте зерно одним предложением. Выпишите foreign keys (день, счёт, клиент, тип счёта) и measures. Среди measures намеренно сделайте и состояние (баланс на конец дня), и потоки (сумма поступлений за день, сумма списаний за день).
Затем разберитесь с аддитивностью. Для каждого вашего measure ответьте: складывается ли он по разрезу «клиент» (баланс всех счетов одного клиента), складывается ли по разрезу «время» (за месяц). Напишите два SQL-запроса: один правильный (суммирует баланс по счетам клиента на один день) и один неправильный (суммирует баланс по дням месяца) — и посчитайте, во сколько раз и почему завышен результат неправильного. Наконец, объясните: почему банку нужны и periodic snapshot балансов, и transaction fact table отдельных операций — на какой вопрос отвечает каждая таблица?