ETL — не новая технология, и на уровне концепций здесь давно нет сюрпризов. Но как только ETL перестает быть учебным примером и становится частью промышленного BI‑продукта, неизбежно появляются сложности на этапах реализации и тестирования.
Всем привет, меня зовут Михайлов Михаил, руководитель отдела тестирования Polymatica BI. В сегодняшней статье я расскажу, какие задачи команда решала при создании собственного ETL‑механизма, какие проектные решения оказались критичными и почему стандартных подходов к тестированию здесь недостаточно. Материал будет полезен тестировщикам, QA‑инженерам и всем, кто работает с интеграцией данных и занимается вопросами обеспечения устойчивости ETL в условиях реальной эксплуатации.
Кому и зачем нужен ETL
Сегодня работа с данными — будь то данные о клиентах, заказах, логистике, финансах, маркетинге — неотъемлемая часть большого бизнеса. Данные не просто «есть» — они динамичны: во‑первых, поступают и накапливаются, со временем требуя систематизации и актуализации, и во‑вторых, перемещаются по разным базам данных и репозиториям, подобно крови, циркулирующей по артериям проекта. Своевременная подача крови позволяет проекту жить и здравствовать — для этого и нужен ETL.
ETL выступает связующим звеном между источниками данных и аналитическими системами. Его ключевая задача — обеспечить управляемый и воспроизводимый поток данных, пригодных для анализа и дальнейшего использования.
Основные преимущества ETL
Централизация и доступность данных. Назначение ETL — сбор информации из разных источников (БД, CRM, файлов, Excel‑таблиц, внешних сервисов и тому подобное) и загрузка в единое хранилище.
Качество и согласованность данных. После сбора и перед размещением ETL преобразует разные данные, приводя к нужному формату, очищая, нормализуя и контролируя корректность перед загрузкой.
Автоматизация и экономия времени. С помощью ETL сбор и унификация нужных данных из разных источников происходит в разы быстрее, снимая рабочую нагрузку с команды и высвобождая время на более важные задачи.
Аудит и трассируемость. Использование ETL позволяет отследить источник данных, изменения и историю загрузок.
Типовые сценарии использования
Миграция данных и построение аналитического хранилища (DWH). ETL помогает «подружить» между собой доселе несовместимые форматы данных и разместить их в единой системе.
Репликация данных. ETL используется для автоматизации регулярной или потоковой синхронизации с хранилищами.
Бизнес‑аналитика и маркетинг. Консолидация данных (о продажах, клиентах и так далее) позволяет применять их для построения отчетности и дальнейшего анализа, что помогает при оценке результатов и корректировке бизнес‑стратегии.
Масштабирование. Благодаря эффективной работе с растущими объемами информации ETL выступает одним из важных элементов в работе с big data.
Машинное обучение (ML). С помощью ETL можно сначала фильтровать данные, выбирая только важное и нужное, затем преобразовывать и загружать в хранилище для машинного обучения.
Для того, чтобы наш ETL стал помощником в работе с данными, мы серьезно подошли к реализации этого решения, уделив должное внимание архитектуре и проектированию и заложив солидный запас времени и ресурсов на разработку, тестирование и мониторинг. Но обо всем по порядку.
Проектирование ETL
Целью проекта было создание ETL‑решения, позволяющего пользователю загружать, объединять и трансформировать данные без необходимости обращаться к внешним инструментам.
Классический ETL‑процесс включает три основных этапа:
Извлечение данных из источников.
Очистка и трансформация.
Загрузка в целевую систему.
Требования к backend
Работа с целевыми источниками: локальное хранилище (ранее созданные датасеты), а также БД (PostgreSQL, MySQL, MSSQL, Clickhouse, Oracle, Vertica, YandexDB)
Возможность формирования превью итогового датасета в процессе объединения таблиц.
Гарантия целостности и достоверности данных на выходе.
Требования к интерфейсу
Интуитивный UI с блочной структурой и drag‑and‑drop‑настройками.
Текстовые подсказки, направляющие пользователя.
Нотификации — прозрачная индикация статуса операций.
Дополнительные требования, которые мы выдвинули к нашему ETL
Реализовать возможность работы как при локальном подключении, так и при прямом, обеспечив при этом актуальность данных ETL‑датасета и дополнительное место на дисковом пространстве.
Поддержка No‑Code (то есть через поля на фронте, без ручного ввода запросов) и SQL‑режима (управление колонками и данными через sql‑запросы).
Корректная обработка расчетных колонок при работе с ETL датасетом.
После фиксации системных требований к задаче приступила команда разработки.
Разработка ETL
С точки зрения backend‑реализации ETL‑процесс был декомпозирован на несколько последовательных шагов:
Подключение к источнику — это могут быть файлы, БД, CRM, облачные сервисы и др.
Выгрузка данных из источника — получение сырых данных для дальнейшей обработки. Местом их выгрузки будет служить PostgreSQL (также может подойти ClickHouse)
Преобразование и очистка данных перед последующей консолидацией. Преобразования, о которых идет речь, могут быть совершенно разными: очистка данных, удаление дубликатов, шифрование некоторой информации и прочее. Для решения таких задач можно использовать мощный SQL или отдельные инструменты предобработки данных (DataWrangler,OpenRefine и тп). При этом осуществляется валидация полноты и корректности данных и сигнализация об ошибках.
Агрегация — предварительная группировка данных и расчет агрегированных показателей.
Создание ETL‑датасета — завершающий шаг. Для вставки данных непосредственно в базу подойдет обычный SQL‑запрос, на ui — индикация с текущим статусом процесса и готовый датасет по завершении загрузки.
На этом backend‑разработка решения заканчивается: помимо написанного, там есть и иные нюансы, но погружаться в детали не будем — все‑таки статья не об этом.
На стороне интерфейса было принято решение реализовать единое рабочее окно, разбитое на блоки, вместо нескольких последовательно открывающихся окон. Таким образом все, что выбирает и настраивает пользователь, всегда остается у него перед глазами и не требует дополнительных кликов «назад — вперед» для корректировок.
Далее, исходя из поставленных задач (в главе «Проектирование ETL»), окно было поделено на функциональные блоки:
выбор источников (включая локальное хранилище) и таблиц с возможностью поиска;
отдельный блок для работы с колонками выбранных таблиц и расчетными колонками;
превью данных, в котором выводятся 15 первых строк будущего датасета или информация о полученной бэкендом ошибке при попытке создать превью;
блок в центральной части страницы для настройки объединения выбранных пользователем таблиц: либо поле для ввода SQL‑запросов, либо поля с выпадающими списками таблиц и колонок выбранной таблицы.
Для удобства пользователей процесс создания и редактирования ETL датасета у нас сопровождается уведомлениями и подсказками:
индикация ошибок при подключении к источнику (например, при неверно заполненных полях для подключения);
индикация ошибки при получении превью (если в SQL‑запросе допущены ошибки, часть данных недоступна и так далее);
нотификации о статусе операций (например, при смене режима с локального на прямое подключение или наоборот);
тултипы;
отдельная страница с инструкцией для пользователя.
Поскольку ETL — нагруженная и сложная в реализации фича, включающая в себя как фундаментальные моменты и решения, так и разного рода мелочи, то остро встает вопрос: а как правильно и в срок протестировать такую громадину?
Тестирование ETL: практические сложности
Тестирование ETL‑решений имеет свою специфику. Не стану пересказывать базу про этапы тестирования — лучше уделим внимание моментам, которые на старте не казались очевидными, однако в ходе проверок неприятно нас огорошили.
Колонки и синхронизация
Одной из проблем стала рассинхронизация между UI и backend. Список колонок ETL‑датасета после редактирования отличался от того, что тестировщик ранее сформировал в настройках, при этом ошибок при сохранении не было. Позднее выяснилось, что список мог продолжать обновляться на бэке, в то время как внешне процесс редактирования датасета уже был завершен, то есть возникала задержка между запросом на сохранение и фактическим обновлением колонок. Для решения проблемы в настройки датасета была добавлена индикация обновления, происходящего после клика на «сохранить».
Расчетные колонки
Не давали нам покоя и расчетные колонки (РК). Помимо легко решаемых багов в духе «в окне редактирования чек‑бокс РК по умолчанию выключен», находились проблемы и посерьезнее. Например, при расчете возникали ошибки, если колонки для расчета брались из нескольких разных таблиц, а не из одной, как в локальных датасетах. Исправление этого бага обнажило другую проблему: РК ломались, если из области трансформации удаляли таблицу, колонки которой теперь успешно использовались в расчете; это привело нас к еще одной небольшой доработке, в которой мы заменили передаваемое для РК значение с алиасов таблиц на уникальный идентификатор типа '${source_id}‑${name_table}'.
SQL‑запросы и алиасы
Несмотря на скрупулезность, с которой мы подошли к проектированию, некоторые моменты все же смогли ускользнуть от нашего взора до начала тестирования. Отдельного внимания потребовала обработка SQL‑запросов, содержащих колонки с названием или псевдонимом «id». Покопавшись в получаемых ошибках и логике работы бэка мы выяснили, что пользовательский запрос преобразуется таким образом, чтобы использовать t1, t2 и t3 вместо исходных названий таблиц (используя with), плюс названия колонок меняются на системные, и в запросе «SELECT „s1“.id as „col_0“, „s1“.id as „col_1“, „s1“.id as „col_2“» у «s1» имеется три колонки с одним названием («id»), что и вызывает ошибку column reference «id» is ambiguous. По сути, описанное поведение багом не является, поскольку написание SQL‑запросов с заданием уникальных алиасов это best practices, однако ситуация в очередной раз напомнила нам о том, что «очевидное» может быть таковым не для всех.
JOIN‑операции
Были выявлены сценарии, при которых корректные на первый взгляд JOIN‑запросы приводили к созданию пустых датасетов: они корректно обрабатывались, когда обращение шло либо по названию таблицы из БД, либо по названию таблицы в системе (t1, t2 и так далее), однако SQL вида:
select t1.id as t1_id, t2.id as t2_id from demo_table t1 left join demo_table1 t2 on t1.id = t2.id
не захватывал данные, в результате чего создавался пустой датасет с ошибкой. Такие случаи потребовали доработки парсинга и обработки SQL.
Подборка тайтлов некоторых нетривиальных багов
Главу хочется завершить на позитивной ноте, поэтому вашему вниманию представлена небольшая подборка багов, пойманных при попытке бегства с фича‑стенда на stage:
Закрывается окно создания датасета при клике на «Прямое подключение», если вынести таблицы из разных источников
При смене джоина название кнопки «Очистить» меняется на выбранный джоин
Все типы данных в таблицах из БД определяются как строковые
После создания датасета у РК пропадает название
В окне изменения структуры в блоках объединения вместо «очистить» указан тип джоина
Поиск по цифрам не работает в источниках и таблицах
При создании расчетной колонки ее тип всегда определяется как строковый
Можно создать ETL датасет без колонок
Итоги
По завершении тестирования вся команда с облегчением выдохнула. Хотя объем работы по реализации ETL и оказался большим, чем ожидали на старте, стоит отдать должное команде проектирования за смелые решения и команде разработки за эрудированность и оперативность в решении ошибок. В обнаружении ошибок не последнюю роль сыграли два кросс‑ревью, проведенных моей командой перед тестированием и после первой итерации тестирования. О том, как можно организовать процесс кросс‑ревью, я писал в другой статье на Хабре.
По сумме написанного в статье могу с полной уверенностью подытожить: преимущества ETL перевешивают сложности в его реализации. Грамотное внедрение ETL, основанное на продуманной масштабируемости, правильном выборе инструментов и надежном тестировании с мониторингом, превращает его в незаменимого союзника в работе с данными и открывает путь к принятию более точных бизнес‑решений, существенной экономии времени и ресурсов, а также к гибкой реакции на динамику рынка. Для Polymatica BI внедрение собственного ETL стало точкой качественного роста продукта. Решение позволило повысить надежность интеграции данных, упростить сценарии работы пользователей и сделать систему более предсказуемой с точки зрения эксплуатации и аналитики.
Спасибо за внимание!
