ETL и ELT: этапы, различия и практика

Почти всякая аналитика начинается с неприятного факта: данные лежат не там, где их считают. Заказы живут в PostgreSQL продакшн-сервиса, платежи — в API эквайринга, клики — в логах, справочник товаров — в Google Sheets у категорийного менеджера. Аналитик хочет одну таблицу «выручка по когортам за 18 месяцев», а получает пять систем, ни одна из которых не переживёт запрос на полтора года истории с четырьмя join’ами.

Задача перемещения и приведения данных в пригодный для анализа вид называется дата-интеграцией, а её классическая реализация — ETL: Extract, Transform, Load. Три буквы описывают не технологию, а порядок операций. Достаточно поменять порядок двух последних — получится ELT, и это изменение переворачивает архитектуру, экономику и организацию работы команды. Разберёмся по первым принципам: что делает каждый этап, почему порядок важен, как писать загрузку, которую можно перезапустить, и где такие пайплайны обычно ломаются.

Интуиция: ресторанная кухня

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

ELT — грязные овощи заносят в холодильник как есть, а моют и режут прямо на кухне, перед подачей. Холодильник должен быть большим и дешёвым, кухня — мощной. Зато любой новый рецепт готовится из уже имеющегося сырья, без нового заказа.

Ровно это и произошло с данными. Пока «холодильник» (хранилище) стоил дорого, а «кухня» (вычислитель) была слабой, единственным разумным вариантом был ETL. Когда объектные хранилища подешевели до долларов за терабайт в месяц, а массово-параллельные СУБД научились считать агрегат по миллиарду строк за секунды, экономически выгоднее стало сначала положить, потом думать.

Три этапа по отдельности

Extract — извлечение

Прочитать данные из источника, по возможности не уронив его. Ключевой вопрос — что именно читать: всё или только изменения.

Главная ловушка Extract — думать, что источник отдаёт согласованный снимок. Запрос SELECT * FROM orders без явной транзакции с уровнем изоляции snapshot прочитает таблицу «размазанной по времени»: начало — состояние на 10:00, конец — на 10:07. Если параллельно шёл перенос заказа между статусами, вы увидите его дважды или ни разу.

Transform — трансформация

Всё, что превращает «данные системы-источника» в «данные, пригодные для ответа на вопрос бизнеса». Типичные операции:

• очистка: приведение типов, нормализация телефонов и валют, обрезка мусора;
• дедупликация: ROW_NUMBER() по бизнес-ключу с сортировкой по времени;
• обогащение: подтягивание справочников, геокодирование, курсы валют на дату;
• конформирование: единый customer_id из трёх систем, где он назывался по-разному;
• агрегация: свёртка событий в дневные метрики;
• историзация: SCD Type 2 — сохранение того, каким был атрибут на момент факта;
• приватность: хеширование или маскирование персональных данных.

Трансформация — единственный этап, где живёт бизнес-логика, и потому единственный, который постоянно меняется. Это критично: E и L пишутся один раз и работают годами, а T переписывают каждый квартал. Архитектура должна делать переписывание T дешёвым.

Load — загрузка

Запись в целевое хранилище. Три режима, и разница между ними определяет, переживёт ли пайплайн повторный запуск:

• append — просто дописать. Быстро, но повторный запуск удваивает данные;
• overwrite партиции — заменить целиком срез (например, один день). Идемпотентно;
• merge / upsert — обновить существующие строки по ключу, вставить новые. Идемпотентно, но дороже: требует поиска совпадений.

ETL: как это делали и делают

Исторически ETL-движок был отдельным продуктом: Informatica PowerCenter, IBM DataStage, Microsoft SSIS, Talend. Логика описывалась мышкой в графическом редакторе, исполнялась на выделенном сервере, а в хранилище (дорогая Teradata или Oracle Exadata) попадало только то, что реально нужно отчётам.

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

Что за это платили. Сырые данные не сохранялись. Когда через год выяснялось, что в агрегате забыли учесть возвраты, пересчитать историю было нельзя — источник уже перезаписал старые состояния. Логика жила в GUI, её нельзя было нормально ревьюить в pull request’е, покрыть тестами или откатить. Изменение требовало отдельной команды ETL-разработчиков и недель ожидания.

ELT: что изменилось

Три технологических сдвига сделали ELT возможным:

1. Разделение хранения и вычислений. S3 и его аналоги дали хранение по цене порядка 23 $ за терабайт в месяц. Хранить сырьё стало дешевле, чем обсуждать, стоит ли его хранить.
2. MPP и колоночные движки. Snowflake, BigQuery, ClickHouse, Redshift сканируют терабайты за секунды, потому что читают только нужные колонки и распараллеливают работу на сотни ядер. Подробнее — в статье Хранилища и форматы.
3. SQL как язык трансформаций плюс инженерные практики. dbt превратил T в набор SELECT-запросов, лежащих в git, с тестами, документацией и графом зависимостей. Логика стала кодом, а значит — ревью, ветки, откаты, CI.

Главное следствие ELT — сырьё сохраняется. Когда бизнес меняет определение метрики (а он меняет его всегда), достаточно переписать SQL и пересчитать витрину из RAW. Никаких разговоров с владельцем прода о повторной выгрузке за два года.

Слои: bronze / silver / gold

Практически все ELT-хранилища организованы одинаково — в три-четыре слоя (в лексике Databricks: bronze, silver, gold; в лексике dbt: staging, intermediate, marts):

Дисциплина «в staging нет join’ов» кажется бюрократией, пока однажды не потребуется понять, откуда взялось число в отчёте. Тогда линейный путь raw → stg → core → mart экономит дни.

Сравнение по существу

Это не «устаревшее против современного». ETL остаётся правильным выбором, когда:

• регуляторика запрещает класть сырые PII в облачное хранилище — маскировать надо до пересечения границы (GDPR, 152-ФЗ, требования PCI DSS);
• источник — поток, и трансформация обязана происходить на лету, иначе задержка недопустима (см. Потоковая обработка);
• трансформация не выражается в SQL: парсинг PDF, распознавание изображений, вызов ML-модели, разбор нестандартного бинарного формата;
• объёмы огромны, а нужен процент: тащить 40 ТБ логов в хранилище, чтобы взять из них агрегат в 2 ГБ, — плохая сделка. Свернуть в Spark и загрузить результат дешевле (см. Пакетная обработка).

На практике почти любой зрелый контур гибридный: «EtLT» — лёгкая обязательная трансформация на лету (маскирование PII, отбрасывание мусорных полей, приведение кодировки), затем загрузка, затем основная бизнес-логика в хранилище.

Эволюция подходов

Инкрементальная загрузка: самая частая ошибка индустрии

Полная перезагрузка проста и всегда корректна, но её стоимость линейна по объёму таблицы и платится каждый запуск: O(N) времени и сетевого трафика при N строк. При почасовом расписании и таблице на 500 млн строк это неприемлемо. Значит — инкремент.

Наивная реализация выглядит так:

-- ОПАСНО: тихо теряет строки
SELECT * FROM orders WHERE updated_at > :last_watermark;

Проблема не в SQL, а в физике транзакций. Значение updated_at присваивается в момент выполнения UPDATE, а видимой для читателей строка становится в момент COMMIT. Между ними может пройти секунда или минута. Если запуск прочитал таблицу в 10:05:00, а транзакция со временем изменения 10:04:58 закоммитилась в 10:05:03, — строка не попала в выгрузку и уже никогда не попадёт: её updated_at меньше нового водяного знака.

Лечение — нахлёст (lookback): читать с запасом назад и полагаться на идемпотентность записи.

SELECT * FROM orders
WHERE updated_at > :last_watermark - INTERVAL '15 minutes'
  AND updated_at <= :run_started_at;   -- верхняя граница фиксирована!

Верхняя граница обязательна: без неё водяной знак «уползает» на момент окончания чтения, и всё, что изменилось во время самой выгрузки, будет считаться загруженным, хотя попало в выборку лишь частично. Сложность такого чтения — O(Δ + δ), где Δ — реальные изменения, δ — перечитанный нахлёст. Лишняя работа оплачивается один раз и страхует от потери данных навсегда.

Что нахлёст всё равно не решает

• Физические удаления. DELETE FROM orders WHERE id = 42 не оставляет строки с новым updated_at. Инкремент по времени её просто не увидит, и в хранилище заказ останется живым вечно. Решения: soft delete (deleted_at), периодическая сверка ключей, или CDC.
• Массовые миграции. UPDATE orders SET status = 'x' по всей таблице обновит updated_at у 500 млн строк, и «инкремент» станет полной перезагрузкой в самый неудачный момент.
• Отсутствие поля времени или его обновление триггером не всегда.

CDC: чтение журнала транзакций

Вместо опроса таблицы читается журнал, который СУБД и так пишет для восстановления: WAL в PostgreSQL, binlog в MySQL, redo log в Oracle. Каждая запись журнала — факт изменения с типом операции, состоянием «до» и «после».

Плюсы. Нагрузка на источник почти нулевая (журнал пишется в любом случае), видны DELETE, видно состояние «до», задержка — секунды. Минусы, о которых узнают в проде:

• Незакрытый слот репликации удерживает WAL, и диск продакшн-базы заполняется. Это инцидент уровня «лежит основной сервис». Мониторинг лага слота обязателен с первого дня.
• Первичный снимок (initial snapshot) большой таблицы может держать долгую транзакцию и мешать вакууму.
• DDL в источнике (переименовали колонку) ломает потребителей вниз по течению — отсюда необходимость контрактов данных.
• Гарантия доставки, как правило, at-least-once: одно и то же событие может прийти дважды. Значит, приёмник обязан быть идемпотентным.

Идемпотентность: главное свойство пайплайна

Пайплайн упадёт. Сеть моргнёт, источник вернёт 503, кластер отберут за spot-цену. Оркестратор перезапустит задачу — и вопрос лишь в том, останется ли результат корректным. Формально требуется: f(f(x)) = f(x) — повторное применение загрузки к тем же входным данным не меняет состояние приёмника.

Три практических правила:

1. Никогда не append без ключа дедупликации. Либо MERGE по бизнес-ключу, либо INSERT OVERWRITE целой партиции.
2. Водяной знак фиксируется атомарно с данными. Отдельная запись «загрузили до 10:05» вне транзакции — источник самых злых багов: при падении между шагами образуется дыра, которую никто не заметит месяцами.
3. Единица работы — партиция, а не «всё что накопилось». Задача «пересчитать 2026-07-14» перезапускаема, задача «догрузить новое» — нет.

MERGE как основа

-- Snowflake / BigQuery / Databricks: идемпотентная загрузка дельты.
-- Шаг 1: снять дубликаты внутри самой дельты, иначе MERGE упадёт
--         с ошибкой "multiple source rows matched" (или, что хуже, выберет случайную).
MERGE INTO core.orders AS t
USING (
    SELECT s.* FROM staging.orders_delta AS s
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY s.order_id ORDER BY s.updated_at DESC, s._ingested_at DESC) = 1
) AS d
ON t.order_id = d.order_id
-- Шаг 2: не перезаписываем более свежую версию более старой. Событие из CDC
-- может прийти повторно и «откатить» строку назад — защищаемся сравнением времени.
WHEN MATCHED AND d.updated_at > t.updated_at THEN UPDATE SET
    t.status = d.status, t.amount = d.amount,
    t.updated_at = d.updated_at, t._loaded_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT
    (order_id, customer_id, status, amount, updated_at, _loaded_at)
VALUES
    (d.order_id, d.customer_id, d.status, d.amount, d.updated_at, CURRENT_TIMESTAMP());

Стоимость MERGE — примерно O(Δ log N) при наличии кластеризации по ключу и O(Δ + N) в худшем случае, когда движку приходится переписывать все затронутые файлы. В колоночных хранилищах и озёрах данных запись идёт файлами: обновление одной строки означает перезапись всего файла на 100 МБ. Отсюда правило: группируйте изменения по партициям, чтобы один запуск трогал десятки файлов, а не тысячи.

Рабочий пример: инкрементальный extract-load на Python

"""Инкрементальная выгрузка PostgreSQL -> объектное хранилище (RAW-слой).

Свойства: фиксированная верхняя граница окна, нахлёст назад, серверный курсор
(не тянем таблицу в память), запись во временный префикс с последующим переносом.
"""
import datetime as dt
import uuid

import pyarrow as pa
import pyarrow.parquet as pq

LOOKBACK = dt.timedelta(minutes=15)  # нахлёст: >= максимальной длительности транзакции
BATCH_ROWS = 50_000                  # компромисс между памятью и числом файлов
COLS = ["order_id", "customer_id", "status", "amount", "updated_at"]
SCHEMA = pa.schema([
    ("order_id", pa.int64()), ("customer_id", pa.int64()), ("status", pa.string()),
    ("amount", pa.decimal128(18, 2)), ("updated_at", pa.timestamp("us", tz="UTC")),
    ("_ingested_at", pa.timestamp("us", tz="UTC")),
    ("_run_id", pa.string()),   # линидж: какой запуск породил строку
])


def extract_batches(conn, table: str, lo: dt.datetime, hi: dt.datetime):
    """Читает полуинтервал (lo, hi] серверным курсором, отдавая батчи строк."""
    query = f"""SELECT {', '.join(COLS)} FROM {table}
                WHERE updated_at > %(lo)s AND updated_at <= %(hi)s
                ORDER BY updated_at"""
    # REPEATABLE READ фиксирует снимок: все батчи курсора увидят одно и то же
    # состояние базы, даже если чтение длится двадцать минут.
    with conn.transaction():
        conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
        with conn.cursor(name=f"cur_{uuid.uuid4().hex}") as cur:
            cur.itersize = BATCH_ROWS
            cur.execute(query, {"lo": lo, "hi": hi})
            batch = []
            for row in cur:
                batch.append(row)
                if len(batch) >= BATCH_ROWS:
                    yield batch
                    batch = []
            if batch:
                yield batch


def load_to_raw(batches, base_uri: str, run_id: str, logical_date: dt.date) -> int:
    """Пишет во временный префикс, затем переносит в партицию одним движением.

    Приём «write to temp, then promote»: читатели не видят частичных данных,
    а упавший запуск не оставляет мусора в рабочем префиксе.
    """
    tmp = f"{base_uri}/_staging/run_id={run_id}"
    now, total = dt.datetime.now(dt.timezone.utc), 0
    for i, batch in enumerate(batches):
        rows = [dict(zip(COLS, r), _ingested_at=now, _run_id=run_id) for r in batch]
        pq.write_table(
            pa.Table.from_pylist(rows, schema=SCHEMA), f"{tmp}/part-{i:05d}.parquet",
            compression="zstd",      # плотнее gzip и заметно быстрее при чтении
            use_dictionary=True,     # status повторяется -> словарь экономит десятки %
        )
        total += len(batch)
    promote(tmp, f"{base_uri}/dt={logical_date.isoformat()}/run_id={run_id}")
    return total


def run(conn, table: str, base_uri: str, logical_date: dt.date) -> dict:
    run_id = uuid.uuid4().hex[:12]
    # hi — время СТАРТА запуска, а не now() в конце: иначе окно «плывёт»
    # и изменения, случившиеся во время самой выгрузки, будут считаться загруженными.
    hi = dt.datetime.now(dt.timezone.utc)
    lo = read_watermark(table) - LOOKBACK      # водяной знак из служебной таблицы
    rows = load_to_raw(extract_batches(conn, table, lo, hi), base_uri, run_id, logical_date)
    # Двигаем знак ТОЛЬКО после успешной записи и ровно на hi — никогда на now().
    commit_watermark(table, hi, run_id=run_id, rows=rows)
    return {"run_id": run_id, "rows": rows, "window": [str(lo), str(hi)]}

Обратите внимание на строку commit_watermark(table, hi, ...). Соблазн написать dt.datetime.now() возникает у каждого второго — и создаёт дыру размером в длительность самого запуска. Это самая дорогая опечатка в дата-инженерии: пайплайн выглядит зелёным, метрики тихо расходятся, а обнаруживается это через квартал при сверке с бухгалтерией.

Трансформация как SQL-модель

-- models/staging/stg_orders.sql  (dbt, инкрементальная материализация)
{{ config(
    materialized='incremental', unique_key='order_id', incremental_strategy='merge',
    partition_by={'field': 'order_date', 'data_type': 'date'}, cluster_by=['customer_id']
) }}

WITH source AS (
    SELECT * FROM {{ source('raw', 'orders') }}
    {% if is_incremental() %}
        -- Фильтр по времени ЗАГРУЗКИ, а не изменения: строка могла приехать
        -- сегодня с updated_at месячной давности (backfill в источнике).
        WHERE _ingested_at > (SELECT MAX(_ingested_at) FROM {{ this }})
    {% endif %}
),
deduplicated AS (
    SELECT * FROM source
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY order_id ORDER BY updated_at DESC, _ingested_at DESC) = 1
)
SELECT
    d.order_id, d.customer_id,
    LOWER(TRIM(d.status))            AS status,
    CAST(d.amount AS NUMERIC(18, 2)) AS amount_original,
    -- Курс берём на дату заказа, а не текущий: отчёт за прошлый год
    -- не должен меняться от сегодняшних колебаний валюты.
    d.amount * r.rate                AS amount_rub,
    DATE(d.updated_at)               AS order_date,
    d.updated_at, d._ingested_at
FROM deduplicated d
LEFT JOIN {{ ref('dim_currency_rates') }} r
       ON r.currency = d.currency AND r.rate_date = DATE(d.updated_at)
WHERE d.order_id IS NOT NULL

Комментарий про курс валют — не мелочь, а фундаментальное различие между моментальным снимком и историческим фактом. Отчёты, которые «меняют прошлое» при каждом пересчёте, разрушают доверие к хранилищу быстрее любых сбоев. Тема подробно раскрыта в моделировании данных в разделе про SCD Type 2.

Сколько это стоит: считаем на пальцах

Инженерное решение «полная перезагрузка или инкремент» — арифметика. Пусть таблица содержит N = 500 000 000 строк по 200 байт (100 ГБ), меняется Δ = 2 000 000 строк в сутки, расписание — почасовое (24 запуска).

Разница между строками 1 и 3 — тысяча раз по объёму и, в облаке, сопоставимая разница в счёте. При этом инкремент дороже в разработке и опаснее в эксплуатации. Разумная стратегия зрелой команды: начинать с полной перезагрузки, пока она укладывается в SLA и бюджет, и переходить на инкремент осознанно, с мониторингом сходимости. Полная перезагрузка не бывает неправильной — она бывает слишком дорогой.

Регулярная сверка обязательна при любом инкременте: раз в сутки сравнивать COUNT(*) и контрольные суммы по срезам источника и приёмника. Расхождение — сигнал, что где-то потерялась строка.

Типичные ошибки

• Водяной знак по now() вместо верхней границы окна. Разобрано выше — тихая потеря данных, обнаруживаемая через месяцы.
• Отсутствие идемпотентности при append. Retry оркестратора удваивает выручку в отчёте. Проверка простая: запустите задачу дважды подряд и сравните COUNT(*).
• Трансформация в extract’е «заодно». Если загрузчик по пути переименовывает колонки и фильтрует строки, RAW перестаёт быть копией источника, и восстановить историю после ошибки в этой логике невозможно.
• Учёт удалений забыт. Классика: отменённые заказы вечно живут в витрине, выручка завышена. Нужен soft delete, CDC либо периодическая сверка ключей.
• Часовые пояса. Источник пишет в локальном времени, хранилище считает в UTC, аналитик смотрит в московском. Правило: всё хранить в UTC, конвертировать только на витрине, и обязательно фиксировать это в документации таблицы.
• Схема источника меняется молча. Добавили колонку — половина пайплайнов упала, или, что хуже, продолжила работать, потеряв данные. Лечится контрактами и тестами схемы.
• Опоздавшие данные и партиционирование по времени загрузки. Событие произошло вчера, приехало сегодня. Если витрина партиционирована по дате события, пересчитывать нужно вчерашнюю партицию — а пайплайн, привыкший считать «только сегодня», её не тронет. Отсюда правило backfill’а: пересчитывать не одну партицию, а окно из N последних.
• Секреты в коде пайплайна. Дата-пайплайны имеют доступ ко всем данным компании сразу — это самая привлекательная цель в инфраструктуре. Только vault/secret manager, только read-only реплики, только раздельные роли на RAW и MARTS.
• «Мы потом почистим RAW». Никогда не чистят. Заранее задайте политику ретенции и lifecycle-правила объектного хранилища, иначе через два года счёт за хранение станет предметом отдельного совещания.

Как это выглядит в проде

Типичный современный контур средней компании выглядит так:

• Extract + Load — готовый коннектор (Airbyte, Fivetran, Debezium) или тонкий собственный загрузчик на Python. Своё пишут для источников, которых нет в коннекторах, и для случаев, где нужен точный контроль нагрузки на прод.
• Хранилище — Snowflake / BigQuery / ClickHouse либо lakehouse на Iceberg или Delta поверх S3.
• Transform — dbt: модели в git, тесты not_null/unique/relationships, автодоки, граф зависимостей, разделение окружений dev/prod по схемам.
• Оркестрация — Airflow или Dagster: расписания, зависимости, retry, backfill, SLA-алерты. Детали — в оркестрации пайплайнов.
• Качество — Great Expectations или dbt-тесты в CI плюс мониторинг свежести (freshness) и объёмов.
• Наблюдаемость — на каждую таблицу: время последнего успешного обновления, число строк за запуск, доля отклонённых записей, стоимость запроса.

Отдельно стоит знать два современных термина. Reverse ETL — обратное движение: посчитанные в хранилище сегменты и скоры выгружаются назад в операционные системы (CRM, рассыльщик, рекламный кабинет); хранилище перестаёт быть тупиком отчётности и становится источником для продукта. Zero-ETL — интеграции, где перенос берёт на себя платформа (реплика Aurora в Redshift, federated queries в BigQuery); это удобно для однородного облака и не отменяет ни трансформаций, ни моделирования, ни качества данных — исчезает только транспортный слой.

Мини-итог

• ETL и ELT различаются местом и моментом трансформации, а не набором операций.
• ELT победил в типовом облачном сценарии, потому что дешёвое хранение позволило сохранять сырьё, а SQL-трансформации в git сделали логику ревьюируемой и тестируемой.
• ETL по-прежнему обязателен там, где трансформация не выражается в SQL, где регуляторика запрещает сырые PII в хранилище и где объём источника на порядки больше полезного результата.
• Полная перезагрузка проста и корректна; инкремент дешевле, но требует нахлёста, фиксированной верхней границы окна и идемпотентной записи.
• Водяной знак фиксируется атомарно с данными и равен верхней границе окна — не now(). Пайплайн, который нельзя безопасно перезапустить, не готов к проду; проверка одна — запустить дважды и сравнить результат.

Источники

• Ralph Kimball, Joe Caserta. The Data Warehouse ETL Toolkit — канонический разбор 34 подсистем ETL, актуален и для ELT: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-etl-toolkit/
• Martin Kleppmann. Designing Data-Intensive Applications, гл. 10–11 — батч и потоки, семантика доставки: https://dataintensive.net/
• Документация dbt: инкрементальные модели и стратегии merge — https://docs.getdbt.com/docs/build/incremental-models
• Debezium: архитектура CDC и эксплуатационные предупреждения — https://debezium.io/documentation/reference/stable/architecture.html
• PostgreSQL: логическая репликация и слоты — https://www.postgresql.org/docs/current/logical-replication.html
• Databricks: medallion-архитектура (bronze/silver/gold) — https://www.databricks.com/glossary/medallion-architecture
• Apache Iceberg: спецификация таблиц и эволюция схемы — https://iceberg.apache.org/spec/

Что дальше

Мы научились доставлять данные и делать это повторяемо. Следующий вопрос — в какую форму их укладывать, чтобы запросы были быстрыми, а история не терялась: Моделирование данных: нормализация, звезда, снежинка, Data Vault.