Baru-baru ini saya sedang berdiskusi dengan seorang rekan kerja tentang bagaimana melakukan pengujian unit dalam SQL dan saya menyadari bahwa, meskipun saya masih di awal karir data saya, saya telah mengalami masalah ini beberapa kali dan pada setiap giliran memiliki pendekatan yang berbeda untuk menyelesaikannya. Saya pikir mungkin berguna untuk membagikan retasan saya sehingga saya dapat mengkonsolidasikan apa yang telah saya pelajari, dan juga untuk belajar dari Anda pembaca apa yang telah Anda lakukan dan bagaimana hasilnya. Bolehkah kita?

Tetapi mengapa unit menguji SQL?

Penting untuk mengklarifikasi bahwa saya sedang mendiskusikan di sini menguji logika SQL , bukan menguji data . Menguji data memastikan bahwa anggapan untuk properti skema, seperti nullability dan uniqueness, benar-benar berlaku untuk nilai yang ada. RDBMS tradisional akan memastikan bahwa dengan batasan tabel, tetapi kami tidak memiliki fitur ini di gudang data, jadi kami harus benar-benar membaca dan memeriksa kondisi sebelum dan sesudah dataset.

Pengujian unit SQL sama dengan kode aplikasi pengujian unit: memastikan bahwa sepotong logika menghasilkan output yang diharapkan untuk serangkaian input tertentu. Meskipun praktik terbaik mengharuskan kami menguji unit hampir semua kode aplikasi, baik itu C++ atau Python, kami jarang melihat ini dicoba dengan SQL. Mengapa?

Jawabannya, bagi saya, menulis SQL bukanlah bagian tersulit . Sebagian besar waktu, transformasi data sepele dan berulang, dan kesulitannya lebih terletak pada pemodelan data dengan tepat daripada memindahkan bit dari A ke B. Namun, ketika logika bisnis diperlukan dalam transformasi, Anda mungkin berakhir dengan kerumitan yang diperlukan yang sulit. untuk pecah — seperti ekspresi CASE-WHEN yang kompleks untuk menghasilkan kolom, dengan input yang berasal dari GABUNG tujuh arah yang menakutkan, dan klausa WHERE multi-baris yang memerlukan tabel kebenaran untuk memahami apa yang sedang terjadi…

Dalam pengalaman saya, memastikan bahwa logikanya baik hanya mengandalkan pengujian manual dan persetujuan klien, yaitu 1) rapuh dan 2) hampir kriminal mengingat seberapa cocok untuk pengujian unit SQL! Meskipun aplikasi memerlukan semua jenis perancah untuk membatasi status yang dapat diubah dan memastikan reproduktifitas dalam pengujian, SQL bersifat murni dan eksplisit. Jadi mengapa tidak menggunakan infrastruktur yang sama untuk memvalidasi bahwa logika berfungsi seperti yang diharapkan?

Upaya pertama: analitik

Upaya pertama saya untuk menguji skrip SQL unit adalah ketika saya bekerja di Google, menggunakan teknologi internal seperti Dremel dan Powerdrill (Anda akan melihat bahwa proyek mereka memiliki nama yang terkait dengan bekerja dengan log ).

Kami sedang membangun saluran analitik untuk aplikasi baru, memproses log pengguna untuk menghasilkan metrik yang disukai manajer produk, seperti pengguna aktif per negara atau kelompok. Kueri yang representatif adalah:

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

Meretas kerangka pengujian

Kami ingin melakukan yang lebih baik dan memastikan bahwa skrip kami dapat dipercaya, jadi kami membangun kerangka pengujian kami sendiri:

1. Setiap tabel sumber akan memiliki CSV sintetik, dengan banyak pengguna dan kolom yang diisi. Untuk setiap test case baru, kami akan menambahkan baris ke tabel sumber yang sesuai.
2. Saat pengujian dijalankan, seluruh kumpulan data diunggah ke tabel sementara di gudang data.
3. Untuk setiap skrip, sumber secara tekstual diganti dengan tabel sementara, dan skrip akan dieksekusi.
4. Untuk setiap skrip, kami membuat CSV keluaran yang diharapkan dan membedakannya dengan keluaran aktual, melaporkan perbedaan apa pun.

Membuat CSV memakan waktu lama karena kami tertarik menghitung jumlah pengguna aktif selama jendela 1 hari, 7 hari, dan 28 hari, jadi kami perlu membuat banyak baris dengan peristiwa log hanya untuk menguji skrip sederhana itu menambahkan dimensi baru. Kami menggunakan kembali sebanyak mungkin, termasuk menggunakan keluaran yang diharapkan yang ditulis untuk satu pengujian sebagai masukan untuk pengujian lainnya, jika masuk akal.

Perincian

Setelah beberapa saat, menambahkan kasus uji baru menjadi terlalu rapuh , karena Anda berpotensi memengaruhi sejumlah uji lainnya! Jika pengujian A perlu menghitung frekuensi relatif, dan untuk itu menghitung semua baris dengan COUNT(*), saat kita menambahkan baris baru ke kumpulan data "emas" untuk pengujian B, keluaran yang diharapkan dari pengujian A akan terputus.

Setelah banyak menghela nafas, kami mengambil keputusan dan membagi kumpulan data emas menjadi satu kumpulan data per skrip . Itu masih belum sempurna karena kami menambahkan beberapa test case per skrip, tetapi menjadi lebih mudah dikelola. Dengan itu kami belajar:

• Isolasi tes sebanyak mungkin . Tes unit memiliki nilai karena rusak ketika asumsi komponen tunggal tidak berlaku lagi. Menghancurkannya karena beberapa tes yang tidak terkait mengubah lingkungan bersama tidak berguna.
• CSV adalah format file yang mengerikan . Serius. Jika kami ingin menguji bagaimana logika berperilaku dengan string NULL versus string kosong, kami perlu memperkenalkan nilai khusus dan logika konversi ke langkah upload. Tidak heran jika pandas.read_csv memiliki 52 argumen untuk menangani semua kasus yang gagal distandarisasi oleh CSV.
• Meskipun pengujian mendapat manfaat dari kesederhanaan dan kejelasan, data sintetik terlalu bertele-tele dan menyulitkan untuk menemukan apa yang berbeda dari satu pengujian dengan pengujian lainnya. Kami sangat bergantung pada komentar untuk memperjelas maksud dari setiap baris dan kasus uji (sekali lagi, bukan sesuatu yang dibakukan dalam CSV).

Upaya kedua: klien sign-off dengan JSON

Dalam pengalaman kedua saya, saya bekerja sebagai konsultan insinyur data di Zup Innovation, yang ditugaskan ke sebuah tim di Itaú, bank terbesar di Amerika Latin. Kami bekerja dengan penerapan Hadoop di tempat.

Jenis pekerjaan di sini sangat berbeda dengan analitik, kebanyakan terdiri dari perataan dan deduplikasi catatan. Tantangan utama yang berasal dari skrip adalah inkremental , dengan keluaran dari transformasi yang diberikan bergantung pada keluaran sebelumnya. Kami biasanya mengujinya dalam dua langkah: kami menyebut T0 sebagai proses tanpa status sebelumnya, dan T1 sebagai proses yang membangun di atas T0.

Seperti sebelumnya, kami kesulitan menemukan bug hanya setelah penerapan, dan ingin memastikan bahwa semua kasus sudut tercakup selama pengembangan. Tim analis bisnis menyiapkan daftar kasus uji yang ekstensif, untuk masing-masing kasus menentukan apakah sebuah kolom akan memiliki nilai baik atau buruk, dan keluaran apa yang diharapkan. Perhatikan bahwa ini hanya deskripsi di atas meja, tetapi masih cukup dekat dengan tes parametri .

Kesulitan utama adalah menerjemahkan daftar kasus uji ini ke input Hadoop yang sebenarnya! Awalnya kami memiliki proses berikut:

1. Kami membuat file Excel bersama di mana setiap tab adalah tabel, baik dari yang pertama (T0) atau yang kedua dijalankan (T1). Setiap kasus uji akan menjadi 0–2 baris di setiap tabel.
2. Kami mengeluarkan setiap tabel T0 sebagai CSV, melakukan transformasi kosmetik, mengunggahnya ke Hadoop, dan menjalankan skrip SQL. Keluaran T0 dikumpulkan.
3. Kami mengeluarkan setiap tabel T1 sebagai CSV, melakukan transformasi kosmetik, mengunggahnya ke Hadoop, dan menjalankan skrip SQL. Keluaran T1 dikumpulkan.

Meretas generator tabel

Frustrasi dengan bolak-balik yang disebabkan oleh kesalahan ini, saya membuat alat penyintesis kecil, yang akan mengambil file JSON sebagai input yang menjelaskan kasus uji , dan kemudian menampilkan semua tabel yang kami butuhkan sekaligus . Awalnya saya berharap ini akan digunakan oleh tim eng dan QA, tetapi salah satu analis bisnis secara teknis paham dan memahami JSON, dan membantu mengonversi test case mereka sendiri ke format ini! File dengan test case terlihat seperti ini:

{
  "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": [...]
}

Alat tersebut mengumpulkan semua baris dari setiap kasus uji dan membuat file perusahaan_t0.csv, perusahaan_t1.csv, dll., yang kemudian diunggah ke Hadoop. Saat kesalahan ditemukan, kami dapat membuka file JSON dan memeriksa apakah ada kesalahan dalam kode kasus uji, yang jauh lebih mudah dilakukan daripada mencari baris tertentu dalam banyak tab Excel yang mungkin memengaruhi kasus uji tertentu. .

Fitur lain dari alat ini memungkinkan setiap baris untuk "mewarisi" kolom dari templat yang diberikan. Ini membuat test case menyenangkan untuk dibaca, karena hanya menyertakan kolom yang relevan dengannya.

Pelajaran baru

Saya tidak bisa mengatakan bahwa alat ini sukses. Pertama, hanya sedikit orang yang mengadopsinya, dan saya seharusnya berkomunikasi lebih baik dengan para insinyur dan QA bagaimana hal itu dapat masuk ke dalam keseluruhan proses. Kedua, ini memperkenalkan peluang kesalahan manual baru, seperti membuat kasus dengan ID duplikat, membiarkan kolom tidak berubah saat salin-tempel, atau mengedit file CSV secara langsung alih-alih progenitor JSON mereka.

Saya mendapat pelajaran berikut darinya:

• Bisnis berpikir dalam kasus yang menjangkau tabel, dan kode berpikir dalam tabel yang berisi kasus uji . Ada transpos yang harus dilakukan di antara keduanya saat membuat test harness.
• Meminta klien menulis kasus uji untuk Anda adalah emas . Tidak ada yang tahu apa input yang relevan dan output yang diharapkan daripada pakar domain! Mereka mungkin tidak memikirkan setiap kasus tepi yang perlu ditangani oleh kode, tetapi mereka dapat memberikan wawasan dengan beberapa kasus uji tentang transformasi rumit. Selain itu, menetapkan bahasa masukan-keluaran bersama dengan mereka membantu Anda mengomunikasikan kasus-kasus tepi tersebut nanti!
• Menjalankan semua test case sekaligus membuat pengecekan dan debugging menjadi lebih sulit . Kami harus menyimpan pemetaan antara kasus uji ID pengguna untuk memetakan hasilnya kembali ke kasus uji dan memeriksanya. Kami mencoba menambahkan nomor kasus uji ke ID pengguna, menandai data "in-band", tetapi ini hanya membawa lebih banyak masalah dengan ID yang tidak valid. Akan lebih baik untuk menjalankan setiap kasus secara terpisah, atau memiliki cara untuk melacak kasus apa yang sesuai dengan keluaran yang diberikan.
• JSON bukanlah format yang baik untuk manusia. Dalam contoh di atas, tidak eksplisit apa yang sedang diuji, dan JSON tidak mengizinkan komentar untuk menjelaskannya. Ya, kami dapat menggunakan "_comment": "blah"bidang, tetapi ini adalah retasan yang perlu diurungkan dalam konversi ke CSV. Juga, melacak koma dan mengutip kunci objek agak menyebalkan .
• Templat baris sangat meningkatkan keterbacaan. Meskipun kami ingin pengujian menjadi sangat eksplisit dan dengan sedikit logika tersembunyi, menggunakan template untuk mengisi kolom yang tidak menarik sangat membantu dalam melihat perbedaan antara nilai yang memang penting untuk kasus pengujian yang ada.

Pengalaman ketiga dan terakhir ini terjadi di perusahaan saya sebelumnya, Cherre, yang menyediakan koneksi data untuk perusahaan real estate. Kami kebanyakan menggunakan dbt untuk menulis transformasi di Google BigQuery, penawaran komersial dari Dremel. Seorang klien telah meminta agar kami memodifikasi logika secara besar-besaran (untungnya, membuatnya lebih sederhana!), dan kami ingin memastikan bahwa kami tidak akan melakukan perubahan yang tidak diinginkan.

Menggunakan kembali model dbt

Karena logika yang ada tidak diuji unit, saya mulai dengan smoke test , yang hanya membuat input dan memastikan tidak ada yang rusak. Grafik dependensi model yang diuji sangat dalam, diakhiri dengan ~20 sumber, jadi akan memusingkan untuk mengejek sumber demi sumber. Sebagai gantinya, saya memilih cutoff DAG yang sesuai dan hanya mengganti ~7 referensi dengan data sintetis, yang dibuat dengan SQL itu sendiri :

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)
)

Untuk memvisualisasikan dan membagikan input dan output dengan lebih baik, saya telah membuat salinan skrip untuk masing-masing CTE (yaitu, masing-masing tabel perantara) dan menambahkan SELECT * FROM <cte>di bagian akhir. Saya kemudian mengeksekusi setiap salinan, mendapatkan CSV untuk setiap transformasi perantara — foo.csv, bar.csv, model_x.csv, dll.. Ini membantu men-debug setiap langkah DAG, serta menyediakan cara untuk berbagi dengan non-teknis orang apa yang akan menjadi hasil dari perubahan logika, diberikan beberapa input sintetik, dalam format CSV.

Keuntungan terbatas

Pada akhirnya, pengujian ini berhasil memastikan bahwa kami tidak akan menambahkan kesalahan apa pun dengan perubahan logika, karena input yang sama digunakan untuk logika yang ada dan yang baru. Ini juga membantu men-debug masalah produksi nanti, dengan mengizinkan kami mereproduksinya dengan data sintetik. Sayangnya, karena dibuat untuk tujuan tertentu, kami tidak berusaha menggeneralisasikannya untuk menulis pengujian di model lain.

Pelajaran utama sejauh ini adalah:

• SQL adalah opsi dengan ketidakcocokan resistansi paling rendah untuk mendeklarasikan data sintetis. Dengan menulis data sintetik dalam SQL murni, kita tidak perlu khawatir tentang transmisi, NULL, komentar, keterbacaan… karena sintaks bahasa sudah menyediakannya!
• Sangat penting untuk mengejek wasit. Bagian paling kompleks dari logika SQL berada di akhir DAG besar, dan harus mengejek setiap sumber akan menjadi kontraproduktif. Mengejek sumber, kami dapat menguji semua model perantara… tetapi saya tidak ingin menguji semuanya, hanya yang menarik!
• Mengelompokkan baris berdasarkan kasus uji lebih baik daripada berdasarkan tabel. Saya telah mempelajari ini di pengalaman sebelumnya, tetapi hanya ingin menegaskan kembali bahwa bahkan ketika kasus uji ditulis dalam SQL dengan komentar, beberapa konteks masih hilang saat membaca kasus dan mundur dari keluaran ke pengujian.

Saya tidak tahu apakah kami dapat mencapai solusi sempurna untuk pengujian unit SQL, tetapi kami masih dapat memimpikannya:

• Uji kasus akan berjalan secara terpisah;
• Setiap keluaran akan menunjukkan dari mana asalnya;
• Kolom yang tidak menarik dapat diisi otomatis dan/atau diabaikan;
• Semua logika digunakan kembali, tidak diperlukan adaptasi untuk pengujian;
• Titik potong grafik ketergantungan bisa berubah-ubah;
• Uji kasus dan hasilnya dapat dipahami — dan dapat ditulis — oleh pakar domain/spesialis bisnis;

Epilog

Itu saja, terima kasih telah membaca! Jika Anda sampai sejauh ini, silakan tinggalkan 10 tepuk tangan untuk membantu artikel ini dilihat oleh orang lain seperti Anda. Yang terpenting, silakan bagikan pengalaman Anda sebelumnya dengan pengujian unit dalam rekayasa data, saya masih baru memulai dan laporan apa pun diterima!

Terima kasih kepada David Barer, Bruno Santos, dan Rodrigo Donizetti atas kesediaan Anda dan meninjau draf postingan ini.