Normalisation des données

1) Destination

La normalisation élimine les doublons et les anomalies des mises à jour, définit des manuels et des clés uniques, rend les données cohérentes et bon marché accompagnées. Dans iGaming, c'est critique pour les analyses GGR/NGR, AML/RG, rapports réglementaires, antifrod et ML.

2) Où nous normalisons

Bronze (raw) : nous ne normalisons pas - stockage tel quel (append-only) pour forensica.
Silver (clean/confort) : normalisation de base (3NF/BCNF, manuels, clés, SCD).
Gold (serve) : vitrines cibles - dénormalisation contrôlée possible en lecture/BI.

3) Principes de base

1. Schema-first : toutes les tables ont des schémas et des clés explicites.
2. Identifiants uniques : 'user _ pseudo _ id', 'session _ id', 'game _ id', 'provider _ id', 'transaction _ id'.
3. Annuaires uniques : devises, marchés/juridictions, statuts KYC/RG, fournisseurs de jeux, canaux de trafic.
4. Heure et devise : stocker 'event _ time' (UTC) et normalisé 'amount _ base' + 'fx _ source'.
5. Évolution : versions sémantiques, seulement les changements compatibles sans les ruptures « fougueuses ».
6. PII-minimisation : l'utilisateur est via pseudo-ID ; le mapping est stocké séparément, l'accès est limité.

4) Formes normales rapidement

1NF : valeurs atomiques, pas de matrices dans les colonnes (matrices → child-tables).
2NF : les attributs dépendent de l'ensemble de la clé composite.
3NF : aucune dépendance transitive (l'attribut dépend uniquement de la clé).
BCNF : chaque déterminant est la clé. Appliquer pour le « noyau » (payments/gameplay).

Pratique : Les modèles silver de paiement et d'activité de jeu gardent un minimum de 3NF ; BCNF plus rigoureux - pour les manuels et les tableaux de référence.

5) Modèle de domaine de référence (Silver)

5. 1 Manuels

`dim. users '(pseudo-ID, pays, tranche d'âge, statuts RG).
`dim. games '(game_id, provider_id, genre, RTP, volatilité).
`dim. providers '(provider_id, type, licence).
`dim. markets '(code de juridiction, régulateur).
`dim. fx_rates` (date, ccy_from, ccy_to, rate, fx_source).

5. 2 Faits (tables d'événements/transactions étroites)

`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).

Liens : faits ↔ manuels sur les clés stables. Tous les montants sont dupliqués dans la « monnaie source » et dans la « monnaie de base » (amount_base) en fixant 'fx _ source'.

6) Mesures à changement lent (SCD)

Type I (réécriture) : corrections orthographiques/non critiques.
Type II (historique) : 'valid _ from/valid _ to/is _ current', contrôle des modifications (par exemple, changement d'état RG).
Type III (colonne alternative) : « avant/après » pour les comparaisons courtes.

Recommandation : pour le canal RG/KYC/Marketing - SCD II ; pour les guides de jeux (RTP) - SCD II avec validation d'influence.

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) Déduplication et clés

Clés de substitution (BIGINT/UUID) pour les liaisons internes.
Clés naturelles (par exemple, 'transaction _ id'de PSP) - valider et stocker séparément.
Dedup par '(event_id, source)' sur ingest + par clé d'entreprise dans 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) Standardisation des monnaies et du temps

'event _ time 'est toujours UTC ; pour les vitrines, nous ajoutons local/timzona du marché.
Devises : 'amount _ orig'et' amount _ base '(par exemple EUR) +' fx _ source ',' fx _ rate _ used '.
Fixation quotidienne des cours : 'bou. fx_rates' avec la source et la signature de hachage.

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) Cohérence des manuels

Un registre de référence unique (games, providers, markets, currencies).
Validateurs DQ : « in _ set », liens FK, unicité, cohérence SCD.
L'auto-génération des dimensies « fines » de sources extérieures (fournisseurs de jeux, pays, PSP).

10) Quand dénormaliser

• des répliques « larges » stables (GGR, vitrines à risque) ;
• accélérer les demandes BI/dashboards ;
• realtime-vitrine (ClickHouse/Pinot) sous lecture SLA.

• La source de la vérité reste Silver.
• Champs dénormalisés - calculés/copiés à partir de Silver ; le versioning de la logique.
• Toute dénormalisation est documentée et testée correctement.

11) Modèle « étoile » et « flocon de neige »

Étoile : un fait + mesures planes - plus facile et plus rapide à lire, plus cher à écrire/négocier.
Flocon de neige : les mesures sont normalisées (sous-guides connectés) - moins de doublons, plus de demandes.

Recommandation : dans l'or, plus souvent « étoile », dans l'argent - normalisé « flocons de neige ».

12) Évolution des schémas (changements sécurisés)

Back-compatible : ajout de colonnes nullables ; nouvelles valeurs de référence avec drapeaux.
Breaking : changement de nom/typage/décalage sémantique - uniquement via '/v2 'et double enregistrement pour la période de migration.
Contrats : JSON/Avro schémas en registry, tests de consommation sur l'interopérabilité.

13) Contrôles DQ pour la normalisation

• L'unicité des clés est 'transaction _ id', 'bet _ id'.
• Intégrité de référence : FK sur « bou. ».
• Devises : 'currency'de whitelist, 'fx _ rate _ used' non NULL, 'amount _ base> = 0'.
• Heure : 'event _ time' dans une fenêtre raisonnable ; l'absence d'événements « futurs ».
• Correct SCD : ne pas croiser les plages 'valid _ from/valid _ to'.

14) Exemples de modèles 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) Vie privée et conformité

Pseudonyme de l'utilisateur dans Silver ; Liens avec l'ID réel - dans un circuit sécurisé distinct.
RLS/CLS et masquage des champs (e-mail/PAN non disponible dans l'analyse).
Régionalisation des répertoires/clés, contrôle DPO pour l'extension des schémas.

16) Observabilité et lignage

Ligne de données de Bronze → Silver → Gold, versions transformations et contrats.
Métriques : compléteness, validation, erreurs FK, doublons, « trous » dans le temps, coût de la requête.
Alertes dans les ruptures de manuels et de sources FX.

17) RACI

R : Data Engineering (modèles Silver/Gold), Data Platform (registre des schémas, DQ).
A: Head of Data/Architecture.
C: Compliance/DPO (PII/retention), Finance (FX/GGR), Risk (RG/AML).
I : BI/Produit/Commercialisation/Opérations.

18) Feuille de route pour la mise en œuvre

1. Répertoire des manuels (markets, currencies, providers, jeux).

2. Modèles Silver 'fact. payments`, `fact. bets ', "bou." (3NF), SCD II pour "bou. users`.

3. Normalisation des monnaies/temporisation, règles DQ de base (FK/uniqueness/in_set).

4. La première vitrine d'or (GGR Daily) et les tests de sverok.

• Extension SCD, couverture des événements de jeu, modèles conformés de fournisseur.
• Auto-tests de compatibilité des schémas, simulateur de migration, répertoire de métadonnées.
• Optimisation des clés/lots, clustering/Z-order.

• Politiques de dénormalisation pour Gold, SLA/coût ; les timplates « star/flocon de neige ».
• Autogénération de la documentation, graphique lineage dans les dashboards.
• Catalogues régionaux et clés de cryptage, exercice DR.

19) Chèque de qualité

• Les clés et manuels uniques ont été approuvés.
• Silver en 3NF, SCD appliqué aux mesures « lentes ».
• Les monnaies/temporisations sont normalisées ; 'fx _ source' est fixe.
• Les règles DQ (FK/uniqueness/range/in_set) sont actives.
• Les dénormalisations sont documentées, les tests d'exactitude sont passés.
• La ligne et les métriques de fraîcheur/plénitude sont visibles sur les dashboards.

20) Erreurs fréquentes et comment les éviter

Mélange de PII en analyse : séparer les mappings, appliquer CLS/RLS.
Normalisation insuffisante Silver : conduisez à des 3NF, sinon le soutien coûteux et les erreurs de sverok.
FX « sur le fait du rapport » : les cours doivent être fixés sur l'événement et non « rétroactivement ».
Pas de SCD pour les mesures clés : l'histoire RG/KYC/canaux est perdue.
Renormalisation Gold : excès de join's → dénormalisation contrôlée.
Évolution opaque des schémas : utilisez registry et consumer-tests.

21) Résultat

La normalisation est une discipline de niveau Argent : clés et manuels uniques, 3NF/BCNF pour les faits et les mesures, histoire correcte (SCD) et normalisation temps/devises. Avec un tel « squelette », les vitrines d'or deviennent prévisibles, les rapports sont comparables et le coût de possession est contrôlé.