緩やかに変化するディメンション (SCD) 型 2 を実装する

ディメンションに SCD タイプ 2 を選択した後、テーブル構造を実装し、Azure Databricks でキャプチャ ロジックを変更する必要があります。 このユニットでは、SCD タイプ 2 テーブルの作成と、ソース データの変更に応じてバージョン履歴を保持する MERGE パターンの実装に重点を置いています。

SCD Type 2 テーブルを作成する

次の SQL では、SCD タイプ 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 Type 2 ロジックを効率的に実装する方法を提供します。 このステートメントは、挿入、更新、およびバージョン管理ロジックを 1 つのトランザクションで処理します。

次のパターンは、現在のバージョンを閉じ、変更が発生したときに新しいバージョンを挿入します。

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';

このクエリは、指定されたタイムスタンプで有効だったバージョンである、顧客ごとに 1 行を返します。

実績の履歴

特定のエンティティの変更の完全な履歴を表示するには:

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 タイプ 2 テーブル設計を補完する組み込みのタイム トラベル機能が用意されています。 TIMESTAMP AS OFまたは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;