Implementierung eines dimensionalen Data Warehouse mit Databricks SQL: Teil 2

Da Organisationen Analyse-Workloads auf Databricks konsolidieren, müssen sie oft traditionelle Data-Warehouse-Techniken anpassen. Diese Serie untersucht, wie dimensionales Modellieren – insbesondere Sternschemata – auf Databricks implementiert wird. Der erste Blog konzentrierte sich auf das Schema-Design. Dieser Blog behandelt ETL-Pipelines für Dimensionstabellen, einschließlich Slowly Changing Dimensions (SCD) Typ-1 und Typ-2 Muster. Teil 3 zeigt Ihnen wie Sie ETL-Pipelines für Faktentabellen erstellen.

Slowly Changing Dimensions (SCD)

Im letzten Blog haben wir unser Sternschema definiert, einschließlich einer Faktentabelle und ihrer zugehörigen Dimensionen. Wir haben insbesondere eine Dimensionstabelle, DimCustomer, hervorgehoben, wie hier gezeigt (mit einigen Attributen zur Platzersparnis entfernt):

Die letzten drei Felder in dieser Tabelle, i.e., StartDate, EndDate und IsLateArriving, stellen Metadaten dar, die uns bei der Versionierung von Datensätzen helfen. Wenn sich das Einkommen, der Familienstand, der Wohneigentumsstatus, die Anzahl der Kinder zu Hause oder andere Merkmale eines bestimmten Kunden ändern, möchten wir neue Datensätze für diesen Kunden erstellen, damit Fakten wie unsere Online-Verkaufstransaktionen in FactInternetSales mit der richtigen Darstellung dieses Kunden verknüpft werden. Der natürliche (auch Business-)Schlüssel, CustomerAlternateKey, wird über diese Datensätze hinweg gleich sein, aber die Metadaten werden unterschiedlich sein, sodass wir wissen, für welchen Zeitraum diese Version des Kunden gültig war, ebenso wie der Surrogatschlüssel, CustomerKey, der es unseren Fakten ermöglicht, sich mit der richtigen Version zu verknüpfen.

HINWEIS: Da der Surrogatschlüssel häufig zum Verknüpfen von Fakten und Dimensionen verwendet wird, werden Dimensionstabellen oft nach diesem Schlüssel gruppiert. Im Gegensatz zu herkömmlichen relationalen Datenbanken, die B-Tree-Indizes auf sortierten Datensätzen verwenden, implementiert Databricks eine einzigartige Gruppierungsmethode namens Liquid Clustering. Während die Einzelheiten von Liquid Clustering außerhalb des Rahmens dieses Blogs liegen, verwenden wir durchweg die Klausel CLUSTER BY für den Surrogatschlüssel unserer Dimensionstabellen während ihrer Definition, um diese Funktion effektiv zu nutzen.

Dieses Muster der Versionierung von Dimensionsdatensätzen bei Attributänderungen wird als Type-2 Slowly Changing Dimension (oder einfach Type-2 SCD) Muster bezeichnet. Das Type-2 SCD-Muster wird in der klassischen dimensionalen Methodik zur Aufzeichnung von Dimensionsdaten bevorzugt. Es gibt jedoch andere Möglichkeiten, mit Änderungen in Dimensionsdatensätzen umzugehen.

Eine der häufigsten Methoden, um mit sich ändernden Dimensionswerten umzugehen, ist die Aktualisierung vorhandener Datensätze an Ort und Stelle. Es wird immer nur eine Version des Datensatzes erstellt, sodass der Business-Schlüssel die eindeutige Kennung für den Datensatz bleibt. Aus verschiedenen Gründen, nicht zuletzt wegen Leistung und Konsistenz, implementieren wir immer noch einen Surrogatschlüssel und verknüpfen unsere Faktendatensätze mit diesen Dimensionen über diese Schlüssel. Dennoch sind die Metadatenfelder StartDate und EndDate, die die Zeitintervalle beschreiben, über die ein bestimmter Dimensionsdatensatz als aktiv gilt, nicht erforderlich. Dies wird als Type-1 SCD Muster bezeichnet. Die Promotion-Dimension in unserem Sternschema bietet ein gutes Beispiel für eine Type-1-Dimensionstabellenimplementierung:

Aber was ist mit dem Metadatenfeld IsLateArriving, das in der Type-2-Kunden-Dimension, aber nicht in der Type-1-Promotion-Dimension zu sehen ist? Dieses Feld wird verwendet, um Datensätze als „late arriving“ zu kennzeichnen. Ein late arriving record ist ein Datensatz, dessen Business-Schlüssel während eines Fakt-ETL-Zyklus auftaucht, aber bei der vorherigen Dimensionsverarbeitung kein Datensatz für diesen Schlüssel gefunden wurde. Im Fall von Type-2 SCDs wird dieses Feld verwendet, um anzuzeigen, dass bei der ersten Beobachtung der Daten für einen „late arriving record“ in einem Dimensions-ETL-Zyklus der Datensatz an Ort und Stelle aktualisiert werden sollte (genau wie bei einem Type-1 SCD-Muster) und von diesem Zeitpunkt an versioniert werden sollte. Im Fall von Type-1 SCDs ist dieses Feld nicht notwendig, da der Datensatz unabhängig davon an Ort und Stelle aktualisiert wird.

HINWEIS: Die Kimball Group erkennt zusätzliche SCD-Muster an, von denen die meisten Variationen und Kombinationen der Muster Typ 1 und Typ 2 sind. Da die SCDs Typ 1 und Typ 2 die am häufigsten implementierten dieser Muster sind und die Techniken, die mit den anderen verwendet werden, eng mit dem verwandt sind, was bei diesen angewendet wird, beschränken wir diesen Blog auf diese beiden Dimensionstypen. Weitere Informationen zu den acht von der Kimball Group anerkannten SCD-Typen finden Sie im Abschnitt Slowly Changing Dimension Techniques von diesem Dokument.

Implementierung des SCD-Musters Typ 1

Da die Daten direkt aktualisiert werden, ist das SCD-Workflow-Muster Typ 1 das einfachste der beiden dimensionalen ETL-Muster. Um diese Arten von Dimensionen zu unterstützen, tun wir einfach Folgendes:

1. Extrahieren der erforderlichen Daten aus unseren operativen Systemen
2. Durchführen aller erforderlichen Datenbereinigungsoperationen
3. Vergleichen unserer eingehenden Datensätze mit denen, die sich bereits in der Dimensionstabelle befinden
4. Aktualisieren aller vorhandenen Datensätze, bei denen eingehende Attribute von dem abweichen, was bereits erfasst wurde
5. Einfügen aller eingehenden Datensätze, die keinen entsprechenden Datensatz in der Dimensionstabelle haben

Um eine SCD-Implementierung vom Typ 1 zu veranschaulichen, definieren wir die ETL für die fortlaufende Befüllung der DimPromotion-Tabelle.

Schritt 1: Extrahieren von Daten aus einem operativen System

Unser erster Schritt ist die Extraktion der Daten aus unserem operativen System. Da unser Data Warehouse nach der Beispiel-Datenbank AdventureWorksDW von Microsoft modelliert ist, verwenden wir die eng damit verbundene AdventureWorks (OLTP) Beispieldatenbank als Quelle. Diese Datenbank wurde auf einer Azure SQL Database-Instanz bereitgestellt und über eine federierte Abfrage in unserer Databricks-Umgebung zugänglich gemacht. Die Extraktion wird dann mit einer einfachen Abfrage (einige Felder zur Platzersparnis geschwärzt) durchgeführt, wobei die Abfrageergebnisse in einer Tabelle in unserem staging-Schema gespeichert werden (das nur den Data Engineers in unserer Umgebung über hier nicht gezeigte Berechtigungseinstellungen zugänglich ist). Dies ist nur eine von vielen Möglichkeiten, wie wir in dieser Umgebung auf Quelldaten zugreifen können:

Schritt 2: Vergleichen mit den Datensätzen in der Tabelle

Unter der Annahme, dass wir keine zusätzlichen Datenbereinigungsschritte durchführen müssen (die wir mit einer UPDATE oder einer anderen CREATE TABLE AS-Anweisung implementieren könnten), können wir dann unsere Datenaktualisierungs-/einfügeoperationen für Dimensionen in einem einzigen Schritt mit einer MERGE-Anweisung durchführen, wobei wir unsere gestagten Daten und Dimensionsdaten anhand des Geschäftsschlüssels abgleichen:

Eine wichtige Anmerkung zu der hier geschriebenen Anweisung ist, dass wir alle vorhandenen Datensätze aktualisieren, wenn eine Übereinstimmung zwischen den gestagten und den veröffentlichten Dimensionsplandaten gefunden wird. Wir könnten zusätzliche Kriterien zur WHEN MATCHED-Klausel hinzufügen, um Aktualisierungen auf Fälle zu beschränken, in denen ein Datensatz im Staging andere Informationen enthält als in der Dimensionstabelle, aber angesichts der relativ geringen Anzahl von Datensätzen in dieser speziellen Tabelle haben wir uns für die relativ schlankere hier gezeigte Logik entschieden. (Wir werden die zusätzliche WHEN MATCHED-Logik mit DimCustomer, die weitaus mehr Daten enthält, verwenden.)

Das SCD-Muster Typ 2

Das SCD-Muster Typ 2 ist etwas komplexer. Um diese Arten von Dimensionen zu unterstützen, müssen wir:

1. Extrahieren der erforderlichen Daten aus unseren operativen Systemen
2. Durchführen aller erforderlichen Datenbereinigungsoperationen
3. Aktualisieren aller spät ankommenden Mitgliedsdatensätze in der Zieltabelle
4. Ablaufenlassen aller vorhandenen Datensätze in der Zieltabelle, für die neue Versionen im Staging gefunden werden
5. Einfügen aller neuen Datensätze (oder neuer Versionen von Datensätzen) in die Zieltabelle

Schritt 1: Extrahieren und Bereinigen von Daten aus einem Quellsystem

Wie beim SCD-Muster Typ 1 sind unsere ersten Schritte die Extraktion und Bereinigung von Daten aus dem Quellsystem. Mit dem gleichen Ansatz wie oben geben wir eine federierte Abfrage aus und speichern die extrahierten Daten in einer Tabelle in unserem staging-Schema:

Schritt 2: Vergleich mit einer Dimensionstabelle

Nachdem diese Daten gelandet sind, können wir sie nun mit unserer Dimensionstabelle vergleichen, um erforderliche Datenänderungen vorzunehmen.  Die erste dieser Änderungen besteht darin, alle Datensätze, die als spät ankommend aus früheren ETL-Prozessen der Faktentabelle gekennzeichnet sind, direkt zu aktualisieren.  Bitte beachten Sie, dass diese Aktualisierungen auf die als spät ankommend gekennzeichneten Datensätze beschränkt sind und das IsLateArriving Flag wird mit der Aktualisierung zurückgesetzt, damit sich diese Datensätze zukünftig wie normale Typ-2-SCDs verhalten:

Schritt 3: Versionierte Datensätze ablaufen lassen

Der nächste Satz von Datenänderungen besteht darin, alle Datensätze ablaufen zu lassen, die versioniert werden müssen.  Es ist wichtig, dass der EndDate Wert, den wir für diese festlegen, mit dem StartDate der neuen Datensatzversionen übereinstimmt, die wir im nächsten Schritt implementieren werden.  Aus diesem Grund legen wir eine Variable für Zeitstempel fest, die zwischen diesen beiden Schritten verwendet wird:

HINWEIS: Abhängig von den Ihnen zur Verfügung stehenden Daten können Sie einen EndDate Wert aus dem Quellsystem verwenden, anstatt notwendigerweise eine Variable wie hier gezeigt zu deklarieren.

Bitte beachten Sie die zusätzlichen Kriterien in der WHEN MATCHED-Klausel.  Da wir mit dieser Anweisung nur eine Operation durchführen, wäre es möglich, diese Logik in die ON-Klausel zu verschieben, aber wir haben sie zur besseren Übersichtlichkeit und Wartbarkeit von der Kernabgleichlogik getrennt, bei der wir mit der aktuellen Version des Dimensionsdatensatzes abgleichen.

Als Teil dieser Logik nutzen wir intensiv die equal_null() Funktion.  Diese Funktion gibt WAHR zurück, wenn die ersten und zweiten Werte gleich oder beide NULL sind; andernfalls gibt sie FALSCH zurück.  Dies bietet eine effiziente Möglichkeit, Änderungen spaltenweise zu suchen.  Weitere Details zur Unterstützung von NULL-Semantik durch Databricks finden Sie in diesem Dokument.

Zu diesem Zeitpunkt wurden alle vorherigen Versionen von Datensätzen in der Dimensionstabelle, die abgelaufen sind, mit einem Enddatum versehen.  

Schritt 4: Neue Datensätze einfügen

Wir können nun neue Datensätze einfügen, sowohl wirklich neue als auch neu versionierte:

Wie zuvor hätte dies mit einer INSERT-Anweisung implementiert werden können, aber das Ergebnis ist dasselbe.  Mit dieser Anweisung haben wir alle Datensätze in der Staging-Tabelle identifiziert, die keinen abgelaufenen entsprechenden Datensatz in den Dimensionstabellen haben. Diese Datensätze werden einfach mit einem StartDate Wert eingefügt, der mit allen abgelaufenen Datensätzen übereinstimmt, die möglicherweise in dieser Tabelle vorhanden sind.

Nächste Schritte: Implementierung der Faktentabellen-ETL

Nachdem die Dimensionen implementiert und mit Daten gefüllt wurden, können wir uns nun auf die Faktentabellen konzentrieren. In Teil 3 werden wir zeigen, wie die ETL für diese Tabellen implementiert werden kann.

Um mehr über Databricks SQL zu erfahren, besuchen Sie unsere Website oder lesen Sie die Dokumentation. Sie können auch die Produkttour für Databricks SQL ansehen. Angenommen, Sie möchten Ihr bestehendes Warehouse in ein Hochleistungs-Data-Warehouse ohne Server mit großartiger Benutzererfahrung und niedrigeren Gesamtkosten migrieren. In diesem Fall ist Databricks SQL die Lösung — probieren Sie es kostenlos aus.

(Dieser Blogbeitrag wurde mit KI-gestützten Tools übersetzt.) Originalbeitrag