Processus ETL/ELT

1) Destination et contexte

Les pipelines ETL/ELT assurent le chargement, la transformation et la publication prévisibles des données pour la production de rapports (GGR/NGR, régulateurs), les analyses/ML et les panneaux opérationnels.

ETL : nous transformons avant le chargement en DWH/Lakehouse (moins souvent dans les staks modernes).
ELT : nous expédions d'abord à Lakehouse (Bronze/Silver), puis nous transformons SQL/moteurs (recommandé).

2) Architecture de référence

1. Ingest/Edge : HTTP/gRPC/Batch, CDC de l'OLTP, fournisseurs de S3/FTP de déchargement.
2. Bronze (raw, append-only) : payload's invariables, lots par date/marché/tenant.
3. Silver (clean/confort) : normalisation, dedup, handbook, SCD, FX/timesons.
4. Gold (serve) : vitrines dénormalisées sous BI/régulateur/modèle.
5. Orchestration : Airflow/Dagster/Prefect (DAG 'et, SLA, retraits, décalages).
6. DQ/Contracts: Schema Registry + DQ-как-код, consumer-driven tests.
7. Observabilité : métriques des pipelines, lineage, logs, cost-dashboards.

3) Sélection ETL vs ELT

Pratique : Dans iGaming - ELT + CDC : rapidement expédié, puis standardisé et compté.

4) Incréments et CDC

• CDC (Debezium/log-réplication) : modifications OLTP → Bronze → MERGE dans Silver.
• Watermark par heure : 'updated _ at> max_loaded_ts'.
• Hash-diff : comparaison 'md5 (row)' pour un détail de changement.
• Upsert/MERGE : idempotence des téléchargements.

sql
MERGE INTO silver. payments s
USING stage. payments_delta d
ON s. transaction_id = d. transaction_id
WHEN MATCHED THEN UPDATE SET
WHEN NOT MATCHED THEN INSERT;

5) Contrats et régimes

Schema-first : JSON/Avro/Protobuf dans Registry ; 'schema _ version' dans les événements/fichiers.
Évolution : back-compatible (ajouts nullables) ; breaking - '/v2 '+ double enregistrement.
Champs obligatoires : 'event _ time (UTC)', 'event _ id', 'trace _ id', 'user _ pseudo _ id', 'market'.

6) DQ-comme-code (ensemble minimum)

yaml table: silver. payments owner: data-payments slo:
freshness_minutes: 15 completeness_percent: 99. 5 rules:
- name: unique_tx # uniqueness of transactions type: unique columns: [transaction_id]
severity: critical
- name: currency_whitelist type: in_set column: currency set: [EUR,USD,GBP,TRY,BRL]
severity: major
- name: amount_positive type: range column: amount_base min: 0. 01 severity: critical
- name: fk_user type: foreign_key column: user_pseudo_id ref_table: dim. users_scd severity: critical

7) Orchestration : DAG 'et, dépendances, SLA

Conception DAG : des sources aux vitrines ; les dépendances évidentes entre les tâches.
Retrai et idempotence : backoff, répétitions « pures », checkpoint.
Les changements (catchup) : un dogon soigné de périodes manquées.
SLA : par exemple Gold. le quotidien est prêt avant 06h00 heure locale ; alertes en cas d'irrégularités.
Paramétrage : marchés/tenants/dates via vars ; un modèle unique de job's.

8) Idempotence et exactly-once

Sur ingest : les doublons sont possibles → le dedup par '(event_id, source)'.
En traitement : upsert/merge ; les fonctions « pures » des transformations.
Dans sink : commits transactionnels ou writes idempotent ; contrôle de la « double comptabilité ».
Outbox/Inbox : publication transactionnelle d'événements de domaine à partir d'OLTP.

9) Backfill и reprocessing

Backfill : remplissage primaire/fourchettes historiques.
Reprocessing : recalculer lorsque la logique/les corrections changent.
Guardrails : limites des fourchettes, quotas, fenêtres temporelles, dry-run avec comparaison des métriques.
Marquage : 'logic _ version', 'reprocessed _ at', 'recalc _ reason'.

10) Simulation Silver/Gold

Silver (3NF/BCNF) : les faits « fact _ bets/payments/payouts », les mesures « bou _ users/games/providers/markets (SCD II) », la normalisation des monnaies/timzone.
Gold : vitrines dénormalisées sous BI/régulateur/modèle ; Paquets d'exportation immuables (WORM) + signature.

Exemple Gold : GGR Daily

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

11) Vie privée et résidence

Minimisation des PII : Tokénisation ; les mappings d'ID réels dans un circuit isolé.
RLS/CLS : Politiques d'accès par rôle/compétence, masquage.
Résidence : catalogues/clés séparés pour l'EEE/UK/BR ; interdiction des join's cross-régionaux sans fondement.
DSAR/RTBF & Legal Hold : édition sélective, archives WORM pour le reporting, audit des exportations.

12) Observabilité et SLO

• Freshness Silver p95 ≤ 15 min ; Gold Daily est prêt avant 06:00 lok. du temps.
• Completeness ≥ 99. 5 %, Validation (schéma) ≥ 99. 9%.
• Succès de job's ≥ 99. 0 %, MTTR incidents ≤ 24-48 heures

Dashboards : Freshness heatmap, vortex de perte DQ, cost/query & cost/GB, lineage-graphe.

13) Productivité et coût

Lot : date/marché/tenant ; clustering/Z-order par filtres.
Formats : Parquet + ACID (Delta/Iceberg/Hudi), compression et statistiques.
Compaction : lutte contre les petits fichiers (OPTIMIZE/VACUUM).
Matérialisation : agrégats stables ; éviter les gigantesques join's on-the-fly.
Chargeback : budgets, quotas de relais/backfill ; planification dans les fenêtres à faible charge.

14) Exemples de tâches types DAG (pseudo-code Airflow)

python with DAG("elt_payments_daily", schedule="@daily", start_date=..., catchup=True) as dag:
extract = BashOperator(task_id="extract_cdc", bash_command="run_cdc_to_bronze. sh {{ ds }}")
load  = BashOperator(task_id="load_to_silver", bash_command="sql/run_merge_silver. sql {{ ds }}")
dq   = BashOperator(task_id="dq_checks", bash_command="dq/run_checks. sh silver. payments {{ ds }}")
gold  = BashOperator(task_id="build_gold_ggr", bash_command="sql/build_gold_ggr. sql {{ ds }}")
export = BashOperator(task_id="export_regulator", bash_command="export/run_worm_pack. sh {{ ds }}")

extract >> load >> dq >> gold >> export

15) Processus et RACI

R (Responsible) : Data Engineering (DAG 'et, modèles Silver/Gold), Data Platform (infra, Registry, DQ).
A (Accountable): Head of Data/CDO.
C (Consulted): Compliance/Legal/DPO (PII/residency/Legal Hold), Finance (FX/GGR), Risk (RG/AML), SRE (SLO/стоимость).
I (Informed) : BI/Produit/Commercialisation/Opérations.

16) Feuille de route pour la mise en œuvre

1. Lakehouse Bronze/Argent (ACID) + CDC/Incréments pour Paiements/Gameplay.

2. DQ-comme-code (10-15 règles) et dashboards de base Freshness/Completeness.

3. Première vitrine Gold (GGR Daily) avec SLA « jusqu'à 06:00 », WORM-export signé.

4. Orchestration DAG et alerte sur SLA/DQ.

• Extension de domaine, SCD II pour users/games/providers.
• Une couche sémantique de métriques ; lineage/analyse d'impact ; procédures de backfill/reprocessing.
• Régionalisation (EEE/Royaume-Uni), RLS/CLS, contrôle de la valeur (quotas/chargeback).

• Replay-simulateur (what-if), autogénération de la documentation vitrine/métriques.
• Optimisation cost (clustering, matérialisation, TTL, compaction).
• Exercice DR et récupération du temps de voyage.

17) Chèque-liste avant la vente

• Contrats/schémas dans Registry, tests de compatibilité sont verts.
• les CDC/incréments et MERGE sont idempotentes ; dedup sur ingest.
• Les règles DQ sont actives (critique → fail + DLQ), les dashboards SLA sont configurés.
• Les vitrines d'or sont documentées, les formules des métriques dans la couche sémantique.
• RBAC/ABAC, cryptage, résidence, DSAR/RTBF/Legal Hold vérifié.
• Compaction/OPTIMIZE/VACUUM selon les horaires ; limites sur les backfill/replis.
• Runbook 'et incidents et reprocessing, audit des exportations (WORM + hash).

18) Anti-modèles et risques

Reload complet « au cas où » : utilisez CDC/incréments.
Mélange des données brutes et des données de déclaration : gardez Bronze/Argent/Or séparément.
Absence de DQ et de lignage : pas de probabilité et de reproductibilité.
PII dans les couches analytiques : isoler les mappings, appliquer CLS/RLS.
Jobes « nocturnes » monolithiques : écraser, parallèles par lots.
Ignorer le coût : surveiller les petits fichiers, matérialiser les agrégats, introduire des quotas.

19) Glossaire (bref)

ETL/ELT - extraction/transformation/chargement (avant/après chargement).
CDC - Capture des changements.
SCD - Historique des mesures (I/II/III).
WORM - Stockage invariable des paquets déclarants.
Time-travel - lecture des versions historiques des tables.

20) Résultat

L'ETL/ELT moderne n'est pas un script, mais une plate-forme gérée : contrats et DQ, incréments idempotent/CDC, discipline des couches Bronze/Silver/Gold, observabilité et SLO, confidentialité et rentabilité. En suivant ce guide, vous recevrez des convoyeurs reproductibles et audités qui alimentent de façon stable les rapports, les produits et les modèles à l'échelle et sans surprise.