Extract, Transform, and Load (ETL)

Ziel

ETL führt in einem einzigen Bestand Daten zusammen, die mehrere Systeme getrennt halten, jedes mit eigenem Format, eigener Codierung und eigenem Aktualisierungsrhythmus.

Ein Dashboard, eine Reserveberechnung, eine aufsichtsrechtliche Meldung lesen nur den Zielbestand. Was sie anzeigen, ist so viel wert wie der Prozess, der ihn gefüllt hat: Seine Frequenz bestimmt die Aktualität der Zahlen, seine Regeln bestimmen deren Bedeutung, die von ihm zurückgewiesenen Datensätze bestimmen deren Vollständigkeit. Eine Zahlendifferenz zwischen zwei Berichten klärt sich fast immer eher im Prozess als im Werkzeug, das sie anzeigt.

Einsatz

Wann einsetzen

• Mehrere Quellsysteme sind zusammenzuführen: abweichende Formate, Codierungen und Wörterbücher, die abzugleichen sind.
• Ein wiederkehrender Bericht ist zu speisen: Sind die Regeln einmal festgelegt, läuft derselbe Prozess zu jedem Termin erneut, ohne neuen Entscheid.
• Deskriptive Analytik oder Proof of Concept: das Feld, auf dem der Leitfaden das klassische ETL ansiedelt, vor jeder Einführung im grossen Massstab.
• Nachvollziehbarkeit ist gefordert: Der Audit-Trail des Ladens sagt, welcher Wert sich wann und unter welchem Lauf geändert hat.

Wann nicht einsetzen

• Algorithmische Entscheide nahezu in Echtzeit: Die Stapelverarbeitung hält die Latenz nicht ein; hier gehört eine Stream-Verarbeitung hin.
• Punktuelle Auswertung auf einer einzigen Quelle: Eine Abfrage oder ein Export genügt; die Investition amortisiert sich nicht.

Beschreibung

Extrahieren

Die Extraktion holt die benötigten Daten aus ihren Ursprungssystemen, und der Leitfaden unterscheidet dabei drei Arbeiten.

Die erste bestimmt die Quellen und die Datentypen ausgehend vom fachlichen Problem: Sie beginnt bei den grossen Beständen (Kundenbeziehungsmanagement, Fakturierung, Vertriebskanäle); je genauer sich der Bedarf abzeichnet, desto tiefer steigt sie zu den Entitäten und danach zu den Feldern hinab.

Die zweite schafft eine einheitliche Klassifikation. Jede Quelle kommt mit ihren eigenen Konventionen und ihrem eigenen Datenwörterbuch; Definitionen, Beschreibungen und Formate werden so abgeglichen, dass ein einziges Extraktionsschema für alle gilt.

Die dritte prüft die Integrität dessen, was herauskommt. Ein Teil davon wird automatisch geprüft, über ein Schema, das die Quellelemente den extrahierten Elementen zuordnet: Konformität von Grössen und Formaten, Redundanzen, Verluste bei der Übertragung. Der andere Teil bleibt manuell: Minimal- und Maximalwerte, Identifikatoren, zulässige Werte, Stichproben. Das DMBOK liefert die Begriffe, unter die sich diese Kontrollen einordnen lassen: Vollständigkeit, Validität, Konsistenz, Aktualität und Eindeutigkeit. Das verhindert, dass eine Kontrolle nach dem Skript benannt wird, das sie ausführt.

Bleibt die Frage, die allein die inkrementelle Extraktion stellt: Was hat sich seit dem letzten Lauf geändert? Kimball und Caserta behandeln diese Erfassung geänderter Daten (change data capture) in ihrer ETL-Architektur als Extraktionstechnik, neben einer Disziplin für Wiederanlauf und Neustart. Der Zeitstempel der letzten Änderung ist der billigste und der anfälligste Weg: Er sieht die Löschungen nicht und er bewegt sich nicht, wenn eine Korrektur über einen technischen Lauf eingespielt wurde, sodass sich die Abweichungen ohne Signal ansammeln, bis eines Tages eine manuelle Abstimmung sie entdeckt. Ein monatliches oder vierteljährliches vollständiges Laden begrenzt diese Drift. Das Lesen des Transaktionsprotokolls der Quelldatenbank sieht alle Schreibvorgänge, zum Preis eines Zugriffs, den der Betreiber nicht immer gewährt. Der vollständige Vergleich mit dem vorherigen Snapshot sieht alles, zum Preis einer vollständigen Extraktion bei jedem Lauf.

Die Staging Area

Der Leitfaden definiert die Staging Area als einen logischen Ort für die Daten, der die Transformationen erleichtert. Kimball und Caserta geben ihr eine zweite Funktion: den Wiederanlaufpunkt. Ein Prozess, der seinen Auszug ablegt und ihn behält, startet nach einem Fehlschlag von diesem Auszug aus neu. Der Wiederanlauf aus der Staging Area dauert einige Minuten. Drei Produktionssysteme erneut zu extrahieren dauert mehrere Stunden und liefert einen Snapshot, der nicht mehr der des laufenden Stapels ist, weil die Systeme in der Zwischenzeit weitergelaufen sind.

Dieselben Autoren stellen eine Bedingung an den Wiederanlauf: Der Prozess muss sich ohne Nebenwirkungen wiederholen lassen, also denselben Zustand mit derselben Zeilenzahl erzeugen. Diese Eigenschaft erreicht man, indem der gekennzeichnete Stapel gelöscht und danach neu eingefügt wird oder indem er über einen natürlichen Schlüssel geschrieben wird. Ohne sie schreibt ein nach einem Teilfehler neu gestarteter Prozess erneut, was er bereits geschrieben hatte: Die Duplikate bestehen die Formatkontrollen und blähen die Aggregate um einige Prozent auf, eine Grössenordnung, die zu klein ist, um zu alarmieren, und zu gross, um sie zu übergehen. Der Probelauf in der Testumgebung ist es, was diese Eigenschaft nachweist.

Transformieren

Die Transformation überführt die extrahierten Daten in ein auswertbares und korrektes Format, das der fachlichen Logik entspricht.

Die Aufgabe der Analystin betrifft den Sinn. Sie prüft, dass keine Geschäftsregel verletzt wird: dass eine Zusammenfassung in Klassen nicht die Schwelle verdeckt, an der ein Entscheid hängt, dass eine Imputation keine Beobachtung erfindet, dass eine vor einer Summe angewandte Rundung das Total nicht verschiebt. Es ist der letzte Moment, in dem die Frage "bedeutet dieser Wert noch dasselbe?" gestellt wird, bevor die Zahl in eine Berechnung eingeht.

Laden

Das Laden bringt die transformierten Daten aus der Staging Area in den Zielbestand. Die üblichen Aufgaben sind, das Zielformat und den Lademodus am fachlichen Bedarf zu überprüfen, den Stapel zu schreiben, den Audit-Trail zu erzeugen und danach den Ablauf zu normieren, damit er zu jedem Termin gleich abläuft.

Das inkrementelle Laden vergleicht den Stapel mit den bereits vorhandenen Daten und schreibt nur die Differenz, was die Aktualisierung der Berichte für die Entscheidungsfindung verkürzt. Das vollständige Laden ersetzt die gesamten Daten des Ziels; der Leitfaden hält es für besser geeignet für prädiktive und präskriptive Arbeiten, die auf einem zu einem bestimmten Stichtag rekonstruierten Datenbestand arbeiten. Kimball und Caserta behandeln diese Wahl als Architekturentscheid, der in Kenntnis seiner beiden Folgen getroffen wird: der Dauer des Ladefensters und der Last, die den Quellsystemen auferlegt wird.

Überschreiben oder historisieren

Das Laden trägt einen fachlichen Entscheid: Was geschieht mit dem alten Wert, wenn ein Attribut sich ändert? Kimball und Ross haben die Antworten unter dem Namen der langsam veränderlichen Dimensionen (slowly changing dimensions) katalogisiert. Typ 1 überschreibt: Der neue Wert ersetzt den alten und die Historie verschwindet. Typ 2 fügt eine Zeile hinzu, schliesst die vorherige mit einem Enddatum ab und kennzeichnet die aktuelle Version, sodass jeder Fakt an die Version gebunden bleibt, die im Zeitpunkt seines Eintretens galt. Typ 3 behält einen einzigen früheren Wert, in einer zusätzlichen Spalte. Ein Design Tip der Kimball Group beschreibt die Erweiterungen mit den Nummern 0, 4, 5, 6 und 7.

Typ 1 ist das Standardverhalten der meisten Werkzeuge, was ihn zur unauffälligsten Falle des Ladens macht: Er erzeugt keinen Fehler, keine Rückweisung und keine Warnung. Der Bericht des letzten Jahres liefert, dieses Jahr neu erzeugt, nicht mehr dieselben Zahlen, und niemand verfügt über den Wert, mit dem sich die Abweichung rekonstruieren liesse. Der Entscheid gehört dem Fachbereich, Attribut für Attribut: Braucht eine Berechnung, ein Bericht oder eine gesetzliche Pflicht das Wissen darüber, was dieses Attribut zu einem vergangenen Zeitpunkt enthielt? Der Wohnkanton einer versicherten Person und der auf einen Vertrag angewandte Satz brauchen es. Eine Korrespondenzadresse oder eine Telefonnummer selten.

Der Audit-Trail und die Abstimmung

Der Audit-Trail hält für jeden Lauf fest, was das Laden geschrieben hat: die hinzugefügten Zeilen, die geänderten oder ersetzten, den früheren Wert und die Kennung des Laufs. Er entsteht beim Laden, weil das Laden der Moment ist, in dem der Wert sich ändert.

Er speist die Abstimmung, die auf jeden Lauf folgt: Zahl der gelesenen, transformierten, geladenen und zurückgewiesenen Zeilen, Prüfsummen über die Beträge, Zeile für Zeile erklärte Abweichung. Das Register der zurückgewiesenen Datensätze wird am nächsten Morgen durchgesehen, mit einem Grund je Datensatz und einer schriftlichen Weisung darüber, was mit ihnen geschieht: Korrektur an der Quelle und Nachlieferung im nächsten Stapel, manuelle Korrektur im Ziel oder bewusster Verzicht.

Diese Spur ist etwas anderes als die Datenherkunft (data lineage), die das DMBOK unter dem Metadatenmanagement behandelt. Die Datenherkunft beschreibt den Weg, den ein Element zwischen den Systemen zurücklegt, und die Transformationen, die es erfährt, unabhängig von jedem einzelnen Lauf. Die Datenherkunft beantwortet die Frage "woher kommt dieses Feld", der Audit-Trail die Frage "wer hat diesen Wert am Dienstagabend geändert".

Die Abgrenzung zum Data Mapping

Data Mapping deklariert, ETL führt aus. Die Spezifikation nennt für jedes Zielattribut, welche Quelle es speist und nach welcher Regel; der Prozess wendet diese Deklaration auf die tatsächlichen Datensätze an. Alles, was mit der Zeit zu tun hat, gehört dem Prozess: die Ablaufsteuerung, das Ladefenster, der Wiederanlauf nach einem Fehlschlag, die Rückweisungen, die Spur dessen, was geschrieben wurde. Die Abstimmung nach dem Laden liegt ebenfalls auf dieser Seite der Grenze. Dort zeigt sich eine Regel, die der Prozess anwendet, die aber in der Spezifikation fehlt.

ETL oder ELT

Die Technologien für grosse Datenmengen kehren die Reihenfolge der letzten beiden Schritte um: extrahieren, laden, dann transformieren. Die Rohdaten werden auf einem verteilten Dateisystem oder in einem Data Warehouse in der Cloud abgelegt, und die Transformation läuft auf der Rechenleistung der Zielplattform. Das ist die übliche Antwort auf grosse, heterogene oder schwach strukturierte Datenmengen. Der Leitfaden verortet dort die Grenzen der klassischen ETL-Werkzeuge, hält aber daran fest, dass die Prinzipien des ETL unabhängig von den Werkzeugen gelten: Nur die Reihenfolge und der Ort der Transformation ändern sich. Für die Analystin findet die Prüfung der Geschäftsregeln dann nach dem Laden statt, auf Daten, die den Nutzenden bereits zugänglich sind, was dazu zwingt, im Bestand die Rohdaten von den geprüften Daten zu trennen, weil sonst ein Dashboard am Ende eine Zwischentabelle liest.

Woran der Prozess scheitert

Die stillen Rückweisungen. Ein Werkzeug, das nicht konforme Datensätze ohne Register und ohne Zähler aussondert, liefert ein Ziel, das um ein unbekanntes Volumen unvollständig ist. Die Kontrolle besteht aus einem Vergleich: an der Quelle gelesene Zeilen gegen geladene plus zurückgewiesene Zeilen.

Das Quellschema, das sich bewegt. Eine in einem Quellsystem hinzugefügte, umbenannte oder im Datentyp geänderte Spalte bricht die Extraktion oder lässt sie, schlimmer noch, auf einem falsch gewordenen Wert weiterlaufen. Die Quellsysteme an die Änderungskontrolle anzubinden und den Prozess bei einem unerwarteten Schema scheitern zu lassen, kostet weniger, als die Abweichung drei Monate später in einem Bericht zu entdecken.

KI-Überlegungen

Die Erzeugung des Transformationscodes ist die unmittelbarste Anwendung. Eine in der Mapping-Spezifikation geschriebene Regel lässt sich ohne Erfindung in einen SQL-Ausdruck oder in eine Transformation des Werkzeugs übersetzen, und ein erster Wurf ist rasch gegengelesen. Testdaten, die die Grenzfälle einer Regel abdecken, Nullwert, Wert ausserhalb des Wertebereichs, maximale Länge, entstehen auf demselben Weg und sind der Teil der Arbeit, den die Teams am seltensten schreiben.

Die Überwachung der Läufe eignet sich für statistisches Lernen. Ein auf der Historie der Ladevorgänge trainiertes Modell meldet einen Stapel, dessen Volumen, dessen Verteilung eines Betrags oder dessen Anteil an Nullwerten von dem abweicht, was vergleichbare Läufe liefern. Die Kontrolle anhand eines festen Schwellenwerts sieht den Rückgang eines regionalen Zuflusses um 12 % an einem Feiertagsmontag nicht; ein Modell, das zwei Jahre an Stapeln gesehen hat, sieht ihn. Die Zuordnungstabelle zwischen zwei regionalen Codierungen vorzuschlagen, ist ein Ähnlichkeitsabgleich, dessen Ergebnis danach Zeile für Zeile validiert wird.

Der Sinn einer Geschäftsregel lässt sich nicht delegieren. Ein Modell erzeugt eine syntaktisch korrekte Transformation auf einem Feld, dessen Bedeutung in der Organisation es nicht kennt. Die Wahl zwischen Bewahren und Überschreiben der Historie hängt von nachgelagerten Verwendungen und von gesetzlichen Pflichten ab, die das Schema nicht trägt. Der Entscheid über die Rückweisungen, einen Datensatz aussondern, ihn korrigieren oder ihn unverändert laden, bestimmt die Vollständigkeit von allem, was danach berechnet wird.

Bleiben die Daten selbst. Ein Transformationsschritt, der einen externen Dienst aufruft, übergibt diesem bei jedem Lauf Produktionsdatensätze, AHV-Nummern, Beträge und Datumsangaben inbegriffen. Die Bekanntgabe von Personendaten an einen Dritten, der im Auftrag des Verantwortlichen bearbeitet, verlangt einen Vertrag zur Auftragsbearbeitung im Sinne von Art. 9 des Bundesgesetzes über den Datenschutz. Bei einem ausserhalb der Schweiz gehosteten Dienst kommt Art. 16 über die Bekanntgabe ins Ausland hinzu. Die Maskierung gehört daher vor den Aufruf.

Beispiele

Eine Krankenkasse lädt jede Nacht die Leistungstransaktionen ihrer regionalen Systeme in ein Data Warehouse, aus dem die Berechnung der Reserven und der Risikoausgleich zwischen den Kantonen gespeist werden. Eine versicherte Person zieht am 1. Juni 2024 von Waadt nach Genf. Die Extraktion der folgenden Nacht meldet den neuen Kanton, und der Lademodus entscheidet über das Schicksal des alten.

Eine Rechnung vom 12. März 2024 über CHF 1'240.50 verweist auf den Surrogatschlüssel 4711. Bei Typ 2 bleibt sie an die Waadtländer Zeile gebunden und die Abrechnung des Risikoausgleichs für 2024 zählt sie weiterhin zu Waadt. Bei Typ 1 zählt dieselbe Rechnung nunmehr zu Genf: Die Kantonssumme eines abgeschlossenen Jahres ändert sich am Tag eines Umzugs, ohne dass eine einzige Schreiboperation die Leistungstabelle berührt hätte. Der eigene Surrogatschlüssel der zweiten Zeile ist es, was die Historisierung möglich macht; den Schlüssel 4711 für die Genfer Version wiederzuverwenden, würde zum Verhalten von Typ 1 zurückführen.

Bei 40'000 Waadtländer Versicherten, von denen 1,5 % im Jahr den Kanton wechseln, also 600 Personen mit einer durchschnittlichen Belastung von CHF 3'800, verschiebt das Überschreiben rund CHF 2'280'000 an Leistungen von einem Kanton in den anderen, in einer bereits eingereichten Abrechnung.

Dieselbe versicherte Person zieht im März 2025 weiter nach Freiburg. Bei Typ 2 schliesst die folgende Nacht die Genfer Zeile und öffnet eine dritte mit eigenem Schlüssel; die Rechnungen von 2024 bleiben je nach Datum an 4711 und 5290 gebunden. Bei Typ 1 ersetzt dieselbe Nacht GE durch FR auf der Zeile 4711 und verschiebt die Abrechnung 2024 ein zweites Mal.

Visualisierungen

Der Prozess lässt sich zeichnen, weil zwei seiner Eigenschaften sich besser sehen als lesen lassen: der Ort, von dem ein Lauf nach einem Fehlschlag wieder anläuft, und die Tatsache, dass der Audit-Trail ein Erzeugnis des Ladeschritts ist. Auch die beiden Verzweigungen verdienen ihren Strich, inkrementell oder vollständig bei der Extraktion, Schreiben der Differenz oder Ersetzen beim Laden, weil sie unabhängig voneinander entschieden werden.

Zwei Register bleiben Tabellen, der Katalog der Transformationsarten und der Vergleich zwischen Überschreiben und Historisierung: Der Stoff passt in Zeilen und Spalten. Daraus ein Bild zu machen, würde ihm die Auswahl und die Sortierung nehmen.

Aufwand

Werkzeuge

Grafische ETL-Werkzeuge (Talend, SQL Server Integration Services, Azure Data Factory, Informatica, Pentaho) sind die Familie, die der Leitfaden beschreibt: Konnektoren zu den Unternehmensquellen, grafische Sicht auf den Ablauf, was einen Teil der Arbeit auch Anwenderinnen und Anwendern öffnet, die keinen Code schreiben. Ihre Grenze ist die Lesbarkeit für den Fachbereich, dessen Zustimmung die Regeln gleichwohl voraussetzen: Eine im Werkzeug ausgedrückte Transformation lässt sich in einer Sitzung schlecht abnehmen.

Die Scheduler (Apache Airflow, Dagster oder der bereits vorhandene Scheduler des Unternehmens) tragen, was das Transformationswerkzeug nicht trägt: die Abhängigkeiten zwischen den Aufgaben, die Ausführungsfenster, die Wiederholungsversuche und die Alarmierung. Dort wird der Wiederanlauf geregelt.

Transformationswerkzeuge im Data Warehouse (allen voran dbt) entsprechen dem ELT-Schema: Die Transformationen werden in versioniertem SQL geschrieben, laufen auf der Zielplattform und werden über Zusicherungen geprüft, die an jeder Tabelle hängen. Die Geschäftsregel wird wieder lesbar, zum Preis einer Abhängigkeit von der verrechneten Rechenleistung der Plattform.

Die Zielplattformen (Snowflake, BigQuery, Databricks oder ein PostgreSQL-Warehouse auf der internen Infrastruktur) bestimmen den verfügbaren Lademodus und die Kosten einer vollständigen Ersetzung. Die Wahl legt das nächtliche Ladefenster fest.

Werkzeuge für Profiling und Qualität (Great Expectations, die Profiling-Funktionen der ETL-Werkzeuge, eine Reihe eigener Abfragen) messen die Dimensionen des DMBOK an jedem Stapel und lassen den Prozess scheitern, wenn ein Schwellenwert überschritten wird. Eine Kontrolle, die protokolliert, ohne den Prozess anzuhalten, lässt genau die Daten herein, die sie soeben als falsch bezeichnet hat.

Quellen

• IIBA, Guide to Business Data Analytics, §3.10 Extract, Transform, and Load (ETL): die Definition, die drei Schritte und ihre Arbeiten, die Staging Area, der Katalog der Transformationsarten, die Lademodi inkrementell und vollständig, die Erzeugung der Audit-Trails, die ELT-Reihenfolge der Technologien für grosse Datenmengen sowie Stärken und Grenzen der Technik.
• Ralph Kimball und Joe Caserta, The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data, Wiley, 2004: die Staging Area als Wiederanlaufpunkt, die Wiederholung ohne Nebenwirkungen als Bedingung des Wiederanlaufs, die Erfassung geänderter Daten als Extraktionstechnik, die Wahl zwischen inkrementellem und vollständigem Laden als Architekturentscheid.
• Kimball Group, ETL Architecture's 34 Subsystems: die Erfassung geänderter Daten und der Wiederanlauf nach einem Fehlschlag als benannte Bestandteile einer ETL-Architektur.
• Ralph Kimball und Margy Ross, The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3. Auflage, Wiley, 2013: die langsam veränderlichen Dimensionen der Typen 1, 2 und 3.
• Kimball Group, Design Tip #152: Slowly Changing Dimension Types 0, 4, 5, 6 and 7: die Erweiterungen des Katalogs der langsam veränderlichen Dimensionen.
• DAMA International, DAMA-DMBOK: Data Management Body of Knowledge, 2. Auflage: Kapitel 12 Metadata Management für die Datenherkunft, Kapitel 13 Data Quality für die bei der Extraktion genannten Qualitätsdimensionen.
• Bundesgesetz über den Datenschutz (DSG), SR 235.1, Art. 9 und 16: der Vertrag zur Auftragsbearbeitung, der verlangt wird, wenn ein Dritter Personendaten im Auftrag des Verantwortlichen bearbeitet; die Voraussetzungen der Bekanntgabe ins Ausland, wenn dieser Dritte ausserhalb der Schweiz gehostet ist.