ETL-витрина данных: CDC вместо полной перезагрузки

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

Что было до

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

Когда это окно было четыре часа - всё укладывалось. Когда база выросла, пайплайн стал отваливаться в 3:40 утра, не доделав заливку. Витрина к утренней сессии аналитиков приходила либо пустая, либо наполовину залитая из предыдущего прогона. Оба варианта - плохо.

Диагностика

Первый вопрос: где именно теряется время? Замерили каждый шаг.

• Полный SELECT из источника - занимал примерно треть времени. База без нормальных индексов на поля с датами, запрос без фильтрации тянул всё подряд.
• TRUNCATE целевой таблицы - быстро, но вынуждает пересобирать индексы после INSERT.
• Массовый INSERT - самое долгое. Транзакция на несколько миллионов строк, плюс логирование.

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

CDC как решение

Change Data Capture - идея простая: не тащить всю таблицу каждый раз, а брать только то, что изменилось с последнего запуска. Технически вариантов несколько: триггеры на источнике, log-based CDC через журнал транзакций, временные метки на строках. Мы выбрали триггеры - они работают на любом SQL-движке без доступа к бинарным логам и без дополнительного агента на стороне СУБД.

На источнике завели отдельную таблицу изменений - что-то вроде staging-журнала: при каждом INSERT/UPDATE/DELETE на основной таблице фактов триггер пишет запись в журнал с типом операции и временной меткой. ETL-джоб теперь читает только этот журнал с момента последнего успешного прогона, применяет изменения к DWH по ключу и отмечает позицию.

-- Упрощённая структура журнала изменений
CREATE TABLE facts_cdc_log (
    log_id     BIGINT PRIMARY KEY AUTO_INCREMENT,
    op         CHAR(1) NOT NULL,   -- I, U, D
    fact_id    BIGINT NOT NULL,
    changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    payload    TEXT
);

Триггер на INSERT/UPDATE/DELETE пишет в этот журнал. ETL-джоб выбирает строки где changed_at > last_run_ts, обрабатывает их последовательно и обновляет метку последнего прогона.

Что изменилось

Первый прогон после внедрения - 22 минуты. Не 25, как мы рассчитывали, чуть меньше - основная часть времени ушла на первичный прогон с полной загрузкой для инициализации. Последующие прогоны держатся в диапазоне 20-30 минут в зависимости от объёма ночных изменений в источнике.

Несколько вещей, которые выяснились в процессе:

• Порядок применения операций важен. Если в журнале за ночь один и тот же ключ сначала UPDATE, потом DELETE - нужно применять в хронологическом порядке, иначе можно воскресить удалённую строку.
• Журнал надо чистить. Без регулярного DELETE из facts_cdc_log он сам превратится в проблему через несколько месяцев.
• Полная перезагрузка должна остаться как fallback. На случай если джоб упал посередине и позиция съехала - нужна кнопка «пересчитать с нуля».

Что это не решает

CDC через триггеры добавляет нагрузку на источник при каждой транзакции. Для OLTP-базы с высоким write-throughput это может быть критично. В нашем случае источник - относительно спокойная учётная система, нагрузка на триггеры оказалась незначительной. Но это надо измерять, не угадывать.

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

В итоге: задача решена, клиент доволен, витрина к утренней сессии приходит вовремя. Подход не универсальный, но для систем с умеренным write-throughput и чётким ночным окном - вполне рабочий.