業務部門が書いたデータ抽出のSQLを、社内の命名規約とBigQueryの性能の観点で点検し、直した版と理由を返す

処理の起点を決める

共有のフォームの送信を起点にします。 Google Apps Script のインストール型トリガーには、フォームの回答の送信で動くものがあります。インストール型トリガーは、作った人のアカウントで動きます。 個人のアカウントで作ると、その人が異動したときに止まり、BigQuery への権限もその人のものになります。データ基盤チームの共有の作業用アカウントで作ります。

1回の送信で1本のSQLを点検します。複数のSQLを1回の依頼にまとめさせません。 まとめると、どの直しがどのSQLのものか分からなくなります。フォームの項目は、SQL本文、目的（何に使うか）、想定する期間、定期のレポートに組み込むかどうか、の4つです。

Apps Script の1回の実行は6分までです。点検と突き合わせが6分に収まらないSQLは、突き合わせを後回しの実行に分けます。 一覧に「突き合わせ待ち」と書き、時間主導型のトリガーで続きを動かします。

入力データを集める

質を決めるのは、3行目と5行目です。 表の情報が無いと、AIはどの列がパーティションの列かを推測し、実在しない列の名前で条件を書きます。 観点の一覧が無いと、AIは一般的なSQLの書き方で直し、社内の決まりと違う直しを返します。

目的と期間は、性能の直しの判断に使います。 「直近3か月の店舗別の売上」が目的なら、日付の条件が無いSQLは全期間を読んでいることになります。目的に書かれていない期間の条件を、AIが足すことはさせません。 足すべきかを指摘し、足すかは依頼者が決めます。

データの取得方法を決める

Apps Script からは、BigQuery の拡張サービスを有効にして呼びます。拡張サービスは公開されている API と同じオブジェクト・メソッド・パラメータを使うとされているので、jobs.query の dryRun をそのまま渡せます。

useLegacySql: false を必ず指定します。 jobs.query の useLegacySql は既定が true です。指定を忘れると、標準のSQLで書かれたSQLが構文の誤りとして返ります。 誤りの原因がSQLではなく呼び方にあるので、気づきにくい失敗です。

メタデータの権限は、メタデータの閲覧だけの役割で足ります。 INFORMATION_SCHEMA.COLUMNS を読むには roles/bigquery.metadataViewer などの役割が使えます。点検のスクリプトに、表の中身を読む権限を広く持たせません。 中身を読むのは、7番目の突き合わせのときだけです。

AIへ渡す前に整形する

1. SELECT文かの確認 … 先頭が SELECT か WITH で、INSERT・UPDATE・DELETE・MERGE・CREATE・DROP を含まないことを確かめます。含むものは点検せずに返します
2. コメントの扱い … -- と /* */ のコメントは残します。依頼者の意図が書かれていることが多いからです
3. 表の名前の取り出し … プロジェクト・データセット・表の3つに分け、WITH句の名前を除きます
4. 元のSQLのドライラン … 構文の誤りがあれば、点検の前に誤りの文を依頼者に返します
5. 読み込む量の記録 … 元のSQLの処理される量を記録します。直した版との比較の基準になります
6. 0バイトの印 … 行レベルのセキュリティがかかった表や外部データソースを読むSQLで0バイトが返ったら、印を付けます
7. 表の情報の圧縮 … 列が多い表は、SQLで使っている列と、パーティション・クラスタの列だけに絞って渡します

1番目をAIに任せずにプログラムで行うのが要点です。 更新を含むSQLを点検の流れに入れると、7番目の突き合わせで更新が実行されてしまいます。 正規表現で弾き、迷うものは点検しません。

4番目で構文の誤りを先に返すのも、同じ理由です。 動かないSQLをAIに渡すと、AIは誤りを直したうえで性能の直しを重ね、どこが構文の直しでどこが性能の直しか分からない版が返ります。

AIに処理させる

させるのは、観点の一覧に沿って問題を拾い、直した版を書き、直しごとに観点の番号と理由と「結果が変わりうるか」を付けることです。

3列目の印が、この構成でいちばん大事な区別です。 印が「変わる」「書き方によって変わる」の直しを含むときだけ、7番目の突き合わせを動かします。命名の直しだけなら突き合わせは要りません。

5行目がいちばん起きやすい失敗です。 書き換えた理由に「結果は変わりません」と添えるのは自然な書き方ですが、その一文を信じて突き合わせを省くと、第3章の（c）がそのまま起きます。

指示内容を固定する

あなたは社内のデータ基盤チームで、業務部門の担当者が書いたSELECT文を
点検する担当者を手伝う立場です。
渡された「点検の観点の一覧」に沿ってSQLを点検し、直した版と、
直しごとの理由を返してください。

【厳守事項】
- 直しは、観点の一覧に載っている項目に当たるものだけにしてください。
  一覧に無い好みの書き方で直さないでください。
- 直しごとに rule_id（N-01 や P-02）を必ず書いてください。
- 表の情報に載っていない列の名前を使わないでください。
- 業務の定義（何を売上とするか、どの店舗・会員を対象にするか）を変えないでください。
- 目的に書かれていない期間の条件を足さないでください。
  期間の条件が無いことが問題なら、notes に指摘として書いてください。
- 結果が変わりうる直しには result_may_change を true にしてください。
  LEFT JOIN の相手の表の条件を ON と WHERE の間で動かす直し、
  SELECT * を列の指定に変える直し、DISTINCT を外す直し、
  集計の位置を動かす直しは、必ず true にしてください。
- 「結果は変わりません」と書かないでください。結果が同じかどうかは
  別の手順で確かめます。
- before には元のSQLの該当部分を一字一句そのまま写してください。
- 理由（reason）は、依頼者が次に同じ書き方をしないで済むように、
  2文以内で書いてください。
- SELECT文以外の文（INSERT、UPDATE、CREATE など）を書かないでください。
- 直す箇所が無ければ、changes を空にして revised_sql に元のSQLを入れてください。

【点検の観点の一覧】{rules}
【SQL本文】{sql}
【目的と期間】{purpose}
【表の情報（列、型、パーティション、クラスタ）】{table_info}
【元のSQLのドライランの結果（処理される量）】{dry_run}

「結果は変わりませんと書かない」を明記しないと、理由のほぼすべてに付きます。 依頼者を安心させる書き方として自然に出てきます。禁じるのは、確かめていないことを確かめたように書くことです。

before を一字一句で写させるのは、後で照らすためです。 プログラムで元のSQLの中に before の文字列があるかを確かめ、見つからない直しは一覧から外して印を付けます。

出力形式を固定する

Claude API の構造化出力で、次の形のJSONを受け取ります。

{
  "revised_sql": "",
  "changes": [
    {
      "rule_id": "P-02",
      "category": "naming | performance",
      "before": "",
      "after": "",
      "reason": "",
      "result_may_change": true
    }
  ],
  "notes": [
    { "kind": "missing_date_filter | business_definition | other", "text": "" }
  ]
}

1つ目の理由は、rule_id で指摘を観点の一覧に結び付けられることです。 一覧に無い番号の直しは受け付けません。どの決まりに基づく直しかが、依頼者にも見えます。 理由の後ろに、観点の一覧の該当項目へのリンクを付けて返します。

2つ目は、result_may_change で突き合わせの要否を決められることです。 1つでも true があれば、スクリプトは突き合わせを動かします。

3つ目は、notes を直しと分けられることです。 期間の条件が無いことや、業務の定義に関わる疑問は、直さずに依頼者への問いとして返します。

スクリプトは、AIの出力の後に次の3つを機械で確かめ、点検の区分を決めます。

突き合わせは、元のSQLと直した版の両方に同じ1日分のパーティションの条件を足して実行し、EXCEPT DISTINCT で双方向の差の行数を数えます。実行には maximumBytesBilled を付けます。jobs.query の説明では、この上限を超えるクエリは料金がかからずに失敗するとされています。

-- 突き合わせの定型（{orig} と {revised} は1日分の条件を足したもの）
WITH a AS ({orig}), b AS ({revised})
SELECT
  (SELECT COUNT(*) FROM (SELECT * FROM a EXCEPT DISTINCT SELECT * FROM b)) AS only_in_orig,
  (SELECT COUNT(*) FROM (SELECT * FROM b EXCEPT DISTINCT SELECT * FROM a)) AS only_in_revised

列の並びが変わる直しでは、この比較が通りません。 出力の列の名前や順を変えた直しを含むときは、突き合わせの前に、元のSQLの列の並びに合わせて直した版を包み直します。

システムへ連携する

Apps Script の UrlFetchApp は、Google Workspace のアカウントで1日10万回まで呼べます。月240件の点検なら上限に届きません。 トリガーの合計の実行時間は1日6時間までで、1件の点検は数十秒で終わる想定です。

依頼者への返却は、データ担当が一覧の行から送ります。 返す文面には、直した版、直しごとの理由と観点の番号、読み込む量の変化（例：元 820GB → 直した版 36GB）、notes の問いを並べます。読み込む量の変化を数字で返すと、依頼者が直しを受け入れる理由になります。

人が確認する

1. 「要確認」と「差し戻し」を先に見る … 突き合わせで差が出たものは、どの直しが差を生んだかを見ます
2. result_may_change が true の直しを読む … 差が0行でも、1日分では出なかっただけの可能性があります。月末や祝日の条件に関わる直しは、別の日でも突き合わせます
3. notes の問いを確かめる … 期間の条件や業務の定義の疑問は、依頼者に聞く形で返します
4. 命名の直しは流し見る … 出力の列の名前を変えた直しは、後ろのスプレッドシートやレポートが列の名前で参照していないかを依頼者に確かめます
5. AIの直しを覆したら記録する … 観点の一覧の書き方を直す材料にします

2番目を省かないでください。 1日分の突き合わせで差が0行でも、月をまたぐ条件や、特定の曜日にしか無いデータでは差が出ることがあります。 突き合わせは「差が無いことの証明」ではなく、「差があることを見つける網」です。

目標は、240件をならして1件5分です。 「返却可」は直しを流し見て返し、「要確認」に時間を使います。

例外に対処する

記録を残す

• 依頼の日時、依頼者、SQL本文、目的と期間
• 渡した表の情報と、そのときの観点の一覧の版
• 元のSQLと直した版のドライランの結果（処理される量）
• Claude API の出力の全文と、照合で外した直し
• 突き合わせの条件（日付）と、双方向の差の行数
• データ担当の最終の判断、覆した直し、依頼者に返した日時

観点の一覧の版を残すのは、決まりが後から変わるためです。 命名の決まりを変えると過去の直しの意味が変わり、当時の決まりが残っていないと、なぜその直しをしたかが追えません。

依頼者ごとの指摘の多い観点も、月に1回数えます。 同じ観点で毎月直されている人には、点検で返すより、短い勉強会で伝えたほうが早く減ります。