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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

© 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

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

Slide 11

Slide 11 text

© 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生成開発フロー ● テスト ● ドキュメンテーション ● レビュー上記全て行いつつリリースまでの工程を短縮保守性を犠牲にスピードを獲得

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Slide 14

Slide 14 text

© 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 保守しやすい形とは？

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

© Recruit Co., Ltd. All Rights Reserved ドメイン知識を貯める 20 分析時のコンテキスト=ドメイン知識に応じて最適なモデリングは変わる売上など財務に関する分析が多い営業組織単位で分析することが多い売り上げは顧客毎に計上している営業組織は顧客毎に組閣している顧客で一意になるテーブルがあれば、両方の分析ニーズを満たすことができる経営企画室営業部リファクタリング案分析の傾向ドメイン知識

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

© 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の方がよく使われた事例紹介開発環境のデータと本番環境のデータが合致するのかをワンライナーで自動チェック

Slide 28

Slide 28 text

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

Slide 31

Slide 31 text

© 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)以上の数が紐づいていたらアラートしてくれる