こんにちは。NTT西日本の谷です。
近年、データドリブンな意思決定の重要性が高まる中、「どのようにデータを整形し、分析できる状態にするか」が課題となっています。今回は、モダンなデータ分析基盤の構築手法として注目されているdbt（data build tool）を使い、日本観光振興会が公開している観光来訪者数のオープンデータを整形・可視化する方法をご紹介します。
この記事で得られること
本記事はハンズオン形式で、実際に手を動かしながらパイプライン構築を体験できる内容になっています。
- ✅ 実践的なdbtスキル: dbt Projects on Snowflakeを使った実データ加工のノウハウ
- ✅ 分析用データモデリングの知見: スタースキーマによるデータ設計手法の理解と適応
- ✅ Tableauダッシュボードの構築スキル: 観光データを可視化したTableauダッシュボード
- ✅ データ品質を担保するテスト実装力: dbt testを活用した、データ品質保証(QA)の組み込み方法
想定読者
- データエンジニア・データアナリスト志望の方
- SQLは書けるが、dbtは初めての方
- モダンデータスタックに興味がある方
- 実際のデータパイプラインを構築してみたい方
所要時間
- 全体: 約2〜3時間
- 環境構築: 30分
- データ準備とアップロード: 20分
- dbtプロジェクトの実装: 60〜90分
- データ品質テストの実装: 10分
- Tableauでの可視化: 30分
前提条件
- SQLの基本的な知識（SELECT、JOIN、GROUP BYなど）
- Webブラウザ（Snowflakeはブラウザ上で完結します）
- データのダウンロード先（日本観光振興会サイトからCSVをダウンロード）
- データ可視化に利用しているTableau Desktopはトライアル版を利用しています（Tableau Public Desktop EditionではSnowflakeとの接続はできません）
💡 ヒント: dbt Projects on Snowflakeを初めて使う方向けに、各ステップに「確認ポイント」を設けているので、進捗を確認しながら進められます。
- この記事で得られること
- 1. 使用する技術の概要
- 2. 全体アーキテクチャ
- 3. 環境構築
- 4. データの準備とアップロード
- 5. dbtプロジェクトの実装
- 6. データ品質テストの実装
- 7. Tableauでの可視化
- 8. まとめ
- 執筆者
- 商標について
- 免責事項
1. 使用する技術の概要
今回構築するデータ基盤では、公益社団法人日本観光振興協会が公開しているデジタル観光統計オープンデータを使用し、下記の技術スタックで分析基盤を構築します。
- データ加工：dbt
- データ蓄積：Snowflake
- データ可視化：Tableau
dbt（data build tool）とは
dbtは、データウェアハウス内のデータ加工に特化したツールです。従来のETLツールと異なり、ELT（Extract, Load, Transform）のTに焦点を当てています。 データ分析をする際に欲しいデータを作るために、SQLを使いデータマートを作ることも多いと思いますが、データ加工に関する領域は作った人にしかわからない属人化がしやすい領域となりがちです。そのような課題の解決やその他にもチームや組織でデータを管理する人にとって便利な機能が多数搭載されていることから、近年注目されています。
データモデリング時の課題とdbtによる解決
| 課題 | 従来の問題点 | dbtによる解決策 |
|---|---|---|
| 処理内容の不透明性 | 最終的なデータマートを作成するまでにどのような処理を行ったのかわかりにくい。複雑なSQLが何層にも重なり、全体像が見えない。 | データリネージの自動生成：モデル間の依存関係を視覚的に表示。どのテーブルがどのテーブルから作られているか一目で把握できる。 |
| 仕様変更への対応困難 | データソースの中身が変わるなど仕様変更が発生した際に、影響範囲の特定と修正が大変。どこを修正すればいいのか分からない。 | モジュール化とref()関数：テーブル参照を名前で管理。上流のテーブル名を変更しても、下流のSQLを自動で追従。依存関係も自動解決。 |
| 属人化とナレッジの分散 | 人によって参照しているDBやテーブル、データ加工のロジックが異なる。担当者が変わるとメンテナンス不可能に。 | Gitによるバージョン管理：すべてのSQLをコードとして管理。変更履歴を追跡でき、チーム全体で共有可能。 |
| データ品質の担保 | データの異常値や欠損があっても気づけない。分析結果の信頼性が低下。 | テスト機能：not_null、unique、範囲チェックなどを自動実行。データ品質を継続的に保証。 |
| ドキュメントの陳腐化 | 手動で書いたドキュメントが実装と乖離。「ドキュメントが古くて信用できない」状態に。 | 自動ドキュメント生成：SQLから自動的にドキュメントを生成。常に最新の状態を維持。 |
Snowflakeとは
クラウドネイティブなデータウェアハウスサービスです。コンピュートとストレージが分離されており、必要に応じてリソースをスケールできます。 最近Snowflake上でdbtを利用できる「dbt Projects on Snowflake」がGAとなったため、今回はこの機能を利用します。
Tableauとは
BIツールの一つで、視覚的にデータ分析・可視化ができるツールです。直感的な操作性と様々なデータソースとの接続を簡単に行うことができます。今回はTableau Desktop(有償版)を使っていますが、Snowflakeに接続可能な他のBIツールに置き換えることも可能です。
2. 全体アーキテクチャ
今回構築するデータ基盤の全体像は以下のとおりです。今回は公益社団法人日本観光振興協会が公開しているデジタル観光統計オープンデータを使用します。 データ処理の流れは下記の通りです。
[日本観光振興会オープンデータ (CSV)]
↓
[Snowflake (データレイク層)]
↓
[dbt変換処理]
↓
┌─────────────┐
│ ステージング層 │ ← データクレンジング、基本的な型変換
└─────────────┘
↓
┌─────────────┐
│ マート層 │ ← ディメンション・ファクトテーブル
└─────────────┘
↓
[Tableau] ← ダッシュボード・可視化
データモデルの設計思想
今回は「集計クエリのパフォーマンス向上」「BIツールでの分析が容易」「データの重複を削減」という観点から、スタースキーマを採用しました。スタースキーマは、中心となるファクトテーブル(トランザクションデータ)と、それを取り巻くディメンションテーブル(マスターデータ)で構成されます。それぞれのテーブルは下記のとおりです。 ※今回dim_datesで作成するディメンションをBIで利用しませんが、データの系統（データリネージ）を確認するために作成しています。
ディメンションテーブル（マスターデータ）
dim_dates：日付マスター（年月情報）dim_regions：地域マスター（都道府県、市区町村、地方ブロック情報）
ファクトテーブル（トランザクションデータ）
fact_tourism_monthly：月次観光来訪者数
3. 環境構築
⏱️ 所要時間: 約30分
🎯 このセクションのゴール:
- Snowflakeアカウントを作成し、ログインできる状態にする
- データベース、ウェアハウス、スキーマを作成する
- dbt Projects on Snowflakeプロジェクトを立ち上げる
3.1 Snowflakeアカウントの準備
まず初めにSnowflake環境を準備するため、30日間のトライアルに申し込みます。 今回はAWSの東京リージョン＆Enterpriseエディションを選択しました。
手順:
- Snowflakeの公式サイトからトライアルアカウントを作成
- アカウント作成後、以下の情報をメモ（後で使用します）
- アカウント識別子（例：
abc12345.ap-northeast-1.aws） - ユーザー名
- パスワード
- アカウント識別子（例：
📝 重要: アカウント識別子は後でTableau接続時に必要になるため、必ずメモしておきましょう。
⚠️ Snowflakeトライアルの留意点:
- 期間: 30日間の無料トライアル
- クレジットカード: 登録時にクレジットカード情報は不要です
- 無料クレジット: $400分の無料クレジットが付与されます
- 自動課金なし: トライアル終了後、自動的に有料プランに移行することはありません(クレジットカードなどの支払い設定を行っている場合は課金されるためご注意ください)
- リージョン選択: 日本からのアクセスの場合、AWS東京リージョン（ap-northeast-1）を推奨
- エディション: Standardエディションでも実施可能ですが、一部機能制限がある場合があるためEnterpriseエディション以上が推奨です
- コスト管理: ウェアハウスを使い終わったら停止する習慣をつけると、無料クレジットを節約できます
✅ 確認ポイント:
- [ ] Snowflakeのログイン画面にアクセスできる
- [ ] ユーザー名とパスワードでログインできる
- [ ] Snowflakeのホーム画面が表示される
3.2 Snowflake上でデータベースとスキーマを作成
SnowflakeのWebコンソールにログインし、ワークスペースを利用してSQLを実行します。
手順:
- 左側メニューから「プロジェクト」→「ワークスペース」を選択
- 「+ 新規作成」→「SQLワークシート」をクリック
プロジェクトからワークスペースを選択
新規作成からSQLファイルを選ぶ
- 以下のSQLをコピーしてワークシートに貼り付け
⚠️ 注意: 今回は検証用のため
ACCOUNTADMINロールを利用していますが、実運用時は適切な権限を持つ専用ロールの設定を行う必要があります。💡 ヒント:
AUTO_SUSPEND = 60を設定することで、60秒間未使用の場合にウェアハウスが自動停止し、コストを削減できます。小規模な開発ではX-SMALLで十分です。
-- 設定にAccountAdminのロールを利用 use role accountadmin; -- データベース作成 CREATE DATABASE tourism_analytics; -- ウェアハウス作成（dbtで使用） CREATE WAREHOUSE dbt_wh WITH WAREHOUSE_SIZE = 'X-SMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE; -- スキーマ作成 USE DATABASE tourism_analytics; CREATE SCHEMA DATA;
- ワークシート上部の青色三角ボタン横の「▽」から「すべて実行」を選択
SQLをワークシートにコピー＆ペースト
青色三角の横の「▽」から全て実行し、結果が「Schema DATA successfully created.」になる
✅ 確認ポイント:
- [ ] すべてのSQL文が正常に実行される（緑色のチェックマークが表示される）
- [ ] 画面下部の結果欄に「Database TOURISM_ANALYTICS successfully created」と表示される
- [ ] 「Warehouse DBT_WH successfully created」と表示される
- [ ] 「Schema DATA successfully created」と表示される
- [ ] 左側のデータベース一覧に「TOURISM_ANALYTICS」が表示される
🔧 トラブルシューティング: エラーが出た場合は、すでに同じ名前のデータベースやウェアハウスが存在する可能性があります。その場合は、先に
DROP DATABASE IF EXISTS tourism_analytics;を実行してください。
3.3 dbt Projects on Snowflakeの有効化
今回はdbt Projects on Snowflakeを使用します。これにより、ローカル環境にdbtをインストールする必要がなく、Snowflake上でdbtを直接実行できます。
💡 ヒント: 実運用ではGit連携して運用することが多いですが、今回は学習目的のため、Snowflake上で完結する方法で進めます。
3.3.1 dbt Projects on Snowflakeのセットアップ手順
手順:
- Snowflakeの左側メニューから「プロジェクト」→「ワークスペース」を選択
- 「+ 新規作成」をクリックし、「dbtプロジェクト」を選択
- 以下の情報を入力:
- プロジェクト名:
dbt_Tourism_Project - ロール:
ACCOUNTADMIN - ウェアハウス:
DBT_WH（先ほど作成したもの） - データベース:
TOURISM_ANALYTICS - スキーマ:
DATA
- プロジェクト名:
- 「作成」ボタンをクリック
dbtプロジェクトを作成
プロジェクト名、ロール、ウェアハウス、データベース、スキーマを設定
✅ 確認ポイント:
- [ ] dbtプロジェクトが作成され、エディタ画面が表示される
- [ ] 左側に
models/、macros/、seeds/などのフォルダが自動生成される - [ ] サンプルファイル（
my_first_dbt_model.sqlなど）が含まれている
📝 補足: これで自動的にdbtを利用するために必要なフォルダやファイルが準備できました。まずはサンプルファイルで動作確認してから、実際のデータモデルを作成していきます。
3.3.2 dbt_project.yml設定
dbt_project.ymlはdbtプロジェクトの中核となる設定ファイルです。 本ファイルを設定することで、「プロジェクト全体の設定を一元管理できる」「チームメンバー全員が同じ設定で開発できる」「モデルの構造化ルールを統一できる」といったメリットがあります。 このファイルで以下の内容を定義します。
dbt_project.ymlの役割：
プロジェクトの基本情報
- プロジェクト名やバージョンの定義
- dbtの設定バージョンの指定
ディレクトリ構造の定義
- SQLモデルの配置場所（
models/） - seedファイルの配置場所（
seeds/） - テストの配置場所（
tests/） - マクロの配置場所（
macros/）
- SQLモデルの配置場所（
モデルごとの動作設定
- マテリアライゼーション方法（table、view、incremental等）
- 出力先スキーマの指定
- タグやドキュメント設定
プロジェクト設定:
📁 ファイル: dbt_project.yml
# dbt_project.yml の基本設定 name: 'dbt_Tourism_Project' version: '1.0.0' config-version: 2 profile: 'dbt_Tourism_Project' model-paths: ["models"] seed-paths: ["seeds"] test-paths: ["tests"] macro-paths: ["macros"] # seedの設定 seeds: dbt_Tourism_Project: +schema: raw # モデルの設定 models: dbt_Tourism_Project: staging: +materialized: table +schema: staging mart: +materialized: table +schema: mart
各設定項目の説明：
| 項目 | 説明 |
|---|---|
name |
プロジェクト名。dbt内部でプロジェクトを識別するために使用 |
version |
プロジェクトのバージョン |
config-version |
dbt_project.ymlの設定形式バージョン（現在は2が推奨） |
profile |
データベース接続情報を定義したプロファイル名 |
model-paths |
SQLモデルファイルを格納するディレクトリ |
seed-paths |
CSVファイル（seed）を格納するディレクトリ |
test-paths |
カスタムテストを格納するディレクトリ |
macro-paths |
マクロ（共通関数）を格納するディレクトリ |
seeds 配下 |
seedファイルの設定（出力先スキーマ等） |
models 配下 |
モデルごとの設定（マテリアライゼーション、スキーマ等） |
+materialized |
テーブルの作成方法（table=物理テーブル、view=ビュー） |
+schema |
モデルまたはseedを作成するスキーマ名 |
プロジェクトの実行:
projectの設定が終わったら、一度dbtを実行してみます。 「Completed successfully」という表示がされたら、うまく動いています。 実はdbtプロジェクトを作成した際、modelsフォルダー内にSQLが書かれたファイルが格納されており、このファイルを参照し設定したStagingスキーマ内にテーブルとビューが作成されています。 DAGのタブを選択すると作成したテーブル同士の関係性（リネージ）も確認することができます。
青色の実行ボタン横のコマンド選択で「実行」を選択し、青色のボタンを押下
Stagingスキーマにテーブルとビューが作成
4. データの準備とアップロード
⏱️ 所要時間: 約20分
🎯 このセクションのゴール:
- 日本観光振興会から観光データ（CSV）をダウンロードする
- dbtのseed機能を使ってSnowflakeにデータをアップロードする
- データが正しくロードされたことを確認する
4.1 観光データのダウンロード
日本観光振興会の観光統計サイトから、観光来訪者数のCSVデータをダウンロードします。今回は都道府県別、市区町村別のデータを2021年1月から2025年10月までをデータソースとします。
💡 ヒント: ダウンロードしたファイルが異なる場合、後のコード上で一部修正が必要になります。
手順:
- 上記リンクにアクセス
- 「都道府県別」と「市区町村別」のデータをそれぞれダウンロード
- 最低でも2〜3ヶ月分のデータを用意すると、後の分析で傾向が見やすくなります
- ファイル名を以下の形式に変更（dbtでの処理がしやすくなります）:
- 都道府県:
pref_YYYYMM.csv（例:pref_202301.csv） - 市区町村:
city_YYYYMM.csv（例:city_202301.csv）
- 都道府県:
💡 ヒント: ファイル名の命名規則を統一することで、後のマクロ処理が簡単になります。
データの構造例:
year,month,region_category,data_category,prefecture_code,prefecture_name,region_code,region_name,visitor_count 2023,1,市区町村,観光来訪者数,1,北海道,1101,札幌市,1500000 2023,1,市区町村,観光来訪者数,13,東京都,13101,千代田区,2500000
主要カラムの説明:
- year, month: 年月
- region_category: 「都道府県」または「市区町村」
- prefecture_code: 都道府県コード（1〜47）
- region_code: 地域コード
- visitor_count: 観光来訪者数
4.2 Snowflakeへのデータアップロード
dbtでは、小規模なCSVファイルをseedとして管理できます。これは参照データやテストデータに最適です。
📚 dbt seedとは？: CSV形式のデータをテーブルとしてデータウェアハウスにロードする機能です。バージョン管理が可能で、dbtコマンドで簡単にロードできます。
seedのメリット:
- バージョン管理が可能（CSVをGitで管理）
- dbtコマンドで簡単にロード可能
- 開発環境と本番環境で同じデータを使用できる
4.2.1 dbt Projects on Snowflakeでのseedアップロード
今回は都道府県と市区町村のデータがそれぞれあるので、整理してアップロードします。
手順:
- dbtプロジェクト画面で
seeds/フォルダを右クリック - 「新しいフォルダ」を選択し、
prefを作成 - 同様に
cityフォルダも作成 - 各フォルダにCSVファイルをアップロード:
seeds/pref/配下pref_202301.csvpref_202302.csv- （ダウンロードした月数分）
seeds/city/配下city_202301.csvcity_202302.csv- （ダウンロードした月数分）
seeds内にPrefとCityのサブディレクトリを作成
seeds内にCSVファイルを保存
プロジェクト構造(例):
tourism_analytics/ ├── seeds/ │ ├── pref/ │ │ ├── pref_2023_01.csv │ │ └── pref_2023_02.csv │ │ └── pref_XXXX_XX.csv │ └── city/ │ ├── city_2023_01.csv │ └── city_2023_02.csv │ └── city_XXXX_XX.csv ├── models/ ├── macros/ └── dbt_project.yml
4.2.2 dbtコマンドを実行してseedをロード
「シード」コマンドを実行することで、CSVファイルがSTAGING_RAWスキーマにテーブルとして作成されます。(5分程度時間がかかります)
SQLシートでデータが格納されているかチェックしておきます。
実行結果の確認:
-- アップロードされたテーブルを確認 SHOW TABLES IN tourism_analytics.staging_raw; -- データの中身を確認 SELECT * FROM tourism_analytics.staging_raw.city202301 LIMIT 10; SELECT * FROM tourism_analytics.staging_raw.pref202301 LIMIT 10;
STAGING_RAWスキーマにデータがロードされる
5. dbtプロジェクトの実装
⏱️ 所要時間: 約60〜90分
🎯 このセクションのゴール:
- マクロを使った複数テーブルの統合処理を実装する
- ステージング層でデータクレンジングを行う
- マート層でディメンションテーブルとファクトテーブルを作成する
- スキーマ定義とテストを設定する
5.1 プロジェクト構成
この章から本格的にデータ加工を行っていきます。dbt Projects on Snowflake上でのプロジェクト構成は以下のようになります。 複数のファイルを作成し、適切なフォルダに保存する必要があるため保存場所に注意が必要です。
dbt_Tourism_Project/ (dbt Projects on Snowflakeプロジェクト) ├── models/ │ ├── staging/ # ステージング層 │ │ ├── stg_city_tourism_from_seeds.sql │ │ ├── stg_pref_tourism_from_seeds.sql │ │ └── schema.yml │ └── mart/ # マート層 │ ├── dim_dates.sql │ ├── dim_regions.sql │ ├── fact_tourism_monthly.sql │ └── schema.yml ├── macros/ # 共通処理 │ ├── union_seed_tables.sql │ └── get_tourism_data_tests.sql ├── seeds/ # CSVデータ │ ├── pref/ # 都道府県データ │ │ ├── pref_2023_01.csv │ │ └── pref_2023_02.csv │ │ └── pref_20XX_XX.csv │ └── city/ # 市区町村データ │ ├── city_2023_01.csv │ └── city_2023_02.csv │ └── city_202X_XX.csv └── dbt_project.yml # プロジェクト設定
5.2 マクロの実装：複数テーブルの統合
観光データは月ごとに別々のCSVファイルとして提供されるため、これらを統合する必要があります。dbtのマクロ機能を使うと、動的にテーブルを結合できます。 マクロを利用することにより、「新しい月のデータを追加しても、コードを変更する必要がない」「DRY原則（Don't Repeat Yourself）に従った実装」「メンテナンス性の向上」といったメリットがあります。
このマクロは以下を実現します。
1. 特定のパターン（例：city_*）に一致するテーブルを自動検索
2. すべてのテーブルをUNION ALLで結合
3. ソースファイル名を追跡
重要：seedのフォルダ構造とテーブル名の関係
seedファイルはseeds/pref/とseeds/city/に分けて格納していますが、Snowflake上ではフォルダ構造に関わらずすべて同じスキーマ（STAGING_RAW）にフラットに作成されます。
例：
- seeds/city/city_2023_01.csv → STAGING_RAW.city202301テーブル
- seeds/pref/pref_2023_01.csv → STAGING_RAW.pref_202301テーブル
したがって、マクロではschema_pattern='STAGING_RAW'を指定し、city_*やpref_*のようなテーブル名パターンで検索することで、該当するすべてのseedテーブルを取得できます。
📁 ファイル: macros/union_seed_tables.sql
-- macros/union_seed_tables.sql {% macro union_seed_tables(prefix) %} {# 実際のテーブルリストを定義 #} {%- if prefix == 'CITY' -%} {%- set table_list = [ 'CITY202101', 'CITY202201', 'CITY202301', 'CITY202401', 'CITY202501', 'CITY202502', 'CITY202503', 'CITY202504', 'CITY202505', 'CITY202506', 'CITY202507', 'CITY202508', 'CITY202509', 'CITY202510' ] -%} {%- elif prefix == 'PREF' -%} {%- set table_list = [ 'PREF202101', 'PREF202201', 'PREF202301', 'PREF202401', 'PREF202501', 'PREF202502', 'PREF202503', 'PREF202504', 'PREF202505', 'PREF202506', 'PREF202507', 'PREF202508', 'PREF202509', 'PREF202510' ] -%} {%- endif -%} {# UNION ALLクエリを生成 #} {% for table_name in table_list %} {% if loop.first %} -- Union all {{ prefix }} seed tables {% else %} UNION ALL {% endif %} SELECT year, month, region_category, data_category, {%- if prefix == 'CITY' %} prefecture_code, prefecture_name, {% endif -%} region_code, region_name, visitor_count, '{{ table_name }}' AS source_file, CURRENT_TIMESTAMP() AS processed_at FROM {{ source('data_raw', table_name) }} {% endfor %} {% endmacro %}
マクロのポイント:
schema_pattern='STAGING_RAW': seedテーブルが格納されているSTAGING_RAWスキーマを明示的に指定table_pattern=prefix + '%':cityやprefで始まるテーブルをパターンマッチで検索relation.identifier: 元のseedファイル名（テーブル名）を記録
5.3 ステージング層の実装
ステージング層では、以下の処理を行います。
- データのクレンジング（NULL、異常値のチェック）
- 日付フィールドの生成
- データ品質フラグの付与
ステージング層を作ることにより、「生データと分析用データを分離」「データ品質の問題を早期に発見」「後続の処理で綺麗なデータのみを扱える」といったメリットがあります。
📁 ファイル: models/staging/stg_city_tourism_from_seeds.sql
-- models/staging/stg_city_tourism_from_seeds.sql {{ config( materialized='table', schema='Staging' ) }} WITH combined_city_data AS ( -- マクロを呼び出して複数テーブルを統合（プレフィックスをCITYに変更） {{ union_seed_tables('CITY') }} ), enriched_data AS ( SELECT year AS visit_year, month AS visit_month, region_category, data_category, prefecture_code, prefecture_name, region_code, region_name, visitor_count, source_file, processed_at, -- 日付フィールドの作成（分析で使いやすくするため） DATE(year || '-' || LPAD(month, 2, '0') || '-01') AS visit_date, -- 年月文字列の作成（ソートキーとして便利） year || LPAD(month, 2, '0') AS year_month, -- データ品質フラグ CASE WHEN visitor_count IS NULL OR visitor_count < 0 THEN 'invalid_count' WHEN year < 2020 OR year > YEAR(CURRENT_DATE()) THEN 'invalid_date' WHEN month < 1 OR month > 12 THEN 'invalid_month' ELSE 'valid' END AS data_quality_flag FROM combined_city_data ) SELECT visit_year, visit_month, visit_date, year_month, region_category, data_category, prefecture_code, prefecture_name, region_code, region_name, visitor_count, data_quality_flag, source_file, processed_at FROM enriched_data WHERE data_quality_flag = 'valid' -- 有効なデータのみを後続処理に渡す ORDER BY visit_year, visit_month, prefecture_code, region_code
同様に、都道府県データ用のステージングモデル（stg_pref_tourism_from_seeds.sql）も実装します。
ファイル名：models/staging/stg_pref_tourism_from_seeds.sql
変更点： {{ union_seed_tables('PREF') }}
都道府県データからは以下のカラムを削除：
- prefecture_code
- prefecture_name
都道府県データには元々これらのカラムが存在しないため、SELECTから除外しました。
📁 ファイル: models/staging/stg_pref_tourism_from_seeds.sql
-- models/staging/stg_pref_tourism_from_seeds.sql {{ config( materialized='table', schema='Staging' ) }} WITH combined_pref_data AS ( -- マクロを呼び出して複数テーブルを統合 {{ union_seed_tables('PREF') }} ), enriched_data AS ( SELECT year AS visit_year, month AS visit_month, region_category, data_category, region_code, region_name, visitor_count, source_file, processed_at, -- 日付フィールドの作成 DATE(year || '-' || LPAD(month, 2, '0') || '-01') AS visit_date, -- 年月文字列の作成 year || LPAD(month, 2, '0') AS year_month, -- データ品質フラグ CASE WHEN visitor_count IS NULL OR visitor_count < 0 THEN 'invalid_count' WHEN year < 2020 OR year > YEAR(CURRENT_DATE()) THEN 'invalid_date' WHEN month < 1 OR month > 12 THEN 'invalid_month' ELSE 'valid' END AS data_quality_flag FROM combined_pref_data ) SELECT visit_year, visit_month, visit_date, year_month, region_category, data_category, region_code, region_name, visitor_count, data_quality_flag, source_file, processed_at FROM enriched_data WHERE data_quality_flag = 'valid' ORDER BY visit_year, visit_month, region_code
Stagingテーブルの元となるデータの保存先を定義します。
📁 ファイル: models/staging/sources.yml
# models/staging/sources.yml version: 2 sources: - name: data_raw database: tourism_analytics schema: data_raw tables: - name: CITY202101 - name: CITY202201 - name: CITY202301 - name: CITY202401 - name: CITY202501 - name: CITY202502 - name: CITY202503 - name: CITY202504 - name: CITY202505 - name: CITY202506 - name: CITY202507 - name: CITY202508 - name: CITY202509 - name: CITY202510 - name: PREF202101 - name: PREF202201 - name: PREF202301 - name: PREF202401 - name: PREF202501 - name: PREF202502 - name: PREF202503 - name: PREF202504 - name: PREF202505 - name: PREF202506 - name: PREF202507 - name: PREF202508 - name: PREF202509 - name: PREF202510
5.4 マート層の実装
マート層では、ビジネスロジックに基づいた分析用のテーブルを作成します。
5.4.1 ディメンションテーブル1：日付マスター
まずは日付マスターを作成します。日付マスターを作成することにより、「データが存在しない月も含めて、連続した時系列データを表現できる」「年月の計算が容易になる」「BIツールでの時系列分析が簡単になる」といったメリットがあります。
📁 ファイル: models/mart/dim_dates.sql
-- models/mart/dim_dates.sql {{ config( materialized='table', schema='mart' ) }} WITH RECURSIVE date_spine AS ( -- 開始日 SELECT DATE('2021-01-01') as date_month UNION ALL -- 月を1つずつ増やす（再帰的に2030年12月まで） SELECT DATEADD(month, 1, date_month) FROM date_spine WHERE date_month < DATE('2030-12-31') ), date_dimension AS ( SELECT date_month as date_key, YEAR(date_month) as year, MONTH(date_month) as month, CURRENT_TIMESTAMP() as created_at FROM date_spine ) SELECT * FROM date_dimension ORDER BY date_key
5.4.2 ディメンションテーブル2：地域マスター
次に地域マスターを作成します。その中で、地方ブロックを追加し、別の切り口での分析ができるようにします。
他にも様々な分析の切り口を作ることも考えられますが、今回は地域ブロックだけにします。
📁 ファイル: models/mart/dim_regions.sql
-- models/mart/dim_regions.sql {{ config( materialized='table', schema='mart' ) }} WITH city_regions AS ( -- 市区町村データから地域情報を取得 SELECT DISTINCT region_code, region_name, region_category, prefecture_code, prefecture_name FROM {{ ref('stg_city_tourism_from_seeds') }} WHERE region_code IS NOT NULL AND region_name IS NOT NULL ), pref_regions AS ( -- 都道府県データから地域情報を取得 SELECT DISTINCT region_code, region_name, region_category, NULL as prefecture_code, region_name as prefecture_name FROM {{ ref('stg_pref_tourism_from_seeds') }} WHERE region_code IS NOT NULL AND region_name IS NOT NULL ), combined_regions AS ( SELECT * FROM city_regions UNION SELECT * FROM pref_regions ), enriched_regions AS ( SELECT -- 複合キーとしてregion_idを作成 region_code || '_' || region_category as region_id, region_code, region_name, region_category, prefecture_code, prefecture_name, -- 都道府県＋市区町村名の列を追加 CASE WHEN region_category = '市区町村' THEN prefecture_name || region_name ELSE region_name -- 都道府県の場合は都道府県名のみ END as full_region_name, -- 地方ブロック分類（ビジネスロジック） CASE WHEN region_code IN (1) THEN '北海道' WHEN region_code IN (2,3,4,5,6,7) THEN '東北' WHEN region_code IN (8,9,10,11,12,13,14) THEN '関東' WHEN region_code IN (15,16,17,18,19,20) THEN '中部' WHEN region_code IN (21,22,23,24) THEN '東海' WHEN region_code IN (25,26,27,28,29,30) THEN '関西' WHEN region_code IN (31,32,33,34,35) THEN '中国' WHEN region_code IN (36,37,38,39) THEN '四国' WHEN region_code IN (40,41,42,43,44,45,46,47) THEN '九州・沖縄' ELSE 'その他' END as region_block, CURRENT_TIMESTAMP() as created_at, -- 重複排除用：region_id（region_code + region_category）ごとに番号を付与 ROW_NUMBER() OVER (PARTITION BY region_code, region_category ORDER BY region_name) as row_num FROM combined_regions ), deduplicated_regions AS ( -- 重複を排除：各region_idの最初の1件のみを取得 SELECT region_id, region_code, region_name, region_category, prefecture_code, prefecture_name, full_region_name, region_block, created_at FROM enriched_regions WHERE row_num = 1 ) SELECT region_id, region_code, region_name, region_category, prefecture_code, prefecture_name, full_region_name, region_block, created_at FROM deduplicated_regions ORDER BY region_code, region_category
5.4.3 ファクトテーブル：観光来訪者数
次にファクトテーブルとして、観光来訪者数テーブルを作成します。 これまでのデータをJOINして非正規化することで、TableauなどのBIツールから直接利用しやすいデータマートを構築できます。具体的には「BIツールでの計算を削減し、クエリパフォーマンスが向上」「よく使われる項目を事前に結合しておくことで、分析者の手間を削減」といったメリットがあります。
📁 ファイル: models/mart/fact_tourism_monthly.sql
{{ config(
materialized='table',
schema='mart'
) }}
WITH tourism_facts AS (
-- 市区町村データ
SELECT
visit_date as date_key,
region_code,
visit_year,
visit_month,
region_category,
data_category,
visitor_count,
source_file,
processed_at
FROM {{ ref('stg_city_tourism_from_seeds') }}
WHERE data_quality_flag = 'valid'
UNION ALL
-- 都道府県データ
SELECT
visit_date as date_key,
region_code,
visit_year,
visit_month,
region_category,
data_category,
visitor_count,
source_file,
processed_at
FROM {{ ref('stg_pref_tourism_from_seeds') }}
WHERE data_quality_flag = 'valid'
)
SELECT
tf.date_key,
tf.region_code,
tf.visit_year,
tf.visit_month,
tf.region_category,
tf.data_category,
tf.visitor_count,
-- 地域情報との結合（非正規化して高速化）
dr.region_name,
dr.prefecture_name,
dr.full_region_name,
dr.region_block,
-- 日付情報との結合（dim_datesを参照）
dd.year as dim_year,
dd.month as dim_month,
tf.source_file,
tf.processed_at,
CURRENT_TIMESTAMP() as mart_created_at
FROM tourism_facts tf
LEFT JOIN {{ ref('dim_regions') }} dr
ON tf.region_code = dr.region_code
AND tf.region_category = dr.region_category
LEFT JOIN {{ ref('dim_dates') }} dd
ON tf.date_key = dd.date_key
ORDER BY date_key, region_category, region_code
5.5 スキーマ定義（schema.yml）
dbtでは、schema.ymlでテーブルの構造とテストを定義します。
スキーマを定義しておくことで、「データの意味が明確になる（ドキュメントとして機能）」「テストを実行してデータ品質を保証できる」「チーム開発時の理解が容易になる」というメリットがあります。
📁 ファイル: models/mart/schema.yml
# models/mart/schema.yml version: 2 models: - name: dim_regions description: "地域マスターテーブル（都道府県・市区町村情報）" columns: - name: region_id description: "地域ID（region_code + region_category の組み合わせ）" tests: - not_null - unique - name: region_code description: "地域コード" tests: - not_null - name: region_name description: "地域名（市区町村名または都道府県名）" tests: - not_null - name: region_category description: "地域区分（都道府県/市区町村）" tests: - not_null - accepted_values: values: ['都道府県', '市区町村'] - name: prefecture_code description: "都道府県コード（都道府県の場合はNULL）" - name: prefecture_name description: "都道府県名" tests: - not_null - name: full_region_name description: "完全な地域名（都道府県名＋市区町村名、または都道府県名のみ）" tests: - not_null - name: region_block description: "地方ブロック" tests: - not_null - name: created_at description: "レコード作成日時" tests: - not_null - name: dim_dates description: "日付マスターテーブル" columns: - name: date_key description: "日付キー" tests: - not_null - unique - name: year description: "年" tests: - not_null - name: month description: "月" tests: - not_null - accepted_values: values: [1,2,3,4,5,6,7,8,9,10,11,12] - name: created_at description: "レコード作成日時" tests: - not_null - name: fact_tourism_monthly description: "観光データファクトテーブル（月次）" columns: - name: date_key description: "日付キー" tests: - not_null - name: region_code description: "地域コード" tests: - not_null - name: visit_year description: "訪問年" tests: - not_null - name: visit_month description: "訪問月" tests: - not_null - accepted_values: values: [1,2,3,4,5,6,7,8,9,10,11,12] - name: region_category description: "地域区分（都道府県/市区町村）" tests: - not_null - accepted_values: values: ['都道府県', '市区町村'] - name: data_category description: "データ区分" tests: - not_null - name: visitor_count description: "訪問者数" tests: - not_null - name: region_name description: "地域名" - name: prefecture_name description: "都道府県名" - name: region_block description: "地方ブロック" - name: source_file description: "ソースファイル名" tests: - not_null - name: processed_at description: "処理日時" tests: - not_null - name: mart_created_at description: "マート作成日時" tests: - not_null
5.6 モデルの実行
dbt Projects on Snowflake上ですべてのモデルを実行します。
実行方法:
Snowflakeのdbtプロジェクト画面で「実行」ボタンをクリック
dbtは依存関係を自動的に解決し、正しい順序でモデルを実行します。
seeds → staging → mart
実行結果の確認:
-- マート層のテーブルを確認 SHOW TABLES IN tourism_analytics.data_mart; -- ファクトテーブルのデータを確認 SELECT * FROM tourism_analytics.data_mart.fact_tourism_monthly LIMIT 10; -- 地域マスターを確認 SELECT * FROM tourism_analytics.data_mart.dim_regions LIMIT 10;
データマートの完成
6. データ品質テストの実装
⏱️ 所要時間: 約10分
🎯 このセクションのゴール:
- カスタムテストマクロを実装する
- データ品質チェックを実行する
- データリネージを確認する
dbtの強力な機能の一つがテスト機能です。データパイプラインの信頼性を高めるために、Snowflake上でdbtのテストを実行し、カスタムテストを実装します。 テストの内容をコードで定義することで、「ビジネスルールに基づいた検証ができる」「データの異常を早期に発見できる」「データパイプラインの信頼性が向上する」「データ品質を継続的に保証できる」といったメリットがあります。
今回のデータでは福島県の桧枝岐村が旧字体の檜枝岐村と同じコードで登録されているため、dim_regions.sqlのREGION_CODEで重複エラーが出ていました。 そのため重複がある場合は、各リージョンIDの最初の市区町村名のみを取得する制約を入れることができました。
6.1 カスタムテストマクロ
📁 ファイル: macros/get_tourism_data_tests.sql
-- macros/get_tourism_data_tests.sql {% macro test_valid_prefecture_codes() %} -- 都道府県コードが1〜47の範囲内かチェック select * from {{ ref('stg_city_tourism_from_seeds') }} where region_code not between 1 and 47 {% endmacro %} {% macro test_reasonable_visitor_counts() %} -- 訪問者数が妥当な範囲内かチェック select * from {{ ref('stg_city_tourism_from_seeds') }} where visitor_count > 100000000 -- 1億人超は異常値 or visitor_count < 0 {% endmacro %} {% macro test_data_completeness() %} -- データの完全性チェック（月間レコード数） select visit_year, visit_month, count(*) as record_count from {{ ref('stg_city_tourism_from_seeds') }} group by visit_year, visit_month having count(*) < 100 -- 月間100件未満は不完全データの可能性 {% endmacro %}
6.2 テストの実行
dbt Projects on Snowflake上でテストを実行します。
dbtテストの実行
データリネージの確認: 依存関係グラフで、どのテーブルがどのテーブルに依存しているかを視覚的に確認できます。これにより、データフローの全体像を把握できます。
DATA_STAGINGからDATA_MARTに含まれるデータのリネージ図
7. Tableauでの可視化
⏱️ 所要時間: 約30分
🎯 このセクションのゴール:
- Tableau Desktopをインストールする
- SnowflakeのデータマートにTableauから接続する
- 都道府県別の観光データを可視化する
- インタラクティブなダッシュボードを作成する
7.1 Tableauのインストール
今回はTableau Desktopを使用します。無料で利用可能なTableau Public Desktop EditionではSnowflakeとの接続ができないため、Tableau Desktopの14日間無料トライアル版を利用します。
💡 ヒント: Tableau Desktopではなく、Tableau Public Desktop Edition を利用する場合は、Snowflake上のデータソースをCSV等でダウンロードする必要があります。Tableau Desktopの有償版とほぼ同じ機能が利用できますが、作成したビジュアライゼーションは公開され、ローカル保存はできません。業務用途では有償版のTableau Desktopの使用を検討してください。
⚠️ Tableau Desktopトライアルの留意点:
- 期間: 14日間の無料トライアル
- メールアドレス: トライアル申込時にメールアドレスの登録が必要です
- クレジットカード: 登録時にクレジットカード情報は不要です
- 自動課金なし: トライアル終了後、自動的に有料プランに移行することはありません
- 機能制限なし: トライアル版でも製品版と同じ全機能を利用できます
インストール手順:
- Tableau Desktop 公式サイトにアクセス
- 登録フォームに以下の情報を入力:
- メールアドレス
- 名前
- 国/地域
- 「無料トライアル版をダウンロード」ボタンをクリックしてインストーラーをダウンロード
- ダウンロードした実行ファイル（
.exe）をダブルクリックしてインストールを開始 - インストールウィザードに従って進め、完了後にTableau Desktopを起動
📝 補足: インストールには数分かかる場合があります。
7.2 Snowflakeへの接続
Tableau DesktopからSnowflakeに接続し、作成したデータマートを利用します。
手順:
- Tableau Desktopを起動
- 左側のメニューから「サーバーへ」→「Snowflake」を選択
- Snowflake用のドライバーのダウンロードが求められるため、利用しているOSに合わせてドライバーをインストール（ドライバーインストール後Tableauを再起動し、2を再度実施）
- Snowflake接続情報を入力:
- サーバー：
<アカウント識別子>.snowflakecomputing.com- 例：
abc12345.ap-northeast-1.aws.snowflakecomputing.com - アカウント識別子はSnowflakeのアカウント詳細から確認できます（後述）
- 例：
- ウェアハウス：
dbt_wh（大文字小文字は区別されません） - データベース：
tourism_analytics - スキーマ：
data_mart - ユーザー名：Snowflakeのユーザー名
- パスワード：Snowflakeのパスワード
- サーバー：
- 「サインイン」ボタンをクリック
⚠️ 注意: サーバー名には
<アカウント識別子>.snowflakecomputing.comの形式を使用してください。https://や末尾の/は不要です。
ファイルから新規作成を行い、データソースを選択
接続先としてSnowflakeに接続
Snowflakeのログインに必要な情報を入力
サーバーのURLが不明な場合はアカウント詳細を確認
アカウント詳細の例
7.3 データソースの設定
FACT_TOURISM_MONTHLYテーブルをキャンバスにドラッグ
ドラッグし、更新するとデータが表示
7.4 観光データを利用したワークシート（グラフなど）の作成
作成したデータマートを活用して、観光来訪者数の傾向を把握するためのビジュアライゼーションを作成します。以下では、都道府県別の観光者数の比較、月次の変化パターン、地理的な分布を視覚化します。
7.4.1 例1：都道府県別観光者数
- 新しいワークシートを作成
- 列に「合計(Visitor Count)」をドラッグ
- 行に「Region Block」（地方ブロック）、「Prefecture Name」(都道府県名)をドラッグ
- フィルターに「Date Key」（年）を追加し2025年など特定の年を選択
表示をしてみると、Region Blockかその他のものが多数含まれていることが分かります。 これは市区町村側ごとのデータマートもまとめて同じデータマートに含まれており、都道府県名でグループ化するとダブルカウントされています。
Region Block内で、その他のデータが含まれていることが分かる
そのため、その他を右クリックし除外することで重複を排除します。 また、フィルター内の「Date Key」（年）を右クリックして、フィルターを表示しておくことでグラフに表示する年を選べるようにしておきます。
その他の除外とフィルターの表示
今回「Region Block」（地方ブロック）ごとではなく、都道府県全体でデータを降順に並べたいため、「Region Block」（地方ブロック）は削除し、降順に並べておきます。 また、ラベルに「合計(Visitor Count)」をドラッグしておくことで棒グラフ横でも数値が見えるようにしておきます。
都道府県別観光者数の棒グラフ表示
7.4.2 例2：都道府県別ヒートマップ
- 新しいワークシートを作成
- 列に「Prefecture Name」（都道府県名）をドラッグ
- 行に「Visit Month」（月）をドラッグ
- 表示形式をヒートマップに変更
先ほどと同様に都道府県別の訪問者数グラフを作成（Region Blockでその他を除外）
表示形式をヒートマップに変更
VisitCountをラベルにドラッグし、訪問者数を表示＆フィルタとして年(DataKey)を追加
7.4.3 例3：地図表示
Tableauは地図表示にも優れており、都道府県名を元に訪問者数を簡単に地図表示で表現することも可能です。
- 新しいワークシートを作成
- 「Prefecture Name」（都道府県名）を右クリックし、「地理的役割」から「都道府県」を選択
- 「Prefecture Name」（都道府県名）を真ん中のフィールドへドラッグ
- マークの形をマップへ変更
- マークカードに「Visitor Count」を色として追加
Prefecture Nameを右クリックし「地理的役割」から「都道府県」を選択
Prefecture Nameを真ん中にドラッグし、マークの形をマップへ変更
Region Blockでその他のデータをフィルタで除外する
7.5 ダッシュボードの作成
複数のワークシートを組み合わせてダッシュボードを作成します。
- 「ダッシュボード」→「新しいダッシュボード」
- 作成したワークシート「都道府県別観光者数」「都道府県別観光者数（マップ）」をドラッグ&ドロップ
- 「都道府県別観光者数（マップ）」を浮動に変更し、タイトル表示を非表示にする
- 「DateKey(年)」フィルタを選択し、適応先をマップにも反映させる
ダッシュボードを新規作成し、シートをドラッグ＆ドロップ
マップを浮動形式に変更し、タイトルを非表示に変更
DateKey（年）フィルタの適応先を変更
マップにもフィルタを反映させる
地図上での数値と棒グラフの数値が連動（フィルタが両方のグラフに反映）
8. まとめ
今回は dbt、Snowflake、Tableau を活用し、観光オープンデータの分析基盤を構築しました。 チームなど複数人でデータ分析を行う場合、今回のようにデータパイプラインを整備し、処理内容が他の人にも分かりやすく、再利用可能な状態にしておくことは非常に重要だと考えています。 まだ完全自動化に向けて改善の余地が多く残されているため、今後も継続的に改善を進めていきたいと思います。
実現できたこと
データ加工の透明性
- SQLでバージョン管理されたデータパイプライン
- いつでも過去の変更を追跡可能
データ品質の保証
- 自動テストによる異常検知
スケーラブルな基盤
- データ量が増えても対応可能
- 新しい月のデータ追加が容易（seedを追加するだけ）
分析の民主化
- BI ツールで誰でも分析可能
- SQL を書かなくても可視化できる
今後の改善案
- パッケージの実装：現状、月ごとにファイルが増えるたびにコードの書き換えが必要なため、dbt-labs/dbt_utilsなどdbt処理を便利にするパッケージの実装を検討
- CI/CDパイプライン：GitHub Actions等で自動テスト・デプロイ
- アラート機能：異常値検知時にSlack通知
- パフォーマンス最適化：クラスタリングキーやマテリアライズドビューの活用
参考リンク
モダンなデータ分析基盤の構築に、ぜひこの技術スタックを試してみてください！
執筆者
谷 清隆（NTT西日本 ビジネス営業本部 エンタープライズビジネス営業部所属）
データエンジニアとして、民間企業や自治体のデータ利活用戦略の構想策定から基盤構築まで幅広く担当しています。
商標について
- 「Snowflake」は、Snowflake Inc.の米国およびその他の国における商標または登録商標です。
- 「Tableau」は、Salesforce, Inc.の米国およびその他の国における商標または登録商標です。
- 「dbt」は、dbt Labs, Inc.の商標または登録商標です。
- 「Amazon Web Services」、「AWS」は、Amazon.com, Inc.またはその関連会社の米国およびその他の国における商標または登録商標です。
- その他、本記事に記載されている会社名、製品名、サービス名は、各社の商標または登録商標です。
免責事項
- 本記事の内容は執筆時点（2025年12月）の情報に基づいており、サービスの仕様変更等により内容が変更される可能性があります。
- 本記事で紹介する設定手順や使用方法は、特定の環境での動作確認に基づくものであり、すべての環境での動作を保証するものではありません。
- Snowflakeの無料トライアルには利用期限・無料クレジット枠がありますが、クレジットカードなどの支払い設定を行っている場合、期限後は課金が発生する可能性があります。利用規約をご確認の上、ご使用ください。
- 本記事で使用している日本観光振興会のオープンデータは、公開時点のものであり、データの正確性や完全性について保証するものではありません。
