Chất lượng dữ liệu: kiểm tra, cách ly, đối soát và hợp đồng dữ liệu
Sáu chiều chất lượng dữ liệu, viết bộ kiểm tra bằng SQL và Python trên file hoá đơn lỗi (1.816 dòng, có 106 dòng lỗi cài sẵn), mẫu cách ly dòng lỗi, đối soát với sổ cái, và hợp đồng dữ liệu giữa nhóm nguồn và nhóm rủi ro.
Một báo cáo rủi ro sai còn nguy hiểm hơn không có báo cáo, vì người ta tin nó. Kiểm tra chất lượng dữ liệu là lớp kiểm soát nội bộ của chính nền tảng dữ liệu: nó quyết định số liệu có được phép đi tiếp lên Gold, lên dashboard của giám đốc hay không.
🎯 Mục tiêu học tập
- Định nghĩa 6 chiều chất lượng với ví dụ cụ thể
- Viết bộ kiểm tra block/warn bằng SQL
- Cài đặt mẫu cách ly (quarantine) bằng pandas
- Đối soát công nợ chi tiết với sổ cái có dung sai
- Viết hợp đồng dữ liệu và kiểm thử dbt
🟢 Sáu chiều chất lượng
| Chiều | Câu hỏi | Ví dụ kiểm tra ở An Phát |
|---|---|---|
| Đầy đủ (completeness) | Có thiếu giá trị/ dòng không? | dealer_id không rỗng; số hoá đơn hôm nay không thấp bất thường |
| Duy nhất (uniqueness) | Có trùng không? | invoice_id duy nhất |
| Hợp lệ (validity) | Giá trị có đúng miền/ định dạng? | amount_vnd > 0; currency ∈ {VND} |
| Nhất quán (consistency) | Các trường có mâu thuẫn nhau? | due_date ≥ invoice_date |
| Toàn vẹn tham chiếu (integrity) | Khoá ngoại có tồn tại? | dealer_id có trong danh mục đại lý |
| Kịp thời (timeliness) | Dữ liệu có đến đúng hạn, ngày có hợp lý? | Không có hoá đơn ngày tương lai; lô đến trước 02:00 |
“Chất lượng dữ liệu 98,5%” là con số vô nghĩa với người làm rủi ro. Điều cần biết là: lỗi nào chặn được báo cáo, lỗi nào chỉ cần cảnh báo, và tổng tiền bị ảnh hưởng là bao nhiêu.
🟡 Bộ kiểm tra bằng SQL: chặn hay cảnh báo
-- Bộ kiểm tra chất lượng cho lô hoá đơn mới (đọc mọi cột dạng chữ để không "mất" lỗi khi ép kiểu)
WITH d AS (SELECT * FROM read_csv('invoices_dirty.csv', all_varchar = true)),
dl AS (SELECT dealer_id FROM 'dealers.csv'),
checks AS (
SELECT 'C1 dealer_id bắt buộc' AS quy_tac, 'block' AS muc_do,
COUNT(*) FILTER (WHERE dealer_id IS NULL OR dealer_id = '') AS so_loi FROM d
UNION ALL SELECT 'C2 invoice_id duy nhất', 'block', COUNT(*) - COUNT(DISTINCT invoice_id) FROM d
UNION ALL SELECT 'C3 số tiền > 0', 'block',
COUNT(*) FILTER (WHERE TRY_CAST(amount_vnd AS BIGINT) IS NULL OR TRY_CAST(amount_vnd AS BIGINT) <= 0) FROM d
UNION ALL SELECT 'C4 tiền tệ = VND', 'block', COUNT(*) FILTER (WHERE currency <> 'VND') FROM d
UNION ALL SELECT 'C5 đại lý tồn tại', 'block',
COUNT(*) FILTER (WHERE dealer_id <> '' AND dealer_id NOT IN (SELECT dealer_id FROM dl)) FROM d
UNION ALL SELECT 'C6 hạn trả ≥ ngày HĐ', 'warn',
COUNT(*) FILTER (WHERE CAST(due_date AS DATE) < CAST(invoice_date AS DATE)) FROM d
UNION ALL SELECT 'C7 ngày HĐ ≤ ngày chốt', 'block',
COUNT(*) FILTER (WHERE CAST(invoice_date AS DATE) > DATE '2025-12-31') FROM d
)
SELECT quy_tac, muc_do, so_loi,
ROUND(100.0 * so_loi / (SELECT COUNT(*) FROM d), 2) AS ty_le_loi_pct,
CASE WHEN so_loi = 0 THEN 'đạt' WHEN muc_do = 'block' THEN 'CHẶN' ELSE 'cảnh báo' END AS ket_qua
FROM checks;+------------------------+--------+--------+---------------+----------+
| quy_tac | muc_do | so_loi | ty_le_loi_pct | ket_qua |
+------------------------+--------+--------+---------------+----------+
| C1 dealer_id bắt buộc | block | 33 | 1.82 | CHẶN |
| C2 invoice_id duy nhất | block | 30 | 1.65 | CHẶN |
| C3 số tiền > 0 | block | 14 | 0.77 | CHẶN |
| C4 tiền tệ = VND | block | 18 | 0.99 | CHẶN |
| C5 đại lý tồn tại | block | 26 | 1.43 | CHẶN |
| C6 hạn trả ≥ ngày HĐ | warn | 19 | 1.05 | cảnh báo |
| C7 ngày HĐ ≤ ngày chốt | block | 8 | 0.44 | CHẶN |
+------------------------+--------+--------+---------------+----------+
(7 dòng)all_varchar = true: đọc mọi cột dạng chữ để thấy được giá trị lỗi. Nếu để DuckDB tự ép kiểu, “VDN” vẫn đọc được nhưng một số tiền “12,5tr” sẽ làm hỏng cả cột.- block (chặn): lỗi làm sai số liệu rủi ro → lô không được lên Gold cho tới khi xử lý. warn (cảnh báo): ghi nhận, báo chủ dữ liệu, vẫn cho đi.
- Lưu kết quả kiểm tra vào bảng
dq_results(run_id, rule, severity, failed_rows, checked_at)để vẽ xu hướng chất lượng theo thời gian.
🟡 Mẫu cách ly: dòng sạch đi tiếp, dòng lỗi ở lại có lý do
import pandas as pd
raw = pd.read_csv("invoices_dirty.csv", dtype=str, keep_default_na=False)
dealers = set(pd.read_csv("dealers.csv", usecols=["dealer_id"]).dealer_id)
amt = pd.to_numeric(raw.amount_vnd, errors="coerce")
inv_d = pd.to_datetime(raw.invoice_date, errors="coerce")
due_d = pd.to_datetime(raw.due_date, errors="coerce")
# Mỗi quy tắc là một cột True/False: dễ đếm, dễ giải thích, dễ ghi log
rules = pd.DataFrame({
"thieu_dealer": raw.dealer_id.eq(""),
"dealer_la": ~raw.dealer_id.eq("") & ~raw.dealer_id.isin(dealers),
"tien_khong_hl": amt.isna() | (amt <= 0),
"tien_te_sai": raw.currency.ne("VND"),
"ngay_tuong_lai": inv_d > pd.Timestamp("2025-12-31"),
"han_truoc_hd": due_d < inv_d,
"trung_khoa": raw.duplicated("invoice_id", keep="first"),
})
bad = rules.any(axis=1)
clean = raw[~bad]
quarantine = raw[bad].assign(ly_do=rules[bad].apply(lambda r: ",".join(r.index[r]), axis=1))
print("Số lỗi theo quy tắc:", rules.sum().to_dict())
print(f"Sạch: {len(clean):,} dòng -> Silver | Cách ly: {len(quarantine):,} dòng -> quarantine")
print(quarantine[["invoice_id", "dealer_id", "amount_vnd", "currency", "ly_do"]].head(5).to_string(index=False))Số lỗi theo quy tắc: {'thieu_dealer': 33, 'dealer_la': 26, 'tien_khong_hl': 14, 'tien_te_sai': 18, 'ngay_tuong_lai': 8, 'han_truoc_hd': 19, 'trung_khoa': 30}
Sạch: 1,682 dòng -> Silver | Cách ly: 134 dòng -> quarantine
invoice_id dealer_id amount_vnd currency ly_do
INV003273 DL0081 81001000 VND ngay_tuong_lai,han_truoc_hd
INV013803 580698000 VND thieu_dealer
INV020176 DL0515 65045000 VND ngay_tuong_lai,han_truoc_hd
INV003165 DL0078 -258634000 VND tien_khong_hl
INV007955 160552000 VND thieu_dealerƯu điểm so với “chặn cả lô”: báo cáo vẫn chạy với 1.682 dòng sạch, đồng thời tổng tiền dòng bị cách ly được hiển thị ngay trên dashboard (“còn 134 hoá đơn, X tỷ đồng chưa xác thực”). Người đọc biết mức độ không chắc chắn của con số.
Khi lỗi mang tính hệ thống: hơn 5% dòng lỗi, mất cả một cột, hoặc tổng tiền lệch sổ cái vượt dung sai. Lúc đó cách ly từng dòng sẽ che giấu sự cố ở nguồn.
🟡 Đối soát với sổ cái: phép thử cuối cùng
Tổng công nợ từ hoá đơn (sổ chi tiết) phải khớp số dư tài khoản 131 (phải thu khách hàng) trên sổ cái. Đây là kiểm tra mà kiểm toán viên sẽ làm đầu tiên.
-- Đối soát hằng ngày, dung sai 0,1% hoặc 50 triệu (lấy số lớn hơn)
WITH sub AS (
SELECT snapshot_date, SUM(outstanding_vnd) AS so_chi_tiet
FROM gold.fct_ar_snapshot GROUP BY 1
), gl AS (
SELECT balance_date AS snapshot_date, SUM(balance_vnd) AS so_cai
FROM silver.gl_balances WHERE account_code LIKE '131%' GROUP BY 1
)
SELECT s.snapshot_date, s.so_chi_tiet, g.so_cai,
s.so_chi_tiet - g.so_cai AS chenh_lech,
CASE WHEN abs(s.so_chi_tiet - g.so_cai) <= GREATEST(0.001 * g.so_cai, 50000000)
THEN 'KHỚP' ELSE 'LỆCH - CHẶN BÁO CÁO' END AS ket_qua
FROM sub s FULL OUTER JOIN gl g USING (snapshot_date)
ORDER BY 1 DESC;Lần đối soát đầu tiên ở An Phát lệch 2,3 tỷ đồng. Nguyên nhân: hoá đơn điều chỉnh giảm (hàng trả lại) được kế toán ghi trực tiếp vào sổ cái, không qua phần mềm bán hàng. Pipeline phải bổ sung nguồn “chứng từ điều chỉnh”. Không có đối soát, sai lệch này sẽ nằm trong mọi báo cáo rủi ro.
| Kiểu đối soát | So cái gì |
|---|---|
| Số dòng | Số bản ghi nguồn = số bản ghi đích (+ số bị cách ly) |
| Tổng kiểm (checksum) | Tổng amount theo ngày giữa nguồn và Silver |
| Số dư | Sổ chi tiết = sổ cái |
| Liên hệ thống | Tiền thu theo sao kê ngân hàng = tiền thu ghi nhận trên phần mềm kế toán |
🟡 Hợp đồng dữ liệu (data contract)
Phần lớn lỗi chất lượng sinh ra ở nguồn khi nhóm phát triển ERP đổi cấu trúc mà không biết ai đang dùng. Hợp đồng dữ liệu là thoả thuận viết ra, có phiên bản, kiểm tra tự động giữa bên cung cấp và bên dùng:
# contracts/erp_invoices.yaml (ví dụ theo tinh thần Data Contract Specification)
id: erp.invoices
version: 1.2.0
owner: { team: erp-sales, contact: erp-team@anphat.vn }
consumers: [risk-data-platform, finance-reporting]
schema:
invoice_id: { type: string, required: true, unique: true, pattern: "^INV[0-9]{6}$" }
dealer_id: { type: string, required: true, references: erp.dealers.dealer_id }
invoice_date: { type: date, required: true }
due_date: { type: date, required: true }
amount_vnd: { type: integer, required: true, minimum: 1 }
currency: { type: string, enum: [VND] }
paid_date: { type: date, required: false }
updated_at: { type: timestamp, required: true, description: "cập nhật mỗi khi dòng thay đổi" }
quality:
- rule: due_date >= invoice_date
- rule: row_count_daily between 0.5 and 2.0 times 30-day average
sla:
freshness: "dữ liệu đến 23:59 có trong bảng nguồn trước 01:30 hôm sau"
availability: "99.5% theo tháng"
change_policy: "thay đổi phá vỡ (đổi tên/ xoá cột, đổi kiểu) báo trước 30 ngày, tăng major version" Không cần công cụ đặc biệt: một file YAML trong kho code chung, một bài kiểm tra trong CI đọc file này và so với cấu trúc thật. Giá trị lớn nhất là cuộc nói chuyện giữa hai nhóm để viết ra nó.
🔴 Công cụ: dbt tests, Great Expectations, Soda, pandera
# models/silver/schema.yml (dbt)
version: 2
models:
- name: silver_invoices
columns:
- name: invoice_id
data_tests: [unique, not_null]
- name: dealer_id
data_tests:
- not_null
- relationships: { to: ref('silver_dealers'), field: dealer_id }
- name: currency
data_tests:
- accepted_values: { values: ['VND'] }
- name: amount_vnd
data_tests:
- dbt_utils.expression_is_true: { expression: "> 0" } # cần package dbt_utils
data_tests:
- dbt_utils.expression_is_true: { expression: "due_date >= invoice_date", config: { severity: warn } }| Công cụ | Mạnh ở | Phù hợp |
|---|---|---|
| dbt tests | Viết cạnh mô hình SQL, chạy cùng dbt build | Nhóm đã dùng dbt |
| Great Expectations | Thư viện kiểm tra phong phú, báo cáo HTML | Pipeline Python, nhiều nguồn |
| Soda | Ngôn ngữ kiểm tra gọn (SodaCL), giám sát | Muốn cấu hình hơn là code |
| pandera | Kiểm tra schema DataFrame bằng Python | Notebook, pipeline pandas/ Polars |
| SQL tự viết (như trên) | Không phụ thuộc, dễ hiểu | Bắt đầu, nhóm nhỏ |
Lưu ý: từ dbt 1.8 khoá tests: được đổi tên thành data_tests: (khoá cũ vẫn chạy). Hãy kiểm tra phiên bản dbt của bạn.
⚠️ Sai lầm thường mắc
Vì sao nguy hiểm: Che giấu lỗi trọng yếu.
Cách làm đúng: Phân loại block/warn, báo số tiền bị ảnh hưởng.
Vì sao nguy hiểm: Giá trị lỗi biến thành NULL rồi biến mất.
Cách làm đúng: Đọc dạng chữ ở Bronze, kiểm tra rồi mới ép kiểu.
Vì sao nguy hiểm: Mất dấu, nguồn không bao giờ được sửa.
Cách làm đúng: Cách ly kèm lý do, gửi chủ dữ liệu.
Vì sao nguy hiểm: Báo cáo rủi ro lệch số kế toán, mất niềm tin.
Cách làm đúng: Đối soát hằng ngày có dung sai.
Vì sao nguy hiểm: Quên chạy đúng hôm có lỗi.
Cách làm đúng: Kiểm tra là một bước bắt buộc trong DAG (module 8).
Vì sao nguy hiểm: Nguồn trả rỗng vẫn “xanh”.
Cách làm đúng: Kiểm tra số dòng so với trung bình lịch sử.
- Sáu chiều: đầy đủ, duy nhất, hợp lệ, nhất quán, toàn vẹn tham chiếu, kịp thời.
- Mỗi quy tắc có mức độ block hoặc warn.
- Cách ly dòng lỗi kèm lý do; chặn cả lô khi lỗi hệ thống.
- Đối soát sổ chi tiết với sổ cái là kiểm tra cuối cùng.
- Hợp đồng dữ liệu: schema + chất lượng + SLA + chính sách thay đổi.
🧩 Tự kiểm tra (bấm để xem đáp án)
1. Hoá đơn có due_date trước invoice_date vi phạm chiều nào?
A. Duy nhất
B. Nhất quán
C. Kịp thời
2. dealer_id không có trong danh mục đại lý vi phạm:
A. Toàn vẹn tham chiếu
B. Đầy đủ
C. Hợp lệ
3. Một lô có 40% dòng thiếu dealer_id. Nên:
A. Cách ly 40% và cho phần còn lại đi tiếp
B. Chặn cả lô, báo sự cố nguồn
C. Bỏ qua
4. Vì sao đọc CSV dạng chữ (all_varchar) khi kiểm tra?
A. Nhanh hơn
B. Để giá trị lỗi không bị ép thành NULL và biến mất
C. Để tiết kiệm bộ nhớ
5. Hợp đồng dữ liệu KHÔNG cần nội dung nào?
A. Schema và kiểu dữ liệu
B. SLA và chính sách thay đổi
C. Màu sắc dashboard
🏋️ Bài tập thực hành
Viết script Python chạy 7 quy tắc trên invoices_dirty.csv, ghi kết quả vào bảng dq_results (run_id, rule, severity, failed_rows, failed_amount_vnd, checked_at), tách sạch/ cách ly ra 2 file Parquet và trả mã thoát 1 nếu có lỗi block vượt 2% tổng số tiền.
💡 Gợi ý lời giải
rules là DataFrame cờ; failed_amount = amt[rules[col]].sum(). Ghi vào DuckDB bằng con.register('df', results) rồi INSERT INTO dq_results SELECT * FROM df. Mã thoát dùng sys.exit(1) để Airflow đánh dấu task thất bại (module 8).📚 Tài nguyên học tập
- dbt Data Testsunique, not_null, relationships, accepted_valuesTài liệu
- Great Expectations DocumentationThư viện kiểm tra chất lượng dữ liệuTài liệu
- Data Contract SpecificationChuẩn mở mô tả hợp đồng dữ liệuChuẩn mở
✅ Tự đánh giá hoàn thành
- Kể 6 chiều chất lượng kèm ví dụ
- Chạy truy vấn kiểm tra SQL
- Chạy ví dụ cách ly
- Viết truy vấn đối soát
- Viết hợp đồng dữ liệu cho một nguồn