SQLテスト計画を自動化！スタディサプリのデータ品質向上への挑戦

田口一輝

この記事をシェア

はじめに➡️

こんにちは、スタディサプリでアナリティクスエンジニアをしている田口一輝です。この記事は、SQLによるアドホックなデータ抽出業務に対してLLMやワークフローを駆使して、品質の向上と工数の削減に挑戦してみたという記事です。

取り組みの背景✍️

アナリティクスエンジニアが担当する業務の一つに、社内データ利用者向けのSQLによるアドホックなデータ抽出があります。SQLによるアドホックなデータ抽出は、PdMが仮説を立てたり、施策の効果振り返りに使うためのデータ抽出、CRM業務で使うユーザーリストの抽出など、様々なシーンで必要になり、依頼数が多いため、工数が多くかかっています。また、作業者のドメイン知識に依存している部分も多いため、品質の担保が難しいという問題がありました。

まず、スタディサプリにおけるSQLによるアドホックなデータ抽出の一連の流れを説明し、その中で問題を感じている部分を取り上げ、その部分に対する具体的な改善を説明していきます。

SQLによるアドホックなデータ抽出の流れ♻️

スタディサプリにおけるデータ抽出業務には、ざっくり以下のフェーズがあります。

登場人物

依頼者: 社内データ利用者（PdM、マーケター、CRMチームなど）

データチーム: スタディサプリのデータ組織

作業者: データチーム内でSQLを実装する担当者

レビュアー: データチーム内でレビューを担当する者

依頼会フェーズ

(依頼者）データ依頼会に抽出依頼を持ち込む
(データチーム）懸念や重要度などを確認し、依頼承認プロセスを通す

要件定義フェーズ

(依頼者・作業者）要件定義を行う
(データチーム）要件定義書に基づきレビューを行う
(依頼者・データチーム）要件に基づき納期のすり合わせを行う

実装フェーズ

(作業者）要件定義書に基づきSQLの実装とテストを行う
(レビュアー）SQLの実装とテストに基づきレビューを行う

納品フェーズ

(作業者）問題がなければ納品へと進む
(依頼者）納品物を確認する

アドホックな抽出における課題😰

多くの部署からの依頼が継続的に発生する

あらゆる組織からくるデータ抽出依頼は量が多いため、1件に対してかけられる時間に限界があります

依頼の抽象度が高い / 要件が整理されていない

依頼の内容が固まっていない、または整理されていないことも多く、整理・承認までに時間がかかってしまうケースがあります

レビュー工数が大きい

要件定義や長いSQLを細かくレビューするのには、かなりの負荷がかかりますが、ここで手を抜くと手戻りやミスが発生するため、念入りに論点を潰す必要があります
結果として、作業者とレビュアーのコミュニケーションが多く発生し、双方の負荷が高まることがあります

レビューの属人性

レビュー観点はレビュアー個人の知識や経験に依存しており、担当メンバーによってドメイン知識に差があるため、細かな論点が見落とされる可能性があります

本記事では多くある課題の中から、特に実装フェーズに関わる以下の二つの課題にフォーカスして取り組みをご紹介します。

テストの内容や細かさは作業者のスキルセットや経験に応じて差がでることがあり、結果としてテストの品質や実施項目にばらつきが生じうる
最低限のチェック項目がレビュアーの熟練度や知識に依存しているため、観点の抜け漏れが発生するリスクがある

型化・生成を自動化することでテストの品質の差分やチェック観点の漏れの問題と闘う🔥

サマリ

github の Pull Request時にテスト計画をLLMで自動生成するGithub Actions(GHA)を作成しました。 Pull Requestのコメント上部に要件定義書のスプレッドシートリンクを貼り、“テスト計画"というラベルを貼れば実行されます。これによって、最低限行うべきテストケースやチェック項目が標準化されました。

背景

スタディサプリのデータチームでは、アドホックなSQLをGithubリポジトリで管理しています。要件定義フェーズでは、要件定義書というカラムレベルで詳細な設計を記述したドキュメントを作成し、依頼者・作業者・レビュアーで合意することをルールとしています。要件定義書サンプルはこちら

要件定義書サンプル

LLMを使ってテスト計画を生成する

要件定義書に対してカラムレベル・SQL全体でのテスト計画を自動生成し、また、必ず組み込むべき条件などが漏れていないかをチェックする仕組みを、LLM（Vertex AI Gemini 2.0 Flash）を使って実装しました（モックの段階で一定の精度が確認できたため、モデル間での比較などは一旦見送り、最速で実装できる形で実装しました）。

プロンプトの工夫
- テスト観点を明示的に指定します（LLMに指定外の観点での自由な意見を許しません）
- テスト実行者の負担を下げるため、テストSQLのサンプルまで生成させます
- アウトプット形式のムラを減らすため、マークダウンの形式を指定し、プロンプトに例として盛り込みます
- 出力したテスト計画をそのままスプシに転記してチェックリストとして使えるよう、マークダウンの表形式で出力するように指定します
テスト項目（リクルート内の他事業領域で実施されていたSQLレビューの観点を参考に選定しました）
- カラムレベルでのテスト
  - not nullテスト
  - 許容値テスト
  - 数値の範囲テスト
  - 入出力一致テスト
  - 境界値テスト
  - CASE式テスト
- クエリレベルでのテスト
  - PKテスト
  - サブクエリのPKテスト
  - 全体の件数テスト
  - 特定必須条件テスト（このテーブルを使う時はこの条件をつけないといけないなどの制約）

生成されたテスト計画をちら見せ！

要件定義書のリンクをPull Requestに貼って、テスト計画ラベルをにつけると… CIが動き始めます！ CI動作中

ワクワクしながら待つと… 以下の二つのテスト計画が出力されます！生成されたテスト計画（カラムレベル）生成されたテスト計画（クエリレベル）

スプシに転記するとこんな感じ↓ スプシに転記してみると

GHAを使って業務フローに組み込む

Pull Requestにラベルをつけるとコメントでテスト計画が生成される仕様を採用しました。これは、アドホックなSQLをGithubリポジトリで管理しており、GitHub Actionsにすると以下のメリットがあったからです。

テスト計画ワークフローを起動できると情報を集約できる
レビュアーがレビューしやすい
履歴が残る
実行トリガーがPull Requestにラベルをつけるだけ

（toggleを押すと展開）pull requestにラベルをつけるとワークフローを起動するgha設定

name: Test Plan Generate
on:
  pull_request:
    types: [labeled]
permissions:
  id-token: write
  contents: read
  pull-requests: write
jobs:
  review:
    if: github.event.label.name == 'テスト計画生成' #テスト計画生成というラベルをつけるとワークフローが起動するようにしている

具体的なワークフロー

ワークフロー図

業務フローの変化

改善前：テスト計画はSQL構築の後にメンバーの判断で行われており、チェック観点漏れの可能性も高かった。テスト計画そのものはレビューされることがなかった。

改善後：テスト計画は要件定義時に半自動で生成され、最低限のテストは必ず行われるようになった。そして、テスト計画もレビューされるようになった。

注意ポイント

テスト計画の自動化は、あくまでベースとなるテストを生成するだけであり、複雑な要件などに対しては、追加でテストを実施する必要があることも当然あります。業務フローに導入する際には、これをやっておけば他はやらなくてもOKということではなく、あくまで最低限これは確認する部分をサポートするツールであることは、チーム内で念入りに合意をとっています。

実際運用してみて

記事執筆時点で2ヶ月ほど改善後の運用をしているのですが、実装者からは「テストの漏れが減った」「ラベルひとつで生成されるので気軽」「プロンプトにどんどんナレッジを入れていって、もっと網羅性を上げたい」などのコメントや、レビュアーからは「テスト観点の一覧と、なぜそれをやらないという決定をしたかなどが見えるようになったので、レビューしやすい」などのコメントをいただいており、運用負荷は高くはなく、テスト観点を網羅的に洗い出す習慣づけに役立っている印象です。

今後やっていきたいこと🚩

本記事では、SQLによるアドホックなデータ抽出業務において、工数を押さえつつ品質を担保するために、スタディサプリのデータチームで行ったテスト計画の自動化の取り組みを紹介しました。取り組みは始まったばかりで改善点も見えてきていますが、すでにテスト計画は必ずやるものという雰囲気の醸成などに貢献してくれています。今後の展望として、レビュアーや作業者の頭の中にだけあったチェック観点を吸い上げて、プロンプトを拡充していきたいと考えています。また、テスト計画だけでなく、要件定義書のレビューや、要件定義書に基づいてSQLのレビューを行うツールもリリースを予定しており、実装フェーズ以外のプロセスの問題も解決したいと考えています。

終わりに💫

今回の取り組みの良かった点は、検証までのスピード感を持ったことです。構想してから2-3日でほぼ運用しているものと変わらないモックを作成し、一部のメンバーに壁打ちしながら最低限の実装をし、とりあえずリリースして後から改善していくというメンタリティで取り組みました。

また、テスト計画作成に工数がかかったり業務適用が進んでない問題に対しても、テスト計画のベースをLLMで自動生成することで、負荷を抑えて運用装着に貢献しました。これは、品質の観点からやった方がいいことは明らかだが、工数制約がネックである取り組みをLLMを活用して実施できるようになった良い事例になったと感じました。

Recruit Tech Blog

タグから見つける