Конвеєри аналітики і ETL

(Розділ: Технології та Інфраструктура)

Коротке резюме

Аналітичний конвеєр перетворює «сирі» операційні події iGaming (ставки, депозити, вебхуки PSP, логи ігор) в стійкі вітрини метрик (GGR/NGR, LTV, ретеншн, антифрод-сигнали). Опорні принципи: єдина модель шарів (Bronze/Silver/Gold), інструментальна дисципліна DQ/lineage, інкрементальність і ідемпотентність, спостережуваність і SLO, контроль вартості. Рішення приймаються з урахуванням профілю навантаження (піки турнірів), регуляторики (PII/локалізація) і вимог бізнесу до свіжості даних.

1) Архітектури: ETL vs ELT, batch vs stream

ETL (Extract → Transform → Load): трансформації до завантаження в DWH. Підходить там, де трансформації вимагають контрольованого оточення/секретів до «хмари».
ELT (Extract → Load → Transform): сировина в Lake/Lakehouse/DWH, далі SQL/рушій (dbt/SQL-скрипти). Зручно для колонкових движків і гнучких ітерацій.
Batch: планові вікна (кожні 5/15/60 хвилин, nightly). Дешево і передбачувано.
Stream: почти real-time (Kafka → Flink/ksqlDB → OLAP). Для вітрин near-real-time (5-60 секунд) і сигналів антифроду/CRM.
Гібрид: Bronze наповнюється стрімом, Silver/Gold - інкрементальні batch-моделі.

Рекомендація: в iGaming тримати ELT + стрімінг: події через CDC/outbox → Bronze (хвилинна свіжість), інкрементальні трансформації в Silver/Gold.

2) Шарувата модель (Medallion)

Bronze (Raw): сирі події/CDC без бізнес-логіки. Формати Parquet/ORC, схеми як є, мінімальна валідація.
Silver (Conformed): чистка, дедуплікація, нормалізація ідентифікаторів, SCD вимірювань, уніфікація валют/часових поясів.
Gold (Marts): бізнес-вітрини (факти/виміри, куби), materialized views, передагрегації (дні/країни/продукти).

Плюси: відтворюваність, прозора еволюція, різні SLO і TTL по шарах.

3) Джерела та навантаження: CDC, outbox, файли

CDC (Change Data Capture): потоки змін з OLTP (Postgres/MySQL) з гарантією порядку та ідемпотентності.
Outbox-патерн: події записуються в таблицю/колекцію outbox в транзакції сервісу → конектор публікує в шину/озеро.
Файлове завантаження: PSP-вивантаження, партнерські звіти; використовуйте маніфести, контроль дублів (checksum) і каталоги прийому.

Практики: джерела версіонуються (schema version), для кожного джерела - контракт полів і очікувань якості.

4) Оркестрація: DAG, залежності, деплою

DAGі: явні залежності (raw → staging → dims → facts → marts).
Ідемпотентність завдань: повторний запуск без побічних ефектів (partition-overwrite,'MERGE '/upsert).
Розділення оточень: Dev/Stage/Prod, промоушен артефактів, «ручні ворота» (manual approval) для дорогих backfill.
Планування: cron/тимчасові вікна + event-тригери (по приходу файлів/партій).
Секрети: з секрет-менеджера; заборона на секрети в коді DAG.

python with DAG("dwh_daily", schedule="0  ") as dag:
bronze = ingest_cdc(source="payments", partition=hour())
silver = dedup_normalize(input=bronze)
dims  = build_dimensions(input=silver)
facts = build_facts(input=silver, dims=dims)
marts = build_marts(input=facts)
bronze >> silver >> [dims, facts] >> marts

5) Якість даних (DQ) і lineage

DQ-чеки: повнота (count, late arrivals), унікальність ключів, діапазони/доменні правила (сума ≥ 0, валюта в довіднику).
Поріг спрацювання: жорстка зупинка/soft-fail з алертом залежно від критичності таблиці.
Lineage/каталог: від репорту до джерела (таблиці, колонки, метрики), власники, документація, класифікація PII.
Контроль схем: автоматичні тести сумісності (backward-/forward-compatible), алерт на «ламаючі» зміни.

6) Моделювання: SCD, surrogate keys, нормалізація

SCD2 для вимірювань: 'valid _ from/valid _ to/is _ current', surrogate key ('_ sk') і натуральний ключ ('_ id').
SCD1: перезапис для несуттєвих атрибутів (наприклад, локаль інтерфейсу).
Surrogate keys: стабільні'_ sk'для join, natural keys - для унікальності.
Нормалізація вимірювань: snowflake там, де ієрархії глибокі; інакше star заради швидкості.

7) Інкрементальні моделі та партіонування

Водяний знак ('updated _ at','ingest _ ts'): читати тільки нові/змінені рядки.
Інкрементальні стратегії: «MERGE» за бізнес-ключами, «INSERT OVERWRITE» за партіями, «DELETE + INSERT» для малих партій.
Партіонування: за датою/годиною/регіоном; кластеризація (sort keys/Z-order) за ключами фільтрації і join.
Матеріалізовані уявлення: передагрегація GGR/NGR, кеш популярних перерізів.
Approx-агрегати: HLL/approx_distinct для дешевих вітрин top-N.

sql
MERGE INTO fact_deposits f
USING staging_deposits s
ON (f. deposit_id = s. deposit_id)
WHEN MATCHED THEN UPDATE SET amount = s. amount, status = s. status, updated_at = s. updated_at
WHEN NOT MATCHED THEN INSERT (...)
VALUES (...);

8) Backfill, reprocessing і управління історією

Backfill: окремі DAGі з лімітами ресурсів і вікнами; чітке «вікно істини» (наприклад, 2024-01-01.. 2025-11-05).
Reprocessing: детерміновані трансформації → повторний прогін дає однаковий результат. Логування версій коду моделей.
Time-travel/версії таблиць: зручно для розслідувань і DR «логічних помилок».
Retraction: політика відгуків даних (видалення/виправлення) з протоколюванням.

9) CLO/SLA/SLO конвеєра

Свіжість (freshness): Bronze ≤ 1-5 хв, Silver ≤ 15 хв, Gold ≤ 60 хв (приклад).
Надійність: відсоток успішних прогонів DAG ≥ 99. x%.
Продуктивність: p95/p99 тривалості вузлів; бюджет часу на партію.
Lag моніторинг: відставання ingest-стріму, глибина черг, частка «late data».
Алерти: порушення свіжості/обсягу, DQ-фейли, зростання вартості сканів, деградація MV.

10) Вартість: прогнозування та оптимізація

Партії і кластери мінімізують обсяг сканів.
Матеріалізація гарячих маркерів (дні/країни/продукти).
Кеш результатів/MVs для часто використовуваних дашбордів.
Контроль частоти перезапусків (ніяких «кожні 5 хвилин» без причини).
TTL: агресивна ретеншн Bronze, середня Silver, довга Gold (тільки агрегати).
Capacity planning: каталожні метрики, прогноз піків турнірів/кампаній.

11) Безпека, PII і локалізація

Класифікація даних: PII/фінансові/операційні.
Шифрування: в спокої і в транзиті; KMS/роль-базований доступ.
Де-ідентифікація: хешування/маскування, окремі стовпці з ключами.
RLS/хуртовини для мульти-тенантності (по'tenant _ id').
Локалізація: зони зберігання та обробки по регіонах (EU/TR/LATAM); експорт тільки в дозволені локації.
Аудит: читання/запису в критичні таблиці, доступ до каталогу.

12) Спостережуваність: метрики, логи, трейси

Метрики конвеєра: тривалість завдань, черга, помилки, ретраї, обсяг оброблених байтів/рядків, вартість.
Логи: структуровані; кореляція по'trace _ id '/' run _ id'.
Трейсинг: від джерела до вітрини (ingest → transform → load → BI).
Дашборди: свіжість шарів, успіх DAGів, топ-дорогих запитів, p95/p99.

13) Інструменти (орієнтири за ролями)

Оркестрація: DAG-оркестратори (з планувальником, ретраями, алертами, секретами).
Трансформації: SQL-моделювання («моделі як код»), юніт-тести моделей, документація.
DQ/контракти: фреймворки перевірок і SLA на набори даних.
Лінеїдж/каталог: автоматична побудова графа залежностей, пошук власника.
Стрімінг: процесори вікон/агрегацій, конектори sink/source.

(Конкретні вендори підбираються під стек компанії і вимоги безпеки.)

14) Приклади шаблонів

Шаблон вітрини GGR (узагальнений SQL)

sql
CREATE OR REPLACE TABLE mart_ggr_daily AS
SELECT
DATE(b. ts) AS d,
c. country_code,
SUM(b. stake) AS stake_sum,
SUM(b. win)  AS win_sum,
SUM(b. stake - b. win) AS ggr
FROM fact_bets b
JOIN dim_country c ON c. country_sk = b. country_sk AND c. is_current
WHERE b. ts >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY)
GROUP BY d, c. country_code;

Інкрементальна модель з «водяним знаком»

sql
INSERT INTO fact_bets PARTITION (dt)
SELECT
FROM staging_bets
WHERE updated_at > (SELECT COALESCE(MAX(watermark), '1970-01-01') FROM _meta_watermarks WHERE table='fact_bets');
-- then update watermark

DQ-перевірки (ідея)

sql
-- 1) key uniqueness
SELECT deposit_id FROM fact_deposits GROUP BY deposit_id HAVING COUNT()>1;

-- 2) negative amounts (error)
SELECT FROM fact_deposits WHERE amount < 0;

15) Чек-лист впровадження

1. Визначте словник метрик (GGR/NGR/LTV/Retention) і власників.
2. Зафіксуйте SLO свіжості по шарах Bronze/Silver/Gold.
3. Стандартизуйте контракти джерел (схеми, DQ, SLA).
4. Побудуйте DAG-граф з ідемпотентними кроками та ізольованими секретами.
5. Реалізуйте інкрементальність (MERGE/overwrite за партіями) і «водяні знаки».
6. Увімкніть DQ (критичні/м'які перевірки), lineage і каталог даних.
7. Налаштуйте спостережуваність (метрики, логи, трейси) та алерти.
8. Введіть ретеншн/TTL і політику backfill/reprocessing.
9. Забезпечте PII-контроль, шифрування, RLS і локалізацію.
10. Проводьте game-day: імітація падіння джерела, що «ламають» схем, масового backfill.

16) Антипатерни

«Один нічний ETL на все» без партій та інкрементальності.
Відсутність DQ і lineage → конфліктуючі звіти і «полювання на привиди».
Повна переробка таблиць при кожному запуску (вибух вартості).
Жорстка зв'язка на реальному часі без буферів/ретраїв.
Змішування PII і публічних вітрин без сегментації і маскування.
Відсутність політики retraction/видалень (неможливо виправити помилки).

Підсумки

Стійкий конвеєр аналітики в iGaming - це ELT + стрімінгове завантаження в шарувату модель з жорстким DQ/lineage, інкрементальними моделями, прозорим оркестратором і вимірними SLO. Додайте контроль вартості, політику PII/локалізації, регулярні backfill/DR-навчання - і ваша аналітична платформа буде надійно масштабуватися під турнірні піки, відповідаючи бізнесу даними потрібної свіжості і якості.