プロンプトと適用範囲
ある企業において、「アクティブ顧客」、「収益」、「リテンション」の集計にダッシュボードごとに異なるSQLが使われていることが判明しました。BI、ノートブック、API、そして将来の自動化エージェントによって共有されるセマンティックレイヤーを設計してください。ディメンション、時間粒度、フィルター、権限、履歴バージョン、準リアルタイムデータをサポートする必要があります。テスト不能なレポートプラットフォームを新たに作ってしまう事態をどのように回避するか説明してください。
面接官がテストしていること
ツールについて議論する前に、エンティティ、ディメンション、メジャー、集計、メトリクスセマンティクスを分離します。次に、結合の粒度、重複カウント、タイムゾーン、遅延データを処理します。メトリクスをSQLスニペットのディレクトリとしてではなく、定義、オーナー、ソース、サポートされる粒度、品質状態、互換性を備えたバージョニングされたプロダクトコントラクトとして扱います。
回答前の明確化事項
- ファクトの粒度（fact grain）は何ですか？注文、注文明細、イベントを混在させると、収益が二重カウントされる可能性があります。
- どのディメンションと時間粒度が必要ですか？すべての組み合わせが安全とは限らないため、サポートマトリクスを公開します。
- 鮮度と一貫性の目標は何ですか？ストリーミング、日次バッチ、バックフィルデータでは可視性状態が異なります。
- 誰が定義を公開し、機密性の高いディメンションを読み取ることができますか？メトリクスの権限で行レベルまたは列レベルのセキュリティを迂回してはなりません。
- 過去の定義は再現可能である必要がありますか、それとも現在の定義のみが必要ですか？これにより、バージョンのルーティング、スナップショット、再計算コストが決まります。
推奨される設計と導出
不変（immutable）なメトリクス定義エンティティを作成します：名前、説明、メジャー式、デフォルト集計、ディメンション、時間セマンティクス、フィルター、ソース、オーナー、バージョン、状態、品質SLO。クエリはメトリクスID、ディメンション、時間枠を参照し、コンパイラがSQLを生成するか事前集計テーブルにルーティングします。
コンパイル前に、結合パスが一意であること、集計が粒度と一致していること、フィルターのプッシュダウンが可能であること、ユーザーが列アクセス権を持っていることを確認します。個別カウント（distinct counts）、比率、ウィンドウメトリクスについては、各ツールに推測させるのではなく、分母、重複排除キー、nullポリシーを記録します。
metric: active_customers
version: 3
owner: growth-data
source: mart_customer_daily
measure: count_distinct(customer_id)
dimensions: [plan, region]
time_grain: [day, week, month]
freshness_slo: 2h
status: published2つのトラックでリリースします：シャドークエリで新バージョンと旧バージョンを比較し、次に一部のワークスペースにのみ公開します。差分がしきい値を超えた場合はプロモーションをブロックします。キャッシュキーにはメトリクスバージョン、ディメンション、フィルター、データウォーターマークを含める必要があります。そうしないと、バージョン切り替え時に古い結果を読み取ってしまう可能性があります。高コストなクエリには、予算、タイムアウト、事前集計へのフォールバックを設定します。
代替案とトレードオフ
各BIツールにロジックを埋め込む手法は迅速にリリースできますが、定義が分岐（フォーク）します。単一の物理的にキュレーションされたデータセットはシンプルですが、すべての粒度や権限を表現できません。中央集中型のセマンティックレイヤーは一貫性と再利用可能なAPIを提供しますが、コンパイラ、バージョン統制、権限マッピング、デバッグのコストがかかります。小規模なチームであれば、マルチツールのアクセスを開放する前に、少数のコアメトリクスと1つのコンシューマーから開始できます。
障害モード、境界、反例
- 返金、税金、通貨、重複した注文結合を無視して、収益を
sum(amount)と定義すること。 - メトリクスが任意の生テーブルを読み取ることを許可し、品質ゲートや列権限を迂回すること。
- 新しいバージョンを作成せずにメトリクスの意味を変更し、過去のダッシュボードを密かに変更してしまうこと。
- ウォーターマーク、遅延イベント、バックフィルの可視性を無視しながら、キャッシュヒット率を正しさとして扱うこと。
- すべてのディメンションの組み合わせに対して直積（Cartesian product）クエリを生成すること。非サポートの組み合わせを公開し、代わりに安全な集計を提供してください。
テストと検証チェックリスト
各メトリクスに対してゴールデンクエリと少量の固定データセットを保持します。集計、分母、タイムゾーン、null、重複結合、遅延イベント、バージョン間の差分をテストします。スキーマ、権限、コンパイラ、キャッシュのコントラクトテストを追加し、本番サンプルと結果およびコストを比較します。リリースゲートでは、定義の完全性、ソースの鮮度、品質SLO、権限マッピング、新旧の差分をチェックする必要があります。
フォローアップの質問
互換性を損なう（breaking）メトリクス定義の変更はどのように処理しますか？
新バージョンを公開し、旧バージョンへのルーティングを維持し、非推奨（deprecation）日を設定して依存関係のある利用者に通知し、移行後にのみ削除します。過去のレポートは再現可能な再計算のために旧バージョンを選択する必要があり、バージョン番号を再利用してはなりません。
このレイヤーは準リアルタイムデータとバッチデータをどのように提供できますか？
ソースとウォーターマークを定義の一部とし、すべての結果とともに鮮度と完全性の状態を返します。ストリーミングソースは暫定的な結果を提供し、後にバッチが調整（リコンサイル）を行うことができます。両方のパスでセマンティクスと重複排除ルールを共有する必要があります。
自然言語エージェントによるメトリクスの誤用をどのように防ぎますか？
公開されたメトリクス、サポートされているディメンション、認可されたスコープのみを公開し、説明可能なクエリプランと定義バージョンを返します。粒度、権限、鮮度を証明できないリクエストは拒否し、エージェントが生テーブルのSQLを自由に組み立てられないようにします。