Implémenter un type de dimension à variation lente (SCD) 2

Après avoir sélectionné SCD Type 2 pour une dimension, vous devez implémenter la structure de table et modifier la logique de capture dans Azure Databricks. Cette unité se concentre sur la création de tables SCD Type 2 et l’implémentation des modèles MERGE qui conservent l’historique des versions en tant que modifications de données sources.

Créer la table SCD Type 2

Le code SQL suivant crée une table de dimension client avec des colonnes de suivi SCD Type 2 :

CREATE TABLE sales.customer (
    customer_sk BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id STRING NOT NULL,
    customer_name STRING,
    email STRING,
    city STRING,
    region STRING,
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP NOT NULL,
    is_current BOOLEAN
)
USING DELTA
TBLPROPERTIES (
    delta.enableChangeDataFeed = true
);

La GENERATED ALWAYS AS IDENTITY clause crée une clé de substitution incrémentante automatiquement pour chaque nouvelle ligne. L’activation du flux de données modifiées permet aux processus en aval de capturer efficacement les modifications incrémentielles.

Implémenter la capture des modifications avec MERGE

L’instruction MERGE fournit un moyen efficace d’implémenter la logique SCD Type 2 lors du traitement des mises à jour à partir de systèmes sources. L’instruction gère les insertions, les mises à jour et la logique de contrôle de version dans une seule transaction.

Le modèle suivant ferme la version actuelle et insère la nouvelle version lorsque des modifications se produisent :

MERGE INTO sales.customer AS target
USING (
    SELECT 
        source.customer_id,
        source.customer_name,
        source.email,
        source.city,
        source.region,
        current_timestamp() AS valid_from,
        CAST('9999-12-31' AS TIMESTAMP) AS valid_to,
        true AS is_current
    FROM staging.customers AS source
) AS updates
ON target.customer_id = updates.customer_id AND target.is_current = true
WHEN MATCHED AND (
    target.customer_name <> updates.customer_name OR
    target.email <> updates.email OR
    target.city <> updates.city OR
    target.region <> updates.region
) THEN UPDATE SET
    target.valid_to = current_timestamp(),
    target.is_current = false
WHEN NOT MATCHED THEN INSERT (
    customer_id, customer_name, email, city, region,
    valid_from, valid_to, is_current
) VALUES (
    updates.customer_id, updates.customer_name, updates.email, 
    updates.city, updates.region, updates.valid_from, 
    updates.valid_to, updates.is_current
);

-- Insert new versions for updated records
INSERT INTO sales.customer
SELECT 
    s.customer_id,
    s.customer_name,
    s.email,
    s.city,
    s.region,
    current_timestamp() AS valid_from,
    CAST('9999-12-31' AS TIMESTAMP) AS valid_to,
    true AS is_current
FROM staging.customers s
JOIN sales.customer h 
    ON s.customer_id = h.customer_id 
    AND h.valid_to = current_timestamp()
    AND h.is_current = false;

Interroger des données historiques

Une fois que vous avez implémenté une table SCD Type 2, vous pouvez interroger les données comme elles existaient à tout moment. Le modèle de requête dépend de vos besoins analytiques.

Requêtes à un instant T

Pour rechercher l’état des données à un moment spécifique, filtrez la période de validité :

SELECT customer_id, customer_name, city
FROM sales.customer
WHERE valid_from <= '2023-06-15 12:00:00'
  AND valid_to > '2023-06-15 12:00:00';

Cette requête retourne exactement une ligne par client , la version qui était valide au timestamp spécifié.

Historique des performances

Pour afficher l’historique complet des modifications pour une entité spécifique :

SELECT customer_name, city, valid_from, valid_to
FROM sales.customer
WHERE customer_id = 'C-555'
ORDER BY valid_from;

Cette requête révèle toutes les versions du client C-555, montrant comment leurs attributs ont changé au fil du temps.

Utiliser la fonctionnalité "Time Travel" de Delta Lake

Delta Lake fournit des fonctionnalités de voyage temporel intégrées qui complètent les conceptions explicites de table SCD Type 2. Vous pouvez interroger les versions précédentes de table à l’aide de la syntaxe TIMESTAMP AS OF ou VERSION AS OF.

-- Query table state from 7 days ago
SELECT * FROM sales.customer 
TIMESTAMP AS OF '2024-01-15';

-- Query a specific table version
SELECT * FROM sales.customer 
VERSION AS OF 42;