こんにちは。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. 使用する技術の概要 dbt(data build tool)とは データモデリング時の課題とdbtによる解決 Snowflakeとは Tableauとは 2. 全体アーキテクチャ データモデルの設計思想 3. 環境構築 3.1 Snowflakeアカウントの準備 3.2 Snowflake上でデータベースとスキーマを作成 3.3 dbt Projects on Snowflakeの有効化 3.3.1 dbt Projects on Snowflakeのセットアップ手順 3.3.2 dbt_project.yml設定 4. データの準備とアップロード 4.1 観光データのダウンロード 4.2 Snowflakeへのデータアップロード 4.2.1 dbt Projects on Snowflakeでのseedアップロード 4.2.2 dbtコマンドを実行してseedをロード 5. dbtプロジェクトの実装 5.1 プロジェクト構成 5.2 マクロの実装:複数テーブルの統合 5.3 ステージング層の実装 5.4 マート層の実装 5.4.1 ディメンションテーブル1:日付マスター 5.4.2 ディメンションテーブル2:地域マスター 5.4.3 ファクトテーブル:観光来訪者数 5.5 スキーマ定義(schema.yml) 5.6 モデルの実行 6. データ品質テストの実装 6.1 カスタムテストマクロ 6.2 テストの実行 7. Tableauでの可視化 7.1 Tableauのインストール 7.2 Snowflakeへの接続 7.3 データソースの設定 7.4 観光データを利用したワークシート(グラフなど)の作成 7.4.1 例1:都道府県別観光者数 7.4.2 例2:都道府県別ヒートマップ 7.4.3 例3:地図表示 7.5 ダッシュボードの作成 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/ ) モデルごとの動作設定 マテリアライゼーション方法(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.csv pref_202302.csv (ダウンロードした月数分) seeds/city/ 配下 city_202301.csv city_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通知 パフォーマンス最適化 :クラスタリングキーやマテリアライズドビューの活用 参考リンク dbt公式ドキュメント Snowflake公式ドキュメント 日本観光振興会 観光統計 モダンなデータ分析基盤の構築に、ぜひこの技術スタックを試してみてください! 執筆者 谷 清隆 (NTT西日本 ビジネス営業本部 エンタープライズビジネス営業部所属) データエンジニアとして、民間企業や自治体のデータ利活用戦略の構想策定から基盤構築まで幅広く担当しています。 商標について 「Snowflake」は、Snowflake Inc.の米国およびその他の国における商標または登録商標です。 「Tableau」は、Salesforce, Inc.の米国およびその他の国における商標または登録商標です。 「dbt」は、dbt Labs, Inc.の商標または登録商標です。 「Amazon Web Services」、「AWS」は、Amazon.com, Inc.またはその関連会社の米国およびその他の国における商標または登録商標です。 その他、本記事に記載されている会社名、製品名、サービス名は、各社の商標または登録商標です。 免責事項 本記事の内容は執筆時点(2025年12月)の情報に基づいており、サービスの仕様変更等により内容が変更される可能性があります。 本記事で紹介する設定手順や使用方法は、特定の環境での動作確認に基づくものであり、すべての環境での動作を保証するものではありません。 Snowflakeの無料トライアルには利用期限・無料クレジット枠がありますが、クレジットカードなどの支払い設定を行っている場合、期限後は課金が発生する可能性があります。利用規約をご確認の上、ご使用ください。 本記事で使用している日本観光振興会のオープンデータは、公開時点のものであり、データの正確性や完全性について保証するものではありません。