こんにちは☀️
タイミーでアナリストとアナリティクスエンジニアしてますokodoonです
今回の記事はdbt CloudでPull Requestを作るときに、レビュー負荷が高くなってしまっていた問題を解消できるように、コンパイル済みのSQLをPR上にコメントするような仕組みを作成したことについての紹介です。
もし同じような課題感を抱えている方がいらっしゃれば、参考にしていただければ幸いです
課題感
弊社のデータ基盤ではDWH層DataMart層は「分析用に加工されたデータを扱う層」として定義しています。
各種ドメインに依存した集計や変換のロジックが含まれるため、この層のモデリングに関しては基盤開発側のレビューのみでなくアナリスト観点でのレビューも必須となります。
ですが、分析ドメイン観点でのレビューが必要な場合に、純粋なSQLではないdbtモデルがアナリストレビューの障壁になることが多いです。
またアナリスト以外であっても「このmacroがどのようにコンパイルされるか」を把握するのは少し面倒だったりします
今回選択した解決策
そこでdbtモデルをコンパイルしたSQLファイルをPullRequest上にコメントするような仕組みを考えました。
この実装によって先ほど挙げた課題感が以下図のように解決されることを期待して開発しました
背景/前提
1. 開発環境について
弊社ではdbtを活用したデータ基盤の開発を行っており、
dbtモデル開発をdbt Cloud上の統合環境にて実施するような流れになっています。
2. CIジョブについて
CI用のdbt CloudのJobはRUN ON PULLREQUEST で実行されて、以下のようなコマンドが実行されています。
dbt build --select state:modified+
mainブランチとの差分があるdbtモデルがBigQuery上にビルドされている状態です（本来CIで走るdbtコマンドをレイヤーごとに分割していますが、ここでは簡略化しています）
3. CI環境のスキーマ名とcustom_schemas.yml戦略
dbt Cloudで設定できるCI Jobではtarget schema名の命名がdbt_cloud_pr_<job_id>_<pr_id>となっており、連携レポジトリのPR番号に対して動的に作成されます。
（DBT_JOB_SCHEMA_OVERRIDEでこの命名規則の上書きもできますが、PR単位でCIテーブルが作成されて欲しいので、デフォルトの命名規則にしたがっています。）
参考：https://docs.getdbt.com/docs/deploy/continuous-integration#how-ci-works
また、弊社では開発環境とCI環境のスキーマ名が {{ターゲットスキーマ名}}_{{カスタムスキーマ名}}になるように custom_schemas.sql で定義してあります。
そのため、CI環境のテーブルは
dbt_cloud_pr_<job_id>_<pr_id>_{{カスタムスキーマ名}}
の命名規則で作成されたデータセット名以下に作成されている状態です。
実装概要
作成したYAMLはこちら(クリックで展開)
name: DBT Compile and Comment on PR on: pull_request: types: [opened, synchronize, reopened] jobs: dbt_compile: runs-on: ubuntu-latest permissions: contents: read pull-requests: write id-token: write env: DBT_ENVIRONMENT: dev steps: - name: Check out repository code uses: actions/checkout@v3 with: fetch-depth: 0 ref: ${{ github.event.pull_request.head.ref }} - name: Fetch base ref run: git fetch origin ${{ github.event.pull_request.base.ref }} - name: Set Up Auth uses: "google-github-actions/auth@v1" with: token_format: "access_token" workload_identity_provider: "hogehogehoge" service_account: "hogehogehoge@hogehoge.iam.gserviceaccount.com" - name: Set up Cloud SDK uses: google-github-actions/setup-gcloud@v1 - name: Set up Python uses: actions/setup-python@v3 with: python-version: 3.11 - name: Install dependencies run: | python -m pip install --upgrade pip pip install dbt-core dbt-bigquery - name: Generate profiles.yml run: | chmod +x ci/compile_sql_comment/generate_profile.sh ci/compile_sql_comment/generate_profile.sh ${{ github.event.pull_request.number }} - name: Compile DBT id: compile run: | dbt deps dbt compile --profiles-dir . --target dev --profile timee-dwh compiled_sqls="" files=$(git diff --name-only origin/${{ github.event.pull_request.base.ref }} ${{ github.event.pull_request.head.ref }} | grep '\.sql$' || true) if [ -n "$files" ]; then for file in $files; do compiled_file_path=$(find target/compiled -name $(basename $file)) echo "Compiled file path: $compiled_file_path" if [ -n "$compiled_file_path" ]; then compiled_sql=$(cat "$compiled_file_path") compiled_sqls="${compiled_sqls}<details><summary>${file}</summary>\n\n\`\`\`\n${compiled_sql}\n\`\`\`\n\n</details>" fi done fi printf "%b" "$compiled_sqls" > compiled_sqls.txt - name: Comment on PR uses: actions/github-script@v6 with: script: | const fs = require('fs'); const output = fs.readFileSync('compiled_sqls.txt', 'utf8'); const issue_number = context.payload.pull_request.number; const owner = context.repo.owner; const repo = context.repo.repo; async function processComments() { const comments = await github.rest.issues.listComments({ owner: owner, repo: repo, issue_number: issue_number, }); const dbtComment = comments.data.find(comment => comment.body.startsWith('DBT Compile Result')); const body = `DBT Compile Result:\n${output}`; if (dbtComment) { await github.rest.issues.updateComment({ owner: owner, repo: repo, comment_id: dbtComment.id, body: body }); } else { await github.rest.issues.createComment({ owner: owner, repo: repo, issue_number: issue_number, body: body }); } } processComments();
actionsの流れを説明するとこうなります。
- PRブランチにチェックアウトして、PRブランチとmainの差分を確認するためにfetch
- workload_identity_providerを用いたBigQueryへの認証
- 必要パッケージのインストールと使用するパッケージの宣言
- PRの情報をもとにprofiles.ymlの動的生成
- コンパイル処理の実施
- dbt compileの実行
- mainとの差分があるファイルだけを抽出
- 差分ファイルのcompile結果を文字列化
- PR上にcompile結果がなければdbt compile結果を新規コメント。既にcompile結果がコメントされていたらコメントをupdate
各ステップの説明
説明が必要そうなステップの説明をしていきます
PRの情報をもとにprofiles.ymlの動的生成
上で述べた通り、CI環境のテーブルはdbt_cloud_pr_<job_id>_<pr_id>_{{カスタムスキーマ名}}の命名規則で作成されたデータセットに配置されています。
dbt compileの出力結果のデータセット名がdbt_cloud_pr_<job_id>_<pr_id>_{{カスタムスキーマ名}}になるように、デフォルトスキーマ名を動的に宣言するためのprofiles.ymlを作成するシャルスクリプトを作成しております
#!/bin/bash set -e cat << EOF > profiles.yml timee-ci-dwh: target: ci outputs: dev: type: bigquery method: oauth project: ci_env_project schema: dbt_cloud_pr_39703_$1 execution_project: ci_env_project threads: 1 EOF
参考: https://docs.getdbt.com/docs/core/connect-data-platform/bigquery-setup#oauth-via-gcloud
これによりdbt compileの出力結果が、このターゲットスキーマ名を参照したスキーマになります。
コンパイル処理の実施
run: | dbt deps dbt compile --profiles-dir . --target ci --profile timee-ci-dwh compiled_sqls="" files=$(git diff --name-only origin/${{ github.event.pull_request.base.ref }} ${{ github.event.pull_request.head.ref }} | grep '\.sql$' || true) if [ -n "$files" ]; then for file in $files; do compiled_file_path=$(find target/compiled -name $(basename $file)) echo "Compiled file path: $compiled_file_path" if [ -n "$compiled_file_path" ]; then compiled_sql=$(cat "$compiled_file_path") compiled_sqls="${compiled_sqls}<details><summary>${file}</summary>\n\n\`\`\`\n${compiled_sql}\n\`\`\`\n\n</details>" fi done fi printf "%b" "$compiled_sqls" > compiled_sqls.txt
- dbt compileをPRブランチで実行
- git diffで差分があった.sqlファイル名をdbt compileの実行結果であるcompile済みsqlファイルが格納される
target/compiled/以下でfindを実行してfileパスを取得 - fileパスのcat結果を折りたたみタグ内に格納して文字列に追加
って流れの処理にしています。
このような処理にすることでSQLコンパイル結果のPRコメントを必要分だけコメントできる形としました。また、折りたたみタグに格納することでPullRequestの可視性を損なわないようにしました。
PR上にコメント
- name: Comment on PR uses: actions/github-script@v6 with: script: | const fs = require('fs'); const output = fs.readFileSync('compiled_sqls.txt', 'utf8'); const issue_number = context.payload.pull_request.number; const owner = context.repo.owner; const repo = context.repo.repo; async function processComments() { const comments = await github.rest.issues.listComments({ owner: owner, repo: repo, issue_number: issue_number, }); const dbtComment = comments.data.find(comment => comment.body.startsWith('DBT Compile Result')); const body = `DBT Compile Result:\n${output}`; if (dbtComment) { await github.rest.issues.updateComment({ owner: owner, repo: repo, comment_id: dbtComment.id, body: body }); } else { await github.rest.issues.createComment({ owner: owner, repo: repo, issue_number: issue_number, body: body }); } }
PR上のコメントの一覧を取得して、既に DBT Compile Result:で始まるコメントがPR上に存在するのであれば、そのコメントのアップデート、存在しないのであれば新しくコメントをする。
という処理をしています。
条件分岐が発生する処理を簡便に記載したかったのでinlineでjavascriptを記載できるgithub-scriptを使用して記述しています
これによってactionが走るたびにコメントが新規でされるのではなく、一つのコメントが上書きされ続けるような処理となり、PullRequestの可視性を損なわないようにしました。
どんなふうに動くかみてみる
こんなモデルをテストで作ってみました
{{ config(
schema = 'sample_schema'
)}}
SELECT
'hogehoge' AS name
, 30 AS amount
UNION ALL
SELECT
'fugafuga' AS name
, 50 AS amount
{{ config(
schema = 'sample_schema'
)}}
SELECT
SUM(amount) AS sum_amount
FROM {{ ref('sample_model1') }} AS sample_model1
以下のようにPullRequest上に変更内容が折り畳まれた状態でコメントされて、ref関数のコンパイル結果がCI環境のデータセットになっていることを確認できます
 |
 |
次にsample_model1.sqlを以下のように修正して再度pushしてみます
{{ config(
schema = 'sample_schema'
)}}
SELECT
'hogehoge' AS name
, 30 AS amount
UNION ALL
SELECT
'fugafuga' AS name
, 50 AS amount
UNION ALL
SELECT
'blabla' AS name
, 100 AS amount
以下のように既存のcommentがeditされて、修正後の内容でコンパイルした結果で上書きされていることが確認できます
 |
 |
結果
弊社のデータ基盤はfour keys計測による開発ヘルススコアの計測を行っており、今回の仕組みのリリース前後で開発リードタイムを計測してみましたが、目立った影響は出ていませんでした😢
レビューを円滑にできる環境を整っていないことが課題ではなく、他業務との兼ね合いでDWH開発のレビューに充てることができる時間がそもそも少なそうだったり、レビューに必要なドメインのインプットが足りていないことがボトルネックになっていそうだなという発見にも繋がったので、そこはプラスに捉えています
メンバーの声を聞いていると便利なことは間違いないらしいので、レビューコストの低減による持続的なトイル削減に将来的には繋がっていけばいいなと思ってます🙏
使えそうだな。試してみようかなと思っていただけたら幸いです！
We’re Hiring!
タイミーのデータ統括部では、ともに働くメンバーを募集しています！！
このたび、アナリティクスエンジニアのポジションが公開されたので、是非ともご応募お願いします
その他ポジションも絶賛募集中なのでこちらからご覧ください
