題幹與適用場景
這是維度建模與歷史語義題。客戶維度的地址變更不能只看「最新值」：財務報表可能要回答某一天客戶屬於哪個地區。假設來源系統每天傳送完整快照、沒有變更日誌，目標表需要支援目前狀態、歷史回溯與可重播處理。Dataquest 將 SCD Type 1、2、3 列為資料工程面試題；AWS 的示例也用沒有 CDC 的完整檔案實作 Type 2。
面試官考察點
- 能否先定義報表要回答「現在」還是「當時」，再選擇 SCD 類型。
- 能否區分覆蓋舊值、追加版本、保留一個前值的儲存語義。
- 能否用自然鍵、代理鍵、有效時間與目前標誌維護唯一性。
- 能否處理遲到快照、重複檔案、刪除、重播與並行寫入，而不是只寫一條
UPDATE。
回答前需要釐清的問題
- 報表是否必須按事件發生時間還原歷史，還是只要目前画像？
- 來源快照是否帶版本號、擷取時間或水位，是否可能亂序抵達？
- 刪除是業務刪除、暫時缺席，還是來源系統漏欄位？
- 允許維度歷史修訂事實表外鍵嗎？是否需要稽核、重播與冪等重跑？
30 秒回答框架
「我先把時間語義寫成契約：Type 1 只保留目前值，Type 2 為每次業務變更新增版本並保存 validfrom、validto，Type 3 只保留一個或少量前值。若要點時分析，我選 Type 2，用自然鍵找目前列、代理鍵連接事實表，並由快照水位識別遲到與刪除。批次先落地並去重，以交易關閉舊版本、插入新版本；重複批次只允許產生相同結果。」
分步驟深入解答
第一步：先確定歷史問題
Type 1 適合更正或不需要歷史的画像，直接覆蓋；Type 2 適合稽核、收入歸屬與點時查詢，完整保留版本；Type 3 只適合「目前值與上一個值」這類固定窗口。若需求說「回看上季的客戶地區」，Type 1 與 Type 3 都會丟失資訊，必須選 Type 2。
第二步：設計 Type 2 約束
維度列包含代理鍵、自然鍵、屬性、validfrom、validto、iscurrent 與來源批次水位。同一自然鍵最多一列 iscurrent = true；有效區間不能重疊。AWS 示例使用開始/結束日期、目前標誌與邏輯刪除保留完整歷史，Microsoft 的 Type 2 設定也要求自然鍵、代理鍵、兩個日期與活動標誌。
第三步：從完整快照推導變更
先把檔案寫入不可變的 landing 表，記錄檔案雜湊、擷取時間與批次序號。對每個自然鍵與目前維度做雜湊比較：新鍵插入、屬性變更關閉舊列並插入新列、相同雜湊跳過。快照中缺少的鍵只有在「這是完整快照」契約成立時才能標記刪除；增量檔案不能用缺少推斷刪除。
第四步：處理遲到與亂序
為每個批次保存單調水位，拒絕或隔離低於已套用水位的檔案。若業務允許歷史修訂，可在交易中找到受影響時間區間，重新切分版本並重算事實連接；若不允許修改已發布報表，就把遲到批次放入修訂佇列並發布影響範圍。不能只按抵達時間覆蓋，否則會把舊事實錯誤顯示成新狀態。
第五步：刪除、重跑與並行
完整快照缺少的自然鍵產生邏輯刪除版本或關閉目前列，保留稽核記錄。批次表以檔案雜湊或來源批次做冪等鍵；同一批次重跑不得重複插入版本。使用單一自然鍵分區的交易或合併鎖，確保關閉舊列與插入新列原子完成。並行批次必須按水位排序，不能讓較舊批次重新開啟已關閉區間。
可驗證的 SQL 處理骨架
-- current_dim: one current row per customer_id
BEGIN;
UPDATE dim_customer AS old
SET valid_to = :as_of,
is_current = FALSE,
source_batch = :batch_id
FROM stage_customer AS incoming
WHERE old.customer_id = incoming.customer_id
AND old.is_current = TRUE
AND old.row_hash <> incoming.row_hash;
INSERT INTO dim_customer (
customer_key, customer_id, city, valid_from, valid_to,
is_current, source_batch, row_hash
)
SELECT nextval('dim_customer_key_seq'), incoming.customer_id,
incoming.city, :as_of, NULL, TRUE, :batch_id, incoming.row_hash
FROM stage_customer AS incoming
LEFT JOIN dim_customer AS old
ON old.customer_id = incoming.customer_id
AND old.is_current = TRUE
WHERE old.customer_id IS NULL
OR old.row_hash <> incoming.row_hash;
COMMIT;生產實作還要給 batchid 建立唯一約束，並在提交前檢查每個自然鍵只有一列目前記錄、區間不重疊與輸入水位合法。SQL 骨架假設 stagecustomer 已按批次去重；它不是處理亂序的完整方案。
高品質示範回答
「我先問報表要保留哪種歷史。只要目前值就用 Type 1；要回答某個過去日期的狀態就用 Type 2；只需比較目前與上一個值才用 Type 3。對每天完整快照，我會先落地原始檔案並以批次水位與檔案雜湊去重，用自然鍵和屬性雜湊識別新增、變更與未變更。Type 2 更新必須在同一交易中關閉舊列、插入新版本，並保證目前列唯一、時間區間不重疊。缺少的鍵只有在完整快照契約下才代表刪除；遲到批次按水位隔離，是否回修歷史由報表發布契約決定。」
常見錯誤
- 看到「維度變更」就預設 Type 2 → 儲存與查詢成本上升 → 先問歷史查詢契約。
- 用抵達時間當有效時間 → 亂序快照污染歷史 → 使用業務生效時間或明確的修訂佇列。
- 把增量檔案中缺少的鍵當刪除 → 正常增量被誤刪 → 只有完整快照才能推斷缺少刪除。
- 只更新
is_current不插入代理鍵版本 → 事實無法點時連接 → 關閉舊列並新增版本。 - 沒有批次冪等鍵 → 重跑生成重複歷史 → 以檔案雜湊或批次號做唯一約束。
追問及應對
遲到事件要回修已發布的報表嗎？
先確認報表是否允許修訂。允許時按業務時間重切有效區間並重算受影響事實；不允許時保留原結果，發布帶影響範圍的修訂批次。兩種契約都要記錄原始批次，避免靜默改數。
同一客戶在一個快照出現兩列怎麼辦？
把它視為輸入品質錯誤，按業務版本或來源序號確定唯一記錄；無法確定時隔離批次並告警。任意 LIMIT 1 會讓歷史結果無法重現。
Type 3 什麼時候比 Type 2 更合適？
只需要「目前地區」和「上一個地區」，且不會查詢更早版本時，Type 3 的欄位模型更省空間、查詢更簡單。需求一旦擴大到任意日期回溯，就應遷移到 Type 2 並說明歷史補建成本。
如何驗證沒有區間重疊？
按自然鍵排序，檢查相鄰版本的結束時間不晚於下一版本的開始時間，並檢查每個自然鍵最多一列目前行。把檢查作為批次發布門檻，而不是事後抽樣。