Travailler avec des données historiques

1) Désignation et principes

Objectif : Stocker et traiter les états passés de manière à ce que les rapports, les modèles et les enquêtes soient reproductibles, précis et cohérents.

• Time-aware by design : modèles de temps explicites dans les schémas et les requêtes.
• Fiabilité : le même rapport de date D donne toujours le même résultat.
• Auditabilité : origine prouvable (lineage), couches immuables, WORM là où tu veux.
• Cost-aware : couches archivées, compression, cold storage avec SLA clair.
• Privacy-by-design : gestion des IPI dans les opérations rétrospectives et les demandes légales.

2) Modèles de temps

Heure de l'événement : heure de l'événement réel (pari, dépôt).
Temps de traitement : lorsque le système a traité l'enregistrement (peut varier).
Bitemporal : stockage à la fois event- et processing-time pour les modifications rétroactives.
Intervalles de validation : 'valid _ from', 'valid _ to', 'is _ current'.
As-of-queries : échantillonnage de données "comme on le savait à l'instant T'.

sql event_time TIMESTAMP, -- event time processed_at TIMESTAMP, -- TIMESTAMP valid_from processing time, -- start of version validity valid_to TIMESTAMP, -- end of validity (NULL if current)
is_current   BOOLEAN

3) Couches de stockage et formats

Lakehouse : Bronze (raw append-only) → Argent (clean/SCD/normalisation) → Or (vitrines).
ACID-форматы: Delta/Iceberg/Hudi (MERGE/Upsert, time-travel, snapshots).
Tiered storage : hot/warm/cold + WORM pour les artefacts réglementaires.
Lot : 'event _ date', 'market', 'tenant' ; clustering/Z-order par prédicats fréquents (user/game/provider).

4) Historique des mesures (SCD)

SCD I : réécriture - pour les modifications non critiques.
SCD II : histoire complète ; recommandé pour RG/KYC/canaux de trafic/attributs de jeu.
SCD III : « avant/après » sont des cas de comparaison rares.

sql
MERGE INTO dim. users_scd t
USING stage. users u
ON t. user_pseudo_id = u. user_pseudo_id AND t. is_current = TRUE
WHEN MATCHED AND (t. rg_status <> u. rg_status OR t. country <> u. country) THEN
UPDATE SET is_current = FALSE, valid_to = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (user_pseudo_id, country, rg_status, valid_from, valid_to, is_current)
VALUES (u. user_pseudo_id, u. country, u. rg_status, CURRENT_TIMESTAMP, NULL, TRUE);

5) Histoire des faits : images et bitemporal

Snapshots (snapshots) : Un instantané des agrégats à la fin de la journée/du mois (par exemple, le solde du portefeuille) accélère la reconstitution des rapports historiques.
Faits Bitemporal : nous enregistrons le temps d'événement et le temps de traitement pour distinguer les corrections tardives des calculs rétrospectifs.
Histoire exactly-once : dedup par 'event _ id' + idempotent MERGE.

6) Temps-voyage et reproductibilité

Temps-voyage : lecture des tables "à l'instant T'pour débogage, incidents, sverok.
Versionage logique : artefacts de transformation (versions SQL/DBT, conteneurs) et étiquettes « logic_version » dans les tables de sortie.
Frozen outputs : Les artefacts de déclaration d'or sont enregistrés et ne sont pas réécrits, hash et journal d'exportation sont disponibles.

sql
SELECT
FROM silver. fact_bets VERSION AS OF 1678901234567
WHERE event_date = DATE '2025-10-31';

7) Backfill и Reprocessing

Backfill : primaire/dogme de la gamme historique.
Reprocessing : recalculer après correction de bugs ou changement de règles d'affaires.

• Idempotence (MERGE/upsert), fourchettes, quotas, « course sombre » (dry-run) avec comparaison des métriques.
• On marque le résultat : 'recalc _ reason', 'logic _ version', 'reprocessed _ at'.

1. Freeze courant Gold ; 2) vérification DLQ/DQ ; 3) une course Silver ; 4) comparaison des métriques ; 5) recycle Gold ; 6) publication et signature.

8) Rapprochements de précision (reconciliation)

Montants de contrôle : rapprochement des chiffres d'affaires/quantités avec OLTP, PSP/fournisseurs.
Vérification à deux boucles : pipeline indépendante sur l'échantillon (comparaison A/B).
Tolérances : Par exemple, écart GGR ≤ 0. 2 % par jour.

sql
-- Duplicates
SELECT transaction_id, COUNT() c
FROM silver. payments
GROUP BY transaction_id
HAVING COUNT() > 1;

-- Unknown Currencies/Markets
SELECT p. currency
FROM silver. payments p
LEFT JOIN ref. currencies r ON r. code = p. currency
WHERE r. code IS NULL;

9) Devises, heure, calendrier : correction historique

FX à la date de l'événement : nous fixons 'fx _ rate _ used' et 'fx _ source'.
Heure du marché local : DST/temporisations via l'annuaire des calendriers.
Vacances/Saisonnalité : Tableau distinct du calendrier, utilisé dans les modèles et les rapports.

sql
SELECT p. transaction_id,
p. amount_orig,
r. rate AS fx_rate_used,
p. amount_orig r. rate AS amount_base,
r. fx_source
FROM bronze. payment_events p
JOIN dim. fx_rates r
ON r. date = DATE(p. event_time) AND r. ccy_from = p. currency AND r. ccy_to = 'EUR';

10) PII, conformité et Legal Hold

Minimisation des PII : pseudonyme, mapping sécurisé séparé.
DSAR/RTBF : projections et édition sélectives des couches historiques ; les exceptions à l'obligation légale de stockage sont documentées.
Legal Hold : drapeaux de « gel » des suppressions sur des plages/objets, WORM pour les artefacts de déclaration.
Audit : logiques d'accès et d'exportation immuables.

11) DQ et lineage pour l'histoire

yaml table: silver. fact_bets slo:
completeness_percent: 99. 5 freshness_minutes: 60 rules:
- name: unique_bet type: unique columns: [bet_id]
severity: critical
- name: market_known type: in_set column: market set_ref: ref. markets
- name: ts_in_range type: temporal expression: "event_time BETWEEN date_sub(now(), interval 5 year) AND now()"

Lineage : nous enregistrons les versions entrées/transformations/sorties ; le graphe des dépendances est obligatoire pour les rétroactions.

12) Productivité et coût

Lot : par date/marché/tenant ; clustering agressif par 'user _ pseudo _ id '/' game _ id' si vous filtrez souvent.
Formats : Parquet + statistiques/compression ; VACUUM/OPTIMIZE régulier.
Matérialisation : precompute pour les agrégations historiques « chères » ; snapshots pour les rapports trimestriels/annuels.
Archivage : traduction des anciens lots dans le cold storage (le SLA est documenté pour la restauration).
Sample : seulement pour les tâches de recherche, pas pour la réglementation/finance.

13) Fiches historiques pour ML

Feature registry : chaque ficha a une formule, owner, SLO, 'model _ version'.
Cohérence online/offline : une base de code de transformations, des tests de réfraction.
Dérive des caractéristiques : PSI/KS par période, stockage des distributions historiques.

14) Modèles de demandes

As-of (à la date) : reproductibilité des rapports.
Analyse cohort : cohortes d'inscriptions/premiers dépôts, fenêtres rolling.
Slowly changing facts: корректные join’ы с SCD II (`event_time BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31')`).

sql
SELECT b. bet_id, u. rg_status
FROM silver. fact_bets b
JOIN dim. users_scd u
ON u. user_pseudo_id = b. user_pseudo_id
AND b. event_time >= u. valid_from
AND (u. valid_to IS NULL OR b. event_time < u. valid_to);

15) Processus et RACI

R (Responsible) : Data Engineering (modèles/SCD/backfill), Data Platform (ACID/archive), Finance/Conformité (rapprochement/exigences de stockage).
A (Accountable): Head of Data/CDO.
C (Consulté) : Legal/DPO (DSAR/RTBF/Legal Hold), SRE (Cost/SLA), Architecture.
I (Informed) : BI/Produit/Commercialisation/Opérations.

16) Feuille de route pour la mise en œuvre

1. Tables ACID time-travel (Delta/Iceberg/Hudi) et lot de base.

2. SCD II pour les mesures clés (users/games/providers).

3. Snapshots quotidiens d'agrégats critiques (GGR Daily).

4. DQ-as-code (uniqueness/in_set/temporal) + lineage-graphe.

• Bitemporal faits, as-of API/SQL templates, runbooks backfill/reprocessing.
• FX/calendrier/enrichissement DST, rapprochement OLTP↔DWH/provaydery.
• Archivage cold storage, WORM pour les paquets de rapport, Legal Hold.

• Automatisation complète « replay & what-if », comparaison des métriques et des alertes de régression.
• Fiches historiques et contrôle de la dérive ML, charge au coût de stockage.
• Documentation des métriques et des rapports reproduits.

17) Chèque-liste avant la vente

• Les tables prennent en charge le temps-voyage ; les politiques de VACUUM/RETRAITE sont harmonisées.
• SCD II est mis en œuvre pour les mesures critiques ; join's testé.
• Les images des principaux agrégats sur D/M sont disponibles et vérifiées par scintillement.
• Les règles DQ sont actives ; lineage affiche les entrées/sorties et les versions de la logique.
• DSAR/RTBF/Legal Hold testés sur des couches historiques.
• L'archivage et la restauration à partir du cold storage sont documentés et validés.
• Coût du stockage sous contrôle (cost/GB, part cold, SLA de récupération).

18) Erreurs fréquentes et comment les éviter

Pas de modèle de temps explicite : ajoutez event/processing/validity.
FX « rétroactivement » : toujours le cours au moment de l'événement, stocker 'fx _ source'.
Join's incorrect avec SCD : utilisez l'intervalle de validation plutôt que 'is _ current'.
Muter les vitrines Gold : les rendements déclarés doivent être immuables (ou avec versioning).
Sans lineage/DQ : pas de probabilité et de points de contrôle - entrez-les dès le premier jour.
Coût non gérable : débranchez les lots chauds, aspirez, transférez-les en cold.

19) Glossaire

As-of Query - demande de données « à quoi ressemblaient-ils au moment T ».
Bitemporal - fixation simultanée de l'événement et du temps de traitement.
Snapshot est un instantané matérialisé de l'état/des agrégats à la fin de la période.
Time-travel - lecture des versions historiques des tables.
WORM (Write Once Read Many).

20) Résultat

Travailler avec des données historiques n'est pas seulement un « stockage long », mais une discipline temporelle : des modèles explicites d'événement/de traitement/bitemporal, SCD et snapshots, des requêtes reproductibles, des contrôles rigoureux et de conformité, une observabilité et une architecture de stockage économique. En suivant ce guide, vous obtiendrez une base historique solide pour le reporting, l'analyse et ML, résistant à la vérification et aux changements de la logique d'entreprise.