Accumulating snapshot: одна строка на процесс
Два прошлых урока разобрали transaction fact table (строка на событие) и periodic snapshot (строка на состояние за период). Остался третий и последний тип — accumulating snapshot fact table (таблица фактов накапливающихся снимков). Он самый необычный из трёх, потому что нарушает свойство, которое казалось незыблемым: его строки обновляются.
Accumulating snapshot создан для одного конкретного класса задач — анализа процессов с чётко определёнными вехами. Заказ проходит путь: создан -> оплачен -> собран -> отгружен -> доставлен. Этот урок объясняет, что это за тип, почему его строка обновляется, какие у него особые measures и чем он принципиально отличается от первых двух типов.
Зерно: один экземпляр процесса
Зерно accumulating snapshot формулируется так: одна строка — это один экземпляр процесса (workflow), проходящего через предсказуемую последовательность вех (milestones).
Ключевое слово — процесс с вехами. Это не одно событие (как в transaction) и не состояние на дату (как в periodic snapshot). Это сущность, которая проживает многошаговый путь, и шаги известны заранее. Примеры таких процессов:
- Заказ: создан -> оплачен -> собран на складе -> отгружен -> доставлен.
- Заявка на кредит: подана -> проверена -> одобрена -> выдана.
- Производство: запущено -> детали получены -> собрано -> проверено -> отправлено.
- Обработка страхового случая: заявлен -> назначен эксперт -> оценён -> выплачен.
У всех общая черта: фиксированный набор вех, через которые экземпляр проходит по порядку. Accumulating snapshot моделирует именно такой пайплайн.
Строка обновляется по мере прохождения вех
Вот что делает этот тип уникальным. В transaction fact table строка пишется один раз и неизменна. В periodic snapshot каждый период добавляется новая строка. В accumulating snapshot одна строка живёт весь срок жизни процесса и обновляется каждый раз, когда экземпляр проходит очередную веху.
Посмотрим на структуру fact-таблицы заказов:
-- Accumulating snapshot: grain = один заказ за весь его жизненный цикл
CREATE TABLE fct_order_pipeline (
order_number VARCHAR(20) PRIMARY KEY, -- один экземпляр процесса
customer_key INT NOT NULL REFERENCES dim_customer(customer_key),
-- по одному date-foreign-key на КАЖДУЮ веху процесса
created_date_key INT NOT NULL REFERENCES dim_date(date_key),
paid_date_key INT REFERENCES dim_date(date_key),
picked_date_key INT REFERENCES dim_date(date_key),
shipped_date_key INT REFERENCES dim_date(date_key),
delivered_date_key INT REFERENCES dim_date(date_key),
-- measures: длительности между вехами (lag measures)
days_create_to_pay INT,
days_pay_to_ship INT,
days_ship_to_deliver INT,
days_total INT,
order_amount NUMERIC(12,2) NOT NULL
);Обратите внимание на две вещи. Первое: по одному date-foreign-key на каждую веху — created_date_key, paid_date_key, picked_date_key, shipped_date_key, delivered_date_key. Второе: все вехи, кроме первой, объявлены nullable — created_date_key обязателен (заказ не существует без создания), остальные могут быть NULL.
Теперь проследим жизнь одной строки.
Заказ создан. В fact-таблицу вставляется строка. created_date_key заполнен; paid_date_key, shipped_date_key, delivered_date_key — пока NULL; длительности — пока NULL. Строка существует, но процесс только начался.
Заказ оплачен (через 2 дня). Та же строка обновляется: проставляется paid_date_key, вычисляется days_create_to_pay = 2. Новая строка не создаётся — обновляется существующая.
Заказ отгружен (ещё через 3 дня). Та же строка снова обновляется: shipped_date_key, days_pay_to_ship = 3.
Заказ доставлен (ещё через 4 дня). Финальное обновление: delivered_date_key, days_ship_to_deliver = 4, days_total = 9. Теперь строка полностью заполнена — процесс завершён.
Одна строка прошла четыре состояния, обновляясь на каждой вехе. NULL-поля постепенно заполнялись по мере прохождения процесса. Это и есть «накапливающийся» снимок: строка накапливает информацию о процессе, пока тот идёт.
Несколько date-foreign-keys и lag measures
У accumulating snapshot две характерные особенности структуры, прямо вытекающие из его задачи.
Много date-foreign-keys — по одному на веху. Transaction fact table обычно имеет один date_key — дату события. Accumulating snapshot имеет date-foreign-key на каждую веху процесса: дату создания, дату оплаты, дату отгрузки, дату доставки. Все они ссылаются на одну и ту же dim_date, но в разных ролях (это пример role-playing dimension — подробнее в модуле про dimension-таблицы). Каждый date-key отвечает на «когда заказ достиг этой вехи».
Lag measures — длительности между вехами. Главная аналитическая ценность accumulating snapshot — измерять, сколько времени процесс проводит между вехами. Поэтому его measures — это days_create_to_pay, days_pay_to_ship, days_ship_to_deliver, days_total. Они называются lag measures (измерения задержки) и вычисляются как разница между датами соседних вех. Именно ради них этот тип и существует: он позволяет одним запросом ответить «какова средняя длительность каждого этапа обработки заказа» и «где в пайплайне узкое место».
-- Анализ длительности этапов пайплайна — то, ради чего нужен этот тип
SELECT
AVG(days_create_to_pay) AS avg_days_to_pay,
AVG(days_pay_to_ship) AS avg_days_to_ship,
AVG(days_ship_to_deliver) AS avg_days_to_deliver,
AVG(days_total) AS avg_total
FROM fct_order_pipeline
WHERE delivered_date_key IS NOT NULL; -- только завершённые заказы
-- avg_days_to_pay | avg_days_to_ship | avg_days_to_deliver | avg_total
-- 1.4 | 3.8 | 2.1 | 7.3Из этого запроса сразу видно: этап «оплата -> отгрузка» (3,8 дня) — самый долгий, вот где узкое место процесса. Такой анализ — родная задача accumulating snapshot, и он даёт ответ из одной строки на заказ, без сложных соединений событий.
NULL в accumulating snapshot — это не «данные потеряны», а «процесс ещё не дошёл до этой вехи». NULL в shipped_date_key означает «заказ пока не отгружен». Это позволяет одним запросом находить «зависшие» экземпляры: WHERE paid_date_key IS NOT NULL AND shipped_date_key IS NULL — заказы, которые оплачены, но застряли перед отгрузкой. NULL здесь несёт смысл состояния процесса.
Полезно осознать ещё одно следствие. В accumulating snapshot текущее состояние процесса не хранится отдельным столбцом — оно читается из картины заполненных и пустых вех. Заполнены created и paid, пусты shipped и delivered — значит заказ сейчас на этапе «оплачен, ждёт сборки и отгрузки». Не нужен отдельный столбец status с текстом «оплачен»: статус выводится из того, до какой вехи дошли даты. Это удобно — статус не может рассинхронизироваться с датами вех, потому что он и есть производная от них. Запрос «сколько заказов сейчас застряло на каждом этапе» — это группировка по тому, какая веха последняя заполненная.
Три типа рядом
Соберём все три типа fact-таблиц вместе — это итоговая карта модуля.
| Свойство | Transaction | Periodic snapshot | Accumulating snapshot |
|---|---|---|---|
| Зерно | одно событие | состояние сущности за период | один экземпляр процесса |
| Строк на сущность | по одной на каждое событие | по одной на каждый период | одна на весь жизненный цикл |
| Строка обновляется | нет, append-only | нет, период добавляет строку | да, на каждой вехе |
| Date-foreign-keys | обычно один | один (период) | много (по одному на веху) |
| Характерные measures | суммы, количества события | уровни состояния + потоки | lag measures (длительности) |
| Отвечает на вопрос | какие события происходили | каким было состояние на момент | сколько длятся этапы процесса |
| Пример | продажи, клики, платежи | остатки, балансы | пайплайн заказа, заявки |
Три типа — это не конкуренты, из которых выбирают один навсегда. Это три инструмента под три разных вопроса, и в одной аналитической системе живут все три одновременно. Заказы магазина: fct_sales (transaction) — что и когда продано; fct_inventory_daily (periodic snapshot) — остатки на складе; fct_order_pipeline (accumulating snapshot) — как быстро обрабатываются заказы. Выбор типа — это всегда ответ на вопрос «что именно мы хотим анализировать»: события, состояние или скорость процесса.
Accumulating snapshot — единственный тип fact-таблицы с обновлением строк, и это надо учитывать. Колоночные warehouse (модуль про физику) не любят точечные UPDATE. Поэтому accumulating snapshot применяют для процессов с УМЕРЕННЫМ числом экземпляров и предсказуемым коротким набором вех. Для процессов с миллиардами экземпляров обновление каждой строки было бы слишком дорого — там моделируют пайплайн транзакционными событиями вех.
Где accumulating snapshot — правильный выбор
Accumulating snapshot подходит, когда выполнены три условия одновременно:
- Есть процесс с фиксированными вехами — заранее известная последовательность шагов.
- Экземпляр процесса проходит вехи по порядку, и интересна длительность между ними.
- Число экземпляров умеренное — не миллиарды.
Типичные случаи: обработка заказа от создания до доставки; рассмотрение заявки на кредит/визу/страховку; производственный цикл изделия; путь кандидата по этапам найма; обработка обращения в поддержку с SLA-вехами. Везде, где бизнес спрашивает «сколько времени занимает каждый этап» и «где процесс застревает», — это accumulating snapshot.
А когда не он. Поток дискретных событий без понятия «процесс с вехами» — transaction fact table. Регулярные снимки уровня (остаток, баланс) — periodic snapshot. Accumulating snapshot — узкоспециализированный тип именно под анализ многошаговых процессов; вне этой задачи его не применяют.
Попробуй сам
Возьмите процесс найма сотрудника. Вехи: заявка получена -> резюме просмотрено -> телефонное интервью -> очное интервью -> оффер сделан -> оффер принят.
Спроектируйте accumulating snapshot fct_hiring_pipeline. Сформулируйте зерно одним предложением. Заведите date-foreign-key на каждую веху — и решите, какие из них NOT NULL, а какие nullable, и почему. Заведите lag measures — длительности между соседними вехами и общую длительность.
Затем проследите жизнь одной строки: что записывается при вставке (кандидат подал заявку), и какие поля обновляются на каждой следующей вехе. Напишите два запроса: первый — средняя длительность каждого этапа найма (где узкое место процесса); второй — найти «зависших» кандидатов, у которых было очное интервью, но оффер так и не сделан. Наконец, объясните, почему для этого процесса подходит именно accumulating snapshot, а не transaction fact table — что именно стало бы неудобно при моделировании найма потоком отдельных событий.