他チームへ越境したら、生データ提供ソリューションのクエリ費用95%削減へ繋がった話 / Cross-Team Impact: 95% Off Raw Data Query Costs

by yamamoto-yuta

Slide 1

Slide 1 text

他チームへ越境したら、⽣データ提供ソリューションのクエリ費⽤95%削減へ繋がった話 DataOps Night #7 株式会社ヤプリ⼭本雄太

Slide 2

Slide 2 text

SPEAKER 開発統括本部プロダクト開発本部データサイエンス室（以下、DS室）⼭本雄太 ● 2023年に新卒⼊社 ● dbt導⼊に際して、開発体制やリリースフローなどの構築を担当 ● pUG（旧TROCCOUG）の運営にも携わらせていただいています

Slide 3

Slide 3 text

ノーコードのアプリ開発プラットフォーム「Yappli」ヤプリの製品 ● 750社以上で導⼊、約900アプリを提供 ● 50種類以上の機能で、多様なシーンに合致したアプリが作成可能

Slide 4

Slide 4 text

Yappli導⼊顧客向けにアナリティクスサービスを提供ヤプリの製品 CMS ダッシュボード Yappli 管理画⾯のトップに表⽰されるダッシュボード Yappli Data Hub アプリ内の⾏動データや属性データをユーザ単位で分析を可能にするデータ連携サービス Yappli Analytics アプリログを網羅した分析や、機能別に特化した分析が可能なダッシュボード無償有償

Slide 5

Slide 5 text

Slide 6

Slide 6 text

「Yappli Data Hub」サービスとは？ヤプリの製品 ● アプリ内の⾏動データや属性データをユーザ単位で分析可能にするデータ連携サービス ● 集約（GROUP BYする）前の⽣データを提供 ○ 顧客は⽣データをそのまま分析したり、⾃社のCRM‧CDPへ取り込んだりして活⽤

Slide 7

Slide 7 text

今⽇お話しすること Yappli Data Hubの開発チームへ越境 ▼ Yappli Data Hubのクエリ費⽤の 95%削減へ繋がった！（まだ絶賛進⾏中）繋がった経緯、取り組んだことについてお話しします！

Slide 8

Slide 8 text

INDEX ⽬次 1. 越境‧クエリ費⽤削減の経緯 2. コストインパクトの試算 3. 実⾏ジョブの時系列整理 4. 顧客影響との折り合い 5. 得られた学び‧恩恵

Slide 9

Slide 9 text

越境‧クエリ費⽤削減の経緯

Slide 10

Slide 10 text

2023年から分析⽤データ基盤の移⾏を開始（詳細はテックブログに⾊々記事書いてます: https://tech.yappli.io/archive/category/dbt ）

Slide 11

Slide 11 text

Yappli導⼊顧客向けにアナリティクスサービスを提供 1. 越境‧クエリ費⽤削減の経緯 CMS ダッシュボード Yappli 管理画⾯のトップに表⽰されるダッシュボード Yappli Data Hub アプリ内の⾏動データや属性データをユーザ単位で分析を可能にするデータ連携サービス Yappli Analytics アプリログを網羅した分析や、機能別に特化した分析が可能なダッシュボード無償有償あとはココだけ！

Slide 12

Slide 12 text

当時のYappli Data Hubの開発体制 ● エンジニアのみで開発‧保守＝ DS室としてはブラックボックスな状態 ● エンジニアも引き継ぎで後からジョイン‧他業務の⽚⼿間 → 専任エンジニアがいない状態 → データ基盤として全体像を把握している⼈がいなかった当時、Yappli Data Hubの開発チームで週次定例を⾏っていた → そこへ私が突撃 1. 越境‧クエリ費⽤削減の経緯 Yappli Data Hubの開発チームに参加

Slide 13

Slide 13 text

参加して取り組んだこと Yappli Data Hubについてのキャッチアップ ● データ基盤としての全体像の整理 ● ドキュメント更新 ● DS室で巻き取るべき業務の特定 → 実際に巻き取り ○ サービスDB→DWH（BigQuery）への同期対象テーブルの追加 1. 越境‧クエリ費⽤削減の経緯

Slide 14

Slide 14 text

Yappli Data Hub基盤の全体像 1. 越境‧クエリ費⽤削減の経緯 Yappli アプリの操作ログサービスDB （Aurora）サービスDB （SQLite）内製トラッキング機構データレイク（BigQuery） dbt データマート（BigQuery）データ加⼯‧設置バッチ Yappli Data Hub 1. 顧客毎に集計クエリを発⾏ 2. 結果をファイル送信 CMSダッシュボード ‧ Yappli Analytics

Slide 15

Slide 15 text

Yappli Data Hubの基盤の全体像 1. 越境‧クエリ費⽤削減の経緯 Yappli アプリの操作ログサービスDB （Aurora）サービスDB （SQLite）内製トラッキング機構データレイク（BigQuery） dbt データマート（BigQuery）データ加⼯‧設置バッチ Yappli Data Hub 1. 顧客毎に集計クエリを発⾏ 2. 結果をファイル送信 CMSダッシュボード ‧ Yappli Analytics

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Yappli Data Hubの基盤の全体像 1. 越境‧クエリ費⽤削減の経緯 Yappli アプリの操作ログサービスDB （Aurora）サービスDB （SQLite）内製トラッキング機構データレイク（BigQuery） dbt データマート（BigQuery）データ加⼯‧設置バッチ CMSダッシュボード ‧ Yappli Analytics Yappli Data Hub 1. 顧客毎に集計クエリを発⾏ 2. 結果をファイル送信

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

会社的にコスト削減の波が到来 ● Yappli Data Hubのクエリ費⽤がかなり⾼額になってしまっていた → なんとか削減できないか？ 1. 越境‧クエリ費⽤削減の経緯

Slide 21

Slide 21 text

なぜクエリ費⽤が⾼額に？ 1. 越境‧クエリ費⽤削減の経緯 Yappli アプリの操作ログサービスDB （Aurora）サービスDB （SQLite）内製トラッキング機構データレイク（BigQuery） dbt データマート（BigQuery）データ加⼯‧設置バッチ Yappliの管理画⾯ ‧ Yappli Analytics Yappli Data Hub 顧客毎の集計をデータレイクから中間テーブルを作らず直接⾏っていたから 1. 顧客毎に集計クエリを発⾏ 2. 結果をファイル送信

Slide 22

Slide 22 text

顧客毎の集計をデータレイクから中間テーブルを作らず直接⾏っていたからなぜクエリ費⽤が⾼額に？ 1. 越境‧クエリ費⽤削減の経緯テーブルA テーブルB 顧客1の集計クエリ顧客2の集計クエリ SELECT * FROM table_A JOIN table_B WHERE client_id = 1 SELECT * FROM table_A JOIN table_B WHERE client_id = 2 データレイク (BigQuery) 同じ処理を顧客毎に実⾏ || 無駄な費⽤が発⽣

Slide 23

Slide 23 text

解決策は事前に中間テーブルを⽤意（事前集計化）するようにすること ▼ DS室的には、 Yappli Data Hubへdbtを導⼊するチャンス！ ▼ dbtによる事前集計化を提案 1. 越境‧クエリ費⽤削減の経緯

Slide 24

Slide 24 text

コストインパクトの試算

Slide 25

Slide 25 text

どのくらいコスト削減できるのか？ ● 削減額が分かれば「どのくらいの温度感でやるべきか？」が分かる ● 今かかっている費⽤、事前集計化した場合の費⽤を試算して⽐較 ○ 両者の⽉額費⽤で⽐較 ● 試算はBigQueryのINFORMATION_SCHEMA.JOBSビューを利⽤ ○ total_bytes_billedカラムの値にBigQueryのクエリ費⽤と為替を掛けて計算 2. コストインパクトの試算

Slide 26

Slide 26 text

試算で分かったコストインパクト 2. コストインパクトの試算 Yappli Data Hubのクエリ費⽤を… Google Cloud全体の費⽤を… 約 20% 削減約 95% 削減チーム全体で「温度感⾼くやるべき」という共通認識ができた！

Slide 27

Slide 27 text

実⾏ジョブの時系列整理

Slide 28

Slide 28 text

Yappli Data Hub基盤の全体像（Before） 3. 実⾏ジョブの時系列整理 Yappli アプリの操作ログサービスDB （Aurora）サービスDB （SQLite）内製トラッキング機構データレイク（BigQuery） dbt データマート（BigQuery）データ加⼯‧設置バッチ Yappliの管理画⾯ ‧ Yappli Analytics Yappli Data Hub 1. 顧客毎に集計クエリを発⾏ 2. 結果をファイル送信

Slide 29

Slide 29 text

Yappli Data Hub基盤の全体像（After） 3. 実⾏ジョブの時系列整理 Yappli アプリの操作ログサービスDB （Aurora）サービスDB （SQLite）内製トラッキング機構データレイク（BigQuery） dbt データマート（BigQuery）データ設置バッチ Yappliの管理画⾯ ‧ Yappli Analytics Yappli Data Hub 1. 顧客毎に抽出クエリを発⾏ 2. 結果をファイル送信

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Yappli Data Hub基盤のジョブ実⾏タイミング 3. 実⾏ジョブの時系列整理 Yappli アプリの操作ログサービスDB （Aurora）サービスDB （SQLite）内製トラッキング機構データレイク（BigQuery） dbt データマート（BigQuery）データ加⼯‧設置バッチ Yappliの管理画⾯ ‧ Yappli Analytics Yappli Data Hub 1. 顧客毎に集計クエリを発⾏ 2. 結果をファイル送信 2h毎毎⽇AM2:00 リアルタイム SQLiteのBQ同期が完了次第開始（＝⽇次）毎⽇AM1:00 （コケたらAM2:00、 AM :00でリトライ）

Slide 33

Slide 33 text

Yappli Data Hub基盤のジョブ実⾏タイミング 3. 実⾏ジョブの時系列整理 Yappli アプリの操作ログサービスDB （Aurora）サービスDB （SQLite）内製トラッキング機構データレイク（BigQuery） dbt データマート（BigQuery）データ加⼯‧設置バッチ Yappliの管理画⾯ ‧ Yappli Analytics Yappli Data Hub 毎⽇AM2:00 SQLiteのAM1:00のBQ同期がコケたら、 AM2:00のdbtの実⾏開始に間に合わない…😱 h毎リアルタイム 1. 顧客毎に抽出クエリを発⾏ 2. 結果をファイル送信 SQLiteのBQ同期が完了次第開始（＝⽇次）毎⽇AM : （コケたらAM2:00、 AM :00でリトライ）

Slide 34

Slide 34 text

代替案を検討してみるが… ● dbtもSQLiteのBQ同期が完了次第開始にする ○ CMSダッシュボードの提供時刻に間に合わない ○ SQLiteテーブルの中にはデータソリューションで使っていないテーブルもある → 無関係なテーブルのBQ同期失敗で後続が全ストップ（＝インシデント）は怖い ● dbtを使わずYappli Data Hub側で独⾃に事前集計 ○ 似たような集計処理をdbtとYappli Data Hubで⼆重管理することになってしまう ○ 保守性が良いとは⾔い難い → どれも決め⼿に⽋ける…🤔 3. 実⾏ジョブの時系列整理なんとか予定通りdbtでいけるようにできないか？

Slide 35

Slide 35 text

Yappli Data Hubの集計クエリを精査分かったこと: ● 集計で使っているSQLite由来のテーブルは1つのみ ● SQLiteのBQ同期が間に合わなかったとしても、確定で全顧客のクエリ結果がおかしくなるわけではない ○ さらに特定条件下の顧客のみが影響範囲 ● 翌⽇の実⾏でちゃんと間に合えばリカバリーできる → 間に合わないリスクを許容できないか？ ● チーム内のSREに調査依頼 → 直近1年でそもそもBQ同期がコケたこと⾃体なし → 許容できると判断 3. 実⾏ジョブの時系列整理予定通り、dbtで事前集計化する⽅針に決定！

Slide 36

Slide 36 text

顧客影響との折り合い

Slide 37

Slide 37 text

既存のdbtモデルを使って事前集計化 4. 顧客影響との折り合い Yappli アプリの操作ログサービスDB （Aurora）サービスDB （SQLite）内製トラッキング機構データレイク（BigQuery） dbt データマート（BigQuery）データ設置バッチ Yappliの管理画⾯ ‧ Yappli Analytics Yappli Data Hub 1. 顧客毎に抽出クエリを発⾏ 2. 結果をファイル送信 dbtモデルA dbtモデルB Yappli Data Hub 事前集計⽤ dbtモデル ref() ref() 既存のdbtモデルを使って事前集計⽤dbtモデルを作成

Slide 38

Slide 38 text

クエリ結果が変わってしまう ● Yappli Data Hubの集計クエリには積年の技術的負債が存在 ● これまでのdbt導⼊で、既存のdbtモデルには様々な改善が施されている → 既存のdbtモデルを使ってYappli Data Hubの集計を⾏うと、クエリ結果が変わってしまう（例: 今まで5番⽬のカラムに⼊ってた値が2番⽬のカラムに⼊るようになる） → 詳細が気になる⽅は懇親会で！（そもそもクエリ作成⾃体が⼤変だった…） ‧ そもそもクエリが⻑い上に難読すぎる問題 ‧ クエリがまともに差分チェックできない問題 ‧ 真の⽣データ提供先顧客のマスタ、どれ…？問題 ‧ etc. 4. 顧客影響との折り合い

Slide 39

Slide 39 text

そのまま本番反映は顧客影響が⼤きすぎる ● ⽣データを提供サービスなので、クエリ結果が変わる場合は要顧客調整 ○ 提供した⽣データは顧客のCRM‧CDPへ取り込まれている → クエリ結果変更でCRM‧CDPの取り込み結果が変わると顧客の業務に⽀障が…😱 しかし… ● 要調整な顧客の数が多い ● 各顧客で社内調整も必要（＝時間が必要） ○ ⾃社のCRM‧CDPへの修正が必要なため → ‧ 顧客調整に⻑い時間が必要 ‧ 本番反映後にインシデントを起こしてしまった場合の対応も⼤変 4. 顧客影響との折り合い何とか顧客影響を⼩さく本番反映ができないか？

Slide 40

Slide 40 text

2フェーズに分割して進めよう 1. 今の集計クエリをそのままdbtモデル化 a. 既存のdbtモデルは使わず、Sourceテーブルを直接参照 b. クエリ⾃体はそのままなので、集計結果は変わらない → 顧客調整なしで進められる → 事前集計化によるクエリ費⽤削減をまずは達成 2. 既存のdbtモデルを使うよう改善 a. クエリ結果変更に伴う顧客調整も含めてじっくり対応 → 今の集計クエリの技術的負債を解消 4. 顧客影響との折り合い

Slide 41

Slide 41 text

Slide 42

Slide 42 text

得られた学び‧恩恵

Slide 43

Slide 43 text

得られた学び（⾏動⾯） ● ⽇頃の越境しての関係構築‧信⽤貯⾦は⼤事 ○ 元々のドキュメント整備や業務巻き取りで、データ基盤の全体像把握と⼀定の関係構築ができている状態だった → 今回のdbtで事前集計化する提案もしやすかった＆受け⼊れられやすかった ● 地道な基盤の全体像把握は⼤事 ○ ちゃんとしないといけないところ、妥協が許容されるところが⾒極められる ■ 今回の「SQLiteのBQ同期がコケるとdbt間に合わない問題」は全体像把握をちゃんと⾏ったことで、気づけた＆妥協が許容されることを⽰せた 5. 得られた学び‧恩恵

Slide 44

Slide 44 text

得られた学び（技術⾯） ● クエリ結果の変更が許容されにくい場⾯では… クエリを⼀旦そのままdbtモデル化すると良い ○ 次の2フェーズに分けて取り組む: ■ ① クエリをそのままdbtモデル化（Source直接参照） ■ ② dbt内で再モデリング（既存dbtモデルを参照するよう繋ぎかえ） ○ メリット: ■ 集計ロジックがdbtへ集約されている状況を早く作れる ● まだ再モデリングまで済んでいなくても、集計ロジック変更をdbt内で完結できる → 対応しやすい 5. 得られた学び‧恩恵

Slide 45

Slide 45 text

Yappli Data Hub基盤の全体像把握による恩恵 Yappli Data Hubについて、チーム外の⼈へ説明しやすくなった ● Yappli CRM開発チームとCRM基盤との⽴ち位置について議論 ○ CRM基盤で管理すべきデータ、Yappli Data Hub基盤で管理すべきデータの棲み分け ● Yappli Data Hub基盤との連携を検討している他の開発チームへのインプット ○ 意図しない連携⽅法でプロダクト側もYappli Data Hub基盤側も⾟い状況になってしまうのを防⽌ → 双⽅にとってベストな連携⽅法を模索しやすくできた 5. 得られた学び‧恩恵 Yappli Data Hubだけでなく、その周辺に対しても⼤きなプラスに！

Slide 46

Slide 46 text

今後の展望 Yappli Data Hubの集計クエリの技術的負債解消 1. 今の集計クエリをそのままdbtモデル化 a. 既存のdbtモデルは使わず、Sourceテーブルを直接参照 b. クエリ⾃体はそのままなので、集計結果は変わらない → 顧客調整なしで進められる → 事前集計化によるクエリ費⽤削減をまずは達成 2. 既存のdbtモデルを使うよう改善 a. クエリ結果変更に伴う顧客調整も含めてじっくり対応 → 今の集計クエリの技術的負債を解消 5. 得られた学び‧恩恵次ココ

Slide 47

Slide 47 text

FOLLOW ME!! Yappli Tech Blog Yappli Developers カジュアル⾯談 @yappli_dev https://tech.yappli.io/ https://open.talentio.com/r/1/c/yappli/pages/59642