Недавно я обсуждал с коллегой, как выполнять модульные тесты в SQL, и я понял, что, хотя я все еще в начале своей карьеры в области данных, я уже несколько раз сталкивался с этой проблемой и каждый раз подходил к ней по-разному. Найди решение. Я подумал, что было бы полезно поделиться своими лайфхаками, чтобы я мог закрепить то, что я узнал, а также узнать от вас, читатель , что вы сделали и как все прошло. А не ___ ли нам?

Но зачем модульное тестирование SQL?

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

Модульное тестирование SQL аналогично модульному тестированию кода приложения: убедитесь, что часть логики выдает ожидаемый результат для заданного набора входных данных. В то время как лучшие практики требуют модульного тестирования почти всего кода приложения, будь то C++ или Python, мы редко сталкиваемся с попытками сделать это с SQL. Почему?

Для меня ответ заключается в том, что написание SQL — не самая сложная часть . В большинстве случаев преобразования данных являются тривиальными и повторяющимися, и сложность заключается скорее в правильном моделировании данных, чем в перемещении битов из A в B. Однако, когда в преобразованиях необходима бизнес-логика, вы можете столкнуться с необходимой сложностью. разбиваться на части — как сложное выражение CASE-WHEN для создания столбца, с входными данными, поступающими от устрашающего семистороннего соединения, и многострочного предложения WHERE, которое гарантирует таблицу истинности, чтобы понять, что происходит…

По моему опыту, проверка правильности логики зависела только от ручного тестирования и подтверждения клиента, что 1) хрупко и 2) почти преступно , учитывая, насколько хорошо SQL подходит для модульного тестирования! В то время как приложения требуют всевозможных каркасов для ограничения изменяемого состояния и обеспечения воспроизводимости в тестах, SQL является чистым и явным. Так почему бы не использовать ту же инфраструктуру для проверки правильности работы логики?

Первая попытка: аналитика

Моя первая попытка модульного тестирования SQL-скриптов была, когда я работал в Google, используя внутренние технологии, такие как Dremel и Powerdrill (вы заметите, что их проекты имели названия, связанные с работой с журналами ).

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

CREATE TABLE _7dau_by_country_platform AS
SELECT
  day,
  country,
  platform,
  COUNT(DISTINCT user_id) AS active_users
FROM UNNEST(dates) day
JOIN app_logs ON DATE(ts) BETWEEN DATE_SUB(day, INTERVAL 7 DAY) AND day
GROUP BY 1, 2, 3

Взлом тестового фреймворка

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

1. Каждая исходная таблица будет иметь синтетический CSV с множеством заполненных пользователей и столбцов. Для каждого нового теста мы добавляли строки в соответствующие исходные таблицы.
2. При выполнении теста весь набор данных загружался во временные таблицы в хранилище данных.
3. Для каждого скрипта исходники текстуально заменялись временной таблицей, и скрипт выполнялся.
4. Для каждого скрипта мы создали ожидаемый выходной CSV-файл и сравнили его с фактическим выходным файлом, сообщая обо всех различиях.

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

Авария

Через некоторое время добавление нового тестового примера стало слишком хрупким , поскольку потенциально вы могли повлиять на любое количество других тестов! Если тесту A необходимо вычислить относительную частоту и для этого подсчитать все строки с помощью COUNT(*), в тот момент, когда мы добавим новую строку в «золотой» набор данных для теста B, ожидаемый результат теста A будет нарушен.

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

• Максимально изолируйте тесты . Модульный тест имеет ценность, потому что он ломается, когда предположения об одном компоненте перестают выполняться. Его поломка из-за того, что какой-то несвязанный тест изменил общую среду, бесполезна.
• CSV - ужасный формат файлов . Серьезно. Если бы мы хотели проверить, как логика ведет себя со строкой NULL по сравнению с пустой строкой, нам нужно было бы ввести специальное значение и логику преобразования на этапе загрузки. Неудивительно, что pandas.read_csv имеет 52 аргумента для обработки всех случаев, которые CSV не может стандартизировать.
• Хотя тесты выигрывают от простоты и ясности, синтетические данные слишком многословны и затрудняют определение того, чем один тест отличается от другого. Мы в значительной степени полагались на комментарии, чтобы прояснить назначение каждой строки и тестового примера (опять же, это не стандартизировано в CSV).

Вторая попытка: выход клиента с помощью JSON

В моем втором опыте я работал консультантом по обработке данных в Zup Innovation, прикомандированный к команде в Итау, крупнейшем банке в Латинской Америке. Мы работали с локальным развертыванием Hadoop.

Тип работы здесь сильно отличался от аналитики, в основном заключаясь в выравнивании и дедупликации записей. Основная проблема возникает из-за того, что сценарии являются инкрементными , когда результат данного преобразования зависит от вывода предыдущего. Обычно мы тестировали его в два этапа: мы назвали T0 запуском без предыдущего состояния, а T1 — запуском, основанным на T0.

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

Основная трудность заключалась в том, чтобы преобразовать этот список тестовых случаев в фактические входные данные Hadoop! Сначала у нас был следующий процесс:

1. Мы создали общий файл Excel, где каждая вкладка представляла собой таблицу либо из первого (T0), либо из второго запуска (T1). Каждый тестовый пример станет 0–2 строками в каждой таблице.
2. Мы выводим каждую таблицу T0 в виде CSV, выполняем косметические преобразования, загружаем их в Hadoop и выполняем сценарий SQL. Выход T0 собирается.
3. Мы выводим каждую таблицу T1 в формате CSV, выполняем косметические преобразования, загружаем их в Hadoop и выполняем сценарий SQL. Выход T1 собирается.

Взлом генератора таблиц

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

{
  "companies_t0": [
    {"company_id": "001-3", "company_name": "ACME Inc."},
    {"company_id": "002-9", "company_name": "Umbrella LLC"}
  ],
  "companies_t1": [
    {"company_id": "001-3", "company_name": "ACME Inc."}
  ],
  "associates_t0": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"}
  ],
  "associates_t1": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"} 
  ],
  "persons": [...]
}

Инструмент собрал все строки из каждого теста и создал файлы company_t0.csv, company_t1.csv и т. д., которые затем были загружены в Hadoop. Когда ошибка была обнаружена, мы могли перейти к файлу JSON и проверить, не была ли это ошибка в коде тестового примера, что было намного проще сделать, чем искать конкретную строку во многих вкладках Excel, которые могли повлиять на данный тестовый пример. .

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

Новые уроки

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

Я извлек из этого следующие уроки:

• Бизнес мыслит случаями, охватывающими таблицы, а код думает таблицами, содержащими тестовые случаи . При создании тестового жгута между ними нужно выполнить транспонирование.
• Когда клиент пишет для вас тест-кейсы, это золото . Никто лучше не знает, каковы соответствующие входные данные и ожидаемые результаты, чем эксперт в предметной области! Они могут не думать о каждом пограничном случае, который должен обрабатывать код, но они могут дать представление о нескольких тестовых примерах сложных преобразований. Кроме того, установление общего языка ввода-вывода поможет вам позже сообщать об этих крайних случаях!
• Одновременный запуск всех тестовых случаев усложняет проверку и отладку . Нам пришлось сохранить сопоставление между идентификатором пользователя и тестовым случаем, чтобы сопоставить результаты с тестовыми примерами и проверить их. Мы пытались добавить номер тестового примера к идентификатору пользователя, пометив данные «внутриполосно», но это только принесло больше проблем с недействительными идентификаторами. Было бы лучше либо запускать каждый случай отдельно, либо иметь способ отслеживать, какому случаю соответствует данный вывод.
• JSON не является хорошим форматом для людей. В приведенном выше примере не указано явно, что тестируется, и JSON не позволяет объяснять это комментариями. Да, мы можем использовать "_comment": "blah"поле, но это хак, который нужно отменить при преобразовании в CSV. Кроме того, отслеживание запятых и цитирование ключей объектов слегка раздражает .
• Шаблоны строк значительно улучшают читаемость. Хотя мы хотели бы, чтобы тесты были очень явными и с небольшим количеством скрытой логики, использование шаблона для заполнения неинтересных столбцов очень помогает в обнаружении различий между значениями, которые действительно важны для рассматриваемого тестового примера.

Этот третий и последний опыт произошел с моим предыдущим работодателем, Cherre, который обеспечивает передачу данных для компаний, занимающихся недвижимостью. В основном мы использовали dbt для написания преобразований в Google BigQuery, коммерческом предложении Dremel. Клиент попросил нас значительно изменить логику (к счастью, сделать ее проще!), и мы хотели убедиться, что не внесем никаких непреднамеренных изменений.

Повторное использование моделей БДТ

Поскольку существующая логика не подвергалась модульному тестированию, я начал с дымового теста , который просто создает входные данные и проверяет, ничего ли с ними не ломается. Граф зависимостей тестируемой модели был глубоким, оканчивающимся примерно на 20 источников, поэтому было бы головной болью имитировать источник за источником. Вместо этого я выбрал подходящее отсечение DAG и заменил только ~7 ссылок синтетическими данными, созданными с помощью самого SQL :

WITH
-- Synthetic sources
foo AS (
  -- Test case 1, active foo
  SELECT "i001" AS foo_id, 200_000 AS foo_value, TRUE AS is_active,
  UNION ALL
  -- Test case 2, inactive foo without any bar
  SELECT "i002" AS foo_id, 240_000 AS foo_value, FALSE AS is_active,
),
bar AS (
  -- Test case 1, active foo with two bars
  SELECT "b001" AS bar_id, 451.40 AS bar_value, "i001" AS foo_id
  UNION ALL
  SELECT "b002" AS bar_id, 123.98 AS bar_value, "i001" AS foo_id
),
-- From now on, everything should be left the same as the DBT model.
model_x AS (
  SELECT ...
  FROM foo
  JOIN bar USING (foo_id)
)

Чтобы лучше визуализировать и совместно использовать входные и выходные данные, я создал копию сценария для каждого из CTE (т. е. для каждой из промежуточных таблиц) и добавил SELECT * FROM <cte>в конце. Затем я выполнял каждую копию, получая CSV для каждого промежуточного преобразования — foo.csv, bar.csv, model_x.csv и т. д. Это помогло отладить каждый шаг DAG, а также предоставило возможность поделиться с нетехническими люди, каковы будут результаты логических изменений, учитывая некоторые синтетические входные данные, в формате CSV.

Ограниченная прибыль

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

Основные уроки до сих пор были следующими:

• SQL — это вариант с наименьшим несоответствием сопротивления для объявления синтетических данных. Записывая синтетические данные на чистом SQL, нам не нужно беспокоиться о приведении, NULL, комментариях, удобочитаемости… поскольку сам синтаксис языка уже обеспечивает их!
• Важно издеваться над рефами. Самая сложная часть логики SQL находится в конце большой DAG, и необходимость имитировать каждый из источников была бы контрпродуктивной. Подделывая источники, мы могли бы протестировать все промежуточные модели… но я не хотел тестировать их все, только интересные!
• Группировать строки по тест-кейсам лучше, чем по таблице. Я уже узнал об этом в предыдущем опыте, но хотел бы повторить, что даже при написании тестовых случаев на SQL с комментариями некоторый контекст все равно теряется при чтении случаев и возврате от выходных данных к тестам.

Я не знаю, сможем ли мы найти идеальное решение для модульного тестирования SQL, но мы все еще можем об этом мечтать:

• Тестовые случаи будут выполняться изолированно;
• Каждый вывод будет указывать, из какого случая он взят;
• Неинтересные столбцы могут быть заполнены автоматически и/или проигнорированы;
• Вся логика используется повторно, никаких доработок для тестирования не потребуется;
• Точка отсечки графа зависимости может быть произвольной;
• Тестовые примеры и их результаты должны быть понятны и написаны экспертом в предметной области/специалистом по бизнесу;

Эпилог

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

Спасибо Дэвиду Бареру, Бруно Сантосу и Родриго Доницетти за доступность и рецензирование черновиков этого поста.