SCD Type 1, 2, 3, 6 : historiser les dimensions en SQL (Snowflake, dbt)

Sans introduction, rien ne vaut un bon exemple pour comprendre le principe SCD alors imaginons que Marc déménage de Paris à Lyon. Côté data, qu'est-ce que tu fais de ton ancienne ligne avec la valeur Paris ? Tu l'écrases ? Tu en crées une nouvelle ? Tu gardes les deux quelque part ?

La réponse dépend du type de SCD (Slowly Changing Dimension) que tu mets en place sur ta dimension et dans cet article, on va voir les 4 types vraiment utilisés en entreprise : Type 1, Type 2, Type 3 et Type 6.

Exemple dataset

Une table customers toute simple, avec 3 lignes :

CREATE OR REPLACE TABLE customers (
    customer_id INT,
    name VARCHAR,
    city VARCHAR,
    segment VARCHAR
);

INSERT INTO customers VALUES
    (1, 'Marc', 'Paris', 'Standard'),
    (2, 'Sophie', 'Bordeaux', 'Standard'),
    (3, 'Karim', 'Marseille', 'Premium');

customers

Marc déménage à Lyon et passe en segment Premium. On va appliquer chaque type SCD à ce changement pour voir ce qu'il se passe concrètement.

SCD Type 0 : pas de changement

Le plus simple. La donnée ne bouge jamais, même si la source change. C'est le mode "lecture seule" appliqué à un champ.

Cas d'usage typiques : date_naissance, date_creation_compte, code_identifiant_original. Des champs qui n'ont pas de raison métier de changer.

Pas de code SQL spécifique car par convention, tu ne fais juste pas d'update sur ces colonnes.

SCD Type 1 : écraser l'ancienne valeur

L'update classique et sans rien de spécial, beaucoup le font par défaut sans savoir que c'est le Type 1. Tu remplaces l'ancienne valeur par la nouvelle, et tu ne gardes rien d'historique.

UPDATE customers
SET city = 'Lyon',
    segment = 'Premium'
WHERE customer_id = 1;

Avant

Après

Simple et lisible. Le problème c'est qu'on perd toute trace du fait que Marc était à Paris avant. Si demain un analyste veut savoir "combien de Premium étaient à Paris au T1 2026 ?", il aura du mal.

Donc le Type 1 est pertinent pour la correction des erreurs, champs purement descriptifs, ou dans le cas où le métier ne fait pas de reporting historique sur cette dimension.

SCD Type 2 : ajouter une nouvelle ligne avec versionning

Le plus utilisé en data engineering moderne. À chaque changement, tu ajoutes une nouvelle ligne et tu marques l'ancienne comme expirée. Pour ça, tu enrichis ta table avec 3 colonnes techniques : valid_from, valid_to, is_current.

CREATE OR REPLACE TABLE customers_scd2 (
    customer_id INT,
    name VARCHAR,
    city VARCHAR,
    segment VARCHAR,
    valid_from DATE,
    valid_to DATE,
    is_current BOOLEAN
);

-- État initial : Marc à Paris en Standard depuis 2024-01-01
INSERT INTO customers_scd2 VALUES
    (1, 'Marc', 'Paris', 'Standard', '2024-01-01', NULL, TRUE);

Marc déménage à Lyon le 15 mars 2026. On expire l'ancienne ligne et on en crée une nouvelle.

-- 1. Expirer l'ancienne ligne
UPDATE customers_scd2
SET valid_to = '2026-03-14',
    is_current = FALSE
WHERE customer_id = 1
  AND is_current = TRUE;

-- 2. Insérer la nouvelle version
INSERT INTO customers_scd2 VALUES
    (1, 'Marc', 'Lyon', 'Premium', '2026-03-15', NULL, TRUE);

Résultat

Pour récupérer la version courante :

SELECT * FROM customers_scd2 WHERE is_current = TRUE;

Pour récupérer l'état au 1er février 2026 (avant le déménagement) :

SELECT *
FROM customers_scd2
WHERE valid_from <= '2026-02-01'
  AND (valid_to >= '2026-02-01' OR valid_to IS NULL);

Utiliser une transaction

Faire un UPDATE puis un INSERT séparés, ça marche en dev mais ça pose problème en prod (si un crash arrive entre les deux, tu te retrouves avec une ligne expirée sans nouvelle ligne courante). Le bon réflexe c'est de toujours encapsuler les deux dans une transaction :

BEGIN;

-- 1. Expirer l'ancienne ligne
UPDATE customers_scd2
SET valid_to = CURRENT_DATE() - 1,
    is_current = FALSE
WHERE customer_id = 1
  AND is_current = TRUE;

-- 2. Insérer la nouvelle version
INSERT INTO customers_scd2 VALUES
    (1, 'Marc', 'Lyon', 'Premium', CURRENT_DATE(), NULL, TRUE);

COMMIT;

La transaction garantit que les deux opérations passent ou aucune. Si le serveur crash entre les deux, Snowflake rollback automatiquement et la table reste cohérente.

Pour un vrai merge atomique en une seule requete, il faut dupliquer les lignes source avec une colonne d'action pour distinguer expire et insert . C'est techniquement possible mais peu lisible.

SCD Type 3 : conserver l'ancienne valeur dans une colonne

Si tu veux garder une trace du dernier changement sans dupliquer la ligne, le Type 3 est l'option intermédiaire. Tu ajoutes une colonne previous_* à côté de la valeur actuelle.

CREATE OR REPLACE TABLE customers_scd3 (
    customer_id INT,
    name VARCHAR,
    city VARCHAR,
    previous_city VARCHAR,
    segment VARCHAR,
    previous_segment VARCHAR,
    last_change_date DATE
);

UPDATE customers_scd3
SET previous_city = city,
    city = 'Lyon',
    previous_segment = segment,
    segment = 'Premium',
    last_change_date = CURRENT_DATE()
WHERE customer_id = 1;

Résultat

Cas d'usage typiques de tracking d'une promotion par exemple (ancien poste vs nouveau poste d'un collaborateur), changement de catégorie tarifaire client (avant/après réabonnement), réorganisation territoriale (ancien secteur vs nouveau secteur) etc.. etc..

La limite est claire car tu perds tous les changements antérieurs au dernier. Si Marc passe de Paris → Lyon → Marseille → Toulouse, tu ne verras que previous_city = Marseille et city = Toulouse. Paris et Lyon sont perdus.

SCD Type 6 : le mélange (Type 1 + 2 + 3)

Le plus complet, et aussi le plus complexe à maintenir. Tu combines :

• Le versionning du Type 2 (plusieurs lignes avec valid_from/valid_to)
• Une colonne "valeur courante" dupliquée sur chaque ligne (Type 1 appliqué dans la structure Type 2)

CREATE OR REPLACE TABLE customers_scd6 (
    customer_id INT,
    name VARCHAR,
    city VARCHAR,
    current_city VARCHAR,
    segment VARCHAR,
    current_segment VARCHAR,
    valid_from DATE,
    valid_to DATE,
    is_current BOOLEAN
);

Quand Marc déménage à Lyon, tu insères la nouvelle ligne et tu mets à jour current_city et current_segment sur toutes les lignes du même customer_id (y compris les anciennes).

UPDATE customers_scd6
SET current_city = 'Lyon',
    current_segment = 'Premium'
WHERE customer_id = 1;

Résultat

L'avantage : sur n'importe quelle ligne tu peux à la fois savoir ce qui était valide à l'époque (city = Paris) et ce qui est valide aujourd'hui (current_city = Lyon). Sans jointure, sans sous-select.

Le coût c'est que la maintenance est plus lourde car à chaque update, il faut propager sur toutes les lignes historiques du customer_id. Les cas d'usage sont trés rare et donc à ne pas utiliser sans un vrai besoin métier.

Récap des 4 types

Pour 90% des projets, c'est Type 1 ou Type 2. Type 3 quand tu veux du "limited history" propre. Type 6 quand tu as un vrai besoin de croiser as-was et as-is.

Implémenter le Type 2 avec dbt snapshots

dbt gère le SCD Type 2 nativement avec la matérialisation snapshot. Tu écris le SELECT de ta source et dbt gère automatiquement les colonnes dbt_valid_from, dbt_valid_to et dbt_scd_id à chaque exécution.

-- snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}
    {{
        config(
            target_database='analytics',
            target_schema='snapshots',
            unique_key='customer_id',
            strategy='check',
            check_cols=['city', 'segment'],
        )
    }}

    SELECT customer_id, name, city, segment
    FROM {{ source('crm', 'customers') }}

{% endsnapshot %}

Puis :

dbt snapshot

À chaque exécution, dbt :

• Compare les valeurs actuelles de city et segment aux dernières versions stockées
• Expire l'ancienne ligne (dbt_valid_to = now()) s'il y a une différence
• Insère la nouvelle ligne (dbt_valid_from = now())

Deux stratégies disponibles :

C'est probablement le meilleur compromis effort/résultat pour mettre en place du SCD Type 2 propre.

Capter les changements avec Streams Snowflake

Si tu construis ton SCD Type 2 directement en SQL sans dbt, les Streams Snowflake sont l'outil pour détecter automatiquement les changements en source.

-- Création du stream sur la table source
CREATE OR REPLACE STREAM customers_stream ON TABLE customers;

Tout dans une transaction pour l'atomicité (et pour pouvoir lire le stream plusieurs fois sans qu'il ne se consomme entre les statements) :

BEGIN;

-- 1. Expirer les anciennes versions des clients réellement modifiés
UPDATE customers_dim AS target
SET valid_to = CURRENT_DATE() - 1,
    is_current = FALSE
FROM customers_stream AS source
WHERE target.customer_id = source.customer_id
  AND target.is_current = TRUE
  AND source.METADATA$ACTION = 'INSERT'
  AND source.METADATA$ISUPDATE = TRUE
  AND (
      target.city    IS DISTINCT FROM source.city
      OR target.segment IS DISTINCT FROM source.segment
  );

-- 2. Insérer les nouvelles versions
INSERT INTO customers_dim (customer_id, name, city, segment, valid_from, valid_to, is_current)
SELECT customer_id, name, city, segment, CURRENT_DATE(), NULL, TRUE
FROM customers_stream
WHERE METADATA$ACTION = 'INSERT';

COMMIT;

Quelques points à noter :

• Dans Snowflake, l'offset du stream avance uniquement au COMMIT de la transaction, pas à chaque DML. Tu peux donc lire le stream plusieurs fois entre BEGIN et COMMIT sans risque qu'il se vide entre deux statements.
• Le filtre METADATA$ISUPDATE = TRUE dans l'UPDATE permet de cibler uniquement les modifications de lignes existantes (pas les nouveaux clients qui apparaissent comme INSERT pur).
• La comparaison IS DISTINCT FROM évite de créer une nouvelle version SCD2 si l'UPDATE source ne change pas réellement les colonnes que tu historises.

Combiné à une Task Snowflake avec WHEN SYSTEM$STREAM_HAS_DATA('customers_stream'), tu obtiens un pipeline qui évite la plupart des exécutions inutiles quand le stream est vide.

Aller plus loin : pratiquer la modélisation

Les SCD, c'est probablement la brique la plus testée en entretien analytics engineer et data engineer. Pas juste à comprendre, mais à savoir mettre en place rapidement et à savoir choisir le bon Type selon le contexte métier.

Sur DataCertification.fr, le module Analytics Engineer Lab couvre les patterns SCD avec un éditeur SQL intégré et une validation automatique. Les certifications SnowPro Core (domaine Transformations) et dbt Analytics Engineering (snapshots) testent toutes les deux les SCD régulièrement.

👉 Pratiquer ces patterns sur DataCertification.fr

Pour comprendre où ces dimensions s'insèrent dans une stack data complète, voir Architecture Médaillon Bronze/Silver/Gold. Les dimensions SCD vivent typiquement en couche Gold.

Pour aller plus loin sur Snowflake en général, j'ai regroupé tous mes articles dans un parcours complet.

👉 Accéder à la Formation Snowflake

Tu veux que je t'accompagne sur un projet data (modélisation dimensionnelle, SCD, dbt, performance Snowflake) ?

👉 Réserver un appel de 30 minutes

Questions