Transaction fact table: одна строка на событие
Прошлый модуль ввёл fact-таблицу вообще: центр звезды, foreign keys плюс measures. Но fact-таблицы не одинаковы. Кимбалл выделяет три типа fact-таблиц, и они различаются тем, что представляет одна строка, то есть зерном. Этот модуль разбирает все три по очереди, и начинается с самого распространённого — transaction fact table (транзакционная таблица фактов).
Transaction fact table — это тип fact-таблицы, который вы будете встречать и проектировать чаще всего. Если кто-то говорит «fact-таблица» без уточнения — почти наверняка имеется в виду транзакционная. Этот урок объясняет, что это за тип, в чём его зерно, почему он самый гибкий из трёх и почему его строки разрежены.
Зерно: одно событие-измерение
Зерно transaction fact table формулируется так: одна строка — это одно отдельное бизнес-событие в конкретной точке пространства и времени.
Что значит «событие-измерение»? Это атомарное событие, которое произошло и которое мы измеряем: одна продажа товара, одна поездка, один платёж, один клик по объявлению, одно обращение в поддержку. Каждое такое событие, происходя, порождает ровно одну строку в транзакционной fact-таблице. Событие случилось — строка появилась. Это самое прямое и интуитивное соответствие между реальностью и таблицей: что произошло в мире, то и записано строкой.
Возьмём конкретный пример — транзакционная fact-таблица продаж интернет-магазина:
-- Transaction fact table: grain = одна позиция в одной продаже
CREATE TABLE fct_sales (
-- foreign keys: контекст события
date_key INT NOT NULL REFERENCES dim_date(date_key),
product_key INT NOT NULL REFERENCES dim_product(product_key),
customer_key INT NOT NULL REFERENCES dim_customer(customer_key),
store_key INT NOT NULL REFERENCES dim_store(store_key),
-- degenerate dimension
order_number VARCHAR(20) NOT NULL,
-- measures: числа события
quantity INT NOT NULL,
unit_price NUMERIC(10,2) NOT NULL,
extended_amount NUMERIC(12,2) NOT NULL,
discount_amount NUMERIC(12,2) NOT NULL
);Каждая строка fct_sales — это факт «такой-то товар продан такому-то клиенту в такой-то день в таком-то магазине, в таком-то количестве, на такую-то сумму». Произошла продажа — строка записана и больше никогда не меняется. Это важное свойство, к которому урок ещё вернётся: транзакционные строки неизменяемы, они только добавляются.
Почему transaction fact table самый гибкий
Транзакционная fact-таблица — самый гибкий из трёх типов, и причина в том, что её зерно атомарно. Прошлый модуль уже объяснял ценность атомарного grain; здесь она проявляется во всю силу.
Раз каждая строка — это одно мельчайшее событие, то из этих строк аналитик может собрать запросом любой агрегат и любой разрез. Выручка по товару, по клиенту, по магазину, по дню, по месяцу, по региону; средний чек; число продаж; количество товаров — всё это GROUP BY поверх атомарных строк. Транзакционная таблица не предрешает, какие вопросы ей зададут: она хранит сырые события, а агрегаты вычисляются на лету под любой запрос.
Сравните с типами, которые разберут следующие уроки. Periodic snapshot хранит уже свёрнутый периодический срез — он отвечает на вопросы про состояние на конец периода, но детализацию отдельных событий внутри периода в нём не достать. Transaction fact table детализацию хранит всю — отсюда гибкость.
-- Из одной транзакционной таблицы — любой разрез:
-- выручка по дням
SELECT d.full_date, SUM(f.extended_amount) AS revenue
FROM fct_sales f JOIN dim_date d ON f.date_key = d.date_key
GROUP BY d.full_date;
-- средний чек по магазинам (агрегат собран из атомарных строк)
SELECT s.store_name,
SUM(f.extended_amount) / COUNT(DISTINCT f.order_number) AS avg_order
FROM fct_sales f JOIN dim_store s ON f.store_key = s.store_key
GROUP BY s.store_name;
-- топ товаров по количеству
SELECT p.product_name, SUM(f.quantity) AS units
FROM fct_sales f JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.product_name ORDER BY units DESC LIMIT 10;Одна таблица — три совершенно разных вопроса, и так можно продолжать бесконечно. Это и есть гибкость атомарного транзакционного зерна.
Практическое правило проектирования: если сомневаетесь, какой тип fact-таблицы взять, начинайте с transaction fact table на атомарном зерне. Это безопасный выбор по умолчанию — он сохраняет максимум информации, и поверх него при необходимости всегда можно построить snapshot или агрегаты. Обратно — из snapshot развернуть атомарные события — нельзя.
Транзакционные строки разрежены
Важное свойство transaction fact table: её строки разрежены (sparse). Строка существует только тогда, когда событие реально произошло. Нет события — нет строки.
Это звучит очевидно, но имеет следствия. Возьмём товар, который в конкретном магазине в конкретный день не продавался ни разу. В транзакционной fct_sales за этот день для этой пары «товар-магазин» не будет вообще ни одной строки. Таблица не содержит строк-нулей «продано 0 штук» — она содержит только строки реальных продаж.
Это сильная сторона: транзакционная таблица не тратит место на «ничего не произошло». Если из миллиона возможных сочетаний «товар-магазин-день» продажи были в десяти тысячах — в таблице десять тысяч строк, а не миллион. Для редких событий это огромная экономия.
Но у разреженности есть и следствие, которое надо осознавать при анализе. Вопрос «в какие дни товар X не продавался ни разу» нельзя ответить, просто посмотрев в fct_sales — отсутствующих дней там просто нет, отсутствие не записано. Чтобы найти «нулевые» дни, нужно взять полный список дат из dim_date, полный список товаров и вычислить, для каких сочетаний в fact-таблице строки отсутствуют. Отсутствие события — это не строка, это пробел, и работать с пробелами надо отдельно.
Только добавление, никаких обновлений
Ещё одно ключевое свойство transaction fact table: режим работы — append-only (только добавление). Новые события дописывают новые строки; существующие строки не обновляются и не удаляются.
Причина — в природе транзакционного события. Продажа, которая произошла вчера, произошла окончательно. Факт «вчера продали 3 штуки на 450 рублей» — исторический, он зафиксирован и не может задним числом стать другим. Поэтому строка транзакционной fact-таблицы, однажды записанная, остаётся как есть навсегда.
Это отличает transaction fact table от accumulating snapshot (третий урок модуля), где строка как раз обновляется по мере прохождения процессом вех. Транзакционная — нет: событие атомарно и завершено в момент записи, обновлять нечего.
Append-only — это ещё и удобно для warehouse. Прошлый модуль про физику показал: колоночные хранилища любят пакетное добавление и плохо переносят точечные обновления. Транзакционная fact-таблица идеально ложится на это: пайплайн загрузки регулярно дописывает пачку новых событий, и ничего не обновляет. Загрузка проста и эффективна.
| Свойство | Transaction fact table |
|---|---|
| Зерно | одно атомарное событие-измерение |
| Когда появляется строка | в момент, когда событие произошло |
| Изменяется ли строка | нет, append-only |
| Плотность | разрежена — строка только там, где было событие |
| Гибкость анализа | максимальная — любой агрегат из атомарных строк |
| Что плохо отвечает | вопросы про отсутствие события и про состояние на момент |
Если данные требуют корректировки задним числом — отмена продажи, возврат, исправление — транзакционную строку не редактируют. Корректировку оформляют НОВОЙ строкой: возврат записывают как отдельное событие с отрицательными measures (quantity = -3, extended_amount = -450). Так таблица остаётся append-only и сохраняет полную историю: видно и исходную продажу, и возврат. Перезапись строки уничтожила бы факт, что продажа вообще была.
Где transaction fact table — правильный выбор
Транзакционная fact-таблица подходит, когда у вас есть поток дискретных событий и вы хотите анализировать их с максимальной свободой разрезов. Это абсолютное большинство аналитических задач:
- Продажи и заказы — каждая позиция продажи как событие.
- Финансовые транзакции — каждый платёж, перевод, начисление.
- Поведение на сайте — каждый клик, просмотр страницы, добавление в корзину.
- Логистика — каждая отгрузка, каждое перемещение товара.
- Поддержка — каждое обращение, каждое сообщение в тикете.
Общее у всех: есть отдельные, дискретные, датируемые события, и аналитика хочет резать их как угодно. Для этого transaction fact table — точный инструмент.
Где она работает хуже и нужны другие типы. Если вопрос не про события, а про состояние — «каков остаток на складе на конец каждого дня», «какой баланс счёта на конец месяца» — то транзакционная таблица отвечает на это неудобно: пришлось бы суммировать все движения от начала времён. Здесь лучше periodic snapshot (следующий урок). А если нужно отслеживать прохождение одного процесса через вехи — заказ создан, оплачен, отгружен, доставлен — лучше accumulating snapshot (третий урок). Три типа fact-таблиц — это три ответа на три разных вопроса; transaction fact table отвечает на «какие события происходили».
Попробуй сам
Возьмите сервис каршеринга. Бизнес-процесс — «завершённая аренда автомобиля».
Спроектируйте transaction fact table fct_rentals. Сформулируйте зерно одним предложением и убедитесь, что оно атомарное и описывает одно событие. Выпишите foreign keys (на какие dimensions ссылается аренда) и measures (что числового измеримо на уровне одной аренды: длительность, расстояние, стоимость). Подумайте, есть ли degenerate dimension.
Затем проверьте на свойства транзакционного типа. Первое: напишите три разных аналитических запроса к своей таблице (выручка по дням, средняя длительность по моделям авто, топ районов по числу аренд) — убедитесь, что все три отвечаются из одной таблицы. Второе: разреженность — будут ли в таблице строки для автомобилей, которые в какой-то день никто не арендовал, и как тогда ответить на вопрос «сколько дней простоял автомобиль X»? Третье: append-only — как оформить отмену уже записанной аренды, не редактируя исходную строку?