Modeling nâng cao 3 — SCD nâng cao & Bi-temporal

Ở bài Dimensions & SCD chúng ta đã đi qua trục chính của Slowly Changing Dimension: Type 0 giữ nguyên, Type 1 ghi đè, Type 2 thêm dòng, Type 3 thêm cột, Type 4 tách bảng, Type 6 hybrid. Đó là nền đủ để một kho dữ liệu giữ lịch sử và trả lời câu hỏi "as-of". Bài này đẩy tiếp tới những góc mà thực tế ngân hàng bắt buộc phải xử lý đúng mà bài nền chưa mổ sâu: bộ Type 0–7 đầy đủ (bổ sung Type 5 và Type 7), mô hình bi-temporal với hai trục thời gian độc lập, late-arriving dimension & fact, và bài toán khó nhất — thay đổi hồi tố (retroactive change): sửa quá khứ mà không làm hỏng những gì báo cáo đã chốt.

Mô hình tinh thần cần mang theo suốt bài: SCD cổ điển chỉ giả định một trục thời gian — thời điểm sự thật có hiệu lực trong thế giới thực. Nhưng có một trục thứ hai mà mọi hệ thống kiểm toán đều quan tâm: thời điểm kho dữ liệu biết về sự thật đó. Hai trục này thường lệch nhau — và mọi rắc rối của late-arriving và retroactive đều sinh ra từ khoảng lệch đó. Bi-temporal chính là cách mô hình hoá cả hai trục cùng lúc.

DDL/SQL trong bài là minh hoạ để nắm tư duy; sandbox là PostgreSQL (MERGE cần Postgres 15+).

Bộ SCD Type 0–7 đầy đủ

Bài nền dừng ở Type 6. Kimball định nghĩa trọn vẹn tới Type 7, và điểm hay là các type cao đều là phép cộng của các type thấp — nhớ công thức là nhớ được cơ chế.

Ba type đầu và Type 4/6 đã bàn ở bài nền. Ở đây làm rõ hai type còn thiếu — 5 và 7 — vì chúng là nơi hay nhầm nhất.

Type 5 = Type 4 + Type 1 (mini-dimension + outrigger)

Type 4 tách nhóm thuộc tính đổi nhanh (khoảng thu nhập, band điểm tín dụng, phân khúc marketing) ra một mini-dimension để dim khách hàng chính khỏi nở dòng bùng nổ. Nhưng Type 4 thuần có một bất tiện: muốn biết "band tín dụng hiện tại của khách này" phải đi vòng qua fact mới nhất.

Type 5 vá chỗ đó: nhúng thẳng vào dim khách hàng chính một cột khoá current_credit_band_sk trỏ tới mini-dimension, và cập nhật cột này theo kiểu Type 1 (luôn ghi đè bằng band hiện hành). Đây là một outrigger kiểu Type 1. Kết quả:

• Fact vẫn trỏ tới mini-dimension theo band tại thời điểm giao dịch (giữ lịch sử qua fact).
• Dim khách hàng cho ngay band hiện tại mà không cần chạm fact.

Con số "5" chính là 4 (mini-dimension) cộng 1 (outrigger ghi đè).

Type 7 = Type 1 + Type 2 (dual key trong fact)

Type 6 nhét cả hai góc nhìn vào một dòng dimension. Type 7 chọn cách khác: để fact table mang hai khoá trỏ về cùng một dimension Type 2.

• Surrogate key (customer_sk) — trỏ vào đúng dòng phiên bản có hiệu lực khi giao dịch xảy ra → góc nhìn Type 2 / as-of / "as-was".
• Durable key (natural/business key bền, ví dụ customer_id) — join tới dòng hiện hành (is_current = TRUE) của khách → góc nhìn Type 1 / "as-is" hôm nay.

Cùng một fact, người phân tích chọn khoá nào thì ra góc nhìn ấy — không phải bảo trì thêm cột "current" trên từng dòng như Type 6. Đổi lại, câu truy vấn "as-is" phải join có điều kiện is_current = TRUE, và ETL không phải cập nhật lan (không đụng vào các dòng lịch sử). Đây là lựa chọn được ưa dùng khi dimension có nhiều thuộc tính và việc lan cột "current" của Type 6 trở nên đắt. So sánh trực diện:

Nguyên tắc chọn nâng cao: đổi nhanh & tránh nở dim → Type 4/5; cần cả as-of lẫn as-is trong một câu, dimension gọn → Type 6; dimension rộng, muốn ETL nhẹ và linh hoạt góc nhìn → Type 7. Chi tiết bus matrix / conformed dimension ở Dimensional nâng cao.

SCD Type 2 nhìn bằng effective dating

Trước khi bước sang bi-temporal, cần đóng đinh lại effective dating — cặp valid_from / valid_to là bộ khung của Type 2 và cũng là một trong hai trục của bi-temporal.

Ba dòng nối tiếp, khoảng [valid_from, valid_to) nửa mở (bao gồm đầu, loại trừ cuối) nên không chồng lấn và không hở. Một giao dịch ngày 2024-05-10 rơi vào phiên bản sk=2 (Vàng) — đó là as-of. Cặp effective date này trả lời "sự thật có hiệu lực khi nào trong thế giới thực" — chính là trục valid time.

MERGE SCD2 — đóng dòng cũ và mở dòng mới trong một câu

Vòng đời Type 2 kinh điển là hai bước (UPDATE đóng dòng cũ → INSERT dòng mới). MERGE gói được cả hai, nhưng có một nút thắt: một dòng nguồn thay đổi cần vừa update dòng cũ vừa insert dòng mới, trong khi mỗi dòng nguồn của MERGE chỉ khớp một nhánh. Thủ thuật chuẩn: nhân đôi nguồn — một bản có join key để khớp-và-đóng, một bản có join key NULL để không khớp-và-chèn.

MERGE INTO dim_customer_scd2 AS d
USING (
    -- Bản 1: giữ business key -> sẽ MATCH dòng hiện hành để ĐÓNG lại
    SELECT s.customer_id AS join_key, s.customer_id, s.full_name, s.tier, s.province
    FROM   stg_customer s
    UNION ALL
    -- Bản 2: join_key = NULL -> KHÔNG MATCH -> INSERT phiên bản mới,
    -- chỉ phát sinh cho khách thực sự có thay đổi thuộc tính
    SELECT NULL::varchar AS join_key, s.customer_id, s.full_name, s.tier, s.province
    FROM   stg_customer s
    JOIN   dim_customer_scd2 c
      ON   c.customer_id = s.customer_id AND c.is_current
    WHERE  c.tier IS DISTINCT FROM s.tier          -- phát hiện thay đổi
        OR c.province IS DISTINCT FROM s.province
) AS src
ON  d.customer_id = src.join_key AND d.is_current
-- Nhánh MATCH: dòng hiện hành mà thuộc tính đã đổi -> đóng phiên bản
WHEN MATCHED AND (d.tier IS DISTINCT FROM src.tier
                  OR d.province IS DISTINCT FROM src.province) THEN
    UPDATE SET valid_to = CURRENT_DATE, is_current = FALSE
-- Nhánh NOT MATCHED: chèn phiên bản mới (từ bản 2, và cả khách hoàn toàn mới)
WHEN NOT MATCHED THEN
    INSERT (customer_id, full_name, tier, province, valid_from, valid_to, is_current)
    VALUES (src.customer_id, src.full_name, src.tier, src.province,
            CURRENT_DATE, NULL, TRUE);

Vài điểm cần đúng: dùng IS DISTINCT FROM để so sánh null-safe (tránh sót thay đổi khi có NULL); mẻ chạy phải idempotent — chạy lại cùng staging không được tạo phiên bản trùng, nhờ điều kiện "chỉ đóng/mở khi thuộc tính thực sự khác". Đây cũng chính là mảnh ghép MERGE/upsert của Batch — Incremental & CDC: CDC bắt thay đổi ở nguồn, MERGE biến chúng thành phiên bản SCD2 ở kho.

Bi-temporal: hai trục thời gian

SCD Type 2 mới chỉ mô hình hoá một trục: valid time. Nhưng hãy hình dung tình huống ngân hàng rất đời: ngày 10/03 chị Lan lên hạng Vàng, nhưng chi nhánh mãi 25/03 mới nhập liệu. Nếu chỉ có valid time, kho ghi "Vàng có hiệu lực từ 10/03" và... xoá sạch dấu vết rằng suốt 10/03–25/03 hệ thống vẫn tưởng chị là Bạc. Với kiểm toán, thanh tra, hay tái dựng một báo cáo đã phát hành, việc mất dấu này là không chấp nhận được.

Bi-temporal modeling giải quyết bằng cách gắn cho mỗi bản ghi hai cặp thời gian độc lập:

• Valid time (business time / effective time): sự thật có hiệu lực trong thế giới thực khi nào. Cặp valid_from / valid_to. Đây là trục ta có thể "sửa quá khứ" — nói rằng một điều đã đúng từ hôm qua.
• Transaction time (system time / record time): kho ghi nhận sự thật đó khi nào, và tin nó tới khi nào. Cặp tx_from / tx_to. Trục này chỉ tiến, không sửa — nó là nhật ký bất biến của "kho biết gì, vào lúc nào".

Đọc sơ đồ theo hai trục:

• Trước 25/03, kho chỉ có bản (1): "chị Lan là Bạc, hiệu lực từ 2023 tới vô cùng". Mọi báo cáo chạy trong khoảng này đều thấy Bạc — và ta tái dựng được đúng như vậy nhờ transaction time.
• Ngày 25/03 nghiệp vụ đính chính. Kho không xoá bản (1); nó đóng transaction time của (1) tại 25/03 (kết thúc "thời kỳ kho tin điều này"), rồi ghi thêm: bản (2) là bản Bạc đã được sửa lại chỉ có hiệu lực tới 2024-03-10, và bản (3) là Vàng hiệu lực từ 2024-03-10.
• Từ 25/03 trở đi, hỏi "hôm nay ta tin chị Lan hạng gì tại 2024-03-15?" → Vàng (bản 3). Hỏi "báo cáo phát hành ngày 20/03 đã nói gì?" → Bạc (bản 1, vì tại thời điểm transaction đó kho chỉ biết bản 1). Cả hai câu đều trả lời được, chính xác.

Đây là năng lực mà một trục không bao giờ cho được: "as-of business date, as-known-at system date" — sự thật đúng theo trục valid, đồng thời đã biết tới đâu theo trục transaction.

Truy vấn bi-temporal

Một câu bi-temporal luôn chốt cả hai trục:

-- "Theo hiểu biết của kho vào ngày 2024-03-20, hạng của KH001 vào 2024-03-15 là gì?"
SELECT customer_id, tier
FROM   dim_customer_bitemporal
WHERE  customer_id = 'KH001'
  -- chốt trục VALID: sự thật có hiệu lực tại 2024-03-15
  AND   DATE '2024-03-15' >= valid_from AND DATE '2024-03-15' < valid_to
  -- chốt trục TRANSACTION: theo những gì kho đã biết tính tới 2024-03-20
  AND   DATE '2024-03-20' >= tx_from    AND DATE '2024-03-20' < tx_to;
-- Kết quả: Bạc (vì đính chính chỉ xảy ra ngày 25/03, sau mốc as-known 20/03)

Đổi mốc transaction sang 2024-04-01 (sau đính chính) thì cùng câu đó trả về Vàng. Bỏ hẳn điều kiện tx_* (dùng tx_to vô cực) là quay về SCD2 một trục — nghĩa là bi-temporal là siêu tập của Type 2: bạn có thể "chiếu" xuống một trục bất cứ lúc nào. Chuẩn SQL:2011 chính thức hoá khái niệm này với PERIOD FOR SYSTEM_TIME (transaction time) và application-time period (valid time); một số CSDL thương mại hỗ trợ trực tiếp, còn trên Postgres ta hiện thực bằng bốn cột như trên.

Late-arriving: khi dữ liệu về trễ

Bi-temporal cho ta khung để nói về độ trễ giữa hai trục. Hai biểu hiện thực tế:

Late-arriving dimension (early-arriving fact)

Fact về trước khi dimension tương ứng kịp về — một giao dịch của khách mà dim_customer chưa có dòng nào. Không được để join rớt fact. Cách xử lý (nối tiếp phần Unknown member ở bài nền):

1. Tạo trước một dòng dimension "khung" (inferred member): chỉ có business key, các thuộc tính để "Unknown", cấp surrogate key ngay.
2. Cho fact trỏ vào surrogate key đó — fact không rớt, báo cáo vẫn cân.
3. Khi dữ liệu dimension thật về: nếu là thuộc tính Type 1 → cập nhật đè lên dòng khung; nếu Type 2 → chèn phiên bản đúng với valid_from lùi về thời điểm thật và chỉnh lại tham chiếu của các fact đã trỏ nhầm.

Late-arriving fact

Bản ghi fact về trễ so với thời điểm nó thực sự xảy ra — giao dịch phát sinh 30/06 nhưng tới 03/07 mới vào kho. Nguy hiểm ở chỗ: nếu cứ join với dimension theo trạng thái hiện tại, ta gán sai phiên bản. Xử lý đúng: join dimension theo event time của fact (thời điểm giao dịch), không theo lúc nạp — tức tra as-of [valid_from, valid_to) tại event_date. Với ngân hàng, đây chính là ranh giới quyết định một giao dịch back-dated được tính vào hạng khách nào và kỳ EOD nào (xem Batch — Incremental & CDC cho watermark/backfill khi reprocess kỳ đã đóng).

Retroactive change: sửa quá khứ cho đúng

Đây là bài toán khó nhất và là lý do bi-temporal tồn tại. Retroactive change = một sự thật hoá ra đã đúng/sai từ trong quá khứ, và ta phải sửa lại lịch sử. Ví dụ: đối soát phát hiện lãi suất của một khoản vay nhập sai từ ba tháng trước; phải sửa lại và tính lại toàn bộ lãi đã tính.

Vấn đề: có những báo cáo đã phát hành, đã gửi cơ quan quản lý dựa trên số cũ. Nếu chỉ dùng valid time và "sửa đè quá khứ", ta phá mất khả năng tái dựng báo cáo đã phát hành — một hành vi không kiểm toán được. Ba chiến lược, xếp theo mức độ bảo toàn lịch sử:

• Type 1 hồi tố (đè quá khứ) — sửa thẳng, nhanh, nhưng mất dấu hoàn toàn "trước đây từng ghi gì". Chỉ chấp nhận cho sửa lỗi vụn không ảnh hưởng báo cáo.
• Type 2 hồi tố (chèn phiên bản với valid_from lùi về) — giữ đúng theo valid time, tái tính được số đúng theo mọi mốc business, nhưng vẫn không nói được "kho đã từng tin gì" nếu ai đó chạy lại báo cáo cũ.
• Bi-temporal — cách duy nhất bảo toàn cả hai: đóng transaction time của bản cũ (giữ nguyên để tái dựng báo cáo đã phát hành), mở phiên bản mới với valid time lùi về. Vừa cho số đúng-hôm-nay, vừa cho số đã-phát-hành-hôm-đó. Đây là chuẩn mực cho dữ liệu chịu kiểm toán/thanh tra.

Nguyên tắc vàng: transaction time không bao giờ được sửa hồi tố — nó là nhật ký bất biến. Chỉ có valid time mới được phép "nói về quá khứ khác đi". Giữ kỷ luật này thì mọi retroactive change đều append-only và tái dựng được — hết sức quan trọng với dữ liệu ngân hàng và là tinh thần cốt lõi của Data Vault 2.0 (insert-only, audit từ gốc).

Use case thực tế: đính chính lãi suất khoản vay (bi-temporal)

Số liệu dưới đây là minh hoạ. Ngày 2026-07-15, đối soát tại NCB phát hiện một khoản vay đã bị nhập sai lãi suất kể từ 2026-04-01: ghi 9.5%/năm trong khi hợp đồng đúng là 9.0%/năm. Trong ba tháng đó, hệ thống EOD đã tính và phát hành sao kê lãi hàng tháng theo mức 9.5%; báo cáo phân loại nợ quý 2 cũng đã nộp.

Nếu dim_loan_terms chỉ có valid time và ta sửa đè:

• Số hôm nay đúng (9.0%), nhưng không ai tái dựng nổi sao kê tháng 4–6 đã gửi khách (số 9.5%), cũng không giải trình được với thanh tra vì sao báo cáo quý 2 lệch so với hệ thống hiện tại.

Với dim_loan_terms bi-temporal, xử lý ngày 2026-07-15:

1. Đóng transaction time của bản "9.5%" tại 2026-07-15 (không xoá) — giữ nguyên để tái dựng mọi thứ đã phát hành trước ngày này.
2. Chèn phiên bản đúng "9.0%" với valid_from = 2026-04-01 (lùi về), tx_from = 2026-07-15.
3. Reprocess lãi tháng 4–6 theo 9.0%, phát hành sao kê đính chính; chênh lệch lãi được hoàn/điều chỉnh cho khách.

-- (1) Đóng transaction time của bản cũ — GIỮ để tái dựng báo cáo đã phát hành
UPDATE dim_loan_terms
SET    tx_to = DATE '2026-07-15'
WHERE  loan_id = 'LN7788' AND interest_rate = 9.5 AND tx_to = DATE '9999-12-31';

-- (2) Bản đúng, valid_from lùi về thời điểm hợp đồng thực sự có hiệu lực
INSERT INTO dim_loan_terms
    (loan_id, interest_rate, valid_from, valid_to, tx_from, tx_to)
VALUES
    ('LN7788', 9.0, DATE '2026-04-01', DATE '9999-12-31',
                    DATE '2026-07-15', DATE '9999-12-31');

-- Tái dựng sao kê tháng 5 "như đã phát hành" (as-known 2026-06-01) -> vẫn 9.5%
SELECT interest_rate
FROM   dim_loan_terms
WHERE  loan_id = 'LN7788'
  AND  DATE '2026-05-31' >= valid_from AND DATE '2026-05-31' < valid_to
  AND  DATE '2026-06-01' >= tx_from    AND DATE '2026-06-01' < tx_to;   -- 9.5%

Sau xử lý: hỏi "lãi suất đúng cho tháng 5 là gì" (as-known hôm nay) → 9.0%; hỏi "sao kê tháng 5 đã gửi khách nói gì" (as-known 2026-06-01) → 9.5%. Cả hai đều trả lời được, và toàn bộ thao tác là append-only — kiểm toán lần theo được từng bước. Bối cảnh mô hình hoá dữ liệu ngân hàng đầy đủ (khoản vay, phân loại nợ, EOD) ở Banking modeling.

Ghi nhớ

• Bộ SCD đầy đủ là Type 0–7; các type cao là phép cộng: 5 = 4 + 1 (mini-dim + outrigger Type 1), 6 = 1 + 2 + 3 (hybrid một dòng), 7 = 1 + 2 (fact mang cả surrogate key lẫn durable key).
• Type 6 vs 7: Type 6 lan cột "current" lên mọi dòng (ETL nặng, dim gọn); Type 7 chỉ đóng/mở dòng và chọn góc nhìn qua khoá khi truy vấn (ETL nhẹ, dim rộng).
• SCD Type 2 chỉ mô hình một trục — valid time (valid_from/valid_to, nửa mở [from, to)). Bi-temporal thêm trục thứ hai — transaction time (tx_from/tx_to).
• Valid time có thể "sửa quá khứ"; transaction time chỉ tiến, không bao giờ sửa hồi tố — nó là nhật ký bất biến "kho biết gì, lúc nào".
• Truy vấn bi-temporal chốt cả hai trục: as-of business date và as-known system date; bỏ trục transaction là chiếu về SCD2.
• Late-arriving dimension → dựng inferred member (khung) rồi vá sau; late-arriving fact → join dimension theo event time, không theo lúc nạp.
• Retroactive change: bi-temporal là cách duy nhất giữ được cả "số đúng hôm nay" lẫn "số đã phát hành hôm đó" — thao tác append-only, tái dựng và kiểm toán được.
• MERGE SCD2 gói đóng-cũ/mở-mới bằng thủ thuật nhân đôi nguồn (join key NULL); dùng IS DISTINCT FROM để null-safe và giữ idempotent — mảnh ghép của CDC/merge ở Batch — Incremental & CDC.

Nguồn tham khảo

• Ralph Kimball & Margy Ross — The Data Warehouse Toolkit (3rd Edition, Wiley) — chương Slowly Changing Dimensions, định nghĩa Type 0–7 (gồm Type 5 mini-dimension + outrigger và Type 7 dual key).
• Kimball Group — Dimensional Modeling Techniques (kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques) — mục SCD Techniques, Late Arriving Dimensions, Type 5/6/7.
• Tom Johnston — Managing Time in Relational Databases: How to Design, Update and Query Temporal Data (Morgan Kaufmann) — tham chiếu chuyên sâu về bi-temporal (assertion time / effective time).
• Richard Snodgrass — Developing Time-Oriented Database Applications in SQL (Morgan Kaufmann) — nền tảng valid time vs transaction time; miễn phí trên trang tác giả.
• ISO/IEC — chuẩn SQL:2011 temporal features: PERIOD FOR SYSTEM_TIME (transaction time) và application-time periods (valid time).
• Martin Kleppmann — Designing Data-Intensive Applications (O'Reilly) — thảo luận về event time, xử lý dữ liệu về trễ và tính bất biến của log.
• PostgreSQL Documentation — MERGE (postgresql.org/docs/current/sql-merge.html) — cú pháp dùng trong ví dụ SCD2 (Postgres 15+).