設問とコンテキスト
決済システムが昨日の注文件数を1,000件と報告しているのに対し、データウェアハウスのレポートでは998件となっています。ソース、ランディング層、変換モデル、およびレポート全体にわたって差異を検出するパイプラインを設計してください。
回答をdbt、Airflow、または特定のデータウェアハウスに限定しないでください。目的は、欠落、重複、不一致、遅延、定義の違いによるレコードを区別する、再現性のあるエビデンスチェーンを構築することです。
面接官がテストしていること
突合の粒度（Reconciliation grain）
注文、加盟店、日付、またはバッチの粒度を選択する前に、ビジネスキー、時間セマンティクス、および金額の精度を定義します。件数のみのチェックでは、相殺されたエラーを見逃す可能性があります。
説明可能なチェック（Explainable checks）
件数、金額、ステータス分布、キーの一意性、およびサンプリングされた詳細をチェックします。すべての結果を再現できるように、ルールのバージョンと入力スナップショットを永続化します。
クローズドループなインシデント管理（Closed-loop incidents）
差異を分類し、担当者を割り当て、エビデンスを保持し、リプレイをサポートし、完了記録を残します。追跡可能なケースを伴わないメール通知は、運用ループとは言えません。
安全な運用（Safe operations）
修復によって新たな不一致が発生しないように、遅延データ、冪等な再実行、パーティション、スキーマ変更、鮮度（freshness）、および監査履歴を処理します。
行うべき確認質問
- 「昨日」はどのタイムゾーンとイベント時刻定義を使用していますか？
- 注文のビジネスキーは何ですか？また、返金やキャンセルはどのようにカウントされますか？
- ソースから遅延更新や削除が送信されることはありますか？
- データウェアハウスはバッチ、ストリーミング、ハイブリッドのいずれですか？
- どの通貨と小数点精度が適用されますか？
- 修復は自動、リプレイ、または最初に手動承認が必要ですか？
30秒での回答フレームワーク
「1つの時間枠とスナップショットを固定し、注文キーごとに件数、金額、ステータスを突合した上で、欠落、重複、未着、変換エラーをドリルダウンして調査します。各差異にはルールのバージョン、ソースサマリー、ウェアハウスサマリー、担当者を保持します。ウォーターマークとリトライウィンドウによって、遅延データと真のデータ損失を区別します。承認されたケースは冪等なバックフィルまたはリプレイで修復し、その後同じスナップショットを再度突合します。入力、出力、アラート、人間の対応アクションは監査テーブルに記録します。」
ステップごとの詳細解説
ステップ 1: スコープとスナップショットの固定
ソースバッチ、イベント時刻範囲、処理時刻、タイムゾーン、スナップショットIDを記録します。ソースが変化する間に結果がずれないよう、常に同じ入力セットを参照する必要があります。
ステップ 2: キーの正規化
注文ID、加盟店ID、ステータスマッピング、通貨、金額の精度を正規化します。両側のハッシュまたはサマリーを保持し、診断に必要な最小限の機密データのみを保持します。
ステップ 3: 階層的なチェックの実行
まず件数と合計金額を比較し、次に加盟店、日付、ステータスごとにグループ化します。続いて、キーの一意性、NULL、重複、許容誤差、および詳細のアンチジョイン（anti-join）チェックを実行します。合計が一致していても、詳細が一致していることの証明にはなりません。
ステップ 4: 遅延と更新の分離
イベント時刻とウォーターマークを使用して、未着のデータと欠落データを区別します。バックフィルウィンドウ内ではレコードを保留（pending）状態に保ち、ウィンドウが閉じた後にのみエスカレーションします。バージョンまたは変更時刻を使用して、更新、返金、削除を再計算します。
ステップ 5: 分類と修復
金額、注文数、ビジネスへの影響、および継続時間によってケースをランク付けします。自動修復は冪等なバックフィルまたはリプレイに限定します。財務定義の変更には承認と事前/事後のエビデンスが必要です。
ステップ 6: 再実行と監査
スナップショットID、パーティション、ルールのバージョンによって冪等に実行します。レビューのために、入力スコープ、クエリバージョン、メトリクス、詳細な差異、アラート、担当者、リトライ履歴、クローズ時刻を保存します。
質の高い模範解答
「各ソースバッチに対して snapshot_id を生成し、UTCのイベント時刻ウィンドウを固定します。正規化層で注文ID、ステータス、通貨、金額精度を統一します。件数、金額、ステータス分布を比較し、主キーのアンチジョインを使用してソースのみ、ウェアハウスのみ、重複、および金額不一致のレコードを特定します。
ウォーターマークと2時間のバックフィルウィンドウにより、遅延イベントを保留状態として分類し、ウィンドウ経過後に未解決の差異のみをエスカレーションします。差異テーブルにはルールのバージョン、双方のレコードサマリー、金額差分、担当者、エビデンスを格納します。承認されたリプレイは snapshot_id とビジネスキーに対して冪等です。修復後は同じスナップショットを再実行し、入力範囲、コードバージョン、アラート、クローズ時刻を監査テーブルに書き込みます。」
よくある間違い
- 総件数のみの比較 → 相殺されたエラーが隠れたままになる → ビジネスキーのアンチジョインを使用し、詳細な差異を永続化する。
- 処理時刻をイベント時刻として使用 → 遅延レコードや異なるタイムゾーンのレコードによってウィンドウがずれる → イベント時刻、処理時刻、タイムゾーンを別々に定義する。
- 返金、キャンセル、更新、削除のセマンティクスの欠落 → 層ごとに異なる数値が算出される → ステータスマッピングと処理ルールをバージョン管理する。
- 浮動小数点数による金額比較 → 小数点以下のノイズが誤ったインシデントになる → 整数の最小単位、通貨、明示的な許容誤差を使用する。
- すべての遅延レコードで即座にアラートを発報 → ノイズの多いインシデントが誤った修復を引き起こす → ウォーターマークと保留状態を持つバックフィルウィンドウを使用する。
- 非冪等な修復 → 再実行により重複行が発生する → スナップショットとビジネスキーによって冪等にupsertする。
- 担当者、エビデンス、またはクローズ状態の欠如 → インシデントの割り当てやレビューができない → ケースと監査レコードを追加する。
- 最終的な数値のみを保持 → 結果を再現できない → 入力スナップショット、ルールバージョン、コードバージョンを保存する。
フォローアップの質問と回答
フォローアップ 1: 合計は一致しているがレコードが異なる場合はどうしますか？
ビジネスキーのアンチジョイン、重複キーチェック、グループ化された分布を使用して、相殺された追加と削除を特定します。レビューのために詳細な差異を永続化します。
フォローアップ 2: 遅延データによる誤アラートをどのように防ぎますか？
イベント時刻、ウォーターマーク、および明示的なバックフィルウィンドウを使用します。ウィンドウ内ではケースを保留中としてマークし、ウィンドウ終了後に未解決のケースをエスカレーションし、ウィンドウのバージョンを記録します。
フォローアップ 3: 修復を安全に再実行するにはどうすればよいですか？
スナップショットID、パーティション、ビジネスキーを冪等性キーとして使用し、書き込みをupsertまたは重複排除します。各リトライの前後に件数と金額を比較します。
フォローアップ 4: 突合ルールをどのようにテストしますか？
欠落、重複、遅延、返金、複数通貨のケースに対するフィクスチャを作成します。ルールのバージョン、許容誤差、境界日、タイムゾーンをテストし、誤検知率を監視します。
フォローアップ 5: アラートのルーティングとオーナーシップはどのように設定しますか？
金額、件数、継続時間、およびビジネス上の重要度によってルーティングします。各アラートをバッチ、エビデンス、修復アクションにリンクさせます。クローズには理由の記録が必要であり、監査可能な状態を維持します。
参考 1: dbt ソースとソーステスト
dbt Developer Hubのソースに関するドキュメントでは、ソースの宣言、リネージの構築、データテストのアタッチ、鮮度（freshness）の測定について説明されており、突合の入力における有用なガバナンスパターンとなっています。
参考 2: 鮮度とSLAウィンドウ
dbt Developer Hubの source-freshness ガイドでは、鮮度管理のための loaded-at フィールド、警告/エラーのしきい値、スナップショット結果が示されており、遅延データのウィンドウやエスカレーションの設計に役立ちます。
参考 3: データ品質チェック
dbt Labsのデータ品質ガイドでは、一意性、リレーションシップ、NULLチェック、鮮度について説明されており、自動チェックをバージョン管理されたモデルやアラートとどのように連携させるかを示しています。