Привет, Habr!

Ятимлид достаточно молодой команды разработки и недавно столкнулась с недопониманием у ребят различий между процессами загрузки данных ETL и ELT. Решила разобрать разницу в одной статье, попробовать объяснить где и почему нужно использовать ETL и зачем понадобился ELT. Также в статье попробую ответить на вопрос — какой подход выбрать.

И это моя первая статья на Habr, буду рада поддержке.

Что такое ETL и ELT? Это процессы перемещения данных из источника А в источник Б с применением необходимых изменений. Цель у этих процессов идентична, шаги то же идентичные, меняется лишь их последовательность, что в корне меняет суть дела. Исторически первым процессом был ETL, с него все начиналось

ETL

ETL( Extract Transform Load) — процесс извлечения данных из различных источников, их преобразование и загрузка в конечное хранилище данных. То есть это процесс загрузки данных по расписанию из источников в основное хранилище с преобразованием данных в нужный формат.

ETL состоит из этапов:

• extract — извлечение данных из внешних источников;

• transform — их трансформация, очистка и обогащение, чтобы они соответствовали потребностям бизнес‑модели;

• load — загрузка в хранилище данных.

Как выглядит ETL процесс на схеме:

1. Extract

На данным этапе загрузчик подключается к внешним источникам и забирает необходимые данные. Источником может выступать внешняя информационная система, файл или другая база данных. Важно понимать какие данные загружать и будет ли это инкрементальная загрузка или загружается источник полностью. Наиболее классическая ситуация — забираем все изменения на Т -1, где Т — отчетная дата. 

Важно понимать, что на данном шаге данные никак не изменяются и не фильтруются, мы просто подключаемся к источнику и забираем все, что есть за нужный нам период. 

2. Transform

На этапе трансформации происходит полноценная работа с данными:

1. Очистка — убираем ненужные и/или мусорные данные, чистим от дублей, объединяем одинаковые данные из разных источников;

2. Изменения данных — применение бизнес логики, агрегация, расчеты атрибутов, фильтрация;

3. Приведение в итоговый формат — формируем временную таблицу, приводим к нужной кодировке, подтягиваем ключи справочников, добавляем технические атрибуты.

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

Так же здесь происходит самая длительная работа DE при написании потоков.

3. Load

Запись данных в основную таблицу из временной в формате 1:1. Так как все трансформации данных уже выполнены, а блокировать основную таблицу надолго нельзя, чтобы данные были доступны пользователям, используется наиболее быстрая операция — простая вставка данных. Также на данном этапе в базу пишутся метаданные потока, которые позволяют более корректно в дальнейшем анализировать и использовать информацию.

Какие метаданные могут быть записаны:

• идентификатор задания;

• дата и время загрузки;

• информация об источнике (имя, код системы);

• имя‑файла источника, если загружается файл;

• хеш;

• версия загрузки;

• статус загрузки;

• многое другое, что поможет точно понимать когда и как пришла информация.

Метаданные помогают точно понимать как информация попала в базу и как она трансформировалась. Это помогает правильно интерпретировать данные, их неточности, а также настраивать процесс DQ (data quality).

ELT

ELT ( Extract Load Transform) — процесс интеграции данных, при котором из различных источников извлекается сырая информация, загружается непосредственно в центральное хранилище, где уже происходит трансформация данных в нужный формат для дальнейшего анализа и отчётности.

То есть этот процесс аналогичен предыдущему с ключевым отличием - трансформация данных происходит на последнем этапе.

Как выглядит ELT процесс на схеме:

Фактически мы получаем ситуацию, когда в центральном хранилище лежат и сырые данные и подготовленные. Да, это увеличивает объемы хранения.

Зачем это нужно?

1. Всегда есть сырые данные, на которых можно проверить гипотезу и после ее подтверждения сформировать отчет. Это дает гибкость, сокращает время разработки отчетов, особенно в ситуации, когда заказчик точно не понимает какие данные ему нужны.

2. Когда нужно загрузить очень большие данные, этап трансформации может быть невероятно тяжелым и мощностей системы просто не хватит. Поэтому бывает проще положить весь объем в базу, и выбрав только нужную информацию, обработать ее. 

Предлагаю рассмотреть процессы на примере.

Одни из самых объемных данных в банке - транзакции по картам. Буквально каждая транзакции клиента эквивалента одной строчке в базе данных. Таких клиентов у банка много сотен тысяч. Соответственно за день это может быть сотни гигабайт данных. 

Задача:

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

Предлагаю внести некоторые технические ограничения для дальнейшей простоты описания:

• рабочий сервер, который используют 3 отдела - карточный бизнес, отдел продаж банковских продуктов, отдел маркетинга;

• у каждого отдела есть свои выделенные мощности от общего сервера;

• Источники данных: ИС выданных карт, ИС с данными клиентских карт, ИС карточных транзакции;

• Один пакет с данными из ИС карточных транзакции ~ 500Гб;

Итак, что нам нужно сделать? 

1. Загрузить все данные по транзакциям за последние сутки;

2. Проверить тип карты (кредит/дебит). Эти данные берем из уже готовой таблицы;

3. Нам нужны данные только по физическим лицам, и мы их заберем так же из готовой таблицы.

Шаг 1 — Extract

Мы имеем три источника, которые нам дают 500+ Гб данных, которые мы загружаем для одного отчета. Объем данных большой, поэтому данные сразу записываем во временные таблицы базы. То есть как минимум одна операция по записи на диск есть. Уже на первом шаге требуется минимум 500Гб свободного дискового пространства.

Шаг 2 — Transform

На этапе трансформации отбираются только необходимые данные для запрошенного отчета, а именно:

• Список идентификаторов всех дебетовых карт;

• К картам добавляем информацию по клиенту и фильтруем только физических лиц;

• Считываем с диска данные по транзакциям и выбираем только необходимые атрибуты;

• Добавляем нужные бизнес атрибуты из первых таблиц;

• Пишем на диск в промежуточную таблицу новый пул данных. Это формирует дополнительный массив, предположим, в 200ГБ.

  По сути, на этапе загрузки данных для нашего примера, важно понять, что, не смотря на то, что транзакционные данные нужны для отчета в разрезе суток, данные по клиентам и картам нужны за всю историю. Поэтому загрузка данных происходит поэтапно: отдельно загружается информация по картам и отдельно по клиентам, а последним этапом загружаются транзакции. Транзакции зависят от других источников и в случае падения первых могут вообще не собраться или собраться без части информации.

Шаг 3 — Load

Допустим, что для отдела карточного бизнеса, который ежедневно работает с огромным пулом данных выделили большой объем мощностей сервера, способные обрабатывать такие объемы на ежедневной основе.

Предположим, что размер очищенных данных по транзакциям с дополнительными бизнес атрибутами занимает около 200Гб, которые мы записываем на диск в итоговую таблицу. Но это уже терабайт свободного места на диске. И это без учета дубликации данных, при которой итоговые 200 Гб легко могут вырасти в 600Гб.

Хорошо, один отчет сформирован. Но что будет, если другому отделу продаж понадобится отчет по аналогичным транзакциям, но в разрезе других продуктов ежемесячно. Или понадобилась информация о платежной системе. Технически данные объемы не объединяются из-за различной структуры. Так же данные нужно агрегировать по разным временным промежуткам.

Учитывая объем данных, технические характеристики сервера, а также частоту загрузки данных, понимаем, что рассчитывать отчет как ETL процесс нельзя.

А в бэклоге есть задача из отдела маркетинга по отчету, который вообще нужно будет собирать в разрезе кодов транзакций в динамически изменяемых наборах. Все будущие задачи не позволяют загружать данные в том формате, которые есть сегодня. И выделенные мощности сервера у двух новых отделов не вытянут такие объемы.

Как сделать три разных отчета из одинаковых данных? Правильно, загрузить данные из трех ИС в хранилище как есть, очистить их от мусорных данных, а дальше эти сформированные таблицы использовать как источник и формировать на них отчеты с необходимыми атрибутами. При этом мы не теряем информацию, которая в данный момент нам не нужна.

Звучит как тознакомо? — Да, ELT! А еще Data Vault и DWH тоже всплывают. И вот тут мы подходим к развязке когда использовать какие процессы.

Предлагаю рассмотреть плюсы и минусы двух подходов в сравнении:

Как выбрать нужный процесс? 

Самым большим вопросов является объем данных. Если данные будут весить 5Гб, то такие данные можно грузить как угодно и разницы во времени и объемах нет.

Вторым вопросом является время, которое уходит на разработку новых отчетов. Если в компании есть релизный процесс и большой бэклог, то вероятность, что придется ждать новый отчет месяцами становиться проблемой.

В моей карьере была компания, где объемы данных были не большими и мы могли позволить себе писать ETL с забором данных из системы под наши нужды. Но при условии, что некоторые данные уже были в хранилище, например, данные по клиентам, картам, депозитам, справочники данных и многое другое. Поэтому нередко была ситуация, когда мы загружали только часть недостающих данных, просто потому что полностью грузить источник было не выгодно с точки зрения объемов хранения.

Но в компаниях, имеющих дело с Big Data такой подход просто не уместен. Когда компании нужны все данные, часть из которых идет на витрины данных, часть просто храниться, часть понадобиться через год, и есть постоянная потребность в экспериментах с данными, тогда больше подходит ELT. Но как и все, их можно комбинировать.

Сейчас вполне нормальная ситуация, когда одна команда формирует с помощью ELT слой данных, из которого другая команда забирает данные как из ИС и формирует следующий слой данных через процесс ETL. Таким ярким примером может служить разделение много раз упомянутых сегодня транзакции для разных групп клиентов: один отдел забирает только физических лиц, второй юридических.

Надеюсь, мне удалось пролить свет и разделить два процесса между собой.