Data Engineering cho Phân tích Rủi ro 🏠
🏠 Trang chủ / Data Engineering cho Phân tích Rủi ro / Phần 2 · Chất lượng, pipeline & quản trị dữ liệu / Module 7
🧪 Module 7 / 18 · 2 tuần Trung cấp

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.

Data qualityQuarantineReconciliationData contractdbt tests
📌 Vì sao module này quan trọng

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ềuCâu hỏiVí 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
File invoices_dirty.csv: tỷ lệ đạt theo từng chiều (%)9797.898.699.410098.2Đầy đủ98.3Duy nhất98.2Hợp lệ99Nhất quán98.6Toàn vẹn tham chiếu99.6Kịp thời/ hợp lý thời gian
Mỗi chiều đều trên 98%, nghe có vẻ tốt. Nhưng gộp lại có 106 dòng lỗi (≈ 5,8%), và một dòng trùng hoá đơn 900 triệu là đủ làm lệch báo cáo công nợ.
Đừng chấm điểm trung bình

“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
Lô thôBronze
Kiểm tra7 quy tắc, mỗi quy tắc một cột cờ
Silverdòng sạch
Quarantinedòng lỗi + ly_do
Phiếu xử lýgửi chủ dữ liệu; sửa ở nguồn rồi nạp lại

Ư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 nào phải chặn cả lô

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;
🎬 Chênh 2,3 tỷ

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átSo cái gì
Số dòngSố 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ốngTiề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" 
Bắt đầu nhỏ

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 testsViết cạnh mô hình SQL, chạy cùng dbt buildNhóm đã dùng dbt
Great ExpectationsThư viện kiểm tra phong phú, báo cáo HTMLPipeline Python, nhiều nguồn
SodaNgôn ngữ kiểm tra gọn (SodaCL), giám sátMuốn cấu hình hơn là code
panderaKiểm tra schema DataFrame bằng PythonNotebook, pipeline pandas/ Polars
SQL tự viết (như trên)Không phụ thuộc, dễ hiểuBắ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

❌ Chỉ báo cáo điểm chất lượng trung bình

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.

❌ Ép kiểu trước khi kiểm tra

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.

❌ Xoá dòng lỗi

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.

❌ Không đối soát với sổ cái

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.

❌ Kiểm tra chỉ chạy thủ công

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

❌ Không có ngưỡng biến động số dòng

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ử.

📌 Cần nhớ
  • 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
Đáp án: B. Hai trường mâu thuẫn nhau.
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ệ
Đáp án: A. Khoá ngoại không tồn tại.
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
Đáp án: B. Lỗi hệ thống, không phải lỗi lẻ.
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ớ
Đáp án: B. Thấy được lỗi gốc.
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
Đáp án: C. Dashboard là việc của bên dùng.

🏋️ Bài tập thực hành

Viết bộ kiểm tra chất lượng có lưu kết quả

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
Tái dùng khung trong ví dụ quarantine: 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

✅ 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
Lưu ý: đây là tài liệu giáo dục. Công ty An Phát Distribution, đại lý, nhà cung cấp, nhân viên và mọi số liệu đều là giả định do code sinh ra, không đại diện cho doanh nghiệp thật. Phương pháp (scorecard, ECL, VaR, Benford...) được đơn giản hoá cho mục đích học; khi áp dụng thật, hãy đối chiếu chuẩn mực kế toán (VAS/ IFRS 9), quy định pháp luật hiện hành (ví dụ về bảo vệ dữ liệu cá nhân) và chính sách nội bộ của tổ chức bạn.