この記事は「dbt Advent Calendar 2024」の16日目の記事です。

はじめに

こんにちは、hoppy(@its_my_hoppy)といいます。
業務でdbtを触るようになってから1年以上経ち、多少はdbtとも仲良く慣れたような気がしています。
dbtには非常に便利なパッケージが数多くありますが、今回はその中でも dbt-audit-helper に焦点を当てて紹介します。このパッケージを活用することで、クエリ間の差分を簡単に比較することができ、特にリファクタリング時に役立ちます。この記事を読んで、「使ってみたい！」と思っていただけると嬉しいです。

dbt-audit-helperとは

dbt-audit-helper は、2つのクエリを比較するためのパッケージです。
業務で既存のクエリをdbtで管理したり、dbtモデルをリファクタリングする際、「変更前後で実行結果に差分がないか」を確認することが必要になります。

私の場合この確認作業は多くの場合、集計単位で行っていました。しかし、dbt-audit-helper を利用することで以下が可能になります：

行単位で差分を比較
差分が発生しているカラムを特定

これにより、従来よりも効率的かつ正確に結果の検証が行えます。

実装

今回実装したのはタイトル通りdbt-audit-helperをGithub Actions(以下GHA)で動かす仕組みです。
想定ユースケースとしてはdbt modelのリファクタリングをしたときとなっています。
また今回はaudit-helperの中でもquick_are_queries_identicalという2つのクエリの全行のハッシュを取り、比較することでリファクタリングによって変更が加えられていないことを保証することができるmacroを利用します。
quick_are_queries_identicalのデフォルトでのアウトプット例は下記のようになっています。

結果が true であれば差分なし、false であれば差分ありというシンプルな出力形式です。

今回構築した仕組みの処理フローは以下の通りです

以下は今回実装したGHAのYAMLファイルです。

audit_helper.yml

name: audit_helper

on:
  push:
    branches:
      - main
  pull_request:
      types: [labeled]
env:
  DBT_PROFILES_DIR: ${{ github.workspace }}/.github/.dbt/

permissions:
  contents: write
  pull-requests: write

jobs:
  audit_helper:
    if: ${{ github.event.pull_request.state == 'open' && github.event.label.name == 'audit_helper' }}
    runs-on: ubuntu-latest

    steps:
      - name: Checkout repository
        uses: actions/checkout@v2
        with:
          fetch-depth: 0

      - name: Authenticate to Google Cloud
        uses: google-github-actions/auth@v1
        with:
          credentials_json: ${{ secrets.json_file_name }}

      - name: "Set up Cloud SDK"
        uses: google-github-actions/setup-gcloud@v2
        with:
          version: "任意のversion"

      - name: Setup Python3
        uses: actions/setup-python@v3
        with:
          python-version: "任意のversion"

      - name: Install dbt and related packages
        run: |
          pip install dbt-bigquery==任意のversion

      - name: Install dependencies
        run: dbt deps
        
      # mainにcheckoutしたときもfile_pathを使いたいから変数に入れておく
      - name: Get new.sql path
        id: get_diff
        run: |
          file_path=$(git diff --name-only origin/main...HEAD -- 'models/**/*.sql')
          echo "file_path=$file_path" >> $GITHUB_ENV 
          
      # リファクタリング後のクエリの中身を取得
      - name: Get new.sql content
        run: |
          cat "$file_path" > new.sql
        env:
          file_path: ${{ env.file_path }}
          
　　　 # mainのリファクタリング前のクエリの中身を取得
      - name: Get old.sql content
        run: |
          git fetch origin main
          git switch main
          cat "$file_path" > old.sql
        env:
          file_path: ${{ env.file_path }}

      # audit-helperのtemplateに取得したクエリを挿入してSQLファイルとして書き出す
      - name: insert audit_helper template
        run: |
          # 各ファイルの内容を変数に読み込む
          old_content=$(<old.sql)
          new_content=$(<new.sql)

          # `audit.sql` にテンプレート形式で書き出し
          cat <<EOF > audit.sql
          {% set old_query %}
          $old_content
          {% endset %}
          {% set new_query %}
          $new_content
          {% endset %}
          {{ audit_helper.compare_queries(
            query_a = old_query,
            query_b = new_query,
            columns=['item_name', 'item_id', 'item_category_name']
          ) }}
          EOF

　　　 # 書き出したSQLファイルをBigQueryで叩けるようにcompileする
      - name: compile audit.sql
        run: |
          # compileするためにmodels配下に移動させる
          mv audit.sql models/
          dbt compile -s models/audit.sql
          
      # BigQueryでcompileしたクエリを叩いて結果をjsonファイルに書き出す
      - name: Run compiled SQL in BigQuery
        run: |
          # コンパイルされたSQLファイルを読み込み
          compiled_query=$(cat target/compiled/project_name/models/audit.sql)
        　 
          echo "$compiled_query" | bq query --use_legacy_sql=false --format=prettyjson > query_result.json

      # jsonの中身をコメントでPR上に表示する
      - name: Post query result to PR
        if: github.event_name == 'pull_request'
        uses: actions/github-script@v6
        with:
          script: |
            const fs = require('fs');
            const result = fs.readFileSync('query_result.json', 'utf8');
            const jsonData = JSON.parse(result);
            const formattedResult = JSON.stringify(jsonData, null, 2);

            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: `### BigQuery Execution Result\n\`\`\`json\n${formattedResult}\n\`\`\``
            });

quick_are_queries_identicalは下記のテンプレートを利用することで動かすことができます。

compare_queries.sql

-- クエリの中身は例です
{% set old_query %}
  select
    hoge
    fuga
  from {{ ref('foo') }}
{% endset %}

{% set new_query %}
  select
    hoge
    fuga
  from {{ ref('foo') }}
{% endset %}

{{ audit_helper.compare_queries(
    query_a = old_query,
    query_b = new_query,
    columns=['order_id', 'amount', 'customer_id'] -- requiered
) }}

今回の場合は{% set old_query %}の中にリファクタリング前のクエリを、{% set new_query %}の中にリファクタリング後のクエリを挿入する必要があるのでGHA上で変数に代入しておいてテンプレートに入れています。
またcolumnsの中には2つのクエリにあるカラム名を入れておきます。
そこからBigQuery上で叩けるようにcompileして実際にクエリを発行して結果を取得するようにしています。

実際に動かしてみると下記のように結果が出力されます。

trueとなっているので差分がないことがわかりました。

また今回GHAを動かすトリガーをPRでlabelを貼ったタイミングとしています。
理由としては特に比較が必要ないPRでも動いてしまうとノイズになってしまうため、動かしたいときだけ動かせるようにするためです。

おわりに

dbt-audit-helper を初めて利用しましたが、クエリ間の差分を即座に確認できる点は非常に便利で、業務で活用できる場面が多いと感じました。また、GHAを活用することで、手作業を省きつつ効率的に差分確認を行える仕組みを構築できました。

今回は簡易的に動作する実装としましたが、次の点を改善することで、さらに汎用性の高いActionsとして運用できると考えています

audit-helperのテンプレートにベタ書きしている
compileしたクエリを実際に叩く前にコストをチェックしていない

長くなってしまいましたがここまで読んでいただきありがとうございました！
ぜひX(@its_my_hoppy)のフォローもよろしくおねがいします！

参照

dbt-labs/dbt-audit-helper