Grain (зерно): почему атомарный grain — главное решение
В четырёхшаговом процессе grain — второй шаг. Но по важности он первый среди равных: Кимбалл называет объявление grain самым ответственным решением размерного дизайна. Прошлый урок ввёл понятие и место grain в процессе. Этот урок целиком о grain: что это, почему его берут самым низким (атомарным), что физически означает выбор зерна и почему смешанный grain — одна из опаснейших ошибок в моделировании данных.
Если из всего модуля вы запомните одну вещь, пусть это будет grain. Неверно выбранное зерно невозможно «починить запросом» — оно отравляет всю модель, потому что dimensions, facts и каждый отчёт строятся поверх него.
Что такое grain
Grain (зерно, гранулярность) — это определение того, что представляет одна строка fact-таблицы. На какой атомарный факт реального мира приходится одна запись.
Grain объявляется в виде точного предложения «одна строка этой fact-таблицы — это …». Примеры корректно объявленного зерна:
- «Одна строка
fct_sales— это одна позиция в одном чеке» (одна строка чека — отдельный товар). - «Одна строка
fct_trips— это одна завершённая поездка». - «Одна строка
fct_account_balance— это баланс одного счёта на конец одного дня». - «Одна строка
fct_page_views— это один просмотр одной страницы одним пользователем».
Заметьте, насколько эти формулировки конкретны. Не «данные о продажах», а «одна позиция в одном чеке». Конкретность — обязательна. Расплывчатое «одна строка про заказы» — не объявление grain: непонятно, строка это весь заказ или отдельная позиция в нём, а это две совершенно разные таблицы.
Почему атомарный grain
Когда зерно можно выбрать на разных уровнях, Кимбалл предписывает выбирать самый низкий, атомарный grain — самое мелкое событие из доступных. Возьмём продажи. Зерно можно объявить на нескольких уровнях:
- Атомарный: одна позиция в чеке (один товар в одной покупке).
- Выше: один чек (вся покупка целиком, без разбивки по товарам).
- Ещё выше: итог по магазину за день (одна строка на магазин на дату).
- Совсем высокий: итог по региону за месяц.
Правильный выбор — атомарный, позиция в чеке. Причина — гибкость, и она вытекает из простого свойства данных: из мелких строк можно собрать любой агрегат, но из свёрнутого итога мелкие детали не достать.
Если fact-таблица хранит позиции чеков, аналитик может посчитать что угодно: выручку по товару, по чеку (сгруппировав позиции), по магазину за день, по региону за месяц, средний размер чека, число товаров на чек. Все агрегаты выводятся из атомарных строк запросом с GROUP BY.
Если же таблица сразу хранит «итог по магазину за день», то выручка по конкретному товару уже потеряна навсегда — её просто нет в данных. Свернув данные при загрузке, вы выбросили детализацию, и никаким запросом её не вернуть. Атомарный grain — это сохранённая возможность ответить на ещё не заданные вопросы.
Заранее посчитанные агрегаты (выручка по магазину за день) тоже бывают полезны — для ускорения частых отчётов. Но их строят как ОТДЕЛЬНЫЕ aggregate fact tables ПОВЕРХ атомарной таблицы, а не вместо неё. Атомарная fact-таблица — фундамент и источник; агрегаты — производный кеш для скорости. Сначала атомарный grain, агрегаты потом и в дополнение.
Grain определяет dimensions и facts
Объявленный grain — не просто формальность, он напрямую диктует, что войдёт в таблицу. Это и есть причина, по которой grain объявляют рано.
Grain определяет допустимые dimensions. Dimension допустима, только если у неё ровно одно значение на одну строку grain. Для зерна «позиция в чеке»: товар — одно значение на позицию (годится), магазин — одно (годится), клиент — один (годится), дата — одна (годится). А «список всех товаров в чеке» — не dimension этого зерна: на одну позицию это много товаров, нарушение grain. Зерно работает как фильтр-критерий для шага выбора dimensions.
Grain определяет допустимые facts. Measure допустима, только если измерима на уровне одной строки grain. Для зерна «позиция в чеке»: количество этого товара (годится), цена за единицу (годится), сумма по позиции (годится). А «общая сумма чека» на уровне позиции некорректна — она относится к чеку целиком, и если положить её в каждую позицию, при суммировании она задвоится. Зерно — критерий и для выбора facts.
Поэтому верно утверждение: меняете grain — переделываете dimensions и facts. Зерно — фундамент, на котором стоит вся остальная таблица.
Смешанный grain — критическая ошибка
Железное правило: все строки одной fact-таблицы обязаны быть одного grain. Нарушение этого правила — смешанный grain (mixed grain) — одна из опаснейших ошибок размерного моделирования. Разберём, почему именно опасных.
Представьте, что в fct_sales по недосмотру оказались строки двух зёрен сразу: строки-позиции чеков и строки-итоги по магазину за день. Таблица:
fct_sales со СМЕШАННЫМ grain (так делать нельзя):
тип строки | store | product | amount
позиция чека | S-01 | Laptop | 1200.00
позиция чека | S-01 | Mouse | 25.00
позиция чека | S-01 | Keyboard | 75.00
ИТОГ магазина/день| S-01 | (нет) | 1300.00 <- сумма тех же трёх позиций!Теперь аналитик пишет невинный запрос: SELECT SUM(amount) FROM fct_sales WHERE store = 'S-01'. Ожидает 1300. Получает 2600 — потому что просуммировались и три позиции (1200+25+75=1300), и строка-итог (ещё 1300), а итог это те же самые позиции, посчитанные второй раз. Двойной счёт.
Что здесь особенно опасно — то же, что было особенно опасно в каскаде JOIN: ошибка тихая. Запрос не падает. Он возвращает 2600 — правдоподобное число, просто завышенное вдвое. Аналитик не видит проблемы, строит на этой цифре отчёт, бизнес принимает на ней решение. Смешанный grain незаметно отравляет каждую агрегацию по таблице, и обнаруживается это часто лишь когда цифры в двух отчётах не сходятся и кто-то начинает раскапывать.
Смешанный grain не даёт сообщения об ошибке. SUM по таблице со смешанным зерном просто молча возвращает завышенное число, потому что складывает атомарные строки вместе со строками-итогами, которые их уже включают. Если детальные и сводные данные нужны оба — это ДВЕ отдельные fact-таблицы с явно объявленным зерном каждая, никогда не одна.
Как избежать. Объявляйте grain явным предложением и записывайте его — в документацию модели, в комментарий к DDL. Каждую строку, попадающую в fact-таблицу через пайплайн загрузки, проверяйте на соответствие объявленному зерну. Нужны и детальные строки, и сводные — заведите две таблицы: fct_sales с зерном «позиция чека» и fct_sales_daily с зерном «итог по магазину за день». Каждая внутри себя однородна по grain — и SUM по каждой корректен.
| Аспект | Один grain (правильно) | Смешанный grain (ошибка) |
|---|---|---|
| Строки таблицы | все одного зерна | зёрна разных уровней вперемешку |
| Поведение SUM | корректное | двойной счёт |
| Сообщение об ошибке | — | нет, ошибка тихая |
| Как обнаруживается | — | случайно, когда цифры не сходятся |
| Решение для детальных + сводных | две отдельные таблицы | (нельзя класть в одну) |
Оценка размера fact-таблицы из grain
Полезный навык «до железа»: из объявленного grain можно сразу оценить, насколько большой будет fact-таблица. Размер — это число событий выбранного зерна.
Сервис проката велосипедов, зерно «одна поездка». 200 станций, в среднем 50 поездок со станции в день. В день: 200 x 50 = 10 000 строк. В год: 10 000 x 365 примерно 3,65 млн строк. За пять лет — около 18 млн. Умеренно.
А теперь зерно «один просмотр страницы» для крупного сайта: 5 млн просмотров в день — это 1,8 млрд строк в год. Атомарный grain на нагруженном событийном потоке закономерно порождает миллиарды строк.
И тут вспомните прошлый модуль. Fact-таблица длинная и узкая (foreign keys плюс measures), и колоночное хранение со сжатием делает миллиарды строк подъёмными: RLE и dictionary encoding ужимают повторяющиеся foreign keys в разы. Атомарный grain технически реализуем именно потому, что fact-таблица колоночная. Выбор зерна и физика хранения связаны напрямую: атомарное зерно — это миллиарды строк, и колоночный warehouse это вывозит.
Попробуй сам
Возьмите три бизнес-процесса: оформление заказа в интернет-магазине, бронирование номера в отеле, отправка SMS-рассылки.
Для каждого: сформулируйте атомарный grain точным предложением. Для заказа подумайте особенно внимательно — зерно это весь заказ или отдельная позиция в нём, и почему атомарным будет именно позиция. Затем для каждого процесса оцените размер fact-таблицы за год: придумайте разумные числа (сколько событий в день) и посчитайте строки.
Финальное задание — разобрать ошибку. Дана fct_orders, куда сложили и строки-позиции заказов, и строки-итоги по клиенту за месяц. Напишите простой SQL-запрос с SUM, который вернёт неверное число, посчитайте руками, во сколько раз оно завышено и почему, и предложите правильную структуру из двух таблиц с явно объявленным зерном каждой.