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

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-витрины становятся предсказуемыми, отчеты — сопоставимыми, а стоимость владения — контролируемой.