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‑процесс включает три основных этапа:

1. Извлечение данных из источников.

2. Очистка и трансформация.

3. Загрузка в целевую систему.

Требования к backend

• Работа с целевыми источниками: локальное хранилище (ранее созданные датасеты), а также БД (PostgreSQL, MySQL, MSSQL, Clickhouse, Oracle, Vertica, YandexDB)

• Возможность формирования превью итогового датасета в процессе объединения таблиц.

• Гарантия целостности и достоверности данных на выходе.

Требования к интерфейсу

• Интуитивный UI с блочной структурой и drag‑and‑drop‑настройками.

• Текстовые подсказки, направляющие пользователя.

• Нотификации — прозрачная индикация статуса операций.

Дополнительные требования, которые мы выдвинули к нашему ETL

• Реализовать возможность работы как при локальном подключении, так и при прямом, обеспечив при этом актуальность данных ETL‑датасета и дополнительное место на дисковом пространстве. 

• Поддержка No‑Code (то есть через поля на фронте, без ручного ввода запросов) и SQL‑режима (управление колонками и данными через sql‑запросы).

• Корректная обработка расчетных колонок при работе с ETL датасетом.

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

Разработка ETL

С точки зрения backend‑реализации ETL‑процесс был декомпозирован на несколько последовательных шагов:

1. Подключение к источнику — это могут быть файлы, БД, CRM, облачные сервисы и др.

2. Выгрузка данных из источника — получение сырых данных для дальнейшей обработки. Местом их выгрузки будет служить PostgreSQL (также может подойти ClickHouse)

3. Преобразование и очистка данных перед последующей консолидацией. Преобразования, о которых идет речь, могут быть совершенно разными: очистка данных, удаление дубликатов, шифрование некоторой информации и прочее. Для решения таких задач можно использовать мощный SQL или отдельные инструменты предобработки данных (DataWrangler,OpenRefine и тп). При этом осуществляется валидация полноты и корректности данных и сигнализация об ошибках.

4. Агрегация — предварительная группировка данных и расчет агрегированных показателей.

5. Создание 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 стало точкой качественного роста продукта. Решение позволило повысить надежность интеграции данных, упростить сценарии работы пользователей и сделать систему более предсказуемой с точки зрения эксплуатации и аналитики.

Спасибо за внимание!