Nach jahrelanger Arbeit im Bereich Datenwissenschaft und -technik taucht in fast jedem Projekt die Datenqualität auf, die den Geschäftserfolg schmälert.

SQL ist die De-facto-Sprache von Daten. Eine Möglichkeit zur Verbesserung der Datenqualität besteht darin, die SQL-Codebasis durch Komponententests und Datentests zu erweitern. Dieser Artikel ist größtenteils von Frau Gaos Beitrag inspiriert .

In diesem Artikel wird die grundlegende Logik des Komponententests für SQL mit dbt anhand eines einfachen Datensatzes veranschaulicht.

Die Grundidee

Die Grundidee der Durchführung von Unit-Tests für SQL ist genau die gleiche wie die Durchführung von Unit-Tests für Python-Code:

1. simulieren Sie einen steuerbaren Eingang D
2. Es gibt ein testbares Modul/eine testbare Funktion/einen testbaren Algorithmus, nennen Sie es A
3. Berechnen Sie das erwartete Ergebnis mit D als Eingabe und erhalten Sie O_should
4. Vergleichen Sie das erwartete Ergebnis (O_should) mit der tatsächlichen Ausgabe von A (O_is).

Manchmal muss es sich nicht um eine exakte Übereinstimmung handeln, d. h. es muss nur bis zu zwei Nachkommastellen übereinstimmen.

Ein Beispiel

Lassen Sie uns ein naives Beispiel erstellen, um die obige Idee zu veranschaulichen. Wir benötigen die folgende dbt-Projektordnerstruktur

-- dbt_project.yml

-- data/
------ iris.csv
------ selected_iris_expected.csv

-- models/
------ iris/
---------- selected_iris.sql
---------- schema.yml

# example: dbt_project.yml

# take things under data/ as seeds
data-paths: ["data"]

# configure seed, all going into unittesting schema
seeds:
    schema: unittesting

-- selected_iris.sql
{{ config(
        materialized='table',
        schema='unittesting',
        tags=['iris']
    )
}}

-- count the number of special iris id (above average in all aspects)
-- not a very meaningful logic, just for exemplare purpose
SELECT
distinct count(distinct id)
FROM "public"."iris";
where sepallengthcm > 5.9 and sepalwidthcm > 3.1 and petallengthcm > 3.8 and petalwidthcm > 1.2
-- selected_iris_expected.csv
count
150

# schema.yml 

version: 2

# table model selected_iris should be equal to iris
models:
  - name: selected_iris

    tests:
      - dbt_utils.equality:
          compare_model: ref('selected_iris_expected')
          tags: ['unit_testing']

Schritt 1: Testdaten in die Datenbank laden

# here we use
# iris.csv will be loaded into unittesting.iris table
# selected_iris_expected.csv will be loaded into unittesting.selected_iris_expected table
dbt seed
# build selected_iris model into unittesting.selected_iris
dbt run

Schritt 2: Vergleichen

# here we use
# all tests within folder model/iris/ with be executed
# of course, we can restrict to only unittesting using tags
dbt test --model iris

Technische Details

Unit-Tests von SQL sehen einfach aus, oder? aber in Wirklichkeit könnte die Sache komplexer sein:

• Sie benötigen wahrscheinlich eine ausgefeiltere Ordnerstruktur (mit verschachtelten Unterordnern), um Tests nach Projekten zu trennen und zu organisieren
• Möglicherweise müssen Sie Unit-Tests basierend auf der aktuellen Entwicklungs-/Produktumgebung aktivieren/deaktivieren
• Möglicherweise haben Sie eine große Eingabetabelle, die schwer zu verspotten ist
• Möglicherweise verfügen Sie über ein komplexes Modell, bei dem es schwierig ist, das erwartete Ergebnis im Voraus zu berechnen (Modell nicht testbar).
• Das erwartete Ergebnis stimmt möglicherweise nicht zu 100 % mit der tatsächlichen Ausgabe überein (auch wenn sie praktisch gleich sind), aufgrund der Genauigkeit von Gleitkommazahlen usw
• Oder Sie haben einfach kein Zeitbudget für dieses Projekt, was keine Seltenheit ist. Im Jahr 2022 werden SQL-Komponententests nicht mehr häufig durchgeführt, SQL-Anweisungen gelten als korrekt, nachdem sie geschrieben wurden

Ein Tipp: Modularisieren Sie SQL-Anweisungen

Trotz aller oben aufgeführten Herausforderungen hilft beim Unit-Testen sowohl bei SQL als auch bei anderen allgemeineren Programmiersprachen wie Python eines.

Das ist Modularisierung. Ein gutes modularisiertes Modell/Funktion/Algorithmus garantiert die Testbarkeit und Lesbarkeit, auch für SQL.

Es gibt viele Möglichkeiten, dies mit SQL und DBT zu realisieren:

• dbt-Makro (https://docs.getdbt.com/docs/build/jinja-macros)
• mit Aussage (https://learnsql.com/blog/what-is-with-clause-sql/)

Abschluss

SQL ist die Muttersprache der Daten. In diesem Artikel haben wir eine Möglichkeit gezeigt, SQL-Einheitentests mit dbt durchzuführen.

Ebenso könnten wir auch Datenintegrationstests oder andere Tricks durchführen, z. B. die Erstellung von SQL an eine leistungsfähigere Sprache wie Python mithilfe von rasgoQ L delegieren.