Po latach pracy nad nauką i inżynierią danych, jakość danych jest wiszącym duchem, który pojawia się w prawie każdym projekcie, dziesiątkując osiągnięcia biznesowe.

SQL jest de facto językiem danych. Jednym ze sposobów poprawy jakości danych jest ulepszenie bazy kodu SQL za pomocą testów jednostkowych i testów danych. Ten artykuł jest głównie inspirowany postem pani Gao .

W tym artykule podstawowa logika testu jednostkowego na SQL z dbt zostanie zilustrowana prostym zestawem danych.

Podstawowy pomysł

Podstawowa idea przeprowadzania testów jednostkowych w SQL jest dokładnie taka sama, jak w przypadku testów jednostkowych w kodzie Pythona:

1. imitować sterowane wejście D
2. istnieje testowalny moduł/funkcja/algorytm, nazwij to A
3. oblicz oczekiwany wynik, używając D jako danych wejściowych, uzyskaj O_powinien
4. porównaj oczekiwany wynik (O_powinien) z rzeczywistym wyjściem z A (O_is)

Czasami nie musi to być dopasowanie dokładne, czyli dopasowanie tylko do 2 cyfr po przecinku.

Przykład

Zbudujmy naiwny przykład, aby zilustrować powyższy pomysł. Potrzebujemy następującej struktury folderów projektu dbt

-- 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']

Krok 1: załaduj dane testowe do bazy danych

# 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

Krok 2: porównaj

# 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

techniczne

Testy jednostkowe SQL wyglądają na proste, prawda? ale w rzeczywistości sprawa może być bardziej skomplikowana:

• prawdopodobnie potrzebujesz bardziej wyrafinowanej struktury folderów (przy użyciu zagnieżdżonych podfolderów), aby oddzielić i uporządkować testy zgodnie z projektami
• może być konieczne włączenie/wyłączenie testów jednostkowych w oparciu o bieżące środowisko dev/prod
• możesz mieć dużą tabelę wejściową, z której trudno kpić
• możesz mieć złożony model, na podstawie którego trudno jest wcześniej obliczyć oczekiwany wynik (model nietestowalny)
• oczekiwany wynik może nie być w 100% zgodny z rzeczywistą wydajnością (nawet jeśli są one praktycznie takie same) ze względu na precyzję liczb zmiennoprzecinkowych itp
• lub możesz po prostu nie mieć budżetu czasowego w tym projekcie, co nie jest rzadkością, ludzie nie testują dużo sql w 2022 r., instrukcje SQL są uważane za poprawne po napisaniu

Wskazówka: Modularyzuj instrukcje SQL

Pomimo wszystkich wymienionych powyżej wyzwań, jedna rzecz pomaga w testowaniu jednostkowym w przypadku SQL, a także innych bardziej ogólnych języków programowania, takich jak Python.

To jest modularyzacja. Dobry modułowy model/funkcja/algorytm gwarantuje testowalność i czytelność, nawet dla sql.

Istnieje wiele sposobów realizacji tego za pomocą sql i dbt:

• makro dbt (https://docs.getdbt.com/docs/build/jinja-macros)
• z oświadczeniem (https://learnsql.com/blog/what-is-with-clause-sql/)

Wniosek

SQL jest natywnym językiem danych, w tym artykule zademonstrowaliśmy sposób przeprowadzania testów jednostkowych SQL za pomocą dbt.

Podobnie moglibyśmy również wykonać test integracji danych lub inne sztuczki, takie jak delegowanie tworzenia sql do bardziej wydajnego języka, takiego jak python, przy użyciu rasgoQ L.