こんにちは☀️
タイミーでアナリストとアナリティクスエンジニアしてますokodoonです

今回の記事はdbt CloudでPull Requestを作るときに、レビュー負荷が高くなってしまっていた問題を解消できるように、コンパイル済みのSQLをPR上にコメントするような仕組みを作成したことについての紹介です。

もし同じような課題感を抱えている方がいらっしゃれば、参考にしていただければ幸いです

課題感
- 今回選択した解決策
背景/前提
実装概要
各ステップの説明
どんなふうに動くかみてみる
結果
We’re Hiring!

課題感

弊社のデータ基盤では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!

タイミーのデータ統括部では、ともに働くメンバーを募集しています！！

このたび、アナリティクスエンジニアのポジションが公開されたので、是非ともご応募お願いします
その他ポジションも絶賛募集中なのでこちらからご覧ください

Timee Product Team Blog

タイミー開発者ブログ

dbtCloudから作成したPullRequestにコンパイル済みSQLをコメントする仕組みを作成した話

課題感