在數據倉庫和商業智能領域，處理隨時間變化的數據是一個常見且具有挑戰性的任務。緩慢變化維度(Slowly Changing Dimensions, SCD)是解決這一問題的經典模式。本文將深入探討SQLMesh中SCD Type 2的實現方式、配置選項以及實際應用場景。

什么是SCD Type 2？

SCD Type 2是一種用于跟蹤維度表中記錄歷史變化的模型。它通過為每條記錄添加有效時間范圍(valid_from和valid_to)來實現這一點：

• valid_from: 記錄生效的起始時間(包含)
• valid_to: 記錄失效的結束時間(不包含)，最新記錄的valid_to設為NULL

這種設計允許我們不僅了解當前的數據狀態，還能追溯任何時間點的歷史數據。

SQLMesh中的SCD Type 2實現

SQLMesh提供了兩種實現SCD Type 2的方式：基于時間戳和基于列值比較。

SCD Type 2 By Time(基于時間戳)

這是SQLMesh推薦的方式，適用于源表包含"更新時間"(updated_at)字段的情況。

模型定義示例：

MODEL (name db.menu_items,kind SCD_TYPE_2_BY_TIME (unique_key id,)
);SELECTid::INT,name::STRING,price::DOUBLE,updated_at::TIMESTAMP
FROMstg.current_menu_items;

特點：

• 使用updated_at字段精確確定記錄變更時間
• 提高SCD Type 2表的準確性
• 需要源表包含時間戳字段

SCD Type 2 By Column(基于列值比較)

適用于源表不包含時間戳字段的情況，通過比較指定列的值變化來檢測變更。

模型定義示例：

MODEL (name db.menu_items,kind SCD_TYPE_2_BY_COLUMN (unique_key id,columns [name, price])
);SELECTid::INT,name::STRING,price::DOUBLE,
FROMstg.current_menu_items;

特點：

• 不需要updated_at字段
• 通過比較指定列的值變化來檢測變更
• 變更時間基于SQLMesh執行時間

高級配置選項

SQLMesh提供了豐富的配置選項來定制SCD Type 2行為：

共享配置

• unique_key: 用于標識源表和目標表之間行的唯一鍵
• valid_from_name/valid_to_name: 自定義有效時間列名
• invalidate_hard_deletes: 控制硬刪除記錄的處理方式
• batch_size: 批處理大小，用于歷史數據處理

SCD Type 2 By Time特有配置

• updated_at_name: 指定包含時間戳的列名
• updated_at_as_valid_from: 控制新記錄valid_from的設置方式

SCD Type 2 By Column特有配置

• columns: 指定需要檢查變化的列(使用*表示所有列)
• execution_time_as_valid_from: 控制新記錄valid_from的設置方式
• updated_at_name: 如果源表包含可用作valid_from的時間戳列

數據變更處理機制

記錄更新

當檢測到記錄變更時，SQLMesh會：

1. 將舊記錄的valid_to設置為變更時間
2. 插入新記錄，valid_from設置為變更時間，valid_to為NULL

記錄刪除

刪除處理取決于invalidate_hard_deletes設置：

• 默認(false): 刪除記錄的valid_to保持NULL，如果記錄重新添加，valid_to設置為新記錄的valid_from
• 設置為true: 刪除記錄的valid_to設置為SQLMesh運行開始時間，重新添加記錄不會改變valid_to

查詢SCD Type 2模型

SQLMesh提供了多種查詢SCD Type 2模型的方法：

1. 查詢當前版本記錄:

   SELECT * FROM menu_items WHERE valid_to IS NULL;
   

2. 查詢特定時間點的記錄:

   SELECT * FROM menu_items 
   WHERE id = 1
   AND '2020-01-02 01:00:00' >= valid_from
   AND '2020-01-02 01:00:00' < COALESCE(valid_to, CAST('2199-12-31 23:59:59+00:00' AS TIMESTAMP));
   

3. 查詢已刪除記錄:

   SELECT id, MAX(CASE WHEN valid_to IS NULL THEN 0 ELSE 1 END) AS is_deleted
   FROM menu_items
   GROUP BY id;
   

歷史數據處理與重置

SQLMesh支持處理包含歷史數據的源表，通過設置batch_size為1可以按時間順序處理每個間隔的數據。

重要提示：SCD Type 2模型設計上保護已捕獲的歷史數據，但可以通過設置disable_restatement為false來清除歷史并重新開始。這是一個危險操作，可能導致數據不可恢復。

實際應用示例

SCD Type 2按時間實現示例。假設您的源表初始有以下數據，并且invalidate_hard_deletes設置為true：

ID	名稱	價格	更新時間
1	雞肉三明治	10.99	2020-01-01 00:00:00
2	芝士漢堡	8.99	2020-01-01 00:00:00
3	薯條	4.99	2020-01-01 00:00:00

目標表當前為空，將會被物化為以下數據：

ID	名稱	價格	更新時間	有效起始時間	有效結束時間
1	雞肉三明治	10.99	2020-01-01 00:00:00	1970-01-01 00:00:00	NULL
2	芝士漢堡	8.99	2020-01-01 00:00:00	1970-01-01 00:00:00	NULL
3	薯條	4.99	2020-01-01 00:00:00	1970-01-01 00:00:00	NULL

現在假設您更新源表為以下數據：

ID	名稱	價格	更新時間
1	雞肉三明治	12.99	2020-01-02 00:00:00
3	薯條	4.99	2020-01-01 00:00:00
4	奶昔	3.99	2020-01-02 00:00:00

變更摘要：

• 雞肉三明治的價格從10.99美元上漲到12.99美元
• 芝士漢堡從菜單中移除
• 奶昔被添加到菜單中

假設您的管道在2020-01-02 11:00:00運行，目標表將被更新為以下數據：

ID	名稱	價格	更新時間	有效起始時間	有效結束時間
1	雞肉三明治	10.99	2020-01-01 00:00:00	1970-01-01 00:00:00	2020-01-02 00:00:00
1	雞肉三明治	12.99	2020-01-02 00:00:00	2020-01-02 00:00:00	NULL
2	芝士漢堡	8.99	2020-01-01 00:00:00	1970-01-01 00:00:00	2020-01-02 11:00:00
3	薯條	4.99	2020-01-01 00:00:00	1970-01-01 00:00:00	NULL
4	奶昔	3.99	2020-01-02 00:00:00	2020-01-02 00:00:00	NULL

在我們的最后一次更新中，假設您將源表更新為以下數據：

ID	名稱	價格	更新時間
1	雞肉三明治	14.99	2020-01-03 00:00:00
2	芝士漢堡	8.99	2020-01-03 00:00:00
3	薯條	4.99	2020-01-01 00:00:00
4	巧克力奶昔	3.99	2020-01-02 00:00:00

變更摘要：

• 雞肉三明治的價格從12.99美元上漲到14.99美元(很不錯！)
• 芝士漢堡被重新添加到菜單中，使用原始名稱和價格
• 奶昔名稱更新為"巧克力奶昔"

目標表將被更新為以下數據：

ID	名稱	價格	更新時間	有效起始時間	有效結束時間
1	雞肉三明治	10.99	2020-01-01 00:00:00	1970-01-01 00:00:00	2020-01-02 00:00:00
1	雞肉三明治	12.99	2020-01-02 00:00:00	2020-01-02 00:00:00	2020-01-03 00:00:00
1	雞肉三明治	14.99	2020-01-03 00:00:00	2020-01-03 00:00:00	NULL
2	芝士漢堡	8.99	2020-01-01 00:00:00	1970-01-01 00:00:00	2020-01-02 11:00:00
2	芝士漢堡	8.99	2020-01-03 00:00:00	2020-01-03 00:00:00	NULL
3	薯條	4.99	2020-01-01 00:00:00	1970-01-01 00:00:00	NULL
4	奶昔	3.99	2020-01-02 00:00:00	2020-01-02 00:00:00	2020-01-03 00:00:00
4	巧克力奶昔	3.99	2020-01-03 00:00:00	2020-01-03 00:00:00	NULL

注意：芝士漢堡從2020-01-02 11:00:00到2020-01-03 00:00:00期間被刪除，這意味著如果您在那個時間范圍內查詢表，將看不到芝士漢堡。這是基于源數據提供的菜單最準確的表示。如果芝士漢堡以原始更新時間戳2020-01-01 00:00:00被重新添加到菜單中，那么新記錄的valid_from時間戳將是2020-01-02 11:00:00，導致沒有時間段顯示該項目被刪除。由于在這種情況下更新時間戳沒有變化，很可能該項目是錯誤刪除的，這再次最準確地反映了基于源數據的菜單情況。

最后總結

SQLMesh中的SCD Type 2實現提供了靈活且強大的方式來處理緩慢變化維度。通過基于時間戳或列值比較的兩種方法，以及豐富的配置選項，可以滿足各種業務場景的需求。理解其工作原理和配置選項對于設計高效、準確的數據倉庫至關重要。

無論是需要精確跟蹤變更時間的場景，還是源表缺乏時間戳信息的情況，SQLMesh都提供了相應的解決方案。合理配置SCD Type 2模型，可以確保數據倉庫既能反映當前狀態，又能保留完整的歷史變更記錄，為業務分析提供堅實的數據基礎。