Нормалізація даних

1) Призначення

Нормалізація усуває дублікати і аномалії оновлень, задає єдині довідники і ключі, робить дані узгодженими і дешевими в супроводі. У iGaming це критично для GGR/NGR, AML/RG-аналізів, регуляторної звітності, антифроду і ML.

2) Де нормалізуємо

Bronze (raw): не нормалізуємо - зберігання як є (append-only) для форензики.
Silver (clean/conform): основна нормалізація (3NF/BCNF, довідники, ключі, SCD).
Gold (serve): цільові вітрини - можлива керована денормалізація під читання/BI.

3) Базові принципи

1. Schema-first: всі таблиці мають явні схеми і ключі.
2. Єдині ідентифікатори: `user_pseudo_id`, `session_id`, `game_id`, `provider_id`, `transaction_id`.
3. Єдині довідники: валюти, ринки/юрисдикції, статуси KYC/RG, провайдери ігор, канали трафіку.
4. Час і валюта: зберігати'event _ time'( UTC) і нормалізований'amount _ base'+'fx _ source'.
5. Еволюція: семантичні версії, тільки сумісні зміни без «мовчать» розривів.
6. PII-мінімізація: користувач - через псевдо-ID; мапінг зберігається окремо, доступ обмежений.

4) Нормальні форми швидко

1НФ: атомарні значення, без масивів у колонках (масиви → child-таблиці).
2НФ: атрибути залежать від усього складеного ключа.
3НФ: немає транзитивних залежностей (атрибут залежить тільки від ключа).
BCNF: кожен детермінант - ключ. Застосовувати для «ядра» (payments/gameplay).

Практика: Silver-моделі платежів та ігрової активності тримаємо мінімум в 3НФ; більш сувору BCNF - для довідників і посилальних таблиць.

5) Еталонна доменна модель (Silver)

5. 1 Довідники

`dim. users'( псевдо-ID, країна, віковий діапазон, RG-статуси).
`dim. games'( game_id, provider_id, жанр, RTP, волатильність).
`dim. providers'( provider_id, тип, ліцензія).
`dim. markets'( код юрисдикції, регулятор).
`dim. fx_rates` (date, ccy_from, ccy_to, rate, fx_source).

5. 2 Факти (вузькі таблиці подій/транзакцій)

`fact. payments` (transaction_id, user_pseudo_id, amount_orig, currency, amount_base, market, event_time, psp_ref, method).
`fact. bets` (bet_id, user_pseudo_id, game_id, stake_base, stake_ccy, outcome, event_time).
`fact. payouts` (payout_id, user_pseudo_id, game_id, amount_base, event_time).

Зв'язки: факти ↔ довідники по стабільним ключам. Всі суми дублюємо в «вихідній валюті» і в «базовій» (amount_base), фіксуючи'fx _ source'.

6) Повільно мінливі вимірювання (SCD)

Type I (перезапис): орфографічні/некритичні виправлення.
Type II (історія): 'valid _ from/valid _ to/is _ current', аудит змін (наприклад, зміни RG-статусу).
Type III (альтернативна колонка): «до/після» для коротких порівнянь.

Рекомендація: для RG/KYC/маркетингового каналу - SCD II; для довідників ігор (RTP) - SCD II з валідацією впливу.

sql
CREATE TABLE dim. users_scd (
user_pseudo_id STRING,
country STRING,
rg_status STRING,
valid_from TIMESTAMP,
valid_to  TIMESTAMP,
is_current BOOLEAN
);

7) Дедуплікація і ключі

Сурогатні ключі (BIGINT/UUID) для внутрішніх зв'язків.
Природні ключі (наприклад,'transaction _ id'від PSP) - валідувати і зберігати окремо.
Дедуп по'( event_id, source)'на ingest + за бізнес-ключами в Silver.

sql
CREATE TABLE silver. payments AS
SELECT EXCEPT(rn) FROM (
SELECT p., ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_time) rn
FROM bronze. payment_events p
)
WHERE rn = 1;

8) Стандартизація валют і таймзон

'event _ time'- завжди UTC; для вітрин додаємо локаль/таймзону ринку.
Валюти: 'amount _ orig'і'amount _ base'( наприклад, EUR) +'fx _ source','fx _ rate _ used'.
Щоденна фіксація курсів: `dim. fx_rates' з джерелом і хеш-підписом.

sql
SELECT t. transaction_id,
t. amount_orig,
t. currency,
r. rate AS fx_rate_used,
t. amount_orig r. rate AS amount_base
FROM bronze. payment_events t
JOIN dim. fx_rates r
ON r. date = DATE(t. event_time) AND r. ccy_from = t. currency AND r. ccy_to = 'EUR';

9) Консистентність довідників

Єдиний реєстр довідників (games, providers, markets, currencies).
Валідатори DQ: 'in _ set', FK-посилання, унікальність, несуперечливість SCD.
Автогенерація «тонких» дименсий від зовнішніх джерел (провайдери ігор, країни, PSP).

10) Коли денормалізувати

• стабільних «широких» репортів (GGR, ризикові вітрини);
• прискорення BI-запитів/дашбордів;
• realtime-вітрин (ClickHouse/Pinot) під SLA читання.

• Джерелом істини залишається Silver.
• Денормалізовані поля - обчислювані/копіювані з Silver; Версіонування логіки.
• Будь-яка денормалізація документується і тестується на коректність.

11) Модель «зірка» і «сніжинка»

Зірка: один факт + плоскі вимірювання - простіше і швидше читання, дорожче запис/узгодження.
Сніжинка: вимірювання нормалізовані (підключені під-довідники) - менше дублікатів, складніше запити.

Рекомендація: в Gold частіше «зірка», в Silver - нормалізовані «сніжинки».

12) Еволюція схем (safe changes)

Back-compatible: додавання nullable колонок; нові довідкові значення з прапорами.
Breaking: перейменування/типозміна/семантичні зрушення - тільки через '/v2'і подвійний запис на період міграції.
Контракти: JSON/Avro схеми в registry, consumer-tests на сумісність.

13) DQ-контролі для нормалізації

• Унікальність ключів: `transaction_id`, `bet_id`.
• Референсна цілісність: FK на'dim.'.
• Валюти: `currency` из whitelist, `fx_rate_used` не NULL, `amount_base >= 0`.
• Час: 'event _ time'в розумному вікні; відсутність «майбутніх» подій.
• SCD-правильність: діапазони, що не перетинаються'valid _ from/valid _ to'.

14) Приклади SQL-моделей

sql
CREATE TABLE silver. fact_bets (
bet_id STRING PRIMARY KEY,
user_pseudo_id STRING NOT NULL,
game_id STRING NOT NULL,
stake_ccy DECIMAL(18,2) NOT NULL,
currency CHAR(3) NOT NULL,
stake_base DECIMAL(18,2) NOT NULL,
market CHAR(2) NOT NULL,
event_time TIMESTAMP NOT NULL
);

sql
CREATE VIEW gold. ggr_daily AS
SELECT
DATE(b. event_time) AS event_date,
m. 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. markets m ON m. code = b. market
JOIN dim. games g  ON g. game_id = b. game_id
GROUP BY 1,2,3;

15) Приватність і комплаєнс

Псевдонімізація користувача в Silver; зв'язку з реальним ID - в окремому захищеному контурі.
RLS/CLS і маскування полів (e-mail/PAN недоступні в аналітиці).
Регіоналізація каталогів/ключів, DPO-контроль на розширення схем.

16) Спостережуваність і lineage

Data lineage від Bronze → Silver → Gold, версії трансформацій і контрактів.
Метрики: completeness, validity, FK-помилки, дублікати, «дірки» в часі, вартість запиту.
Алерти при розривах довідників і FX-джерел.

17) RACI

R: Data Engineering (моделі Silver/Gold), Data Platform (регістр схем, DQ).
A: Head of Data/Architecture.
C: Compliance/DPO (PII/retention), Finance (FX/GGR), Risk (RG/AML).
I: BI/Продукт/Маркетинг/Операції.

18) Дорожня карта впровадження

1. Реєстр довідників (markets, currencies, providers, games).

2. Silver-моделі'fact. payments`, `fact. bets','dim.'( 3НФ), SCD II для'dim. users`.

3. Нормалізація валют/таймзон, базові DQ-правила (FK/uniqueness/in_set).

4. Перша Gold-вітрина (GGR Daily) і тести звірок.

• Розширення SCD, покриття ігрових подій, провайдерські конформні моделі.
• Автотести сумісності схем, симулятор міграцій, каталог метаданих.
• Оптимізація ключів/партій, кластеризація/Z-order.

• Політики денормалізації для Gold, SLA/вартість; темплейти «зірка/сніжинка».
• Автогенерація документації, граф lineage в дашбордах.
• Регіональні каталоги та ключі шифрування, DR-навчання.

19) Чек-лист якості

• Єдині ключі та довідники затверджені.
• Silver в 3НФ, SCD застосовані до «повільних» вимірювань.
• Валюти/таймзони нормалізовані;'fx _ source'фіксується.
• DQ-правила (FK/uniqueness/range/in_set) активні.
• Денормалізації документовані, тести коректності пройдені.
• Лінійдж і метрики свіжості/повноти видно на дашбордах.

20) Часті помилки і як їх уникнути

Змішування PII в аналітиці: відокремлюйте маппінги, застосовуйте CLS/RLS.
Недостатня нормалізація Silver: приводьте до 3НФ, інакше дорогі підтримка і помилки звірок.
FX «за фактом звіту»: курси повинні фіксуватися на події, а не «заднім» числом.
Немає SCD для ключових вимірювань: втрачається історія RG/KYC/каналів.
Перенормалізація Gold: надлишкові join'и → керована денормалізація.
Непрозора еволюція схем: використовуйте registry і consumer-tests.

21) Підсумок

Нормалізація - це дисципліна Silver-рівня: єдині ключі і довідники, 3НФ/BCNF для фактів і вимірювань, коректна історія (SCD) і стандартизація часу/валют. З таким «скелетом» Gold-вітрини стають передбачуваними, звіти - порівнянними, а вартість володіння - контрольованою.