2025年01月29日玄貓（BlackCat）

dbt建構資料倉儲粒度與實作

本文闡述如何利用 dbt 建構資料倉儲，包含定義業務事實的粒度、dbt 專案設定、staging 模型建立、維度與事實表設計，以及程式碼實作細節與 YAML 檔案組態。文章涵蓋銷售資料和網站效能分析等案例，並提供程式碼範例和最佳實踐，以確保資料倉儲的結構化和一致性。

資料工程資料倉儲

dbt 資料粒度維度模型事實表 Staging YAML

在資料分析中，定義資料粒度至關重要，它決定了資料在資料倉儲中的精細程度。本文將探討如何使用 dbt 建構資料倉儲，並以銷售資料和網站效能分析為例，說明如何定義適當的粒度。同時，文章也涵蓋了 dbt 專案設定、staging 模型建立、維度和事實表設計，以及程式碼實作細節和 YAML 檔案組態，以確保資料倉儲的結構化和一致性，並提供程式碼範例和最佳實踐，協助讀者理解 dbt 的使用和資料倉儲的建構。

建立資料倉儲的粒度與dbt實作

在分析資料建模的最後階段，我們需要定義業務事實的粒度（granularity）。粒度決定了資料在維度資料模型中被捕捉和分析的詳細程度。選擇適當的粒度對於確保資料模型能夠有效地支援組織的分析需求和目標至關重要。

定義業務事實的粒度

定義業務事實的粒度需要考慮分析的具體需求，並在捕捉足夠的細節和避免過度複雜之間取得平衡。所選擇的粒度應提供足夠的資訊以進行有意義的分析，同時保持資料的可管理性和效能。

銷售資料的粒度

在銷售資料的背景下，粒度被確定為交易層級，捕捉個體客戶的購買記錄：fct_purchase_history。這種粒度的選擇使得組織能夠進行詳細的銷售模式分析，例如檢查個別交易、識別客戶行為趨勢以及進行產品級別的分析。

網站效能分析的粒度

對於網站效能分析的需求，粒度被選擇為存取層級，收集個體客戶存取和進入平台的通路資訊：fct_visit_history。透過這種詳細程度，組織能夠瞭解客戶參與度、跟蹤不同通路的流量模式，並衡量不同行銷活動或網站功能的有效性。

粒度的聚合

除了定義粒度之外，還可以根據需要確定較低粒度的分析單位，例如每日、每週或每月的聚合資料。聚合資料可以提供更簡潔的表示形式，同時提供有價值的洞察。這種方法減少了資料量並簡化了分析，使得識別更廣泛的趨勢、季節性模式或跨多個維度的整體效能變得更加容易。

使用dbt建立資料倉儲

完成分析資料建模階段後，我們將進入開發資料倉儲的階段。資料倉儲作為結構化和整合化資料的中央儲存函式庫，支援組織內部的強大報告、分析和決策過程。

dbt專案設定

我們的目標是根據分析資料建模階段制定的藍圖開發所有dbt模型，這些模型將作為設計和構建資料倉儲的基礎。同時，我們還將開發所有必要的引數化YAML檔案，以確保我們能夠利用ref()和source()函式，最終使我們的程式碼更加DRY（Don’t Repeat Yourself）。

建立staging模型區域

除了開發資料模型外，建立一致的命名約定在資料倉儲中也是至關重要的。這些命名約定提供了一種標準化的方法來命名錶格、列和其他資料函式庫物件，確保了整個資料基礎設施的清晰度和一致性。下表展示了用於使用dbt構建資料倉儲的命名約定：

約定	欄位型別	描述
stg	表格/CTE	臨時表格或CTE
dim	表格	維度表格
fct	表格	事實表格

實作細節

程式碼規範與結構

在建立資料倉儲時，我們需要嚴格遵循dbt的最佳實踐，並確保所有的模型和YAML檔案都按照預定的命名約定進行組織。這不僅提高了程式碼的可讀性和可維護性，也使得未來的擴充套件和修改變得更加容易。

-- 示例：建立一個staging模型
WITH stg_purchase_history AS (
  SELECT 
    transaction_id,
    customer_id,
    product_id,
    transaction_date,
    amount
  FROM 
    {{ source('sales', 'purchase_history') }}
)
SELECT 
  *
FROM 
  stg_purchase_history

內容解密：

上述SQL程式碼展示瞭如何建立一個名為stg_purchase_history的staging模型。首先，它從原始的purchase_history表中選擇必要的欄位，然後將結果儲存在臨時表中。這種方法有助於簡化後續的資料處理和分析過程。

使用dbt建立全方位資料倉儲的實務

在建立第一個模型之前，我們必須確保dbt專案已正確設定，並且具備適當的資料夾結構。在這個階段，我們將保持簡單，只建立staging和marts目錄。因此，一旦初始化了dbt專案，請建立指定的資料夾。models資料夾目錄應該如範例6-9所示。

建立初始專案和資料夾

範例6-9展示了全方位資料倉儲的models目錄結構：

root/
├─ models/
│ ├─ staging/
│ ├─ marts/
├─ dbt_project.yml

建立staging層的YAML檔案

現在我們已經建立了初始專案和資料夾，下一步是建立staging層的YAML檔案。根據我們之前討論的YAML檔案最佳實踐，我們將為sources和models分別建立一個YAML檔案。目前，我們將重點放在source YAML檔案上。這個檔案必須位於staging目錄內，並且應該如範例6-10所示。

範例6-10展示了 _omnichannel_raw_sources.yml 檔案的組態：

version: 2
sources:
  - name: omnichannel
    database: analytics-engineering-book
    schema: omnichannel_raw
    tables:
      - name: Channels
      - name: Customers
      - name: Products
      - name: VisitHistory
      - name: PurchaseHistory

內容解密：

此YAML檔案定義了來源資料表的結構，包括資料函式庫名稱、schema名稱和五個資料表（Channels、Customers、Products、VisitHistory和PurchaseHistory）。這使得我們可以使用source()函式來存取原始資料。

建立staging模型

現在，讓我們開始建立staging模型。我們的目標是為每個來源資料表建立一個staging模型。範例6-11至6-15展示了建立每個staging模型的程式碼片段。

stg_channels.sql

with raw_channels AS (
  SELECT 
    channel_id,
    channel_name,
    CREATED_AT,
    UPDATED_AT
  FROM {{ source("omnichannel","Channels") }}
)
SELECT 
  *
FROM raw_channels

內容解密：

此模型從Channels資料表中提取資料，並將其暫存於raw_channels CTE中。然後，從raw_channels中選取所有欄位。

stg_customers.sql、stg_products.sql、stg_purchase_history.sql和stg_visit_history.sql的建立方式與stg_channels.sql類別似，差異在於選取的欄位和來源資料表不同。

建立staging層的YAML組態檔案

在成功建立staging模型之後，下一步是設定staging層的YAML檔案。這個檔案將作為組態檔案，參照staging模型並指定其執行順序和相依性。範例6-16展示了YAML檔案的組態。

範例6-16展示了 _omnichannel_raw_models.yml 檔案的組態：

version: 2
models:
  - name: stg_customers
  - name: stg_channels
  - name: stg_products
  - name: stg_purchase_history
  - name: stg_visit_history

內容解密：

此YAML檔案列出了所有建立的staging模型，指定了其執行順序和相依性。

建立維度模型

現在，我們將繼續建立維度模型。維度模型代表業務實體及其屬性，是資料倉儲的重要組成部分。這些模型捕捉了提供事實資料背景的描述性資訊，使得分析更加深入。

dim_channels.sql

with stg_dim_channels AS (
  SELECT 
    channel_id AS nk_channel_id,
    channel_name AS dsc_channel_name,
    created_at AS dt_created_at,
    updated_at AS dt_updated_at
  FROM {{ ref("stg_channels") }}
)
SELECT 
  {{ dbt_utils.generate_surrogate_key(["nk_channel_id"]) }} AS sk_channel,
  *
FROM stg_dim_channels

內容解密：

此模型從stg_channels中提取資料，並將其轉換為維度模型的格式。使用dbt_utils.generate_surrogate_key函式產生代理鍵sk_channel。

其他維度模型的建立方式與dim_channels.sql類別似，例如dim_customers.sql、dim_products.sql等。

圖表翻譯：

此階段的流程可以透過Plantuml圖表呈現，如下所示：

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 圖表翻譯：

rectangle "提取" as node1
rectangle "轉換" as node2
rectangle "載入" as node3

node1 --> node2
node2 --> node3

@enduml

圖表翻譯： 此圖表展示了從原始資料表到資料倉儲的流程。首先，從原始資料表中提取資料並暫存於staging模型中。然後，將staging模型中的資料轉換為維度模型的格式。最後，將維度模型中的資料載入到資料倉儲中。

建立資料倉儲中的維度表與事實表

在資料倉儲的建設過程中，維度表與事實表的建立是至關重要的步驟。本文將探討如何使用dbt（Data Build Tool）來建立這些表，並對相關程式碼進行詳細解析。

維度表的建立

維度表是用於描述事實表中資料的背景資訊。在本案例中，我們建立了四個維度表：dim_channels、dim_customers、dim_products和dim_date。

`dim_channels`、`dim_customers`和`dim_products`

這些維度表分別對應於不同的業務實體，如銷售通路、客戶和產品。它們的建立過程相似，都是從相應的staging表中檢索資料，並轉換成所需的結構。

-- dim_channels.sql
SELECT 
    {{ dbt_utils.generate_surrogate_key(['channel_id']) }} AS sk_channel,
    channel_id AS nk_channel_id,
    channel_name,
    channel_type
FROM {{ ref('stg_channels') }}

內容解密：

使用dbt_utils.generate_surrogate_key函式為維度表生成代理鍵（surrogate key），這裡以channel_id作為自然鍵（natural key）。
從stg_channels表中選擇需要的欄位，如channel_id、channel_name和channel_type。
ref函式用於參照其他dbt模型或表，這裡參照了stg_channels。

`dim_date`

dim_date維度表是透過dbt_date套件中的get_date_dimension函式生成的，這個函式可以根據指定的日期範圍建立日期維度表。

-- dim_date.sql
{{ dbt_date.get_date_dimension("2022-01-01", "2024-12-31") }}

內容解密：

dbt_date.get_date_dimension函式用於生成日期維度表，引數指定了日期範圍（2022-01-01至2024-12-31）。
這個函式會自動建立必要的欄位和資料，涵蓋了日期的不同屬性。

安裝必要的dbt套件

為了成功構建我們的專案，需要安裝dbt_utils和dbt_date套件。在dbt_packages.yml檔案中新增以下組態：

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  - package: calogica/dbt_date
    version: [">=0.7.0", "<0.8.0"]

然後執行dbt deps和dbt build命令來安裝套件並構建專案。

事實表的建立

事實表代表了可衡量的、數值的資料，捕捉了業務事件或交易。本案例中，我們建立了兩個事實表：fct_purchase_history和fct_visit_history。

`fct_purchase_history`

這個事實表旨在跟蹤不同通路的銷售業績。透過將銷售資料與相應的維度表進行連線，我們可以獲得有關收入產生和不同銷售通路有效性的寶貴見解。

-- fct_purchase_history.sql
WITH stg_fct_purchase_history AS (
    SELECT 
        customer_id AS nk_customer_id,
        product_sku AS nk_product_sku,
        channel_id AS nk_channel_id,
        quantity AS mtr_quantity,
        discount AS mtr_discount,
        CAST(order_date AS DATE) AS dt_order_date
    FROM {{ ref('stg_purchase_history') }}
)
SELECT 
    COALESCE(dcust.sk_customer, '-1') AS sk_customer,
    COALESCE(dchan.sk_channel, '-1') AS sk_channel,
    COALESCE(dprod.sk_product, '-1') AS sk_product,
    fct.dt_order_date AS sk_order_date,
    fct.mtr_quantity,
    fct.mtr_discount,
    dprod.mtr_unit_price,
    ROUND(fct.mtr_quantity * dprod.mtr_unit_price, 2) AS mtr_total_amount_gross,
    ROUND(fct.mtr_quantity * dprod.mtr_unit_price * (1 - fct.mtr_discount), 2) AS mtr_total_amount_net
FROM stg_fct_purchase_history AS fct
LEFT JOIN {{ ref('dim_customers') }} AS dcust ON fct.nk_customer_id = dcust.nk_customer_id
LEFT JOIN {{ ref('dim_channels') }} AS dchan ON fct.nk_channel_id = dchan.nk_channel_id
LEFT JOIN {{ ref('dim_products') }} AS dprod ON fct.nk_product_sku = dprod.nk_product_sku

內容解密：

使用CTE（Common Table Expression）從stg_purchase_history表中選擇需要的欄位，並進行必要的轉換。
將轉換後的資料與相應的維度表進行連線，以取得代理鍵。
計算額外的指標，如總金額（毛額和淨額）。

`fct_visit_history`

這個事實表用於跟蹤存取和跳出率，以瞭解客戶參與度和網站效能。透過連線存取資料與客戶和通路維度表，我們可以識別驅動流量的通路。

-- fct_visit_history.sql
WITH stg_fct_visit_history AS (
    SELECT 
        customer_id AS nk_customer_id,
        channel_id AS nk_channel_id,
        CAST(visit_timestamp AS DATE) AS sk_date_visit,
        CAST(bounce_timestamp AS DATE) AS sk_date_bounce,
        CAST(visit_timestamp AS DATETIME) AS dt_visit_timestamp,
        CAST(bounce_timestamp AS DATETIME) AS dt_bounce_timestamp
    FROM {{ ref('stg_visit_history') }}
)
SELECT 
    COALESCE(dcust.sk_customer, '-1') AS sk_customer,
    COALESCE(dchan.sk_channel, '-1') AS sk_channel,
    fct.sk_date_visit,
    fct.sk_date_bounce,
    fct.dt_visit_timestamp,
    fct.dt_bounce_timestamp,
    DATE_DIFF(dt_bounce_timestamp, dt_visit_timestamp, MINUTE) AS mtr_length_of_stay_minutes
FROM stg_fct_visit_history AS fct
LEFT JOIN {{ ref('dim_customers') }} AS dcust ON fct.nk_customer_id = dcust.nk_customer_id
LEFT JOIN {{ ref('dim_channels') }} AS dchan ON fct.nk_channel_id = dchan.nk_channel_id

內容解密：

從stg_visit_history表中選擇需要的欄位，並進行必要的轉換。
連線轉換後的資料與客戶和通路維度表，以取得代理鍵。
計算停留時間（分鐘）作為額外的指標。

建立資料倉儲的粒度與dbt實作

定義業務事實的粒度

銷售資料的粒度

網站效能分析的粒度

粒度的聚合

使用dbt建立資料倉儲

dbt專案設定

建立staging模型區域

實作細節

程式碼規範與結構

內容解密：

使用dbt建立全方位資料倉儲的實務

建立初始專案和資料夾

建立staging層的YAML檔案

內容解密：

建立staging模型

stg_channels.sql

內容解密：

stg_customers.sql、stg_products.sql、stg_purchase_history.sql和stg_visit_history.sql的建立方式與stg_channels.sql類別似，差異在於選取的欄位和來源資料表不同。

建立staging層的YAML組態檔案

內容解密：

建立維度模型

dim_channels.sql

內容解密：

圖表翻譯：

建立資料倉儲中的維度表與事實表

維度表的建立

dim_channels、dim_customers和dim_products

內容解密：

dim_date

內容解密：

安裝必要的dbt套件

事實表的建立

fct_purchase_history

內容解密：

fct_visit_history

內容解密：

`dim_channels`、`dim_customers`和`dim_products`

`dim_date`

`fct_purchase_history`

`fct_visit_history`