Setelah bertahun-tahun mengerjakan ilmu dan teknik data, kualitas data adalah hantu yang menggantung muncul di hampir setiap proyek, yang menghancurkan pencapaian bisnis.

SQL adalah bahasa de facto Data. Salah satu cara untuk meningkatkan kualitas data adalah dengan meningkatkan basis kode SQL dengan pengujian unit dan pengujian data. Artikel ini sebagian besar terinspirasi dari postingan Nyonya Gao .

Pada artikel ini, logika dasar pengujian unit pada SQL dengan dbt akan diilustrasikan dengan kumpulan data sederhana.

Ide dasar

Ide dasar melakukan pengujian unit pada SQL sama persis dengan melakukan pengujian unit pada kode Python:

1. tiruan input yang dapat dikontrol D
2. ada modul/fungsi/algoritma yang dapat diuji , sebut saja A
3. hitung hasil yang diharapkan menggunakan D sebagai input, dapatkan O_should
4. bandingkan hasil yang diharapkan (O_should) dengan output aktual dari A (O_is)

Terkadang tidak harus sama persis, yaitu hanya mencocokkan hingga 2 angka di belakang titik desimal.

Sebuah contoh

Mari kita buat contoh naif, untuk mengilustrasikan ide di atas. Kami membutuhkan struktur folder proyek dbt berikut

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

Langkah 1: memuat data uji ke dalam basis data

# 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

Langkah 2: bandingkan

# 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

Teknis

Pengujian unit SQL terlihat sederhana bukan? tetapi pada kenyataannya hal bisa lebih kompleks:

• Anda mungkin memerlukan struktur folder yang lebih canggih (menggunakan subfolder bersarang) untuk memisahkan dan mengatur pengujian menurut proyek
• Anda mungkin perlu mengaktifkan/menonaktifkan pengujian unit berdasarkan dev/prod lingkungan saat ini
• Anda mungkin memiliki tabel input besar yang sulit ditiru
• Anda mungkin memiliki model yang rumit, di mana sulit untuk menghitung hasil yang diharapkan sebelumnya (model tidak dapat diuji)
• hasil yang diharapkan mungkin tidak 100% cocok dengan keluaran aktual (walaupun secara praktis sama) karena presisi angka mengambang dll
• atau Anda mungkin tidak memiliki anggaran waktu dalam proyek ini yang tidak biasa, orang tidak banyak menguji sql pada tahun 2022, pernyataan SQL dianggap benar setelah ditulis

Tip: Memodulasi Pernyataan SQL

Terlepas dari semua tantangan yang tercantum di atas, satu hal yang membantu dengan pengujian unit dalam kasus SQL serta bahasa pemrograman lain yang lebih umum seperti Python.

Itu adalah Modularisasi. Model/fungsi/algoritma termodulasi yang baik menjamin testabilitas dan keterbacaan, bahkan untuk sql.

Ada banyak cara untuk mewujudkannya dengan sql dan dbt:

• dbt makro (https://docs.getdbt.com/docs/build/jinja-macros)
• dengan pernyataan (https://learnsql.com/blog/what-is-with-clause-sql/)

Kesimpulan

SQL adalah bahasa asli data, dalam artikel ini, kami mendemonstrasikan cara melakukan pengujian unit SQL dengan dbt.

Demikian juga kita juga bisa melakukan tes integrasi data atau trik lain seperti mendelegasikan pembuatan sql ke bahasa yang lebih kuat seperti python menggunakan rasgoQ L.