Schlechte Daten kündigen sich nicht an. Ein Alter von 200 Jahren, eine doppelte Kundennummer, ein Ländercode, den es nicht gibt — im Quellsystem fällt das niemandem auf. Erst wenn der ETL-Lauf die Sätze in die strikt modellierte Zielschicht schieben will, bricht der Load ab: an einem CHECK, an einem UNIQUE-Index, an einem Fremdschlüssel. Datenqualität mit SQL prüfen heißt, genau diese Sätze schon vorher zu finden, nach Schweregrad zu klassifizieren und gezielt auszusortieren — ohne Spezial-Tool, mit einer Handvoll generischer SQL-Routinen.
Das Wichtigste vorab:
- Drei generische Prüfroutinen — eine WHERE-Klausel, eine Eindeutigkeits-Prüfung und eine Fremdschlüssel-Prüfung — decken einen großen Teil der typischen Datenfehler ab.
- Alle drei schreiben in eine gemeinsame Fehlertabelle: je Verstoß eine Zeile, mit fachlichem Schlüssel, beanstandetem Wert und Klartext-Meldung.
- Ein Schweregrad (Fehler / Warnung / Information) steuert ein Quality Gate: nur fehlerfreie Datensätze fließen weiter.
- Alles in reinem PL/pgSQL — dasselbe Grundprinzip, das auch spezialisierte Datenqualitäts-Tools nutzen, hier abhängigkeitsfrei zum Selberbauen.
Voraussetzung: Postgres als Beispiel-Engine und eine zentrale Staging-Schicht, in die zuerst roh geladen wird. Gegen sie laufen die Prüfungen mengen-basiert — der Gegenentwurf zum tool-zentrischen Paket, das pro Tabelle Extraktion, Transformation und Laden vermischt und die Qualitätsprüfung über den ganzen Prozess verstreut (mehr dazu im Architektur-Artikel der Serie).
Datenqualität mit SQL prüfen: die Dimensionen dahinter
Über Datenqualität gibt es viel zu lesen und wenig zum Anfassen. Die Literatur ist sich seit Jahrzehnten einig, was gute Daten ausmacht: Wang & Strong haben 1996 in „Beyond Accuracy“ fünfzehn Dimensionen beschrieben, die DAMA-Gruppe hat sie 2013 auf sechs Kern-Dimensionen eingedampft (Vollständigkeit, Eindeutigkeit, Aktualität, Validität, Korrektheit, Konsistenz), und ISO/IEC 25012 hat das Ganze normiert. Was die Literatur kaum liefert, ist das Wie — und wenn doch, dann meist an ein bestimmtes Werkzeug gebunden.
Dabei ist der konkrete Hebel naheliegend: Wer Datenqualität mit SQL prüfen will, formuliert die Prüfung als Abfrage und bekommt am Ende eine Tabelle, in der die schlechten Datensätze stehen. Drei Routinen reichen für einen großen Teil der Praxis, und jede deckt sauber eine der etablierten Dimensionen ab:
| Prüfroutine | Was sie findet | Dimension |
|---|---|---|
| WHERE-Klausel auf einer Tabelle | Werte außerhalb erlaubter Bereiche, fehlende Pflichtwerte | Validität (+ Vollständigkeit) |
| Eindeutigkeit / maximale Vorkommen | Duplikate, zu häufige Schlüssel | Eindeutigkeit |
| Fremdschlüssel gegen eine Referenz | verwaiste Sätze ohne Master | Konsistenz / Integrität |
Damit decken die drei Routinen vier der sechs Dimensionen ab; Aktualität und Korrektheit — im Sinne der Übereinstimmung mit der echten Welt — liegen außerhalb ihrer Reichweite und brauchen andere Mittel.
Ehrlich eingeordnet: Neu ist dieser Ansatz nicht. Werkzeuge wie Soda Core, dbt-Tests oder Great Expectations machen unter der Haube nichts anderes — sie generieren SQL, sammeln die Treffer und hängen einen Schweregrad daran; drumherum bieten sie freilich mehr, von Monitoring über Profiling bis Lineage. Der Mehrwert des Eigenbaus ist nicht Originalität, sondern Transparenz: jede Zeile ist lesbar, nichts ist an ein Produkt gebunden, und es läuft überall dort, wo man kein zusätzliches Tool installieren darf.
Der gemeinsame Nenner: eine Fehlertabelle
Das Rückgrat ist nicht die Prüfung, sondern ihr Ergebnis. Alle drei Routinen schreiben in dieselbe Tabelle — eine Zeile pro gefundenem Verstoß, so abgelegt, dass man den Quell-Datensatz später eindeutig wiederfindet:
1: CREATE TABLE dq.error
2: (
3: id bigint NOT NULL GENERATED ALWAYS AS IDENTITY
4: ,schema_name text NOT NULL
5: ,table_name text NOT NULL
6: ,id1_column text
7: ,id1_value text
8: ,id2_column text
9: ,id2_value text
10: ,id3_column text
11: ,id3_value text
12: ,error_column text
13: ,error_value text
14: ,severity char(1) NOT NULL
15: ,message text NOT NULL
16: ,created_on timestamptz NOT NULL DEFAULT now()
17: ,CONSTRAINT pk_error PRIMARY KEY (id)
18: ,CONSTRAINT ck_error_severity CHECK (severity IN ('E', 'W', 'I'))
19: );
Die Spaltenpaare id1_column/id1_value bis id3_column/id3_value halten den fachlichen Schlüssel des betroffenen Datensatzes: in id1_column steht der Spaltenname (etwa customer_id), in id1_value der Wert (etwa 4711). Damit lässt sich der schlechte Satz später rekonstruieren — WHERE customer_id = 4711. Drei Paare reichen für zusammengesetzte Schlüssel; in der Praxis genügt fast immer eines. Daneben protokolliert jede Zeile die geprüfte Spalte (error_column), den beanstandeten Wert (error_value), die Meldung im Klartext und den Schweregrad (severity: Error, Warning, Information).
Die Konfiguration: eine Regel pro Zeile
Die Prüfregeln werden nicht in Code gegossen, sondern in eine Tabelle geschrieben. Eine Zeile = eine Regel. So kommen neue Prüfungen ohne Deployment dazu, und die Fachseite kann mitlesen, was geprüft wird:
1: CREATE TABLE dq.check_rule
2: (
3: id bigint NOT NULL GENERATED ALWAYS AS IDENTITY
4: ,check_type text NOT NULL -- 'constraint' | 'unique' | 'lookup'
5: ,schema_name text NOT NULL
6: ,table_name text NOT NULL
7: ,id1_column text NOT NULL -- fachlicher Schluessel (bis zu drei)
8: ,id2_column text
9: ,id3_column text
10: ,check_column text NOT NULL -- gepruefte Spalte
11: ,where_clause text -- 'constraint': das "schlecht"-Praedikat
12: ,max_occurrence int NOT NULL DEFAULT 1 -- 'unique': erlaubte Vorkommen
13: ,ref_schema text -- 'lookup': Referenztabelle
14: ,ref_table text
15: ,ref_column text
16: ,severity char(1) NOT NULL DEFAULT 'E'
17: ,message text NOT NULL
18: ,active boolean NOT NULL DEFAULT true
19: ,CONSTRAINT pk_check_rule PRIMARY KEY (id)
20: );
check_type entscheidet, welche der drei Routinen für die Zeile gebaut wird. Je nach Typ sind unterschiedliche Spalten relevant: where_clause für die WHERE-Prüfung, max_occurrence für die Eindeutigkeit, ref_schema/ref_table/ref_column für den Fremdschlüssel. severity und message hängen an jeder Regel — so trägt jeder Befund seinen Schweregrad und seinen Klartext schon aus der Konfiguration.
Routine 1: die WHERE-Klausel
Die einfachste und gleichzeitig mächtigste Prüfung: eine Bedingung, die schlechte Zeilen beschreibt, an eine Tabelle gehängt. Alles, was die Bedingung trifft, ist ein Befund. Für die Regel „Alter muss zwischen 0 und 120 liegen“ erzeugt die Routine dieses Statement:
1: INSERT INTO dq.error (schema_name, table_name, id1_column, id1_value,
2: error_column, error_value, severity, message)
3: SELECT
4: 'staging'
5: ,'customer'
6: ,'customer_id'
7: ,T01.customer_id::text
8: ,'age'
9: ,T01.age::text
10: ,'E'
11: ,'Alter ausserhalb 0..120'
12: FROM
13: staging.customer T01
14: WHERE
15: age < 0 OR age > 120;
Das Prädikat in Zeile 15 kommt unverändert aus where_clause. Damit deckt diese eine Routine eine ganze Familie ab: Wertebereiche (age < 0 OR age > 120), Pflichtfelder (email IS NULL), Formate (length(zip) <> 5), Plausibilität (order_date > current_date). Zwei Dimensionen auf einmal — Validität und Vollständigkeit.
Routine 2: Eindeutigkeit und Kardinalität
Doppelte Schlüssel sind der Klassiker. Die Prüfung ist ein GROUP BY mit HAVING auf die Anzahl:
1: SELECT
2: customer_id
3: FROM
4: staging.customer
5: GROUP BY
6: customer_id
7: HAVING
8: count(*) > 1;
Der eigentliche Trick steckt in der > 1: Sie kommt aus max_occurrence. Statt auf Eindeutigkeit zu prüfen, prüft man auf eine maximale Anzahl — > 1 für echte Eindeutigkeit, > 3 etwa, wenn ein Schlüssel höchstens dreimal vorkommen darf. Die Routine joint die gefundenen Schlüssel anschließend zurück auf die Tabelle und protokolliert jedes Vorkommen (nicht nur das erste), damit in der Fehlertabelle jeder betroffene Satz steht.
Geprüft wird hier genau der fachliche Schlüssel, der den Datensatz identifiziert — dieselbe Spalte, die in der Fehlertabelle als id1_column landet. Das ist kein Zufall: Diese Prüfung spiegelt direkt den UNIQUE-Constraint, den die strikte Zielschicht auf dem fachlichen Schlüssel trägt. Was dort eindeutig sein muss, fängt man hier vorab in der Quelle ab.
Routine 3: referenzielle Integrität
Ein Fremdschlüssel ins Leere — ein country_code, zu dem es keinen Eintrag in der Stammdaten-Tabelle gibt. Generisch ausgedrückt: alle Sätze der Kind-Tabelle, die per LEFT JOIN keinen Partner in der Master-Tabelle finden:
1: SELECT
2: T01.customer_id
3: FROM
4: staging.customer T01
5: LEFT JOIN staging.country T02
6: ON
7: T01.country_code = T02.country_code
8: WHERE
9: T01.country_code IS NOT NULL
10: AND T02.country_code IS NULL;
Kind-Tabelle, Kind-Spalte (check_column) und Master (ref_schema/ref_table/ref_column) kommen aus der Konfiguration — damit ist dieselbe Routine für jede Master-Kind-Beziehung verwendbar. Die IS NOT NULL-Bedingung in Zeile 9 trennt bewusst „unbekannter Wert“ (Fehler) von „kein Wert angegeben“ (das ist Sache der WHERE-Routine).
Der Runner: dynamisches SQL — sicher
Die drei Statements oben sind hartkodiert. Generisch werden sie, indem eine Funktion sie zur Laufzeit aus der Konfiguration zusammensetzt. In PL/pgSQL ist format() das richtige Werkzeug — und der Punkt, an dem man aufpassen muss. Bezeichner gehören mit %I eingesetzt, Literale mit %L; beides quotet Postgres korrekt und schließt SQL-Injection über Tabellen- und Spaltennamen aus. So sieht der Zweig für die WHERE-Prüfung aus:
1: l_sql := format($sql$INSERT INTO dq.error
2: (
3: schema_name
4: ,table_name
5: ,id1_column
6: ,id1_value
7: ,error_column
8: ,error_value
9: ,severity
10: ,message
11: )
12: SELECT
13: %1$L
14: ,%2$L
15: ,%3$L
16: ,T01.%4$I::text
17: ,%5$L
18: ,T01.%6$I::text
19: ,%7$L
20: ,%8$L
21: FROM
22: %9$I.%10$I T01
23: WHERE
24: %11$s
25: $sql$
26: ,l_rule.schema_name
27: ,l_rule.table_name
28: ,l_rule.id1_column
29: ,l_rule.id1_column
30: ,l_rule.check_column
31: ,l_rule.check_column
32: ,l_rule.severity
33: ,l_rule.message
34: ,l_rule.schema_name
35: ,l_rule.table_name
36: ,l_rule.where_clause
37: );
38: EXECUTE l_sql;
Die Schema- und Spaltennamen (Zeile 16, 18, 22) laufen über %I, die festen Werte über %L. Die Eindeutigkeits- und Fremdschlüssel-Zweige sind nach demselben Muster gebaut — nur das innere Statement unterscheidet sich.
Eine Stelle bleibt bewusst roh: das Prädikat in Zeile 24 wird mit %s eingesetzt, also als unverändertes SQL. Das muss so sein — where_clause ist ein SQL-Ausdruck, kein Wert. Damit ist die dq.check_rule-Tabelle die Vertrauensgrenze des Systems: Wer dort schreiben darf, kann beliebiges SQL ausführen lassen. In der Praxis ist das unkritisch, weil die Konfiguration administrativ gepflegt wird und nie aus Benutzereingaben gefüllt wird — aber man muss es wissen und absichern. Bezeichner sind durch %I dagegen wasserdicht: Ein Spaltenname wie age"; DROP TABLE staging.customer; -- aus der Konfiguration wird zu einem (nicht existierenden) gequoteten Bezeichner und löst einen sauberen Fehler aus, statt die Tabelle zu löschen.
Über alle aktiven Regeln einer Tabelle läuft der Runner in einer Schleife, führt pro Regel das gebaute Statement aus und schreibt am Ende die Schweregrad-Zähler zurück.
Zur Laufzeit muss man ehrlich sein: Jede Regel ist im Kern ein Full-Table-Scan über die Quelltabelle, und für ein frei konfiguriertes Prädikat gibt es keinen passenden Index — bei vielen Regeln auf großen Tabellen summiert sich das. Vertretbar ist es, weil die Prüfung im ohnehin eingeplanten Staging-Fenster gegen die frisch geladene Menge läuft, nicht gegen das Produktivsystem. Bei sehr großen Tabellen lohnt es, die Prüfung auf die im aktuellen Lauf geladenen Partitionen oder Batches zu begrenzen, statt jedes Mal alles zu scannen.
Schweregrad und das Quality Gate
Bis hierher steht in dq.error, was falsch ist. Die Steuerung des ETL-Prozesses braucht aber eine Aussage pro Datensatz: darf er weiter oder nicht? Dafür bekommt die Quelltabelle drei Zähler-Spalten — sys_error, sys_warning, sys_info — und der Runner füllt sie nach jedem Lauf: pro fachlichem Schlüssel die Anzahl der Befunde je Schweregrad.
Das Zurückschreiben läuft hier über den einspaltigen fachlichen Schlüssel (id1) — den mit Abstand häufigsten Fall. Zusammengesetzte Schlüssel über mehrere Spalten zu gaten, ginge analog, ist aber Stoff für einen eigenen Spoke; die Schlüsselteile id1–id3 hält die Fehlertabelle dafür bereits vor.
Damit wird das Gate zu einer trivialen WHERE-Bedingung:
1: SELECT
2: customer_id
3: ,country_code
4: ,email
5: ,age
6: FROM
7: staging.customer
8: WHERE
9: sys_error = 0;
Nur Sätze ohne Fehler fließen in die nächste Ebene. Warnungen und Informationen blockieren nicht — sie sind protokolliert, aber kein Hinderungsgrund. Das ist der ganze Witz des Schweregrads: Er trennt „darf nicht weiter“ von „sollte man sich ansehen“. Im Demo-Datensatz mit sieben Zeilen passieren genau zwei das Gate — die saubere Zeile und die Zeile mit der fehlenden E-Mail (nur Warnung). Alles mit age außerhalb des Bereichs, dem unbekannten Land und der doppelten Kundennummer bleibt liegen, sauber protokolliert:
| severity | id1_value | error_column | error_value | message |
|---|---|---|---|---|
| E | 1 | customer_id | 1 | customer_id nicht eindeutig |
| E | 1 | customer_id | 1 | customer_id nicht eindeutig |
| E | 2 | age | 200 | Alter ausserhalb 0..120 |
| E | 3 | country_code | XX | Unbekannter country_code |
| E | 5 | age | -3 | Alter ausserhalb 0..120 |
| W | 4 | E-Mail fehlt |
Warum nicht einfach Constraints?
Die naheliegende Frage: Wenn die Zielschicht ohnehin CHECK-, UNIQUE– und Fremdschlüssel-Constraints hat — wozu der Aufwand? Die Antwort steckt genau darin. Die weiterführenden Tabellen sind streng modelliert; das ist gewollt. Aber ein Constraint kennt nur zwei Ausgänge: Der Satz passt, oder der ganze Load bricht ab. Beim Laden tausender Zeilen ist „bricht ab“ die schlechteste aller Optionen — eine einzige schlechte Zeile stoppt den kompletten Prozess, und man weiß nicht einmal, welche.
Genau deshalb prüft man in der Quelle vorab: Man identifiziert alle Sätze, die an den Ziel-Constraints scheitern würden, klassifiziert sie nach Schweregrad und lässt nur die sauberen passieren. Das Framework baut die Constraints nicht nach — es ist das transparente, auditierbare Vorsieb vor einer bewusst strikten Zielschicht. Statt eines abgebrochenen Loads bekommt man eine Tabelle mit Befunden und einen Prozess, der mit den guten Daten weiterläuft.
Tatsächlich sind die drei Routinen exakt das Vorsieb für die drei Constraint-Typen, die das Ziel erzwingt:
| Constraint am Ziel | Prüfroutine in der Quelle |
|---|---|
CHECK | WHERE-Klausel (Routine 1) |
UNIQUE | Eindeutigkeit (Routine 2) |
FOREIGN KEY | Fremdschlüssel (Routine 3) |
Was am Ziel als Constraint erzwungen wird, prüft man in der Quelle vorab — auf demselben fachlichen Schlüssel, der am Ziel den UNIQUE-Constraint trägt. Der Unterschied ist nicht was geprüft wird, sondern wie mit dem Verstoß umgegangen wird: melden und klassifizieren statt den Load abbrechen.
Was dieser Ansatz nicht abdeckt
Drei Routinen sind viel, aber nicht alles — und es lohnt, ehrlich zu sein, wo die Grenze liegt:
- Reparieren gehört bewusst nicht dazu. Das Framework findet und macht transparent; es korrigiert nichts. Das ist eine Entscheidung, kein Versäumnis — Transparenz zuerst.
- Feldübergreifende Geschäftsregeln („Rabatt nur, wenn Status = aktiv“) lassen sich teils als WHERE-Klausel ausdrücken, teils nicht.
- Zeitliche Konsistenz, Genauigkeit gegen eine externe Wahrheit, komplexe Muster (jenseits einfacher Längen- und Format-Checks) sind eigene Themen.
Und wer doch lieber ein fertiges Werkzeug nimmt: Soda Core, dbt-Tests und Great Expectations decken denselben Bereich ab, kostenfrei und gut gepflegt. Der SQL-Eigenbau lohnt, wenn man Transparenz, null Abhängigkeiten und volle Kontrolle über jede Zeile will — oder schlicht in einer Umgebung arbeitet, in der kein zusätzliches Tool installiert werden darf.
Postgres-Brücke nach SQL Server
Das Muster ist nicht Postgres-spezifisch. In SQL Server übernimmt sp_executesql die Rolle von EXECUTE format(); Bezeichner schützt man dort mit QUOTENAME() statt mit %I. Die Struktur bleibt identisch: eine Konfigurationstabelle, ein Cursor (oder eine Schleife) über die Regeln, ein dynamisch gebautes INSERT … SELECT … WHERE je Regel, eine gemeinsame Fehlertabelle und Schweregrad-Spalten in der Quelle als Gate. Wer von SQL Server kommt, überträgt den Ansatz eins zu eins.
FAQ
Nein. Datenqualität mit SQL prüfen gelingt mit drei generischen Routinen — Wertebereichs-, Eindeutigkeits- und Fremdschlüssel-Prüfung —, die einen großen Teil der typischen Fehler abdecken. Tools wie Soda oder dbt nehmen einem Arbeit ab, machen im Kern aber dasselbe: SQL bauen, Treffer sammeln, Schweregrad anhängen.
Nur, wenn man Eingaben roh zusammenklebt. Mit format() und %I (Bezeichner) bzw. %L (Literale) quotet Postgres korrekt und Injection über Tabellen-/Spaltennamen ist ausgeschlossen. Das frei konfigurierbare Prädikat (where_clause) ist bewusst Roh-SQL — deshalb ist die Konfigurationstabelle die Vertrauensgrenze und gehört administrativ geschützt.
Weil ein Constraint den ganzen Load abbricht, statt schlechte Sätze einzeln zu melden. Die Prüfung in der Quelle identifiziert vorab alle Sätze, die am Ziel scheitern würden, klassifiziert sie und lässt nur saubere passieren — auditierbar statt abgebrochen.
Über die id*_column/id*_value-Paare: Sie halten Spaltenname und Wert des fachlichen Schlüssels. WHERE <id1_column> = <id1_value> führt zurück zum Quell-Satz. Für zusammengesetzte Schlüssel stehen bis zu drei Paare bereit.
Ja. sp_executesql ersetzt EXECUTE format(), QUOTENAME() ersetzt %I. Konfigurationstabelle, Fehlertabelle, Schweregrad-Gate und die drei Routinen bleiben strukturgleich.
Verwandte Artikel
- Datenqualität in einem ETL-Prozess — der übergeordnete Blick: technische und fachliche Fehler erkennen, bevor sie das Zielsystem erreichen.
- Design Pattern // Architektur eines ETL-Prozesses — wie sich schlechte Daten über geschichtete Staging-Ebenen sauber isolieren lassen.
- Datenqualität // Grundlagen der Typ-Konvertierung mit T-SQL — die feldweise Validitäts-Prüfung beim Konvertieren, der „T“-Baustein dieser Serie.