APEP

Go・PostgreSQL・Metabaseで、データレイクからBIまでの流れを作ってみた

Takao

データレイク、データウェアハウス（DWH）、データマート。それぞれの役割を説明する文章を読んでも、「同じデータを何度も保存するのはなぜだろう」「どの段階で分析用の数字になるのだろう」と感じることがあります。

そこで、架空のECを題材に、業務DBからBIまでをローカルで動かせる学習用プロジェクトを作りました。Goで処理を実行し、PostgreSQLでデータを整理・集計し、学習用のWeb画面とMetabaseで結果を確認します。

この記事では、まず業務DB・データレイク・分析用DBを分ける理由と、分析用DBの中にあるraw・DWH・マートの関係を整理します。そのうえで各段階の実装を紹介し、返品を1件追加すると売上の表示がどう変わるかを追っていきます。

ソースコードは analytics-playground にあります。

できあがった画面

最初に、マートのデータから作ったレポートを載せます。

データの流れと3つのグラフをまとめたEC売上レポート

学習用Web画面のレポートです。この画像はテスト用データで生成したため、後述する返品の実験とは金額が異なります。

業務DB・データレイク・分析用DBを分ける理由

まず、今回の構成を「業務を処理する場所」「抽出したデータを残す場所」「分析のために加工する場所」の3つに分けて考えます。

保存先分ける目的
業務DB注文の登録や返品処理など、サービスの日々の処理を行う。
データレイク抽出したデータを残し、後から加工・再処理する材料を保管する。
分析用DB業務DBと処理負荷を分け、集計しやすい形でデータを持つ。

「業務用」「分析用」はDBの用途を表します。どちらにもPostgreSQLを使えます。一方、データレイクはさまざまなデータを蓄積し、後から利用するための保管・利用の構成を指します。同じ注文がそれぞれに保存されても、持つ目的が異なります。

小規模な分析なら業務DBで直接集計できます。業務への負荷、元データの保管、加工方法の変更などの要件に応じて、保存先や処理を分けます。この記事では、その役割の違いを観察するために3つを用意しました。

分析用DBの中にraw・DWH・マートを作る

この記事でいう分析用DBは、PostgreSQLの analytics データベース全体です。その中に、レイクから取り込んだJSONを保持するraw、分析用の明細を持つデータウェアハウス（DWH）、用途別のデータを持つデータマートを作ります。今回はマートを日別売上などの集計テーブルとして実装しました。

DWHやマートも、分析用のデータを持つ役割を担います。この学習環境では同じDB内のスキーマで分けていますが、規模や要件に応じて別のDBやサービスに分ける構成も取れます。

業務DB（PostgreSQL・source）
注文・返品などを登録する

↓ 全件を抽出

データレイク（/lake）
抽出時点のデータを、バッチごとのJSONで残す

↓ 読み込み

分析用DB（PostgreSQL・analytics）
raw
レイクから取り込んだJSONを保持する

↓ 型の変換・テーブルの結合

DWH
分析に使う明細を揃える

↓ 日別・商品別・月別に集計

マート
用途別の指標を計算する

↓ 参照

学習用レポート / Metabase
数字を可視化する

同じ注文データを追うと、保存する場所だけでなく、データの形が変わることも分かります。

段階注文データの形
業務DB注文と注文明細を別テーブルで管理する。
レイク抽出時点の各テーブルをJSONファイルで残す。
分析用DBrawレイクから取り込んだJSONを、DWH向けに整形する前の状態で保持する。
DWH注文と注文明細を結合し、顧客・商品・数量・単価・決済日時を持つ明細にする。
マート明細を集計して、日別売上などを作る。

レイクのJSONはバッチごとの抽出結果、rawはSQLで変換するために取り込んだデータです。今回、レイクには過去バッチも残しますが、rawは取り込み処理で更新します。DWHは複数の分析に使える明細を揃え、マートはその明細から用途別の数字を作ります。

今回の保存先

役割このプロジェクトでの保存先
業務DBsource データベース
データレイクDockerボリューム内の /lake
分析用DBのrawanalytics DBの raw.records
分析用DBのDWHanalytics DBの dwh スキーマ
分析用DBのマートanalytics DBの mart スキーマ
BI学習用Web画面とMetabase

今回「データレイク」として扱っているのは、抽出データを残すローカルのJSON保存領域です。クラウドではS3などのオブジェクトストレージを使う構成もあります。ここでは、まず保存と変換の役割を分けて観察するために、小さなローカル構成にしました。

レイクは加工済みデータも置けるため、「レイク＝未加工データだけ」とは限りません。この記事では、抽出した元データの保管に役割を絞っています。また、過去バッチを指定した再処理は、このサンプルでは未実装です。

データレイクでは、抽出した時点のデータを残す

業務DBには、顧客・商品・注文・注文明細・返品の5種類のテーブルがあります。抽出処理では、これらをJSONファイルにして保存します。

保存先はバッチごとのディレクトリです。

/lake/
└── <バッチID>/
    ├── customers.json
    ├── products.json
    ├── orders.json
    ├── order_items.json
    ├── returns.json
    └── manifest.json

manifest.json にはバッチIDと抽出日時を記録します。次の抽出では新しいディレクトリを作るため、前回のファイルは上書きしません。

業務DBの値は更新されますが、保存済みのJSONは抽出時点の状態を保持します。たとえば返品を追加しても、抽出を実行するまではレイクの最新バッチにその返品は入りません。

複数テーブルの抽出には、1つの REPEATABLE READ の読み取り専用トランザクションを使っています。注文と注文明細をそれぞれ異なるタイミングの状態から読むことを避け、同じスナップショットから取得するためです。

rawでレイクから取り込んだJSONを保持し、DWHで型と明細を揃える

レイクから読み込んだデータは、まず raw.records に保存します。元のテーブル名、バッチID、JSONB形式のデータを持たせています。

DWHでは、JSON内の値を数値や日時に変換し、次のテーブルを作ります。

  • dwh.dim_customers：顧客
  • dwh.dim_products：商品
  • dwh.fact_order_items：注文明細
  • dwh.fact_returns：返品明細

注文明細を作る際は、rawの注文と注文明細を注文IDで結合します。商品ID・数量・単価に加え、注文側にある顧客IDと決済日時も持つ明細にします。

この段階では、まだ「1日分の売上」にはまとめません。明細を残すことで、次の段階で日別、商品別、顧客別といった集計に使えます。

このように、rawでは元のJSONを扱い、DWHでは型と項目を揃えた明細を扱います。BIで知りたい数字への集計は、次のマートで行います。

データマートで、知りたい数字にまとめる

テーブルデータの粒度用途
mart.daily_sales1日1行売上・返品額・純売上の推移
mart.daily_product_sales1日・1商品につき1行商品ごとの販売数量と売上
mart.monthly_customer_activity1か月1行購入顧客数と月内リピート率

たとえば日別売上なら、BI側は注文明細と返品明細を毎回結合する必要がありません。マートを読むだけでグラフを作れます。

SELECT day, gross_sales, returns, net_sales
FROM mart.daily_sales
ORDER BY day;

ただし、集計する前に「売上をどの日に数えるか」「返品をどう扱うか」を決める必要があります。今回のルールは次のとおりです。

  • 売上は決済日に計上する。
  • 返品額は返品完了日に計上する。
  • 日付は日本時間で区切る。
  • 純売上は、その日の売上からその日の返品額を引く。

日付の変換には、SQL内で AT TIME ZONE 'Asia/Tokyo' を使っています。なお、この純売上は学習用に定義した指標で、送料や税、値引きなどを網羅した会計上の指標ではありません。

返品を元の購入日に戻して差し引く設計も考えられますが、今回は返品が発生した日の動きを見るため、このルールにしました。売上がなく返品だけある日は、純売上がマイナスになります。

商品別マートの売上は返品控除前です。日別の純売上と商品別の売上を比較するときは、この定義の違いを意識する必要があります。

返品を1件追加して、数字が変わるまでを追う

日付内容売上返品額純売上
2026-10-01コーヒーを500円で2個購入1,000円0円1,000円
2026-10-02マグカップを1,200円で1個購入1,200円0円1,200円
2026-10-03コーヒーを1個返品0円500円-500円

初期データだけなら、全期間の売上は2,200円、返品額は500円、純売上は1,700円です。

ここに、注文2のマグカップの返品を追加します。返品ボタンは、実行時点の日時で1,200円分の返品を業務DBに登録します。同じ明細への返品は重複追加しません。

この操作だけでは、レポートの数字は変わりません。レポートが読んでいるのはマートだからです。

操作変わる場所返品データの形マートの返品額
初期データを最後まで処理全レイヤーに初期状態を反映マグカップの返品は未登録500円
注文2の返品を追加業務DB1,200円の返品レコード500円
抽出を実行レイクの新しいバッチreturns.json 内の1件500円
rawを更新raw元テーブル名とバッチIDが付いたJSONB500円
DWHを更新DWHの返品明細返品額と完了日時を型付きの列にした明細500円
マートを更新日別売上返品完了日の日別集計に1,200円を計上1,700円

最後まで処理すると、全期間の純売上は1,700円から500円になります。売上そのものは2,200円のままです。

この変化は、学習画面の「比較の基準を保存」でも確認できます。基準を保存してから返品を追加し、各段階を実行すると、レポート下部に返品額と純売上の変更前後が表示されます。

Analytics Playgroundの返品比較。返品額は500円から1,700円に増え、純売上は1,700円から500円に減った。
初期データにマグカップの返品を1件追加した後のレポート。下部に返品額と純売上の変化を表示しています。

この金額を再現するには、サンプルデータを追加する前に試してください。

全件抽出と差分更新を分けて考える

画面には「rawを全件更新」と「rawを差分更新」の2つがあります。

全件更新はrawを入れ直します。差分更新は、最新のスナップショットと既存rawをテーブル名・IDで比較し、追加・変更・削除を反映します。内容が変わらない行は、以前のバッチIDを保持します。

ここでの差分更新は、レイクからrawへの取り込み方の違いです。業務DBからレイクへの抽出は、どちらの場合も全件です。業務DBの変更だけを取得するCDCは実装していません。

DWHの更新は全件再構築です。マートは全件の再集計に加え、期間指定にも対応しています。月別マートでは、指定した日付を含む月全体を再集計します。たとえば10月10日から15日を指定しても、月別リピート率は10月全体の注文を使って計算します。

同じデータで再実行しても、マートの集計は二重計上されません。

グラフは、指標の定義と一緒に読む

レポートには、日別売上・商品別売上TOP 10・月別リピート率を表示しています。商品別は商品IDごとに全期間の売上を合計し、上位10件を表示します。

月別リピート率は、次の式です。

その月に2回以上注文した顧客数 ÷ その月に注文した顧客数

注文回数は注文明細の件数ではなく、重複を除いた注文IDの件数で数えます。1回の注文で複数の商品を買っても、それだけではリピートになりません。

この指標は、翌月に戻ってきた顧客の割合や、初回購入からの継続率とは異なります。また、進行中の月はまだ期間が短いため、確定した月と単純に比較できません。

大量のサンプルデータは規則的に生成した架空データです。画像中に高いリピート率が見えても、実際のECの傾向を示すものではありません。今回は「どの明細から、どんな定義で指標を計算するか」を理解するために使っています。

手元で動かす

Docker Desktopを起動し、リポジトリを取得して実行します。

git clone https://github.com/taako-502/analytics-playground.git
cd analytics-playground
docker compose up --build -d

学習画面は http://localhost:8080 です。最初は「すべて実行」を押すと、小さな初期データがマートまで流れます。

返品の実験をするなら、この段階で基準を保存し、返品追加と各段階の実行を試すと、手計算と比較できます。操作途中はページ自体を再読み込みせず、画面内の操作で進めます。比較基準はページを開いている間だけ保持されます。

グラフに十分な量のデータを表示したくなったら、「サンプルデータを追加」→「すべて実行」を押します。顧客1,000人、商品20種類、約1万件の注文を扱えます。追加分の日付は実行日を基準にしているため、画像の期間は実行時期によって変わります。

「記事用表示」を押すと操作パネルを隠せます。「グラフをPNG保存」では、構成図・指標・グラフをまとめた2400 × 2080ピクセルの画像を保存できます。

Metabaseから同じマートを読む

http://localhost:3000 を開いて管理者アカウントを作成し、PostgreSQLの接続を追加します。

項目設定値
ホストanalytics
ポート5432
データベース名analytics
ユーザー名postgres
パスワードplayground

これは、このリポジトリのローカル学習用設定です。MetabaseもDocker内で動くため、DBのホストにはComposeのサービス名である analytics を指定します。

Go 1.27以降があるmacOS/Linux環境では、リポジトリのルートから次のコマンドを実行すると、3つのグラフを持つダッシュボードを作成できます。

go run ./cmd/metabase-dashboard

入力するメールアドレスとパスワードは、Metabaseの初期設定で作成した管理者アカウントのものです。DB接続用パスワードとは別です。続いて、表示された一覧から分析用DBのIDを入力します。

完了時に表示されるURLを開くと、3つのグラフを確認できます。

MetabaseのEC売上分析ダッシュボード。上段に日別売上と返品、下段に商品別売上TOP 10と月別リピート率を表示。
返品を1件追加した後のマートをMetabaseで表示した画面。

コマンドを再実行するたびに、新しいダッシュボードが作られます。

この構成で扱う範囲

この構成はローカルで流れを理解するためのものです。業務DBからの差分抽出、過去バッチを指定した再処理、大量データ向けの取り込み最適化、学習用Web画面の認証などは未実装です。処理も画面から手動実行するため、定期実行や運用監視を含む本番用の基盤ではありません。

作ってみて整理できたこと

返品を追加してもグラフがすぐ変わらない動作は、この関係を確かめる手がかりになりました。数字が変わらないときは、業務DBへの登録、抽出、取り込み、変換、集計のどこまで進んだかを追えます。

グラフの見た目を整える前に、「どのデータを、いつ、何のルールで集計した数字なのか」を説明できること。そのための練習環境として、小さなECのデータを自分で流してみる形にしました。

, ,

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です