プロンプトとコンテキスト
DAU、収益、コンバージョン率がレポート間で一致していません。アナリスト、BI、アプリケーション、自動ジョブがメトリクス定義を共有できるように、ガバナンスされたセマンティックレイヤーを設計してください。
メトリクス規約（contracts）、エンティティの粒度、ディメンション、結合グラフ、時間セマンティクス、バージョン、パーミッション、キャッシュ、品質テスト、移行について説明してください。dbtやLookerを前提としないでください。これらは実装の選択肢であり、答えそのものではありません。この不整合は架空の演習シナリオです。
面接官がテストしていること
一貫した定義
コピーされた別のSQLクエリにするのではなく、メトリクス名を分子、分母、フィルター、時間枠、重複排除、デフォルトのディメンションに変換すること。
粒度と結合
ファクトテーブルの粒度を特定し、多対多の重複を防ぎ、セマンティックに合成できない組み合わせを拒絶すること。
ガバナンスと変更管理
定義には、バージョン管理、レビュー、非推奨化（deprecation）、互換性期間が必要です。BIユーザーが2つ目の信頼できる唯一の情報源（source of truth）を作成してはなりません。
ユーザビリティ
セマンティックレイヤーには、機械可読な規約と、オーナー、例、品質ステータスを備えた人間向けのドキュメントが必要です。
最初に明確にすべき質問
- DAU、収益、コンバージョンは具体的に何を意味するのか？
- コンシューマーが必要としているのは、SQL、API、BIエクスプローラー、それとも埋め込みチャートか？
- ソースの粒度とタイムゾーンは何か？
- 準リアルタイムの値と最終改訂値を共存させることができるか？
- パーミッションはデータセット、行、列、ディメンションのどのレベルで設定されるか？
- レガシーレポートはどのくらいの期間互換性を維持する必要があるか？
30秒の回答
「私なら、名前、説明、分子、分母、フィルター、時間粒度、タイムゾーン、エンティティ粒度、ディメンション、オーナー、機密度、品質ステータスを含むバージョン管理された規約を定義します。クエリエンジンは宣言された結合グラフを使用し、安全でない多対多や互換性のない粒度のパスを拒否します。
定義はバージョン管理内でレビューされ、旧バージョンおよび非推奨期間が設けられます。SQL APIとBIアダプターは、定義バージョンと鮮度を返します。テストでは、フィクスチャ、照合、重複結合、レイテンシ、パーミッション、履歴回帰をカバーします。」
ステップごとの詳細な回答
ステップ1: ユースケースの棚卸し
レポート、アラート、プロダクト、実験で必要とされるクエリ、レイテンシ、精度をリストアップします。すべてのSQLファイルを移行する前に、高価値な2〜3個のメトリクスをパイロット運用します。
ステップ2: 規約の作成
メトリクス名、ビジネス上の意味、メジャー、フィルター、時間枠、タイムゾーン、エンティティ、ディメンション、オーナー、機密度、バージョン、鮮度SLOを記録します。
ステップ3: 粒度と結合のモデリング
各モデルのプライマリキーと粒度を宣言します。カーディナリティと集計方向が安全な結合のみを許可し、多対多のパスは事前集計、ブリッジ、または拒否します。
ステップ4: 時間と改訂の処理
イベント時刻、処理時刻、タイムゾーン、遅延データ、最終改訂ルールを定義します。準リアルタイムの結果には、鮮度と最終ステータスを公開する必要があります。
ステップ5: リリースと認可
定義をバージョン管理に保存し、オーナーとデータ品質のレビュー後に公開します。データセット、行、列、ディメンションを認可し、機密メトリクスへのアクセスを監査します。旧バージョンの移行期限を設定します。
ステップ6: テストと提供
フィクスチャ、照合サンプル、重複結合チェック、鮮度、null、分布テストを使用します。バージョン、タイムゾーン、品質ステータスとともに、SQL、BI、または埋め込みAPIを通じて値を返します。
模範解答
「私ならDAUと収益をパイロット運用します。各定義には、分子、分母、フィルター、イベント時刻、タイムゾーン、エンティティ粒度、許可されたディメンション、オーナー、機密度、バージョン、鮮度を記録します。DAUはユーザーを重複排除するのかデバイスなのかを明記する必要があり、収益は認識基準、返金、税金を定義する必要があります。
このレイヤーはモデルの粒度と結合グラフを保持します。多対多のパスは事前集計されるか拒否されます。定義はGitでレビューされ、リリース前にテストされます。移行期間中は旧バージョンも引き続き利用可能であり、レスポンスにはバージョンと鮮度が含まれます。パーミッションはデータセットとディメンションをカバーし、アクセス監査を実施します。
検証には、照合サンプル、重複カウント、遅延データ、タイムゾーン、パーミッション、鮮度、履歴回帰が含まれます。すべてのSQLクエリを一度に書き直すのではなく、価値の高いレポートを最初に移行し、新旧の結果の違いを説明します。」
よくある間違い
- 分子、分母、フィルターなしでメトリクス名のみを保存すること。
- 幅広いテーブル（wide table）を使用して異なるファクトの粒度を隠蔽すること。
- ファクトを重複させる任意の結合を許可すること。
- イベント時刻、処理時刻、タイムゾーンを混同すること。
- バージョン、オーナー、非推奨化、移行期間を省略すること。
- 結果の照合や鮮度を確認せずに、クエリの成功のみをテストすること。
- 機械可読な規約なしでBIプラグインのみを構築すること。
- ダッシュボードのみを保護し、基盤となるディメンションやクエリ監査を保護しないこと。
フォローアップ質問
フォローアップ1: 収益の重複をどのように防ぎますか？
収益の粒度と一意キーを宣言し、結合前に対象エンティティに事前集計し、安全でない多対多パスを拒否し、既知の合計と照合します。
フォローアップ2: 定義の変更がダウンストリームのユーザーに悪影響を与えることはありますか？
新しいバージョンまたは互換性のあるフィールドを公開し、期限まで旧バージョンを維持し、レスポンスにバージョンを含め、オーナーとコンシューマーの承認を必須とします。
フォローアップ3: 準リアルタイム値と最終値はどのように共存できますか？
鮮度、ウォーターマーク、最終ステータスとともに値を返します。コンシューマーは、暫定的な値を最終的な財務データとして扱うのではなく、許容可能なレイテンシと改訂セマンティクスを選択します。
フォローアップ4: メトリクスをどのように認可しますか？
データセット、行、列、ディメンションのポリシーを最小権限の原則と組み合わせます。リクエスター、定義バージョン、エクスポートを記録し、定期的に監査します。
フォローアップ5: このレイヤーが価値を生み出していることをどのように証明しますか？
移行前後の定義の競合、重複SQL、照合の差異、クエリの成功率、鮮度、導入率、インシデントを比較するとともに、ユーザーが結果を説明できるかを確認します。