Snowflake Semantic View を dbt で定義し、MetricFlow と同じメトリクスをクエリしてみる

packages:
  - package: Snowflake-Labs/dbt_semantic_view
    version: 1.0.3

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from {{ source('jaffle_shop', 'orders') }}

version: 2

models:
  - name: stg_orders
    semantic_model:
      enabled: true
    agg_time_dimension: order_date   # metric_time として自動公開される

    columns:
      - name: order_id
        entity:
          type: primary
          name: orders              # プライマリエンティティ

      - name: customer_id
        entity:
          type: foreign
          name: customer            # stg_customers との JOIN キー

      - name: order_date
        granularity: day
        dimension:
          type: time

      - name: status
        dimension:
          type: categorical

    metrics:
      - name: order_count
        type: simple
        agg: count
        expr: 1

select
    id as customer_id,
    first_name,
    last_name,
    first_name || ' ' || last_name as full_name

from {{ source('jaffle_shop', 'customers') }}

version: 2

models:
  - name: stg_customers
    semantic_model:
      enabled: true

    columns:
      - name: customer_id # プライマリエンティティ
        entity:
          type: primary
          name: customer

      - name: full_name
        dimension:
          type: categorical

{{ config(materialized='semantic_view') }}

TABLES(
    o AS {{ ref('stg_orders') }},
    c AS {{ ref('stg_customers') }}
        PRIMARY KEY (customer_id)
)
RELATIONSHIPS(
    orders_customers AS o (customer_id)
    REFERENCES c (customer_id)
)
FACTS(
    o.order_id    AS o.order_id,
    c.customer_id AS c.customer_id
)
DIMENSIONS(
    o.order_date AS o.order_date,
    o.status     AS o.status,
    c.full_name  AS c.full_name
)
METRICS(
    o.order_count AS COUNT(o.order_id)
)

# パッケージのインストール（初回のみ）
dbt deps

# semantic view のみビルド
dbt run -s sv_orders

$ dbt sl query --metrics order_count --group-by orders__status
+----------------+-------------+
| ORDERS__STATUS | ORDER_COUNT |
+----------------+-------------+
| completed      |          67 |
| shipped        |          13 |
| returned       |           4 |
| return_pending |           2 |
| placed         |          13 |
+----------------+-------------+

SELECT
    status,
    AGG(order_count) AS order_count
FROM dbt_sl_db.dbt_tyasuhara.sv_orders
GROUP BY status
ORDER BY status;
+----------------+-------------+                                                
| STATUS         | ORDER_COUNT |
|----------------+-------------|
| completed      |          67 |
| placed         |          13 |
| return_pending |           2 |
| returned       |           4 |
| shipped        |          13 |
+----------------+-------------+

$ dbt sl query --metrics order_count --group-by metric_time__month
+-------------------------+-------------+
| METRIC_TIME__MONTH      | ORDER_COUNT |
+-------------------------+-------------+
| 2018-01-01T00:00:00.000 |          29 |
| 2018-02-01T00:00:00.000 |          27 |
| 2018-03-01T00:00:00.000 |          35 |
| 2018-04-01T00:00:00.000 |           8 |
+-------------------------+-------------+

SELECT
    DATE_TRUNC('MONTH', order_date) AS month,
    AGG(order_count) AS order_count
FROM dbt_sl_db.dbt_tyasuhara.sv_orders
GROUP BY DATE_TRUNC('MONTH', order_date)
ORDER BY month;
+------------+-------------+                                                    
| MONTH      | ORDER_COUNT |
|------------+-------------|
| 2018-01-01 |          29 |
| 2018-02-01 |          27 |
| 2018-03-01 |          35 |
| 2018-04-01 |           8 |
+------------+-------------+

dbt sl query --metrics order_count --group-by customer__full_name --order-by customer__full_name
+---------------------+-------------+
| CUSTOMER__FULL_NAME | ORDER_COUNT |
+---------------------+-------------+
| Aaron R.            |           2 |
| Adam A.             |           1 |
| Adam T.             |           1 |
| Adam W.             |           3 |
| Amanda H.           |           1 |
| ...（62行）          |             |
+---------------------+-------------+

SELECT
  stg_customers_src_10000.full_name AS customer__full_name,
  SUM(subq_1.__order_count) AS order_count
FROM (
  SELECT
    customer_id AS customer,
    1 AS __order_count
  FROM dbt_sl_db.dbt_tyasuhara.stg_orders stg_orders_src_10000
) subq_1
LEFT OUTER JOIN
  dbt_sl_db.dbt_tyasuhara.stg_customers stg_customers_src_10000
ON
  subq_1.customer = stg_customers_src_10000.customer_id
GROUP BY
  stg_customers_src_10000.full_name
LIMIT 100

SELECT
    full_name,
    AGG(order_count) AS order_count
FROM dbt_sl_db.dbt_tyasuhara.sv_orders
GROUP BY full_name
ORDER BY full_name;
+--------------+-------------+                                                  
| FULL_NAME    | ORDER_COUNT |
|--------------+-------------|
| Aaron R.     |           2 |
| Adam A.      |           1 |
| Adam T.      |           1 |
| Adam W.      |           3 |
| Amanda H.    |           1 |
| ...（62行）   |             |
+--------------+-------------+
62 Row(s) produced. Time Elapsed: 0.502s

-- 全顧客数
SELECT COUNT(*) AS total_customers
FROM dbt_sl_db.dbt_tyasuhara.stg_customers;
+-----------------+
| TOTAL_CUSTOMERS |
|-----------------|
|             100 |
+-----------------+

-- 注文あり顧客数・注文なし顧客数・full_name のユニーク数
SELECT
    COUNT(DISTINCT c.customer_id)                                        AS customers_with_orders,
    (SELECT COUNT(*) FROM dbt_sl_db.dbt_tyasuhara.stg_customers)
        - COUNT(DISTINCT c.customer_id)                                  AS customers_without_orders,
    COUNT(DISTINCT c.full_name)                                          AS unique_full_names
FROM dbt_sl_db.dbt_tyasuhara.stg_customers c
INNER JOIN dbt_sl_db.dbt_tyasuhara.stg_orders o ON c.customer_id = o.customer_id;
+-----------------------+--------------------------+-------------------+
| CUSTOMERS_WITH_ORDERS | CUSTOMERS_WITHOUT_ORDERS | UNIQUE_FULL_NAMES |
|-----------------------+--------------------------+-------------------|
|                    62 |                       38 |                62 |
+-----------------------+--------------------------+-------------------+

-- テストデータ挿入（customer_id=9999 は stg_customers に存在しない）
INSERT INTO dbt_sl_db.dbt_tyasuhara.stg_orders (order_id, customer_id, order_date, status)
VALUES (99999, 9999, '2018-03-15', 'completed');

SELECT 
    full_name, 
    AGG(order_count) AS order_count
FROM dbt_sl_db.dbt_tyasuhara.sv_orders
WHERE order_date = '2018-03-15'
GROUP BY full_name
ORDER BY full_name;
+-----------+-------------+                                                     
| FULL_NAME | ORDER_COUNT |
|-----------+-------------|
| NULL      |           1 |  
+-----------+-------------+

{{ config(materialized='table') }}

SELECT
    status,
    AGG(order_count) AS order_count
FROM {{ ref('sv_orders') }}
GROUP BY status
ORDER BY status