スタースキーマでdimensionテーブルをSCD Type2で実装するアプローチ

はじめに

最近チームで、スタースキーマでDimensionテーブルをSCD Type2形式で実装する方法について共有することがあり、せっかくなのでその設計方法についてブログにしようと思いました。

DimensionテーブルにSCD Type2を導入するとSnapshotの実行とfactテーブルとのジョイン整合性を保つ必要あります。ここの方法はシンプルな話ですが、ドキュメントが意外と少ないかもしれないので、お役に立てれば幸いです。

実装の流れ

実装は以下の4ステップで進めます：

1. ディメンションテーブルの最新断面を作成
2. 1のデータをsnapshotでSCD Type2化
3. 2のデータを使ってSCD Type2形式のディメンションテーブルを作成
4. ファクトテーブルにディメンションのサロゲートキーを付与する

今回は例として、従業員とそれらの従業員が日毎に会社に所属しているかの情報を扱うケースを想定します。従業員情報をディメンションテーブル、従業員の会社への所属情報をファクトテーブルとして実装します。
具体的には上記ステップの番号に対応して、SQLファイルを作成するイメージです。

1. base_dim_employee.sql: 従業員情報のディメンションのベースとなるテーブル（最新断面のみ）
2. scd2_employee.sql: 1のテーブルのSCD Type2化（dbt snapshotで実行）
3. dim_employee.sql: 従業員情報のSCD Type2形式のディメンションテーブル
4. fct_company_affiliation.sql: 日毎の従業員の会社への所属情報のファクトテーブル（ファクトレスファクト）

Step 1: ディメンションテーブルの最新断面を作成

まずは普通（SCD Type1形式）のディメンションテーブルを作ります。ここでは従業員情報を例にします。
これは普通のdbtモデルです。

このテーブルの特徴：

• 最新断面のみを保持
• snapshotの元データとして使用
• ビジネスに必要な変換処理はここで処理

サンプルデータはこんな感じ：

employee_id	employee_name	position
1001	田中太郎	エンジニア
1002	佐藤花子	マネージャー

Step 2: 1のデータをsnapshotでSCD Type2化

次に、dbtのsnapshot機能を使ってSCD Type2形式に変換します。
ちなみにstrategyはcheckを使っていますが、updated_atに対応するカラムを用意できるならtimestampを使っても良いです。

snapshot設定例：

{% snapshot scd2_employee %}

{{
    config(
        target_schema='snapshots',
        unique_key='employee_id',
        strategy='check',
        check_cols='all'
    )
}}

select * from {{ ref('base_dim_employee') }}

{% endsnapshot %}

田中太郎さんのポジションがエンジニア→シニアエンジニアに昇進したとします。
dbtのsnapshotを実行すると、dbtが自動的に履歴管理用のカラムを追加してくれます。

ちなみにdbtはsnapshotをモデルの間に配置しても、dbt buildコマンドで依存するモデルが実行後にsnapshotが実行されるようになっています。

dbt_scd_id	employee_id	employee_name	position	dbt_valid_from	dbt_valid_to	dbt_updated_at
abc123	1001	田中太郎	エンジニア	2024-01-01 10:00:00	2024-03-15 10:00:00	2024-01-01 10:00:00
def456	1001	田中太郎	シニアエンジニア	2024-03-15 10:00:00	null	2024-03-15 10:00:00
ghi789	1002	佐藤花子	マネージャー	2024-01-01 10:00:00	null	2024-01-01 10:00:00

田中さんが3月15日に昇進してシニアエンジニアになった履歴が記録されていますね。

Step 3: 2のデータを使ってSCD Type2形式のディメンションテーブルを作成

このモデルではStep2で作成したsnapshotをほとんどそのまま流用します。

重要なのはこの時点で各行でユニークになるサロゲートキーを生成することです。今回はemployee_keyというカラムを作成しました。（ユニークなものであれば、値はdbt snapshotで自動発番されるサロゲートキーでも、また自分で別に作成してもお好みで良いと思います。）

ちなみにdbt snapshotで生成されたカラムを使う際には、カラム名にdbt_が付与されているので、それが不要であればカラム名を変更する処理は入れてもいいかもしれないです。

調整後のデータ例：

employee_key	employee_id	employee_name	position	valid_from	valid_to
abc123	1001	田中太郎	エンジニア	2024-01-01 10:00:00	2024-03-15 10:00:00
def456	1001	田中太郎	シニアエンジニア	2024-03-15 10:00:00	null
ghi789	1002	佐藤花子	マネージャー	2024-01-01 10:00:00	null

Step 4: ファクトテーブルにディメンションのサロゲートキーを付与する

Step3までで、ディメンションテーブルのSCD Type2化は完了しました。

あとは、それをファクトテーブルからSCD Type2形式のディメンションテーブルを参照できるようにサロゲートキーをファクトテーブルに付与する必要があります。

おそらく、ここが一番面白いポイントかなと思っています。SCD Type2ディメンションとファクトテーブルを結合する際のJOIN条件を見てみましょう。

ファクトテーブルでは、ファクトの基準日とディメンションのナチュラルキー、valid_to, valid_fromを使ってディメンションと結合する必要があります。

select
    fact.*,
    dim_employee.employee_key -- ファクトテーブルに従業員のサロゲートキーを付与する
from
    fct_company_affiliation as fact -- 日毎の会社の在籍情報ファクト
    left join
        dim_employee_scd2 as dim_employee
            on fact.employee_id = dim_employee.employee_id -- ナチュラルキーで結合
            and fact.date >= dim_employee.valid_from -- 有効開始日の条件
            -- 有効終了日の条件
            and (
                dim_employee.valid_to is null -- 有効終了日がnullの場合は現在も有効
                or 
                fact.date < dim_employee.valid_to -- 有効終了日がある場合はファクトの日付より前であること
            )

これにより、ファクトテーブルの日付の時点で対象のディメンションのレコードを紐づけることが可能になりました。

（余談で、当初自分は混乱していたことなのですが、スタースキーマを使うときにファクト、ディメンションをジョインしますが、SCD type2の場合、事前にファクトにディメンションをジョインする必要があるのは特徴的だと思います。このジョインはあくまでディメンションのサロゲートキーを付与するためだけのジョインであり、分析時のファクト、ディメンションのジョインとは異なります。）

実際のデータでの動作例

田中さんの例で見てみましょう：

ディメンションテーブル（dim_employee）:

employee_key	employee_id	position	valid_from	valid_to
abc123	1001	エンジニア	2024-01-01 10:00:00	2024-03-15 10:00:00
def456	1001	シニアエンジニア	2024-03-15 10:00:00	null

ファクトテーブル（fct_company_affiliation）:

date	employee_id	employee_key
2024-03-14	1001	abc123
2024-03-15	1001	abc123
2024-03-16	1001	def456

結合結果:

date	employee_id	employee_key	position
2024-03-14	1001	abc123	エンジニア
2024-03-15	1001	abc123	エンジニア
2024-03-16	1001	def456	シニアエンジニア

3月15日（具体的には3月15日00:00:00には注意）は昇進前なので「エンジニア」の期間（abc123）と結合され、3月16日は昇進後なので「シニアエンジニア」の期間（def456）と結合されます。

注意として、snapshotの実行時間が10時なので、3月15日はあくまでabc123と結合されます。ここはユーザーによってはdef456と結合したい場合もあるかもしれません。その場合は処理を変更するなどして対応してください。

まとめ

以上、DimensionテーブルをSCD Type2形式で実装する方法について紹介しました。
意外とドキュメントがないテーマかと思うので、読者の方にお役に立てると嬉しいです。