dbt語義層構建度量指標解析

– 11 分鐘閱讀

dbt語義層提供了一個抽象層，讓分析師可以定義和計算指標，無需關心底層資料結構。透過定義實體、維度和度量指標，可以建立一致的資料檢視，簡化分析和報告流程。實體代表核心業務物件，維度提供上下文，度量指標則是可量化的資料點。利用MetricFlow，可以根據定義的語義模型生成SQL查詢，實作資料分析。此外，文章也探討了動態SQL、SQL宏和dbt包等高階主題，以及如何設計和實施端對端分析工程案例，從資料攝取到建模和報告，完整展示dbt和SQL的應用。

建立dbt語義層：度量、實體、維度和度量指標的解析

在資料分析的世界中，建立一個強大且靈活的語義層至關重要。dbt（Data Build Tool）透過其語義層為資料分析師和工程師提供了一個強大的框架，用於定義和管理度量指標。本文將探討dbt語義層的核心組成部分：實體、維度和度量指標，以及如何利用這些元件建立有意義的度量指標。

語義層的核心概念

語義層依賴於三個基本概念來建立度量指標：實體、維度和度量指標。

實體

實體是指在特定上下文中獨立且可識別的物件。在資料函式庫語言中，實體通常對應於表格，是資料收集的核心主題。實體代表了業務中的真實概念，例如客戶或訂單。在語義模型中，實體透過ID列來表示，這些ID列作為連線鍵與語義圖中的其他語義模型相連線。

實體對於幫助語義引擎理解表格或資料集之間的關係至關重要。這使得引擎能夠理解資料如何相互連線，從而在查詢特定實體時知道從哪裡檢索相關資訊。

維度

維度為度量指標提供了上下文，作為分類別屬性允許在分析過程中以不同的方式分解資料。維度通常描述模型中其他元素的特性。

維度的組態讓使用者能夠從多個角度探索和分析資料。語義引擎利用這些維度根據使用者偏好定製查詢。

度量指標

度量指標是分析的主要焦點，是我們打算檢查的可量化資料點。度量指標通常需要聚合，在許多情況下，BI工具的基本作用是跨多個維度聚合這些度量指標。度量指標的定義確保了計算在所有查詢和報告中保持一致性，消除了任何語義模糊性。

構建dbt語義層

讓我們透過一個例子來說明如何構建dbt語義層。假設我們要測量客戶和訂單實體的相關度量指標，包括總支付金額（total_amount）、現金支付金額（cash_amount）和信用卡支付金額（credit_amount），以及訂單數量（order_count）和有訂單的客戶數量（customers_with_orders）。此外，我們還希望能夠按日期（order_date）和訂單是否完成（is_order_completed）進行分析。

語義模型的YAML組態

semantic_models:
  - name: orders
    description: |
      訂單事實表。每個訂單一行。
    model: ref('fct_orders')
    entities:
      - name: order_id
        type: primary
      - name: customer
        type: foreign
        expr: customer_id
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: is_order_completed
        type: categorical
    measures:
      - name: total_amount
        description: 成功支付的客戶總支付金額。
        agg: sum
      - name: order_count
        expr: 1
        agg: sum
      - name: cash_amount
        description: 成功支付的客戶現金支付總金額。
        agg: sum
      - name: credit_amount
        description: 成功支付的客戶信用卡支付總金額。
        agg: sum
      - name: customers_with_orders
        description: 下單客戶的獨立計數。
        agg: count_distinct
        expr: customer_id

定義度量指標

在建立了語義模型之後，我們需要定義具體的度量指標。這些度量指標根據語義模型，使用MetricFlow生成分析查詢。

度量指標的YAML組態

metrics:
  - name: order_total
    description: 訂單總金額的總和。
    type: simple
    label: 訂單總計
    type_params:
      measure: total_amount
  - name: order_count
    description: 訂單數量。
    label: 訂單數
    type: simple
    type_params:
      measure: order_count
  - name: completed_orders
    description: 已完成的訂單數量。
    label: 已完成訂單
    type: simple
    type_params:
      measure: order_count

MetricFlow：dbt的語義引擎

MetricFlow作為dbt的語義引擎，負責解析語義模型並生成分析查詢。它使得定義的度量指標能夠被實際應用於資料分析中，提供了一致且可重用的查詢結果。

使用MetricFlow生成分析查詢

首先，需要根據建立的語義模型定義度量指標。然後，MetricFlow會根據這些定義生成相應的分析查詢，從而實作對資料的深入分析和洞察。

資料轉換與分析的高階應用：dbt 語義層與 MetricFlow

在資料分析的世界中，dbt（Data Build Tool）已成為一個不可或缺的工具。它不僅簡化了資料轉換的過程，還提供了強大的語義層來統一資料的定義和計算。在本章中，我們將探討 dbt 的高階功能，特別是語義層和 MetricFlow 的應用。

dbt 語義層的建立與應用

dbt 語義層是一個抽象層，它使得資料分析師可以定義和計算各種指標（metrics），而無需關心底層資料的具體結構和儲存方式。透過定義語義模型，我們可以建立一個統一的資料檢視，從而簡化資料分析和報告的過程。

建立語義層

要建立 dbt 語義層，首先需要定義語義模型。語義模型是對資料的一種抽象描述，它定義了資料的結構、關係和計算邏輯。例如，我們可以定義一個 orders 語義模型，用於描述訂單資料的結構和相關指標。

-- 定義 orders 語義模型
SELECT 
  order_id,
  customer_id,
  order_date,
  total_amount
FROM 
  fct_orders

使用 MetricFlow 進行指標計算

MetricFlow 是 dbt 提供的一個命令列工具，用於計算和查詢指標。透過定義指標的計算邏輯，MetricFlow 可以自動生成相應的 SQL 查詢陳述式，從而簡化指標的計算過程。

例如，要計算 order_total 指標，可以使用以下命令：

mf query --metric order_total

這將生成相應的 SQL 查詢陳述式，如下所示：

-- Order total 查詢
SELECT 
  SUM(total_amount) as order_total
FROM 
  fct_orders

語義層的好處

dbt 語義層的建立，使得資料分析師可以輕鬆地定義和計算各種指標，而無需關心底層資料的具體實作。這不僅簡化了資料分析的過程，還提高了資料的一致性和準確性。

高階 dbt 主題與應用

除了語義層和 MetricFlow 之外，dbt 還提供了許多其他高階功能，例如動態 SQL、SQL宏和 dbt 包等。這些功能使得資料分析師可以更靈活地管理和最佳化資料轉換過程。

動態 SQL 與 Jinja

透過使用 Jinja 範本引擎，dbt 允許資料分析師建立動態 SQL 查詢陳述式。這使得資料轉換過程更加靈活和可重用。

SQL 宏

SQL 宏是 dbt 提供的一種程式碼重用機制。透過定義 SQL 宏，資料分析師可以簡化程式碼並提高可維護性。

dbt 包

dbt 包是 dbt 提供的一種程式碼分享機制。透過使用 dbt 包，資料分析師可以分享和重用程式碼，從而提高開發效率。

第六章：建立端對端分析工程案例

在前面的章節中，我們已經介紹了使用 dbt 和 SQL 進行分析工程的各種概念、技術和最佳實踐。現在，是時候將這些主題結合起來，建立一個端對端的分析工程案例。

在本章中，我們將設計、實施和佈署一個全面的分析解決方案，從頭到尾利用 dbt 和 SQL 的全部潛力，建立一個強壯且可擴充套件的分析基礎設施，並使用資料建模來滿足操作和分析需求。

我們的目標是展示如何將本文中涵蓋的原理和方法實際應用於解決現實世界的資料問題。透過結合前幾章所獲得的知識，我們將建立一個跨越資料生命週期所有階段的分析引擎，從資料攝取和轉換到建模和報告。在本章中，我們將解決實施過程中出現的常見挑戰，並提供有效的解決方案。

端對端分析工程案例的設計與實施

要建立一個端對端的分析工程案例，需要考慮多個方面，包括資料攝取、轉換、建模和報告等。下面是一個簡單的架構圖，用於展示整個分析流程：

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title dbt 語義層架構

package "語義模型核心元件" {
    component [實體 Entity\n(order_id, customer_id)] as entity
    component [維度 Dimension\n(order_date, is_completed)] as dimension
    component [度量 Measure\n(total_amount, order_count)] as measure
}

package "語義層組態" {
    component [semantic_models.yml] as sm_yaml
    component [metrics.yml] as m_yaml
    component [ref('fct_orders')] as model_ref
}

package "MetricFlow 查詢引擎" {
    component [語義圖解析] as graph
    component [SQL 生成器] as sql_gen
    component [查詢執行] as execute
}

package "商業指標輸出" {
    component [order_total] as total
    component [order_count] as count
    component [customers_with_orders] as customers
}

model_ref --> sm_yaml : 定義來源
sm_yaml --> entity : 主鍵/外鍵
sm_yaml --> dimension : 分類/時間
sm_yaml --> measure : 聚合計算

entity --> graph : 連接關係
dimension --> graph : 分析切角
measure --> graph : 計算邏輯

m_yaml --> graph : 指標定義
graph --> sql_gen : 解析語義
sql_gen --> execute : 生成查詢

execute --> total : SUM(total_amount)
execute --> count : COUNT(*)
execute --> customers : COUNT_DISTINCT

note right of entity
  實體類型：
  - primary: 主鍵
  - foreign: 外鍵連接
end note

note right of measure
  聚合類型：
  - sum, count
  - count_distinct
  - avg, min, max
end note

@enduml

圖表翻譯： 此圖示展示了端對端分析工程案例的流程，包括資料攝取、轉換、建模和報告等階段。

#### 內容解密：

此圖表展示了一個簡單的端對端分析工程案例流程。首先，資料被攝取到系統中，然後經過轉換處理，接著進行建模，最後生成報告。

全方位分析案例：開發無縫客戶體驗的資料分析策略

在當前數位時代，企業面臨著提升客戶體驗的巨大挑戰。為了實作這一目標，我們需要建立一個全面的資料集，以捕捉寶貴的客戶洞察。客戶資訊（如姓名、電子郵件地址和電話號碼）對於構建強大的客戶檔案至關重要。透過追蹤客戶在不同通路（如網站、行動應用程式和客戶支援）的互動，我們能夠深入瞭解他們的偏好和需求。

營運資料建模

在追求全面性的過程中，我們首先進行營運資料建模。這一階段至關重要，因為它為後續步驟奠定了堅實的基礎。我們的策略包括使用精心記錄的管理資料函式庫需求，這將指導我們的整個過程。按照行業最佳實踐，我們將嚴格遵循概念建模、邏輯建模和物理建模這三個基本步驟，以精細地構建我們的資料函式庫。

概念模型

概念建模階段使我們能夠概念化和定義資料函式庫的整體結構和關係。透過識別關鍵實體、其屬性和關聯，我們能夠捕捉管理系統的精髓，並將其轉化為簡潔而全面的概念模型（圖 6-1）。

圖 6-1 展示了我們的概念模型，其中包含三個實體：客戶（Customer）、通路（Channel）和產品（Products），以及兩個關鍵關係：購買（Buy）和存取（Visit）。第一個關係使我們能夠追蹤客戶在特定通路中對特定產品的購買行為。第二個關係則允許我們追蹤客戶在不同通路中的互動。

邏輯模型

邏輯建模涉及將概念模型轉換為結構化的表示形式，包括實體、屬性和它們之間的關係。這一架構為在特定系統中實作資料函式庫奠定了基礎。我們將實體轉化為表格，將屬性轉化為表格列。根據關係型別的不同，我們採用不同的處理方式：對於 N:1 關係，我們使用外部索引鍵連線表格；對於 M:N 關係，我們建立一個單獨的表格來表示這種連線。

圖 6-2 展示了我們的邏輯模型，其中包含五個表格：三個主要實體表格（客戶、產品和通路），以及兩個關係表格（購買歷史和存取歷史）。

物理模型

物理建模關注的是資料管理的實際方面，假設我們已經選擇了特定的資料函式庫引擎——在本案例中是 MySQL。因此，我們需要將邏輯模型轉化為符合 MySQL 最佳實踐和限制的特定儲存組態。

圖 6-3 展示了我們的 ERD 圖，代表了 MySQL 中的資料型別和約束。

建立 MySQL 資料函式庫和表格

首先，我們建立一個新的 MySQL 資料函式庫來儲存我們的表格結構。

CREATE DATABASE IF NOT EXISTS OMNI_MANAGEMENT;
USE OMNI_MANAGEMENT;

內容解密：

此段程式碼建立了一個名為 OMNI_MANAGEMENT 的資料函式庫（如果該資料函式庫不存在），並切換到該資料函式庫以進行後續操作。這是資料函式庫初始化的第一步。

接下來，我們建立三個主要的表格：客戶、產品和通路。

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150),
    date_birth DATE,
    email_address VARCHAR(150),
    phone_number VARCHAR(30),
    country VARCHAR(100),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS products (
    product_sku INTEGER PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(150),
    unit_price DOUBLE,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS channels (
    channel_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    channel_name VARCHAR(150),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

內容解密：

  • customers 表格包含客戶的基本資訊，如客戶ID、姓名、出生日期、電子郵件地址、電話號碼和國家。customer_id 是主鍵，並自動遞增。
  • products 表格包含產品資訊，如產品SKU、產品名稱和單價。product_sku 是主鍵，並自動遞增。
  • channels 表格包含通路資訊，如通路ID和通路名稱。channel_id 是主鍵，並自動遞增。
  • 每個表格都有 CREATED_AT 和 UPDATED_AT 欄位，用於記錄記錄的建立和最後更新時間。

透過這些步驟，我們成功地建立了一個全面的資料模型，為後續的資料分析和客戶體驗最佳化奠定了堅實的基礎。

資料函式庫關聯表的建立與資料完整性維護

在建立資料函式庫時，我們使用 IF NOT EXISTS 子句來確保只有在資料表不存在的情況下才會被建立。這個做法可以避免在多次執行程式碼時產生錯誤或衝突。我們在所有資料表中都加入了 CREATED_AT 和 UPDATED_AT 欄位，這是一種最佳實踐，可以使我們的資料函式庫準備好進行增量資料擷取。

建立關聯表

如範例 6-3 所示，我們可以建立關聯表。

purchaseHistory 表

CREATE TABLE IF NOT EXISTS purchaseHistory (
  customer_id INTEGER,
  product_sku INTEGER,
  channel_id INTEGER,
  quantity INT,
  discount DOUBLE DEFAULT 0,
  order_date DATETIME NOT NULL,
  CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (channel_id) REFERENCES channels(channel_id),
  FOREIGN KEY (product_sku) REFERENCES products(product_sku),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

visitHistory 表

CREATE TABLE IF NOT EXISTS visitHistory (
  customer_id INTEGER,
  channel_id INTEGER,
  visit_timestamp TIMESTAMP NOT NULL,
  bounce_timestamp TIMESTAMP NULL,
  CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (channel_id) REFERENCES channels(channel_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

purchaseHistory 表結構說明

此表格儲存客戶的購買歷史，包含客戶 ID、產品 SKU、通路 ID、購買數量、折扣和訂單日期等資訊。customer_id、product_sku 和 channel_id 欄位作為外部索引鍵，參照對應的主鍵在 customers、products 和 channels 資料表中。這些外部索引鍵建立了資料表之間的關聯。

visitHistory 表結構說明

此表格儲存客戶的存取歷史，包含客戶 ID、通路 ID、存取時間戳和離開時間戳等資訊。customer_id 和 channel_id 欄位作為外部索引鍵，參照對應的主鍵在 customers 和 channels 資料表中。

資料完整性維護

外部索引鍵約束強制執行參照完整性，確保外部索引鍵欄位中的值對應於被參考資料表中的現有值。這有助於維護資料函式庫中資料的完整性和一致性。

營運資料建模的重要性

營運資料建模是分析師的重要技能，即使他們不總是直接負責設計原始資料函式庫結構。瞭解營運資料建模的原理和考慮因素，可以使分析師對整個資料管道有全面的瞭解。這有助於他們瞭解所處理資料的來源和結構，從而更有效地與負責營運層的資料工程師合作。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。