dbt管理のクエリの異常をリリース前に検知するCIづくり

この記事は「Eureka Advent Calendar 2023」3日目の記事です。

· 本記事について
· コード
· 変更があったファイルのみ検査対象にする
· 最終的に実行されるクエリを検査する
· 下流のモデルのチェックも行う
· Sqlfluff の導入
· おわりに

Site & Data Reliability Engineer (以下、SDRE )の hisamouna です。

今年は、 SRE 業に加え Data Engineering 領域にも手を出し始めました。

まだまだ分からないことだらけですが、日々 Data Engineering の楽しさに触れています。

本記事について

eureka のデータ基盤(通称 Metis )では、データプラットフォームは Dagster で動いており、その中で data transformation ツールとして dbt を使っています。dbt cloud ではなく dbt-core です。

• 過去ブログ

Evolution of Eureka’s Data Platform

dbt の yaml と sql を管理するリポジトリがあり、コードオーナーは SDRE チームです。一部、データのレイヤーによっては分析チームがモデルの作成・更新を行なっています。

データ基盤のオーナーは SDRE チームであり、基盤の信頼性を高めることは責務の1つです。そのために、デプロイ前のクエリ異常チェックを手厚くしていき、事前に検知する仕組みづくりに注力しました。

本記事では、 Github Actions で Pull Request 時に発火するクエリ異常チェック CI について紹介できればと考えています。

コード

一部修正していますが、下記が Github Actions の steps です。

steps:
  - name: Checkout branch
    uses: actions/checkout@v3

  - name: Get changed files
    id: changed-model-files
    uses: tj-actions/changed-files@v18.7
    with:
      files: |
        models/1_metis/*
        models/2_staging/*
        models/3_analytics/*

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

  - name: 'Set up Cloud SDK'
    uses: google-github-actions/setup-gcloud@v1
    with:
      version: '>= 363.0.0'

  - name: Setup Python3
    uses: actions/setup-python@v3
    with:
      python-version: "3.9.x"

  - name: Install dbt
    run: pip install dbt-bigquery==1.6.4

  - name: Install dependencies
    run: dbt deps

  - name: dbt ls
    run: dbt ls

  - name: dbt compile
    if: steps.changed-model-files.outputs.any_changed == 'true'
    run: |
      for filepath in ${{ steps.changed-model-files.outputs.all_changed_files }}; do
        dbt compile --select "${filepath}+"
      done

  - name: bq dry-run
    if: steps.changed-model-files.outputs.any_changed == 'true'
    run: |
      for file in $(find "target/compiled/metis" -type f -name "*.sql"); do
        cat "${file}" | bq query --use_legacy_sql=false --dry_run
      done

  - name: Get changed files in 1_metis
    id: changed-model-files-mts
    uses: tj-actions/changed-files@v18.7
    with:
      files: |
        models/1_metis/*

  - name: Install SQLFluff
    if: steps.changed-model-files-mts.outputs.any_changed == 'true'
    run: pip install sqlfluff sqlfluff-templater-dbt

  - name: Lint SQL files
    if: steps.changed-model-files-mts.outputs.any_changed == 'true'
    run: |
      echo "ルールの詳細は、https://docs.sqlfluff.com/en/stable/rules.html を参照"
      sqlfluff lint --dialect bigquery ${{ steps.changed-model-files-mts.outputs.all_changed_files }

ざっくりとレイヤーの話をすると、1_metis レイヤーは raw レイヤーのデータをクレンジングして PII データや重複データの削除を施したレイヤーです。

そこから実際にアナリストが利用しやすいように加工されたデータの置き場が 3_analytics レイヤーです。前段の 2_staging レイヤーでは、データモデル間で共通化できる部分を個別にモデル化して保存しています。

レイヤー構成の見直しもしていきたいと考えており、別の機会で紹介できればと考えています。

CIの処理の中でいくつか工夫した点を深掘りしていきます。

変更があったファイルのみ検査対象にする

tj-actions/changed-files を導入しました。

with:
  files: |
    models/1_metis/*
    models/2_staging/*
    models/3_analytics/*

このように設定することで、特定のディレクトリ配下で更新があったファイルの一覧を取得し、

for filepath in ${{ steps.changed-model-files.outputs.all_changed_files }}; do

そのファイル一覧のみを処理対象にすることができます。

最終的に実行されるクエリを検査する

dbt は Jinja を使って sql ファイルを書くことができます。そのため、そのままの sql ファイルではクエリのバリデーションを行うことができません。

そのため、 dbt compile を実行し yaml と sql の設定を統合した実際に実行されるクエリを生成します。

- name: dbt compile
  if: steps.changed-model-files.outputs.any_changed == 'true'
  run: |
    for filepath in ${{ steps.changed-model-files.outputs.all_changed_files }}; do
      dbt compile --select "${filepath}+"
    done

そして、実際にクエリが実行される環境は BigQuery なので、 bq query --dry_run で実行環境上で問題がないかをチェックします。

BigQuery の記法上問題がないかも確認することができます。

- name: bq dry-run
  if: steps.changed-model-files.outputs.any_changed == 'true'
  run: |
    for file in $(find "target/compiled/metis" -type f -name "*.sql"); do
      cat "${file}" | bq query --use_legacy_sql=false --dry_run
    done

FYI ですが、これらの処理をまとめて行なってくれるパッケージもあります。

https://github.com/autotraderuk/dbt-dry-run/tree/main

下流のモデルのチェックも行う

修正したモデルは問題なくてもそのモデルを参照するモデル側でクエリ異常になるケースがあります。
例えば、カラムを削除した時です。あるモデル( src_table )のクエリが、

SELECT 
 col1,
 col2
FROM hoge

だったとして、その下流のモデル( dst_table )が、

WITH base AS (
SELECT
 *
FROM {{ ref(“src_table”) }}
)

SELECT 
 *
FROM base
WHERE col1 = "xxx"

だとします。

src_table から col1 を削除した場合、src_table は単純に col1 が削除されたモデルが生成されるだけで何も問題は起こりません。
しかし、dst_table はこのままだと未定義のカラムを参照していることになりモデルの生成に失敗してしまいます。

下流のモデルのクエリも修正する必要がありますが、どのモデルが依存しているかを常に意識して漏れなく直すというのはなかなか難しく、100%未然に防ぐことは現実的ではありません。
そのため、 CI の中で変更があったモデルの下流のモデルに対してもクエリチェックをするようにしました。

単純な変更ですが、dbt compile で — select を指定する際に、ファイルパスのサフィックスに + をつけることで 変更があったモデルを参照するモデルも対象にすることができました。

SQLFluff の導入

試験的に一部レイヤーで導入しています。

- name: Lint SQL files
  if: steps.changed-model-files-mts.outputs.any_changed == 'true'
  run: |
    echo "ルールの詳細は、https://docs.sqlfluff.com/en/stable/rules.html を参照"
    sqlfluff lint --dialect bigquery ${{ steps.changed-model-files-mts.outputs.all_changed_files }

.sqlfluff は最低限のことを記述しています。exclude_rules でとりあえずは遵守不要なルールを定めることができるのも良い点です。

(例)

[sqlfluff]
templater = dbt

# Rules Reference (https://docs.sqlfluff.com/en/stable/rules.html)
exclude_rules = jinja.padding,layout,capitalisation,,,

例えば、aliasing.unused ルールを入れておくことで、不要なサブクエリの検知ができます。

# unused.sql
with unused AS (
  SELECT * FROM source
)
, base AS (
  SELECT * FROM source
)
, final AS (
  SELECT
  *
  FROM base
)

SELECT * FROM final

└─> sqlfluff lint --dialect bigquery models/unused.sql                                
=== [dbt templater] Sorting Nodes...
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
== [models/unused.sql] FAIL                                                                                                                              
L:   2 | P:   6 | ST03 | Query defines CTE "unused" but does not use it.
                       | [structure.unused_cte]
All Finished 📜 🎉!

bq dry-run はあくまでクエリ異常の検知を担保するところなので、合わせ技で導入するのをお勧めします。

おわりに

CI 改善に着手し、未然にクエリ異常を防ぐことができ、データ基盤の信頼性を高めることができました。

とはいえ、まだまだ改善できるポイントはあります。

• レイヤ構造の見直し
• モデルの定期的な整理
• モデルごとのコスト可視化

これらに一緒に取り組んでくれるエンジニアを募集してるので興味があれば、カジュアル面談だけでもぜひご連絡ください！