設計・SQL の実技の面接

まず要件として、誰が何時までに見るか、1日の件数、継続率の定義（初回の日から7日後に1回でも使ったか）を確かめます。ここでは翌朝 9時までに前日分、1日数千万件と仮定します。

アプリからは event_id・起きた時刻・届いた時刻・user_id を持つイベントを送り、収集の窓口からレイクに届いた日のパーティションで保存します。dbt で event_id の重複を除き、起きた日（日本時間）で（日, user_id）の1日1行の表を作り、DAU はそこから数えます。継続率は初回の日のコホートごとに分子と分母を持つ表にします。遅れて届く分のために毎回直近3日を作り直し、3日より前は確定とします。品質は件数の前週比と鮮度を見張ります。

設計を大きく変える3つを先に聞きます。鮮度（何時までに、どの間隔で要るか）、量（1日の件数と大きさ、何年分持つか）、正確さ（請求に使うほどか、傾向が分かればよいか）です。あわせて、誰が何を決めるために使うかと、主な指標の定義を聞きます。

全部を聞くと時間が足りないので、残りは「既存の DWH があり、個人情報は含むと仮定します」のように宣言して進め、前提を図の横に書いておきます。前提が変われば設計のどこが変わるかも、最後に話せるようにします。

1件を約1KB と仮定すると、1日 1億件で約 100GB、1年で約 36TB です。Parquet などの列指向で圧縮すると、データによりますが数分の1程度になると見込みます。平均の流量は 1億 ÷ 約 10万秒で毎秒 約 1,000件、ピークはその数倍と見ます。

この量なら、保存はオブジェクトストレージか DWH で問題なく、読むときは日付のパーティションで期間を絞ることが前提になります。受け口はピークの毎秒数千件をキューで受け、まとめて書き込む構成にします。古いデータは集計済みのテーブルだけ残して、生データは保存期間で安い保存先に移すことも考えます。

まず何分以内に、何を判断するのかを確かめます。数時間〜翌朝の判断なら日次や毎時のバッチ、10〜15分で足りるなら短い間隔のバッチを選びます。今の SQL と道具がそのまま使え、遅れて届く分も作り直しで吸収できるからです。

数秒〜数分で反応する必要がある場合だけ、CDC と Kafka、ストリーム処理を使います。その場合はウォーターマーク、重複の除去、コンシューマーラグの監視が要り、運用の負担が増えることを説明します。どちらでも、確定の数字は日次のバッチで作り、速報と区別して見せる形にします。

書き始める前に質問します。特に、テーブルの1行が何か（粒度）と、何が一意か、同点のときの扱い、NULL やデータのない日の扱い、日付のタイムゾーンを確かめます。答えが返らなければ「同点は ID の小さいほうを取ると仮定します」と宣言して進めます。

書くときは CTE で段階に分け、各段階で何が出るかを声に出し、最後に小さな例で手で検算します。時間が残れば、大きなテーブルで重くなる所と、その直し方を自分から付け足します。