データエンジニアリング面接：SCDタイプの選定と遅延変更の処理方法

出題の意図と適用場面

これはディメンショナルモデリングと履歴セマンティクスに関する設問です。財務レポートが過去の日付において顧客がどの地域に属していたかを回答する必要がある場合、顧客住所を最新の値としてのみ扱うことはできません。日次のフルスナップショットがあり、チェンジログは存在せず、ターゲットは現在の状態、履歴検索、および再実行（リプレイ）をサポートしていると仮定します。Dataquestはデータエンジニアリングの面接質問としてSCD Type 1、2、3を挙げており、AWSはCDCなしのフルファイルからType 2を構築する例を示しています。

面接官が見ているポイント

タイプを選択する前に、レポートが「現在」を求めているのか「その時点」を求めているのかを定義できているか。
上書き、バージョンの追記、1つ前の値の保持というセマンティクスの違いを区別できているか。
ナチュラルキー、サロゲートキー、有効期間、およびカレントフラグによって一意性を維持できているか。
単一の UPDATE を書くだけで済ませず、遅延スナップショット、重複ファイル、削除、再実行、および同時実行書き込みを処理できているか。

最初に確認すべき明確化のための質問

レポートはイベント発生時点の状態を復元する必要がありますか、それとも現在のプロファイルで十分ですか？
スナップショットにはバージョン、抽出時刻、またはウォーターマークが含まれていますか？また、順不同で到着する可能性はありますか？
データの欠損は、ビジネス上の削除、一時的な除外、またはソース側の不具合のどれを意味しますか？
公開済みの履歴やファクトの結合は修正可能ですか？また、監査、再実行、および冪等な再実行は必須ですか？

30秒での回答

「私は時間セマンティクスを明確にします。Type 1は現在の値のみを保持し、Type 2は valid_from と valid_to を用いてビジネス上の変更ごとにバージョンを追記し、Type 3は1つまたは少数の以前の値を保持します。特定時点（point-in-time）の分析にはType 2を選択し、ナチュラルキーで現在の行を検索し、サロゲートキー経由でファクトと結合し、スナップショットのウォーターマークを使用して遅延データや削除を特定します。各バッチを着信させて重複排除を行い、古いバージョンをクローズして新しいバージョンをアトミックに挿入します。同じバッチを再実行しても同一の結果が生成されなければなりません。」

ステップ別の解決策

ステップ 1: 履歴要件の定義

Type 1は履歴を必要としない修正やプロファイルに適しており、その場で上書きします。Type 2はすべてのバージョンを保持することで、監査、収益アトリビューション、および特定時点クエリに適しています。Type 3は固定された『現在対直前』の比較ウィンドウに適しています。前四半期における顧客の地域をレポートで求める要件がある場合、Type 1および3では情報が失われるため、Type 2を選択します。

ステップ 2: Type 2の不変条件（Invariants）の設定

サロゲートキー、ナチュラルキー、属性、valid_from、valid_to、is_current、およびソースバッチのウォーターマークを保存します。各ナチュラルキーに対して、is_current = true を持つ行は最大1行です。有効期間が重複してはなりません。AWSは完全な履歴のために開始/終了日、カレントフラグ、論理削除を使用し、MicrosoftのType 2構成ではナチュラルキー、サロゲートキー、2つの日付、およびアクティブフラグを必要とします。

ステップ 3: フルスナップショットからの変更の推論

ファイルハッシュ、抽出時刻、バッチシーケンスとともに、ファイルを不変のランディングテーブルに書き込みます。各ナチュラルキーの属性ハッシュを現在のディメンションと比較します。新しいキーは挿入し、変更されたキーはクローズして追記し、ハッシュが一致するものはスキップします。キーの欠落を削除と推論できるのは、完全スナップショット（complete-snapshot）の規約が成立している場合のみです。増分ファイルでは欠落から削除を推論してはなりません。

ステップ 4: 遅延および順不同データの処理

ソースごとに単調増加するウォーターマークを保持します。適用済みのウォーターマークより前のファイルは拒否または隔離します。履歴修正が許可されている場合は、影響を受ける有効期間を分割し、トランザクション内で影響を受けるファクト結合を再計算します。公開済みレポートが不変である場合は、バッチを修正キューに入れてその影響を公開します。到着時刻を有効時刻として使用すると、古いスナップショットが新しい状態に化けてしまいます。

ステップ 5: 削除、再実行、および同時実行性

完全スナップショットでキーが存在しない場合は、論理削除バージョンを作成するか現在の行をクローズしつつ、監査証跡を保持します。ファイルハッシュまたはソースバッチを一意の冪等性キーとします。バッチを再実行しても別のバージョンが追記されてはなりません。ナチュラルキーのパーティションに対してトランザクションまたはマージロックを使用し、クローズと挿入がアトミックに行われるようにします。バッチはウォーターマーク順に適用します。古いバッチがクローズ済みの期間を再オープンしてはなりません。

検証可能なSQL骨格

sql

-- current_dim: one current row per customer_id
BEGIN;

UPDATE dim_customer AS old
SET valid_to = :as_of,
    is_current = FALSE,
    source_batch = :batch_id
FROM stage_customer AS incoming
WHERE old.customer_id = incoming.customer_id
  AND old.is_current = TRUE
  AND old.row_hash <> incoming.row_hash;

INSERT INTO dim_customer (
    customer_key, customer_id, city, valid_from, valid_to,
    is_current, source_batch, row_hash
)
SELECT nextval('dim_customer_key_seq'), incoming.customer_id,
       incoming.city, :as_of, NULL, TRUE, :batch_id, incoming.row_hash
FROM stage_customer AS incoming
LEFT JOIN dim_customer AS old
  ON old.customer_id = incoming.customer_id
 AND old.is_current = TRUE
WHERE old.customer_id IS NULL
   OR old.row_hash <> incoming.row_hash;

COMMIT;

本番環境では、batch_id に対する一意性制約や、各ナチュラルキーに現在の行が1つだけ存在すること、期間が重複していないこと、入力ウォーターマークが有効であることのコミット前チェックが依然として必要です。この骨格は stage_customer がバッチごとに重複排除されていることを前提としており、順不同処理を完全にカバーするものではありません。

質の高い模範解答

「まず、レポートがどのような履歴を保持する必要があるかを確認します。現在値のみが必要ならType 1、過去の任意の時点の状態が必要ならType 2、直前の1つの値のみが必要ならType 3を使用します。日次フルスナップショットの場合は、未加工ファイルをランディングさせ、バッチウォーターマークとファイルハッシュで重複排除します。ナチュラルキーと属性ハッシュを比較して、挿入、変更、無処理（no-op）を分類します。Type 2の更新では、古い行のクローズと新しいバージョンの挿入をアトミックに実行し、カレント行を1つにし、期間が重複しないようにする必要があります。キーの欠落は完全スナップショットの規約下でのみ削除を意味します。遅延バッチはウォーターマークによって順序付けまたは隔離され、レポートの規約によって履歴を修正するかどうかが決定されます。」

よくある落とし穴

すべてのディメンションに安易にType 2を採用する → ストレージとクエリコストが増加する → まず履歴の規約を確認する。
到着時刻を有効時刻として使用する → 順不同のスナップショットによって履歴が破損する → ビジネス上の有効時刻または修正キューを使用する。
増分ファイルでのキー欠落を削除として扱う → 有効な増分データが削除されてしまう → 完全スナップショットからのみ削除を推論する。
サロゲートキーのバージョンを更新せずに is_current を更新する → ファクトが特定時点の状態と結合できなくなる → 古い行をクローズしてバージョンを追記する。
バッチの冪等性キーを省略する → 再実行時に重複した履歴が追記される → ファイルハッシュまたはバッチIDの一意性を強制する。

フォローアップ質問と効果的な回答

遅延イベントによって公開済みレポートを修正すべきですか？

レポートが修正可能（revisionable）かどうかを確認します。可能な場合は、ビジネス時間によって有効期間を分割し、影響を受けるファクトを再計算します。不可能な場合は、公開済みの結果を維持し、その影響を示す修正バッチを発行します。どちらの規約でも、数値が知らないうちに変更されないよう未加工バッチを保持します。

スナップショット内に同一の顧客が2回現れた場合はどうしますか？

入力データの品質エラーとして扱います。ビジネスバージョンやソースのシーケンスによって正解となる行が定義されている場合にのみ1行を選択し、それ以外の場合は隔離してアラートを発報します。任意の LIMIT 1 を適用すると、履歴の再現性が失われます。

Type 2よりもType 3が適しているのはどのような場合ですか？

現在と直前の比較のみが必要で、それ以前のバージョンがクエリされることが決してない場合です。ストレージを節約でき、クエリもシンプルになります。将来的に任意の日付の履歴が必要になった場合は、Type 2へ移行し、バックフィルにかかるコストを提示します。

期間が重複していないことをどのように検証しますか？

ナチュラルキーでソートし、各期間の終了時刻が次の期間の開始時刻以降になっていないことを確認した上で、キーごとにカレント行が最大1つであることをアサートします。これを事後的なサンプリングではなく、バッチのリリースゲート（品質関門）として組み込みます。