この記事は何？
データパイプラインの構築や移行をしていると、「構成したパイプラインがつくったデータ」と「正解データ」の間の数値が一致しているのか検証したくなることがあります。数千万行を超える単位（つまり、Excelでは扱えない規模）のテーブルデータの突合となると、意外に慣れやテクニックが必要で、初めてやる人にとっては躓きやすい作業のひとつです。
この記事では、そういった数値検証のためのテクニックをまとめたものです。
大まかな数値検証のステップ
- 作ったデータのデータプロファイルを見て、明らかにおかしい問題を事前にさがします。
- 作ったデータと正解データを同じ環境で扱えるようにします。
- データが一致するか、テーブルハッシュを用いてチェックします。
- カテゴリカルな列があれば、カテゴリごとの集計を用いて、大まかに切り分けをします。
- 明細レベルの比較では、minusとunionを活用します。
具体例と解説
購入履歴をまとめたレポートを作ることを考えます。レポートに必要な情報を集めるためのSQLを書き終えたので、その集計が正しいのかを確かめたいです。データオーナーからは、過去の実績報告で作られた別のレポートを受領しているので、そのレポートと数値突合をして、集計の妥当性を担保しようとしています。
▼ 履歴テーブル
| ID | 購入日時 | ユーザーID | 商品ID | 購入品数 | 購入時合計金額 |
|---|---|---|---|---|---|
| 1 | 2025-09-10 13:10:12 | aaa | xxx | 1 | ¥5,000 |
| 2 | 2025-09-10 09:30:34 | bbb | yyy | 5 | ¥400 |
| 3 | 2025-09-10 20:40:56 | ccc | zzz | 7 | ¥10,000 |
▼ 商品マスタ
| ID | 大カテゴリー | 小カテゴリー | 商品名 |
|---|---|---|---|
| aaa | 食品 | 穀類 | 米 |
| bbb | 日用品 | キッチン用具 | 洗剤 |
| ccc | 衣類 | トップス | Tシャツ |
▼ 出力したいレポート/正解データ
| 日付 | 大カテゴリー | 小カテゴリー | 総購入点数 | 総購入金額 | 総利用者数 |
|---|---|---|---|---|---|
| 2025-09-20 | 食品 | 穀類 | 120 | 36,000 | 45 |
| 2025-09-20 | 食品 | 飲料 | 85 | 12,750 | 37 |
| 2025-09-20 | 食品 | 菓子 | 150 | 22,500 | 52 |
| 2025-09-21 | 衣類 | トップス | 60 | 90,000 | 40 |
| 2025-09-21 | 衣類 | ボトムス | 45 | 67,500 | 28 |
| 2025-09-21 | 家電 | キッチン | 15 | 120,000 | 12 |
| 2025-09-21 | 家電 | AV機器 | 10 | 150,000 | 8 |
| 2025-09-22 | 日用品 | 筆記具 | 200 | 30,000 | 90 |
| 2025-09-22 | 日用品 | 掃除用品 | 130 | 19,500 | 55 |
| 2025-09-22 | 日用品 | 紙製品 | 300 | 45,000 | 120 |
| 2025-09-22 | 日用品 | バス用品 | 100 | 25,000 | 75 |
1. 作ったデータのデータプロファイルを見て、明らかにおかしい問題を事前にさがします
データマートをつくったら、必ずデータプロファイルを作りましょう。
データプロファイルの例
| カラム | データ型 | Null数 | Null率 | ユニーク数 | ユニーク率 | 最大値 | 最小値 |
|---|---|---|---|---|---|---|---|
| 日付 | DATE | 0 | 0% | 50 | 20% | 2025-09-01 | 2025-09-30 |
| 大カテゴリ | VARCHAR | 0 | 0% | 80 | 5% | ー | ー |
| 小カテゴリ | VARCHAR | 30 | 2% | 200 | 50% | ー | ー |
| 総購入点数 | NUMBER | 0 | 0% | 5,000 | 89% | 300 | -20 |
| 総購入金額 | NUMBER | 0 | 0% | 6,200 | 97% | 999,999,999,999 | 25,000 |
| 総利用者数 | NUMBER | 0 | 0% | 1 | 0% | 0 | 0 |
統計量を観察するだけでも、わかることがたくさんあります。
- 小カテゴリにNullが含まれているようです。商品マスタ自体の欠損や、購入履歴との結合に問題があるのかもしれません。
- 総購入点数がマイナスのレコードがあるようです。ビジネス上購入点数がマイナスになることはあり得るのでしょうか？
- 総購入金額の最大値があまりにも大きいです。
- 総利用者数がすべて0になっているようです。
また、時系列に沿った件数のカウントなども有効です。
▼ 時系列に沿ったプロファイルの例
| 日付 | 件数 |
|---|---|
| 2025-09-01 | 7,000 |
| 2025-09-02 | 7,200 |
| 2025-09-03 | 6,900 |
| 2025-09-05 | 7,100 |
| 2025-09-06 | 14,000 |
- 9/4断面が存在しないようです。
- 9/6はデータの二重取り込みが起きていないでしょうか？
2. 作ったデータと正解データを同じ環境で扱えるようにします
大規模なデータとなると、一つの環境で合わせてデータが見られないと、検証は難しいです。データパイプラインをレガシーなものから新しい環境へ移行する際などでは、新旧両方のデータが同じ場所で比較してみられるように設計しておくことが重要です。
Snowflakeであれば、旧環境のデータをS3などのストレージに配置してもらい、外部ステージからクエリできるようにしておくといった方法が考えられます。
3. データが一致するか、テーブルハッシュを用いてチェックします
正解データと比較したいデータが完全に一致することが期待値なのであれば、１レコード単位の比較ではなく、テーブルハッシュ（チェックサム）をつくって比較すると楽です。
select
hash_agg(* exclude updated_at)
from
正解データ
select
hash_agg(* exclude updated_at)
from
比較したいデータ
4. カテゴリカルな列があれば、カテゴリごとの集計を用いて、大まかに切り分けをします
ここまでの手順を実施して、もし正解との間に差分があったならば、次はその原因を特定したいです。
大規模なテーブルの比較となると、そのレコード数に圧倒されそうになりますが、扱えるサイズになるまで分解して一つひとつ差分原因を消し込むのが、結局のところ一番の近道になります。
もし比較対象のテーブルにカテゴリカルな列があるならば、それを利用して、差分の発生箇所を絞り込みましょう。
with seikai as (
select
"大カテゴリー",
count(*) as "件数",
sum("総購入金額") as "総購入金額"
from
正解データ
group by 1
),
taisyou as (
select
"大カテゴリー",
count(*) as "件数",
sum("総購入金額") as "総購入金額"
from
比較対象データ
group by 1
),
sabun as (
select '正解' as "データソース", * from seikai
union all
select '比較対象' as "データソース", * from taisyou
)
select * from sabun
order by 1,2
| データソース | 大カテゴリー | 件数 | 総購入金額 |
|---|---|---|---|
| 正解 | 衣類 | 5,000 | 5,800,000 |
| 比較対象 | 衣類 | 5,000 | 5,800,000 |
| 正解 | 家電 | 300 | 780,000 |
| 比較対象 | 家電 | 250 | 690,000 |
特定のカテゴリーだけで差分が生じているのであれば、そのカテゴリーだけに絞って比較を進めていけば良く、効率的に対象を狭めていくことができます。
もし、全体的に差分が生じていても、安心してください。数千万件あるレコードがすべて別々の原因でずれることなどありません。大抵の場合、それらは同じ理由でズレが生じています。なので、適当なパターンのレコードをサンプリングすれば、原因を特定できるでしょう。
5. 明細レベルの比較では、minusとunionを活用します
実際にずれているレコードを特定したい場合には、minusを使いましょう。
with seikai as (
select * from 正解データ
),
taisyou as (
select * from 比較対象データ
),
select * from seikai
minus
select * from taisyou
これにより、「正解データにはあるが、比較対象にはないデータ」が検出できます。重要なのは、「比較対象にはあって、正解データにはない」レコードは抽出できないという点です。minusを使うときは、上下の位置関係に注意し、両方の組み合わせを試しましょう。
どのレコードがずれているのかが特定できたら、二つのレコードを実際に横並びで見比べてみましょう。unionを利用できます。
with seikai as (
select * from 正解データ
),
taisyou as (
select * from 比較対象データ
),
diff as (
select * from seikai
minus
select * from taisyou
),
union_tables as (
select
'正解' as "データソース",
*
from seikai
inner join diff
on seikai.primary_key = diff.primary_key
union all
select
'比較対象' as "データソース",
*
from taisyou
inner join diff
on taisyou.primary_key = diff.primary_key
)
select * from union_tables
order by primary_key
| データソース | 日付 | 大カテゴリー | 小カテゴリー | 総購入点数 | 総購入金額 | 総利用者数 |
|---|---|---|---|---|---|---|
| 正解 | 2025-09-20 | 食品 | 穀類 | 120 | 36,000 | 45 |
| 比較対象 | 2025-09-20 | 食品 | 穀類 | 110 | 32,000 | 40 |
ここまで来れば、あとはロジックを見ながら、なぜこのレコードがズレたのかを調べることはできるはずです。
SnowsightなどのUIで見比べていると、空白文字とスペースの差や、データ型の微妙な違いを見落としやすいです。適宜差分を絞り込んでからCSVやExcelへ落としてみるとよいです。
調査記録について
データ調査の記録は、後になって第三者が読み返せるように記述・保管しておくことを強くオススメします。
データはGit管理されていません。1週間後の自分やチームの他のメンバーが、同じマートの調査をするときに「過去にどんな形で調査したのか」を参照できるようにするには、能動的にドキュメント化しようという強い意思が大事でしょう。
▼ 残すべき情報の例：
- どのデータとどのデータを突合したか（バージョンや取得日時含む）
- 発見した差分とその原因
- 許容した差分とその判断理由
- 使用したSQLクエリ（パラメータ含む）
SnowflakeではNotebookやWorkspaceをGitと統合することができ、こういった作業記録をGitに残すのも本当に楽に実現できるようになりましたね！
おまけ：役にたつリソース
dbtのUnit testを使えば、正解データとの差分を簡単に取り出すことが可能です。正解データはymlの定義ファイルから与えるだけでなく、正解データ取得のためのSQL指定で与えることも可能なので、このような数値検証にも活用できます。
dbtにはデータプロファイルの取得のためのパッケージもいくつか公開されているので、これをCIに組み込むことで、自動的にプロファイルを取ることも可能です。CIへの組み込みはグノシーさんがとてもわかりやすい記事を挙げられています。
Discussion