Implementera en långsamt föränderlig dimension (SCD) typ 2

När du har valt SCD Typ 2 för en dimension måste du implementera tabellstrukturen och logiken för ändringsinsamling i Azure Databricks. Den här lektionen fokuserar på att skapa SCD Type 2-tabeller och implementera MERGE-mönstren som underhåller versionshistoriken när källdata ändras.

Skapa SCD Type 2-tabellen

Följande SQL skapar en kunddimensionstabell med SCD Typ 2-spårningskolumner:

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
);

Satsen GENERATED ALWAYS AS IDENTITY skapar en automatisk inkrementell surrogatnyckel för varje ny rad. Genom att aktivera ändringsdataflödet kan underordnade processer samla in inkrementella ändringar effektivt.

Implementera ändringsinsamling med MERGE

Instruktionen MERGE är ett effektivt sätt att implementera SCD Typ 2-logik vid bearbetning av uppdateringar från källsystem. Instruktionen hanterar infogningar, uppdateringar och versionslogik i en enda transaktion.

Följande mönster stänger den aktuella versionen och infogar den nya versionen när ändringar sker:

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;

Fråga historiska data

När du har implementerat en SCD Type 2-tabell kan du fråga efter data när som helst. Frågemönstret beror på dina analytiska behov.

Punkt-i-tid-frågeställningar

Om du vill hitta datatillståndet vid ett visst tillfälle filtrerar du efter giltighetsperioden:

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';

Den här frågan returnerar exakt en rad per kund – den version som var giltig vid den angivna tidsstämpeln.

Resultathistorik

Så här visar du den fullständiga historiken för ändringar för en viss entitet:

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

Den här frågan visar alla versioner av kund C-555 och hur dess attribut har ändrats över tid.

Använd Delta Lake-tidsresor

Delta Lake tillhandahåller inbyggda tidsresefunktioner som kompletterar explicita SCD Type 2-tabelldesigner. Du kan köra frågor mot tidigare tabellversioner med hjälp av syntaxen TIMESTAMP AS OF eller 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;