В отчёте сумма заказов завысилась после присоединения таблицы позиций. Какой дефект модели нужно проверить первым?
Сначала нужно проверить несовпадение зерна данных: заказ представлен одной строкой, а после соединения с позициями превращается в несколько строк. Если сумму заказа считать после такого соединения, она будет повторена для каждой позиции и завысится.
Правильное решение — явно определить зерно каждой таблицы, агрегировать данные до нужного уровня перед соединением либо считать показатель из набора, где одна строка соответствует одному заказу.
В транзакционных системах данные обычно нормализуют: заказ, его позиции, платежи и доставки хранятся в отдельных сущностях. Это уменьшает дублирование и упрощает изменение отдельных частей заказа.
В аналитических моделях те же сущности часто соединяются для расчётов. Здесь появилась отдельная проблема: физическое соединение таблиц может изменить количество строк, поэтому разработчики стали явно фиксировать зерно факта — уровень, которому соответствует одна строка набора данных.
Пусть в таблице заказов одна строка соответствует одному заказу, а в таблице позиций — одной позиции заказа. У заказа с пятью позициями после соединения появится пять строк, и его общая сумма окажется повторённой пять раз.
Ошибка особенно опасна, когда запрос одновременно считает показатели разных уровней: сумму заказа, количество позиций и выручку по клиенту. Результат может выглядеть правдоподобно, но будет зависеть от количества связанных строк.
Сначала формулируют зерно каждого набора: например, одна строка на заказ, одна строка на позицию, одна строка на платёж. Затем проверяют, является ли связь между наборами отношением один-к-одному, один-ко-многим или многие-ко-многим.
Если отчёт строится на уровне заказа, позиции нужно предварительно агрегировать до одного ряда на заказ. После этого соединение не размножит заказ. Другой вариант — считать сумму по позициям, если именно позиции являются источником истины для этого показателя, но нельзя бездумно складывать уже рассчитанную сумму заказа после размножающего соединения.
Особое внимание требуется при нескольких отношениях один-ко-многим. Если к заказу одновременно присоединить пять позиций и три платежа, наивное соединение может создать до пятнадцати строк для одного заказа. Предварительная агрегация каждой дочерней сущности отдельно предотвращает такое перемножение.
Полезны проверки кардинальности: число заказов до и после соединения, доля заказов с несколькими совпадениями, уникальность ключа на ожидаемом уровне. Ограничения уникальности в источнике или тесты качества данных помогают обнаружить нарушение предположений, но сами по себе не исправляют неверно выбранное зерно.
Компромисс состоит в выборе места агрегации. Ранняя агрегация уменьшает объём соединения и риск дублирования, но может лишить отчёт детализации. Сохранение детализации даёт больше гибкости, однако требует строго контролировать уровень каждого показателя.
Команда строила отчёт по заказам, присоединив к таблице заказов позиции и платежи. После этого выручка оказалась выше показателя в транзакционной системе. Проверка показала, что один заказ с четырьмя позициями и двумя платежами превращался в восемь строк.
Рассматривались три варианта. Удалить дубликаты после соединения было быстро, но ненадёжно: разные позиции и платежи могли иметь разные атрибуты. Считать сумму через условную агрегацию можно, но решение становилось сложным и легко ломалось при добавлении новых связей. Пересчитать позиции и платежи до уровня заказа перед соединением потребовало отдельного шага, зато сделало зерно явным и стабильным.
Выбрали третий вариант. После раздельной агрегации показатели совпали с источником, а объём промежуточных данных уменьшился. Для каждого аналитического набора дополнительно зафиксировали зерно и тест, проверяющий уникальность ключа на этом уровне.
Почему удаление дубликатов после соединения не является универсальным исправлением?
Дубликаты могут быть не полностью идентичными: строки позиций отличаются товарами, а строки платежей — датами или способами оплаты. Механическое удаление одной строки уничтожит часть фактов или выберет значение случайно. Сначала нужно устранить размножение на уровне модели — агрегировать каждую дочернюю сущность до требуемого зерна.
Как отличить завышение из-за неверного соединения от реального дублирования в источнике?
Нужно отдельно проверить уникальность ключа заказа в таблице заказов, количество строк после каждого соединения и число совпадений дочерних строк для одного заказа. Если ключ уже не уникален до соединения, проблема находится в источнике или загрузке. Если до соединения всё корректно, а рост появляется после добавления конкретной таблицы, нужно анализировать кардинальность именно этого соединения.
Почему соединение двух таблиц, каждая из которых уникальна по своему ключу, всё равно может завысить показатель?
Уникальность должна проверяться относительно ключа соединения, а не любого другого поля. Таблица заказов может быть уникальна по идентификатору заказа, а таблица платежей — по идентификатору платежа; при этом у одного заказа допустимо несколько платежей. Соединение таких таблиц сохраняет несколько строк на заказ, поэтому показатель уровня заказа нужно агрегировать отдельно или рассчитывать после приведения данных к единому зерну.