ディレクトリ構造

s3://dataflow-data-lake/
├── bronze/
│   ├── app_db/          # PostgreSQL CDC
│   │   ├── users/
│   │   ├── subscriptions/
│   │   ├── campaigns/
│   │   └── email_deliveries/
│   ├── event_logs/      # MongoDB
│   │   └── clickstream/
│   ├── salesforce/      # Fivetran
│   │   ├── accounts/
│   │   └── opportunities/
│   ├── hubspot/         # Fivetran
│   │   └── leads/
│   └── stripe/          # Fivetran
│       ├── invoices/
│       └── subscriptions/
├── silver/
│   ├── users/
│   ├── subscriptions/
│   ├── events/
│   ├── campaigns/
│   └── payments/
├── gold/
│   ├── dimensions/
│   │   ├── dim_customer/
│   │   ├── dim_date/
│   │   ├── dim_plan/
│   │   └── dim_campaign/
│   ├── facts/
│   │   ├── fact_subscription/
│   │   ├── fact_user_activity/
│   │   └── fact_campaign_performance/
│   └── marts/
│       ├── revenue_daily/
│       ├── customer_ltv/
│       └── churn_features/
└── _system/
    ├── data_quality/
    └── audit_logs/

パーティショニング

フォーマット・ライフサイクル

ER図

                 ┌─────────────┐
                 │  dim_date   │
                 └──────┬──────┘
                        │
  ┌─────────────┐  ┌────┴────────────┐  ┌──────────────┐
  │ dim_customer │──│ fact_subscription│──│  dim_plan    │
  │ (SCD Type 2)│  └────┬────────────┘  └──────────────┘
  └──────┬──────┘       │
         │         ┌────┴────────────────┐
         ├─────────│ fact_user_activity   │
         │         └─────────────────────┘
         │
         │         ┌────────────────────────┐  ┌──────────────┐
         └─────────│ fact_campaign_perform.  │──│ dim_campaign │
                   └────────────────────────┘  └──────────────┘

ファクトテーブル

ディメンションテーブル

-- models/gold/marts/mart_monthly_metrics.sql
-- 月次KPIメトリクス

{{ config(
    materialized='table',
    partition_by={'field': 'metric_month', 'data_type': 'date'}
) }}

WITH active_users AS (
    SELECT
        DATE_TRUNC(activity_date, MONTH) AS metric_month,
        COUNT(DISTINCT customer_key) AS active_user_count
    FROM {{ ref('fact_user_activity') }}
    WHERE login_count > 0
    GROUP BY 1
),

mrr AS (
    SELECT
        DATE_TRUNC(effective_date, MONTH) AS metric_month,
        SUM(mrr) AS total_mrr
    FROM {{ ref('fact_subscription') }}
    WHERE status = 'ACTIVE'
    GROUP BY 1
),

churn AS (
    SELECT
        metric_month,
        active_start,
        active_end,
        SAFE_DIVIDE(active_start - active_end, active_start) AS churn_rate
    FROM (
        SELECT
            DATE_TRUNC(d.full_date, MONTH) AS metric_month,
            COUNT(DISTINCT CASE
                WHEN ua_start.login_count > 0 THEN ua_start.customer_key
            END) AS active_start,
            COUNT(DISTINCT CASE
                WHEN ua_end.login_count > 0 THEN ua_end.customer_key
            END) AS active_end
        FROM {{ ref('dim_date') }} d
        LEFT JOIN {{ ref('fact_user_activity') }} ua_start
            ON ua_start.activity_date BETWEEN DATE_TRUNC(d.full_date, MONTH)
               AND DATE_ADD(DATE_TRUNC(d.full_date, MONTH), INTERVAL 29 DAY)
        LEFT JOIN {{ ref('fact_user_activity') }} ua_end
            ON ua_end.activity_date BETWEEN DATE_SUB(LAST_DAY(d.full_date), INTERVAL 29 DAY)
               AND LAST_DAY(d.full_date)
            AND ua_end.customer_key = ua_start.customer_key
        WHERE d.full_date = DATE_TRUNC(d.full_date, MONTH)
        GROUP BY 1
    )
)

SELECT
    a.metric_month,
    a.active_user_count,
    m.total_mrr,
    c.churn_rate
FROM active_users a
LEFT JOIN mrr m ON a.metric_month = m.metric_month
LEFT JOIN churn c ON a.metric_month = c.metric_month

dbt テスト定義

# models/gold/marts/schema.yml
models:
  - name: mart_monthly_metrics
    description: "月次KPIメトリクス。アクティブユーザー数、MRR、チャーン率を統合"
    columns:
      - name: metric_month
        description: "メトリクスの対象月（月初日）"
        tests:
          - not_null
          - unique
      - name: active_user_count
        description: "過去30日間に1回以上ログインしたユーザー数"
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"
      - name: total_mrr
        description: "月次経常収益（Monthly Recurring Revenue）"
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"
      - name: churn_rate
        description: "月次解約率"
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 1