CDC в SQL Server 2008 и инкрементальная загрузка SSIS: с 4 часов до 25 минут

Один из клиентов - средний ритейлер с несколькими торговыми точками - давно жил с ночным ETL, который к утру едва успевал заканчиваться. Процесс стартовал в полночь, а часам к четырём утра витрина наконец обновлялась. Если что-то шло не так - а шло регулярно - аналитики приходили к устаревшим данным. Проблема была не в железе и не в запросах: проблема была в подходе. Каждую ночь пайплайн тащил из источников полные таблицы, усекал целевые и заливал заново. Для небольших справочников это нормально, но для транзакционных таблиц с несколькими миллионами строк - брутфорс в чистом виде.

Когда мы занялись этим проектом в рамках сопровождения DWH и BI, первый вопрос был простой: а зачем каждую ночь перегружать всё, если за сутки меняется процентов пять от объёма?

Что такое CDC и почему именно сейчас

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

В апреле мы переезжали на SQL Server 2008 SP1 с клиентом на 1С - тогда оценили улучшения оптимизатора. Здесь история другая: CDC для ETL - это отдельный кейс, и ритейлер как раз подходил по профилю.

Включить CDC несложно:

-- Включаем CDC на базе
EXEC sys.sp_cdc_enable_db;

-- Включаем CDC на конкретной таблице
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Sales',
    @role_name     = NULL;

После этого SQL Server начинает писать в cdc.dbo_Sales_CT все вставки, обновления и удаления с метаданными: LSN транзакции, тип операции, старые и новые значения. SSIS 2008 получил специальный компонент CDC Source, который умеет читать эти таблицы и отдавать только то, что изменилось с момента последнего запуска.

Как выглядел пайплайн после переработки

Старая схема была монолитной: один большой пакет SSIS с truncate-and-load на каждую таблицу, всё последовательно. Новая - разбита по слоям.

Для каждой транзакционной таблицы - отдельный CDC-пакет:

• Извлечение. CDC Source читает таблицу изменений с LSN прошлого запуска до текущего. На выходе три потока: INSERT, UPDATE, DELETE.
• Трансформация. Минимальная - lookup-и для разрешения ключей, несколько вычисляемых колонок для хэша измерений. Ничего тяжёлого.
• Загрузка. OLE DB Command для апдейтов и делитов по суррогатному ключу, Destination для инсертов. Без truncate.

Справочники (номенклатура, контрагенты, точки продаж) - тут оставили полную перегрузку, они небольшие и логика SCD Type 2 проще с full load. Не стоит усложнять то, что работает.

Оркестрация - SQL Server Agent, несколько джобов с зависимостями. CDC-пакеты запускаются после того, как справочники загружены.

Что получилось на практике

Результат честный: ночное окно с четырёх часов сжалось до двадцати пяти минут. Большую часть этих двадцати пяти минут занимают справочники и финальная агрегация витрины, сами CDC-пакеты суммарно укладываются минут в семь-восемь.

Несколько наблюдений, которые не очевидны из документации:

• LSN-управление нужно хранить явно. SSIS сам не запоминает, до какого LSN добрался в прошлый раз. Мы завели служебную таблицу etl.cdc_watermark с именем таблицы и последним обработанным LSN. После каждого успешного запуска пакет обновляет watermark. Это же позволяет перезапустить с любой точки, если пакет упал в середине.

• Удаления - отдельная история. Если в источнике физически удаляют строки (а в транзакционных системах это бывает), CDC их честно показывает. Нужно решить: мягкое удаление в витрине или физическое. У клиента выбрали мягкое - ставим флаг is_deleted, потому что аналитика хочет видеть историю.

• Latency capture job. CDC работает через SQL Server Agent job, который читает transaction log и пишет в таблицы изменений. У этого job есть задержка - по умолчанию он стартует каждые пять секунд, но в нагруженных средах может отставать. Для ночного ETL это не критично, но стоит мониторить fn_cdc_get_max_lsn() против текущего LSN лога.

• Первоначальная нагрузка. CDC начинает писать изменения с момента включения. Исторические данные он не захватывает - начальную загрузку витрины всё равно делали full load, CDC подхватил уже после.

Где сейчас

Пайплайн отработал три недели в проде без инцидентов. Аналитики перестали жаловаться на устаревшие данные с утра - это главный показатель. Мониторинг джобов добавили в Zabbix: если CDC-пакет не завершился за 45 минут - алерт дежурному.

Есть открытый вопрос: что делать с таблицами в источнике, где разработчики иногда делают bulk-операции без логирования (BULK_LOGGED recovery model). CDC в таком случае теряет часть изменений - нужно либо договариваться с командой источника, либо для этих таблиц держать full load. Пока с клиентом обсуждаем.

Инструмент рабочий. Для ETL с большими транзакционными таблицами и коротким окном загрузки - хорошо подходит.