Ein praktischer Leitfaden zur Erstellung von Slowly Changing Dimensions Typ 2 in dbt | Teil 1

Viele Unternehmen verwenden das Kimball-Rahmenwerk zur Dimensionsmodellierung, um ihre Daten zu modellieren, und denken dabei zweifellos an Slowly Changing Dimensions Type 2 (SCD2)-Modelle oder verwenden sie bereits. Dies ist einer der gängigsten Ansätze zur Modellierung historischer Daten für analytische Zwecke. Sie ist so weit verbreitet, dass das heute populärste Analyse-Engineering-Tool dbt (data build tool) seit 2016 (!) die Möglichkeit bietet, SCD2-Modelle zu erstellen. Wie immer gibt es jedoch viele verschiedene Möglichkeiten, um Slowly Changing Dimensions Type 2 in dbt zu erstellen. Darüber hinaus bestimmt die Art und Weise, wie historische Daten in Ihr Data Warehouse aufgenommen und gespeichert werden, zu einem großen Teil, wie Sie SCD2-Modelle innerhalb von dbt implementieren können.

Dieser praktische 2-teilige Leitfaden erläutert das Konzept der Slowly Changing Dimensions, die Speicherung historischer Daten unter Verwendung verschiedener Muster und die verschiedenen Möglichkeiten der Erstellung von SCD2-Modellen in dbt.

In Teil 1 gehen wir darauf ein, was Slowly Changing Dimensions sind und vor allem, wie Sie Daten in Ihrer Datenplattform extrahieren und speichern, um sie in SCD2-Modelle zu konvertieren. In Teil 2 werden wir uns mit dbt-Snapshots befassen und wie Sie selbst benutzerdefinierte SCD2-Modelle erstellen, die flexibler und skalierbarer sind als die native Lösung von dbt.

Kimball & Langsam wechselnde Dimensionen

Bevor wir uns mit anderen Themen befassen, lassen Sie uns kurz auf die Dimensionsmodellierung von Kimball und die Notwendigkeit von Slowly Changing Dimensions eingehen.

Dimensionale Modellierung

Eine der beliebtesten Datenmodellierungstechniken, die immer noch von vielen Unternehmen verwendet wird, wurde 1996 von Kimball eingeführt. In seinem Buch stellte Kimball die Konzepte von Fakten und Dimensionen sowie einen Prozess vor, mit dem die Anforderungen für die Erstellung dieser Fakten und Dimensionen ermittelt werden können. Die Idee dahinter ist, dass Sie mit dieser Technik alles auf konsistente Weise modellieren, was die Anpassung von Datenmodellen und die Nutzung der Daten durch Analysten und BI-Tools erleichtert.

Fakten

Der Zweck von Faktentabellen ist "das Repository der numerischen Fakten, die während des Messvorgangs beobachtet werden" (Link). Mit anderen Worten: Für jeden Geschäftsprozess, den Sie messen möchten, sollten Sie alle numerischen Messwerte in einer Faktentabelle speichern. Faktentabellen sind in der Regel sehr eng gefasst (begrenzte Anzahl von Spalten), enthalten aber eine Vielzahl von Datensätzen. Diese Tabellen können dann zur Berechnung von Metriken wie Umsatz, verkaufte Menge usw. verwendet werden.

transaktion_id	kunden_id	transaktion_datum	umsatz	Menge
22	123	2024-01-01	19,99	2

Beispiel einer Faktentabelle für eine Auftragszeile mit 3 Fremdschlüsseln und 2 Fakten (Umsatz und Menge).

Abmessungen

Um ein wertvolles Data Warehouse zu erstellen, reicht es nicht aus, nur Faktentabellen zu haben. Neben der Erstellung von Faktentabellen müssen Sie auch Dimensionen erstellen. Dimensionen "liefern den Wer-, Was-, Wo-, Wann-, Warum- und Wie-Kontext rund um ein Geschäftsprozessereignis"(Link). Mit anderen Worten: Alles, was eine Messung in einem Fakt beschreibt, landet normalerweise in einer Dimension. Sie können diese Dimensionen mithilfe von Schlüsseln mit Fakten verknüpfen, um den Fakten mehr Kontext hinzuzufügen. So können Sie beispielsweise eine Kundendimension mit einem Transaktionsfaktum verknüpfen, um die Anzahl der Transaktionen von Kunden aus einem bestimmten Land zu analysieren.

Dimensionen sind in der Regel sehr umfangreich (viele verschiedene Spalten), enthalten aber nicht viele Datensätze. In ihrer einfachsten Form zeigt eine Dimension den aktuellen Zustand der Daten. Was trifft zum Beispiel im Moment auf einen Kunden zu?

kunde_id	kunde_name	Kunde_Adresse	kunde_email	kunde_erstellt_am
123	Adam Johnson	Hauptstraße 1, London	a.johnson@email.com	2024-01-01T14:00

Beispiel für eine Kunden-Dimensionstabelle mit einer Reihe von Attributen.

Die Notwendigkeit eines langsamen Dimensionswechsels

Während viele geschäftliche Fragen beantwortet werden können, indem man nur grundlegende Fakten und Dimensionen hat und diese miteinander verbindet, reicht dies nicht aus, um geschäftliche Fragen zu beantworten, die sich auf Veränderungen der "Wahrheit" im Laufe der Zeit beziehen. Grundlegende Fakten und Dimensionen helfen bei der Beantwortung von Fragen wie der folgenden:

• Wie viele Kunden haben wir jetzt?
• Wie viel Umsatz haben wir letztes Jahr gemacht?
• Welche Produktkategorie hat im letzten Monat den meisten Umsatz eingebracht?

Es wird jedoch nicht in der Lage sein, Fragen wie diese zu beantworten:

• Welche Produkte haben im letzten Jahr ihre Inhaltsstoffe geändert?
• Welche Kunden sind umgezogen?
• Wie oft ändern Kunden ihre E-Mail Adresse?

Um diese Fragen beantworten zu können, benötigen Sie nicht nur die Wahrheit über Kunden, Produkte usw. in diesem Moment, sondern auch die Wahrheit über Kunden oder Produkte zu einem beliebigen Zeitpunkt. Zum Glück für uns hat Kimball das Konzept der sich langsam verändernden Dimensionen eingeführt, um dieser Anforderung gerecht zu werden. Im weiteren Verlauf dieses Leitfadens werden wir die Wahrheit als einen Zustand bezeichnen. Ein Kunde kann also viele historische Zustände haben, wenn sich die Informationen über diesen Kunden ändern.

Verschiedene Möglichkeiten zur Modellierung historischer Datenzustände (SCD-Typen)

Kimball beschreibt 8 Arten von sich langsam verändernden Dimensionen, die auf die Erfassung von Veränderungen für verschiedene Anwendungsfälle zugeschnitten sind. Da der Zweck dieses Leitfadens nicht darin besteht, die gesamte Kimball-Methodik im Detail zu erläutern, beschränken wir uns auf die Typen, die relevant sind und am häufigsten verwendet werden. Da Typ 1 und 2 die beliebtesten und relevantesten Typen sind, werden wir nur diese 2 behandeln. Andere Typen sind in der Anzahl der speicherbaren Zustände begrenzt oder sind Variationen von Typ 2.

Zur Veranschaulichung der verschiedenen Arten von Slowly Changing Dimensions nehmen wir im weiteren Verlauf dieses Leitfadens die Quelltabelle eines niederländischen Stroopwaffel-Ladens mit nur 1 Kunden als Beispiel. Es handelt sich um eine sehr einfache Quelltabelle mit einer ID, einem Namen und einer Adresse eines Kunden, die auch Metadaten-Spalten enthält, die beschreiben, wann ein Datensatz erstellt und/oder aktualisiert wurde.

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	aktualisiert_am
123	Adam Johnson	Hauptstraße 1, London	2024-01-01T14:00	2024-01-02T14:00

Kunden-Quelltabelle

Typ 1

Dies ist der 'Standard'-Typ einer Dimension. Sie enthält keine Historie, sondern speichert nur den aktuellen Zustand einer Dimension. Wenn sich z.B. ein Kunde ändert, wird der zugehörige Kundendatensatz einfach überschrieben.

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	aktualisiert_am
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00

Beispiel für einen Kunden Langsam ändernde Dimension Typ 1 mit einer Reihe von Attributen. Sie enthält nur die aktuelle Wahrheit und 1 Datensatz = 1 Kunde. Beachten Sie die aktualisierten Werte für Adresse und updated_at.

Typ 2

In einer sich langsam ändernden Dimension Typ 2 wird jedes Mal, wenn ein Kunde hinzugefügt oder aktualisiert wird, ein neuer Datensatz eingefügt. Um nachzuvollziehen, welcher Zustand zu welchem Zeitpunkt zutreffend war, profitiert dieser Typ von drei Spalten: valid_from, valid_to und is_current_record. Wenn sich beispielsweise die Adresse eines Kunden ändert, wird ein neuer Datensatz mit denselben Informationen und einer aktualisierten Kundenadresse eingefügt, für den der Gültigkeitszeitraum ermittelt und in diesen drei Spalten verfügbar gemacht wird.

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	gültig_von	gültig_bis	is_valid_record
123	Adam Johnson	Hauptstraße 1, London	2024-01-01T14:00	2024-01-01T14:00	2024-01-01T14:59	false
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	2024-01-05T15:59	false
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-05T16:00	9999-12-31:23:59	true

Beispiel für einen Kunden, der seine Dimension langsam ändert Typ 2, bei dem ein Kunde zweimal seine Adresse ändert.

Langsam wechselnde Dimensionen und gelöschte Datensätze

Wenn Sie mit historischen Daten arbeiten, werden Sie fast immer auf Situationen stoßen, in denen Datensätze in den Quelldaten gelöscht werden. Denken Sie an Kunden, die sich abmelden möchten (GDPR), an Produkte, die gelöscht werden usw. Das bedeutet, dass Sie dies bei der Erstellung von SCD2-Tabellen berücksichtigen müssen.

Kimball geht in seinem Buch nur sehr kurz auf das Löschen von Datensätzen ein, aber in der Praxis müssen Unternehmen gelöschte Datensätze in den Quelldaten berücksichtigen und das Löschen von Datensätzen ist eine sehr wichtige Designentscheidung. Es gibt zwei Möglichkeiten, Löschungen zu behandeln.

Weiche Löschungen

Immer dann, wenn ein Datensatz als gelöscht markiert wird, aber physisch in der Datenbank verbleibt. Dies geschieht in der Regel, um sicherzustellen, dass diese Daten nicht von nachgelagerten Systemen verwendet werden, während sie für Prüfungs- und Analysezwecke aufbewahrt werden.

Nehmen wir als Beispiel die gleichen Beispieldaten für einen bestimmten Kunden. Dieser Kunde bittet darum, aus den Systemen des Unternehmens gelöscht zu werden. In einem SCD2-Modell könnte dies wie folgt modelliert werden:

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	gültig_von	gültig_bis	is_valid_record	gelöscht_am
123	Adam Johnson	Hauptstraße 1, London	2024-01-01T14:00	2024-01-01T14:00	2024-01-01T14:59	false	2024-01-06T12:00
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	2024-01-05T15:59	false	2024-01-06T12:00
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-05T16:00	2024-01-6T11:59	false	2024-01-06T12:00
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-06T12:00	9999-12-31:23:59	true	2024-01-06T12:00

Diese Art der Modellierung ermöglicht es Ihnen, den softgelöschten Kunden in seinem historischen Zustand zu belassen, markiert aber jeden Datensatz als gelöscht, einschließlich des Zeitstempels der Löschung.

Hartes Löschen

Wenn ein Datensatz physisch aus einer Datenbank gelöscht wird, sprechen wir von einer harten Löschung. Dies geschieht in der Regel, um den Vorschriften zur Datenaufbewahrung oder der Opt-out-Verordnung (GDPR in Europa) zu entsprechen.

Es ist einfach, dies anhand eines Beispiels zu veranschaulichen, da es keine Daten gibt, die den Moment zeigen, in dem ein Kunde hart aus einer Datenbank gelöscht wird. Wenn Sie SCD2-Modelle erstellen, sollten Ihre Datenoperationen einen Prozess zum harten Löschen von Datensätzen in Tabellen enthalten, die Ihrem Quellsystem nachgelagert sind. Andernfalls würden Sie nur die Daten in der Quelle löschen, nicht aber z.B. in Ihren SCD2-Modellen.

Wie bereitet man Daten für SCD2 vor?

Wenn Sie die Anforderung haben, Slowly Changing Dimensions Type 2-Modelle zu erstellen, gibt es drei relevante und häufig verwendete Muster, um Quelldaten zu extrahieren, Zustände zu speichern und schließlich eine SCD2-Tabelle zu erstellen.

• Das erste Muster ist die Verwendung von Change Data Capture zur Erfassung jeder Datenbanktransaktion.
• Das zweite Muster, vollständige Extrakte, verwendet einen Snapshotting-Prozess, um den vollständigen Status der Quelltabelle regelmäßig zu erfassen und den vollständigen Status an eine Verlaufstabelle anzuhängen.
• Das dritte Muster, Delta-Extraktionen, ist ein Snapshot-Verfahren, das nur geänderte Datensätze in einer Quelltabelle erfasst.

In den folgenden Abschnitten gehen wir auf die verschiedenen Ansätze zum Extrahieren und Speichern Ihrer Quelldaten ein und erläutern, wie diese in SCD2-Modelle übertragen werden können.

Datenerfassung ändern (CDC)

Die detailliertesten SCD2-Modelle sind möglich, wenn Sie CDC-Tabellen verwenden. Mit dieser Art von Quelltabelle haben Sie Zugriff auf die vollständige Historie jedes Datensatzes in einer Tabelle. Das bedeutet, dass Sie genau wissen, wann ein Datensatz erstellt, aktualisiert und/oder aus einer Tabelle gelöscht wurde, einschließlich des Status des Datensatzes bei jedem Schritt.

Was ist Change Data Capture?

Change Data Capture, kurz CDC, ist ein Prozess, bei dem Sie jede Änderung an Daten in der Quelltabelle erfassen. Die Änderungen können dann entweder in Stapeln oder in Echtzeit in eine andere Tabelle auf Ihrer Datenplattform geladen werden. CDC wird von vielen SQL-Datenbanken unterstützt, die genaue Implementierung und der Funktionsumfang sind jedoch von Datenbank zu Datenbank unterschiedlich. In diesem Leitfaden beschränken wir uns auf die Grundlagen.

In einer Tabelle in einer SQL-Datenbank wird jede Änderung eines Datensatzes in einer Tabelle mit einer SQL-Operation durchgeführt. Die gängigsten Operationen sind INSERT, UPDATE und DELETE. Wenn Sie einen Kunden anlegen, fügen Sie einen neuen Datensatz in die Tabelle ein (INSERT), wenn Sie den Datensatz eines Kunden aktualisieren möchten, führen Sie eine UPDATE-Operation durch und wenn Sie einen Kunden entfernen möchten, können Sie eine DELETE-Anweisung ausführen. Die meisten SQL-Datenbanken speichern diese Vorgänge in einem Protokoll, das dann gelesen und verwendet werden kann, um alle Datenänderungen in einer Tabelle zu erfassen.

Wie können Sie eine SCD2-Tabelle mit CDC erstellen?

Nehmen wir die Kundentabelle, die wir bereits in diesem Leitfaden verwendet haben, um zu veranschaulichen, wie CDC mehr Einblick in die Änderungen von Kundendaten gibt. Bei einer direkten Abfrage der Kundentabelle würden Sie normalerweise nur den letzten Stand der Kundentabelle sehen.

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	aktualisiert_am
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-05T16:00

Kundenquellentabelle von Stroopwaffle Shop

Im Hintergrund haben wir jedoch jede Änderung am Datensatz dieses Kunden in dieser Tabelle mit Hilfe der CDC-Funktion der Datenbank erfasst und diese Änderungen in eine neue Tabelle geladen:

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	aktualisiert_am	Betrieb	zeilen_operation_at
123	Adam Johnson	Hauptstraße 1, London	2024-01-01T14:00	2024-01-01T14:00	INSERT	2024-01-01T14:00
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	UPDATE	2024-01-01T15:00
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-05T16:00	UPDATE	2024-01-05T16:00

CDC-Tabelle der Kundenquelltabelle

In der neuen Tabelle können Sie sehen, dass es 3 Tabellenoperationen für diesen speziellen Kunden gegeben hat: 1 INSERT und 2 UPDATEs. Außerdem können Sie sehen, dass die Art der Operation und der Zeitstempel der Datensatzoperation erfasst und zusammen mit den Datensätzen gespeichert wurden. Aus dieser Tabelle geht hervor, dass die Adresse des Kunden am selben Tag, an dem der Datensatz erstellt wurde, aktualisiert und dann 4 Tage später erneut angepasst wurde.

Da Sie nun alle historischen Änderungen zur Verfügung haben, können Sie ein SCD2-Modell erstellen:

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	scd2_valid_from	scd2_valid_until	is_current_record
123	Adam Johnson	Hauptstraße 1, London	2024-01-01T14:00	2024-01-01T14:00	2024-01-01T14:59	false
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	2024-01-05T15:59	false
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-05T16:00	NULL	true

Resultierende SCD2-Kundentabelle unter Verwendung der CDC-Kundentabelle

In der resultierenden SCD2 können Sie sehen, wie die Zeitstempel der Datensatzoperationen in die Spalten valid_from und valid_until übersetzt werden.

Wie arbeitet die CDC mit gelöschten Datensätzen?

Wie bereits erwähnt, besteht eine Tabellenoperation darin, Datensätze zu LÖSCHEN. Dies geschieht zum Beispiel, wenn Kunden sich abmelden möchten, falsche Daten entfernt werden usw. CDC verarbeitet DELETE-Operationen auf die gleiche Weise wie INSERT- und UPDATE-Operationen. Jedes Mal, wenn ein Datensatz in der Quelltabelle gelöscht wird, wird der Vorgang im Protokoll registriert.

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	aktualisiert_am	Betrieb	zeilen_operation_at
123	Adam Johnson	Hauptstraße 1, London	2024-01-01T14:00	2024-01-01T14:00	INSERT	2024-01-01T14:00
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	UPDATE	2024-01-01T15:00
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-05T16:00	UPDATE	2024-01-05T16:00
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-10T12:00	LÖSCHEN	2024-01-10T12:00

CDC-Tabelle mit DELETE-Operation

Sie können nun diesen Datensatz der DELETE-Operation verwenden, um diesen Kunden in der SCD2-Tabelle weich oder hart zu löschen. Mehr über Löschvorgänge erfahren Sie später in diesem Leitfaden.

Wie richten Sie CDC für Ihre Datenbank ein?

Wie bereits erwähnt, unterstützen viele verschiedene Datenbanken CDC, haben aber unterschiedliche Funktionen und Implementierungen. Es würde den Rahmen dieses Leitfadens sprengen, in die Tiefe zu gehen, aber wir können kurz ein paar Optionen erwähnen.

Databricks

Databricks unterstützt CDC von Haus aus, wenn Sie das Delta Lake-Format verwenden. Es heißt Change Data Feed (CDF) und bietet Ihnen alles, was Sie brauchen. Beachten Sie, dass CDF vorausschauend ist, d.h. es beginnt erst mit der Erfassung von Datenänderungen, wenn Sie es für eine Tabelle aktivieren. Um es zu aktivieren, führen Sie aus:

CREATE TABLE customer (  
  customer_id INT,   
  customer_name STRING,   
  customer_address STRING,  
  created_at TIMESTAMP,  
  updated_at TIMESTAMP  
)   
TBLPROPERTIES (delta.enableChangeDataFeed = true)

Google Cloud

Wenn Sie Google Cloud nutzen, können Sie Datastream in Betracht ziehen. Dieser vollständig verwaltete Datenbankreplikationsdienst ermöglicht Ihnen die Replikation von Datenbanken wie Postgres, MySQL usw., indem Sie ein CDC-Muster verwenden und die Daten in Echtzeit in Google Cloud Storage oder Google BigQuery einspeisen.

Debezium

Wenn Sie einen verwalteten Dienst wie Change Data Feed von Databricks oder Datastream von Google Cloud nicht nutzen möchten oder dieser für Ihre Cloud-Plattform einfach nicht verfügbar ist, können Sie auch auf das Open-Source-Tool Debezium zurückgreifen. Debezium kann sich mit Datenbanken wie MySQL, PostgreSQL oder MongoDB verbinden, Änderungen erfassen und diese Änderungen in Echtzeit für andere Anwendungen zur Verfügung stellen.

Schnappschuss-Tabellen

Wenn Sie nicht die Möglichkeit haben, mit CDC alle Datenänderungen zu erfassen, und Ihre Quelldaten Ihnen nur den letzten Stand der Daten liefern, können Sie für Ihre Quelldaten, für die Sie SCD2-Modelle erstellen möchten, einen Snapshot erstellen.

Was ist ein Snapshot?

Ein Snapshot einer Datenquelle bedeutet, dass Sie sich eine Quelltabelle in regelmäßigen Abständen ansehen und den Zustand dieser Quelltabelle zu diesem Zeitpunkt erfassen. Idealerweise speichern Sie dann jeden Snapshot, den Sie machen (in einem Data Lake), so dass Sie jederzeit eine Verlaufstabelle anhand dieser Snapshots von Grund auf neu erstellen können. In einer analytischen Datenbank können Sie all diese Snapshots offenlegen, indem Sie einfach alle Snapshots aneinander anhängen, was zu einer großen historischen Snapshot-Tabelle führt. Auf diese Weise erfassen Sie den Zustand der Daten häufig und können dies zur Erstellung einer SCD2-Tabelle verwenden.

Schnappschuss-Strategien

Vollständige Auszüge

In seiner einfachsten Form erstellen Sie jeden Tag eine Kopie der gesamten Tabelle, einen Snapshot, und fügen ihn in einer Verlaufstabelle an. Wenn wir zum Beispiel die Kundentabelle des Stroopwaffle-Shops täglich abfotografieren und jeden Snapshot in einer Historientabelle speichern, erhalten Sie die folgende Historientabelle:

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	aktualisiert_am	snapshotted_at
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	2024-01-02T00:00
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	2024-01-03T00:00
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	2024-01-04T00:00
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	2024-01-05T00:00
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-05T16:00	2024-01-06T00:00

Verlaufstabelle mit täglichem Snapshotting unter Verwendung vollständiger Auszüge aus der Kundentabelle von Stroopwaffle Shop

In der resultierenden Tabelle können Sie sehen, dass ein Datensatz unabhängig davon kopiert wird, ob sich die Informationen eines Kunden ändern.

Der Vorteil von vollständigen Extrakten ist, dass sie einfach einzurichten sind und dass Sie gelöschte Datensätze identifizieren können (Datensätze, die in einem Extrakt enthalten sind, aber nicht im nächsten). Dies ist besonders bei weichen Löschungen nützlich. Bei harten Löschungen ist ein separater Prozess erforderlich, um auch Datensätze aus früheren Vollextrakten zu löschen. Der Nachteil der Strategie für Vollextrakte ist, dass Sie bei einem mittleren bis hohen Datenvolumen auf Probleme mit der Skalierbarkeit stoßen werden.

Delta-Extrakte

Eine Delta-Strategie erfordert eine zuverlässige updated_at Spalte, um zu prüfen, ob ein Datensatz in einer Quelltabelle seit dem letzten Snapshot aktualisiert wurde. Auf diese Weise extrahieren Sie nur eingefügte oder aktualisierte Datensätze und Sie erhalten nicht viele doppelte Datensätze in der Verlaufstabelle. Auch hierfür ist eine sehr zuverlässige updated_at Spalte erforderlich, denn wenn die updated_at Spalte beispielsweise nicht aktualisiert wird, wenn manuelle Korrekturen vorgenommen werden, führt dies zu Problemen mit der Datenqualität bei der Verwendung dieser Spalte. Um auf unser Beispiel zurückzukommen: Wenn Sie eine Delta-Extraktionsstrategie verwenden, werden Sie viel weniger Datensätze erhalten.

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	aktualisiert_am	gelöscht_am	snapshotted_at
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	NULL	2024-01-02T00:00
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-05T16:00	NULL	2024-01-06T00:00
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-06T12:00	2024-01-06T12:00	2024-01-07T00:00

Verlaufstabelle mit täglichem Snapshotting unter Verwendung von Delta-Auszügen der Kundenquelltabelle von Stroopwaffle Shop

Die resultierende Tabelle ist viel schlanker und enthält keine doppelten Datensätze mehr.

Wenn es keine Möglichkeit gibt, eine zuverlässige updated_at Spalte zu verwenden, um aktualisierte Datensätze zu identifizieren, können Sie auch die Datensatzinhalte des vorherigen Zustands mit dem neuen Zustand vergleichen und nachsehen, ob sich irgendwelche Werte geändert haben. Dazu können Sie jede Spalte einzeln auf Änderungen überprüfen oder einen vollständigen Datensatz-Hash erstellen (empfohlen).

Gelöschte Datensätze müssen ebenfalls berücksichtigt werden, z.B. durch Hinzufügen des Zeitstempels deleted_at in dem Moment, in dem ein Datensatz gelöscht wird. Dies funktioniert jedoch nur bei weichen Löschungen. Für harte Löschungen, ähnlich wie bei vollständigen Extrakten, müssen Sie einen separaten Prozess einrichten, um Datensätze aus allen Delta-Extrakten zu löschen.

Erstellen einer SCD2-Tabelle auf der Grundlage eines Snapshotings

Sowohl die Strategie des vollständigen Extrakts als auch die des Delta-Extrakts führen zu demselben SCD2-Modell:

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	scd2_valid_from	scd2_valid_until	is_current_record
123	Adam Johnson	Hauptstraße 1, London	2024-01-01T14:00	2024-01-01T14:00	2024-01-05T15:59	false
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-01T15:00	NULL	true

Sie sehen, dass die doppelten Datensätze, die mit der Strategie des vollständigen Extrakts erfasst wurden, verschwunden sind und nur der erstellte Datensatz und der aktualisierte Datensatz in der SCD2 enthalten sind. Bei der Delta-Strategie können Sie sehen, dass die Delta-Strategie bereits zu einer Tabelle führt, die einer SCD2-Tabelle sehr ähnlich ist.

Einschränkung der Genauigkeit von Schnappschüssen

Es gibt verschiedene Möglichkeiten, einen Snapshot zu erstellen. Allen gemeinsam ist jedoch, dass der Detaillierungsgrad von der Häufigkeit der Snapshot-Erstellung abhängt. Mit anderen Worten: Wenn Sie monatlich einen Snapshot Ihrer Quelltabelle erstellen, erfassen Sie den Zustand der Daten nur einmal pro Monat und wissen nicht genau, was dazwischen passiert ist. Wenn wir unser Kundenbeispiel betrachten und die Verlaufstabelle mit CDC mit der Verlaufstabelle mit Snapshotting vergleichen, werden Sie feststellen, dass wir die erste UPDATE-Operation (den roten Datensatz), die die Adresse geändert hat, übersehen haben. Das liegt daran, dass zwischen 2 Snapshots 2 Vorgänge am selben Tag stattfanden.

kunde_id	kunde_name	Kunde_Adresse	erstellt_am	aktualisiert_am	Betrieb	zeilen_operation_at
123	Adam Johnson	Hauptstraße 1, London	2024-01-01T14:00	2024-01-01T14:00	INSERT	2024-01-01T14:00
123	Adam Johnson	Hauptstraße 3, London	2024-01-01T14:00	2024-01-01T15:00	UPDATE	2024-01-01T15:00
123	Adam Johnson	Hauptstraße 4, London	2024-01-01T14:00	2024-01-05T16:00	UPDATE	2024-01-05T16:00

CDC-Quelltabelle von Stroopwaffle Shop, in der der rot markierte Datensatz nicht durch einen Snapshot erfasst werden würde

Wenn wir stündliche Snapshots erstellt hätten, wäre das Problem gelöst. In der Regel geht eine Erhöhung der Snapshotting-Häufigkeit jedoch Hand in Hand mit der Prüfung, wie häufig Sie aktualisieren können, bevor die Systeme nicht mehr skalieren können. Wenn zum Beispiel ein stündlicher Snapshot-Prozess mehr als 1 Stunde dauert oder die Quelldaten sehr groß sind, sollten Sie von stündlichen Snapshots absehen und weniger häufige Snapshots in Betracht ziehen.

Und schließlich gibt es beim Snapshotting keine Möglichkeit, einen Snapshot wiederherzustellen, wenn er aus irgendeinem Grund nicht erfasst wird. Das heißt, wenn Ihr Snapshotting-Prozess 5 Tage lang ausfällt, gibt es keine Möglichkeit, den Zustand in jedem dieser 5 Tage zu ermitteln und wiederherzustellen. Mit CDC ist dies jedoch kein Problem.

Wie funktioniert der Snapshot mit gelöschten Datensätzen?

Die Verarbeitung gelöschter Datensätze mit der Snapshotting-Strategie ist nicht ganz einfach. An einem bestimmten Punkt müssen Sie feststellen, welche Datensätze gelöscht wurden. Der genaue Punkt, an dem Sie dies tun, ist je nach Strategie leicht unterschiedlich.

Identifizieren gelöschter Datensätze mit vollständigen Auszügen

Wenn Sie einen Snapshot erstellen und die vollständigen Auszüge mit jedem Snapshot speichern, können Sie den vorherigen Zustand mit dem neuen Zustand vergleichen und feststellen, ob eine Entität im vorherigen Zustand im neuen Zustand nicht mehr vorhanden ist. Um genauer zu sein, führen Sie diese Aktion durch, NACHDEM Sie die gesamte Quelltabelle in die Historientabelle im Data Lake gedumpt haben. Dazu können Sie eine einfache WINDOW-Funktion verwenden. Zum Beispiel:

-- BigQuery SQL
with customers as (
    select customer_id, customer_name, customer_address, created_at, updated_at, snapshotted_at from raw.customer_snapshots
),

identify_previous_and_next_record as (
  select 
    *,
    if(
      lag(customer_id) over (partition by customer_id order by snapshotted_at) is not null, 
    true, false) as has_previous_record,

    -- This method is not very scalable since it's a window function. For larger tables, consider storing the max snapshotted_at in a pre-computed Jinja variable in dbt.
    max(snapshotted_at) over () as max_snapshotted_at
  from 
    customers
),

identify_deleted_customers as (
  select 
    *,
    if(snapshotted_at <> max_snapshotted_at AND has_next_record is false, true,false) as is_deleted_record
  from 
    identify_previous_and_next_record
)

select * from identify_deleted_customers

Identifizierung gelöschter Datensätze mit Delta-Extrakten

Die Identifizierung gelöschter Datensätze mit Delta-Extrakten ist etwas weniger einfach, da diese Logik entweder beim Einlesen oder bei der Umwandlung von Daten erfolgen kann. Der Prozess, der einen Snapshot der Quelldaten erstellt, sollte neue, aktualisierte und gelöschte Datensätze gleichzeitig identifizieren. Dabei wird der neueste Stand im Data Lake mit dem aktuellen Stand des Quellsystems verglichen. Da der Ingestionsprozess in der Regel von Unternehmen zu Unternehmen sehr unterschiedlich ist, ist es nicht einfach, ein Beispiel zu nennen. Die Funktion dbt snapshots ist jedoch in der Lage, diesen Prozess in der Transformationsphase durchzuführen (da die Daten bereits auf der Datenplattform vorhanden sein sollten, damit dbt snapshot funktioniert). Sie werden in Teil 2 dieser Serie mehr darüber erfahren.

Welches Muster verwenden Sie wann?

Ausgehend von den Beschreibungen der CDC- und Snapshotting-Strategien könnte man meinen, dass CDC aufgrund der Detailgenauigkeit und der Latenzzeit immer der empfohlene Weg wäre. Die Wahrheit ist jedoch, wie immer, dass es darauf ankommt.

Strategie	Genauigkeit	Skalierbarkeit	Pünktlichkeit	Kosten
CDC (Echtzeit)	Genauigkeit auf Transaktionsniveau	Geeignet für große Datenmengen	Aktualisierungen in Echtzeit	Relativ schwieriger Aufbau und relativ höhere Kosten für den Betrieb von Streaming-Pipelines.
CDC (Batch)	Genauigkeit auf Transaktionsniveau	Geeignet für große Datenmengen	Abhängig von der Batch-Häufigkeit	Relativ schwieriger Aufbau und relativ geringere Kosten für den Betrieb von Batching-Pipelines.
Schnappschüsse (vollständige Auszüge)	Abhängig von der Häufigkeit des Snapshotings (z.B. täglich)	Geeignet für kleine Datenmengen	Hängt von der Häufigkeit des Schnappschusses ab	Relativ einfache Einrichtung und relativ niedrige Betriebskosten. Etwas höhere Lagerkosten aufgrund des ineffizienten Lagermusters.
Snapshotting (Delta-Extrakte)	Abhängig von der Häufigkeit des Snapshotings (z.B. täglich)	Geeignet für mittlere Datenmengen	Abhängig von der Snapshot-Häufigkeit	Relativ einfach einzurichten und relativ geringe Betriebskosten.

Mit CDC können Sie zwar eine vollständige Historientabelle für jeden Datensatz erstellen, aber die Kosten für die Implementierung, die Pflege und den Betrieb eines solchen Prozesses können den Mehrwert, der sich aus der Verfügbarkeit dieser Details ergibt, übersteigen. Insbesondere die Streaming-Variante von CDC wird zu höheren Betriebskosten führen.

Die Implementierung und Pflege von CDC-Prozessen erfordert Data-Engineering-Kapazitäten, denn es geht nicht nur darum, eine Funktion einzuschalten. Vor allem, wenn Sie eine CDC-Lösung in Echtzeit benötigen, ist die Komplexität deutlich höher. Außerdem, und das ist ein kleines Detail, muss CDC auch von Ihrem Quellsystem unterstützt werden.

Andererseits ist die Erstellung von Snapshots sehr unkompliziert und relativ einfach einzurichten. Je nach Verfügbarkeit der Spalte updated_at können Sie diesen Prozess sogar recht einfach gestalten, wie Sie auch in Teil 2 dieser Serie sehen werden. Für die meisten Anwendungsfälle benötigen Sie in der Regel keine so detaillierte Historie und tägliche Snapshots sind mehr als genug.

Eine letzte sehr wichtige Anforderung, die all diese Überlegungen überflüssig macht, ist die Notwendigkeit der Auditierbarkeit. Wenn Ihr Unternehmen in der Lage sein muss, Audits einzuhalten, ist die Genauigkeit auf Transaktionsebene sehr wichtig.

Bei Xebia haben wir viele Anwendungsfälle in Unternehmen gesehen, die diese ganze Komplexität gar nicht benötigen. Wir empfehlen daher, den Anwendungsfall, für den Sie SCD2-Modelle haben möchten, sorgfältig zu evaluieren und dann zu entscheiden, wie Sie historische Daten extrahieren und speichern möchten.

Wie erstellt man SCD2-Modelle in dbt?

Jetzt wissen Sie, wie Sie historische Daten anhand verschiedener Muster extrahieren und speichern können.

In Teil 2 dieser Serie werde ich mich eingehender mit der praktischen Erstellung von SCD2-Modellen auf der Grundlage historischer Daten beschäftigen. Dabei werden sowohl die bestehende dbt Snapshots-Funktion (die zum Snapshotting-Muster der Delta-Extraktion gehört) als auch benutzerdefinierte Implementierungen von SCD2-Modellen in dbt behandelt.

Bleiben Sie dran für Teil zwei!