題目與適用範圍
客戶來源每天傳送一份包含 5000 萬列的完整快照，約 1% 被選中保留歷史的屬性每天發生變化。訂單持續到達，而來源端修正最多會遲到 3 天。請設計一個緩慢變化維度 Type 2 客戶維度，使每筆訂單都能關聯到業務發生時有效的客戶屬性，同時正確處理刪除、安全重跑，並讓回填後的歷史仍可稽核。
核心任務是維度建模和時間正確性。變更資料擷取可以提供輸入事件，但不會替你定義目標歷史。回答需要決定哪些屬性值得使用 Type 2，區分持久業務鍵和版本代理鍵，定義互不重疊的有效區間，並說明事實表如何解析到正確版本。
規模會暴露草率設計。每天 1% 的變化率約等於每天新增 50 萬個版本、每年新增 1.825 億個版本。若每個新增版本平均占 300 位元組，則每年約增加 54.75 GB 原始列資料，尚未計算索引、副本、中繼資料和壓縮。這些數字是面試假設和容量估算起點，不是產品儲存承諾。
面試官在考察什麼
基礎回答會說「關閉舊列，再插入新列」。高品質回答會先定義歷史語義。Type 1 直接覆寫值，舊狀態隨之遺失；Type 2 為新狀態建立帶新代理鍵的版本，並保留舊版本。並非每個來源欄位都應觸發新版本：修正大小寫可以使用 Type 1，而用於歷史報表的銷售區域或定價分層通常適合 Type 2。
第二個訊號是時間邊界是否嚴謹。候選人應明確使用 [validfrom, validto) 這樣的左閉右開區間，保證同一業務鍵的版本不重疊，並且只能有一個目前版本。時間 t 的事實應關聯開始時間不晚於 t、結束時間晚於 t 的版本。如果需求依據業務生效時間，卻用載入時間建立歷史，遲到資料就會被靜默歸入錯誤版本。
面試官也會檢查生產行為。相同批次重跑不能再造一個版本；不完整快照不能把幾千萬客戶判成刪除；關閉目前列與插入替代列必須原子完成；遲到修正可能需要拆分歷史區間，而非只改目前列。如果業務既要查詢「值何時生效」，又要查詢「資料倉儲何時得知」，普通 SCD Type 2 不夠，需要雙時態模型。
回答前先釐清的問題
- 哪些屬性會影響歷史分析？ 只追蹤已約定的 Type 2 欄位。稽核欄位和擷取時間不應建立業務版本。
customerid是否穩定且永不重複使用？ 它是業務鍵，每個歷史版本另有獨立的customersk代理鍵。- 來源端是否提供可信生效時間或序列？ 每日快照只能證明何時觀察到某個值，不一定能證明它何時生效。沒有可靠來源時間，資料倉儲不能憑空建立正確的回溯邊界。
- 每份快照是否有明確的完整標記？ 只有完整性、列數和控制總數檢查通過後，才可把缺失解釋為刪除。部分擷取不是刪除流。
- 刪除應表示什麼？ 本方案關閉活動版本，並插入
is_deleted = true的目前墓碑版本，明確保留刪除邊界。 - 事實表是在擷取時儲存
customer_sk，還是查詢時按時間關聯？ 事實載入時解析代理鍵可簡化後續查詢；時間關聯仍適合回填和驗證。 - 修正是否允許改寫過去的業務歷史？ 若允許，必須保留原始來源版本和稽核記錄，因為回填可能合理改變舊報表結果。
- 是否同時保留知識時間和業務時間？ 若稽核需要兩者，應分別建模有效時間與系統時間，而非強塞進一個區間。
30 秒回答框架
我會先定義業務鍵、Type 2 屬性、可信生效時間和刪除策略。每個版本使用獨立代理鍵和
左閉右開的 [valid_from, valid_to) 區間，每個客戶只有一個目前版本。我先暫存並去重
一份已驗證完整的快照，只對追蹤屬性計算雜湊，再把記錄分為未變化、新增、變化和刪除。
屬性變化時，在同一個原子操作中關閉舊版本並插入新版本；批次 ID 和來源版本保證重跑冪等。
事實表按訂單發生時間解析有效版本。遲到修正需要拆分受影響的歷史區間；發佈前拒絕目前列
重複、區間重疊、事實外鍵錯誤和異常刪除激增。分步拆解
第一步：載入前先定義粒度和欄位
粒度是「一個客戶在一個有效區間內的一個版本」。實用表結構可以包含：
dim_customer {
customer_sk // 目前版本的代理主鍵
customer_id // 來源端持久業務鍵
segment
sales_region
valid_from
valid_to // null 表示開放結束
is_current
is_deleted
change_hash // 只包含 Type 2 屬性的規範化雜湊
source_version
load_batch_id
}全鏈路統一使用 [validfrom, validto)。如果變化在 2026-07-10T09:00:00Z 生效，舊列就在該時刻結束，新列也從該時刻開始。左閉右開約定使邊界只屬於一個版本。時間戳還要統一時區和精度；混用日期、本地時間與 UTC 會製造假缺口或重複匹配。
業務鍵把多個版本歸為同一實體，代理鍵標識一個不可變的歷史狀態，並由事實表儲存為外鍵。iscurrent 只是查詢便利欄位，不能成為另一套真相，必須與 validto IS NULL 一致。雜湊只是比較最佳化。應先規範化空值、類型、Unicode 和欄位順序，同時保留真實追蹤欄位用於解釋與稽核。
第二步：估算掃描和寫入路徑
每日快照需要掃描 5000 萬筆來源記錄。變化率為 1% 時：
50,000,000 × 1% = 500,000 個新版本/天
500,000 × 365 = 182,500,000 個新版本/年
182,500,000 × 300 位元組 ≈ 54.75 GB/年的原始列資料這裡要分清掃描成本與變化寫入成本。快照只暫存一次，只投影必要欄位，再按業務鍵與維度目前列比較。分割或叢集應服從真實查詢與維護模式，例如業務鍵用於查找、有效時間用於裁剪；不要機械建立數千個很小的日分割區。索引、列式壓縮、保留週期和回填放大都應使用接近生產的資料實測。
第三步：讓普通批次確定、原子且冪等
使用不可變的 snapshotid 落地擷取結果。觸碰目標表前，檢查完成標記、結構描述、業務鍵唯一性、列數和控制總數。按 customerid 使用可信來源版本去重；若兩列排序等級相同但內容不同，應把它當成輸入錯誤，而非隨意挑一列。
規範化追蹤屬性，並將每筆暫存記錄與目標表目前版本比較：
- 沒有目前業務鍵：插入第一個版本。
- 追蹤值相同：不做任何事；重新整理
loaded_at不應製造歷史。 - 追蹤值不同：在可信生效時間關閉目前列，並插入新的目前版本。
- 目前目標鍵不在已驗證完整的快照中：關閉該列，並插入目前墓碑版本。
每個變化鍵的關閉與插入必須位於同一個目標交易或原子表操作中。目前版本唯一限制可以阻止兩個並行載入器同時造版本。記錄 snapshotid、loadbatchid 和 sourceversion，已套用的來源版本應被拒絕或識別為無操作。這樣即使提交後的確認遺失，重試也只會收斂，不會重複歷史。
第四步：按業務事件時間解析事實
載入訂單時，使用訂單業務時間解析客戶版本，再把 customer_sk 固化到事實表。一個與具體產品無關的時點查詢形態如下：
SELECT d.customer_sk
FROM dim_customer AS d
WHERE d.customer_id = :customer_id
AND d.is_deleted = false
AND d.valid_from <= :order_time
AND (d.valid_to > :order_time OR d.valid_to IS NULL);不等式表達左閉右開區間。查詢必須恰好傳回一列。零匹配需要明確的未知成員或隔離策略；多列匹配則證明歷史已損壞。遲到事實應使用事件時間，而非擷取時間。Kimball 的 Type 2 模式讓事實儲存版本代理鍵，正是為了使已載入事實保留當時有效的歷史輪廓。
第五步：把遲到修正當成區間手術
假設資料倉儲現有記錄是 7 月 1 日起為西雅圖、7 月 12 日起為丹佛；7 月 14 日收到可信修正，說明丹佛實際從 7 月 10 日起生效。只更新目前列會讓 7 月 10 日和 11 日仍然錯誤。應找到包含 7 月 10 日的版本，在 7 月 10 日關閉西雅圖，並把丹佛的開始時間移到 7 月 10 日。如果修正在既有區間中引入第三個值，就拆分該區間並保留下個已知邊界。
按來源版本順序套用修正，並對同一業務鍵加鎖或序列化。保留原始輸入和修正稽核，包括舊區間、新區間、來源版本、批次和原因。如果業務約定要求事實外鍵反映修正後的歷史，就重新解析受影響時間窗內的事實。
這裡存在資訊上限：7 月 14 日首次觀察到的快照，不能自行證明某值在 7 月 10 日已經生效，除非來源端攜帶可信業務時間或其他有序記錄。此時應使用 7 月 14 日作為觀察時間，或隔離等待確認；靜默回填到 7 月 10 日是在製造虛假精度。如果資料倉儲同時需要 7 月 10 日的有效時間和 7 月 14 日的知悉時間，就增加第二套系統時間區間，並明確稱為雙時態。
第六步：驗證不變量與執行護欄
每個批次完成後、發佈前檢查：
- 每個代理鍵唯一；
- 每個業務鍵恰好有一個目前版本，已刪除業務鍵也保留一個墓碑目前版本；
iscurrent與開放的validto一致；- 同一業務鍵的區間不重疊，有結束時間的列都滿足
validfrom早於validto； - 未變化的來源值不新增版本；
- 相同來源版本或批次重播不新增任何列；
- 每個非未知事實代理鍵都引用事實事件時間有效的維度列；
- 新增、變化、未變化與刪除數量能與暫存快照對帳。
監控快照完整性、重複鍵、變化率、刪除率、版本增長、被拒絕的遲到修正、事實零匹配與多匹配、載入延遲和重跑差異。若突然消失 4000 萬個業務鍵，應在它們變成 4000 萬個墓碑前失敗關閉。測試至少涵蓋新鍵、相同快照重播、普通變化、刪除後重建、遲到 3 天的修正、亂序來源版本、部分快照，以及關閉舊列和插入新列之間的當機。
高品質示例回答
「我會把粒度定義成每個客戶版本一列，而不是每個客戶一列。customerid 是持久業務鍵，每個版本都有新的 customersk。Type 2 欄位需要與分析人員約定，例如客戶分層和銷售區域；載入時間或格式修正不會產生歷史。每列使用左閉右開的 [validfrom, validto)，每個客戶恰好有一列目前版本。
來源端每天掃描 5000 萬列，約 50 萬列發生變化。這意味著每年約新增 1.825 億個版本；按每版本 300 位元組舉例，物理開銷前的原始增長約為 54.75 GB。我會只暫存一次快照，並只把它與維度目前列比較，避免每次聯結全量歷史。
每份快照都有穩定 ID，且必須通過完成標記、業務鍵唯一性、結構描述、列數和控制總數檢查。只對 Type 2 追蹤欄位做規範化雜湊。新鍵直接插入；雜湊相同不處理；發生變化時，在一個原子操作中關閉舊區間並插入新版本。只有經過驗證的缺失鍵才會關閉舊版本並插入刪除墓碑。來源版本和批次 ID 保證冪等，目前列唯一限制阻止並行重複版本。
訂單透過 validfrom <= ordertime 且 validto > ordertime 解析 customer_sk，空結束時間表示仍然有效。查詢必須傳回一個未刪除版本。遲到事實使用訂單時間，而不是到達時間。遲到的維度修正應定位到可信生效時間所在區間，拆分或調整該區間，並保留之後的已知邊界。如果來源端只有到達時間，我不會虛構提前 3 天的生效時間；如果業務既要業務時間又要資料倉儲知悉時間，我會提出雙時態模型。
發佈前，我會檢查每個業務鍵只有一個目前版本、區間無重疊、邊界有效、事實引用正確、來源目標數量可對帳，並驗證相同輸入重跑時零新增。異常刪除率或變化率會觸發阻斷，原始快照和修正稽核則保證回填可解釋、可重播。」
常見錯誤
- 對全部來源欄位計算雜湊 → 稽核時間戳會製造無意義版本 → 只對約定的 Type 2 屬性做規範化雜湊。
- 把自然鍵當維度主鍵 → 多個歷史版本發生鍵衝突 → 用業務鍵歸組，用代理鍵標識每個版本。
- 混用包含結束端點的區間 → 變化邊界上的事實匹配兩列 → 全鏈路統一
[validfrom, validto)。 - 把擷取時間當生效時間 → 遲到更新和事實關聯到錯誤狀態 → 使用可信業務時間，並說明缺失時的降級語義。
- 把快照中每個缺失列都判為刪除 → 部分擷取可能清空維度 → 套用缺失語義前要求完成標記和異常檢查通過。
- 分兩次提交關閉與插入 → 當機後會留下零個或兩個目前版本 → 原子完成版本切換，並限制目前列唯一。
- 每次重跑都建立版本 → 重試膨脹歷史並改變舊結果 → 持久化來源版本和批次標識，讓相同輸入成為無操作。
- 遲到修正只改目前列 → 較早事實仍掛在錯誤狀態 → 拆分或調整受影響歷史區間，並重新解析對應事實視窗。
- 看到兩個時間戳就稱為雙時態 → 兩者語義仍不清楚 → 明確有效時間和系統時間，並分別定義區間與修正規則。
- 只檢查列數 → 區間重疊和重複目前列仍可漏過 → 同時檢查時間、引用、冪等和對帳不變量。
延伸追問與回答
追問一：為什麼不把所有屬性都做成 Type 1？
Type 1 適用於舊值沒有分析意義的欄位，例如拼字修正。它會銷毀舊值，因此無法回答「這筆訂單當時屬於哪個區域」。應按報表語義分類屬性。同一維度可以讓部分欄位使用 Type 1、部分欄位使用 Type 2，但更新規則必須明確。
追問二：目前列應該用 valid_to = NULL，還是遠未來哨兵值？
兩者都可以。NULL 能直白表達「開放結束」，但查詢需要處理空值；最大支援時間之類的哨兵值可以簡化範圍篩選，卻可能洩漏到報表，或超出另一個引擎的日期範圍。選擇一種表示，讓 is_current 與之保持一致，並在所有查詢和連接器中驗證同一約定。
追問三：刪除後又使用同一業務鍵重建，怎麼辦？
在刪除時間關閉活動業務版本，並加入刪除墓碑；重建時關閉墓碑，再插入帶新代理鍵的活動版本。還要先確認來源端確實在重複使用同一個實體身分；若這個標識被分配給了另一個人，就應引入能區分實體的持久鍵。
追問四：來源端沒有可靠的 updated_at 怎麼辦？
像快照工具的 check 策略一樣，明確列出需要規範化比較的追蹤欄位。產生的區間從資料倉儲觀察到變化時開始，未必等於真實業務變化時間，必須記錄這一限制。如果必須得到準確業務生效歷史，就取得來源序列、稽核日誌、CDC 流或領域事件，而非編造時間戳。
追問五：它和 CDC 管道有什麼區別？
CDC 回答發生了哪些已提交新增、更新和刪除，以及它們的來源端順序。SCD Type 2 回答所選維度屬性如何成為歷史版本，以及事實表應引用哪個代理鍵。CDC 流可以驅動 SCD 載入器，快照也可以。可靠擷取本身不會防止有效區間重疊或錯誤時點關聯。
追問六：什麼時候應避免 Type 2 的列數增長？
不需要歷史分組的高頻屬性、大型自由文字，以及更適合表達為事件或事實的執行狀態，都不適合直接做 Type 2。根據查詢需求，可以改用 Type 1、獨立迷你維度、累積或週期事實，或專門歷史表。決策依據是分析語義和實測讀寫成本。
追問七：如何證明遲到回填沒有破壞歷史？
先把回填結果寫入暫存區或影子表，按業務鍵比較區間集合，並報告新增、拆分、縮短、延長和刪除的版本。斷言無重疊、只有一個目前列、未受影響的代理鍵穩定，並且事實鍵只在預期修正時間窗內變化。保留原始來源版本和批次清單，用於重播與回復。