問題とスコープ
顧客データソースから、毎日5,000万行の完全なスナップショットが送信されます。履歴追跡対象として選択された属性の約1%が毎日変更されます。注文は継続的に到着する一方、ソースの修正データは最大3日遅れて到着する可能性があります。各注文が発生した時点で有効だった値を保持し、削除を処理し、安全な再実行をサポートし、バックフィル後も監査可能性を維持するSlowly Changing Dimension Type 2（SCD Type 2）の顧客ディメンションを設計してください。
中核となるタスクは、ディメンショナルモデリングと時間的正確性です。Change Data Capture（CDC）は入力イベントを提供する場合がありますが、ターゲット側の履歴を定義するものではありません。回答では、どの属性がType 2の処理に値するかを決定し、永続的なビジネスキーとバージョンのサロゲートキーを区別し、重複しない有効期間インターバルを定義し、ファクトがどのように正しいバージョンを解決するかを説明する必要があります。
この規模では、不注意な選択がすぐに顕在化します。1日あたり1%の変更率は、1日あたり約50万件、年間で1億8,250万件の新しいバージョンを意味します。追加されるバージョンが平均300バイトの場合、インデックス、レプリカ、メタデータ、圧縮を考慮する前の行データ生サイズだけで年間約54.75 GBになります。これらの数値は面接における前提条件およびサイジングのベースラインであり、ストレージの保証値ではありません。
面接官が評価するポイント
基本的な回答では「古い行を失効させて新しい行を挿入する」と答えます。優れた回答では、まず履歴セマンティクスを定義します。Type 1は値を上書きし、以前の状態を失います。Type 2は新しいサロゲートキーで新しいバージョンを作成し、古いバージョンを保持します。すべてのソース列がバージョン生成のトリガーになるべきではありません。大文字・小文字の修正はType 1で十分かもしれませんが、履歴レポートで使用される販売地域や価格帯はType 2にする必要があります。
次の評価ポイントは、時間的一貫性の規律です。候補者は[valid_from, valid_to)のようなインターバルの規約を述べ、1つのビジネスキーに対する複数のバージョンが決して重複しないことを保証し、現在のバージョンを1つのみ許可する必要があります。時刻tのファクトは、開始がt以前かつ終了がtより後のバージョンと一致します。要件が業務上の有効時刻であるにもかかわらずロード時刻を使用すると、遅延データが暗黙のうちに誤ったバージョンに紐付けられてしまいます。
面接官は本番環境での振る舞いも確認します。同一バッチを再実行した際に別のバージョンが作成されてはなりません。不完全なスナップショットによって数百万の顧客が誤って削除されてはなりません。現在の行をクローズして後続行を挿入する処理はアトミックでなければなりません。遅延修正では、現在の行のみを変更するのではなく、過去のインターバルを分割する必要が生じる場合があります。ビジネス側が「値がいつ有効になったか」と「DWHがそれをいつ認識したか」の両方を必要とする場合、通常のSCD Type 2では不十分であり、それはバイテンポラル（bitemporal）要件となります。
回答前に確認すべき質問
- どの属性が履歴分析に影響を与えるか？ 合意されたType 2フィールドのみを追跡します。監査フィールドや取り込みタイムスタンプによってビジネスバージョンを作成すべきではありません。
customer_idは安定しており、決して再利用されないか？ これはビジネスキーです。すべての履歴バージョンには個別のcustomer_skサロゲートキーが付与されます。- ソースから信頼できる有効時刻またはシーケンスが提供されるか？ 日次スナップショットは値がいつ観測されたかを証明するものであり、必ずしもいつ有効になったかを証明するものではありません。信頼できるソース時刻がない場合、DWH側で過去の日付境界を勝手に作り出すことはできません。
- 各スナップショットには完了フラグが明示されているか？ 完了フラグ、行数、コントロールトータルのチェックに合格した後にのみ、データ不在を削除として扱います。部分的な抽出データは削除フィードではありません。
- 削除は何を意味すべきか？ 本設計では、アクティブなバージョンをクローズし、
is_deleted = trueを持つ現在のトゥームストーン（tombstone）バージョンを挿入することで、明示的な削除境界を保持します。 - ファクトは取り込み時に
customer_skを保存するか、それともクエリ時に時刻で結合するか？ ファクトのロード時にサロゲートキーを解決しておくと、その後のクエリがシンプルになります。バックフィルや検証のためには時間結合も引き続き有用です。 - 修正によって過去のビジネス履歴を書き換えることは許可されているか？ 許可されている場合は、バックフィルによって以前の分析結果が正当に変更される可能性があるため、未加工のソースバージョンと監査証跡を保持してください。
- システムは業務時刻に加えて認識時刻も保持する必要があるか？ 監査人が両方を必要とする場合は、1つのインターバルに両方を無理に押し込まず、有効時刻（valid time）とシステム時刻（system time）を別々にモデル化します。
30秒の要約回答
I would first define the business key, Type 2 attributes, trusted effective time, and delete policy.
Each version gets a surrogate key and a half-open [valid_from, valid_to) interval; one version
per customer is current. I stage and deduplicate a complete snapshot, hash only tracked attributes,
then classify rows as unchanged, new, changed, or deleted. A changed key closes the old version and
inserts the new one atomically. The batch ID and source version make reruns idempotent. Facts resolve
the version effective at order time. Late corrections split the historical interval they affect, and
validation rejects duplicate current rows, overlapping intervals, broken fact references, or an
implausible delete surge.ステップごとの詳細解説
ステップ1：ロード前に粒度とカラムを定義する
粒度は「1つの有効期間インターバルにおける1顧客の1バージョン」です。実践的なテーブルには以下が含まれます。
dim_customer {
customer_sk // surrogate primary key for this version
customer_id // durable business key from the source
segment
sales_region
valid_from
valid_to // null means open-ended
is_current
is_deleted
change_hash // canonical hash of Type 2 attributes only
source_version
load_batch_id
}一貫して[valid_from, valid_to)を使用します。変更が2026-07-10T09:00:00Zに有効になる場合、古い行はその瞬間に終了し、新しい行はまったく同じ瞬間に開始します。半開区間の規約により、境界値は厳密に1つのバージョンにのみ属します。タイムスタンプは定義されたタイムゾーンと精度で保存してください。日付、ローカル時刻、UTCを混在させると、不自然なギャップや二重一致が発生します。
ビジネスキーはバージョンをグループ化します。サロゲートキーは不変の履歴状態の1つを識別し、ファクトによって保存される外部キーになります。is_currentは便宜上のものであり、独立した真実ではありません。必ずvalid_to IS NULLと一致している必要があります。ハッシュは比較の最適化にすぎません。NULL、型、Unicode、カラムの順序を正規化し、説明や監査のために実際に追跡対象となるカラムも保持してください。
ステップ2：書き込みおよびスキャンパスのサイズを見積もる
日次スナップショットは5,000万行のソース行をスキャンします。1%の変更率の場合：
50,000,000 × 1% = 500,000 new versions/day
500,000 × 365 = 182,500,000 new versions/year
182,500,000 × 300 bytes ≈ 54.75 GB/year of raw row dataこれにより、スキャンコストと変更書き込みコストが分離されます。スナップショットをステージングに1回配置し、必要なカラムのみを射影して、ビジネスキーでディメンションの現在の行と比較します。パーティショニングやクラスタリングは、数千の小さな日次パーティションを作成するのではなく、実際のクエリとメンテナンスのパターン（通常はルックアップ用のビジネスキーとプルーニング用の有効期間）に従う必要があります。本番に近いデータを使用して、インデックス、カラムナ圧縮、保持期間、およびバックフィルの増幅を測定してください。
ステップ3：通常のバッチを決定論的かつアトミックにする
抽出データを不変のsnapshot_idの下に取り込みます。ターゲットを変更する前に、完了マーカー、スキーマ、期待されるキーの一意性、行数、およびコントロールトータルを検証します。信頼できるソースバージョンを使用してcustomer_idで重複排除します。同一ランクで内容が異なる2つの行が存在する場合、それは入力エラーであり、恣意的に1つを選択してよい理由にはなりません。
追跡対象の属性を正規化し、ステージングされた各行を現在のターゲットバージョンと比較します。
- 現在のビジネスキーが存在しない場合：最初のバージョンを挿入します。
- 追跡対象の値が同じ場合：何もしません。
loaded_atの更新によって履歴を捏造してはなりません。 - 追跡対象の値が異なる場合：信頼できる有効時刻で現在の行をクローズし、新しい現在のバージョンを挿入します。
- 検証済みの完全スナップショットに現在のターゲットキーが存在しない場合：それをクローズし、現在のトゥームストーンバージョンを挿入します。
変更されたキーごとに、クローズと挿入は1つのターゲットトランザクションまたは1つのアトミックなテーブル操作内で実行されます。現在のバージョンに対する一意性ルールにより、2つの並行ローダーからの保護が行われます。snapshot_id、load_batch_id、source_versionを記録し、すでに適用済みのソースバージョンは拒否します。これにより、確認応答の喪失後に再試行が行われても、履歴を重複させることなく収束します。
ステップ4：業務イベント時刻でファクトを解決する
注文をロードする際は、注文の業務タイムスタンプを使用して顧客バージョンを解決し、その後ファクトにcustomer_skを永続化します。ベンダー非依存の特定時点ルックアップは次のような形式になります。
SELECT d.customer_sk
FROM dim_customer AS d
WHERE d.customer_id = :customer_id
AND d.is_deleted = false
AND d.valid_from <= :order_time
AND (d.valid_to > :order_time OR d.valid_to IS NULL);不等号によって半開区間がエンコードされます。ルックアップは厳密に1行を返す必要があります。0件一致の場合は明示的な未知メンバー（unknown-member）ポリシーまたは隔離ポリシーが必要であり、複数件一致は履歴の破損を証明しています。遅れて到着したファクトに対しては、取り込み時刻ではなくイベント時刻を使用します。KimballのType 2パターンでファクトにバージョンのサロゲートキーを使用するのは、過去にロードされたファクトが当時の履歴プロファイルをそのまま維持できるようにするためです。
ステップ5：遅延修正をインターバルの外科手術として処理する
DWHに現在、7月1日からのSeattleと7月12日からのDenverが存在すると仮定します。7月14日に、Denverが7月10日に有効になったという信頼できる修正データを受信しました。現在の行のみを更新すると、7月10日と7月11日のデータが誤ったままになります。7月10日を含むバージョンを見つけ、Seattleを7月10日でクローズし、Denverの開始を7月10日に移動します。修正によって既存のインターバル内に第3の値が導入される場合は、そのインターバルを分割し、その後に判明している境界を維持します。
修正はソースバージョンの順序で適用し、ビジネスキーごとに更新をロックまたはシリアル化します。生入力データと、以前のインターバル、新しいインターバル、ソースバージョン、バッチ、理由を含む修正監査ログを保持します。修正された履歴を外部キーに反映させることがビジネス規約で求められている場合は、影響を受けるファクトを再解決します。
ここには情報の限界があります。7月14日に初めて観測されたスナップショットは、ソースに信頼できる業務時刻またはその他の順序付けられたレコードが含まれていない限り、ある値が7月10日に有効になったことを証明できません。7月14日を観測時刻として使用するか、修正データを隔離してください。暗黙的に日付を遡らせると、架空の精度を生み出すことになります。DWHが7月10日の有効時刻と7月14日の認識時刻の両方を保持する必要がある場合は、2つ目のシステム時間インターバルを追加し、そのモデルをバイテンポラルと呼びます。
ステップ6：不変条件と運用のガードレールを検証する
各バッチの後、および公開前にターゲットをテストします。
- 各サロゲートキーが一意であること。
- 各ビジネスキーに、削除されたキーのトゥームストーンを含めて厳密に1つの現在のバージョンが存在すること。
is_currentが開いているvalid_toと一致していること。- 1つのビジネスキーに対するインターバルが決して重複せず、終了が存在する場合、すべての行で
valid_fromがvalid_toより前であること。 - 変更のないソース値によってバージョンが追加されないこと。
- 同じソースバージョンまたはバッチの再実行によって新しい行が生成されないこと。
- 未知（unknown）以外のすべてのファクトサロゲートキーが、ファクトのイベント時刻に有効なディメンション行を参照していること。
- 挿入、変更、未変更、削除の件数が、ステージングされたスナップショットと一致（リコンサイル）していること。
スナップショットの完全性、重複キー、変更率、削除率、バージョンの増加、拒否された遅延修正、0件および複数件一致のファクトルックアップ、ロード遅延、再実行時の差分を監視します。4,000万件のキーが突然消失した場合は、4,000万件のトゥームストーンになる前にフェイルクローズ（処理を安全に中断）する必要があります。新規キー、同一スナップショットの再実行、通常の変更、削除と再作成、3日遅れの修正、順不同のソースバージョン、部分的なスナップショット、クローズと挿入の間のクラッシュをテストしてください。
優れた回答例
「顧客ごとに1行ではなく、顧客のバージョンごとに1行をモデル化します。customer_idは永続的なビジネスキーであり、各バージョンには新しいcustomer_skが付与されます。ロードのタイムスタンプやフォーマット修正によって履歴が作られないよう、Type 2フィールド（セグメントや販売地域など）はアナリストと合意しておきます。各行は半開区間[valid_from, valid_to)を使用し、顧客ごとに厳密に1行のみが現在（current）となります。
ソースは毎日5,000万行をスキャンしますが、変更されるのは約50万行です。これは年間約1億8,250万件の新しいバージョンを意味し、1バージョンあたり300バイトと仮定すると、物理的なオーバーヘッドを除く生データの増加量は年間約54.75 GBになります。スナップショットをステージングに1回配置し、履歴全体を繰り返し結合するのではなく、ディメンションの現在の行とのみ比較します。
各スナップショットには安定したIDがあり、完全性、一意性、スキーマ、行数、コントロールトータルのチェックに合格する必要があります。追跡対象のType 2カラムのみを正規化してハッシュ化します。新しいキーは挿入され、ハッシュが同一の場合は何もしません。変更されたキーについては、1つのアトミックな操作で古いインターバルをクローズして新しいバージョンを挿入します。検証済みの欠落キーに対しては、古いバージョンをクローズして削除トゥームストーンを挿入します。ソースバージョンとバッチIDによりロードは冪等になり、現在行の一意性ルールによって並行処理によるバージョンの重複がブロックされます。
注文データは、終了のNULLを開いている状態として扱い、valid_from <= order_timeおよびvalid_to > order_timeを使用してcustomer_skを解決します。ルックアップは削除されていない1つのバージョンを返す必要があります。遅延ファクトには到着時刻ではなく注文時刻を使用します。ディメンションの遅延修正は、信頼できる有効時刻を含むインターバルに適用します。つまり、そのインターバルを分割または調整し、その後の判明している境界を維持します。ソースから到着時刻しか提供されない場合、3日前の有効時刻を勝手に作り出すことはしません。業務時刻とDWHの認識時刻の両方をクエリ可能にする必要がある場合は、バイテンポラルモデルを提案します。
公開前には、ビジネスキーごとに1つの現在バージョンが存在すること、インターバルの重複がないこと、有効な境界であること、ファクトの参照整合性、ソースとターゲット間の件数一致、および同一の再実行で変更行数がゼロであることをチェックします。異常な削除率や変更率に対してアラートを発信し、未加工のスナップショットと修正監査ログを保持することで、バックフィルの説明可能性とロールバック可能性を担保します。」
よくある間違い
- すべてのソース列をハッシュ化する → 監査タイムスタンプによって無意味なバージョンが作成される → 正規化後、規約で追跡対象として合意されたType 2属性のみをハッシュ化する。
- ナチュラルキーをディメンションの主キーとして使用する → 複数の履歴バージョンが衝突する → ビジネスキーでグループ化し、各バージョンをサロゲートキーで識別する。
- 両端を含むインターバル終了を混在させる → 変更境界上のファクトが2行に一致してしまう → エンドツーエンドで
[valid_from, valid_to)を採用する。 - 取り込み時刻を有効時刻として使用する → 遅延更新や遅延ファクトが誤った履歴状態に結合される → 信頼できる業務時刻を使用し、利用できない場合のフォールバックを明記する。
- スナップショットの欠落行をすべて削除として扱う → 部分的な抽出によってディメンションが消去される可能性がある → 不在セマンティクスを適用する前に、完全性マーカーと異常チェックを必須とする。
- クローズと挿入を別々のコミットで実行する → クラッシュにより現在行が0行または2行になる → バージョンの移行をアトミックに適用し、現在行の一意性を強制する。
- 再実行のたびにバージョンを作成する → 再試行によって履歴が肥大化し過去の回答が変わる → ソースバージョンとバッチIDを永続化し、同一の入力はno-op（何もしない）にする。
- 遅延修正に対して現在の行のみを修復する → 過去のファクトが誤った状態に紐付いたままになる → 影響を受ける過去のインターバルを分割または調整し、影響を受けるファクト期間を再解決する。
- 任意の2つのタイムスタンプを「バイテンポラル」と呼ぶ → その意味が曖昧なままになる → 有効時刻とシステム時刻を明確に命名し、両方のインターバルと修正ルールを定義する。
- 行数のみをチェックする → 重複や重複した現在行が見逃される可能性がある → 時間的、参照的、冪等性、およびリコンサイルに関する不変条件を検証する。
フォローアップの質問と回答
フォローアップ1：なぜすべての属性にType 1を使用しないのか？
Type 1は、誤字の修正など、以前の状態に分析上の意味がない値に対して適切です。古い値を破棄してしまうため、「当時この注文を管轄していた地域はどこか？」といった質問には答えられません。レポートのセマンティクスに基づいて属性を分類します。更新ルールが明示されている限り、1つのディメンション内で一部のカラムにType 1を使用し、他のカラムにType 2を使用することができます。
フォローアップ2：現在の行にはvalid_to = NULLと遠い未来の番兵値のどちらを使用すべきか？
どちらでも機能します。NULLは「オープンエンド」であることを明示しますが、NULLを考慮した述語が必要です。サポートされている最大タイムスタンプなどの番兵値は範囲フィルターを簡素化できますが、レポートに漏れ出したり、別のエンジンの日付範囲を超えたりする可能性があります。表現を1つ選択し、is_currentと整合させ、すべてのクエリとコネクタを同じ規約でテストしてください。
フォローアップ3：同じビジネスキーの削除とそれに続く再作成はどのように処理するか？
削除時刻にアクティブなビジネスバージョンをクローズし、削除トゥームストーンを追加します。再作成時には、トゥームストーンをクローズし、新しいサロゲートキーで新しいアクティブバージョンを挿入します。まず、ソースが真に同一エンティティのアイデンティティを再利用していることを確認します。識別子が別の人物用にリサイクルされた場合は、エンティティを区別する永続キーを導入します。
フォローアップ4：ソースに信頼できるupdated_atがない場合はどうするか？
スナップショットツールのチェック戦略と同様に、正規化された追跡対象カラムの明示的なリストを比較します。結果として得られるインターバルは、業務上の変更が実際に発生した時点ではなく、DWHが変更を観測した時点から開始します。その制約事項をドキュメント化してください。正確な業務有効履歴が必要な場合は、タイムスタンプを捏造するのではなく、ソースのシーケンス、監査ログ、CDCフィード、またはドメインイベントを取得します。
フォローアップ5：これはCDCパイプラインとどのように異なるか？
CDCは、コミットされたどの挿入、更新、削除がどのようなソース順序で発生したかに答えます。SCD Type 2は、選択されたディメンション属性がどのように履歴バージョンになり、ファクトがどのサロゲートキーを参照すべきかに答えます。CDCフィードからSCDローダーを駆動することも、スナップショットから駆動することも可能です。取り込みの信頼性が高いこと自体は、有効期間インターバルの重複や誤った時点結合を防ぐものではありません。
フォローアップ6：Type 2の行増加を避けるべきケースはどのような場合か？
履歴のグループ化に必要のない頻繁に変更される属性、大規模な自由形式のペイロード、およびイベントやファクトとして表現する方が適している運用ステータスについては避けるべきです。クエリに応じて、Type 1、独立したミニディメンション、累積ファクトまたは定期ファクト、あるいは専用の履歴テーブルを使用します。この決定は、分析セマンティクスと測定された書き込み/クエリコストに従います。
フォローアップ7：遅延バックフィルによって履歴が破損していないことをどのように証明するか？
ステージングまたはシャドウテーブルに対してバックフィルを実行し、ビジネスキーごとにインターバルセットを比較して、挿入、分割、短縮、延長、削除されたバージョンをレポートします。重複がないこと、現在行が1つであること、影響を受けないサロゲートキーが不変であること、および修正された時間枠内でのみ期待通りのファクトキー変更が発生していることをアサートします。再実行およびロールバックのために、未加工のソースバージョンとバッチマニフェストを保持します。