■ 1. はじめに：データ分析特有の「テストのしづらさ」
システム開発と異なり、データ分析には「事前に定義された正解」が存在しないケースが多いです。
システム開発では、「期待される出力（仕様）」が事前に決まっており、テストコードを用いて実装がその通りに動くかを検証することができます。 一方で、データ分析では、私たちがこれから導き出そうとしている集計結果こそが正解の候補であり、手元には答え合わせをするための「正解データ」が用意されていないことがほとんどです。
しかし、正解がないからといって品質を疎かにして良いわけではありません。 集計を行うSQLにロジックミス（バグ）があれば、「誤った集計結果」が算出され、それは「誤った意思決定」に直結します。
本記事では、このようなビジネスリスクを最小化するために、「正解がない中で、いかにしてバグを減らし、データの確からしさを担保するか」という課題に向き合い、実務の中で見えてきた「頻出するバグのパターン」と、それを防ぐために開発した「データプロファイリングの仕組み」についてまとめます。
■ 2. 背景・直面していた問題
私が所属しているデータマネジメントチームでは、KPIモニタリング用のデータマート構築や、事業部からのデータ抽出依頼対応などのデータ活用業務を担当しています。データ分析基盤にはGoogle BigQueryを利用しています。
これらの業務、特に「データ抽出依頼対応」においては、主に以下のフローで進行します。
▼ データ抽出の標準的なフロー
依頼起票→要件のすり合わせ→SQL実装→レビュー →データ納品
このプロセスにおいて、私たちはSQLを用いてデータを抽出・加工し、依頼者に提供します。 しかし、分析用途のSQLには前述の通り「事前に正解を定義しにくい」という特性があるため、特に「実装」から「レビュー」のフェーズにおいて、以下の問題が発生していました。
直面していた問題点：品質のばらつきとレビュー負荷の肥大化
以前は、組織として明確なテスト観点が定まっていなかったため、品質担保が開発者やレビュワーの経験則や注意力に依存してしまい、担当者によってテスト精度に大きなばらつきが生じていました。
結果として、テスト段階で検知すべき初歩的なバグを見逃してしまうことがあり、特にSQLの特性上、エラーが出ずに実行できてしまうFan-out（レコード増幅）やNULL計算による欠損などの「サイレントなバグ」が、テスト不足のまま依頼者に提出されやすい状態でした。
また、レビュワーの負担が大きいことも問題でした。提出されたクエリにデータの不整合やバグが含まれていないかを確認するために、都度手元でクエリを実行して検証する必要があったため、レビュー工数が肥大化していました。
目指した姿：品質保証の標準化
そこで私たちは、品質担保を「属人的な気づき」から「標準化されたプロセス」へシフトすることを目指しました。 dbtのテスト機能や過去の不具合事例、バグパターンを調査し、「レビューを依頼する時点で、最低限のデータの整合性が数値で証明されている状態」を目標としました。
もちろん、個別の要件によって検証すべきロジックやテスト観点は異なります。 しかし、どのような要件であっても、データの不整合や典型的な実装ミスなど、共通して確認すべき「汎用的なチェック観点」は存在します。
本記事では、この「共通して発生しやすい」課題に焦点を当て、データの状態を可視化することで防ぐことができる「代表的な3つのバグパターン」を例として紹介します。
■ 3. 頻出するバグパターン
SQLにおいて発生しやすい論理バグは多岐にわたりますが、ここでは特に頻発しがちな3つのパターンを取り上げます。
- データ仕様の「想定」と「実態」の乖離（NULL/Unique）
- テーブル結合による「意図しないレコードの増幅」(Fan-out)
- 複雑なロジックの「ブラックボックス化」
以下、それぞれの詳細と要因について解説します。
パターン1：データ仕様の「想定」と「実態」の乖離（NULL/Unique）
事象：エラーは出ないが、値が消滅する
SQL実装時に想定した「Not NULL」や「Unique」という前提が、実際のデータと食い違っていることで発生するバグです。
例えば、BigQuery 等で GREATEST 関数（最大値の取得）を使うケースを考えてみます。
SELECT
user_id,
GREATEST(plan_A_price, plan_B_price) AS max_price
FROM
subscriptions
この時、もし plan_A_price に想定外の NULL が入っていた場合、多くのDBにおいて結果は plan_B_price ではなく NULL になります。 クエリ自体は正常に実行されるためエラーは出ないものの、集計結果が欠損し、売上が実際より低く算出されるといった集計ミスに繋がります。
要因
このようなバグの主な要因は、「データ仕様」と「実データ」の状態に乖離があることです。たとえば、「会員IDなのだから、重複はないはず」「必須入力項目だから、NULLはないはず」と思っていても、実際にはそのようなデータになっていないことがあります。
その背景には以下のような要因が考えられます。
- データ取得元のシステムに未知の仕様が存在しているなど、単純に開発者の認識が誤っている
- サービスの進化や変更に伴い、データ仕様そのものが当初から変更されている
- 過去の不具合やマイグレーションミス等により、データ仕様と実データの間に不整合が生じている
このように要因は様々ですが、いずれも開発者が前提としていた「データ仕様」と目の前にある「実データの状態」が乖離していることが根本的な原因です。
パターン2：テーブル結合による「意図しないレコードの増幅」(Fan-out)
事象：集計値が「意図せず増幅」する
2つのテーブルを結合（JOIN）した際、意図せずに行数が増えてしまうバグです。例えば、「ユーザー1人につき1行（1:1）」の関係だと思って結合したのに、実際には結合キーに重複があり、行数が増幅したまま集計してしまうケースが典型的です。 これにより、「売上合計（SUM）が、実際の数値の数倍になる」といった大幅な集計ミスが発生します。
要因
このバグの原因は、結合キーのユニーク制約が崩れていることを認識できていない点にあります。
結合するキーの値が、左側のテーブルに $M$ 個、右側のテーブルに $N$ 個存在する場合、結合後の行数は $M \times N$ 個になります。
▼ 具体例：PCログ（2行）x 社員の兼務（2行重複）の場合
社員マスタにおいて、「一人の社員が複数の部署を兼務している」ことを見落として、単純に結合してしまったケースを考えます。
①結合前のテーブル状態
-
左側：PCアクセスログ (
pc_logs)- 社員
E001のログが2件存在している
- 社員
-
右側：社員マスタ (
employees)- 社員
E001は「営業部」と「企画部」を兼務しているため、2件登録されている
- 社員
PCアクセスログ (pc_logs)
| log_id | emp_id | access_ts |
|---|---|---|
| L01 | E001 | 2023-01-01 09:00:00 |
| L02 | E001 | 2023-01-01 10:00:00 |
社員マスタ (employees)
| emp_id | dept_name | emp_name |
|---|---|---|
| E001 | 営業部 | Tanaka |
| E001 | 企画部 | Tanaka |
②実行クエリ
SELECT l.log_id, e.emp_name, e.dept_name
FROM pc_logs AS l
LEFT JOIN employees AS e
ON l.emp_id = e.emp_id -- 部署を絞らずに結合してしまった
③結合結果（2行 x 2行 = 4行に増幅）
| log_id | emp_name | dept_name |
|---|---|---|
| L01 | Tanaka | 営業部 |
| L01 | Tanaka | 企画部 |
| L02 | Tanaka | 営業部 |
| L02 | Tanaka | 企画部 |
このように、「社員マスタだから1人1行のはず」という思い込みにより、兼務や所属変更履歴などの「1:N」の構造を見落として結合すると、ログ件数が倍増し、数値が大きく狂ってしまいます。
なお、「LEFT JOIN なら左側の行数が維持されるはず」と誤認されがちですが、図のように結合相手（右側）のキーに重複があれば、LEFT JOIN や INNER JOIN であっても行数は増えます。
パターン3：複雑なロジックの「ブラックボックス化」
事象：複雑な処理での実装ミス
RANK() などのWindow関数や複雑な条件分岐を使用する場合、パーティション粒度の誤りや条件漏れといった論理的な実装ミスが混入しやすくなります。 「ロジックは合っているつもりなのに、特定のケースで計算が狂う」という状態です。
要因
なぜこうしたミスが見過ごされるかというと、SQLは処理ステップごとのデータの変遷（中間データ）が可視化されず、ブラックボックス化しやすいからです。 通常SQLでは手続き型言語のように変数の推移をステップ実行で確認できないため、脳内でのデータ遷移のシミュレーションが極めて困難になります。結果として、開発時に実装ミスが見過ごされやすく、バグが混入しやすくなります。
■ 4. 解決策①：データプロファイリングツールの開発
上記のようなバグのパターンが頻出するということが洗い出せたので、これらを防ぐことを目的として、Google Colab上で動作するデータプロファイリングツールを作成しました。Python経由でBigQueryに接続してSQLを実行し、クイックに検証を行える仕組みとなっており、大きく2つの機能で品質を担保しています。
機能A：全CTEの統計情報・行数推移の算出
- 対応するバグ：パターン1（NULL/Unique）＆ パターン2（Fan-out）
SQLをCTE単位に分解し、それぞれの統計情報（Null数、ユニーク数など）と行数を一括で算出・表示します。
# --- 1. セットアップ（ライブラリのインポートと認証） ---
from google.cloud import bigquery
from google.colab import auth
import pandas as pd
import regex
from IPython.display import display, Markdown
# BigQueryクライアントの初期化
auth.authenticate_user()
PROJECT_ID = 'your-project-id' # ※ご自身のプロジェクトIDに書き換えてください
client = bigquery.Client(project=PROJECT_ID)
# --- 2. SQLをCTE単位で分解する ---
def parse_query(query: str) -> list[dict]:
"""
SQLクエリをCTE単位で分解し、各ステップで実行可能なクエリを生成する。
"""
# 前処理（コメント削除、整形）
query = regex.sub(r'--.*\n|#.*\n|/\*([^*]|\*[^/])*\*/', '', query)
query = query.strip().rstrip(';')
# CTEの抽出（with ... as (...)のパターンを取得）
cte_pattern = r'(?:with|,)\s*(\w+)\s+as\s*(?<rec>\((?:[^\(\)]+|(?&rec))*\))'
ctes = regex.finditer(cte_pattern, query, regex.IGNORECASE)
cte_set = [{'name': cte.group(1), 'definition': cte.group(2)} for cte in ctes]
# メインクエリの抽出
if cte_set:
last_cte_end = list(regex.finditer(cte_pattern, query, regex.IGNORECASE))[-1].end()
main_query = query[last_cte_end:].strip()
else:
main_query = query.strip()
cte_set.append({'name': 'main_output', 'definition': f'({main_query})'})
# 実行用クエリの組み立て
cte_definitions = []
for i, step in enumerate(cte_set):
prefix = "WITH" if i == 0 else ","
cte_definitions.append(f"{prefix} {step['name']} AS {step['definition']}")
step['executable_query'] = "\n".join(cte_definitions) + f"\nSELECT * FROM {step['name']}"
return cte_set
# --- 3. 統計情報の算出・表示関数 ---
def print_query_stats_all(query: str) -> None:
"""
クエリ内の全CTEについて、統計量と行数を一括表示する
"""
cte_set = parse_query(query) # 上記の関数を使用
for item in cte_set:
display(Markdown(f"### {item['name']}"))
# クエリ実行
try:
df = client.query(item['executable_query']).to_dataframe()
record_num = len(df)
print(f"レコード数: {record_num}")
if record_num > 0:
# 基本統計量の算出
stats = df.describe(include='all').T.drop(columns=['25%', '50%', '75%', 'std'], errors='ignore')
# カラムの名前・データ型を加工
stats['count'] = stats['count'].astype(int)
rename_map = {
'count': '非nullレコード数',
'unique': '非nullユニークレコード数',
'top': '最頻値', 'freq': '最頻値の出現回数',
'mean': '平均値', 'min': '最小値', 'max': '最大値'
}
stats = stats.rename(columns={k: v for k, v in rename_map.items() if k in stats.columns})
# is_not_null, uniqueの情報を追加
s_non_null = pd.to_numeric(stats['非nullレコード数'], errors='coerce')
stats['is_not_null'] = (s_non_null == record_num)
if '非nullユニークレコード数' in stats.columns:
s_unique = pd.to_numeric(stats['非nullユニークレコード数'], errors='coerce')
stats['unique'] = (s_non_null == s_unique)
# 表示
display(stats.astype(object).fillna('-'))
else:
print("該当レコードなし")
except Exception as e:
print(f"Error executing {item['name']}: {e}")
query = """
WITH users AS (
SELECT user_id, email, age, status, last_login
FROM `raw_data.users`
),
access_logs AS (
SELECT user_id, log_id, access_ts
FROM `raw_data.access_logs`
)
SELECT l.log_id, l.user_id, l.access_ts, u.email
FROM access_logs AS l
LEFT JOIN users AS u
ON l.user_id = u.user_id
"""
print_query_stats_all(query)
▼ 出力イメージ
users
レコード数：10,000
| column | 非nullレコード数 | 非nullユニークレコード数 | 平均値 | 最小値 | 最大値 | is_not_null (欠損がないか) |
unique (値がユニークか) |
|---|---|---|---|---|---|---|---|
| user_id | 10,000 | 10,000 | - | - | - | true | true |
| 10,000 | 9,200 | - | - | - | true | false | |
| age | 9,500 | 80 | 34.5 | 18 | 90 | false | false |
| status | 10,000 | 3 | - | - | - | true | false |
| last_login | 9,800 | 8,500 | - | 2023-01-01 | 2023-12-31 | false | false |
access_logs
レコード数：50,000
| column | 非nullレコード数 | 非nullユニークレコード数 | 平均値 | 最小値 | 最大値 | is_not_null (欠損がないか) |
unique (値がユニークか) |
|---|---|---|---|---|---|---|---|
| log_id | 50,000 | 50,000 | - | - | - | true | true |
| user_id | 25,000 | 8,000 | - | - | - | false | false |
| access_ts | 50,000 | 48,000 | - | 2023-01-01 00:00:05 | 2023-12-31 23:59:55 | true | false |
main_output
レコード数：50,000
| column | 非nullレコード数 | 非nullユニークレコード数 | 平均値 | 最小値 | 最大値 | is_not_null (欠損がないか) |
unique (値がユニークか) |
|---|---|---|---|---|---|---|---|
| log_id | 50,000 | 50,000 | - | - | - | true | true |
| user_id | 25,000 | 8,000 | - | - | - | false | false |
| access_ts | 50,000 | 48,000 | - | 2023-01-01 00:00:05 | 2023-12-31 23:59:55 | true | false |
| 25,000 | 7,600 | - | - | - | false | false |
この出力により、以下の異常を検知することができます。
-
データ仕様の乖離: CTEに含まれるカラムの
is_not_nullやuniqueを見ることで、欠損や重複（パターン1）を発見できる。 - Fan-outの検知: CTEごとのレコード数の推移を見ることで、意図しない結合による行数増幅（パターン2）を発見できる。
機能B：全CTEの実テーブル一括作成
- 対応するバグ：パターン3（複雑ロジック）
全CTEをBigQuery上のテーブルとして一括保存します。
def create_or_replace_table_all(query: str, project_id: str, dataset_id: str, prefix: str = 'debug_cte') -> None:
"""
クエリ内の全CTEをBigQueryテーブルとして保存する
"""
cte_set = parse_query(query)
for item in cte_set:
# テーブル名にプレフィックスを付与
table_name = f"{prefix}_{item['name']}"
table_id = f"{project_id}.{dataset_id}.{table_name}"
# CREATE OR REPLACE TABLE 文の生成
ddl = f"""
CREATE OR REPLACE TABLE `{table_id}` AS
{item['executable_query']}
"""
# 実行
try:
client.query(ddl).result()
print(f"Created table: {table_id}")
except Exception as e:
print(f"Error creating {table_id}: {e}")
query = """
-- ここに検証対象のクエリを記述
WITH ...
"""
create_or_replace_table_all(query, PROJECT_ID, 'your_dataset_id')
上記のメソッドを実行すると、BigQuery上に debug_cte_users, debug_cte_access_logs といったテーブルが一括生成されます。これにより、複雑なロジックの中間状態（ブラックボックス）が可視化され、特定レコードの追跡やエッジケースの検証が容易になります。
■ 5. 解決策②：チーム開発プロセスの標準化
このツールを組織全体で有効活用し、品質を組織的に担保するために、以下のステップで開発プロセスを標準化しました。
ステップ1：テスト観点の分類と定義
はじめに、テスト項目の選定が個人の裁量に委ねられている状態（属人化）を解消するため、開発時にチェックすべき観点を「汎用性」と「重要度」に基づいて以下の2つに分類・定義しました。
- 汎用的な観点（MUST）： NULLチェック、ユニーク判定、行数推移など、要件に関わらず全てのクエリで守るべき最低限の品質基準です。これらは全案件で確認を必須（MUST）としました。
- ロジック固有の観点（WANT）： ビジネスロジックに依存する複雑な検証です。案件ごとに検証すべき内容が異なるため、必要に応じて追加する推奨（WANT）項目としました。
ステップ2：実行ハードルの低減
次に、分類した観点を開発者が負担なく実施できるよう、手段を整備しました。
- 「汎用的な観点（MUST）」へのアプローチ: 前述したツールをGoogle Colab上で共有し、環境構築せずに即座に実行できる状態を作りました。ツールにクエリを貼り付けるだけで前述した機能を実行できるようにすることで、スムーズに確認作業へ移れるようにしています。
- 「ロジック固有の観点（WANT）」へのアプローチ: 適用ケースやテストクエリのテンプレートを整備・ドキュメント化しました。一からテストクエリを書くのではなくテンプレートを調整するだけで済むようにし、複雑なロジック検証における工数削減と属人化の排除を図っています。
ステップ3：運用のルール化
環境が整った段階で、Pull Request（PR）のレビュー依頼要件として、「ツールの実行結果（統計情報）の添付」をルール化しました。
あわせて、統計情報の解釈における属人化対策も行いました。「数値を見て違和感に気づけるか」は個人のスキルに依存するため、「各指標の意味」や「異常検知の着眼点」をまとめたガイドラインを整備し、セルフチェック精度の標準化を図りました。
これにより、開発者は事前にセルフチェックを行い、レビュワーはまず統計情報でデータの整合性を確認してからロジックを見るというレビュー体制を構築することで、プロセスの効率化を推進しています。
■ 6. 導入効果
ツール導入とプロセスの標準化により、以下のような成果が得られています。
- 品質保証のベースライン向上: 「NULL」「Unique」「Fan-out」といった必須観点が整備されたことで、個人の経験に依存せずデータの不整合を検知できるようになり、組織全体の品質基準が向上しました。
- 手戻りの削減: 開発者がPRを出す前の「セルフチェック」の段階で異常に気づけるようになり、レビュー段階での初歩的な指摘や、リリース後の手戻りが減少しました。
- レビュー負荷の低減: データの整合性が数値で証明された状態でロジック確認に入れるため、レビュワーの検証工数と心理的負担が大幅に軽減されました。
■ 7. おわりに
実務において、「たぶん大丈夫だろう」という直感や想定は当てになりません。「ロジックはシンプルだから」「マスタデータだから、ユニークなはず」と思いながら、実際に検証してみると、未知のデータ仕様や不整合・ロジックの考慮漏れが見つかる、というケースが多々ありました。ほとんど自明に思えることでも、それを疑い、確認することの重要性をあらためて感じました。
正解がないデータ分析だからこそ、品質を担保するのは「実データ（ファクト）」のみだと考えます。思い込みを排除し、データそのものを客観的に検証することが品質の向上に繋がります。本記事で紹介したアプローチが、「答え合わせ」のできないSQL開発において、信頼性を高めるための一助となれば幸いです。
■ 8. 関連記事
本記事で紹介したプロファイリング手法を用いることでバグを発見した事例について、以下の記事でも紹介されています。ぜひ併せてご覧ください。