🤖

dbt + Docker + PostgreSQL で靴店舗データ分析基盤を構築する

に公開
PostgreSQL
Streamlit
dbt
Claude Code
tech

はじめに

本記事では、dbt（data build tool）を使用して靴店舗のデータ分析基盤を構築する方法を解説します。Docker環境でPostgreSQLを立ち上げ、dbtでデータ変換パイプラインを構築し、Streamlitでダッシュボードを作成するまでの一連の流れを紹介します。

このプロジェクトを通じて、以下のことが学べます：

  • Dockerを使用したPostgreSQL環境の構築
  • dbtプロジェクトの基本的な構成と設定
  • レイヤー構造（staging/intermediate/marts）によるデータモデリング
  • dbtのテスト機能を活用したデータ品質管理
  • Streamlitによるインタラクティブなダッシュボード構築

環境構成

プロジェクトのフォルダ構成は以下の通りです：

dbt_test/
├── docker-compose.yml          # Docker構成ファイル
├── Dockerfile                  # dbtコンテナ用
├── .env                        # 環境変数
├── Makefile                    # 便利コマンド集
│
├── postgres/
│   └── init/
│       └── 01_init.sql         # DB初期化（スキーマ作成）
│
├── dbt_project/                # dbtプロジェクト本体
│   ├── dbt_project.yml         # dbtプロジェクト設定
│   ├── profiles.yml            # 接続設定
│   ├── packages.yml            # dbt_utils等のパッケージ
│   ├── seeds/                  # シードデータ（CSV）
│   │   ├── seed_product_categories.csv
│   │   ├── seed_products.csv
│   │   ├── seed_customers.csv
│   │   ├── seed_orders.csv
│   │   └── seed_order_items.csv
│   ├── models/
│   │   ├── staging/            # ステージングレイヤー
│   │   │   ├── _staging.yml
│   │   │   ├── stg_product_categories.sql
│   │   │   ├── stg_products.sql
│   │   │   ├── stg_customers.sql
│   │   │   ├── stg_orders.sql
│   │   │   └── stg_order_items.sql
│   │   ├── intermediate/       # 中間レイヤー
│   │   │   ├── int_orders_enriched.sql
│   │   │   └── int_order_items_enriched.sql
│   │   └── marts/              # マートレイヤー
│   │       ├── _marts.yml
│   │       ├── dim_customers.sql
│   │       ├── dim_products.sql
│   │       ├── fct_orders.sql
│   │       └── fct_daily_sales.sql
│   └── tests/                  # カスタムテスト
│       ├── assert_order_total_positive.sql
│       └── assert_no_orphan_order_items.sql
│
├── streamlit/                  # Streamlitアプリ
│   ├── requirements.txt
│   └── app.py
│
├── e2e/                        # Playwright E2Eテスト
│   ├── package.json
│   ├── playwright.config.ts
│   ├── tests/
│   │   └── streamlit.spec.ts
│   └── screenshots/            # スクリーンショット保存先
│
└── zenn/                       # Zenn記事
    └── articles/

Docker環境構築

docker-compose.yml

PostgreSQL、dbtコンテナ、Streamlitの3つのサービスを定義します。

docker-compose.yml
version: '3.8'

services:
  postgres:
    image: postgres:15-alpine
    container_name: dbt_postgres
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./postgres/init:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - dbt_network

  dbt:
    build:
      context: .
      dockerfile: Dockerfile
    container_name: dbt_runner
    working_dir: /dbt_project
    volumes:
      - ./dbt_project:/dbt_project
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_HOST: ${POSTGRES_HOST}
      POSTGRES_PORT: ${POSTGRES_PORT}
    depends_on:
      postgres:
        condition: service_healthy
    networks:
      - dbt_network

  streamlit:
    image: python:3.11-slim
    container_name: dbt_streamlit
    working_dir: /app
    volumes:
      - ./streamlit:/app
    ports:
      - "8501:8501"
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_HOST: ${POSTGRES_HOST}
      POSTGRES_PORT: ${POSTGRES_PORT}
    depends_on:
      postgres:
        condition: service_healthy
    networks:
      - dbt_network
    command: bash -c "pip install -r requirements.txt && streamlit run app.py --server.address=0.0.0.0"

volumes:
  postgres_data:

networks:
  dbt_network:
    driver: bridge

Dockerfile

dbt用のDockerイメージを作成します。

Dockerfile
FROM python:3.11-slim

# Install system dependencies
RUN apt-get update && apt-get install -y \
    git \
    libpq-dev \
    gcc \
    && rm -rf /var/lib/apt/lists/*

# Install dbt-core and dbt-postgres
RUN pip install --no-cache-dir \
    dbt-core==1.7.* \
    dbt-postgres==1.7.*

# Set working directory
WORKDIR /dbt_project

# Keep container running
CMD ["tail", "-f", "/dev/null"]

.env

環境変数を定義します。

.env
POSTGRES_USER=dbt_user
POSTGRES_PASSWORD=dbt_password
POSTGRES_DB=dbt_db
POSTGRES_HOST=postgres
POSTGRES_PORT=5432

Docker環境を起動するには以下のコマンドを実行します：

docker-compose up -d
docker-compose ps  # 全コンテナがhealthyであること

dbtプロジェクト設定

dbt_project.yml

モデルのレイヤー構造とスキーマ、マテリアライゼーションを設定します。

dbt_project/dbt_project.yml
name: 'shoe_store'
version: '1.0.0'
config-version: 2

profile: 'shoe_store'

model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
macro-paths: ["macros"]

models:
  shoe_store:
    staging:
      +schema: staging
      +materialized: view
    intermediate:
      +schema: intermediate
      +materialized: view
    marts:
      +schema: marts
      +materialized: table

profiles.yml

環境変数を使用してデータベースに接続します。

dbt_project/profiles.yml
shoe_store:
  target: dev
  outputs:
    dev:
      type: postgres
      host: "{{ env_var('POSTGRES_HOST') }}"
      port: "{{ env_var('POSTGRES_PORT') | int }}"
      user: "{{ env_var('POSTGRES_USER') }}"
      password: "{{ env_var('POSTGRES_PASSWORD') }}"
      dbname: "{{ env_var('POSTGRES_DB') }}"
      schema: public
      threads: 4

シードデータ

靴店舗のサンプルデータを5つのCSVファイルとして用意します。

seed_product_categories.csv（5件）

category_id,category_name,description
1,スニーカー,カジュアルなスニーカー
2,ビジネスシューズ,フォーマルなビジネスシューズ
3,ブーツ,秋冬向けブーツ
4,サンダル,夏向けサンダル
5,ランニングシューズ,スポーツ用ランニングシューズ

seed_products.csv（20件）

Nike、Adidas、New Balance、ASICS、Pumaなど人気ブランドの靴データを用意。

seed_customers.csv（15件）

顧客セグメント（プレミアム、レギュラー、新規）と都道府県情報を含む顧客マスタ。

seed_orders.csv（30件）

2024年1月〜3月の注文データ。ステータスはcompleted、shipped、pending。

seed_order_items.csv（約50件）

各注文の明細データ。1注文あたり1〜3アイテム。

シードデータをデータベースにロードするには：

docker-compose run --rm dbt dbt seed

dbtモデル設計

dbtモデルは3つのレイヤーで構成されています。

Stagingレイヤー

生のシードデータをクレンジングし、型変換と命名規則の統一を行います。

dbt_project/models/staging/stg_order_items.sql
with source as (
    select * from {{ ref('seed_order_items') }}
),

staged as (
    select
        order_item_id,
        order_id,
        product_id,
        cast(quantity as integer) as quantity,
        cast(unit_price as integer) as unit_price,
        cast(quantity as integer) * cast(unit_price as integer) as line_total
    from source
)

select * from staged

Intermediateレイヤー

ビジネスロジックを適用し、分析用のデータを準備します。

dbt_project/models/intermediate/int_orders_enriched.sql
with orders as (
    select * from {{ ref('stg_orders') }}
),

customers as (
    select * from {{ ref('stg_customers') }}
),

enriched as (
    select
        o.order_id,
        o.customer_id,
        c.customer_name,
        c.customer_segment,
        c.prefecture,
        o.order_date,
        o.status
    from orders o
    left join customers c on o.customer_id = c.customer_id
)

select * from enriched

Martsレイヤー

最終的な分析用のディメンションテーブルとファクトテーブルを作成します。

dbt_project/models/marts/dim_customers.sql
with customers as (
    select * from {{ ref('stg_customers') }}
),

orders as (
    select * from {{ ref('stg_orders') }}
),

order_items as (
    select * from {{ ref('stg_order_items') }}
),

order_totals as (
    select
        o.customer_id,
        count(distinct o.order_id) as total_orders,
        sum(oi.line_total) as total_spent,
        max(o.order_date) as last_order_date
    from orders o
    left join order_items oi on o.order_id = oi.order_id
    group by o.customer_id
),

final as (
    select
        c.customer_id,
        c.customer_name,
        c.email,
        c.customer_segment,
        c.prefecture,
        c.created_at,
        coalesce(ot.total_orders, 0) as total_orders,
        coalesce(ot.total_spent, 0) as total_spent,
        ot.last_order_date
    from customers c
    left join order_totals ot on c.customer_id = ot.customer_id
)

select * from final
dbt_project/models/marts/fct_daily_sales.sql
-- 日次売上集計
with order_totals as (
    select
        o.order_id,
        o.order_date,
        o.customer_id,
        sum(oi.quantity) as total_quantity,
        sum(oi.line_total) as order_total
    from {{ ref('stg_orders') }} o
    left join {{ ref('stg_order_items') }} oi on o.order_id = oi.order_id
    group by o.order_id, o.order_date, o.customer_id
),

daily_aggregation as (
    select
        order_date,
        count(distinct order_id) as total_orders,
        count(distinct customer_id) as total_customers,
        sum(total_quantity) as total_items,
        sum(order_total) as total_revenue,
        round(sum(order_total) * 1.0 / count(distinct order_id), 0) as avg_order_value
    from order_totals
    group by order_date
)

select * from daily_aggregation
order by order_date

モデルを実行するには：

docker-compose run --rm dbt dbt run

テスト実装

YAMLベースのジェネリックテスト

_staging.ymlでunique、not_null、relationships、accepted_valuesなどのテストを定義。

dbt_project/models/staging/_staging.yml
version: 2

models:
  - name: stg_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: customer_segment
        tests:
          - accepted_values:
              values: ['プレミアム', 'レギュラー', '新規']

シンギュラーテスト

カスタムSQLでビジネスルールを検証します。

dbt_project/tests/assert_order_total_positive.sql
-- 全ての注文金額が正であることを確認
select
    order_id,
    order_total
from {{ ref('fct_orders') }}
where order_total <= 0

テストを実行するには：

docker-compose run --rm dbt dbt test

テスト実行結果

dbt buildコマンドで、シード、モデル、テストを一括実行した結果：

Completed successfully

Done. PASS=82 WARN=0 ERROR=0 SKIP=0 TOTAL=82

内訳：

  • 5 seeds（シードデータロード）
  • 7 view models（staging + intermediate）
  • 4 table models（marts）
  • 66 tests（ジェネリックテスト + シンギュラーテスト）

すべてのテストがパスし、データ品質が担保されていることを確認できました。

Streamlit UI

PostgreSQLのマートテーブルに接続してダッシュボードを表示するStreamlitアプリを作成しました。

機能概要

  1. 概要ページ: 主要KPI（総売上、注文数、顧客数、平均注文額など）
  2. テーブル一覧: データベース内の全テーブルとデータプレビュー
  3. 売上分析: 日次・月次の売上推移チャート（Plotly）
  4. 商品分析: 商品売上ランキング、カテゴリ別売上円グラフ
  5. 顧客分析: 顧客セグメント分布、上位顧客ランキング

スクリーンショット

概要ページ

概要ページ

テーブル一覧ページ

テーブル一覧ページ

売上分析ページ

売上分析ページ

商品分析ページ

商品分析ページ

顧客分析ページ

顧客分析ページ

実行手順

1. Docker環境の起動

docker-compose up -d
docker-compose ps  # 全コンテナがhealthyであること

2. dbt接続確認

docker-compose run --rm dbt dbt debug

3. シードデータのロード

docker-compose run --rm dbt dbt seed

4. モデルの実行

docker-compose run --rm dbt dbt run

5. テストの実行

docker-compose run --rm dbt dbt test

6. フルビルド（seed + run + test）

docker-compose run --rm dbt dbt build

7. Streamlitダッシュボードの確認

ブラウザで http://localhost:8501 にアクセス

8. E2Eテストの実行（オプション）

cd e2e
npm install
npx playwright install
npx playwright test

E2Eテスト実行結果

Playwrightで7つのテストを実行し、すべてパスしました：

Running 7 tests using 7 workers

  ✓  Overview page loads correctly (4.5s)
  ✓  Tables page loads correctly (9.0s)
  ✓  Sales analysis page loads correctly (9.8s)
  ✓  Product analysis page loads correctly (9.5s)
  ✓  Customer analysis page loads correctly (9.7s)
  ✓  Sidebar filters are visible (4.4s)
  ✓  Dashboard footer is visible (4.4s)

  7 passed (12.5s)

各ページのスクリーンショットがe2e/screenshots/に自動保存されます。

まとめ

本記事では、dbt + Docker + PostgreSQL を使用して靴店舗のデータ分析基盤を構築する方法を解説しました。

学んだこと

  1. Dockerによる環境構築: PostgreSQL、dbt、Streamlitをコンテナ化することで、再現性のある開発環境を実現
  2. dbtのレイヤー構造: staging/intermediate/martsの3層構造により、保守性の高いデータパイプラインを構築
  3. テストの重要性: dbtの組み込みテストとカスタムテストを活用してデータ品質を担保
  4. 可視化: StreamlitとPlotlyで迅速にインタラクティブなダッシュボードを構築

技術スタック

技術 バージョン
PostgreSQL 15-alpine
dbt-core 1.7.x
dbt-postgres 1.7.x
Python 3.11-slim
dbt_utils 1.1.1
Streamlit latest
Playwright latest

今後の改善点

  • CI/CDパイプラインの構築（GitHub Actions）
  • dbt docsによるドキュメント生成
  • 増分更新（incremental）モデルの導入
  • より複雑な分析モデルの追加

dbtは学習コストが低く、SQLさえ書ければデータエンジニアリングの基盤を構築できる強力なツールです。ぜひ皆さんもプロジェクトに導入してみてください。

参考リンク

Discussion