Best Practices Datenqualität
Typische Aufgabe in der Datentransformation ist die Integration neuer Daten ohne dabei gewisse Einschränkungen zu verletzen.
Bei herkömmlichen Datenbanksystemen werden Prüfungen z.B. auf Primär- oder Fremdschlüsseleigenschaften meist innerhalb von Einzelsatzverarbeitung (pl/SQL, Cursors) durchgeführt oder aber aus Performancegründen sogar deaktiviert.
Da EXASolution keine Cursors bietet und die Meldung einer Primärschlüsselverletzung innerhalb einer Batchverarbeitung wenig hilfreich ist, sollten hier alle notwendigen Prüfungen noch innerhalb der Staging-Area vorgenommen werden und nur fertig transformierte und geprüfte Daten in das Hauptschema übernommen werden.
Ausgangssituation
- Faktentabelle customers mit PK (customer_no) und FK (city_id)
- Dimensionstabelle cities mit PK (city_id) und Infospalte name
- Stagingtabelle new_customers mit den relevanten Spalten customer_no, last_name, city_name, error_text
Ziel
Entwicklung eines Prozess, der alle Neukunden aus der Stagingtabelle in die Faktentabelle übernimmt und fehlerhafte Einträge mit einer entsprechenden Fehlermeldung in der Stagingtabelle belässt.
Implementation
Für die optimale Nutzung von EXASolution sollte bei Transformationen ein Paradigmenwechsel erfolgen.
Ein typischer Vorgang bei Prüfungen ist:
- Man nehme eine Input-Zeile
- Versuche in die Zieltabelle einfügen
- Auf Exceptions reagieren
- Zur nächsten Zeile übergehen
Mit EXASolution sollen Sie anstatt in "FOR-Schleifen" in Datenmengen denken:
- Fehlermöglichkeiten im Vorfeld identifizieren
- Alle Zeilen auf einen Fehler prüfen und Fehlercodes setzen
- Am Ende des Prozesses alle korrekten Zeilen in Zieltabellen integrieren
- Fehlerhafte Daten evtl. zur Weiterverarbeitung in der Staging Area belassen.
Step 1: Neue Einträge filtern (PK Prüfen)
-
- UPDATE new_customers n
- SET error_text='Customer already exists'
- WHERE EXISTS
- (
- SELECT * FROM customers c
- WHERE c.customer_no = n.customer_no
- )
- ;
Step 2. Prüfung von Fremdschlüssel
-
- UPDATE new_customers n
- SET error_text='Unknown city'
- WHERE error_text IS NULL
- AND NOT EXISTS
- (SELECT * FROM cities
- WHERE cities.name=n.city_name
- );
3. Step: Integration in die Fakttabelle
Nachdem alle bekannten Fehlerfälle abgedeckt sind, können die restlichen Einträge in die Faktentabelle übernommen und aus der Stagingtabelle gelöscht werden:
-
- INSERT INTO customers(customer_no, last_name,
- city_id)
- (SELECT n.customer_no, n.last_name, c.city_id
- FROM new_customers n, cities c
- WHERE n.city_name=c.name
- AND n.error_text IS NULL
- );
- DELETE FROM new_customers WHERE error_text IS NULL;
Aktualisierung der Daten existierender Kunden
In vielen Fällen wird die Staging-Tabelle nicht nur Neueinträge, sondern auch Aktualisierungen existierender Kunden enthalten.
Hierzu könnte dann die PK-Prüfung entfallen sowie das INSERT durch einen geeigneten MERGE ersetzt werden.
-
MERGE INTO customers c USING (SELECT n.customer_no, n.last_name, c.city_id FROM new_customers n, cities c WHERE n.city_name=c.name AND n.error_text IS NULL ) NEW ON (NEW.customer_no=c.customer_no ) WHEN MATCHED THEN UPDATE SET city_id = NEW.city_id, last_name = NEW.last_name WHEN NOT MATCHED THEN INSERT (customer_no, last_name, city_id) VALUES (NEW.customer_no, NEW.last_name, NEW.city_id ) ;
