2年運用した dbt プロジェクトを リファクタリングする

Transcript

1. © Recruit Co., Ltd. All Rights Reserved 2年運用した dbt プロジェクトを

   リファクタリングする 株式会社リクルート データ推進室 D3M 部 森田 順也 2023/03/28

2. © Recruit Co., Ltd. All Rights Reserved 自己紹介 森田 順也

   （もりた じゅんや） @jjjjj_kn データ推進室 データテクノロジーユニット D3M 部 アナリティクスエン ジニア SIerにてデータ異常検知の研究開発に携わった後、2019年にリクルー トへ入社。アナリティクスエンジニアとして分析基盤の開発に従事。 data saber。 Podcast https://open.spotify.com/show/6Xc6jNS2p1UqjnPppwO80V?si=WXCP2O58RrePvsjTMomtIw Zenn https://zenn.dev/mjunya1030 2 #recruitdata

3. © Recruit Co., Ltd. All Rights Reserved 本日お話しすること カーセンサーでは、2020年にdbtの検証を始め、導入して2年以上が経っていま す。長期間運用する中で、分析のスピードをとにかく早めるため試行錯誤を重ね

   てきました。 本日はこの試行錯誤の一つである、リファクタリングの推進を深ぼってお伝 えしたいと思います。 3

4. © Recruit Co., Ltd. All Rights Reserved 4 ご存知でしょうか カーセンサー

   実は、単なる中古車情報サイト ではないんです

5. © Recruit Co., Ltd. All Rights Reserved CASE: 自動車業界は100年に一度の大変革時台 5

   Connected Autonomous Shared & Services Electrification コネクティッド 自動化 シェアリング 電動化

6. © Recruit Co., Ltd. All Rights Reserved クルマの利用シーンを包括的に支援するプラットフォームへ 6 新車

   買いたい 中古車 買いたい 新車 リースしたい 中古車 リースしたい 借りたい カスタマー クライアント Mission クライアントの商いを拡げ、 それによって、 ユーザーの選択肢を拡げる。

7. © Recruit Co., Ltd. All Rights Reserved 私たちのMission: あらゆる業務をデータで牽引すること 7

   カーディーラー様に 売れ筋の車種を提案したい！ 新規事業で追うべき KPIを 見つけたい！ 集客の効果・コストを モニタリングしたい！ 営業 企画 マーケティング 提案にそのまま持っていける BIツールを提供 さまざまな軸でドリルダウンでき るデータソースを提供 数値定義を統一した KPIダッシュボードを提供 さまざまな分析ニーズに対応する必要がある

8. © Recruit Co., Ltd. All Rights Reserved リファクタリングが必要になった経緯 8

9. © Recruit Co., Ltd. All Rights Reserved 分析サイクルのスピードはビジネスによってさまざま 9 分析サイクル

   課題設定→ログ設計→データ収集→分析→行動... ref. adobe SLOW: 集客効果のモニタリング 月次、年次で統一されたKPIを定常的に振 り返る FAST: 新機能の初速分析 毎週のように課題発見、打ち手実施を行 い、NorthStar となる指標を変えながら 振り返る サイクルの早い分析に どう対応するか？

10. © Recruit Co., Ltd. All Rights Reserved adhoc レイヤーの導入 10

    生成されるモデルが全て view であり、更新jobが不要 で、モデリングルールを無視でき、 通常の開発フローから切り出してリリースできるレイヤー Source Staging Mart Reporting Adhoc

11. © Recruit Co., Ltd. All Rights Reserved 開発着手 Pull Requrest

    Main Merge Tag adhoc レイヤーの導入 11 生成されるモデルが全て view であり、更新jobが不要 で、モデリングルールを無視でき、 通常の開発フローから切り出してリリースできるレイヤー non-adhoc adhoc dbt SQL設計・実装 dbt SQL設計・実装 dbt JOB設計・実装 開発環境デプロイ 開発環境デプロイ 開発JOBの実行・テーブル生成 開発デプロイ前レビュー 本番デプロイ前レビュー 開発環境データテスト 開発環境データテスト 本番デプロイ前レビュー 本番環境デプロイ 本番環境デプロイ 本番JOBの実行・テーブル生成 本番環境データテスト /Doc生成 本番環境データテスト /Doc生成 開発フロー • テスト • ドキュメンテーション • レビュー 上記全て行いつつ リリースまでの工程を短縮 保守性を犠牲に スピードを獲得

12. © Recruit Co., Ltd. All Rights Reserved adhoc レイヤーのクエリはリファクタリング前提 12

    dbtを使ったクエリのリファクタリングについてご紹介します 本日のメインテーマ!

13. © Recruit Co., Ltd. All Rights Reserved データパイプライン開発の リファクタリングとは何か 13

14. © Recruit Co., Ltd. All Rights Reserved You Ain’t Gonna

    Need It 14 「こんなこともあろうかと」と思って作ったテーブ ルは大抵使われない。 先に仕様を満たすテーブルをリリースし、後から保守 しやすい形に変えるほうが価値を最大化できる。 Source Staging Mart Adhoc Adhoc Adhoc Source Staging Mart reporti ng reporti ng reporti ng Source Adhoc Adhoc Adhoc Source Staging Mart 保守しやすい形とは？

15. © Recruit Co., Ltd. All Rights Reserved 保守しやすいデータパイプラインは永遠のテーマ 15 パイプライン開発の課題を解く方法はたくさんある

    都度設計について議論・設計していてはリファクタリングが進まない UU数算出の計算が 多数のクエリで登場している 思わぬところでBIツールや Jobから参照されている 加工・結合が乱立していて処 理を読み解きにくい データパイプラインにもデザインパターンが欲しい テーブルを分ける？ クエリにコメントを書く？ マクロでまとめる？ 中間テーブルにする？ 不用意な参照を禁止？ リネージをメタ情報に記載？

16. © Recruit Co., Ltd. All Rights Reserved モデリング:データパイプライン開発のデザインパターン 16 Source

    Staging Mart Reporting データパイプラインのリファクタリングとは、 モデリングのルールを作り、クエリをルールに寄せていくこと Source Adhoc 一次加工的 な処理 複数のCTEの 結合処理 BIレポート用に 集計する処理

17. © Recruit Co., Ltd. All Rights Reserved カーセンサーで導入したモデリングのルール 17 Source

    Staging Mart Reporting 1要件:1テーブルとし、 要件外から参照させない ユーザーごとの粒度に 揃え、ユーザーに関する 分析はこのテーブルを 必ず使う 元のデータの単純変換 のみとし、Join等を行 わない。 参照先テーブルを限定さ せて不具合時の影響範囲 を絞る SSoTとして機能させ、 指標の定義ズレをなく す 1次加工とドメインに依 存するロジックを分離 し、見通しをよくする ルール 目的 事例紹介 このルールに寄せれば “ある程度” リファクタリング可能

18. © Recruit Co., Ltd. All Rights Reserved “良い” リファクタリングとは 18

19. © Recruit Co., Ltd. All Rights Reserved 不吉な匂いから課題を見つける 19 解決したい課題を言語化し、既存のモデリングルールを適用して課題が解けそうか確認し、解けなければ

    ルール自体を見直す。 不吉な匂い 500行を超えるような長大なクエリがあ る 全く同じ加工処理がある(CASE文、 CAST等) 粒度が同じテーブルがある 特定の用途で作られたテーブルが、意図し ないテーブルから参照されている 課題 クエリを保守できる人が限定されてしまい改 修が進みにくくなっていないか？ ロジック変更時に修正する箇所が多く、抜け漏 れを起こしていないか？ どちらのテーブルを参照すべきか判断がつか なくなっていないか？ テーブルを修正したら思わぬ場所に影響が出 てしまいデグレを起こしていないか？

20. © Recruit Co., Ltd. All Rights Reserved ドメイン知識を貯める 20 分析時のコンテキスト=ドメイン知識に応じて最適なモデリングは変わる

    売上など財務に関する分 析が多い 営業組織単位で分析する ことが多い 売り上げは顧客毎に計上している 営業組織は顧客毎に組閣している 顧客で一意になるテーブルがあれば、両方の分析ニーズを満たすことができる 経営企画室 営業部 リファクタリング案 分析の傾向 ドメイン知識

21. © Recruit Co., Ltd. All Rights Reserved ドメイン知識をディメンショナルモデリングに応用 21 ファクト=粒度を導出する

    ディメンション=属性を導出する 事例紹介 年月と車台番号の粒度でデータを持 たせておく 複数の属性から導けるボディタイプ という属性を作っておき、不要な属 性を減らす カーディーラー様が、カーセンサーの 掲載効果を月次でチェックしている 車は、ボディタイプによって人気や売 れる地域が大きく変わる 年月と車台番号に関する分析が多い ボディタイプでフィルタする分析が多い 分析の傾向 ドメイン知識 リファクタリング案

22. © Recruit Co., Ltd. All Rights Reserved リファクタリングをやり切るには 22

23. © Recruit Co., Ltd. All Rights Reserved 振る舞いを変えず、内部処理を変える 23 Source

    @prd Adhoc Staging Mart Adhoc リファクタリング前 リファクタリング後 Source 振る舞い 最終的なテーブルのデータ が変わってない 内部処理 中間テーブルが 増えている 振る舞いを定義して、テストすることがゴール

24. © Recruit Co., Ltd. All Rights Reserved 外部に公開・提供しているテーブルを振る舞いとする 24 Source

    Staging Mart adhoc Reporting Ephemeral 営業部 UI/UXチーム 経営企画室 振る舞い 内部処理 ※レイヤーと 内部処理/振る舞い がある程度対応するモデリングルールのほうが扱いやすいことが多い

25. © Recruit Co., Ltd. All Rights Reserved Exposureを用いて振る舞いとなるテーブルを管理 25 事例紹介

26. © Recruit Co., Ltd. All Rights Reserved 振る舞いをテストする 26 テーブルの一意性が崩れていないかに加え、変更前後で完全合致するかを確認する

    スキーマテスト リファクタリング前後で、テーブルの一意性・参 照整合性が変わってないことをテストする。 - unique 結合処理を書き換えるとレコードの重複が起 こりやすいため、uniqueテストで検知する - not_null 抽出・結合処理を書き換えるとデータの欠損 が起こりやすいため、not_nullテストで検知 する - relation 抽出・結合処理を書き換えるとデータの欠損 が起こりやすいため、参照整合を保てている かで検知する 完全合致テスト リファクタリング前後で、データが一切変わって いないことをテストする。 ただし、環境の違いによってデータが変わるた め、下記のようにテストコードを用意しておく。 - カラムの除外・指定 タイムスタンプなど、テーブル生成時刻で値 の変わるカラムを除外できるテストコードを 用意する - 抽出条件の指定 最新断面同士の比較など、比較対象を限定 できるようテストコードを用意する

27. © Recruit Co., Ltd. All Rights Reserved 完全合致テストの自動化 27 $

    dbt build -s is_same --vars \ '{target_table: "sample_table", except_columns: "column1,column2", where_condition: column3="HOGE"}' 14:48:01 Running with dbt=1.2.1 14:48:01 Unable to do partial parsing because config vars, config profile, or config target have changed 14:48:04 Found 132 models, 577 tests, 0 snapshots, 0 analyses, 739 macros, 0 operations, 2 seed files, 138 sources, 24 exposures, 0 metrics 14:48:04 14:48:11 Concurrency: 4 threads (target='dev') 14:48:11 14:48:11 1 of 1 START table model dsu_treasure_data.is_same ............................. [RUN] 14:48:14 1 of 1 OK created table model dsu_treasure_data.is_same ........................ [CREATE TABLE (0.0 rows, 1.1 MB processed) in 3.21s] 14:48:14 14:48:14 Finished running 1 table model in 0 hours 0 minutes and 9.75 seconds (9.75s). 14:48:14 14:48:14 Completed successfully 14:48:14 14:48:14 Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1 ※実際のところ、このコマンドはエラー時の解析が手間なので、コンパイルしたSQLの方がよく使われた 事例紹介 開発環境のデータと本番環境のデータが合致するのかをワンライナーで自動チェック

28. © Recruit Co., Ltd. All Rights Reserved 終わりに 28

29. © Recruit Co., Ltd. All Rights Reserved リファクタリングと dbt の持つ魅力

    29 事前に将来の分析ニーズを把握し、汎用的なデータマートを用意することは難し い。 アドホックなクエリを許容しつつ、ドメイン知識を吸収してリファクタリングを進め るほうが、分析アウトプットも多く出せて、良いモデリングに近づける。 dbt は、リファクタリングを積極的に行える状態を作るのに最適 - モデリングルールの適用 - 振る舞いの定義 - データのテスト

30. © Recruit Co., Ltd. All Rights Reserved カーセンサーには、モダンな分析環境、大規模 なデータ、チャレンジングな課題が揃っていま す。

    アナリティクスエンジニアリングを駆使して 一緒にビジネスを Drive して行く人を、 お待ちしています！ 30

31. © Recruit Co., Ltd. All Rights Reserved <参考>dbt-project-evaluator 31 dbt-project-evaluator

    という dbt project の良し悪しを判断する package も存在する。 https://docs.getdbt.com/blog/align-with-dbt-project-evaluator $ dbt build --select package:dbt_project_evaluator,+fct_model_fanout {{略}} 04:31:40 Completed with 1 warning: 04:31:40 04:31:40 Warning in test is_empty_fct_model_fanout_ (models/marts/dag/dag.yml) 04:31:40 Got 8 results, configured to warn if != 0 04:31:40 04:31:40 compiled SQL at target/compiled/dbt_project_evaluator/models/marts/dag/dag.yml/is_empty_fct_model_fanout_.sql 04:31:40 04:31:40 Done. PASS=14 WARN=1 ERROR=0 SKIP=0 TOTAL=15 ← 参照されているモデル を洗い出し、閾値(=5)以上 の数が紐づいていたらア ラートしてくれる