Un client passe de Standard à Premium le 1er juin. Vous actualisez sa fiche, puis relancez le chiffre d’affaires de mai par segment. Ses achats de mai apparaissent maintenant en Premium. Le montant total n’a pas changé, mais le récit commercial du mois a été réécrit.
Une dimension SCD de type 2 conserve plusieurs versions d’un même client pour rattacher chaque vente à son contexte de l’époque. L’historique ne devient pourtant fiable que si la vente retrouve la bonne version. Deux lignes client et une colonne « date de fin » ne suffisent pas.
Ce guide suit quatre ventes fictives. Vous verrez pourquoi une jointure peut changer leur segment, doubler leurs montants ou rater un événement arrivé en retard. Un script Python et SQL autonome reproduit le cas avec SQLite, sans compte cloud.
Choisir la question avant le type de dimension
Deux analyses peuvent être légitimes :
- « Combien avons-nous vendu aux clients qui étaient Premium lors de leur achat ? »
- « Combien ont dépensé les clients qui sont Premium aujourd’hui ? »
La première demande un historique. La seconde reclasse volontairement les achats anciens avec la segmentation actuelle. Les confondre produit des chiffres plausibles, avec le même total, mais une autre signification.
Une dimension de type 1 remplace la valeur précédente : elle convient lorsque seule la valeur actuelle compte, ou lorsqu’on corrige une erreur sans vouloir conserver cette erreur comme état métier. Le type 2 ajoute une version datée pour les attributs dont l’évolution doit rester analysable. On peut appliquer les deux règles à des attributs différents d’une même dimension. Guide Microsoft sur les dimensions et les changements historiques.
Pour notre cas, le segment doit rester celui de la vente. Le montant payé, lui, appartient à la vente : il ne faut pas le recalculer à partir d’un tarif actuel. Ce guide prolonge notre atelier sur le schéma en étoile, qui traitait les doubles comptes entre faits sans historiser les dimensions.
Un client, plusieurs versions, plusieurs clés
Le client C001 existe avant et après le 1er juin. Son identifiant métier ne change pas. La clé de sa version, elle, change : 101 pour Standard, puis 102 pour Premium. C002 reste Standard avec la clé 201.
| Clé de version |
Client |
Segment |
Début inclus |
Fin exclue |
| 101 |
C001 |
Standard |
2026-01-01 |
2026-06-01 |
| 102 |
C001 |
Premium |
2026-06-01 |
NULL |
| 201 |
C002 |
Standard |
2026-01-01 |
NULL |
Dans cet exemple, NULL signifie « pas de fin connue ». La clé de version est une clé technique, ou surrogate key : elle identifie une ligne précise de la dimension. C’est elle que la table des ventes conserve.
Kimball décrit ce principe : un changement suivi en type 2 crée une nouvelle ligne et une nouvelle clé technique, utilisée pour relier les faits à cette version. Définition du SCD type 2.
Le script commence avec C001 Standard et enregistre sa première vente. Il clôt ensuite cette version au 1er juin et insère Premium dans une même transaction. La vente déjà enregistrée garde sa clé 101. Un changement ordinaire de segment crée une nouvelle version ; il ne déplace pas les anciennes ventes vers cette version.
Nous supposons ici que la date métier du changement est connue et que les changements de dimension arrivent dans l’ordre. Les corrections rétroactives demandent un autre traitement, abordé plus loin.
Quatre ventes pour rendre l’erreur visible
Les montants sont fictifs, en euros, stockés en centimes entiers dans le script. Chaque ligne représente une vente unique.
| Vente |
Client |
Date de vente |
Date de réception |
Montant |
Version attendue |
| V1 |
C001 |
20 mai |
20 mai |
100 EUR |
101, Standard |
| V2 |
C001 |
1er juin |
1er juin |
60 EUR |
102, Premium |
| V3 |
C001 |
28 mai |
3 juin |
40 EUR |
101, Standard |
| V4 |
C002 |
2 juin |
2 juin |
80 EUR |
201, Standard |
Toutes les dates sont en 2026. Le total vaut 280 EUR. Avec le segment à la date de vente, Standard représente 220 EUR et Premium 60 EUR.
Si l’on joint chaque vente à la fiche actuelle du client, les trois achats de C001 deviennent Premium : 200 EUR Premium et 80 EUR Standard. Le total reste 280 EUR. Un contrôle limité au total ne détecte donc pas cette erreur de sens.
Si l’on joint seulement sur customer_id, sans filtrer les versions, chaque vente de C001 rencontre deux lignes. Ses 200 EUR sont comptés deux fois ; avec C002, le total incorrect atteint 480 EUR.
Le problème n’est pas la somme. C’est le nombre de versions auxquelles chaque vente correspond.
Utiliser un intervalle avec une seule borne incluse
La version Standard s’arrête exactement lorsque Premium commence. Nous choisissons des intervalles [début, fin[ : le début appartient à la période, la fin appartient à la suivante.
La recherche de la version pertinente s’écrit ainsi :
SELECT s.sale_id, d.customer_key
FROM staging_sales s
JOIN dim_customer d
ON d.customer_id = s.customer_id
AND s.sold_on >= d.valid_from
AND (d.valid_to IS NULL OR s.sold_on < d.valid_to);
V2, vendue le 1er juin, ne correspond qu’à Premium. V1 et V3 restent Standard. Microsoft décrit cette recherche temporelle pour obtenir la clé de dimension pertinente à la date du fait. SCD type 2 dans le guide du schéma en étoile.
Dans le contre-exemple du script, BETWEEN d.valid_from AND COALESCE(d.valid_to, '9999-12-31') remplace la fin NULL par une date lointaine. Mais BETWEEN inclut les deux extrémités : avec notre convention de bornes, la vente du 1er juin rencontre l’ancienne version et la nouvelle. Ces 60 EUR supplémentaires font passer le total à 340 EUR. Sans COALESCE, les versions à fin NULL ne correspondraient pas et ce jeu donnerait 200 EUR : une autre erreur. Sémantique de BETWEEN dans SQLite.
Une convention à fin incluse peut fonctionner si les périodes sont conçues autrement. Ce qui casse ici est le mélange d’une fin égale au début suivant avec une comparaison inclusive des deux côtés.
Les dates du script sont des chaînes ISO AAAA-MM-JJ, toutes au même format, et la précision métier est le jour. Si deux changements peuvent survenir dans une journée, ce modèle est trop grossier : il faut des horodatages cohérents, une précision suffisante et une règle de fuseau partagée.
Une vente tardive doit retrouver son passé
V3 arrive le 3 juin, alors que C001 est déjà Premium. Elle a pourtant été vendue le 28 mai. Sa recherche de version utilise le 28 mai, pas la date d’ingestion ni la date d’exécution du chargement.
Kimball traite explicitement ce cas : lorsqu’un fait arrive en retard, il faut rechercher les clés de dimension qui étaient valides au moment de l’événement. Late Arriving Facts.
Une fois la clé trouvée et contrôlée, le chargement inscrit 101 dans V3. Les rapports peuvent ensuite joindre directement cette clé :
SELECT d.segment, SUM(f.amount_cents) AS total_cents
FROM fact_sales f
JOIN dim_customer d
ON d.customer_key = f.customer_key
GROUP BY d.segment;
La recherche par période a lieu au rattachement. Le rapport lit la version retenue. Ajouter ici un filtre « version actuelle uniquement » ferait disparaître les ventes liées à 101. Pour une vue du portefeuille actuel, il faut préparer une autre lecture et la nommer clairement.
Contrôler les correspondances avant de charger
Une vente doit trouver exactement une version dans ce cas métier. Zéro correspondance signale un client absent, une date hors couverture ou un trou dans l’historique. Deux correspondances signalent un chevauchement. Un INNER JOIN seul peut masquer le premier défaut et multiplier le second.
Le script compte les clés candidates avant chaque insertion. Il refuse la vente si ce nombre diffère de un. Il teste aussi deux anomalies volontairement injectées : un client inconnu et une période supplémentaire qui chevauche les deux versions de C001.
Pour détecter les périodes qui se recouvrent, le contrôle SQL compare les versions d’un même client deux à deux :
SELECT a.customer_key, b.customer_key
FROM dim_customer a
JOIN dim_customer b
ON a.customer_id = b.customer_id
AND a.customer_key < b.customer_key
AND (b.valid_to IS NULL OR a.valid_from < b.valid_to)
AND (a.valid_to IS NULL OR b.valid_from < a.valid_to);
Le résultat doit être vide. Le contrôle « fin strictement après début » est également nécessaire ; il ne détecte pas, à lui seul, un chevauchement entre deux lignes.
Dans un chargement réel, les ventes refusées doivent rester traçables et rejouables après correction. Une autre politique peut utiliser une version « inconnue », à condition de la rendre visible et de prévoir sa résolution. Choisir arbitrairement la première correspondance ou supprimer les doublons après la jointure cache le problème.
Rejouer le cas, puis provoquer les défauts
Prérequis : Python 3 avec son module standard sqlite3, et savoir lancer une commande dans un terminal. Aucun paquet supplémentaire, fichier de données externe ou accès réseau n’est utilisé. Le script a été exécuté avec Python 3.14.7 et SQLite 3.53.4.
Téléchargez atelier-scd2.py, placez-vous dans son dossier, puis lancez :
python3 atelier-scd2.py
Sous Windows, si votre installation utilise le lanceur Python, la commande est py atelier-scd2.py. Le module sqlite3 est décrit dans la documentation Python.
Le programme crée une base en mémoire, charge V1, applique le changement de segment, puis charge les autres ventes dans leur ordre de réception. Il calcule les différentes lectures, injecte les deux défauts, vérifie les refus et annule ces injections. Il termine par un JSON dont voici les résultats essentiels :
{
"sales_keys": {"V1": 101, "V2": 102, "V3": 101, "V4": 201},
"historical_cents": {"Premium": 6000, "Standard": 22000},
"current_segments_cents": {"Premium": 20000, "Standard": 8000},
"correct_total_cents": 28000,
"wrong_business_key_only_total_cents": 48000,
"wrong_inclusive_between_total_cents": 34000,
"status": "PASS"
}
Les messages X0: 0 versions, chargement refusé et X2: 2 versions, chargement refusé sont attendus : ils prouvent que les anomalies injectées n’entrent pas dans les faits. Un échec de contrôle arrête le programme avec une exception. Chaque lancement repart d’une base vide en mémoire ; cela ne constitue pas une garantie de rejeu d’un pipeline de production.
L’atelier démontre le versionnement simple, le rattachement et ses contrôles. Il ne mesure ni performance à grande échelle ni comportement d’un outil cloud.
Ce que SCD2 ne peut pas reconstruire tout seul
Si le CRM écrase le segment et que vous n’avez conservé aucun historique, ajouter une date de début aujourd’hui ne recrée pas les états passés. Une capture quotidienne ne voit pas non plus les changements intermédiaires survenus entre deux captures. Il faut connaître la portée de la date disponible : début métier effectif, ou première observation par votre système.
Une vente arrivée en retard et un changement de segment rétroactif sont deux problèmes différents. Imaginons qu’on apprenne le 10 juin que C001 était Premium dès le 25 mai. V3, datée du 28 mai, doit alors être réexaminée. Les périodes de dimension et les clés des faits concernés peuvent nécessiter une correction. Kimball précise que les changements rétroactifs de type 2 peuvent conduire à réviser les faits associés. Late Arriving Dimensions.
Si vous devez conserver à la fois « ce qui était vrai à la date métier » et « ce que nous savions lors du rapport publié », une seule paire de dates ne suffit pas à représenter ces deux axes. Il faut concevoir aussi la trace des corrections. L’atelier ne couvre pas cette gestion.
La décision à prendre sur votre modèle
Commencez par une seule dimension et un attribut réellement utilisé dans vos analyses. Écrivez la question temporelle, fixez le sens des dates, puis prenez trois ventes : avant le changement, exactement à la frontière et arrivée en retard.
Votre modèle doit conserver une clé de version unique pour chacune, expliquer le résultat par segment et refuser les ambiguïtés. Si l’analyse attend seulement le portefeuille actuel, gardez cette simplicité. Si elle demande le contexte d’époque, la bonne preuve n’est pas l’existence d’une table historique : c’est le rattachement correct de chaque fait à cette histoire.
