10年選手の広告プラットフォームの データモデリングをいい感じにした話

データモデリングとデータ基盤の構築・運用
（第14回ちゅらコラボ）CARTA HOLDINGS x ちゅらデータ 合同イベント
https://churadata.connpass.com/event/254417/

の登壇資料です

1. 10年選手の広告プラットフォームの データモデリングをいい感じにした話 CARTA HOLDINGS x ちゅらデータ 合同イベント データモデリングとデータ基盤の構築・運用 村上 柊 @shu22203 CARTA HOLDINGS fluct 開発本部
2. 自己紹介 • しゅう ◦ 村上 柊 ◦ @shu22203 • 2019年新卒入社4年目 • 好きなもの ◦ Celeste ◦ ずっと真夜中でいいのに ◦ スノボー • 嫌いなもの ◦ データがズレること ◦ CASE式で頑張ってるSQL
3. 今日話すこと • レポート作り直しに至った経緯 • 既存モデリングの辛い話 • どうしたか • これから
4. 今日話さないこと • ETL基盤をリプレイスした話 • データ分析基盤の話
5. まずはじめに 再モデリングにおいてやったことは これだけです
6. 集計層では1つの事実を正しく集める 見たい側の都合は別の層で解決する
7. 以上です ご清聴ありがとうございました
8. 本題です
9. fluct • メインはSSP事業 • メディア向け広告配信プラット フォーム • JSタグを貼るといい感じに広告が 出るシステム • 2010年スタートの国内最大級 SSP
10. SSPの広告レポートとは・特徴 • ある期間において発生したインプレッション数(広告表示回数)・クリック数・売上など が知りたい • 見たいディメンション・指標がとにかく多い ◦ サイト / 広告枠 / デバイス / OS / etc … • 数がとても多い ◦ x億 impresssions / day 以上 ◦ 生のログに対してレポーティング文脈でクエリするのは現実的ではない ◦ ある程度の集約が必要
11. 作り直しに至った経緯
12. 時は2020年(入社2年目) fluct の人々のレポートに対する不満は 青天井状態であった
13. 人々の想い • PC, スマホレポートで画面が別れてるのが辛いです！(見れる軸も違う) • 長めの期間で引こうとするとレポートが全然返ってこないです！ • テーブルの繋がりが全然分からなくてSQLが書けないです！ • あの画面とこの画面で数字がズレててどっちが正しいんですか？なんでズレてるん ですか？ • 新しくこういうディメンション・指標でレポートが見たいんですけど出来ないですか？ • xx(サイト名)を変更したので再取り込みお願いします！
14. どうしてこうなったか(想像含) • fluct 初期、当然スマートフォンは存在していなかった ◦ (詳しい人向け) RTB すら無かった • 今ほどデータウェアハウス・計算機・ストレージなどが充実しておらず、部分ごとに 集計するのが精一杯だった • 運用システムのノリでモデリングしてしまい必要以上に正規化されていた ◦ クエリが難しい・パフォーマンスも悪い ◦ 一方で不適切な非正規化もあり本来 不要な再取り込み依頼 が多発 • → 10年間のビジネスドメインの変化・技術進化にシステムが追いつけていなかった • → 技術的負債
15. 新卒2年目半ばの私 私 > もはや既存の仕組みの上でこれらを改善するのは無理だから、全部作り変えて最 強のレポート基盤作って世界救ってやんよ！(絶望) (そのとき当時の私より詳しいレポート戦士はみな巣立っていってしまっていた(絶望)) > よろしく！！ CTO みたいな感じでスタート
16. 何をやったか • データモデリング • データストアの刷新 • ETL基盤刷新 • BIツールの社内導入
17. 何をやったか • データモデリング ←今回はここ • データストアの刷新 • ETL基盤刷新 • BIツールの社内導入
18. データモデリング
19. ディメンションモデリング
20. ディメンションモデリングとの出会い いくつかモデリング案を出すもしっくりこない日々を過ごす中・・ > こんな本見つけたから読んでみるわ ドンッ(鈍器) (英書850ページ)
21. スタースキーマの衝撃 • 運用DBのモデリングとは全く発想が違うことを思い知った。 ◦ 詳しく説明する時間が無いので本を読んだりしてください ▪ FYI: https://zenn.dev/pei0804/articles/star-schema-design • 1つの事実(ファクト)の周囲に複数のディメンションが存在するモデリング手法 • 冗長であっても分析に関心のあるディメンションであれば非正規化して記録する • これならなんとか戦えそうという気持ちになれた
22. ここからは実例とともに
23. 失敗事例1 複数タイミングに発生する事実が 1つのテーブルに集約されている
24. どうなっていたか 1テーブルに impression, click カラムが存在していた。 集計バッチではまず impression を記録し、click はとりあえず0を入れる。 その後 click 集計で↑で入れたレコードを UPDATE する。
25. 何が辛いか あるタイミングで見ると、本当にクリックが0なのかまだ集計されていないだけなのか分からな い。 → テーブルの利用者が集計側の知識を知っていないといけない。 →集計に依存が発生しているので、impression がコケると click も入らない。 どちらかのファクトの粒度に引きづられて、もう片方の集計の粒度を落とさなければいけなくな る。 → レポーティング・分析の幅が狭くなる。
26. どうしたか impression, click でそれぞれ素直に事実を集める。 ファクトを横断して見るために、ビューを作った。 before after ※ 説明のために簡素化しています
27. 失敗事例2 同じタイミングに発生する事実が 複数のテーブルに重複して記録されている
28. どうなっていたか さっきの逆 impression という事実が様々な切り口で複数テーブルに記録されている。 見る画面もその数だけ存在している。 • オーダーレポート • 枠別レポート • デマンドレポート • PCレポート • スマホレポート
29. 何が辛いか テーブルによって集計方法がばらばらなので、画面によって数字のズレが起こる。 → どれが正しいんですか？ズレの原因はなんですか？というお問い合わせ対応で 本来やるべき仕事の時間が奪われる。
30. どうしたか before after 1つのファクトに対するテーブルは1つにして レポーティング・分析対象のディメンションを全てそこに含める ※ 説明のために簡素化しています
31. 失敗事例3 不必要な正規化・非正規化が行われている
32. どうなっていたか • あるディメンションから導出できるディメンションが記録されていない ◦ 例）サイト → 媒体社 は導出できるので記録しない • 運用システムで頻繁に変更されうる値がファクトテーブルにそのまま記録されてい る(※ あえて発生時の値を記録しておく手法もある (Slow Changing Dimension)) ◦ そしてサロゲートキーは記録されていない
33. 何が辛いか • 運用DBのモデリングに詳しくないとSQLが書けない。書けたとしても重厚長大な SQLになる。 • 運用DBで変更が起きた際に再取り込み依頼が頻発する。 ◦ xx(サイト名など)を変更したので再取り込みお願いします！！ ◦ 変更前の値には関心がない ケースがほとんど
34. どうしたか before after レポーティング・分析文脈で関心のあるディメンションは非正規化し冗長に記録する ディメンションはサロゲートキーを記録する するとクエリは概ね depth 1 JOIN で済むようになる ※ 説明のために簡素化しています
35. 届いた喜びの声
36. これから • 情報が正しく見れるという当たり前を整備するところまでやっただけ。 ◦ みんなにどんどん使ってもらう ◦ 今まで雰囲気でせざるを得なかった業務を根拠を持って意思決定できるように ◦ 仮説検証を早く回しデータドリブンな意思決定 が出来るように促進する • ディメンションモデリング思想の伝承・啓蒙 ◦ DBの寿命はアプリケーションより長いが、 破綻するのも一瞬。 ◦ ディメンションモデリング完全理解者を増やしてモデリングをサステナブルにする。
37. まとめ
38. 集計層では1つの事実を正しく集める 見たい側の都合は別の層で解決する
39. ありがとうございました