ETL

Was es ist

ETL steht für Extract, Transform, Load: Daten werden aus den
Systemen kopiert, in denen sie entstehen, in die von einem anderen
System erwartete Form und Bedeutung gebracht und dort geschrieben,
meist in ein Data Warehouse. Die nahe verwandte Variante ELT behält
die drei Schritte bei, lädt aber zuerst die Rohdaten und
transformiert sie im Zielsystem. Viele Teams schreiben ETL/ELT, um
beides abzudecken.

Jeder Buchstabe steht für eine eigene Aufgabe. Extract liest aus
einer Quelle: einer ganzen Tabelle, einer Exportdatei, einer API
oder nur den seit dem letzten Lauf geänderten Zeilen. Diese werden
über einen Zeitstempel wie `updated_at` oder über Change Data
Capture (CDC) gefunden, das die eigenen Aufzeichnungen der Datenbank
zu jeder Änderung liest. Bei Transform werden Daten bereinigt und
abgeglichen: Text wird in Datumswerte und Zahlen umgewandelt,
Einheiten und Zeitzonen werden vereinheitlicht, Duplikate entfernt
und Codes in Referenztabellen nachgeschlagen. Anschließend wird das
Ergebnis in das Modell des Zielsystems gebracht, etwa in Fakten- und
Dimensionstabellen. Load schreibt das Ergebnis, entweder als
Massenimport in eine leere Tabelle oder als Zusammenführung, die
vorhandene Zeilen aktualisiert und neue hinzufügt.

Die Reihenfolge der letzten beiden Schritte entscheidet, wo die
Arbeit läuft. Klassisches ETL transformiert auf einem separaten
Server, bevor etwas das Zielsystem erreicht. Das war sinnvoll, als
Speicher und Rechenleistung im Warehouse knapp und teuer waren. ELT
legt die Rohdaten in Staging-Tabellen im Zielsystem ab und führt die
Transformation dort in SQL aus. Die Rohdatenkopie bleibt verfügbar.
Ändert sich eine Regel, lässt sich die Transformation deshalb erneut
auf die historischen Daten anwenden, ohne diese ein zweites Mal zu
extrahieren. Der Preis: Alles, was die Quelle gesendet hat,
einschließlich nicht angeforderter Spalten, liegt nun im Warehouse
und muss dort verwaltet und kontrolliert werden.

PostgreSQL bietet für jeden Schritt eigene Werkzeuge. `COPY` bewegt
Zeilen zwischen einer Tabelle und einer Datei im Text-, CSV- oder
Binärformat und ist der schnellste Weg zum Laden. Das Lesen einer
Datei auf dem Server erfordert Superuser-Rechte oder die Rolle
`pg_read_server_files`. Der psql-Befehl `\copy` liest die Datei
dagegen auf dem Client. `INSERT ... ON CONFLICT` und `MERGE` führen
neue Zeilen mit vorhandenen zusammen. Logical Decoding verwandelt
das Write-Ahead Log für CDC in einen Strom von Zeilenänderungen, und
eine Prozedur in PL/pgSQL kann die Transformation nahe an den Daten
halten, die sie verändert.

Warum es wichtig ist

Die meisten lohnenden Geschäftsfragen erfordern Daten aus mehreren
Systemen. Bestellungen liegen in einer Datenbank, Zahlungen im
Export eines Anbieters, Kundendaten in einem CRM. Solange sie nicht
unter einheitlichen Regeln an einem Ort zusammengeführt werden, wird
jede Antwort von Hand zusammengestellt, und das Vorgehen ändert sich
mit der ausführenden Person. Genau diese Zusammenführung übernimmt
ETL. Ein Warehouse ist nur so korrekt wie die Ladevorgänge, die es
füllen.

Im Transformationsschritt hält ein Unternehmen außerdem fest, was
seine Daten bedeuten. Welche Zeilen zählen als Verkauf, welchem Tag
wird eine Zahlung um Mitternacht zugerechnet, welcher Datensatz hat
Vorrang, wenn zwei Kundeneinträge übereinstimmen? Wird das einmal im
Ladevorgang entschieden, gilt die Antwort für jeden nachgelagerten
Bericht. Entscheidet jeder Bericht selbst, laufen die Antworten
auseinander, und zwei Dashboards zeigen zwei Summen.

Die Art der Extraktion bestimmt, wie der Aufwand wächst. Ein
vollständiger Neuladevorgang kopiert jedes Mal alles. Das ist
einfach und korrigiert sich selbst, weil der Fehler von gestern
heute Nacht überschrieben wird. Die Kosten steigen jedoch mit der
gesamten Datenmenge. Ein inkrementeller Ladevorgang überträgt nur
Änderungen; seine Kosten steigen deshalb mit der Änderungsrate. Er
muss sich merken, wo der letzte Lauf aufgehört hat, in der Quelle
gelöschte Zeilen erkennen und verspätete Zeilen mit alten
Zeitstempeln annehmen. Die meisten Systeme beginnen mit
vollständigem Neuladen und stellen Tabelle für Tabelle um, sobald
die Nacht dafür nicht mehr ausreicht.

Der laufende Aufwand entsteht dadurch, dass Ladevorgänge
unbeaufsichtigt Code ausführen, meist nachts und gegen Quellen, die
sich ohne Ankündigung ändern. Sie brauchen Protokolle darüber, was
lief und wie viele Zeilen übertragen wurden, Alarme bei einem
abgebrochenen Lauf und vor allem eine Eigenschaft: Ein mit derselben
Eingabe wiederholter Lauf sollte das Ziel exakt so hinterlassen wie
ein einzelner Lauf. Ein solcher Ladevorgang lässt sich nach jedem
Fehler wiederholen, ohne dass vorher jemand nachsehen muss.

Wie es in der Praxis aussieht

Zersplitterte Pipelines verzögerten geschäftskritische Berichte in
mehreren Entwicklungs- und Produktionsumgebungen mit PostgreSQL.

600 ETL/ELT-Pipelines wurden in PL/pgSQL geschrieben, um sie zu
ersetzen: ETL in der Größenordnung Hunderter routinemäßiger
Ladevorgänge. Primär- und Fremdschlüsselbedingungen sicherten bei
den Transformationen die referenzielle Integrität, eindeutige und
zusammengesetzte Indizes unterstützten die Joins, und
Ausnahmebehandlung mit Transaktionskontrolle rollte einen
fehlgeschlagenen Lauf zurück. Inkrementelle Ladevorgänge nutzten CDC
und zeitstempelbasierte Deltas; Ausführungsprotokolle und Auditing
unterstützten Nachverfolgung und Fehlersuche. Geliefert wurden
Transformationslogik, Integritätsbedingungen, inkrementelles Laden
und Ausführungsprotokolle. Der Nachweis enthält weder einen
Vergleichsbenchmark noch eine berechnete Fehlerquote.

Eine Migration ist ETL, das einmal läuft. Der Umzug einer
Kartenanwendung von Microsoft SQL Server zu PostgreSQL mit PostGIS
erforderte, über 50 Millionen Geodatensätze durch ETL-Pipelines zu
übertragen. Dabei rechneten die Transformationsschritte jede
Koordinate in das Referenzsystem EPSG:4326 um. Nachdem die
Datensätze im Ziel angekommen waren, lieferten räumliche Abfragen
dort mit GiST- und SP-GiST-Indizes und einer abgestimmten
Konfiguration statt durchschnittlich 2,5 Sekunden Ergebnisse in
unter 250 Millisekunden, also zehnmal schneller. Die CPU-Last im
Backend sank um 65 %.

Über eine Netzwerkgrenze hinweg zeigen sechs ETL/ELT-Pipelines alle
drei im Namen enthaltenen Arbeitsschritte in der Praxis. Die
Extraktion bestand aus geplanten Downloads komprimierter Archive von
einem nur über ein IPsec-VPN erreichbaren Server. Skripte in Bash
und Python sortierten die darin enthaltenen Parquet-, CSV- und
i.bak-Dateien nach Typ und Spaltenlayout; die .bak-Sicherungen wurden		engineer.company	70
in einer lokalen Instanz von MSSQL Server wiederhergestellt. Je nach
Format wurden die Daten mit bcp, psql oder SSIS in Staging-Schemas
in MSSQL und PostgreSQL geladen, durch Prozeduren in PL/pgSQL und
T-SQL bereinigt, normalisiert und angereichert, dann mit gzip
komprimiert und über einen Bucket in Google Cloud Storage nach
Google BigQuery übertragen. Arbeit, die von Hand mindestens drei
Stunden gedauert hatte, lief von Anfang bis Ende in unter

20 Minuten. Die Synchronisierung wurde von wöchentlich auf täglich
umgestellt.

Wo es schiefgeht

Ein wiederholter Lauf lädt dieselben Zeilen doppelt. Ein Job fügt
mit einem einfachen `INSERT` ein, bricht nach der Hälfte wegen eines
Netzwerkfehlers ab, und der Scheduler startet ihn erneut von vorn.
Die beim ersten Mal geschriebenen Zeilen stehen nun doppelt in der
Tabelle, und jede darauf aufbauende Summe ist zu hoch. Geben Sie dem
Ziel einen Schlüssel, der eine Zeile fachlich identifiziert, und
führen Sie den Stapel mit `INSERT ... ON CONFLICT DO UPDATE`
zusammen. PostgreSQL dokumentiert dies auch bei hoher
Nebenläufigkeit als atomare Einfüge- oder Aktualisierungsoperation.
Alternativ lässt sich `MERGE` verwenden. Eine Einschränkung: Eine
einzelne Anweisung mit `ON CONFLICT DO UPDATE` weigert sich,
dieselbe Zeile zweimal zu ändern, und meldet eine
Kardinalitätsverletzung. Ein Stapel mit wiederholten Schlüsseln muss
daher zuerst auf eine Zeile je Schlüssel reduziert werden.

```sql
INSERT INTO meter_reading (meter_id, read_at, kwh)
SELECT DISTINCT ON (meter_id, read_at) meter_id, read_at, kwh
FROM staging_meter_reading
ORDER BY meter_id, read_at, received_at DESC
ON CONFLICT (meter_id, read_at)
DO UPDATE SET kwh = EXCLUDED.kwh;
```

Ein Massenimport bricht kurz vor dem Ende ab und hinterlässt eine
vollere Festplatte. `COPY FROM` schreibt die Zeilen nach und nach in
die Tabelle. Bricht der Befehl an einer fehlerhaften Zeile ab,
bleiben diese Zeilen im Zustand „gelöscht“ zurück: für Abfragen
unsichtbar, aber auf der Platte, bis `VACUUM` den Platz freigibt.
Ein Fehler kurz vor dem Ende einer großen Datei kann viel Speicher
verschwenden. PostgreSQL 17 führte `ON_ERROR ignore` ein. Damit
werden Zeilen in Text- oder CSV-Eingaben übersprungen, deren Werte
sich nicht in den Typ ihrer Spalte umwandeln lassen. PostgreSQL 18
ergänzte `REJECT_LIMIT`, um die Zahl der übersprungenen Zeilen zu
begrenzen. Das gilt nur für Typumwandlungen; eine Zeile, die eine
Integritätsbedingung verletzt, stoppt den Befehl weiterhin. Wer
zuerst in eine Staging-Tabelle lädt, dort prüft und die guten Zeilen
in einer Transaktion in die eigentliche Tabelle überträgt, hält eine
schlechte Datei von den Daten fern, die Menschen lesen.

Change Data Capture füllt die Festplatte. Logical Decoding liest
Änderungen über einen Replikations-Slot. Dieser hält jeden Teil des
Write-Ahead Logs vor, den sein Konsument noch braucht, unabhängig
davon, ob ein Konsument verbunden ist. Stoppt der Leser, wächst das
Log, bis die Platte voll ist. In extremen Fällen fährt der Server
herunter, um einen Überlauf der Transaktions-IDs zu verhindern. Die
PostgreSQL-Dokumentation sagt klar: Ein nicht mehr benötigter Slot
sollte entfernt werden. Nach einem Absturz kann ein Slot zudem die
letzten Änderungen erneut senden. Der Konsument muss daher mit
Duplikaten umgehen können; es ist wieder dieselbe Eigenschaft für
wiederholte Läufe.

Der erste Bericht am Morgen ist nach einem großen Ladevorgang
langsam. Der Planer entscheidet anhand von Statistiken zu jeder
Tabelle, wie er eine Abfrage ausführt. Ein Massenimport verändert
die Daten, auf denen sie beruhen. Autovacuum kann die Statistiken
selbst aktualisieren, aber nicht immer vor dem ersten Bericht.
`ANALYZE` als letzter Schritt des Ladevorgangs behebt das. Für sehr
große Ladevorgänge in eine vorhandene Tabelle ergänzt der
PostgreSQL-Leitfaden zum Befüllen einer Datenbank, dass sich das
Entfernen und anschließende Neuanlegen von Indizes und
Fremdschlüsseln lohnen kann: Sie gesammelt aufzubauen ist schneller,
als sie Zeile für Zeile zu aktualisieren. Während sie fehlen,
entfallen jedoch auch die Fehlerprüfungen. Vor dem Entfernen eines
eindeutigen Index ist deshalb besondere Vorsicht angebracht.

Weiterführende Lektüre

  * COPY — der Befehl für Massenimporte, seine Formate und Optionen
sowie die Rückstände eines fehlgeschlagenen Ladevorgangs.

  * Befüllen einer Datenbank — PostgreSQL-Empfehlungen für große
Ladevorgänge, von einer einzelnen Transaktion und `COPY` bis zum
Neuaufbau der Indizes und anschließendem `ANALYZE`.

  * MERGE — Einfügen, Aktualisieren und Löschen in einer Anweisung,
abhängig davon, wie jede Quellzeile zum Ziel passt.

  * INSERT — die Klausel `ON CONFLICT`, ihr atomares Upsert und das
Verbot, eine Zeile zweimal zu ändern.

  * Konzepte des Logical Decoding — wie Änderungen aus dem
Write-Ahead Log gelesen werden und warum ein ungenutzter
Replikations-Slot entfernt werden muss.

hDiese Seite im Web	URL:https://engineer.company/de/glossary/etl/	engineer.company	70
