Audit_helper в dbt: Поднимаем аудит данных на новый уровень

Аудит таблиц — это важная часть ежедневных задач аналитиков данных, особенно при рефакторинге таблиц, созданных с использованием SQL-хранимых процедур или Alteryx Workflows. В этой статье мы представляем, как пакет audit_helper может (как следует из названия) помочь в процессе аудита таблиц, чтобы убедиться, что рефакторинг модели дает (почти) такой же результат, как и оригинальная, основываясь на нашем опыте использования этого пакета для поддержки наших клиентов в Indicium Tech®.

Введение

Аналитикам данных (AE) и аналитикам часто приходится рефакторить (или переводить) хранимые процедуры SQLServer®, Alteryx Workflows® или другие инструменты моделирования в модели dbt, или даже рефакторить модель dbt для обновления ее источников данных. Также часто некоторые бизнес-правила применяются в разных моделях (и инструментах), и мы, как аналитики, должны убедиться, что результаты совпадают. Однако обеспечение совпадения значений в оригинальной таблице и в рефакторенной раньше было сложной задачей, требующей много ручного кодирования и некоторых общих тестов (таких как подсчет количества строк или суммирование всех значений в столбце).

Этот подход не только отнимает много времени, но и подвержен наивным предположениям о совпадении значений на основе агрегированных мер (таких как подсчеты или суммы). Чтобы предложить лучший, более точный подход к аудиту, dbt Labs создали пакет audit_helper. audit_helper — это пакет для dbt, основная цель которого — аудит данных путем сравнения двух таблиц (оригинальной и рефакторенной модели). Он использует простую и интуитивно понятную структуру запросов, которая позволяет быстро сравнивать таблицы на основе значений столбцов, количества строк и даже типов столбцов (например, чтобы убедиться, что данный столбец является числовым как в вашей таблице, так и в оригинальной). На рисунке 1 графически показан рабочий процесс и место audit_helper в процессе рефакторинга.

Рисунок 1 — Рабочий процесс аудита с использованием audit_helper

Теперь, когда понятно, где пакет audit_helper находится в процессе рефакторинга, важно подчеркнуть преимущества использования audit_helper (и, в конечном итоге, аудита рефакторенных моделей). Среди преимуществ можно упомянуть:

• Обеспечение качества: Убедитесь, что рефакторенная модель достигает того же результата, что и оригинальная модель, которая подвергается рефакторингу.
• Простой и интуитивно понятный код: Поскольку audit_helper опирается на макросы dbt, он был разработан как интуитивно понятный инструмент, работающий на простых SQL-запросах.
• Четкий вывод: Audit_helper предоставляет четкий вывод, показывающий, насколько ваша рефакторенная таблица соответствует оригинальной.
• Видимость проекта: Audit_helper дает видимость процесса рефакторинга, показывая, как ваш код дает те же результаты как в строковом, так и в столбцовом сравнении.
• Гибкость для сравнения строк и столбцов: Легко быстро сравнить результаты в строках или столбцах с помощью готовых шаблонов, которые требуют только указания имен ваших столбцов и оригинальных моделей.

Далее мы предоставим инструкции по установке audit_helper в ваш проект dbt, как запустить сравнение строк (с макросом compare_queries), сравнение столбцов (с compare_column_values) и дадим несколько советов по использованию audit_helper.

Инструкции по установке

Начнем с подготовки пакета audit_helper для работы в нашем проекте. Важно отметить, что пакет audit_helper должен работать на версиях dbt выше или равных 1.2.0 и ниже 2.0.0. Далее два шага помогут вам установить и запустить audit_helper.

1. Сначала добавьте файл packages.yml в ваш проект dbt, если его еще нет. Внутри файла packages.yml добавьте пакет audit_helper, скопировав и вставив код ниже. Обратите внимание, что этот файл должен находиться на том же уровне, что и ваш файл dbt_project.yml.

   packages:
   - package: dbt-labs/audit_helper
     version: 0.7.0

2. Запустите dbt deps в командной строке, чтобы установить пакет(ы). Пакеты устанавливаются в директорию dbt_packages — по умолчанию эта директория игнорируется git, чтобы избежать дублирования исходного кода пакета. Для получения дополнительной информации о использовании пакетов в вашем проекте dbt, ознакомьтесь с документацией dbt.

Теперь, когда audit_helper установлен, давайте поговорим о его двух основных макросах:

• compare_queries — для аудита строк
• compare_column_values — для аудита значений в столбцах.

Аудит строк (compare_queries)

Согласно документации пакета audit_helper, этот макрос полезен, когда:

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

Как это работает

Когда вы запускаете модель аудита dbt, она сравнивает все столбцы, строка за строкой. Чтобы засчитать совпадение, каждый столбец в строке из одного источника должен точно соответствовать строке из другого источника, как показано в примере на рисунке 2 ниже:

Рисунок 2 — Рабочий процесс аудита строк (compare_queries) с использованием audit_helper

Как показано в примере, модель сравнивается построчно, и в этом случае все строки в обеих моделях эквивалентны, и результат должен быть 100%. На рисунке 3 ниже показана строка, в которой два из трех столбцов равны, и только последний столбец строки 1 имеет расхождения в значениях. В этом случае, несмотря на то, что большая часть строки 1 идентична, эта строка не будет засчитана в итоговый результат. В этом примере только строка 2 и строка 3 являются действительными, что дает 66,6% совпадения в общем количестве проанализированных строк.

Рисунок 3 — Пример различных значений

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

Код для макроса compare_queries: пошагово

1. Создайте новую модель .sql в папке по вашему выбору
2. Скопируйте и вставьте следующий пример в созданную модель:

{# в dbt Develop #}


{% set old_fct_orders_query %}
select
    id as order_id,
    amount,
    customer_id
from old_etl_schema.fct_orders
{% endset %}


{% set new_fct_orders_query %}
select
    order_id,
    amount,
    customer_id
from {{ ref('fct_orders') }}
{% endset %}


{{ audit_helper.compare_queries(
    a_query=old_fct_orders_query,
    b_query=new_fct_orders_query,
    primary_key="order_id"
) }}

Давайте разберем аргументы, используемые в макросе compare_queries:

• primary_key (необязательный): Макрос принимает аргумент первичного ключа для объединения результатов запросов, но если в сравниваемых запросах его нет, вы можете создать его или просто удалить эту строку кода. Поскольку этот тип сравнения оценивает все значения в строке, он не покажет никаких различий, если первичный ключ не указан.
• summarize (необязательный): Этот аргумент позволяет переключаться между сводным или подробным (подробным) видом сравниваемых данных. Этот аргумент принимает значения true или false (по умолчанию установлено значение true).

3. Замените источники из примера на свои

   Рисунок 4 — Замена пути источников

   Как показано на рисунке 4, использование операторов ref позволяет легко ссылаться на вашу модель разработки, а использование полного пути упрощает ссылку на оригинальную таблицу (что будет полезно, когда вы рефакторите хранимую процедуру SQL Server или Alteryx Workflow, которая уже материализуется в хранилище данных).

4. Укажите столбцы для сравнения

   Рисунок 5 — Удаление и написание имен столбцов

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

   Когда в наборе данных много столбцов, начните с нескольких из них (скажем, 5 столбцов) и запустите; когда получите хорошее совпадение, добавьте больше столбцов и запустите снова. Вы также можете легко комментировать столбцы, которые не хотите сравнивать!

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

5. Запустите модель аудита, как вы бы запустили любую другую модель dbt, используя команду ниже:

   dbt run --select <name of your audit model>

6. Проверьте результат, скопировав и вставив код ниже в вашу среду разработки:

   select * from <name of your audit model>
   
   -- или select * from {{ ref('your_audit_model') }} если вы в dbt Cloud IDE

   Вывод будет похож на показанный на рисунке 6 ниже:

   Рисунок 6 — Пример вывода модели аудита запросов сравнения
   
   

   Вывод представлен в табличном формате, с объяснением каждого столбца ниже:

   
   

• IN_A: Данные, присутствующие в модели A
  

• IN_B: Данные, присутствующие в модели B
  

• COUNT: Количество строк
  

• PERCENT_OF_TOTAL: Процент от общего количества строк
  

  В первой строке мы видим TRUE в столбце IN_A и TRUE в столбце IN_B, что означает, что в обеих моделях есть 1,966,215 строк с совпадающими значениями всех столбцов, что составляет 97,65% от общего количества. Вторая строка показывает 20,543 строки из модели A, которые не совпадают напрямую с какой-либо строкой в модели B, а третья строка окончательно утверждает, что есть 26,720 строк из модели B, которые не совпадают напрямую с какой-либо строкой в модели A.

Дополнительно

Чтобы узнать разницу между ними (в приведенном выше примере, совокупная разница в 2,35%), объедините оба источника, используя первичный ключ, и разместите одни и те же столбцы рядом, и используйте оператор where, чтобы помочь вам найти, где один столбец отличается от модели A по сравнению с моделью B. Это исследование может быть первым шагом к определению возможной причины ошибки. Мы рекомендуем анализировать один столбец за раз.

Аудит столбцов (compare_column_values)

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

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

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

Действительно полезный способ проверить, какие конкретные столбцы снижают процент совпадения между таблицами, — это макрос compare_column_values, который позволяет нам проводить аудит значений столбцов. Этот макрос требует установки столбца, чтобы его можно было использовать в качестве якоря для сравнения записей между столбцом рефакторенной модели dbt и столбцом устаревшей таблицы. На рисунке 7 показано, как работает макрос compare_column_values.

Рисунок 7 — Рабочий процесс аудита строк (compare_column_values) с использованием audit_helper

Вывод макроса резюмирует статус совместимости столбцов, разбивая его на разные категории: полное совпадение, оба значения null, значения не совпадают, значение null только в A, значение null только в B, отсутствует в A и отсутствует в B. Этот уровень детализации упрощает для аналитика данных или аналитика выяснение, что может вызывать проблемы с несовместимостью между моделями. При рефакторинге модели часто бывает, что некоторые ключи, используемые для объединения моделей, несовместимы, что приводит к появлению нежелательных значений null в окончательной модели, и это вызовет сбой запроса аудита строк, не давая много дополнительных деталей.

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

Код для макроса compare_column_values: пошагово

1. Создайте новую модель .sql в папке по вашему выбору в вашем проекте dbt
2. Скопируйте и вставьте следующий пример в созданную модель:

{# в dbt Develop #}

{% set old_etl_relation_query %}
select * from public.dim_product
where is_latest
{% endset %}


{% set new_etl_relation_query %}
select * from {{ ref('dim_product') }}
{% endset %}


{% set audit_query = audit_helper.compare_column_values(
    a_query=old_etl_relation_query,
    b_query=new_etl_relation_query,
    primary_key="product_id",
    column_to_compare="status"
) %}


{% set audit_results = run_query(audit_query) %}


{% if execute %}
    {% do audit_results.print_table() %}
{% endif %}

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

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

Также мы видим, что в примерном коде включена функция печати таблицы по умолчанию. Это выводит результат запроса в терминал, когда макрос запускается во время шага компиляции dbt. Это может быть полезно для быстрого проверки статуса совместимости столбца, не покидая редактор кода при рефакторинге SQL через dbt.

3. Замените имена столбцов и ссылки на источники из примера на соответствующую информацию сравниваемых моделей.

4. Необязательно: Отключите команду print_table(), чтобы модель могла быть материализована в вашем хранилище данных. Чтобы отключить печать таблицы в терминале и включить материализацию модели в вашем целевом хранилище данных, можно сделать следующее: замените весь этот раздел кода, который объявляет SQL-модель как переменную и делает ее печатной, на шаблон выполнения макроса.

   - Замените закомментированный код ниже:
   - {% set audit_results = run_query(audit_query) %}
   
   - {% if execute %}
   - {% do audit_results.print_table() %}
   - {% endif %}
   
   - На следующий фрагмент кода:
   {{ audit_query }}

5. Чтобы получить результаты, вы можете просто запустить модель, как вы бы сделали с обычной моделью dbt, используя следующую команду:

   dbt run --select <name of your audit model>

   Но в отличие от макроса compare_queries, если вы оставили функцию печати включенной, вы должны ожидать, что таблица будет напечатана в командной строке, когда вы запустите модель, как показано на рисунке 8. В противном случае она будет материализована в вашем хранилище данных следующим образом:

   Рисунок 8 — Пример таблицы, напечатанной в командной строке

   Макрос compare_column_values разделяет результаты аудита столбцов на семь различных меток:

   • Полное совпадение: количество строк (и относительный процент), где значения столбцов, сравниваемые между обеими таблицами, равны и не равны null;
   • Оба значения null: количество строк (и относительный процент), где значения столбцов, сравниваемые между обеими таблицами, равны null;
   • Отсутствует в A: количество строк (и относительный процент) с значениями столбцов, которые существуют в таблице B, но не в таблице A;
   • Отсутствует в B: количество строк (и относительный процент) с значениями столбцов, которые существуют в таблице A, но не в таблице B;
   • Значение null только в A: количество строк (и относительный процент) с значениями столбцов, которые не равны null в таблице B, но равны null в таблице A;
   • Значение null только в B: количество строк (и относительный процент) с значениями столбцов, которые не равны null в таблице A, но равны null в таблице B;
   • Значения не совпадают: количество строк (и относительный процент), где значения столбцов, сравниваемые между обеими таблицами, различны и не равны null.

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

Ссылки

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

• Репозиторий GitHub Audit_helper
• Пост dbt Labs об аудите данных "Как не сойти с ума при аудите данных, часть 1"
• Пост dbt Labs об аудите данных "Как не сойти с ума при аудите данных, часть 2"
• Пост dbt Labs о миграции от хранимых процедур SQL к моделям dbt