Omniで動的に年次・月次などの集計テーブルを参照してスキャンコストを削減できる「Aggregate Awareness」を試してみた

# Reference this view as omni_dbt_mart__orders
schema_label: ""
description: 注文概要データマート 。顧客の最初の注文かどうか、食べ物と飲み物の内訳など、各注文の重要な詳細を提供します。注文ごとに 1 行。

schema: omni_dbt_mart
table_name: ORDERS

dimensions:
  order_id:
    sql: '"ORDER_ID"'
    format: ID
    #This description was pulled from dbt.
    description: プライマリキー

  location_id:
    sql: '"LOCATION_ID"'
    format: ID

  customer_id:
    sql: '"CUSTOMER_ID"'
    format: ID
    #This description was pulled from dbt.
    description: 注文顧客と紐づける外部キー

  subtotal_cents:
    sql: '"SUBTOTAL_CENTS"'

  tax_paid_cents:
    sql: '"TAX_PAID_CENTS"'

  order_total_cents:
    sql: '"ORDER_TOTAL_CENTS"'

  subtotal:
    sql: '"SUBTOTAL"'

  tax_paid:
    sql: '"TAX_PAID"'

  order_total:
    sql: '"ORDER_TOTAL"'
    #This description was pulled from dbt.
    description: 税込みの注文総額 (USD)

  order_cost:
    sql: '"ORDER_COST"'
    #This description was pulled from dbt.
    description: 注文を履行するための供給費の合計

  order_items_subtotal:
    sql: '"ORDER_ITEMS_SUBTOTAL"'

  count_food_items:
    sql: '"COUNT_FOOD_ITEMS"'

  count_drink_items:
    sql: '"COUNT_DRINK_ITEMS"'

  count_order_items:
    sql: '"COUNT_ORDER_ITEMS"'

  is_food_order:
    sql: '"IS_FOOD_ORDER"'
    #This description was pulled from dbt.
    description: この注文に食品が含まれているかどうかを示すブール値

  is_drink_order:
    sql: '"IS_DRINK_ORDER"'
    #This description was pulled from dbt.
    description: この注文にドリンクアイテムが含まれているかどうかを示すブール値

  customer_order_number:
    sql: '"CUSTOMER_ORDER_NUMBER"'

  purchased_at:
    sql: '"PURCHASED_AT"'
    #This description was pulled from dbt.
    description: 注文が行われたタイムスタンプ

measures:
  count:
    aggregate_type: count

  order_total_sum:
    sql: ${omni_dbt_mart__orders.order_total}
    aggregate_type: sum

  orders_count_new_customer:
    sql: |-
      COUNT(
        CASE
          WHEN customer_order_number = 1 THEN 1
          ELSE 0
        END
      )
    label: Orders Count New Customer

  # --- 以下はaggregate awareness検証用---
  total_revenue:
    sql: ${omni_dbt_mart__orders.order_total}
    aggregate_type: sum

  total_cost:
    sql: ${omni_dbt_mart__orders.order_cost}
    aggregate_type: sum

  total_subtotal:
    sql: ${omni_dbt_mart__orders.order_items_subtotal}
    aggregate_type: sum

  total_tax:
    sql: ${omni_dbt_mart__orders.tax_paid}
    aggregate_type: sum

  total_profit:
    sql: ${omni_dbt_mart__orders.order_total} - ${omni_dbt_mart__orders.order_cost}
    aggregate_type: sum

  total_items_sold:
    sql: ${omni_dbt_mart__orders.count_order_items}
    aggregate_type: sum

  total_food_items:
    sql: ${omni_dbt_mart__orders.count_food_items}
    aggregate_type: sum

  total_drink_items:
    sql: ${omni_dbt_mart__orders.count_drink_items}
    aggregate_type: sum

  count_orders_with_food:
    aggregate_type: count
    filters:
      is_food_order:
        is: true

  count_orders_with_drink:
    aggregate_type: count
    filters:
      is_drink_order:
        is: true

#The info below was pulled from your dbt repository and is read-only.
dbt:
  name: orders
  target_schema: PROD
  description: 注文概要データマート 。顧客の最初の注文かどうか、食べ物と飲み物の内訳など、各注文の重要な詳細を提供します。注文ごとに 1 行。
  config:
    schema: mart
    materialized: table
  code: |-
    with

    orders as (

        select * from {{ ref('stg_orders') }}

    ),

    order_items as (

        select * from {{ ref('order_items') }}

    ),

    order_items_summary as (

        select
            order_id,

            sum(supply_cost) as order_cost,
            sum(product_price) as order_items_subtotal,
            count(order_item_id) as count_order_items,
            sum(
                case
                    when is_food_item then 1
                    else 0
                end
            ) as count_food_items,
            sum(
                case
                    when is_drink_item then 1
                    else 0
                end
            ) as count_drink_items

        from order_items

        group by 1

    ),

    compute_booleans as (

        select
            orders.*,

            order_items_summary.order_cost,
            order_items_summary.order_items_subtotal,
            order_items_summary.count_food_items,
            order_items_summary.count_drink_items,
            order_items_summary.count_order_items,
            order_items_summary.count_food_items > 0 as is_food_order,
            order_items_summary.count_drink_items > 0 as is_drink_order

        from orders

        left join
            order_items_summary
            on orders.order_id = order_items_summary.order_id

    ),

    customer_order_count as (

        select
            *,

            row_number() over (
                partition by customer_id
                order by purchased_at asc 
                -- order by ordered_at asc
            ) as customer_order_number

        from compute_booleans

    )

    select * from customer_order_count
  referenced_by: [ customers, orders_yearly, orders_monthly, product_total_sales ]

materialized_query:
  fields:
    [
      "omni_dbt_mart__orders.purchased_at[year]",
      omni_dbt_mart__orders.total_revenue,
      omni_dbt_mart__orders.total_cost,
      omni_dbt_mart__orders.total_subtotal,
      omni_dbt_mart__orders.total_tax,
      omni_dbt_mart__orders.total_profit,
      omni_dbt_mart__orders.count,
      omni_dbt_mart__orders.total_items_sold,
      omni_dbt_mart__orders.total_food_items,
      omni_dbt_mart__orders.total_drink_items,
      omni_dbt_mart__orders.count_orders_with_food,
      omni_dbt_mart__orders.count_orders_with_drink
    ]
  base_view: omni_dbt_mart__orders

materialized_query:
  fields:
    [
      "omni_dbt_mart__orders.purchased_at[month]",
      omni_dbt_mart__orders.total_revenue,
      omni_dbt_mart__orders.total_cost,
      omni_dbt_mart__orders.total_subtotal,
      omni_dbt_mart__orders.total_tax,
      omni_dbt_mart__orders.total_profit,
      omni_dbt_mart__orders.count,
      omni_dbt_mart__orders.total_items_sold,
      omni_dbt_mart__orders.total_food_items,
      omni_dbt_mart__orders.total_drink_items,
      omni_dbt_mart__orders.count_orders_with_food,
      omni_dbt_mart__orders.count_orders_with_drink
    ]
  base_view: omni_dbt_mart__orders