ETL/ELT процессы

1) Назначение и контекст

ETL/ELT-конвейеры обеспечивают предсказуемую загрузку, трансформации и публикацию данных для отчетности (GGR/NGR, регуляторы), аналитики/ML и оперативных панелей.

ETL: трансформируем до загрузки в DWH/Lakehouse (реже в современных стэках).
ELT: сначала грузим в Lakehouse (Bronze/Silver), затем трансформируем SQL/движками (рекомендовано).

2) Эталонная архитектура

1. Ingest/Edge: HTTP/gRPC/Batch, CDC из OLTP, провайдерские S3/FTP выгрузки.
2. Bronze (raw, append-only): неизменяемые payload’ы, партиции по дате/рынку/тенанту.
3. Silver (clean/conform): нормализация, дедуп, справочники, SCD, FX/таймзоны.
4. Gold (serve): денормализованные витрины под BI/регуляторку/модели.
5. Оркестрация: Airflow/Dagster/Prefect (DAG’и, SLA, ретраи, сдвиги).
6. DQ/Contracts: Schema Registry + DQ-как-код, consumer-driven tests.
7. Наблюдаемость: метрики пайплайнов, lineage, логи, cost-дашборды.

3) Выбор ETL vs ELT

Практика: в iGaming — ELT + CDC: быстро грузим, затем стандартизируем и считаем.

4) Инкременты и CDC

• CDC (Debezium/лог-репликация): изменения OLTP → Bronze → MERGE в Silver.
• Watermark по времени: `updated_at > max_loaded_ts`.
• Хэш-дифф: сравнение `md5(row)` для детекта изменений.
• Upsert/MERGE: идемпотентность загрузок.

sql
MERGE INTO silver. payments s
USING stage. payments_delta d
ON s. transaction_id = d. transaction_id
WHEN MATCHED THEN UPDATE SET
WHEN NOT MATCHED THEN INSERT;

5) Контракты и схемы

Schema-first: JSON/Avro/Protobuf в Registry; `schema_version` в событиях/файлах.
Эволюция: back-compatible (nullable добавления); breaking — `/v2` + двойная запись.
Обязательные поля: `event_time (UTC)`, `event_id`, `trace_id`, `user_pseudo_id`, `market`.

6) DQ-как-код (минимальный набор)

yaml table: silver. payments owner: data-payments slo:
freshness_minutes: 15 completeness_percent: 99. 5 rules:
- name: unique_tx # uniqueness of transactions type: unique columns: [transaction_id]
severity: critical
- name: currency_whitelist type: in_set column: currency set: [EUR,USD,GBP,TRY,BRL]
severity: major
- name: amount_positive type: range column: amount_base min: 0. 01 severity: critical
- name: fk_user type: foreign_key column: user_pseudo_id ref_table: dim. users_scd severity: critical

7) Оркестрация: DAG’и, зависимости, SLA

DAG-дизайн: от источников к витринам; явные зависимости между задачами.
Ретраи и идемпотентность: backoff, «чистые» повторы, checkpoint’ы.
Сдвиги (catchup): аккуратный догон пропущенных периодов.
SLA: например, Gold.daily готово до 06:00 локального времени; оповещения о нарушениях.
Параметризация: рынки/тенанты/даты через vars; единый шаблон job’ов.

8) Идемпотентность и exactly-once

На ingest: дубликаты возможны → дедуп по `(event_id, source)`.
В обработке: upsert/merge; «чистые» функции трансформаций.
В sink: транзакционные коммиты или idempotent writes; контроль «двойного учета».
Outbox/Inbox: транзакционная публикация доменных событий из OLTP.

9) Backfill и reprocessing

Backfill: первичное заполнение/исторические диапазоны.
Reprocessing: перерасчет при изменении логики/исправлениях.
Guardrails: лимиты диапазонов, квоты, окна времени, dry-run с сравнением метрик.
Маркировка: `logic_version`, `reprocessed_at`, `recalc_reason`.

10) Моделирование Silver/Gold

Silver (3НФ/BCNF): факты `fact_bets/payments/payouts`, измерения `dim_users/games/providers/markets (SCD II)`, стандартизация валют/таймзон.
Gold: денормализованные витрины под BI/регуляторку/модели; неизменяемые экспортные пакеты (WORM) + подпись.

Пример Gold: GGR Daily

sql
CREATE OR REPLACE VIEW gold. ggr_daily AS
SELECT
DATE(b. event_time) AS event_date,
b. market,
g. provider_id,
SUM(b. stake_base) AS stakes_eur,
SUM(p. amount_base) AS payouts_eur,
SUM(b. stake_base) - SUM(p. amount_base) AS ggr_eur
FROM silver. fact_bets b
LEFT JOIN silver. fact_payouts p
ON p. user_pseudo_id = b. user_pseudo_id
AND p. game_id = b. game_id
AND DATE(p. event_time) = DATE(b. event_time)
JOIN dim. games g ON g. game_id = b. game_id
GROUP BY 1,2,3;

11) Приватность и резидентность

PII-минимизация: токенизация; маппинги реальных ID в изолированном контуре.
RLS/CLS: политики доступа по ролям/юрисдикциям, маскирование.
Residency: отдельные каталоги/ключи для EEA/UK/BR; запрет кросс-региональных join’ов без основания.
DSAR/RTBF & Legal Hold: селективные редактирования, WORM-архивы для отчетности, аудит экспортов.

12) Наблюдаемость и SLO

• Freshness Silver p95 ≤ 15 мин; Gold daily готово до 06:00 лок. времени.
• Completeness ≥ 99.5%, Validity (схема) ≥ 99.9%.
• Успешность job’ов ≥ 99.0%, MTTR инцидентов ≤ 24–48 ч.

Дашборды: Freshness heatmap, воронка DQ-потерь, cost/query & cost/GB, lineage-граф.

13) Производительность и стоимость

Партиционирование: дата/рынок/тенант; кластеризация/Z-order по фильтрам.
Форматы: Parquet + ACID (Delta/Iceberg/Hudi), компрессия и статистики.
Компакция: борьба с small files (OPTIMIZE/VACUUM).
Материализация: стабильные агрегаты; избегать гигантских on-the-fly join’ов.
Chargeback: бюджеты, квоты на реплеи/backfill; планирование в окна низкой нагрузки.

14) Примеры типовых задач DAG (псевдокод Airflow)

python with DAG("elt_payments_daily", schedule="@daily", start_date=..., catchup=True) as dag:
extract = BashOperator(task_id="extract_cdc", bash_command="run_cdc_to_bronze. sh {{ ds }}")
load  = BashOperator(task_id="load_to_silver", bash_command="sql/run_merge_silver. sql {{ ds }}")
dq   = BashOperator(task_id="dq_checks", bash_command="dq/run_checks. sh silver. payments {{ ds }}")
gold  = BashOperator(task_id="build_gold_ggr", bash_command="sql/build_gold_ggr. sql {{ ds }}")
export = BashOperator(task_id="export_regulator", bash_command="export/run_worm_pack. sh {{ ds }}")

extract >> load >> dq >> gold >> export

15) Процессы и RACI

R (Responsible): Data Engineering (DAG’и, модели Silver/Gold), Data Platform (инфра, Registry, DQ).
A (Accountable): Head of Data/CDO.
C (Consulted): Compliance/Legal/DPO (PII/residency/Legal Hold), Finance (FX/GGR), Risk (RG/AML), SRE (SLO/стоимость).
I (Informed): BI/Продукт/Маркетинг/Операции.

16) Дорожная карта внедрения

1. Lakehouse Bronze/Silver (ACID) + CDC/инкременты для Payments/Gameplay.

2. DQ-как-код (10–15 правил) и базовые дашборды Freshness/Completeness.

3. Первая Gold-витрина (GGR Daily) с SLA «до 06:00», WORM-экспорт с подписью.

4. Оркестрация DAG и алерты на SLA/DQ.

• Расширение доменов, SCD II для users/games/providers.
• Семантический слой метрик; lineage/impact-анализ; процедуры backfill/reprocessing.
• Регионализация (EEA/UK), RLS/CLS, контроль стоимости (квоты/chargeback).

• Реплей-симулятор (what-if), автогенерация документации витрин/метрик.
• Cost-оптимизация (кластеризация, материализация, TTL, компакция).
• DR-учения и time-travel восстановление.

17) Чек-лист перед продом

• Контракты/схемы в Registry, тесты совместимости зеленые.
• CDC/инкременты и MERGE идемпотентны; дедуп на ingest.
• DQ-правила активны (critical → fail + DLQ), SLA-дашборды настроены.
• Gold-витрины документированы, формулы метрик в семантическом слое.
• RBAC/ABAC, шифрование, резидентность, DSAR/RTBF/Legal Hold проверены.
• Компакция/OPTIMIZE/VACUUM по расписанию; лимиты на backfill/реплеи.
• Runbook’и инцидентов и reprocessing, аудит экспортов (WORM+hash).

18) Анти-паттерны и риски

Full reload «на всякий случай»: используйте CDC/инкременты.
Смешение сырых и отчетных данных: держите Bronze/Silver/Gold отдельно.
Отсутствие DQ и lineage: нет доказуемости и воспроизводимости.
PII в аналитических слоях: изолируйте маппинги, применяйте CLS/RLS.
Монолитные «ночные» джобы: дробите, параллельте по партициям.
Игнор стоимости: следите за small files, материализуйте агрегаты, вводите квоты.

19) Глоссарий (кратко)

ETL/ELT — извлечение/трансформации/загрузка (до/после загрузки).
CDC — захват изменений.
SCD — историзация измерений (I/II/III).
WORM — неизменяемое хранение отчетных пакетов.
Time-travel — чтение исторических версий таблиц.

20) Итог

Современный ETL/ELT — это не скрипты, а управляемая платформа: контракты и DQ, идемпотентные инкременты/CDC, дисциплина слоев Bronze/Silver/Gold, наблюдаемость и SLO, приватность и экономичность. Следуя этому руководству, вы получите воспроизводимые и аудируемые конвейеры, стабильно питающие отчетность, продукт и модели в масштабе и без сюрпризов.