Normalizarea datelor

1) Scop

Normalizarea elimină duplicatele și anomaliile actualizărilor, setează directoare și chei uniforme, face ca datele să fie consecvente și ieftine de întreținut. În iGaming, acest lucru este esențial pentru analizele GGR/NGR, AML/RG, raportarea de reglementare, antifraudă și ML.

2) În cazul în care ne normalizăm

Bronz (brut): nu este normalizat - depozitare așa cum este (numai adăugați) pentru criminalistică.
Argint (curat/conform): normalizare de bază (3NF/BCNF, directoare, chei, SCD).
Aur (servi): storefronturi țintă - denormalizarea controlată pentru citire/BI este posibilă.

3) Principii de bază

1. Schema-first-All tabele au scheme și chei explicite.
2. Identificatori unici: 'user _ pseudo _ id',' session _ id', 'game _ id',' provider _ id', 'transaction _ id'.
3. Directoare comune: valute, piețe/jurisdicții, statusuri KYC/RG, furnizori de jocuri, canale de trafic.
4. Timp și valută: stocați 'event _ time' (UTC) și normalizați 'sound _ base' + 'fx _ source'.
5. Evoluție: versiuni semantice, numai schimbări compatibile fără pauze „silențioase”.
6. PII minimizare: utilizator - prin pseudo-ID; cartografierea este stocată separat, accesul este restricționat.

4) Forme normale rapid

1NF: valori atomice, fără matrice în coloane (matrice → tabele pentru copii).
2NF-Attributes depinde de întreaga cheie compusă.
3NF: nu există dependențe tranzitive (atributul depinde doar de cheie).
BCNF: fiecare determinant este o cheie. Utilizare pentru „kernel” (plăți/gameplay).

Practică: Modelele de argint ale plăților și activității de joc păstrează cel puțin 3NF; mai stricte BCNF - pentru cărți de referință și tabele de referință.

5) Modelul domeniului de referință (Silver)

5. 1 Cărți de referință

"dim. utilizatorilor (pseudo-ID, țară, interval de vârstă, stări RG).
"dim. games '(game_id, provider_id, gen, RTP, volatilitate).
"dim. furnizorilor (provider_id, tip, licență).
"dim. piețe "(codul jurisdicției, autoritatea de reglementare).
"dim. fx_rates' (data, ccy_from, ccy_to, rata, fx_source).

5. 2 Fapte (eveniment îngust/tabele de tranzacții)

"fact. plăți "( , , , valută, , piață, , metodă).
"fact. pariuri "( , , , , rezultat, .
"fact. plăți "( , , , .

Link-uri: fapte ↔ ghiduri pe chei stabile. Duplicăm toate sumele în „moneda sursă” și în „bază” (amount_base), fixând „fx _ source”.

6) Schimbarea lentă a măsurătorilor (SCD)

Tipul I (suprascriere): corecții ortografice/non-critice.
Tipul II (istoric): 'valid _ from/valid _ to/is _ current', modificări de audit (de exemplu, modificări de stare RG).
Tipul III (coloană alternativă): „înainte/după” pentru comparații scurte.

Recomandare: pentru canalul RG/KYC/marketing - SCD II; pentru cărțile de referință ale jocului (RTP) - SCD II cu validare a impactului.

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) Eliminarea duplicatelor și a cheilor

Chei surogat (BIGINT/UUID) pentru link-uri interne.
Cheile naturale (de exemplu, "transaction _ id' de la PSP) - pentru a fi validate și stocate separat.
Dedup by '(event_id, sursă)' pentru a ingera + prin chei de afaceri în 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) Standardizarea valutară și fusul orar

'event _ time' - întotdeauna UTC; pentru vitrinele magazinelor, adăugați localizarea/fusul orar al pieței.
Valute: 'cuantum _ orig' şi 'cuantum _ base' (de exemplu, EUR) + 'fx _ source', 'fx _ rate _ used'.
Fixarea zilnică a cursurilor: "dim. fx_rates' cu sursă și semnătură hash.

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) Coerența cărților de referință

Registru director unificat (jocuri, furnizori, piețe, valute).
Validatoare DQ: 'in _ set', referințe FK, unicitate, consistența SCD.
Autogenerarea dimenciilor „subțiri” din surse externe (furnizori de jocuri, țări, PSP).

10) Când să denormalizeze

• rapoarte stabile „la scară largă” (RGG, vitrine de risc);
• Accelerarea interogărilor BI/tablouri de bord
• storefronturi în timp real (ClickHouse/Pinot) în lecturi SLA.

• Argintul rămâne sursa adevărului.
• Câmpuri denormalizate - calculate/copiate din argint; versioning logica.
• Orice denormalizare este documentată și testată pentru corectitudine.

11) Modelul de stea și fulg de zăpadă

Steaua: un fapt + măsurători plate - lectură mai ușoară și mai rapidă, scriere/potrivire mai scumpă.
Fulg de zăpadă: măsurătorile sunt normalizate (subdirectoare conectate) - mai puține duplicate, interogări mai complexe.

Recomandare: în aur mai des „stea”, în argint - normalizat „fulgi de zăpadă”.

12) Evoluția schemelor (modificări sigure)

Back-compatibil: adăugarea de coloane nullable; noi valori de referință cu steaguri.
Breaking: redenumire/tastare/schimburi semantice - numai prin „/v2 ”și intrare dublă pentru perioada de migrare.
Contracte: scheme JSON/Avro în registru, teste de compatibilitate pentru consumatori.

13) Controale DQ pentru normalizare

• Cheile sunt unice: 'transaction _ id',' bet _ id'.
• Integritatea de referință: FK pe „dim”.
• Valute: 'valută' din lista albă, 'fx _ rate _ used' nu NULL, 'sumă _ bază> = 0'.
• Timp: 'event _ time' într-o fereastră rezonabilă; fără evenimente „viitoare”.
• SCD-correct: intervale care nu se suprapun 'valid _ from/valid _ to'.

14) Exemple de modele 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) Confidențialitate și conformitate

Aliasing un utilizator în Silver; conexiune cu ID-ul real - într-un circuit protejat separat.
RLS/CLS și mascarea câmpului (e-mail/PAN nu sunt disponibile în analiză).
Regionalizarea directoarelor/cheilor, controlul DPO pentru extinderea schemei.

16) Observabilitate și descendență

Descriere de date de la Bronze → Silver → Gold, versiunea de transformări și contracte.
Valori: integralitate, valabilitate, erori FK, duplicate, „găuri” în timp, cost cerere.
Alerte la pauze în directoare și surse FX.

17) RACI

R: Ingineria datelor (modele Silver/Gold), Platforma de date (registru de circuit, DQ).
R: Șef de date/arhitectură.
C: Compliance/DPO (PII/retenție), Finance (FX/GGR), Risk (RG/AML).
I: BI/Produs/Marketing/Operațiuni.

18) Foaia de parcurs privind implementarea

1. Registru director (piețe, valute, furnizori, jocuri).

2. Realitatea modelelor argintii. plăţi „,” fapt. pariuri ',' dim. '(3HF), SCD II pentru' dim. utilizatori ".

3. Normalizarea valutei/fusul orar, regulile DQ de bază (FK/uniqueness/in_set).

4. Prima vitrină Gold (GGR Daily) și teste de reconciliere.

• Extinderea SCD, acoperirea evenimentelor de joc, modele conforme furnizorului.
• Schema de autotesturi de compatibilitate, simulator de migrare, catalog de metadate.
• Optimizare cheie/partid, clustering/Z-ordine.

• Politici de denormalizare pentru Gold, SLA/valoare; șabloane de stele/fulgi de zăpadă.
• Generarea automată a documentației, graficul descendenței în tablouri de bord.
• Directoare regionale și chei de criptare, exerciții DR.

19) Lista de verificare a calității

• Cheile și directoarele unice sunt aprobate.
• Argint în 3NF, SCD aplicat la măsurători „lente”.
• Valutele/fusurile orare sunt normalizate; 'fx _ source' este fixat.
• Regulile DQ (FK/uniqueness/range/in_set) sunt active.
• Denormalizări documentate, testele de corectitudine au trecut.
• Linage și prospețime/plinătatea metrici sunt vizibile pe tablouri de bord.

20) Greșeli frecvente și cum să le evitați

Amestecarea PII în analitică: mapări separate, utilizarea CLS/RLS.
Normalizarea insuficientă a argintului: duce la erori de sprijin 3NF, altfel costisitoare și de reconciliere.
FX „per raport”: Ratele ar trebui să fie capturate pe un eveniment, nu „backdated”.
Fără SCD pentru dimensiunile cheie: a pierdut istoria RG/KYC/canal.
Renormalizarea aurului: se alătură redundant → denormalizarea gestionată.
Evoluția opacă a sistemelor: utilizați registrul și testele de consum.

21) Linia de jos

Normalizarea este o disciplină de nivel argint: chei uniforme și cărți de referință, 3NF/BCNF pentru fapte și măsurători, istoria corectă (SCD) și standardizarea timpului/valutelor. Cu un astfel de „schelet”, cazurile de aur devin previzibile, rapoartele sunt comparabile, iar costul proprietății este controlat.