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.
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ảng | Loại | Grain (một dòng là) | Đo lường |
|---|---|---|---|
| fct_invoice | Fact giao dịch | một hoá đơn | amount_vnd, term_days |
| fct_payment | Fact giao dịch | một lần thanh toán | amount_paid |
| fct_ar_snapshot | Fact ảnh chụp định kỳ | một hoá đơn còn nợ × một ngày chốt | outstanding_vnd, dpd, bucket |
| fct_dealer_daily | Fact ảnh chụp định kỳ | một đại lý × một ngày | exposure, limit, utilization, max_dpd |
| dim_dealer | Dimension (SCD2) | một phiên bản thông tin đại lý | segment, region, credit_limit, valid_from/to |
| dim_date | Dimension | một ngày | is_workday, is_month_end, quý, năm tài chính, tuần Tết |
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
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 SCD | Cách xử lý thay đổi | Dùng cho |
|---|---|---|
| Loại 0 | Không bao giờ đổi | Ngày gia nhập |
| Loại 1 | Ghi đè, mất lịch sử | Sửa lỗi chính tả tên |
| Loại 2 | Thê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 3 | Thê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.
- 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.
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ục | Câu hỏi | Cộ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';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
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.
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.
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.
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.
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.
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.
- 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
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ũ
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
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ó
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ể
🏋️ Bài tập thực hành
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
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
- Kimball Dimensional Modeling TechniquesGrain, fact snapshot, SCDTài liệu
- DuckDB Documentation (mục ASOF JOIN)Hướng dẫn ASOF JOINTài liệu
- dbt SnapshotsSCD2 tự động bằng dbtTài liệu
✅ 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