SQL Beispiele für die ETL Überwachung sowie Datenintegrität und Datenqualität

Die Überwachung des ETL-Fortschritts und -Status ist entscheidend, um sicherzustellen, dass Ihre Daten rechtzeitig, vollständig und korrekt in Ihr Data Warehouse geladen werden. Hier sind die Schritte, um einen Überwachungsprozess für den ETL-Fortschritt und -Status zu erstellen:

1. ETL-Protokolliertabelle erstellen

Zunächst müssen Sie eine Tabelle in Ihrer Datenbank erstellen, die die Protokolle für jeden ETL-Lauf speichert. Diese Tabelle könnte so aussehen:

CREATE TABLE ETL_Log (
  LogID INT PRIMARY KEY IDENTITY(1,1),
  TLProcessName NVARCHAR(255),
  StartDateTime DATETIME,
  EndDateTime DATETIME NULL,
  NumberOfRecordsProcessed INT,
  Status NVARCHAR(50),
  ErrorMessage NVARCHAR(MAX) NULL
);

2. ETL-Prozess aktualisieren

Jedes Mal, wenn ein ETL-Prozess beginnt, sollten Sie einen neuen Eintrag in der ETL_Log-Tabelle erstellen, der den Namen des ETL-Prozesses, das Startdatum und die -zeit und den initialen Status (z. B. „Running“) enthält.

Wenn der ETL-Prozess beendet ist, sollten Sie das Enddatum und die -zeit aktualisieren, die Anzahl der verarbeiteten Datensätze festlegen und den Status auf „Completed“ setzen. Wenn während des ETL-Prozesses ein Fehler auftritt, sollten Sie den Status auf „Failed“ setzen und die Fehlermeldung in der ErrorMessage-Spalte speichern.

3. Überwachungsabfragen erstellen

Mit der ETL_Log-Tabelle können Sie nun verschiedene Überwachungsabfragen erstellen:

• Letzte ETL-Läufe anzeigen:

SELECT TOP 10 *
FROM ETL_Log
ORDER BY StartDateTime DESC;

Nicht abgeschlossene ETL-Läufe anzeigen:

SELECT *
FROM ETL_Log
WHERE EndDateTime IS NULL;

Fehlerhafte ETL-Läufe anzeigen:

SELECT *
FROM ETL_Log
WHERE Status = 'Failed';

4. Benachrichtigungen

Abhängig von Ihren Tools und Ihrem Setup können Sie automatische Benachrichtigungen einrichten, die bei bestimmten Ereignissen ausgelöst werden, z. B. wenn ein ETL-Prozess fehlschlägt oder länger als erwartet dauert.

Erstellen Sie eine gespeicherte Prozedur, die überprüft, ob fehlgeschlagene ETL-Jobs vorhanden sind und eine Benachrichtigung sendet:

CREATE PROCEDURE sp_NotifyOnFailedETL
AS
BEGIN
IF EXISTS (SELECT 1 FROM ETL_Log WHERE Status = 'Failed')
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourMailProfile', -- Ersetzen Sie durch Ihren Mail-Profil-Namen
@recipients = 'youremail@example.com', -- Ersetzen Sie durch Ihre E-Mail-Adresse
@subject = 'ETL Failure Alert',
@body = 'One or more ETL processes have failed. Please check the ETL_Log table for details.';
END
END;

Erstellen Sie einen SQL Server Agent-Job, der diese gespeicherte Prozedur in regelmäßigen Abständen ausführt:

1. Klicken Sie mit der rechten Maustaste auf „Jobs“ unter „SQL Server Agent“ und wählen Sie „Neuer Job“.
2. Geben Sie die Details für den Job ein.
3. Fügen Sie unter „Schritte“ einen neuen Schritt hinzu. Wählen Sie als Typ „T-SQL“ und geben Sie den Befehl EXEC sp_NotifyOnFailedETL ein.
4. Fügen Sie einen Zeitplan hinzu, um festzulegen, wie oft der Job ausgeführt werden soll, z. B. alle 15 Minuten.

Da die gespeicherte Prozedur sp_send_dbmail nur aufruft, wenn tatsächlich fehlgeschlagene ETL-Jobs vorhanden sind, erhalten Sie nur dann eine E-Mail, wenn etwas schief gelaufen ist.

Stellen Sie sicher, dass SQL Server Database Mail korrekt konfiguriert ist und dass Sie die richtigen Parameter für sp_send_dbmail (wie Mail-Profil und Empfänger-E-Mail) festlegen.

Um die ETL_Log-Tabelle zu befüllen, basierend auf den Ergebnissen Ihrer ETL-Tasks (z.B. Merge-Befehle), können Sie die folgende Strategie verwenden:

5. Beginn des ETL-Prozesses protokollieren:

Zu Beginn Ihres ETL-Prozesses fügen Sie einen Eintrag in die ETL_Log-Tabelle ein, um den Startzeitpunkt und den Namen des ETL-Prozesses zu protokollieren. Sie setzen den Status zunächst auf „Running“.

INSERT INTO ETL_Log (ETLProcessName, StartDateTime, Status)
VALUES ('YourETLProcessName', GETDATE(), 'Running');

Speichern Sie die generierte LogID für spätere Aktualisierungen. In T-SQL können Sie dies mit SCOPE_IDENTITY() tun:

DECLARE @CurrentLogID INT;
INSERT INTO ETL_Log (ETLProcessName, StartDateTime, Status)
VALUES ('YourETLProcessName', GETDATE(), 'Running');
SET @CurrentLogID = SCOPE_IDENTITY();

Führen Sie Ihren ETL-Task aus:

Hier führen Sie Ihren Merge-Befehl oder andere ETL-Operationen aus.

3. ETL-Prozess abschließen:

Am Ende des ETL-Prozesses aktualisieren Sie den Eintrag in der ETL_Log-Tabelle mit dem Enddatum und dem Status „Completed“. Sie können auch die Anzahl der verarbeiteten Datensätze speichern, wenn dies relevant ist.

UPDATE ETL_Log
SET EndDateTime = GETDATE(), Status = 'Completed', NumberOfRecordsProcessed = @YourProcessedRecordsCount
WHERE LogID = @CurrentLogID;

BEGIN TRY
-- Ihr ETL-Code (z.B. Merge-Befehl)

-- ETL-Prozess abschließen (wie oben beschrieben)
END TRY
BEGIN CATCH
UPDATE ETL_Log
SET EndDateTime = GETDATE(), Status = 'Failed', ErrorMessage = ERROR_MESSAGE()
WHERE LogID = @CurrentLogID;
END CATCH;

DECLARE @YourProcessedRecordsCount INT;

-- Führen Sie Ihren ETL-Code aus (z.B. ein MERGE-Befehl)
MERGE TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID
-- ...
-- (Der Rest Ihres MERGE-Codes)

-- Sofort nach dem MERGE-Befehl (oder INSERT, UPDATE, DELETE):
SET @YourProcessedRecordsCount = @@ROWCOUNT;

Nachdem Sie Ihren ETL-Code ausgeführt haben, enthält die Variable @YourProcessedRecordsCount die Anzahl der durch den letzten DML-Befehl betroffenen Zeilen. Sie können diese Zahl dann in Ihrer ETL_Log-Tabelle speichern, wie zuvor beschrieben.

Beachten Sie, dass Sie @@ROWCOUNT sofort nach Ihrem DML-Befehl verwenden sollten, bevor Sie irgendeinen anderen Code ausführen, da sonst der Wert von @@ROWCOUNT verändert wird.

6. Dashboard oder Bericht

Für die regelmäßige Überwachung können Sie ein Dashboard oder einen Bericht erstellen, der die wichtigsten Metriken aus der ETL_Log-Tabelle anzeigt. Dies kann in Tools wie Power BI, SSRS oder einem anderen Reporting-Tool erfolgen.

Mit diesen Schritten haben Sie eine solide Grundlage für die Überwachung Ihrer ETL-Prozesse. Es ist wichtig, regelmäßig die ETL_Log-Tabelle zu überprüfen, um sicherzustellen, dass Ihre Daten korrekt geladen werden und um Probleme so schnell wie möglich zu identifizieren und zu beheben.

Überwachung der Datenintegrität und Datenqualität

Suchen nach NULL-Werten in Schlüsselfeldern:

SELECT *
FROM YourTableName
WHERE YourKeyColumn IS NULL;

Ermitteln von Duplikaten in einer Tabelle:

SELECT YourColumn1, YourColumn2, COUNT(*)
FROM YourTableName
GROUP BY YourColumn1, YourColumn2
HAVING COUNT(*) > 1;

Prüfen auf inkonsistente Datumsformate (z. B. zukünftige Daten oder unplausible Jahreszahlen):

SELECT *
FROM YourTableName
WHERE YourDateColumn > GETDATE() OR YEAR(YourDateColumn) < 1900;

Prüfen auf inkonsistente Datenlängen (z. B. für Textfelder):

SELECT *
FROM YourTableName
WHERE LEN(YourTextField) > YourExpectedMaxLength;

Verwenden von CHECK-Constraints, um Datenintegrität zu gewährleisten: Sie können Bedingungen für Daten in Ihren Tabellen festlegen, z. B. dass ein Alter immer größer als 0 sein muss.

ALTER TABLE YourTableName
ADD CONSTRAINT CK_YourTableName_Age
CHECK (Age > 0);

Verwenden von FOREIGN KEY-Constraints: Dies stellt sicher, dass die Beziehungen zwischen den Tabellen erhalten bleiben und keine ungültigen Daten in Bezugstabellen eingefügt oder gelöscht werden.

ALTER TABLE YourChildTableName
ADD FOREIGN KEY (YourForeignKeyColumn)
REFERENCES YourParentTableName(YourPrimaryKeyColumn);

Ermitteln von Daten außerhalb erwarteter Bereiche: Dies kann helfen, Ausreißer oder falsche Daten zu identifizieren.

SELECT *
FROM YourTableName
WHERE YourValueColumn < YourMinValue OR YourValueColumn > YourMaxValue;

Verwenden von UNIQUE-Constraints: Dies stellt sicher, dass Werte in einer Spalte (oder einer Gruppe von Spalten) eindeutig sind.

ALTER TABLE YourTableName
ADD CONSTRAINT UQ_YourTableName_YourColumn
UNIQUE (YourColumn);

• Datenvalidierung durch Verwendung von Triggern: Sie können Triggers verwenden, um Daten vor dem Einfügen oder Aktualisieren zu validieren und Aktionen basierend auf bestimmten Bedingungen durchzuführen.
• Ermitteln von Datensätzen, die von Referenzdaten abweichen: Wenn Sie z. B. eine Referenztabelle mit gültigen Postleitzahlen haben, können Sie prüfen, ob alle Postleitzahlen in Ihrer Haupttabelle in dieser Referenztabelle vorhanden sind.

SELECT DISTINCT YourZipCodeColumn
FROM YourTableName
WHERE YourZipCodeColumn NOT IN (SELECT ZipCode FROM YourReferenceTable);

Diese Abfragen und Techniken sind nur ein Ausgangspunkt. Datenintegrität und -qualität erfordern eine ständige Überwachung und regelmäßige Überprüfungen, insbesondere in großen oder sich schnell ändernden Datenbankumgebungen. Sie können auch Drittanbieter-Tools und -Lösungen in Betracht ziehen, die speziell entwickelt wurden, um die Datenqualität in Datenbanken zu überwachen und sicherzustellen.

Ein DWH vereint oft Daten aus verschiedenen Quellsystemen, was zusätzliche Komplexität und potenzielle Inkonsistenzen mit sich bringt. Hier sind einige weitere SQL-Abfragen und Techniken, die speziell für die Überwachung und Sicherstellung der Datenqualität in einem DWH nützlich sein können:

Ermitteln von fehlenden Dimensionseinträgen in Faktentabellen:

SELECT DISTINCT FactYourDimensionKey
FROM FactTable
WHERE FactYourDimensionKey NOT IN (SELECT DimensionKey FROM YourDimensionTable);

Prüfen auf langsam ändernde Dimensionen (SCD): Stellen Sie sicher, dass historische Daten korrekt versioniert oder kategorisiert sind.

SELECT DimensionKey, COUNT(*)
FROM YourDimensionTable
GROUP BY DimensionKey
HAVING COUNT(*) > 1;

Ermitteln von Fakten ohne korrespondierenden Zeiteintrag (z. B. fehlende Datumsdimension):

SELECT DISTINCT FactDateKey
FROM FactTable
WHERE FactDateKey NOT IN (SELECT DateKey FROM DateDimension);

Ermitteln von Faktentabellen mit NULL-Werten in Metrikspalten: Dies kann auf fehlende Aggregationen oder Ladeprobleme hinweisen.

SELECT *
FROM FactTable
WHERE YourMetricColumn IS NULL;

• Prüfen auf Inkonsistenzen zwischen Quellsystemen und dem DWH: Sie könnten Abfragen erstellen, die Daten im DWH mit den ursprünglichen Quellsystemen vergleichen, um Diskrepanzen festzustellen.
• Ermitteln von Datensätzen mit älterem Änderungsdatum als im DWH: Dies kann auf fehlende oder verspätete ETL-Läufe hinweisen.

SELECT *
FROM StagingTable
WHERE LastModifiedDate > (SELECT MAX(LastLoadDate) FROM ETLLogTable);

• Überwachung des ETL-Fortschritts und -Status: Erstellen Sie eine Tabelle, die ETL-Laufzeiten, Status und mögliche Fehler protokolliert. Sie können regelmäßig Abfragen ausführen, um den aktuellen Status und mögliche Probleme zu überwachen.
• Validieren von Business Rules: Wenn Ihr DWH Geschäftsregeln implementiert (z. B. ein bestimmter Umsatz kann nicht negativ sein), können Sie Abfragen erstellen, um sicherzustellen, dass diese Regeln eingehalten werden.

SELECT *
FROM FactSales
WHERE TotalRevenue < 0;

Prüfen auf Orphans in der Faktentabelle: Insbesondere in großen DWHs können „verwaiste“ Datensätze auftreten, bei denen z. B. eine Kunden-ID in der Faktentabelle nicht in der Dimensionstabelle vorhanden ist.

SELECT DISTINCT CustomerID
FROM FactSales
WHERE CustomerID NOT IN (SELECT CustomerID FROM DimCustomer);

• Überwachung der Datenqualität über Zeit: Erstellen Sie Dashboards oder Berichte, die Metriken zur Datenqualität über die Zeit anzeigen. Dies kann helfen, Trends oder wiederkehrende Probleme zu identifizieren.

Die Überwachung und Sicherstellung der Datenqualität in einem DWH ist ein fortlaufender Prozess. Es erfordert eine Kombination aus automatisierten Prozessen, regelmäßigen Überprüfungen und guten Kommunikationskanälen zwischen den Datenbankadministratoren, ETL-Entwicklern und Business-Analysten.

Ähnliche Beiträge:

Performance Überwachung bei MS SQL Server über System Tables SQL Server Sequenzen: Nutzung und Beispiele Datenqualitätsfunktionen – Optimierung der Datenqualität im DWH SQL Lernen für Anfänger Navigieren in SQL-Joins: effizientes Abrufen von Daten Data Vault: Die moderne Architektur für agile Data Warehousing