Normalizacja danych

1) Cel

Normalizacja eliminuje duplikaty i anomalie aktualizacji, ustawia jednolite katalogi i klucze, sprawia, że dane są spójne i tanie do utrzymania. W iGamingu ma to kluczowe znaczenie dla analizy GGR/NGR, AML/RG, sprawozdawczości regulacyjnej, antyfraud i ML.

2) Gdzie normalizujemy

Brąz (surowy): nie znormalizowany - przechowywanie, jak jest (tylko dodatek) do kryminalistyki.
Srebro (czyste/zgodne): podstawowa normalizacja (3NF/BCNF, katalogi, klucze, SCD).
Złoto (służyć): docelowe sklepy - kontrolowana denormalizacja do odczytu/BI jest możliwa.

3) Podstawowe zasady

1. Schemat-pierwszy-Wszystkie tabele mają wyraźne schematy i klucze.
2. Pojedyncze identyfikatory: 'user _ pseudo _ id',' session _ id', 'game _ id',' provider _ id', 'transaction _ id'.
3. Wspólne katalogi: waluty, rynki/jurysdykcje, statusy KYC/RG, dostawcy gier, kanały ruchu.
4. Czas i waluta: przechowywać 'event _ time' (UTC) i znormalizowaną 'amount _ base' + 'fx _ source'.
5. Ewolucja: wersje semantyczne, tylko kompatybilne zmiany bez „cichych” przerw.
6. Minimalizacja PII: użytkownik - poprzez pseudo-ID; mapowanie jest przechowywane oddzielnie, dostęp jest ograniczony.

4) Normalne formy szybko

1NF: wartości atomowe, brak tablic w kolumnach (tablice → tablice dla dzieci).
2NF-Attributes zależy od całego klucza złożonego.
3NF: brak zależności transitive (atrybut zależy tylko od klucza).
BCNF: każdy wyznacznik jest kluczem. Użyj dla „jądra” (płatności/rozgrywka).

Praktyka: Srebrne modele płatności i aktywności w grach utrzymują co najmniej 3NF; bardziej rygorystyczne BCNF - dla książek referencyjnych i tabel referencyjnych.

5) Wzorcowy model domeny (Silver)

5. 1 Księgi referencyjne

'dim. użytkowników (pseudo-ID, kraj, zakres wiekowy, statusy RG).
'dim. gry "(game_id, provider_id, gatunek, RTP, zmienność).
'dim. dostawców (provider_id, typ, licencja).
'dim. rynki "(kod jurysdykcji, organ regulacyjny).
'dim. fx_rates' (data, ccy_from, ccy_to, stawka, fx_source).

5. 2 Fakty (wąskie zdarzenia/tabele transakcji)

"fact. płatności "(transaction_id, user_pseudo_id, amount_orig, waluta, amount_base, rynek, event_time, psp_ref, metoda).
"fact. zakłady (bet_id, user_pseudo_id, game_id, stake_base, stake_ccy, wynik, event_time).
"fact. wypłaty "(payout_id, user_pseudo_id, game_id, amount_base, event_time).

Linki: fakty, poradniki dotyczące stabilnych kluczy. Duplikujemy wszystkie kwoty w „walutie źródłowej” i w „bazie” (amount_base), ustalając 'fx _ source'.

6) Powoli zmieniające się pomiary (SCD)

Typ I (nadpisanie): korekty pisowni/niekrytyczne.
Typ II (historia): 'valid _ from/valid _ to/is _ current', zmiany w audycie (na przykład zmiany stanu RG).
Typ III (kolumna alternatywna): „przed/po” dla krótkich porównań.

Zalecenie: dla RG/KYC/kanał marketingowy - SCD II; dla książek referencyjnych do gier (RTP) - SCD II z walidacją uderzeń.

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) Deduplicacja i klucze

Klucze zastępcze (BIGINT/UUID) dla linków wewnętrznych.
Klucze naturalne (na przykład 'transaction _ id' z PSP) - do weryfikacji i przechowywania oddzielnie.
Dedup przez '(event_id, źródło)' do połknięcia + przez klucze biznesowe w 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) Standaryzacja waluty i strefy czasowe

„event _ time” - zawsze UTC; dla okien sklepowych dodaj lokalny/timezon rynku.
Waluty: „amount _ orig” i „amount _ base” (na przykład EUR) + „fx _ source”, „fx _ rate _ used”.
Codzienne ustalanie kursów: 'dim. fx_rates' z podpisem źródłowym i hashem.

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) Spójność ksiąg referencyjnych

Jednolity rejestr katalogów (gry, dostawcy, rynki, waluty).
Walidatory DQ: 'in _ set', odniesienia FK, wyjątkowość, spójność SCD.
Autogeneracja „cienkich” wymiarów ze źródeł zewnętrznych (dostawcy gier, kraje, PSP).

10) Kiedy denormalizować

• stabilne „szerokie” sprawozdania (GGR, prezentacje ryzyka);
• Przyspieszenie zapytań BI/desek rozdzielczych
• realtime storefronts (ClickHouse/Pinot) pod odczytami SLA.

• Srebro pozostaje źródłem prawdy.
• Pola denormalizowane - obliczone/skopiowane z Silver; logika wersioning.
• Wszelkie denormalizacje są udokumentowane i testowane pod kątem poprawności.

11) Model gwiazdy i płatków śniegu

Gwiazda: jeden fakt + płaskie pomiary - łatwiejszy i szybszy odczyt, droższe pisanie/dopasowywanie.
Płatek śniegu: pomiary są znormalizowane (połączone podkatalogi) - mniej duplikatów, bardziej złożone zapytania.

Rekomendacja: w złocie częściej „gwiazda”, w srebrnym - znormalizowane „płatki śniegu”.

12) Ewolucja systemów (bezpieczne zmiany)

Kompatybilne z powrotem: dodawanie nieważnych kolumn; nowe wartości referencyjne z flagami.
Łamanie: zmiana nazwy/wpisanie/zmiany semantyczne - tylko przez '/v2'i podwójny wpis na okres migracji.
Umowy: systemy JSON/Avro w rejestrze, testy konsumenckie pod kątem zgodności.

13) Sterowanie DQ do normalizacji

• Klucze są unikalne: 'transaction _ id',' bet _ id'.
• Integralność odniesienia: FK na 'dim'.
• Waluty: 'waluta' z listy białej, 'fx _ rate _ used', nie NULL, 'amount _ base> = 0'.
• Czas: 'event _ time' w rozsądnym oknie; żadnych „przyszłych” wydarzeń.
• SCD-correct: non-overlapping ranges 'valid _ from/valid _ to'.

14) Przykłady modeli 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) Prywatność i zgodność

Aliasing użytkownika w Silver; połączenie z prawdziwym identyfikatorem - w oddzielnym obwodzie chronionym.
RLS/CLS i maskowanie pola (e-mail/PAN niedostępny w analityce).
Regionalizacja katalogów/klawiszy, sterowanie DPO dla rozszerzenia schematu.

16) Obserwowalność i rodowód

Rodowód danych z Brązu → Srebrny → Złoto, wersja transformacji i kontraktów.
Mierniki: kompletność, ważność, błędy FK, duplikaty, „otwory” w czasie, koszt żądania.
Wpisy przy przerwach w katalogach i źródłach FX.

17) RACI

R: Inżynieria danych (modele srebrne/złote), platforma danych (rejestr obwodów, DQ).
Odp.: Szef danych/architektury.
C: Zgodność/DPO (PII/zatrzymanie), Finanse (FX/GGR), Ryzyko (RG/AML).
I: BI/Produkt/Marketing/Operacje.

18) Plan działania na rzecz realizacji

1. Rejestr katalogów (rynki, waluty, dostawcy, gry).

2. Fakt srebrnych modeli. płatności „,” fakt. zakłady „,” dim' (3HF), SCD II dla 'dim. użytkowników.

3. Normalizacja waluty/strefa czasowa, podstawowe zasady DQ (FK/uniqueness/in_set).

4. Pierwszy Gold Showcase (GGR Daily) i testy pojednawcze.

• Rozszerzenie SCD, pokrycie wydarzeń w grze, dostawca zgodnych modeli.
• Autotest kompatybilności schematu, symulator migracji, katalog metadanych.
• Optymalizacja klucza/partii, klastrowanie/Z-order.

• Zasady denormalizacji złota, SLA/wartość; szablony gwiazdy/płatków śniegu.
• Automatyczna generacja dokumentacji, wykres linii w deskach rozdzielczych.
• Regionalne katalogi i klucze szyfrujące, ćwiczenia DR.

19) Lista kontrolna jakości

• Zatwierdzono pojedyncze klucze i katalogi.
• Srebro w 3NF, SCD stosowane do „powolnych” pomiarów.
• Waluty/strefy czasowe są znormalizowane; „fx _ source” jest stałe.
• Zasady DQ (FK/uniqueness/range/in_set) są aktywne.
• Udokumentowane denormalizacje, przeszedł testy poprawności.
• Na deskach rozdzielczych widoczne są wskaźniki wiązania i świeżości/pełności.

20) Częste błędy i jak ich uniknąć

Mieszanie PII w analityce: oddzielne mapowania, użyj CLS/RLS.
Niewystarczająca normalizacja srebra: prowadzi do 3NF, w przeciwnym razie kosztownego wsparcia i błędów w pojednaniu.
FX „na raport”: Stawki powinny być wychwytywane na zdarzeniu, a nie „wsteczne”.
Brak SCD dla kluczowych wymiarów: utracona historia RG/KYC/kanału.
Renormalizacja złota: zbędne połączenia → zarządzana denormalizacja.
Nieprzejrzysta ewolucja systemów: wykorzystanie rejestru i testów konsumenckich.

21) Najważniejsze

Normalizacja to dyscyplina na poziomie srebrnym: jednolite klucze i książki referencyjne, 3NF/BCNF dla faktów i pomiarów, poprawna historia (SCD) i standaryzacja czasu/walut. Przy takim „szkielecie” sprawy złota stają się przewidywalne, raporty są porównywalne, a koszt własności jest kontrolowany.