Тестирование агрегатов методом Шерлока

Когда вводишь в поисковой запрос "Курсы для аналитика данных", то сразу в глаза бросается SQL, Phytnon, R и другие инструменты. Но в первую очередь аналитику данных помогли бы не только инструменты, а еще и определенные способы мышления цифрового детектива. На наглядном примере мы вместе протестируем витрину данных, выстроим логику развития событий, а далее применим SQL. Статья предназначена для junior аналитиков данных или тех, кто только знакомится с будущей профессией Data Analyst.

Шерлок Холмс сидел в кресле и читал утреннюю сводку в газете. Неожиданно он спросил Ватсона:

«Вы слышали что-нибудь об ограблении аптеки?»

«Ни слова. Я несколько дней не заглядывал в газеты». – сказал Ватсон.

В этот момент в дверь постучали и вошел Аптекарь Миллер. Он был вне себя от горя. Ночью на аптеку напали грабители и выкрали 2 самых дорогих лекарственных препарата «Оживин». Эти препараты продавались только у аптекаря Миллера, в Лондоне их больше нигде не найти. Охотники знали, что искали.

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

«Я обращаюсь к Вам, потому что вы лучший в этом деле. Мистер Холмс, помогите найти препараты и преступников в короткие сроки», - попросил, аптекарь

Шерлок сел и раскурил трубку, а потом произнес: «Располагайте мною», - сказал он. «Расскажите подробно, что произошло».

Последовала типичная история ограбления.

Пока аптекарь рассказывал, Шерлок придумал и предложил инновационный путь: «Мистер Миллер, рынок сбыта может быть «черный» и «белый». Для проверки легальной продажи нам поможет «Честное бюро».

«Честное бюро», как вы знаете, нанесло маркировку на каждую упаковку лекарств. Предлагаю запросить данные по продажам и отпуску лекарств из аптек. Мы сможем определить цепочку, как только «Оживин» попробуют продать легально в другой аптеке», Аптекарь задумался и произнес: «Странно, что это идея мне не пришла в голову сразу, завтра я приду к вам с отчетом из Бюро, мистер Холмс».

Шерлок помогает Бюро

Утром аптекарь Миллер пришел в назначенный срок, в руках он держал планшет.

«Честное бюро» отработало запрос о продажах и отпуску лекарств по рецепту из аптек молниеносно. Отчет содержал данные обо всех реализованных лекарственных препаратах в Лондоне. Кроме того, аптекарю удалось договориться, что обновленный отчет он будет получать каждый день.

Шерлок Холмс взглянул на отчет и произнес: «Вот тут-то бюро и село в лужу».

Мистер Миллер недоуменно посмотрел на Шерлока и попросил объяснений: «Если бы аптеки, действительно продавали столько, сколько здесь написано. То, дорогой друг, боюсь, Вы уже были бы давно банкрот. Видели эти цифры?»

Шерлок Холмс и «Честное бюро»

Утром Шерлок уже разговаривал с Бюро и выяснял, как получились странные цифры по продажам.

Сотрудник Бюро, Уотсон, начал рассказ: «Как вы знаете, на каждую упаковку таблеток наносится код маркировки. Все данные о кодах мы храним в базе данных. В ней лежит поток данных по всем движениям товара. Так как данных очень много, то для ускорения расчетов мы создаем агрегаты. Например, из потока данных stg.pharma мы создали агрегат agg.pharma_out по реализации лекарственных препаратов.

Stg(staging) - это промежуточная область хранения, используемая для обработки данных во время процесса извлечения, преобразования и загрузки (ETL)

Агрегат – это массив агрегированных данных, содержащий рассчитанные показатели (например, продажи) по разрезам (например: страна, дата, возраст и так далее). Агрегат ускоряет доступ к данным и играет роль сводного отчета

Отчет, который вы видели утром, как раз показывает количество реализованных лекарств (кодов маркировки) по датам, ценам, местам реализации, типам вывода из оборота», «Мистер Уотсон, вы считаете отчет правильным?» – спрашивает, Холмс,

«Ну конечно, Холмс, я могу рассказать про каждую метрику и разрез, посчитанные в нем. А лучше вместе посмотрим на схему агрегата» - уверенно, произнес мистер Уотсон.

Агрегат agg.pharma_out:

Поля в агрегате:

«Как все у вас складно, Мистер Уотсон. Но давайте просто посмотрим на цифры, которые вы мне прислали за первую половину апреля 2021 года по продажам всех аптек Лондона за каждый день. Вас ничего не смущает?» - спросил, Холмс.

Мистер Уотсон покраснел и не сразу ответил: «Да, мистер Холмс, 10$ — это нереальная цифра по сумме продаж лекарств для самого крупного города Британии, но количество в отчете правильное. Мы обязательно перепроверим цифры и вернемся к вам, мистер Холмс»,

Холмс критично произнес: «Прекрасно, но мое доверие подорвано, и я хотел бы сам разобраться в проблеме, иначе я не смогу опираться на ваши отчеты. Придется взяться за дело и рассказать вам о тестировании. Иначе мы не вычислим мошенников в короткие сроки»

Чему научил Шерлок Холмс «Честное Бюро» по тестированию агрегатов?

Правило 1. Поспешные выводы опасны.

Или проверка на здравый смысл. При первом взгляде на цифры определяем, а правильные данные они показывают и можно ли на них делать выводы.

Приведу пример: при проверке агрегата и поля «Место деятельность» среди аптек в агрегате найдены не только аптеки, но и продажи со склада. А может ли такое быть по смыслу, хотя и сходится с источником? Нет, и тут возникает вопрос о верности источника.

Еще более простой пример смысловой проверки, а есть ли среди продаваемых лекарств наиболее употребляемые? Парацетамол, Аспирин, Ибупрофен, Цитрамон и так далее.

Правило 2. Внимание к деталям

“Здесь следы кончаются. Неясно, куда они пошли дальше. В конце концов, это нам ничего не дает.

-Вы правы, Андерсен. Ничего. Кроме размера обуви, роста, походки и скорости ходьбы!”

Первые шаги по тестированию агрегата должны начинаться с внимательной проверки.

• А все ли требуемые поля добавлены в агрегат?

• Правильный ли тип данных задан у полей?

• И соответствуют ли единицы измерения требуемым?

• Какое минимальное и максимальное значение у нетекстовых полей?

Реальные примеры:

1. Часто при реализации задачи, один из разрезов теряется, например, место деятельности

2. В поле цена сделан строковый тип вместо числового

3. При использовании данных забывают, что цена указана в копейках/$, а не рублях/млн руб//млн $, и тем самым увеличивают данные о продажах

4. В данных цена парацетамола 1 000 000 рублей

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

Вывод: подмечаем все до малейших подробностей.

Правило 3. Выделяем главное

В данном агрегате главное, что мы считаем – это кол-во реализованного товара и сумму продаж.

Предлагаю сразу сверить эти метрики с источником. Считаем сколько реализовали товара в агрегате и на какую стоимость, аналогично считаем в источнике.

Посчитать можно с помощью простых запросов:

Агрегат

select 
sum(cnt) cnt_out,--Считаем кол-во проданного товара 
sum(cnt*price/100) sum_sale –Считаем сумму продаж в рублях
from agg.pharma_out

Источник

select 
count(sgtin) cnt_out,--Считаем кол-во проданного товара
sum(price/100) sum_sale –Считаем сумму продаж в рублях
from stg.pharma
where exit_type in (50,51,52)

Если в результате метрики сходятся, то мы можем сделать вывод о сходимости с источником: Источник=Агрегат

Если кол-во или сумма расходятся, то необходимо идти от общего к частному. Например, посмотреть распределение по датам, месяцам или годам продажи.

Правило 4. От общего к частному или дедуктивный метод

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

Выгружаем данные в разрезе Месяц/Год и сверяем результат в Excel

select 
toStartOfMonth(dt) as dt,
sum(cnt) cnt_out,--Считаем кол-во проданного товара 
sum(cnt*price/100) sum_sale –Считаем сумму продаж в рублях
from agg.pharma_out 
group by dt

Источник

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

Правило 5. От частного к общему. Индуктивный метод

Шерлок Холмс составлял общую картину о человеке, обращая внимание на детали. По прическе, виду ботинок или часов он мог сделать вывод о портрете владельца, чем занимается, какие беды пережил в жизни. Этот вид мышления называется индуктивным.

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

Вспомните, как Шерлок показал отчет за первую половину апреля мистеру Уотсону. Ошибочные данные были за каждый день. Это означает, что частная причина ошибки в данных скорее всего является общей. Мистер Уотсон ее выяснил, оказалось, что показываемые цифры были в млн $, а не в $.

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

Правило 6. Мыслим критически и учимся быть любопытными

Чем больше вопросов вы будете задавать, тем лучше. Критически анализируйте факты, ищите причины и объяснения, источники влияния и воздействия. Стройте логические цепочки и причинно-следственные связи. Умение задавать вопросы постепенно сформирует навык находить ответы.

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

Давайте генерировать гипотезы.

1. Возможно, отсекаются незаполненные места деятельности?

2. Возможно, не все идентификаторы лекарств попадают в агрегат?

3. Возможно, учтены только продажи?

4. В агрегате посчитана только часть данных, не все данные обработаны и агрегат не обновляется?

Правило 7. Развиваем память

Навык запоминания цифр сыграет вам добрую службу. Объясню почему.

Например, вы знаете, что при расчете продаж в прошлых месяцах выходила сумма +/- 1,3 млн $. При тестировании агрегата вы получает 8 млрд за последний месяц. Что-то не сходится, правда?).

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

Правило 8. Ведение полевых заметок

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

Не забывайте записывать! Иногда правильная мысль может прийти во время мытья посуды.

Правило 9. Нет ничего более неясного, чем очевидный факт

Приведу реальный пример.

В поле место деятельности должен записываться ИД места деятельности из потока данных. При построении отчета вдруг часть проданного товара не распределилась по местам деятельности.

В чем причина? Оказывается, в это же поле может быть записан и ИД участника.

Обязательно проверяйте возможные значения выборки!

Правило 10. Мыслите стратегически

Любое действие в настоящем ведет к последствиям в будущем. Наша цель – из всего извлекать пользу!

Работа по тестированию агрегата не изолирована от других этапов разработки и используется для автоматизации тестирования в системе контроля качества данных. Таким образом проведенное тестирование 1 раз далее автоматизируется и постоянно используется.

Завершение истории и раскрытие преступления

Шерлок Холмс научил сотрудников «Честное бюро» тестировать агрегаты. С помощью отчета в одной из аптек была найдена продажа лекарства «Оживин». По горячим следам Шерлок смог найти мошенников очень быстро. Правильные цифры и доверие к данным помогают добиваться превосходных результатов!

А что же дальше?

На примере лекарств можно делать не только простые проверки, но и выделить смысловые более сложные. Например,

1. разделить лекарства на типы и проанализировать динамику потребления по типам. В корректных данных по препаратам для лечения простудных заболеваний мы должны увидеть явную сезонность, т.к. потребление в осенне-зимний период активно растет;

2. распределить потребление по регионам, в правильных данных мы увидим самый высокий процент потребления в самых крупных регионах – Москве и Санкт-Петербурге. Остальные доли рынка можно попробовать сравнить с открытыми источниками;

3. проанализировать количество проданных лекарств по ценам продажи. Распределение чаще всего должно приближаться к нормальному. Т.к. только небольшую часть лекарств продают по очень низким или высоким ценам;

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

Лайфхаки улучшения навыков тестирования

1. Решать головоломки и логические задачи

2. Анализировать новости и генерировать гипотезы о причинах, которые за ними стоят

3. Наблюдать и замечать мелочи, строить гипотезы

4. Развивать память

5. Стройте стратегии и варианты развития событий при разных сценариях

Авторы статьи: Темникова Виктория, Югай Петр