Een langzaam veranderende dimensie (SCD) type 2 implementeren

Nadat u SCD Type 2 voor een dimensie hebt geselecteerd, moet u de tabelstructuur en de logica voor het vastleggen van wijzigingen in Azure Databricks implementeren. Deze les is gericht op het maken van SCD Type 2-tabellen en het implementeren van de MERGE-patronen die de versiegeschiedenis behouden wanneer brongegevens worden gewijzigd.

De SCD Type 2-tabel maken

Met de volgende SQL maakt u een klantdimensietabel met SCD Type 2-traceringskolommen:

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

Met GENERATED ALWAYS AS IDENTITY wordt voor elke nieuwe rij een automatisch oplopende surrogaatsleutel aangemaakt. Door de wijzigingengegevensfeed in te schakelen, kunnen downstreamprocessen incrementele wijzigingen efficiënt vastleggen.

Wijzigingsopname implementeren met MERGE

De MERGE instructie biedt een efficiënte manier om SCD Type 2-logica te implementeren bij het verwerken van updates van bronsystemen. De instructie verwerkt invoegingen, updates en de versiebeheerlogica in één transactie.

Met het volgende patroon wordt de huidige versie gesloten en wordt de nieuwe versie ingevoegd wanneer er wijzigingen optreden:

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;

Query's uitvoeren op historische gegevens

Zodra u een SCD Type 2-tabel hebt geïmplementeerd, kunt u op elk gewenst moment query's uitvoeren op gegevens zoals deze bestaan. Het querypatroon is afhankelijk van uw analytische behoeften.

Query's op een specifiek tijdstip

Als u de status van gegevens op een bepaald moment wilt vinden, filtert u op de geldigheidsperiode:

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

Deze query retourneert precies één rij per klant: de versie die geldig was op de opgegeven tijdstempel.

Geschiedenis van het trackrecord

De volledige geschiedenis van wijzigingen voor een specifieke entiteit weergeven:

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

Deze query toont alle versies van klant C-555, waarin wordt getoond hoe hun kenmerken in de loop van de tijd zijn gewijzigd.

Delta Lake-tijdreizen gebruiken

Delta Lake biedt ingebouwde mogelijkheden voor tijdreizen die een aanvulling vormen op expliciete SCD Type 2-tabelontwerpen. U kunt een query uitvoeren op eerdere tabelversies met behulp van de TIMESTAMP AS OF of VERSION AS OF syntaxis:

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