HOME  /  SABER  /  2026.11.14

BEYOND THE VIZ|汚い公的データを、AIに設計からDataform実行まで任せてみた #3

READ  17 MIN CHARS  9,639 UPDATED  2026.11.19

INTRO

サンプル – スーパーストアは、きれいすぎる。

Tableauを学ぶ題材としては最高です。1行が1つの注文明細で、列名もそろっていて、欠損もない。でも、仕事で渡されるデータはそうではありません。Excelを開くと見出しが2段になっていて、セルが結合され、欄外に「注:」が並び、数値の欄に「―」や「…」が入っている。

IPAのデータリテラシーのガイドブックにも、分析に使うデータの多くは整っていないこと、分析の前の準備で力尽きてしまうことが書かれています。現場で心折られるのは、Vizを作る前工程ではないでしょうか?

この準備を、「丸ごとAIに任せられないか」。

BigQueryには、パイプラインを作るためのAI、Data Engineering Agent があります。自然言語で頼むと、SQLを書いてDataformの中に整理してくれます。ただし、実行まではさせません。つまり役割分担はこうなります。

工程担当
設計(カラムの意味、粒度、ファクト/ディメンション、品質チェック)AI
実装(SQL、Dataform定義、テスト、カラム説明)AI
レビュー人間
実行人間(ボタンを押すだけ)

今回はこの流れを、汚い公的データ1枚で最後まで通します。自分でSQLは1行も書きません。

利用データは、e-Statで公開されている総務省統計局「人口推計」の都道府県別の表です。

全体像:raw → DWH → マートのどこをAIに任せるか

データ基盤は、raw層 → DWH層 → マート層 の3層で整理するのが定番です。今回の作業をこの3層に当てはめると、こうなります。BigQueryのデータセットも、層ごとに raw、dwh、mart と分けます。

層役割今回のテーブル従来は誰が今回は誰が
取得e-StatからExcelをダウンロード-人間人間
raw層元データをそのまま保存する。変えるのはファイル形式だけraw.population_pref_2024データエンジニアAIがCSV化、人間がアップロード
DWH層汚れを取り除き、ファクトとディメンションに整理する。用途を問わず使い回せる正しいデータdwh.stg_population(途中段階)、dwh.dim_prefecture、dwh.fct_populationデータエンジニア、または分析者がExcelやTableau Prepで手作業AIが設計・実装、人間がレビュー
マート層特定の用途向けに、ファクトとディメンションを結合したワイドテーブルを作るmart.mart_population_prefデータエンジニア、または分析者がTableauのリレーションやPrepで結合AIが設計・実装、人間がレビュー
可視化ダッシュボードを作る-分析者(Tableau)分析者
(Tableau)

Tableau(BIツール)で取り組むのは、いちばん下の「可視化」です。今回AIに任せるのは、その手前のデータエンジニアリング(raw層、DWH層、マート層)の部分です。Tableauでデータソースを開いた瞬間に「すぐ使えるデータ」があるかどうかは、データエンジニアリングで決まります。

今回のブログにおける、それぞれの層のルールです。

  • raw層は、直さない。 AIには形式の変換だけを頼み、中身には触らせません。直すのはDWH層で、Dataformのコードとして。そうすると、何をどう直したかが全部コードに残ります。
  • DWH層は、用途を決めない。 Tableauで使うかどうかに関係なく、正しくて使い回せる形(ファクトとディメンション)にします。
  • マート層は、使う人に合わせる。 今回はTableauで都道府県別・男女別に見る用途でワイドテーブルを作成します。

ちなみに、同じ考え方を「ブロンズ/シルバー/ゴールド」と呼ぶこともあります。ただ、どこにDWHやファクトを置くかは流儀が分かれる印象。僕の場合は、「raw/DWH/マート」で整理しています。

DATA:今回使ったデータ

項目内容
統計名人口推計(総務省統計局)
統計表第4表 都道府県,男女別人口及び人口性比-総人口,日本人人口(2024年10月1日現在)
形式Excel(閲覧用)
公開日2025年4月14日
入手先政府統計の総合窓口(e-Stat)
利用条件e-Stat利用規約(政府標準利用規約 第2.0版に準拠。商用利用も可)
利用日2026年11月12日

利用データは、e-Statで公開されている総務省統計局「人口推計」の都道府県別の表です。同じデータで試したい人は、入手先のページで「EXCEL 閲覧用」を押してください。「EXCEL」と「EXCEL 閲覧用」の2つがありますが、今回使うのは人間向けに整形された 閲覧用です。人間には読みやすく、機械には読みにくい。いわゆる「ネ申Exel(紙Excel)」です。

今回の環境

項目内容
データ基盤Google Cloud, BigQuery
パイプラインDataform(BigQueryの中で使える、SQLベースの変換ツール)
AIBigQuery Data Engineering Agent
費用エージェントとDataform自体には利用料がかからず、かかるのはBigQueryの計算コストだけ。今回のデータ量なら無料枠の範囲内。

STEP 1:raw層を作る(AIに一言でCSVにしてもらう)

Excelを開くと、こうなっています。

汚れはこんなところにあります。(まだあるかも)

  • 1〜9行目:列番号だけの行、日本語と英語の表題、「(単位 千人)」、「総人口/日本人人口」と「男女計・男・女・人口性比」の2段の見出し、さらに日英2行ずつ
  • A〜D列:表番号「00400」や、空白だけのセルなど、画面では気づきにくい列
  • 都道府県名の末尾に全角スペース(「岡山県 」「沖縄県    」)←!?
  • 人口性比の見出しの下に「*」、表の下に「注) * 女性100人に対する男性の数」
  • 人口の単位は千人。ただし人口性比だけは単位が違う

これだけ汚れていると、そもそもBigQueryに読み込めませんね。そこで前処理を挟みます。ただし、やるのは形式の変換だけ です。あくまで、BigQuery上のraw層に読み込める状態にする前処理です。

ExcelをAI(Claude)に渡して、こう頼みます。

添付のExcel(1シート)を、BigQueryにそのまま取り込めるCSVに変換してください。
条件:
- 中身は一切直さない。行も列もExcelの見た目どおり、1セル=1値で出す
- 表題、見出し、注記、空行も消さずに残す
- 結合セルは左上のセルにだけ値を入れ、残りは空にする
- 全角スペースや前後の空白も、そのまま残す
- 1行目に列名として c01, c02, … を付ける
- 文字コードはUTF-8
- 最後に、行数と列数を教えてください

ポイントは「直さない」を、しつこいくらい書くことです。AIは親切なので、何も言わないと表題を消したり空白を取ったりしてくれます。ここでやられると、何を直したかがどこにも残らない、「raw/DWH/マート」で整理に従わない状態になります。

今回の表は、70行×21列のCSVになりました。

あとはBigQueryに入れるだけです。型は推測させず、全部の列を文字列(STRING)にします。raw層に汚れごとそのまま保存しておくためです。

  1. BigQueryでデータセット「raw」を作る
  2. 「テーブルを作成」→ ソースは「アップロード」、ファイル形式は「CSV」、テーブル名は population_pref_2024
  3. スキーマは「テキストとして編集」にして、次の1行を貼る
c01:STRING,c02:STRING,c03:STRING,c04:STRING,c05:STRING,c06:STRING,c07:STRING,c08:STRING,c09:STRING,c10:STRING,c11:STRING,c12:STRING,c13:STRING,c14:STRING,c15:STRING,c16:STRING,c17:STRING,c18:STRING,c19:STRING,c20:STRING,c21:STRING
  1. 詳細オプションの「スキップするヘッダー行」を 1 にする(c01、c02…の行を飛ばす)
  2. 「テーブルを作成」

これで、汚れがそのままのraw層ができました。ここから先のDWH層とマート層は、全部AIの仕事にします。

STEP 2:AIに設計させる

Dataformのリポジトリを作り、エージェントを開きます。

いきなりコードを書かせず、まず設計書だけを出させます。

これから、BigQuery上のraw層のテーブルから、Dataformで DWH層 と マート層 を作りたいです。
まずはコードを書かず、設計書だけを日本語で作ってください。
この段階ではファイルを作成・変更せず、設計書を回答として返すだけにしてください。

# 今回のデータ
- rawテーブル:raw.population_pref_2024
- 元データ:e-Stat「人口推計 第4表 都道府県,男女別人口及び人口性比-総人口,日本人人口(2024年10月1日現在)」
- 取り込み方:閲覧用Excelを加工せず、70行×21列(c01〜c21、全列STRING)で取り込んだ
- 利用目的:Tableauで都道府県別・男女別の人口を見たい。地図や棒グラフで可視化する予定

# 層のルール
- raw(データセット raw):触らない。直すのはすべて下の層で、何をどう直したかをコードに残す
- DWH(データセット dwh):用途を決めない。汚れを取り除き、ファクトとディメンションに整理する。途中段階は stg_、ディメンションは dim_、ファクトは fct_、照合用の参照値は ref_ を付ける
- マート(データセット mart):Tableauで、そのままドラッグ&ドロップして使える形にする。テーブル名は mart_ を付ける

# Tableauで使える形にするためのルール
1. 縦持ちにする:分類(地域、性別、区分、年月など)は「列名」ではなく「行の値」として持つ。分類を列名に埋め込まない(例:「売上_東日本」は不可)
2. 1列1意味:1つの列に、意味の違う値や単位の違う値を混ぜない
3. 型でディメンションとメジャーを分ける
   - ディメンションにする列はSTRING。コード類もゼロ埋めした文字列にする
   - メジャーにする列は数値型(件数・人数・金額はINT64、比率・小数はNUMERIC)
   - 日付はDATE。数値を文字列のまま残さない
4. 粒度ごとにテーブルを分ける:足してよい指標(件数、人数、金額)と、足してはいけない指標(比率、平均、公表された合計値)は、同じテーブルに混ぜない。必要なら粒度の違うマートを複数作る
5. 合計行(全国、総数、計、小計など)は、ファクトとマートに入れない。照合用に ref_ テーブルへ分ける
6. 分類の値は互いに排他的にする:同じ列の値どうしに包含関係(例:「売上合計」⊃「うちEC売上」、「全社」⊃「各部門」)がある場合、そのままファクトやマートに入れない。排他的な値に分解する(例:EC/EC以外=合計−EC)か、テーブルを分ける。包含する側の値は照合用に ref_ へ分ける
7. 地名がある場合は、Tableauの地理的役割でそのまま認識される正式な表記にする。空白(全角を含む)・番号・記号を混ぜない
8. 列名は、Tableauにそのまま表示される日本語にし、単位を末尾に付ける(例:売上_円)。各テーブルと各列に日本語の説明を付ける
9. メジャーの「足してよいか」は分類の軸ごとに書く(例:地域方向○、期間方向×)。「SUMしてよい」とひとまとめに書かない
10. ディメンションとファクトの結合キーは、列名と型をそろえる(Tableauのリレーションでそのままつなげるため)

# 実装上の制約(設計に反映し、コードを書くときにも守る)
- 日本語の列名は、SQL本文、uniqueKey、rowConditionsのすべてでバッククォートで囲む
- カスタムアサーションのSELECT列には必ず別名をつける(SELECT 1 は禁止。ビュー化に失敗するため)
- パーティション・クラスタは最初に決める(後から変えるとDROPが必要になるため)。付けない場合は理由を書く
- TRIMは半角スペースしか取らない。全角スペースや記号は REGEXP_REPLACE で明示的に取り除く
- 数値への変換は SAFE_CAST を使い、変換に失敗した行を検知できるようにする

# 設計書に書いてほしいこと
1. 既存カラムの棚卸し:rawの各列が何を表すか、データ行はどこからどこまでか
2. 汚れの一覧:見出し、セル結合、空白(全角を含む)、単位、注記、合計行・小計行、記号(秘匿・該当なし・未集計などを表す記号を含む)など。BigQueryの仕様で取り切れないものと、その取り除き方も書く
3. 分類列の一覧:各分類列の値の一覧と、値どうしが排他的か包含関係かを、実データで確かめて書く。包含関係があれば、ルール6に沿ってどう分解するかを書く
4. データ行の外にある情報:時点・期間・単位・対象範囲などが、表題・見出し・注記にしか書かれていない場合、それをどこから取ってどう持つかを書く
5. 足してはいけない指標の扱い:元の表に比率・平均・率・指数などがあれば列挙し、公表値を使うか計算し直すか、どのテーブルに置くかを書く
6. 各テーブルの粒度:1行が何を表すか、想定の行数とその計算根拠
7. DWHのテーブル定義:テーブル名、キー、パーティション・クラスタ、各列の名前・型・単位・説明
8. マートのテーブル定義:各列の名前・型・ディメンションかメジャーか・単位・説明・足してよいか(軸ごと)
9. 変換の流れ:raw → stg → dim/fct/ref → mart
10. データ品質チェック(assertions)の一覧。最低限、次のものを入れる
   - 型が上のルールどおりか(INFORMATION_SCHEMA.COLUMNSで確認)
   - 行数が想定どおりか
   - 粒度を表す列の組み合わせが重複しないか
   - 分類の列が、決めた値だけになっているか
   - 型変換(SAFE_CAST)に失敗した行が0件か
   - 合計行が混ざっていないか。ref_ の公表値との差が理論上の許容範囲(丸め誤差の最大値など)に収まるか
   - 加算性:マートを全行SUMした値 = ref_ の総計(分類に包含関係が残っていると、合わずに落ちる)
   - 各テストについて「どんな壊れ方をしたら落ちるか」を書き、壊したデータ(合計行を1行混ぜる、分類を重ねる、など)で落ちることを確かめた結果を書く
   - 必ず通ってしまうテストや、甘すぎる許容値になっていないか。許容値にはその根拠を書く
11. 足してよい指標と、足してはいけない指標の区別
12. 判断に迷った点と、人間に確認したいこと

# 書き方のルール
- 書いたことはすべて「確認した事実」か「推測」かを区別する。確認した事実には根拠(サンプル行・プロファイル・実行したクエリ)を付ける

プロンプトは、何度も練り直した末、これが良いかなと思いました。が、まだまだ改善できると思うので、改善点あればどこかで教えて欲しいです(小声)

AIが設計書を作成してくれました。長文になるので、AIからの確認事項だけまとめます。

AIが「人間に確認したい」と言ったこと:

確認事項への対応を整理します。

Q1. 端数処理(四捨五入)による「男+女≠男女計」の扱い
   - 都道府県ごとに、男+女と公表の男女計が最大±1千人ずれることは許容します。元の公表値の時点で四捨五入のずれがあり、データの誤りではないためです。
   - ただし、テストを「全国の合計だけを確かめる緩い形」にはしないでください。都道府県ごとのテストはそのまま残します。
   - 許容範囲は次のとおりにしてください。
     ・総人口、日本人人口:都道府県ごとに「公表の男女計」と「男+女」の差が1千人以内
     ・外国人人口:都道府県ごとに「外国人の男女計」と「外国人の男+女」の差が2千人以内
       (総人口のずれと日本人人口のずれ、それぞれ±1が重なるため)
     ・全国:前回お伝えしたとおり、人口区分×性別ごとに、47都道府県の合計と全国の差が24千人以内
   - 外国人の男女計は「総人口の男女計 − 日本人人口の男女計」(公表値どうしの差)で計算してください。外国人の男+女を足して作らないでください。
   - 外国人の列の説明には、「男+女が男女計と最大±2千人ずれることがある」と書いてください。
   - 参考:Excelで計算すると、男+女≠男女計になるのは、総人口が16県、日本人人口が7県、外国人人口が18県です。最大は福島県の外国人で、−2千人です。

Q2. 英文名「Japan」の扱い
   - 推測ではなく確定です。全国の行の c12 は "Japan" です。c12 の値が入っている48件は、全国1件と47都道府県です。
   - ref_national_population に地域名を持たせる場合は、「全国」「Japan」をそのまま使ってください(前後の空白は除去します)。
   - dim_prefectures には入れないでください。47都道府県だけにします。

STEP 3:AIに実装させる

確認事項への回答・設計書の修正事項を整理してから、実装を頼みます。

設計書ありがとうございます。

【確認事項への回答】
Q1. 端数処理(四捨五入)による「男+女≠男女計」の扱い
   - 都道府県ごとに、男+女と公表の男女計が最大±1千人ずれることは許容します。元の公表値の時点で四捨五入のずれがあり、データの誤りではないためです。
   - ただし、テストを「全国の合計だけを確かめる緩い形」にはしないでください。都道府県ごとのテストはそのまま残します。
   - 許容範囲は次のとおりにしてください。
     ・総人口、日本人人口:都道府県ごとに「公表の男女計」と「男+女」の差が1千人以内
     ・外国人人口:都道府県ごとに「外国人の男女計」と「外国人の男+女」の差が2千人以内
       (総人口のずれと日本人人口のずれ、それぞれ±1が重なるため)
     ・全国:前回お伝えしたとおり、人口区分×性別ごとに、47都道府県の合計と全国の差が24千人以内
   - 外国人の男女計は「総人口の男女計 − 日本人人口の男女計」(公表値どうしの差)で計算してください。外国人の男+女を足して作らないでください。
   - 外国人の列の説明には、「男+女が男女計と最大±2千人ずれることがある」と書いてください。
   - 参考:Excelで計算すると、男+女≠男女計になるのは、総人口が16県、日本人人口が7県、外国人人口が18県です。最大は福島県の外国人で、−2千人です。

Q2. 英文名「Japan」の扱い
   - 推測ではなく確定です。全国の行の c12 は "Japan" です。c12 の値が入っている48件は、全国1件と47都道府県です。
   - ref_national_population に地域名を持たせる場合は、「全国」「Japan」をそのまま使ってください(前後の空白は除去します)。
   - dim_prefectures には入れないでください。47都道府県だけにします。

【修正してほしい点】
1.-

この設計で、Dataformの定義を作ってください。
- raw → stg → dwh(dim/fct/ref)→ mart の順に、SQLXファイルを分ける
- 各テーブルと各列に、日本語の説明をつける
- 設計書の「データ品質チェック」と上の修正を、すべて assertions(テスト)として入れる
- まだ実行はしない

それっぽいデータリネージが作成されました。sqlxファイルもぱっと見よさそうです。

STEP 4:Dataformで実行する

生成されたsqlxファイルに問題がなければ、実行します。

  1. Dataformのワークスペースで「実行を開始」
  2. 「すべてのアクション」を選んで実行
  3. 実行履歴で、テーブル作成とテストの結果を確認する

できたテーブルは、元のExcelの数字を2つだけ拾って照らし合わせます。

確認する値元のExcelできたテーブル
東京都・総人口・男女計14,178(千人)14,178
沖縄県・日本人人口・女732(千人)732

簡単なテストにはなりますが、処理は上手くいっていそうです。沖縄県を選んだのは、元の名前の後ろに全角スペースが4つ付いているからです。「沖縄県」で絞り込んで出てくれば、空白の処理もうまくいっています。

汚いExcelが、テストつきのDWHと、Tableauにそのまま渡せるワイドテーブルになりました。

NEXT

今回は、「データの前処理・加工を丸ごとAIに任せられないか?」というテーマで、「raw/DWH/マート」の整理の下、Data Engineering Agentを活用したデータパイプライン作成を実践しました。

次回は、Tableau Desktop Free Editionを用いて、今回作成したBigQuery上のデータに接続・可視化してみます。

Sources

出典:「人口推計」(総務省統計局)第4表 都道府県,男女別人口及び人口性比-総人口,日本人人口(2024年10月1日現在)、政府統計の総合窓口(e-Stat)(https://www.e-stat.go.jp/stat-search/files?layout=dataset&toukei=00200524&stat_infid=000040268913)

Follow the signal.

𝕏  FOLLOW @tkoily→