小売業のためのdbtプロジェクト構成を考えてみた
小売業界は膨大な量の取引データを日々生成しており、このデータを効率的に分析し、ビジネスの意思決定に活用することが重要です。dbt (data build tool) は、データモデリングプロセスを効率化し、データの変換を容易にするツールです。本記事では、dbt公式ドキュメントを参考に小売業のためのdbtプロジェクトの構成について解説します。
はじめに
小売業界は膨大な量の取引データを日々生成しており、このデータを効率的に分析し、ビジネスの意思決定に活用することが重要です。dbt (data build tool) は、データモデリングプロセスを効率化し、データの変換を容易にするツールです。本記事では、dbt公式ドキュメントを参考に小売業のためのdbtプロジェクトの構成について解説します。
プロジェクトの全体構造
まずプロジェクトの全体像として、小売業に特化したdbtプロジェクト「supermarket_retail」を作成し、以下のようなディレクトリとファイル構造を持たせます。
supermarket_retail
├── README.md # プロジェクトの概要や使い方が記載されたドキュメント
├── analyses # 複雑な分析クエリを保存するディレクトリ
├── seeds
│ └── products.csv # 静的なデータセット（例：商品リスト）
├── dbt_project.yml # DBTプロジェクトの設定ファイル
├── macros
│ └── yen_tax.sql # SQLマクロ（例：税込み価格を計算するマクロ）
├── models # データモデリングのSQLファイルを格納するディレクトリ
│ ├── intermediate
│ │ └── finance
│ │ ├── _int_finance__models.yml # 中間層ファイナンスモデルの設定ファイル
│ │ └── int_retail_pivoted_to_orders.sql # 注文データのピボット処理SQL
│ ├── marts
│ │ ├── finance
│ │ │ ├── _finance__models.yml # ファイナンスマートの設定ファイル
│ │ │ ├── orders.sql # 注文データ集計SQL
│ │ │ └── payments.sql # 支払いデータ集計SQL
│ │ └── marketing
│ │ ├── _marketing__models.yml # マーケティングマートの設定ファイル
│ │ └── customers.sql # 顧客データ集計SQL
│ ├── staging
│ │ ├── supermarket
│ │ │ ├── _supermarket__docs.md # スーパーマーケットデータのドキュメント
│ │ │ ├── _supermarket__models.yml # ステージング層の設定ファイル
│ │ │ ├── _supermarket__sources.yml # スーパーマーケットデータソース設定ファイル
│ │ │ ├── base
│ │ │ │ ├── base_supermarket__customers.sql # 基本顧客データ加工SQL
│ │ │ │ └── base_supermarket__deleted_customers.sql # 削除済み顧客データ処理
│ │ │ ├── stg_supermarket__customers.sql # 顧客データステージングSQL
│ │ │ └── stg_supermarket__orders.sql # 注文データステージングSQL
│ │ └── stripe
│ │ ├── _stripe__models.yml # Stripeデータモデル設定ファイル
│ │ ├── _stripe__sources.yml # Stripeデータソース設定ファイル
│ │ └── stg_stripe__payments.sql # Stripe支払いデータステージングSQL
│ └── utilities
│ └── all_dates.sql # 汎用ユーティリティ（例：全日付テーブル生成SQL）
├── packages.yml # DBTパッケージ依存関係ファイル
├── snapshots # データのスナップショットを格納するディレクトリ
└── tests
└── assert_positive_value_for_total_amount.sql # テストSQL（例：合計金額が正の値であることを確認）モデルの詳細とSQLサンプル
プロジェクトのmodelsディレクトリは、データの変換と集計を行うための核心部分です。各サブディレクトリ（staging、intermediate、marts）は特定の役割を持ち、異なるタイプのデータ変換を担います。
ステージング層: stg_supermarket__customers
ステージング層は、生データを初期処理し、分析のための基礎を作ります。例えば、stg_supermarket__customers では以下のように顧客データを加工します。
「_int_finance__models.yml」
version: 2
models:
- name: int_retail_pivoted_to_orders
description: "注文データを製品ごとにピボットした中間層モデル"
columns:
- name: order_id
description: "注文の一意識別子"
- name: product_A_sales
description: "製品Aの売上"
- name: product_B_sales
description: "製品Bの売上"
- name: product_C_sales
description: "製品Cの売上"
「stg_supermarket__customers.sql」
SELECT
customer_id,
first_name,
last_name,
email,
created_at
FROM raw_customers_data
このモデルでは、スーパーマーケットの生の顧客データから必要な列を選択し、ステージングテーブルで利用するために整形しています。customer_id、first_name、last_name、email、created_at などの重要な顧客情報が含まれています。
中間層: int_retail_pivoted_to_orders
中間層では、ステージング層のデータをさらに結合し、ビジネスの複雑な要件に対応するための加工を行います。
「_int_finance__models.yml」
version: 2
models:
- name: int_retail_pivoted_to_orders
description: "注文データを製品ごとにピボットした中間層モデル"
columns:
- name: order_id
description: "注文の一意識別子"
- name: product_A_sales
description: "製品Aの売上"
- name: product_B_sales
description: "製品Bの売上"
- name: product_C_sales
description: "製品Cの売上"
「int_retail_pivoted_to_orders.sql」
SELECT
order_id,
SUM(CASE WHEN product_id = 'A' THEN quantity ELSE 0 END) AS product_A_sales,
SUM(CASE WHEN product_id = 'B' THEN quantity ELSE 0 END) AS product_B_sales,
...
FROM staged_orders_data
GROUP BY order_id
このSQLでは、注文データを製品ごとにピボットしています。各注文に対して、製品A、製品B、製品Cなどの売上を集計しています。
データマート層: orders
データマート層は、最終的なビジネスインテリジェンスやレポーティングのためのデータ集計を行います。例えば、orders では以下のように注文データを集計します。
「_finance__models.yml」
version: 2
models:
- name: orders
description: "スーパーマーケットの注文データを集計したデータマート"
columns:
- name: order_id
description: "注文の一意識別子"
- name: order_date
description: "注文日"
- name: customer_id
description: "注文を行った顧客のID"
- name: total_order_amount
description: "注文の合計金額"
「orders.sql」
SELECT
o.order_id,
o.order_date,
c.customer_id,
SUM(o.total_amount) AS total_order_amount
FROM stg_supermarket__orders AS o
JOIN stg_supermarket__customers AS c ON o.customer_id = c.customer_id
GROUP BY o.order_id, o.order_date, c.customer_idこのデータマートモデルでは、注文データと顧客データを結合し、注文ごとの合計金額と顧客情報を提供します。
まとめ
今回は、supermarket_retail プロジェクトとして、小売業のデータモデリングに特化したdbtプロジェクトを構成を考えてみました。ステージング、中間、マートの各層を分割してデータを効率的に変換することで、データの品質とトレーサビリティーを確保し、ビジネスの分析ニーズに応えることができます。
