ETL без боли: переписали ночную загрузку DWH с 6 часов до 40 минут

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

Что пришло вместе с запросом

Клиент - оптовый торговец, около 50 человек в офисе, SQL Server 2005 в качестве и транзакционной базы, и хранилища. Витрина продаж кормит несколько SSRS-отчётов и Excel-сводок для коммерческого директора. Жалоба простая: «ночная загрузка занимает 6 часов, к утру иногда не успевает, люди приходят и видят вчерашние данные».

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

Смотрим что там написано

SSIS-пакет оказался незатейливым. Логика примерно такая: TRUNCATE витрины, потом INSERT ... SELECT из нескольких исходных таблиц с джойнами. Каждый раз полностью. Объём исходных данных - порядка 5 миллионов строк в таблице документов и ещё несколько связанных справочников.

Полная перегрузка каждую ночь - это классическое решение «работает и ладно», которое нормально живёт пока данных мало, а потом начинает ехать по времени вместе с ростом базы. За два года накопилось достаточно, чтобы 6 часов стали нормой.

Профилирование показало очевидное: большую часть времени съедают джойны при SELECT и перестройка индексов после TRUNCATE/INSERT. Причём менялось за ночь обычно от силы несколько тысяч строк из пяти миллионов - закрытые накладные, исправленные документы, новые отгрузки за день.

Как переписали

Решение очевидное, но требует аккуратной реализации - инкрементальная загрузка с чекпоинтом.

В транзакционной базе у документов есть поле modified_at - timestamp последнего изменения. Оно уже было, просто ETL его игнорировал. Добавили служебную таблицу etl_checkpoint с одной строкой: последний успешно обработанный timestamp.

Логика нового пакета:

• Читаем чекпоинт - берём last_run из etl_checkpoint.
• Тянем дельту - SELECT из источника WHERE modified_at > last_run. Вместо 5 миллионов строк - обычно несколько тысяч.
• Обновляем или вставляем - MERGE (в SQL Server 2005 это MERGE через синтаксис с OUTPUT, либо UPDATE + INSERT по условию). Обновляем существующие записи, вставляем новые.
• Пишем новый чекпоинт - только если весь пакет прошёл без ошибок.

Последний пункт важен: чекпоинт обновляется в конце, в одной транзакции с завершением записи в витрину. Если пакет упал на середине - при следующем запуске он возьмёт тот же last_run и перезагрузит дельту заново. Идемпотентность обеспечивает MERGE: повторная загрузка тех же строк не создаёт дублей.

Отдельно учли удаления: если документ удалили в источнике, через modified_at это не поймаешь. Сделали отдельную короткую процедуру - раз в неделю сверка ключей между источником и витриной, мягкое удаление через флаг is_deleted. Для этого клиента удаления редкие, ночная пробежка по ключам занимает минуты.

Что получили

Время ночной загрузки - 35-40 минут в среднем, до 50 при большом дне (закрытие периода, массовое перепроведение). Это в 8-9 раз быстрее исходного. Окно обслуживания освободилось: резервное копирование встало на 2 ночи, перестройка индексов - на 3, загрузка стартует в 5 и заканчивает задолго до прихода первых сотрудников.

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

Что стоит иметь в виду

Поле modified_at должно быть надёжным. Если в источнике есть обходные пути записи в базу, которые не трогают этот timestamp - дельта будет неполной. У этого клиента обновления шли только через приложение, поэтому проблемы не было. Но перед переходом на инкрементальную загрузку стоит убедиться в этом явно.

MERGE в SQL Server 2005 требует аккуратности. Синтаксис есть, но при определённых условиях гонки он ведёт себя не так как ожидается - известная история с параллельными сессиями. У нас загрузка однопоточная, поэтому обошлось, но в более нагруженных сценариях это надо держать в голове.

Начальная загрузка остаётся полной. Инкрементальность работает на операционной фазе; первоначальное наполнение витрины всё равно делается через TRUNCATE + полный INSERT. Это одноразово, но важно не забыть при смене сервера или пересоздании схемы.

Работа по построению и оптимизации DWH нередко начинается именно с таких аудитов: хранилище есть, отчёты есть, а вот процессы загрузки писались второпях и теперь тормозят рост. Инкрементальная загрузка - не rocket science, но требует понимания источника данных и аккуратной обработки крайних случаев.