Implementierung einer langsam wechselnden Dimension (SCD) Typ 2

Nachdem Sie SCD Typ 2 für eine Dimension ausgewählt haben, müssen Sie die Tabellenstruktur implementieren und die Erfassungslogik in Azure Databricks ändern. Diese Lektion konzentriert sich auf das Erstellen von SCD-Typ 2-Tabellen und die Implementierung der MERGE-Muster, die den Versionsverlauf bei Änderungen der Quelldaten beibehalten.

Erstellen Sie eine "SCD-Typ-2"-Tabelle.

Die folgende SQL-Anweisung erstellt eine Kundendimensionstabelle mit SCD-Typ-2-Verfolgungsspalten.

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

Die GENERATED ALWAYS AS IDENTITY Klausel erstellt einen automatisch inkrementierenden Ersatzschlüssel für jede neue Zeile. Durch aktivieren des Änderungsdatenfeeds können nachgelagerte Prozesse inkrementelle Änderungen effizient erfassen.

Implementieren der Änderungserfassung mit MERGE

Die MERGE Anweisung bietet eine effiziente Möglichkeit, SCD Type 2-Logik beim Verarbeiten von Updates von Quellsystemen zu implementieren. Die Anweisung behandelt Einfügungen, Aktualisierungen und die Versionsverwaltungslogik in einer einzelnen Transaktion.

Das folgende Muster schließt die aktuelle Version und fügt die neue Version ein, wenn Änderungen vorgenommen werden:

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;

Abfragen von historischen Daten

Nachdem Sie eine SCD-Typ 2-Tabelle implementiert haben, können Sie Jederzeit Daten abfragen, wie sie vorhanden sind. Das Abfragemuster hängt von Ihren analytischen Anforderungen ab.

Punkt-in-Time-Abfragen

Um den Status der Daten zu einem bestimmten Zeitpunkt zu finden, filtern Sie nach dem Gültigkeitszeitraum:

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

Diese Abfrage gibt genau eine Zeile pro Kunde zurück – die Version, die zum angegebenen Zeitstempel gültig war.

Aufzeichnungsverlauf nachverfolgen

So zeigen Sie den vollständigen Verlauf der Änderungen für eine bestimmte Entität an:

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

Diese Abfrage zeigt alle Versionen von Kunden C-555 an, die zeigen, wie sich ihre Attribute im Laufe der Zeit geändert haben.

Verwenden der Zeitreise am Delta Lake

Delta Lake bietet integrierte Zeitreisefunktionen, die explizite SCD Type 2-Tabellendesigns ergänzen. Sie können frühere Tabellenversionen mit der TIMESTAMP AS OF- oder VERSION AS OF-Syntax abfragen.

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