Kürzlich diskutierte ich mit einem Kollegen darüber, wie man Unit-Tests in SQL durchführt, und mir wurde klar, dass ich, obwohl ich noch am Anfang meiner Datenkarriere stehe, bereits einige Male auf dieses Problem gestoßen bin und jedes Mal einen anderen Ansatz hatte löse es. Ich dachte, es könnte nützlich sein, meine Hacks zu teilen, damit ich festigen kann, was ich gelernt habe, und um auch von Ihnen als Leser zu erfahren , was Sie getan haben und wie es gelaufen ist. Sollen wir?

Aber warum Unit-Test SQL?

Es ist wichtig klarzustellen, dass es hier um das Testen der SQL-Logik und nicht um das Testen von Daten geht . Durch das Testen von Daten wird sichergestellt, dass Annahmen für Schemaeigenschaften wie Nullbarkeit und Eindeutigkeit tatsächlich für die vorliegenden Werte gelten. Ein herkömmliches RDBMS würde dies mit Tabelleneinschränkungen sicherstellen, aber wir haben diese Funktion in Data Warehouses nicht, also müssen wir tatsächlich Vor- und Nachbedingungen für den Datensatz lesen und überprüfen.

Unit-Tests von SQL sind dasselbe wie Unit-Tests von Anwendungscode: Stellen Sie sicher, dass eine Logik die erwartete Ausgabe für einen bestimmten Satz von Eingaben erzeugt. Während Best Practices vorschreiben, dass wir fast den gesamten Anwendungscode, sei es C++ oder Python, einem Unit-Test unterziehen, wird dies bei SQL selten versucht. Warum?

Die Antwort lautet für mich, dass das Schreiben von SQL nicht der schwierigste Teil ist . Meistens sind Datentransformationen trivial und repetitiv, und die Schwierigkeit liegt eher darin, Daten angemessen zu modellieren, als darin, Teile von A nach B zu verschieben. Wenn jedoch Geschäftslogik innerhalb von Transformationen erforderlich ist, kann es am Ende zu einer notwendigen Komplexität kommen, die schwierig ist auseinanderbrechen – wie ein komplexer CASE-WHEN-Ausdruck zum Generieren einer Spalte, mit Eingaben, die von einem entmutigenden siebenfachen JOIN stammen, und einer mehrzeiligen WHERE-Klausel, die eine Wahrheitstabelle erfordert, um zu verstehen, was vor sich geht ...

Meiner Erfahrung nach beruhte die Sicherstellung, dass die Logik korrekt ist, nur auf manuellen Tests und einer Freigabe durch den Client, was 1) fragil und 2) fast kriminell ist , wenn man bedenkt, wie gut SQL für Unit-Tests geeignet ist! Während Apps alle Arten von Gerüsten erfordern, um den veränderlichen Zustand einzuschränken und die Reproduzierbarkeit in Tests sicherzustellen, ist SQL rein und explizit. Warum also nicht dieselbe Infrastruktur verwenden, um zu überprüfen, ob die Logik wie erwartet funktioniert?

Erster Versuch: Analytik

Mein erster Versuch, SQL-Skripte in Einheiten zu testen, war, als ich bei Google arbeitete und interne Technologien wie Dremel und Powerdrill verwendete (Sie werden feststellen, dass ihre Projekte Namen hatten, die sich auf die Arbeit mit Protokollen bezogen ).

Wir bauten eine Analysepipeline für eine neue App auf und verarbeiteten Benutzerprotokolle, um für Produktmanager wichtige Kennzahlen zu generieren, wie etwa aktive Benutzer pro Land oder Kohorte. Eine repräsentative Abfrage wäre:

CREATE TABLE _7dau_by_country_platform AS
SELECT
  day,
  country,
  platform,
  COUNT(DISTINCT user_id) AS active_users
FROM UNNEST(dates) day
JOIN app_logs ON DATE(ts) BETWEEN DATE_SUB(day, INTERVAL 7 DAY) AND day
GROUP BY 1, 2, 3

Hacken eines Testframeworks

Wir wollten es besser machen und sicherstellen, dass unsere Skripte vertrauenswürdig sind, also haben wir unser eigenes Test-Framework erstellt:

1. Jede Quelltabelle hätte eine synthetische CSV-Datei mit zahlreichen Benutzern und ausgefüllten Spalten. Für jeden neuen Testfall würden wir Zeilen zu den entsprechenden Quelltabellen hinzufügen.
2. Beim Ausführen eines Tests wurde der gesamte Datensatz in temporäre Tabellen im Data Warehouse hochgeladen.
3. Für jedes Skript wurden die Quellen textlich durch die temporäre Tabelle ersetzt und das Skript ausgeführt.
4. Für jedes Skript haben wir eine erwartete CSV-Ausgabe erstellt und diese mit der tatsächlichen Ausgabe verglichen und alle Unterschiede gemeldet.

Das Erstellen der CSVs war zeitaufwändig, da wir daran interessiert waren, die Anzahl der aktiven Benutzer über 1-Tages-, 7-Tage- und 28-Tage-Fenster zu berechnen. Daher mussten wir viele Zeilen mit Protokollereignissen erstellen, um ein einfaches Skript zu testen eine neue Dimension hinzugefügt. Wir haben so viel wie möglich wiederverwendet, einschließlich der Verwendung der erwarteten Ausgabe, die für einen Test geschrieben wurde, als Eingabe für einen anderen, wenn es sinnvoll war.

Abbauen

Nach einer Weile wurde das Hinzufügen eines neuen Testfalls zu spröde , da dies möglicherweise Auswirkungen auf eine beliebige Anzahl anderer Tests haben könnte! Wenn Test A eine relative Häufigkeit berechnen muss und dafür alle Zeilen mit COUNT(*) gezählt hat, würde die erwartete Ausgabe von Test A in dem Moment unterbrochen werden, in dem wir dem „goldenen“ Datensatz für Test B eine neue Zeile hinzufügen.

Nach langem Seufzen bissen wir in den sauren Apfel und teilten den goldenen Datensatz in einen Datensatz pro Skript auf . Es war immer noch nicht perfekt, weil wir mehrere Testfälle pro Skript hinzugefügt haben, aber es wurde einfacher zu verwalten. Damit haben wir gelernt:

• Isolieren Sie Tests so weit wie möglich . Ein Komponententest ist wertvoll, weil er scheitert, wenn die Annahmen einer einzelnen Komponente nicht mehr zutreffen. Es ist nicht sinnvoll, dass es kaputt geht, weil ein unabhängiger Test die gemeinsame Umgebung verändert hat.
• CSV ist ein schreckliches Dateiformat . Im Ernst. Wenn wir testen wollten, wie sich die Logik mit einem NULL-String im Vergleich zu einem leeren String verhält, müssten wir eine spezielle Wert- und Konvertierungslogik in den Upload-Schritt einführen. Kein Wunder, dass pandas.read_csv über 52 Argumente verfügt , um alle Fälle zu behandeln, die CSV nicht standardisieren kann.
• Obwohl Tests von ihrer Einfachheit und Eindeutigkeit profitieren, sind synthetische Daten zu ausführlich und machen es schwierig, die Unterschiede zwischen den einzelnen Tests zu erkennen. Wir haben uns stark auf Kommentare verlassen, um klarzustellen, was die Absicht jeder Zeile und jedes Testfalls war (wiederum nicht etwas, das in CSV standardisiert ist).

Zweiter Versuch: Client-Abmeldung mit JSON

In meiner zweiten Erfahrung arbeitete ich als Dateningenieur-Berater bei Zup Innovation, einem Team in Itaú zugeteilt, der größten Bank Lateinamerikas. Wir arbeiteten mit einer On-Premise-Hadoop-Bereitstellung.

Die Art der Arbeit unterschied sich hier stark von der Analyse und bestand hauptsächlich aus der Reduzierung und Deduplizierung von Datensätzen. Die größte Herausforderung bei Skripten besteht darin , inkrementell zu arbeiten , wobei die Ausgabe einer bestimmten Transformation von der Ausgabe der vorherigen abhängt. Normalerweise testen wir es in zwei Schritten: Wir nennen T0 einen Lauf ohne vorherigen Zustand und T1 einen Lauf, der auf T0 aufbaut.

Nach wie vor hatten wir Probleme damit, Fehler erst nach der Bereitstellung zu finden, und wollten sicherstellen, dass alle Eckfälle während der Entwicklung abgedeckt wurden. Das Business-Analysten-Team erstellte eine umfangreiche Liste von Testfällen, in denen jeweils angegeben wurde, ob eine Spalte einen guten oder schlechten Wert haben würde und was die erwartete Ausgabe war. Beachten Sie, dass es sich lediglich um Beschreibungen in einer Tabelle handelte, die aber dennoch einem parametrisierten Test ziemlich nahe kamen .

Die größte Schwierigkeit bestand darin, diese Liste von Testfällen in tatsächliche Hadoop-Eingaben zu übersetzen! Zunächst hatten wir folgenden Ablauf:

1. Wir haben eine gemeinsame Excel-Datei erstellt, in der jede Registerkarte eine Tabelle war, entweder aus dem ersten (T0) oder dem zweiten Durchlauf (T1). Jeder Testfall würde zu 0–2 Zeilen in jeder Tabelle werden.
2. Wir geben jede T0-Tabelle als CSV aus, führen kosmetische Transformationen durch, laden sie in Hadoop hoch und führen das SQL-Skript aus. Die T0-Ausgabe wird gesammelt.
3. Wir geben jede T1-Tabelle als CSV aus, führen kosmetische Transformationen durch, laden sie in Hadoop hoch und führen das SQL-Skript aus. Die T1-Ausgabe wird gesammelt.

Hacken eines Tabellengenerators

Frustriert über das durch diese Fehler verursachte Hin und Her, habe ich ein kleines Synthesizer-Tool erstellt, das als Eingabe JSON-Dateien verwendet, die einen Testfall beschreiben , und dann alle benötigten Tabellen auf einmal ausgibt . Zuerst hoffte ich, dass es von den Technik- und QA-Teams verwendet werden würde, aber einer der Geschäftsanalysten war technisch versiert und verstand JSON und half dabei, ihre eigenen Testfälle in dieses Format zu konvertieren! Eine Datei mit einem Testfall sah so aus:

{
  "companies_t0": [
    {"company_id": "001-3", "company_name": "ACME Inc."},
    {"company_id": "002-9", "company_name": "Umbrella LLC"}
  ],
  "companies_t1": [
    {"company_id": "001-3", "company_name": "ACME Inc."}
  ],
  "associates_t0": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"}
  ],
  "associates_t1": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"} 
  ],
  "persons": [...]
}

Das Tool sammelte alle Zeilen aus jedem Testfall und erstellte die Dateien „firmen_t0.csv“, „firmen_t1.csv“ usw., die dann auf Hadoop hochgeladen wurden. Wenn ein Fehler entdeckt wurde, konnten wir in der JSON-Datei nachsehen, ob es sich um einen Fehler im Testfallcode handelte. Das war viel einfacher, als in vielen Excel-Registerkarten nach der spezifischen Zeile zu suchen, die einen bestimmten Testfall beeinflusst haben könnte .

Eine weitere Funktion dieses Tools bestand darin, dass jede Zeile Spalten aus einer bestimmten Vorlage „erben“ konnte. Dies machte die Testfälle angenehm lesbar, da sie nur die für sie relevanten Spalten enthielten.

Neue Lektionen

Ich kann nicht sagen, dass dieses Tool ein Erfolg war. Erstens haben nur wenige Leute es übernommen, und ich hätte besser mit den Ingenieuren und der Qualitätssicherung kommunizieren sollen, wie es in den Gesamtprozess passen könnte. Zweitens wurden neue Möglichkeiten für manuelle Fehler eingeführt, etwa das Erstellen von Fällen mit doppelten IDs, das Belassen von Spalten beim Kopieren und Einfügen oder das direkte Bearbeiten der CSV-Dateien anstelle ihrer JSON-Vorläufer.

Folgende Lehren habe ich daraus gezogen:

• Unternehmen denken in Fällen, die sich über Tabellen erstrecken, und Code denkt in Tabellen, die Testfälle enthalten . Bei der Erstellung eines Testkabelbaums muss eine Transponierung zwischen ihnen vorgenommen werden.
• Es ist Gold wert, wenn der Kunde Testfälle für Sie schreibt . Niemand weiß am besten, was die relevanten Eingaben und erwarteten Ergebnisse sind, als der Fachexperte! Sie denken vielleicht nicht an jeden Randfall, den der Code verarbeiten muss, aber sie können mit einer Handvoll Testfällen Einblick in knifflige Transformationen geben. Wenn Sie mit ihnen eine gemeinsame Eingabe-Ausgabe-Sprache etablieren, können Sie diese Randfälle später auch besser kommunizieren!
• Das gleichzeitige Ausführen aller Testfälle erschwert die Überprüfung und das Debuggen . Wir mussten eine Zuordnung zwischen Benutzer-ID und Testfall aufrechterhalten, um die Ergebnisse wieder den Testfällen zuzuordnen und sie zu überprüfen. Wir haben versucht, die Testfallnummer zur Benutzer-ID hinzuzufügen und die Daten als „in-band“ zu kennzeichnen, aber das brachte nur noch mehr Probleme mit ungültigen IDs mit sich. Es wäre besser gewesen, entweder jeden Fall einzeln auszuführen oder eine Möglichkeit zu haben, nachzuverfolgen, welchem ​​Fall eine bestimmte Ausgabe entspricht.
• JSON ist kein gutes Format für Menschen. Im obigen Beispiel ist nicht explizit angegeben, was getestet wird, und JSON lässt keine Kommentare zur Erläuterung zu. Ja, wir können ein Feld verwenden "_comment": "blah", aber es ist ein Hack, der bei der Konvertierung in CSV rückgängig gemacht werden muss. Außerdem ist es ein wenig ärgerlich , Kommas im Auge zu behalten und Objektschlüssel in Anführungszeichen zu setzen .
• Zeilenvorlagen verbessern die Lesbarkeit erheblich. Obwohl wir möchten, dass die Tests sehr explizit sind und wenig versteckte Logik enthalten, hilft die Verwendung einer Vorlage zum Ausfüllen uninteressanter Spalten sehr dabei, Unterschiede zwischen Werten zu erkennen, die für den jeweiligen Testfall tatsächlich wichtig sind .

Diese dritte und letzte Erfahrung machte ich bei meinem früheren Arbeitgeber Cherre, der Datenverbindungen für Immobilienunternehmen bereitstellt. Wir haben dbt hauptsächlich zum Schreiben von Transformationen in Google BigQuery, dem kommerziellen Angebot von Dremel, verwendet. Ein Kunde hatte uns gebeten, die Logik stark zu ändern (zum Glück, um sie einfacher zu machen!), und wir wollten sicherstellen, dass wir keine unbeabsichtigten Änderungen vornehmen.

Wiederverwendung von DBT-Modellen

Da die vorhandene Logik nicht einem Unit-Test unterzogen wurde, habe ich mit einem Smoke-Test begonnen , bei dem lediglich Eingaben erstellt und sichergestellt werden, dass nichts kaputt geht. Das Abhängigkeitsdiagramm des getesteten Modells war umfangreich und endete in etwa 20 Quellen, sodass es schwierig wäre, Quelle für Quelle nachzuahmen. Stattdessen habe ich einen geeigneten DAG-Grenzwert gewählt und nur ~7 Refs durch synthetische Daten ersetzt, die mit SQL selbst erstellt wurden :

WITH
-- Synthetic sources
foo AS (
  -- Test case 1, active foo
  SELECT "i001" AS foo_id, 200_000 AS foo_value, TRUE AS is_active,
  UNION ALL
  -- Test case 2, inactive foo without any bar
  SELECT "i002" AS foo_id, 240_000 AS foo_value, FALSE AS is_active,
),
bar AS (
  -- Test case 1, active foo with two bars
  SELECT "b001" AS bar_id, 451.40 AS bar_value, "i001" AS foo_id
  UNION ALL
  SELECT "b002" AS bar_id, 123.98 AS bar_value, "i001" AS foo_id
),
-- From now on, everything should be left the same as the DBT model.
model_x AS (
  SELECT ...
  FROM foo
  JOIN bar USING (foo_id)
)

Um die Ein- und Ausgaben besser zu visualisieren und zu teilen, habe ich eine Kopie des Skripts für jeden einzelnen CTE (dh jede der Zwischentabellen) erstellt und SELECT * FROM <cte>am Ende angehängt. Anschließend habe ich jede Kopie ausgeführt und eine CSV-Datei für jede Zwischentransformation erhalten – foo.csv, bar.csv, model_x.csv usw. Dies half beim Debuggen jedes Schritts der DAG und bot eine Möglichkeit zum Teilen mit Nicht-Technikern Leute, was wären die Ergebnisse der logischen Änderungen, angesichts einiger synthetischer Eingaben im CSV-Format?

Begrenzte Gewinne

Am Ende war dieser Test erfolgreich und stellte sicher, dass wir mit der Logikänderung keine Fehler hinzufügten, da für die bestehende und die neue Logik dieselben Eingaben verwendet wurden. Es hat uns auch dabei geholfen, später ein Produktionsproblem zu beheben, da wir es mit synthetischen Daten reproduzieren konnten. Da es für einen bestimmten Zweck gebaut wurde, haben wir leider nicht versucht, es zu verallgemeinern, um Tests in anderen Modellen zu schreiben.

Die wichtigsten Erkenntnisse waren bisher:

• SQL ist die Option mit dem geringsten Widerstand gegen Fehlanpassungen bei der Deklaration synthetischer Daten. Durch das Schreiben der synthetischen Daten in reinem SQL müssen wir uns keine Gedanken über Umwandlung, NULL-Werte, Kommentare, Lesbarkeit usw. machen, da die Syntax der Sprache diese bereits bereitstellt!
• Es ist wichtig, Schiedsrichter zu verspotten. Der komplexeste Teil der SQL-Logik befindet sich am Ende einer großen DAG, und es wäre kontraproduktiv gewesen, jede der Quellen verspotten zu müssen. Abgesehen von den Quellen könnten wir alle mittelschweren Modelle testen ... aber ich wollte nicht alle testen, sondern nur die interessanten!
• Das Gruppieren von Zeilen nach Testfällen ist besser als nach Tabellen. Ich hatte dies bereits in der vorherigen Erfahrung gelernt, möchte aber noch einmal betonen, dass selbst beim Schreiben von Testfällen in SQL mit Kommentaren beim Lesen von Fällen und beim Zurückverfolgen von Ausgaben zu Tests immer noch ein Teil des Kontexts verloren geht.

Ich weiß nicht, ob wir eine perfekte Lösung für Unit-Test-SQL finden können, aber wir können immer noch davon träumen:

• Testfälle würden isoliert ablaufen;
• Jede Ausgabe würde angeben, aus welchem ​​Fall sie stammt;
• Uninteressante Spalten können automatisch ausgefüllt und/oder ignoriert werden;
• Die gesamte Logik wird wiederverwendet, zum Testen wären keine Anpassungen erforderlich;
• Der Grenzwert des Abhängigkeitsdiagramms kann beliebig sein;
• Testfälle und ihre Ergebnisse wären für einen Fachexperten/Geschäftsspezialisten verständlich – und beschreibbar;

Epilog

Das war's, danke fürs Lesen! Wenn Sie bis hierher gekommen sind, hinterlassen Sie bitte 10 Klatschzeichen, damit dieser Artikel von anderen Menschen wie Ihnen gesehen wird. Teilen Sie uns vor allem Ihre bisherigen Erfahrungen mit Unit-Tests in der Datentechnik mit. Ich fange noch gerade erst an und alle Berichte sind willkommen!

Vielen Dank an David Barer, Bruno Santos und Rodrigo Donizetti für Ihre Verfügbarkeit und die Überprüfung der Entwürfe dieses Beitrags.