dbt Charts で Snowflake 上の dbt モデルを YAML だけでダッシュボード化してみた

title: Jaffle Shop 売上ダッシュボード
notes: >
  jaffle_shop の marts モデル (orders / order_items / products / locations / customers) を
  ref() で参照するボード。location と period の 2 つの変数で絞り込む。
  金額は税抜 (orders.subtotal / order_items.product_price) でそろえている。

source: jaffle

variables:
  location:
    input: select
    label: 店舗
    options:
      query: location_options
      column: location_name
  period:
    input: daterange
    label: 期間
    default: ["2024-09-01", "2025-08-31"]

queries:
  location_options: |
    select location_name
    from {{ ref('locations') }}
    order by location_name

  kpis: |
    select
        sum(o.subtotal)                          as revenue,
        count(*)                                 as orders,
        count(distinct o.customer_id)            as customers,
        sum(o.subtotal) / nullif(count(*), 0)    as avg_order_value
    from {{ ref('orders') }} as o
    join {{ ref('locations') }} as l
        on o.location_id = l.location_id
    where {{ filter('l.location_name', location) }}
      and {{ filter_date_range('o.ordered_at', period) }}

  monthly_revenue: |
    select
        date_trunc('month', o.ordered_at)::date as month,
        sum(o.subtotal)                         as revenue,
        count(*)                                as orders
    from {{ ref('orders') }} as o
    join {{ ref('locations') }} as l
        on o.location_id = l.location_id
    where {{ filter('l.location_name', location) }}
      and {{ filter_date_range('o.ordered_at', period) }}
    group by 1
    order by 1

  revenue_by_type: |
    select
        date_trunc('month', o.ordered_at)::date  as month,
        p.product_type                           as product_type,
        sum(oi.product_price)                    as revenue
    from {{ ref('order_items') }} as oi
    join {{ ref('products') }} as p
        on oi.product_id = p.product_id
    join {{ ref('orders') }} as o
        on oi.order_id = o.order_id
    join {{ ref('locations') }} as l
        on o.location_id = l.location_id
    where {{ filter('l.location_name', location) }}
      and {{ filter_date_range('o.ordered_at', period) }}
    group by 1, 2
    order by 1, 2

  customer_mix: |
    select
        customer_type,
        count(*) as customers
    from {{ ref('customers') }}
    where customer_type is not null
    group by 1
    order by 1

  top_products: |
    select
        p.product_name                              as product_name,
        p.product_type                              as product_type,
        count(*)                                    as items_sold,
        sum(oi.product_price)                       as revenue,
        sum(oi.product_price) - sum(oi.supply_cost) as gross_profit
    from {{ ref('order_items') }} as oi
    join {{ ref('products') }} as p
        on oi.product_id = p.product_id
    where {{ filter_date_range('oi.ordered_at', period) }}
    group by 1, 2
    order by revenue desc
    limit 10

charts:
  kpi_revenue:
    type: kpi
    query: queries.kpis
    label: 売上(税抜)
    value: revenue
    style:
      value:
        format: currency_whole

  kpi_orders:
    type: kpi
    query: queries.kpis
    label: 注文数
    value: orders
    style:
      value:
        format: integer

  kpi_customers:
    type: kpi
    query: queries.kpis
    label: 顧客数
    value: customers
    style:
      value:
        format: integer

  kpi_aov:
    type: kpi
    query: queries.kpis
    label: 平均注文額
    value: avg_order_value
    style:
      value:
        format: currency_full

  revenue_trend:
    type: line
    query: queries.monthly_revenue
    title: 月次売上
    x: month
    y: revenue
    style:
      number_format: currency

  customer_mix:
    type: bar
    query: queries.customer_mix
    title: 新規 / リピート顧客
    x: customer_type
    y: customers

  revenue_by_type:
    type: bar
    query: queries.revenue_by_type
    title: 商品タイプ別の月次売上
    x: month
    y: revenue
    color: product_type
    style:
      orientation: vertical
      stack: zero
      number_format: currency

  top_products:
    type: table
    query: queries.top_products
    title: 売上上位 10 商品
    style:
      columns:
        product_name:
          label: 商品
        product_type:
          label: タイプ
        items_sold:
          label: 販売数
          format: integer
        revenue:
          label: 売上
          format: currency_whole
        gross_profit:
          label: 粗利
          format: currency_whole

rows:
  - cols: [kpi_revenue, kpi_orders, kpi_customers, kpi_aov]
  - title: 売上の推移
    grid:
      columns: 24
      items:
        - item: revenue_trend
          width: 16
        - item: customer_mix
          width: 8
  - revenue_by_type
  - top_products