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, приватність і економічність. Слідуючи цьому керівництву, ви отримаєте відтворювані і аудіруемие конвеєри, стабільно живлять звітність, продукт і моделі в масштабі і без сюрпризів.