หลังจากทำงานด้านวิทยาศาสตร์ข้อมูลและวิศวกรรมมาหลายปี คุณภาพของข้อมูลเป็นสิ่งที่ผีแขวนอยู่ในเกือบทุกโครงการ ทำลายความสำเร็จทางธุรกิจ

SQL เป็นภาษาที่แท้จริงของข้อมูล วิธีหนึ่งในการปรับปรุงคุณภาพข้อมูลคือการปรับปรุง SQL codebase ด้วยการทดสอบหน่วยและการทดสอบข้อมูล บทความนี้ส่วนใหญ่ได้รับแรงบันดาลใจจาก โพส ต์ของคุณ Gao

ในบทความนี้ ตรรกะพื้นฐานของการทดสอบหน่วยบน SQL ด้วย dbt จะแสดงด้วยชุดข้อมูลอย่างง่าย

แนวคิดพื้นฐาน

แนวคิดพื้นฐานของการดำเนินการทดสอบหน่วยบน SQL นั้นเหมือนกับการทดสอบหน่วยในโค้ด Python ทุกประการ:

1. จำลอง อินพุต ที่ควบคุมได้ D
2. มี โมดูล/ฟังก์ชัน/อัลกอริ ทึมที่ทดสอบได้ เรียกว่า A
3. คำนวณผลลัพธ์ที่คาดหวังโดยใช้ D เป็นอินพุต รับ O_should
4. เปรียบเทียบผลลัพธ์ที่คาดหวัง (O_should) กับผลลัพธ์จริงจาก A (O_is)

บางครั้งไม่จำเป็นต้องจับคู่แบบตรงทั้งหมดก็ได้ เช่น จับคู่ได้ไม่เกิน 2 หลักหลังจุดทศนิยม

ตัวอย่าง

ลองสร้างตัวอย่างไร้เดียงสาเพื่ออธิบายแนวคิดข้างต้น เราต้องการโครงสร้างโฟลเดอร์โครงการ 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']

ขั้นตอนที่ 1: โหลดข้อมูลทดสอบลงในฐานข้อมูล

# 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

ขั้นตอนที่ 2: เปรียบเทียบ

# 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

เทคนิค

การทดสอบหน่วย SQL ดูง่ายใช่ไหม แต่ในความเป็นจริงอาจซับซ้อนกว่านี้:

• คุณอาจต้องการโครงสร้างโฟลเดอร์ที่ซับซ้อนกว่านี้ (โดยใช้โฟลเดอร์ย่อยที่ซ้อนกัน) เพื่อแยกและจัดระเบียบการทดสอบตามโครงการ
• คุณอาจต้องเปิด/ปิดการทดสอบหน่วยตามสภาพแวดล้อมปัจจุบัน dev/prod
• คุณอาจมีตารางอินพุตขนาดใหญ่ซึ่งยากต่อการจำลอง
• คุณอาจมีโมเดลที่ซับซ้อน ซึ่งเป็นเรื่องยากที่จะคำนวณผลลัพธ์ที่คาดไว้ล่วงหน้า (โมเดลไม่สามารถทดสอบได้)
• ผลลัพธ์ที่คาดหวังอาจไม่ตรงกับผลลัพธ์จริง 100% (แม้ว่าจะเหมือนกันทุกประการ) เนื่องจากความแม่นยำของตัวเลขลอยตัว ฯลฯ
• หรือคุณอาจไม่มีงบประมาณเวลาในโครงการนี้ซึ่งไม่ใช่เรื่องแปลก คนไม่ทดสอบ sql เป็นจำนวนมากในปี 2565 คำสั่ง SQL ถือว่าถูกต้องหลังจากเขียนแล้ว

เคล็ดลับ: ทำให้คำสั่ง SQL เป็นโมดูล

แม้จะมีความท้าทายทั้งหมดตามรายการด้านบน แต่มีสิ่งหนึ่งที่ช่วยในการทดสอบหน่วยในกรณีของ SQL เช่นเดียวกับภาษาโปรแกรมทั่วไปอื่น ๆ เช่น Python

นั่นคือการทำให้เป็นโมดูล โมเดล/ฟังก์ชัน/อัลกอริทึมแบบโมดูลาร์ที่ดีรับประกันความสามารถในการทดสอบและการอ่านค่า แม้แต่สำหรับ sql

มีหลายวิธีในการตระหนักถึงสิ่งนี้ด้วย sql และ dbt:

• มาโคร dbt (https://docs.getdbt.com/docs/build/jinja-macros)
• พร้อมคำสั่ง (https://learnsql.com/blog/what-is-with-clause-sql/)

บทสรุป

SQL เป็นภาษาดั้งเดิมของข้อมูล ในบทความนี้ เราได้สาธิตวิธีทดสอบหน่วย SQL ด้วย dbt

ในทำนองเดียวกัน เรายังสามารถทดสอบการรวมข้อมูลหรือเทคนิคอื่นๆ เช่น มอบหมายการสร้าง sql เป็นภาษาที่ทรงพลังกว่า เช่น python โดยใช้rasgoQ L