實作緩時變維度 (SCD) 類型 2

選擇 SCD Type 2 作為維度後，你需要在 Azure Databricks 中實作資料表結構並更改擷取邏輯。 本單元重點介紹如何建立 SCD 類型 2 資料表，以及如何實作 MERGE 模式以維護來源資料變更的版本歷程記錄。

建立 SCD 第二型表

以下的 SQL 指令建立一個客戶維度表，並包含 SCD Type 2 追蹤欄位：

CREATE TABLE sales.customer (
    customer_sk BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id STRING NOT NULL,
    customer_name STRING,
    email STRING,
    city STRING,
    region STRING,
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP NOT NULL,
    is_current BOOLEAN
)
USING DELTA
TBLPROPERTIES (
    delta.enableChangeDataFeed = true
);

此 GENERATED ALWAYS AS IDENTITY 子句為每個新列建立一個自動遞增的代理鍵。 啟用變更資料流讓下游流程能有效捕捉增量變更。

使用 MERGE 執行變更擷取

此 MERGE 語句提供了一種有效率的方式來實作 SCD 第二型邏輯，以處理來自來源系統的更新。 此敘述在單一交易中處理插入、更新及版本管理邏輯。

以下模式會關閉目前版本，並在變更時插入新版本：

MERGE INTO sales.customer AS target
USING (
    SELECT 
        source.customer_id,
        source.customer_name,
        source.email,
        source.city,
        source.region,
        current_timestamp() AS valid_from,
        CAST('9999-12-31' AS TIMESTAMP) AS valid_to,
        true AS is_current
    FROM staging.customers AS source
) AS updates
ON target.customer_id = updates.customer_id AND target.is_current = true
WHEN MATCHED AND (
    target.customer_name <> updates.customer_name OR
    target.email <> updates.email OR
    target.city <> updates.city OR
    target.region <> updates.region
) THEN UPDATE SET
    target.valid_to = current_timestamp(),
    target.is_current = false
WHEN NOT MATCHED THEN INSERT (
    customer_id, customer_name, email, city, region,
    valid_from, valid_to, is_current
) VALUES (
    updates.customer_id, updates.customer_name, updates.email, 
    updates.city, updates.region, updates.valid_from, 
    updates.valid_to, updates.is_current
);

-- Insert new versions for updated records
INSERT INTO sales.customer
SELECT 
    s.customer_id,
    s.customer_name,
    s.email,
    s.city,
    s.region,
    current_timestamp() AS valid_from,
    CAST('9999-12-31' AS TIMESTAMP) AS valid_to,
    true AS is_current
FROM staging.customers s
JOIN sales.customer h 
    ON s.customer_id = h.customer_id 
    AND h.valid_to = current_timestamp()
    AND h.is_current = false;

查詢歷史資料

一旦你實作了 SCD Type 2 表格，就可以查詢任何時間點存在的資料。 查詢模式取決於你的分析需求。

時間點查詢

要找出特定時刻的資料狀態，請在有效期間上進行篩選：

SELECT customer_id, customer_name, city
FROM sales.customer
WHERE valid_from <= '2023-06-15 12:00:00'
  AND valid_to > '2023-06-15 12:00:00';

此查詢為每個客戶傳回一個資料列—即在指定時間戳記時有效的版本。

追蹤記錄歷程記錄

欲查看特定實體的完整變更歷史：

SELECT customer_name, city, valid_from, valid_to
FROM sales.customer
WHERE customer_id = 'C-555'
ORDER BY valid_from;

此查詢顯示所有客戶 C-555 版本，顯示其屬性隨時間的變化。

使用 Delta Lake 時間移動

Delta Lake 提供內建的時間旅行功能，以輔助和增強具體的 SCD Type 2 表格設計。 你可以用 TIMESTAMP AS OF or VERSION AS OF 語法查詢先前的表格版本：

-- Query table state from 7 days ago
SELECT * FROM sales.customer 
TIMESTAMP AS OF '2024-01-15';

-- Query a specific table version
SELECT * FROM sales.customer 
VERSION AS OF 42;