Data Engineering cho Phân tích Rủi ro 🏠
🏠 Trang chủ / Data Engineering cho Phân tích Rủi ro / Phần 1 · Thu thập & lưu trữ dữ liệu rủi ro / Module 5
🧱 Module 5 / 18 · 2 tuần Trung cấp

Mô hình hoá dữ liệu rủi ro: star schema, SCD2 và đúng thời điểm

Thiết kế mô hình hình sao cho rủi ro công nợ, phân biệt fact giao dịch và fact ảnh chụp định kỳ, giữ lịch sử hạn mức bằng SCD loại 2, nối đúng thời điểm bằng ASOF JOIN, và thấy tận mắt rò rỉ dữ liệu làm mô hình đẹp giả tạo ra sao.

Star schemaSnapshot factSCD Type 2Point-in-timeASOF JOIN
📌 Vì sao module này quan trọng

Câu hỏi rủi ro gần như luôn có chữ “tại thời điểm đó”: hạn mức lúc phát hành hoá đơn là bao nhiêu, công nợ cuối tháng 9 ra sao, khi chấm điểm đại lý ta đã biết gì. Mô hình dữ liệu chỉ lưu “trạng thái hiện tại” sẽ trả lời sai mọi câu hỏi như vậy. Ở An Phát, cùng một mô hình chấm điểm cho AUC 0,99 khi lỡ dùng dữ liệu tương lai, so với 0,80 khi làm đúng.

🎯 Mục tiêu học tập

  • Xác định grain và thiết kế bảng fact/ dimension cho rủi ro công nợ
  • Phân biệt fact giao dịch và fact ảnh chụp định kỳ
  • Cài đặt SCD loại 2 và join theo khoảng hiệu lực
  • Dùng ASOF JOIN cho dữ liệu thị trường
  • Nhận diện và phòng tránh rò rỉ dữ liệu tương lai

🟢 Grain trước, cột sau

Câu hỏi đầu tiên khi thiết kế bảng: một dòng đại diện cho cái gì? Trộn nhiều grain trong một bảng là nguồn gốc của hầu hết các con số bị nhân đôi.

BảngLoạiGrain (một dòng là)Đo lường
fct_invoiceFact giao dịchmột hoá đơnamount_vnd, term_days
fct_paymentFact giao dịchmột lần thanh toánamount_paid
fct_ar_snapshotFact ảnh chụp định kỳmột hoá đơn còn nợ × một ngày chốtoutstanding_vnd, dpd, bucket
fct_dealer_dailyFact ảnh chụp định kỳmột đại lý × một ngàyexposure, limit, utilization, max_dpd
dim_dealerDimension (SCD2)một phiên bản thông tin đại lýsegment, region, credit_limit, valid_from/to
dim_dateDimensionmột ngàyis_workday, is_month_end, quý, năm tài chính, tuần Tết
Mô hình hình sao cho rủi ro công nợ (rút gọn)
dim_dealerSCD2: hạn mức theo thời gian
fct_ar_snapshothoá đơn × ngày chốt
dim_datengày làm việc, cuối tháng
Vì sao cần fact ảnh chụp?

Bảng tuổi nợ cuối tháng 9 có thể tính lại từ hoá đơn và thanh toán (module 3), nhưng tốn công và dễ sai khi dữ liệu nguồn bị sửa sau. Lưu ảnh chụp mỗi ngày chốt giúp báo cáo cũ không đổi sau khi đã gửi HĐQT, và truy vấn xu hướng rất nhanh.

🟢 DDL mô hình tối thiểu

CREATE TABLE dim_dealer (
  dealer_sk          BIGINT PRIMARY KEY,         -- khoá thay thế (surrogate key) cho từng PHIÊN BẢN
  dealer_id          VARCHAR NOT NULL,           -- khoá nghiệp vụ
  segment            VARCHAR NOT NULL,
  region             VARCHAR NOT NULL,
  credit_limit_mvnd  INTEGER NOT NULL,
  valid_from         DATE    NOT NULL,
  valid_to           DATE    NOT NULL,           -- '9999-12-31' cho phiên bản hiện hành
  is_current         BOOLEAN NOT NULL
);

CREATE TABLE fct_ar_snapshot (
  snapshot_date      DATE    NOT NULL,
  invoice_id         VARCHAR NOT NULL,
  dealer_sk          BIGINT  NOT NULL REFERENCES dim_dealer (dealer_sk),
  outstanding_vnd    BIGINT  NOT NULL CHECK (outstanding_vnd >= 0),
  dpd                INTEGER NOT NULL,
  bucket             VARCHAR NOT NULL,
  _loaded_at         TIMESTAMP NOT NULL,
  PRIMARY KEY (snapshot_date, invoice_id)        -- ràng buộc grain
);

Khoá chính (snapshot_date, invoice_id) chính là grain được viết thành ràng buộc: cơ sở dữ liệu sẽ từ chối nếu ai đó nạp trùng.

🟡 SCD loại 2: giữ lịch sử hạn mức

🎬 Báo cáo vượt hạn mức sai

Chuyên viên rủi ro tính “đại lý nào từng vượt hạn mức trong quý 2” bằng hạn mức hiện tại. DL0004 bị hạ hạn mức xuống 800 triệu từ tháng 6, nên báo cáo cho thấy nó “vượt hạn mức” suốt quý 2, trong khi lúc đó hạn mức là 1,55 tỷ và đại lý hoàn toàn tuân thủ. Phòng kinh doanh mất niềm tin vào cả bộ báo cáo.

-- SCD Type 2: giữ lịch sử hạn mức để biết "tại thời điểm hoá đơn, hạn mức là bao nhiêu"
CREATE TABLE dim_dealer_scd2 AS
SELECT dealer_id, segment, credit_limit_mvnd,
       DATE '2020-01-01' AS valid_from, DATE '9999-12-31' AS valid_to, TRUE AS is_current
FROM 'dealers.csv';

-- Ngày 01/06/2025: DL0004 bị hạ hạn mức từ 1.550 xuống 800 triệu
UPDATE dim_dealer_scd2 SET valid_to = DATE '2025-05-31', is_current = FALSE
WHERE dealer_id = 'DL0004' AND is_current;
INSERT INTO dim_dealer_scd2 VALUES ('DL0004', 'Cấp 2', 800, DATE '2025-06-01', DATE '9999-12-31', TRUE);

-- Join theo thời điểm: mỗi hoá đơn gắn với phiên bản hạn mức đúng ngày của nó
SELECT i.invoice_id, i.invoice_date, d.credit_limit_mvnd AS han_muc_luc_do, d.valid_from
FROM 'invoices.csv' i
JOIN dim_dealer_scd2 d
  ON i.dealer_id = d.dealer_id
 AND i.invoice_date BETWEEN d.valid_from AND d.valid_to
WHERE i.dealer_id = 'DL0004' AND i.invoice_date >= DATE '2025-05-01'
ORDER BY i.invoice_date;
+------------+--------------+----------------+------------+
| invoice_id | invoice_date | han_muc_luc_do | valid_from |
+------------+--------------+----------------+------------+
| INV000159  |   2025-05-19 |           1550 | 2020-01-01 |
| INV000160  |   2025-05-29 |           1550 | 2020-01-01 |
| INV000161  |   2025-06-16 |            800 | 2025-06-01 |
| INV000163  |   2025-07-06 |            800 | 2025-06-01 |
| INV000162  |   2025-07-20 |            800 | 2025-06-01 |
+------------+--------------+----------------+------------+
(5 dòng)

Quy tắc SCD2: khi thuộc tính quan trọng thay đổi, đóng phiên bản cũ (valid_to = ngày trước thay đổi, is_current = false) và mở phiên bản mới. Join với fact bằng điều kiện khoảng thời gian. Trong dbt, tính năng snapshots làm việc này tự động.

Loại SCDCách xử lý thay đổiDùng cho
Loại 0Không bao giờ đổiNgày gia nhập
Loại 1Ghi đè, mất lịch sửSửa lỗi chính tả tên
Loại 2Thêm phiên bản mới, giữ lịch sửHạn mức, phân khúc, xếp hạng tín dụng
Loại 3Thêm cột “giá trị trước”Hiếm dùng; chỉ cần một bước lịch sử

🟡 Rò rỉ dữ liệu: khi mô hình “biết trước tương lai”

Thí nghiệm trên dữ liệu An Phát: xếp hạng 472 đại lý theo số ngày trễ trung bình để dự đoán ai vỡ nợ trong năm 2025.

Cùng một đặc trưng, hai thời điểm tính: AUC00.280.550.831.10.79Đặc trưng tính tại 31/12/2024 (đúng)0.99Đặc trưng tính tại 31/12/2025 (rò rỉ)
Tính đặc trưng tại 31/12/2025 nghĩa là đã nhìn thấy chính hành vi trễ hạn trong năm vỡ nợ. Mô hình gần như hoàn hảo trên giấy, nhưng vô dụng khi dùng thật, vì lúc ra quyết định ta chưa có dữ liệu đó.
  • Quy tắc vàng: mọi đặc trưng phải tính bằng điều kiện event_time <= ngày chốt; nhãn (kết quả) lấy từ khoảng sau ngày chốt.
  • Cẩn thận các cột “trạng thái hiện tại” (status = 'blacklist', current_limit): chúng thường được cập nhật vì sự kiện xấu đã xảy ra.
  • Dữ liệu bị sửa sau (hoá đơn điều chỉnh, ghi nhận thanh toán muộn) cũng là rò rỉ tinh vi: dùng ảnh chụp hoặc lưu song thời gian.
Dấu hiệu nghi rò rỉ

AUC > 0,95 với bài toán tín dụng, hoặc một biến có IV > 0,5 (module 11): hãy kiểm tra thời điểm tính trước khi ăn mừng.

🟡 ASOF JOIN: lấy giá trị gần nhất trước thời điểm

Tỷ giá chỉ có vào ngày làm việc; hoá đơn có cả thứ Bảy. Join bằng dấu “=” sẽ mất dòng. ASOF JOIN lấy dòng gần nhất thoả điều kiện bất đẳng thức:

-- ASOF JOIN: lấy tỷ giá gần nhất KHÔNG SAU ngày hoá đơn (cuối tuần không có tỷ giá)
SELECT i.invoice_id,
       i.invoice_date,
       dayname(i.invoice_date)            AS thu,
       f.date                             AS ngay_ty_gia,
       f.usd_vnd,
       ROUND(i.amount_vnd / f.usd_vnd)    AS amount_usd
FROM 'invoices.csv' i
ASOF JOIN 'fx_usdvnd.csv' f ON i.invoice_date >= f.date
WHERE i.invoice_date IN (DATE '2025-03-08', DATE '2025-03-10')
ORDER BY i.invoice_date, i.invoice_id
LIMIT 4;
+------------+--------------+----------+-------------+---------+------------+
| invoice_id | invoice_date | thu      | ngay_ty_gia | usd_vnd | amount_usd |
+------------+--------------+----------+-------------+---------+------------+
| INV000568  |   2025-03-08 | Saturday |  2025-03-07 |  25,920 |      5,443 |
| INV001042  |   2025-03-08 | Saturday |  2025-03-07 |  25,920 |      4,934 |
| INV002985  |   2025-03-08 | Saturday |  2025-03-07 |  25,920 |     10,390 |
| INV003528  |   2025-03-08 | Saturday |  2025-03-07 |  25,920 |      6,833 |
+------------+--------------+----------+-------------+---------+------------+
(4 dòng)
-- Hệ thống không có ASOF JOIN (PostgreSQL): dùng LATERAL
SELECT i.invoice_id, f.usd_vnd
FROM invoices i
CROSS JOIN LATERAL (
  SELECT usd_vnd FROM fx_rates f
  WHERE f.date <= i.invoice_date
  ORDER BY f.date DESC
  LIMIT 1
) f;

🔴 Song thời gian: khi chính quá khứ bị sửa

Ngày 05/01/2026 kế toán phát hiện một hoá đơn tháng 12 bị ghi sai số tiền và sửa lại. Báo cáo công nợ 31/12 gửi HĐQT hôm 02/01 có còn đúng không? Có hai trục thời gian:

TrụcCâu hỏiCột
Thời gian hiệu lực (valid time)Sự việc đúng ở đời thực khi nào?valid_from, valid_to
Thời gian ghi nhận (transaction time)Hệ thống biết điều đó từ khi nào?recorded_from, recorded_to
-- "Số liệu ngày 31/12/2025 theo những gì ta BIẾT vào ngày 02/01/2026" (tái lập báo cáo đã gửi)
SELECT SUM(amount_vnd)
FROM invoice_versions
WHERE invoice_date <= DATE '2025-12-31'
  AND recorded_from <= TIMESTAMP '2026-01-02 23:59:59'
  AND recorded_to   >  TIMESTAMP '2026-01-02 23:59:59';

-- "Số liệu ngày 31/12/2025 theo hiểu biết mới nhất" (đã sửa lỗi)
SELECT SUM(amount_vnd) FROM invoice_versions
WHERE invoice_date <= DATE '2025-12-31' AND recorded_to = TIMESTAMP '9999-12-31';
Không phải lúc nào cũng cần

Bảng song thời gian phức tạp. Với đa số doanh nghiệp, kết hợp fact ảnh chụp (đóng băng số đã báo cáo) + time travel của lakehouse (module 6) là đủ để tái lập báo cáo cũ.

⚠️ Sai lầm thường mắc

❌ Không xác định grain

Vì sao nguy hiểm: Join làm nhân đôi số tiền.

Cách làm đúng: Viết grain thành ràng buộc khoá chính.

❌ Join với dimension hiện tại cho câu hỏi quá khứ

Vì sao nguy hiểm: Báo cáo vượt hạn mức sai.

Cách làm đúng: SCD2 + join theo khoảng hiệu lực.

❌ Tính đặc trưng mô hình với toàn bộ dữ liệu

Vì sao nguy hiểm: Rò rỉ tương lai, AUC giả tạo.

Cách làm đúng: Tính tại ngày chốt; nhãn lấy sau ngày chốt.

❌ Join tỷ giá bằng dấu bằng

Vì sao nguy hiểm: Mất giao dịch cuối tuần/ ngày lễ.

Cách làm đúng: ASOF JOIN hoặc LATERAL.

❌ Tính lại báo cáo cũ từ dữ liệu đã bị sửa

Vì sao nguy hiểm: Số khác với bản đã gửi HĐQT, không giải thích được.

Cách làm đúng: Ảnh chụp định kỳ, time travel hoặc song thời gian.

❌ Khoá thay thế dùng chung cho mọi phiên bản

Vì sao nguy hiểm: Không phân biệt được phiên bản trong fact.

Cách làm đúng: Mỗi phiên bản SCD2 một surrogate key.

📌 Cần nhớ
  • Grain trước, cột sau; viết grain thành khoá chính.
  • Fact giao dịch + fact ảnh chụp định kỳ cho câu hỏi “tại ngày chốt”.
  • SCD2 giữ lịch sử hạn mức; join bằng khoảng valid_from/valid_to.
  • Rò rỉ dữ liệu: AUC 0,99 (sai) so với 0,79 (đúng) trên cùng đặc trưng.
  • ASOF JOIN cho dữ liệu thị trường; song thời gian khi quá khứ bị sửa.

🧩 Tự kiểm tra (bấm để xem đáp án)

1. Grain của fct_ar_snapshot là:
A. Một đại lý
B. Một hoá đơn còn nợ × một ngày chốt
C. Một tháng
Đáp án: B. Mỗi ngày chốt chụp lại các hoá đơn còn nợ.
2. Hạn mức đại lý thay đổi và cần biết hạn mức tại thời điểm hoá đơn. Dùng:
A. SCD loại 1
B. SCD loại 2
C. Xoá dòng cũ
Đáp án: B. SCD2 giữ lịch sử phiên bản.
3. Đặc trưng nào có nguy cơ rò rỉ cao nhất khi dự đoán vỡ nợ năm 2025 (ngày chốt 31/12/2024)?
A. Số năm hoạt động
B. Trạng thái 'blacklist' hiện tại của đại lý
C. Số ngày trễ trung bình năm 2024
Đáp án: B. Trạng thái hiện tại được cập nhật sau khi sự kiện xấu xảy ra.
4. Hoá đơn ngày thứ Bảy cần tỷ giá. ASOF JOIN với điều kiện invoice_date >= date sẽ lấy:
A. Tỷ giá thứ Hai tuần sau
B. Tỷ giá thứ Sáu trước đó
C. Không có
Đáp án: B. Gần nhất và không sau ngày hoá đơn.
5. Báo cáo 31/12 đã gửi; ngày 05/01 kế toán sửa một hoá đơn tháng 12. Để tái lập đúng bản đã gửi, cần:
A. Tính lại từ bảng hiện tại
B. Ảnh chụp/ time travel/ song thời gian
C. Không thể
Đáp án: B. Cần biết dữ liệu như lúc đã biết.

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

Xây dim_dealer SCD2 và bảng đại lý theo ngày

Giả lập 3 thay đổi hạn mức trong năm 2025 cho 3 đại lý (tự chọn ngày). Xây dim_dealer SCD2, rồi tạo bảng fct_dealer_month_end (đại lý × cuối tháng) gồm dư nợ, hạn mức tại ngày đó và tỷ lệ dùng hạn mức. Đếm số đại lý vượt 100% hạn mức mỗi tháng.

💡 Gợi ý lời giải
Tạo danh sách cuối tháng bằng range(DATE '2025-01-01', DATE '2026-01-01', INTERVAL 1 MONTH) + last_day() (xem truy vấn KRI module 16). Join dư nợ theo điều kiện còn nợ tại ngày chốt, rồi join dim_dealer với d.month_end BETWEEN valid_from AND valid_to. Kiểm tra: mỗi (đại lý, tháng) chỉ có một dòng.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Xác định grain cho 5 bảng
  • Viết DDL có khoá chính theo grain
  • Chạy ví dụ SCD2
  • Giải thích được thí nghiệm rò rỉ dữ liệu
  • Làm bài tập fct_dealer_month_end
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.