672
0
0
Скопировать ссылку
Telegram
WhatsApp
Vkontakte
Одноклассники
Назад

Как мы тестируем витрины данных

Время чтения 4 минуты
Нет времени читать?
Скопировать ссылку
Telegram
WhatsApp
Vkontakte
Одноклассники
672
0
0
Нет времени читать?
Скопировать ссылку
Telegram
WhatsApp
Vkontakte
Одноклассники

Приветствую специалистов по данным! Меня зовут Павел Беляев, я тимлид группы Data Processing отдела Data Office сервиса eLama. В этой статье я расскажу, как можно тестировать витрины — источники данных для аналитических отчетов.

Группа Data Processing разрабатывает и поддерживает витрины данных. Под витриной мы понимаем представление (то есть SQL-запрос) и его кеш, обновляемый с определенной регулярностью, либо материализованное представление. Часто витрина представляет собой временной ряд — например, перечень транзакций, проведенных клиентами компании.

Мы получаем сырые данные от команды Data Transfer & Integration, собираем из них витрины, после чего команда BI использует их для визуализации и построения отчетов.

Витрин у нас сотни, время от времени их требуется дорабатывать или создавать новые. И вот как мы при этом их тестируем, чтобы избежать ошибок и неточностей.

Как мы тестируем витрины данных

Задачи тестирования

Итак, у нас есть два сценария тестирования:

  1. Доработка существующей витрины. Чаще всего при этом нужно либо добавить новые поля, либо с некоторого момента изменить логику имеющихся. Большая часть данных в исходных полях при этом не меняется.
  2. Создание новой витрины. В этом случае создается новая логика. Сравнить результат можно только с источником данных, обработанным по той же логике.

Покажем типовые шаги, которые мы применяем в обоих случаях.

Тестируем доработанную витрину данных

Первым делом важно убедиться, что в тех фрагментах витрины, которые не должны измениться, столько же строк, сколько в исходной витрине. Если строк больше, скорее всего, возникли дубли — например, из-за новых джойнов. Тогда следует обратить внимание на новые подзапросы и проверить, сколько строк выдается по полям, по которым происходит джойн.

Если строк больше, но дублей нет, нужно проверить, что значения старых полей за старые периоды не изменились. Это легко сделать, используя запрос с объединением типа EXCEPT DISTINCT. Оно позволяет вывести все строки первой таблицы, которых не нашлось во второй. Чтобы увидеть значения различающихся столбцов обеих таблиц, можно найти сначала отличия исходника от новой витрины, затем отличия новой от исходника — и объединить результаты. Шаблон запроса примерно такой:

(
    SELECT "new vs old" AS _type, a, b, c
    FROM new_table
    EXCEPT DISTINCT
    SELECT "new vs old" AS _type, a, b, c
    FROM old_table
)
UNION ALL
(
    SELECT "old vs new" AS _type, a, b, c
    FROM old_table
    EXCEPT DISTINCT
    SELECT "old vs new" AS _type, a, b, c
    FROM new_table
)
ORDER BY a, b, c, _type

Поле-маркер _type поясняет, что с чем мы сравниваем. В списке сравниваемых полей a, b, c мы оставляем только те, которые не должны измениться при доработке витрины.

Если в новой витрине появились новые строки, они выведутся со значением _type равным new vs old. Если значения в полях a, b, c изменились, то на каждую изменившуюся строку выведется пара строк с обоими маркерами — new vs old и old vs new, в которых мы увидим новые и старые значения соответственно. Если какие-то строки пропали, они отобразятся с маркером old vs new.

Добавленные поля, а также измененную с некоторого момента логику старых полей следует тестировать отдельно, так как со старым значением их не сравнишь. Методика здесь такая же, как для проверки витрин, создаваемых с нуля. Рассмотрим ее далее.

Тестируем вновь созданную витрину

В этом случае также важно проверить, нет ли дублей. Готового эталона для сравнения у нас нет, поэтому мы посчитаем строки с группировкой по составному ключу — набору полей, который должен быть в этой витрине уникальным.

SELECT a, b, c, COUNT(a) AS row_count
FROM new_datamart
GROUP BY 1, 2, 3
HAVING row_count > 1

Разумеется, так можно выявить дубли, только если в витрине нет агрегации. Иначе может выйти, например, так: один из столбцов выведет сумму по составному ключу, все строки будут уникальными, но если в агрегируемых строках затесался дубль — сама сумма задвоится. Именно поэтому дубли следует искать в каждом подзапросе витрины, а также после их объединения, но до агрегирования.

Помимо дублей, в новоиспеченной витрине могут встретиться дефекты трех видов:

  1. строка из источника попала в выборку, в которую не должна была попасть;
  2. значения расчетных столбцов неправильные;
  3. строка не попала в выборку, а должна была.

И вот как их можно отследить.

Находим лишние строки

Чтобы выявить строки, которые должны были отфильтроваться, можно выставить условия фильтрации, противоположные тем, что указаны в техническом задании (ТЗ). При этом, как и в случае с дублями, следует искать их до агрегации.

Допустим, по ТЗ нам нужно посчитать пользователей, заплативших за месяц не менее 10 000 рублей. Тогда вместо подсчета юзеров мы можем вывести все строки, которые будут считаться, и добавить условие с «инвертированным» требованием из ТЗ:

--SELECT COUNT(user_id) AS  — для конечной витрины
SELECT user_id, monthly_turnover — для тестирования
FROM
(
  SELECT user_id
      SUM(sum_paid) AS monthly_turnover
  FROM user_transactions  — таблица с транзакциями пользователей
  GROUP BY 1
  -- HAVING monthly_turnover >= 10000 — предположим, что это условие мы забыли указать при разработке витрины
) AS u
WHERE monthly_turnover < 10000 — для тестирования

Такой запрос не только покажет нам все строки, которые не должны были просочиться в результат, но и подскажет место, где следует искать ошибку: нужно лишь найти, где вычисляется проверяемая метрика.

Если витрина содержит множество подзапросов, проверить стоит каждый из них, а также итог каждого нового объединения.

Находим неверные значения

Чтобы проверить правильность значений расчетных столбцов, нужно прежде всего четко сформулировать ТЗ — конкретные условия и алгоритмы расчета. Когда они у нас есть, мы можем сформировать тестовые примеры для каждого варианта или диапазона условий. Это позволит разработчику лучше понять, что именно и как проверять.

Допустим, нам нужно выбрать клиентов, их оборот за месяц и комиссию сотрудника отдела продаж (ОП) с каждого клиента. При этом для разных оборотов процент комиссии меняется, то есть клиенты сегментируются по оборотам. А сам оборот идет в зачет сотруднику ОП только за первые 90 дней активности клиента.

SELECT month, user_id, responsible, turnover,
    turnover * commission_rate AS commission
FROM
(
  SELECT month, user_id
      responsible, — ответственный за клиента сотрудник ОП
      first_pay, — дата первого платежа клиента
      SUM(turnover) AS turnover
  FROM user_transactions
  --WHERE DATE_DIFF(date_paid, first_pay, DAY) <= 90 — допустим, мы забыли указать это условие
  GROUP BY 1, 2, 3
) AS t
LEFT JOIN
(
  SELECT turnover_from, turnover_to, commission_rate
  FROM commisson_rates -- таблица с комиссионными коэффициентами в привязке к оборотам
) AS c ON t.turnover >= c.turnover_from AND t.turnover < c.turnover_to
WHERE turnover > 0

Этот запрос может завысить сумму комиссионных, так как ограничение по «времени жизни» клиента оказывается не учтено. Но в конечном результате ошибка не обнаружится, ведь там нет этой метрики. Сформируем примеры по ТЗ.

В примере мы учли и изменение коэффициента комиссии commission_rate, и ограничение по lifetime (LT = date_paid – first_pay). Сама по себе эта табличка поможет внимательнее посмотреть на код и проверить, все ли условия учтены. А если попробовать воспроизвести примеры на настоящих данных и применить вспомогательные расчеты к соответствующим подзапросам, мы сразу найдем потерявшееся условие:
WHERE DATE_DIFF(date_paid, first_pay, DAY) <= 90.

Находим пропущенные строки

Случай, когда строки из источников не попадают в выборку, выявить, пожалуй, сложнее всего. Для этого мы сначала смотрим, сколько строк выдает первый подзапрос витрины без фильтрующих условий и группировок. Это число, по идее, должно совпадать с количеством строк в источнике.

Затем сопоставляем его фильтры с требованиями ТЗ и составленными нами примерами для тестирования. Если выяснится, что какой-то из примеров не отрабатывается кодом должным образом, следует подкорректировать код.

Поясню на примере. Предположим, у нас есть три таблицы: 

  • users хранит данные обо всех пользователях некоего сервиса. Часть из них пользуются сервисом и платят самостоятельно. Часть — партнеры сервиса, то есть имеют своих клиентов, у которых тоже есть аккаунты в сервисе, но они не пользуются им сами, а делегируют эту работу партнерам.
  • partner_clients хранит связи между партнерами и их клиентами. При этом партнер является клиентом для самого себя, потому что тоже может быть активным пользователем.
  • transactions хранит все транзакции, то есть платежи всех пользователей. Важный нюанс: партнеры могут платить как со своего аккаунта, так и с аккаунтов своих клиентов. 

Нам требуется вычислить сумму оборотов активных партнеров и всех их клиентов. В users имеется флаг активации юзера, указывающий, что он хоть раз платил, то есть активировался. Первую версию запроса мы составили так:

SELECT partner,
    SUM(turnover) AS partner_turnover
FROM
( -- Выберем всех активированных пользователей
  SELECT user_id
  FROM users — таблица со всеми юзерами
  WHERE activated = TRUE — флаг активации юзера: такой юзер хоть раз платил
) AS u
JOIN
( -- Выберем из активных пользователей только партнеров и их клиентов
    SELECT partner, client
    FROM partner_client — таблица с клиентами партнеров
) AS p ON p.partner = u.user_id — каждый партнер, который клиент сам для себя
JOIN 
( -- Выберем только платящих клиентов и их обороты
    SELECT user_id, SUM(turnover) AS turnover
    FROM transactions — таблица с оборотом
    GROUP BY 1
) AS t ON t.user_id=ag.client — привязываемся к клиентам
GROUP BY 1

В примерах мы выделяем все комбинации пользователей в зависимости от их сегмента (партнер, клиент партнера, самостоятельный) и свойства активации (активирован, не активирован). Проведя каждый из примеров по запросу, мы видим, что не все желаемые строки попали в конечную выборку:

Ведь мы не учли, что партнер сам может быть неактивным, а его клиенты при этом могут иметь оборот. Такие тонкости очень сложно отследить, просто глядя на результат или на код. Нужно хорошо понимать бизнес-логику и уметь ее визуализировать до разработки.

В нашем примере правильнее было бы вовсе не привязываться к флагу активации, а просто взять все транзакции и выбрать из них юзеров — клиентов партнера:

SELECT partner,
    SUM(turnover) AS partner_turnover
FROM
( — Выберем только платящих клиентов и их обороты
    SELECT user_id, SUM(turnover) AS turnover
    FROM transactions — таблица с оборотом
    GROUP BY 1
) AS t
JOIN
( — Выберем из активных пользователей только партнеров и их клиентов
    SELECT partner, client
    FROM partner_client — таблица с клиентами партнеров
) AS p ON p.client = t.user_id
GROUP BY 1

Резюме

Итак, что мы делаем для проверки доработанной витрины:

  1. Проверяем ее на дубли подсчетом по уникальному составному ключу до группировки.
  2. Сравниваем старые поля новой версии с текущей с помощью EXCEPT DISTINCT.
  3. Новые поля проверяем так же, как при создании новой витрины.

Для проверки вновь создаваемой витрины:

  1. Проверяем ее на дубли.
  2. Находим лишние строки, фильтруя конечную витрину по «инвертированным» условиям ТЗ.
  3. Составляем на основе требований ТЗ примеры исходных данных и ожидаемых результатов для них — и убеждаемся, что запрос верно обрабатывает все варианты.

Комментарии0
Тоже интересно
Комментировать
Поделиться
Скопировать ссылку
Telegram
WhatsApp
Vkontakte
Одноклассники