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ベースの変換ツール) |
| AI | BigQuery 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層に汚れごとそのまま保存しておくためです。
- BigQueryでデータセット「raw」を作る
- 「テーブルを作成」→ ソースは「アップロード」、ファイル形式は「CSV」、テーブル名は
population_pref_2024 - スキーマは「テキストとして編集」にして、次の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 にする(c01、c02…の行を飛ばす)
- 「テーブルを作成」

これで、汚れがそのままの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ファイルに問題がなければ、実行します。
- Dataformのワークスペースで「実行を開始」
- 「すべてのアクション」を選んで実行
- 実行履歴で、テーブル作成とテストの結果を確認する

できたテーブルは、元の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)