Автор: Сергей Иванов, руководитель группы, Neoflex

Одним из этапов разработки витрин данных является тестирование результата и подтверждение корректности разработанного функционала. При этом организовано тестирование может быть по-разному.

Определим несколько видов тестирования:

1.     Технические тесты

Техническими тестами легко можно проверить корректность сборки витрины. Из основных видов технических тестов можно выделить:

• Дубли - проверка на наличие дублей по ключу

• Разрывы - проверка на разрывы в истории

• Перекосы - проверка наложения исторических записей друг на друга

• Даты - проверка корректности формирования дат

• NULL в ключе - проверка NULL в ключевых и обязательных к заполнению полях

Подробно на этих тестах останавливаться не будем, информация по ним есть в открытом доступе.

2.     Бизнес-тесты

Это набор тестовых запросов, направленных на выявление ошибок в бизнес-данных. Как правило набор бизнес-тестов предоставляет владелец объекта.

Бизнес-тестов может быть великое множество, здесь все зависит от вашего бизнес-домена и от конкретных требований к витрине.

Приведу примеры некоторых бизнес-тестов:

• Проверка уникальности ИНН среди клиентов

• Проверка ИНН на длину строки в 10/12 символов

• Проверка наличия ссылочной целостности при заполнении поля «родительский договор»

• Заполнение поля «валюта» при заполнении поля «сумма»

• Проверка поля «сумма платежей» путем пересчета платежей

3.     Сверка с эталоном

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

Но не все так просто, как кажется на первый взгляд.

3.1  Сравнение витрин, расположенных в одной СУБД

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

В таком случае все довольно просто. Можно применить следующий алгоритм действий:

Первое – Сверяем количество записей по ключу. При этом все ограничения, которые были наложены на нашу витрину следует также наложить и на эталон (если, например, мы строим  витрину только на актуальный срез или за конкретную дату)

Если количество сходится – отлично! Значит источники нашей витрины и эталона синхронны и дают равнозначный результат по количеству. Мы можем двигаться дальше и сверять сами данные.

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

Второе – Нам нужно проверить качество данных в витрине. Для сверки качества данных используем EXCEPT. Сначала вычитаем из витрины эталон, потом из эталона витрину – и на выходе остаются записи, которые имеют расхождения по какому-либо атрибуту.

Ниже пример такого вычитания.

select
column1, column2, column3
from TABLE1 ---Наша витрина-
EXCEPT -- Вычитаем из нашей витрины эталон
select
column1, column2, column3
from TABLE2 ---Эталон-
UNION -- Объединяем выборки
select
column1, column2,column3
from TABLE2 ---Эталон-
EXCEPT --Вычитаем из эталона нашу витрину
select
column1, column2, column3
from TABLE1 ---Наша витрина-

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

Чтоб обеспечить равнозначность выборок по количеству достаточно подрезать их друг под друга, то есть приравнять количество строк. Делаем это через JOIN эталона по уникальным ключам витрины.

with cte as
(select distinct KEY1 --столбец, являющийся ключем витрины 
from TABLE1) ---витрина
select *
from TABLE2 ---эталон
join cte
on (CTE.KEY1 = TABLE2.KEY1) 

3.2. Сравнение витрин, расположенных в разных СУБД

А что, если эталон существует, но мы не можем его загрузить к себе в БД. Например, нет инструментов для репликации или дорого и долго настраивать эту репликацию, или, например, под эталон нет места в вашей БД,  или же в крайнем случае  нужно разово быстро сверить. Как быть тогда?

Для поиска несоответствий двух выборок я использую разбивку на группы с помощью остатка от деления.

Что мы делаем:

Первое – разбиваем всю витрину и эталон на группы - получаем два списка с группами.

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

SELECT CEIL(COUNT(1)/1000)
  FROM TABLE1

На этом шаге получаем 100 тысяч – значит разбивать выборки будем на 100 тысяч групп.

select mod (KEY1, 100000) as grp, count (*)
from TABLE1 -- наша витрина 
group by mod (KEY1, 100000)

select mod (KEY1, 100000) as grp, count (*)
from TABLE2 -- эталон
group by mod (KEY1, 100000)

Второе – полученные два списка групп, состоящих из номера группы и количества записей в каждой группе, мы должны сравнить и выявить именно те группы, в которых это количество не совпадает. Результат можем сравнить, например, с помощью EXCEL.

Витрина

Эталон

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

select KEY1
from TABLE1 -- наша витрина 
where mod (KEY1, 100000)= 5

select KEY1
from TABLE2 -- эталон
where mod (KEY1, 100000)= 5

Далее уже дело не сложное, так как в результате объём записей в одной группе достаточно небольшой и его можно проанализировать.

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

Четвертое -  а как же сравнить данные? Ответ – контрольные суммы.

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

Чтобы сверить даты, можно подсчитать количество дней. Например, разница между нашей датой и какой-нибудь константой, а далее уже просуммируем количество дней.

--витрина (рostgresql)
Sum (EXTRACT (DAY FROM (column_date '2024-01-01 00:00:00'::timestamp))) as result_1,

--Эталон (oracle)
Sum (EXTRACT (DAY FROM (column_date TO_TIMESTAMP ('2024-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')))) as result_2,

Теперь про сравнение текстовых полей типа string (varchar, char, text). Реализация данного сравнения достаточно спорная, так как от больших объемов данных будет зависеть производительность и точность.

Важно учитывать два условия: преобразования должны давать одинаковый результат в обеих СУБД, а также обеспечивать хорошую производительность.

Один из возможных вариантов - сравнение hash. Первым шагом вычисляем hash функцию текстового поля (хватит 32 символа, а значит md5 подойдет). Полученный результат представляет собой строку в формате HEX, т.е. если выполнить преобразование hex2int, то мы получим числовой hash строки, а значит можем просуммировать его для всей таблицы.

Выводы

Как мы видим, даже находясь в разных БД можно сверить как количественные характеристики нашей витрины, так и качественные. При этом мы можем варьировать количество групп, на которые мы будем делить. Остаток от деления на 100 тысяч был выбран в качестве оптимального для витрины, содержащей примерно 50-100 миллионов записей.

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