DBT 與 Snowflake 建構資料倉儲架構最佳實務

– 16 分鐘閱讀

在資料爆炸時代，建構一個穩健且可擴充套件的資料倉儲至關重要。本文將引導您使用 dbt（data build tool）和 Snowflake 建構一個高效能的資料倉儲，包含資料載入、轉換、建模、測試、佈署和維護等關鍵步驟。從建立登入表到定義資料來源，再到利用 dbt 進行 ELT 過程，本文提供完整的程式碼範例和最佳實務，協助您打造一個高品質、易維護的資料平臺。同時，我們也將探討資料品質保證的重要性，並示範如何使用 dbt 進行資料測試和驗證，確保資料的正確性、完整性和一致性。最後，我們將討論如何組態 dbt 專案，並建立一個包含資料儲存層、精煉資料層和資料交付層的三層架構，以滿足不同資料分析和報表需求。

資料載入與轉換

在資料分析和轉換的過程中，瞭解如何載入和轉換資料至目標資料函式庫是非常重要的。以下是使用 dbt 和 Snowflake 進行資料載入和轉換的步驟和範例。

步驟 1：建立登入表

首先，需要建立一個登入表（landing table）來存放外部資料。這個表格的結構應該與外部資料的格式相符，以便於資料的載入和轉換。

CREATE SCHEMA PORTFOLIO_TRACKING.SOURCE_DATA;
CREATE OR REPLACE TABLE 
PORTFOLIO_TRACKING.SOURCE_DATA.ABC_BANK_POSITION (
    accountID TEXT,
    symbol TEXT,
    description TEXT,
    exchange TEXT,
    report_date DATE,
    quantity NUMBER(38,0),
    cost_base NUMBER(38,5),
    position_value NUMBER(38,5),
    currency TEXT
);

步驟 2：載入外部資料

接下來，需要載入外部資料到登入表中。這可以使用 Snowflake 的介面或其他工具來完成。

-- 載入外部資料到登入表
COPY INTO PORTFOLIO_TRACKING.SOURCE_DATA.ABC_BANK_POSITION
FROM '@~/ABC_Bank_PORTFOLIO__2021-04-09.csv'
FILE_FORMAT = (TYPE = 'CSV' COMPRESSION = 'AUTO' FIELD_DELIMITER = ',');

步驟 3：定義資料來源

定義登入表為資料來源（source），以便於 dbt 的 ELT 過程。

-- 定義資料來源
SOURCE SOURCE_DATA (
    TABLE = PORTFOLIO_TRACKING.SOURCE_DATA.ABC_BANK_POSITION
);

步驟 4：進行 ELT

使用 dbt 進行 ELT（Extract、Load、Transform）過程，將資料從登入表轉換和載入到目標表格中。

-- 進行 ELT
MODEL PORTFOLIO_TRACKING.TARGET_TABLE (
    TABLE = PORTFOLIO_TRACKING.TARGET_TABLE
) AS
SELECT 
    accountID,
    symbol,
    description,
    exchange,
    report_date,
    quantity,
    cost_base,
    position_value,
    currency
FROM 
    SOURCE_DATA;

圖表翻譯：

此圖示展示了資料載入和轉換的流程，從建立登入表、載入外部資料、定義資料來源，到進行 ELT。

  flowchart TD
    A[建立登入表] --> B[載入外部資料]
    B --> C[定義資料來源]
    C --> D[進行 ELT]
    D --> E[目標表格]

內容解密：

以上步驟和範例展示瞭如何使用 dbt 和 Snowflake 進行資料載入和轉換。首先，需要建立登入表來存放外部資料。接下來，需要載入外部資料到登入表中。定義登入表為資料來源，以便於 dbt 的 ELT 過程。最後，使用 dbt 進行 ELT，將資料從登入表轉換和載入到目標表格中。

Snowflake 中的資料載入和來源定義

資料載入

為了將 CSV 檔案中的資料載入 Snowflake，需要先定義檔案格式。檔案格式的定義包括了欄位分隔符、記錄分隔符、欄位選擇性包圍字元等屬性。以下是檔案格式的定義範例：

RECORD_DELIMITER = '\n'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '\042'
TRIM_SPACE = FALSE
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE
ESCAPE = 'NONE'
ESCAPE_UNENCLOSED_FIELD = '\134'
DATE_FORMAT = 'AUTO'
TIMESTAMP_FORMAT = 'AUTO'
NULL_IF = ('\\N')

這些屬性分別定義了記錄的分隔符、是否跳過首行、欄位是否可以被雙引號包圍、是否修剪空白字元、是否在欄位數量不匹配時報錯、逃逸字元、未包圍欄位的逃逸字元、日期格式、時間戳格式和空值的替代字元。

Snowflake 中的資料載入

載入資料的過程包括選擇倉函式庫、選擇檔案、選擇檔案格式和載入資料。以下是載入資料的步驟：

  1. 登入 Snowflake 帳戶，選擇要使用的角色。
  2. 點選左上角的「Databases」圖示，然後選擇要載入資料的資料函式庫。
  3. 點選「Load table」按鈕，開始載入資料的過程。
  4. 選擇要使用的倉函式庫，然後點選「Next」按鈕。
  5. 選擇要載入的檔案，然後點選「Next」按鈕。
  6. 選擇檔案格式，然後點選「Load」按鈕。

來源定義

來源定義是使用 dbt 的 YAML 檔案來定義的。以下是來源定義的範例：

version: 2
sources:
  - name: abc_bank
    database: PORTFOLIO_TRACKING
    schema: SOURCE_DATA
    tables:
      - name: ABC_BANK_POSITION

這個 YAML 檔案定義了一個名為 abc_bank 的來源，該來源位於 PORTFOLIO_TRACKING 資料函式庫的 SOURCE_DATA 結構中，且包含了一個名為 ABC_BANK_POSITION 的表格。

測試來源

可以使用 dbt 的 source 函式來測試來源。以下是測試來源的範例：

{{ source('abc_bank', 'ABC_BANK_POSITION') }}

這個函式會傳回來源中的資料，可以用來測試來源是否正確定義。

使用dbt進行資料轉換和建模

dbt（data build tool）是一種強大的資料轉換和建模工具，允許使用者使用SQL語言定義和轉換資料。以下是使用dbt進行資料轉換和建模的步驟：

步驟1：定義資料源

首先，需要定義資料源。資料源可以是任何支援SQL的資料函式庫，例如Snowflake、PostgreSQL等。在dbt中，資料源被稱為「source」。以下是定義資料源的範例：

SELECT *
FROM {{ source('abc_bank', 'ABC_BANK_POSITION') }}

這個範例定義了一個名為「ABC_BANK_POSITION」的資料源，來自「abc_bank」資料函式庫。

步驟2：建立dbt模型

接下來，需要建立dbt模型。dbt模型是一個SQL檔案，定義了資料的轉換和建模。以下是建立dbt模型的範例：

SELECT
  *
, POSITION_VALUE - COST_BASE as UNREALIZED_PROFIT
, UNREALIZED_PROFIT / COST_BASE as UNREALIZED_PROFIT_PCT
FROM {{ source('abc_bank', 'ABC_BANK_POSITION') }}

這個範例建立了一個名為「POSITION_ABC_BANK」的dbt模型，定義了資料的轉換和建模。

步驟3：佈署dbt模型

最後，需要佈署dbt模型。佈署dbt模型需要使用dbt命令列工具，執行以下命令：

dbt run

這個命令會將dbt模型佈署到資料函式庫中，並執行資料的轉換和建模。

步驟4：檢視資料線age

dbt提供了一個資料線age功能，允許使用者檢視資料的轉換和建模過程。以下是檢視資料線age的範例：

dbt lineage

這個命令會顯示資料的轉換和建模過程，包括資料源、dbt模型和資料目的地。

步驟5：使用dbt進行資料轉換和建模

dbt提供了一個強大的資料轉換和建模功能，允許使用者使用SQL語言定義和轉換資料。以下是使用dbt進行資料轉換和建模的範例：

SELECT
  *
, POSITION_VALUE - COST_BASE as UNREALIZED_PROFIT
, UNREALIZED_PROFIT / COST_BASE as UNREALIZED_PROFIT_PCT
FROM {{ source('abc_bank', 'ABC_BANK_POSITION') }}

這個範例使用dbt進行資料轉換和建模，定義了資料的轉換和建模過程。

內容解密：

dbt是一個強大的資料轉換和建模工具，允許使用者使用SQL語言定義和轉換資料。dbt模型是一個SQL檔案，定義了資料的轉換和建模。佈署dbt模型需要使用dbt命令列工具，執行以下命令：dbt run。dbt提供了一個資料線age功能，允許使用者檢視資料的轉換和建模過程。

圖表翻譯：

  graph LR
  A[資料源] -->|dbt模型|> B[資料轉換和建模]
  B -->|dbt命令列工具|> C[資料函式庫]
  C -->|資料線age|> D[資料轉換和建模過程]

這個圖表顯示了dbt的資料轉換和建模過程，包括資料源、dbt模型、dbt命令列工具、資料函式庫和資料線age。

使用dbt進行資料建模和佈署

dbt（data build tool）是一種強大的資料建模和佈署工具，允許使用者定義和管理複雜的資料轉換和載入過程。以下是使用dbt進行資料建模和佈署的步驟：

建立dbt模型

首先，需要建立一個dbt模型，該模型定義了資料的結構和轉換規則。可以使用dbt的指令碼語言來定義模型，例如：

{{
  config(
    materialized = 'table',
    alias = 'my_table'
  )
}}

SELECT * FROM my_source_table

這個模型定義了一個名為my_table的表，該表是從my_source_table中選取所有列的結果。

執行dbt模型

建立模型後，需要執行dbt模型以生成資料。可以使用dbt run指令來執行模型，例如：

dbt run

這個指令會執行所有定義的模型，並生成相應的資料表。

組態dbt專案

dbt專案需要組態以指定資料來源、目標schema等資訊。可以使用dbt_project.yml檔案來組態專案，例如：

name: 'my_project'
version: '1.0.0'
config-version: 2

profile: 'default'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
target-path: "target"
clean-targets:
  - "target"
  - "dbt_packages"

這個組態檔案指定了專案的名稱、版本、資料來源和目標schema等資訊。

佈署dbt模型

佈署dbt模型需要使用dbt deploy指令，例如：

dbt deploy

這個指令會將模型佈署到指定的目標schema中。

使用Mermaid圖表

可以使用Mermaid圖表來視覺化dbt模型的執行流程，例如：

  flowchart TD
    A[dbt模型] --> B[資料來源]
    B --> C[資料轉換]
    C --> D[資料載入]
    D --> E[目標schema]

這個圖表展示了dbt模型的執行流程，從資料來源到目標schema。

圖表翻譯

這個Mermaid圖表展示了dbt模型的執行流程，從資料來源到目標schema。圖表中的每個節點代表了一個步驟，例如資料來源、資料轉換、資料載入和目標schema。圖表中的箭頭代表了資料的流向，從資料來源到目標schema。

DBT 專案組態與資料函式倉管理

在 DBT 專案中，組態檔案（如 dbt_project.yml）扮演著重要的角色，定義了模型（model）的材化（materialization）方式、schema 等重要設定。在這篇文章中，我們將探討如何組態 DBT 專案，以便在 Snowflake 資料函式庫中建立模型。

組態模型材化

在 dbt_project.yml 檔案中，我們可以定義模型的材化方式。例如，以下組態將模型材化為 view：

models:
  portfolio_tracking:
    +materialized: view

而以下組態將模型材化為 table：

models:
  portfolio_tracking:
    refined:
      +materialized: table

注意 + 符號是可選的，僅用於提高可讀性。

組態 schema

我們也可以定義模型的 schema。例如，以下組態將模型建立在 REFINED schema 中：

models:
  portfolio_tracking:
    refined:
      +schema: REFINED

模型佈署與資料函式倉管理

當我們執行 dbt run 命令時，DBT 會根據組態檔案建立模型。如果模型已經存在，DBT 不會自動刪除舊有的模型。因此，需要手動刪除舊有的模型，以保持資料函式庫的清潔。

在開發環境中，手動管理模型是可接受的。但是在 QA、TEST 或 PROD 環境中，需要自動化的管理模型，以確保變更的一致性與正確性。

自動化資料函式倉管理

DBT 提供了自動化資料函式倉管理的功能，稱為資料函式庫遷移（database migration）。這個功能可以自動化地將資料函式庫從一個狀態遷移到另一個狀態。

在未來的章節中，我們將探討如何使用 DBT 進行自動化資料函式倉管理。

程式碼範例

以下是使用 DBT 進行模型材化與 schema 組態的程式碼範例：

# dbt_project.yml
models:
  portfolio_tracking:
    +materialized: view
    refined:
      +materialized: table
      +schema: REFINED

-- POSITION_ABC_BANK.sql
{{
  config(
    materialized='table',
    schema='REFINED'
  )
}}

SELECT * FROM ...

注意：以上程式碼僅為範例，實際使用時需要根據您的資料函式庫結構與需求進行修改。

資料倉儲架構組態

在討論了模型生成的位置後，現在是時候思考整體架構並開始組態使其成為現實。讓我們看看如何組織三個層次：資料儲存層、精煉資料層和資料交付層。

資料儲存層

我們將教您使用兩種方法實作這一層：使用 dbt 快照和僅插入的方式將資料寫入歷史表。在兩種情況下，我們都會將快照或模型組態到自己的 schema 中。主要區別在於快照是全域性的，意味著每個環境都使用相同的快照，並儲存在快照資料夾中，而歷史表則使用正常的模型儲存在臨時資料夾中，因此是每個環境特有的，允許更多的自由度和可擴充套件性。

精煉資料層

我們已經組態了精煉資料夾，以便在其下生成的模型將在精煉 schema 中生成。

資料交付層

資料交付層是使用一個或多個資料倉儲實作的。每個資料倉儲的模型都在其自己的資料夾中建立，在資料倉儲資料夾下，並且每個資料倉儲都在資料函式庫中建立自己的 schema。

讓我們準備組態以設定所有層次。模型和快照組態都可以儲存在主組態檔案 dbt_project.yml 中，因此請開啟並更新它，如下所示：

models:
  portfolio_tracking:
    +materialized: view

  staging: # 資料儲存層
    +materialized: view
    +schema: STAGING

  refined: # 精煉資料層
    +materialized: view
    +schema: REFINED

  marts: # 資料交付層
    portfolio: # 一個具體的資料倉儲
      +materialized: table
      +schema: MART_PORTFOLIO

  snapshots:
    portfolio_tracking:

這個組態檔案定義了我們的資料倉儲架構，包括資料儲存層、精煉資料層和資料交付層。每個層次都有其自己的 schema 和物化化設定，以確保資料的正確儲存和交付。

內容解密：

上述組態檔案使用 YAML 格式定義了資料倉儲架構。models 節點下定義了各個層次的組態，包括 staging、refined 和 marts。每個層次都有其自己的 materialized 和 schema 設定，以控制資料的儲存和交付方式。

圖表翻譯：

  flowchart TD
    A[資料儲存層] --> B[精煉資料層]
    B --> C[資料交付層]
    C --> D[資料倉儲]
    D --> E[資料交付]

這個圖表展示了資料倉儲架構的流程，從資料儲存層到精煉資料層，然後到資料交付層，最終到達資料倉儲和資料交付。

資料倉儲與資料品質保證

在進行資料倉儲的過程中，資料品質是非常重要的。資料品質保證是確保資料正確、完整、一致和可靠的過程。這裡，我們將介紹如何使用 dbt 進行資料品質保證。

資料品質保證的重要性

資料品質保證是確保資料倉儲中的資料正確、完整、 一致和可靠的過程。這是因為資料倉儲中的資料是從各個不同的來源收集而來的，可能存在錯誤、不完整或不一致的資料。因此，資料品質保證是非常重要的。

使用 dbt 進行資料品質保證

dbt 是一個資料倉儲工具，提供了多種功能，包括資料品質保證。以下是使用 dbt 進行資料品質保證的步驟：

  1. 定義資料品質標準：定義資料品質標準，包括資料的正確性、完整性、一致性和可靠性。
  2. 建立測試：建立測試，包括 generic 測試和 singular 測試。generic 測試是使用 dbt 提供的預設測試，例如 not_null、unique 等。singular 測試是使用 SQL 查詢進行的測試。
  3. 組態測試：組態測試，包括定義測試的引數和執行測試的順序。
  4. 執行測試：執行測試，包括執行 generic 測試和 singular 測試。
  5. 分析結果：分析測試結果，包括檢視測試結果和進行問題的診斷和修復。

資料品質保證的最佳實踐

以下是資料品質保證的最佳實踐：

  1. 定期執行測試：定期執行測試，以確保資料品質。
  2. 使用自動化工具：使用自動化工具，例如 dbt，進行資料品質保證。
  3. 定義明確的資料品質標準：定義明確的資料品質標準，以確保資料品質。
  4. 進行問題的診斷和修復：進行問題的診斷和修復，以確保資料品質。

報表日期與投資組合分析

玄貓提取的報表日期是投資組合分析的關鍵。從這個日期開始，直到有變化出現之前，都是有效的。因此，報表日期（REPORT_DATE）欄位不能為空，因為我們需要知道資訊的日期來進行分析。

報表日期欄位分析

REPORT_DATE欄位的測試包括：

  • 不為空（not_null）：這是必要的，因為我們需要知道資訊的日期。

持有證券數量

投資組閤中的證券數量（QUANTITY）是另一項重要資訊。這個欄位也不能為空，因為知道持有證券的數量是必要的。

持有證券數量欄位分析

QUANTITY欄位的測試包括：

  • 不為空（not_null）：這是必要的，因為知道持有證券的數量是必要的。

成本基礎

成本基礎（COST_BASE）是指投資組閤中持有的證券的成本。這個欄位也不能為空，因為我們需要知道成本基礎來計算投資組合的收益。

成本基礎欄位分析

COST_BASE欄位的測試包括：

  • 不為空（not_null）：這是必要的，因為知道成本基礎是必要的。

投資組合價值

投資組合價值（POSITION_VALUE）是指投資組閤中持有的證券的價值。這個欄位也不能為空，因為我們需要知道投資組合價值來計算投資組合的收益。

投資組合價值欄位分析

POSITION_VALUE欄位的測試包括：

  • 不為空（not_null）：這是必要的，因為知道投資組合價值是必要的。
  flowchart TD
    A[報表日期] --> B[持有證券數量]
    B --> C[成本基礎]
    C --> D[投資組合價值]
    D --> E[投資組合分析]

圖表翻譯：

這個流程圖展示了投資組合分析的流程。首先，報表日期是投資組合分析的基礎。然後，持有證券數量和成本基礎是必要的資訊。最後，投資組合價值是投資組合分析的結果。

內容解密：

這個內容解釋了投資組合分析的流程和必要的資訊。報表日期、持有證券數量、成本基礎和投資組合價值都是必要的欄位。這些欄位的測試包括不為空的測試，以確保投資組合分析的正確性。

資料測試與驗證

在資料處理的過程中，測試與驗證是非常重要的步驟。它們可以幫助我們確保資料的品質和正確性，同時也可以讓我們更好地理解資料的結構和內容。

資料測試的目的

資料測試的主要目的就是要確保資料符合我們的預期和需求。這包括了資料的格式、內容、品質等方面。透過測試，我們可以發現資料中可能存在的問題和錯誤，從而進行糾正和改善。

資料測試的方法

資料測試的方法有很多種，包括了單元測試、整合測試、系統測試等。單元測試是指對資料中的個別單元進行測試，例如對某一列資料進行測試。整合測試是指對多個單元進行測試，例如對多個表格進行測試。系統測試是指對整個系統進行測試，例如對資料函式庫進行測試。

資料驗證的目的

資料驗證的主要目的就是要確保資料的正確性和完整性。這包括了資料的格式、內容、品質等方面。透過驗證，我們可以確保資料是否符合我們的預期和需求。

資料驗證的方法

資料驗證的方法也有很多種，包括了手動驗證和自動驗證。手動驗證是指人工對資料進行驗證，例如對資料進行查核和核對。自動驗證是指使用工具和程式對資料進行驗證，例如使用資料驗證工具進行驗證。

dbt 中的測試和驗證

在 dbt 中，測試和驗證是非常重要的步驟。dbt 提供了多種測試和驗證工具和方法，包括了 dbt test 命令和 dbt validate 命令。

dbt test 命令

dbt test 命令是用於執行測試的。它可以執行單元測試、整合測試和系統測試等。透過這個命令，我們可以確保資料的品質和正確性。

dbt validate 命令

dbt validate 命令是用於驗證資料的。它可以驗證資料的格式、內容、品質等方面。透過這個命令，我們可以確保資料的正確性和完整性。

實際應用

在實際應用中，測試和驗證是非常重要的步驟。它們可以幫助我們確保資料的品質和正確性，同時也可以讓我們更好地理解資料的結構和內容。

範例

以下是 dbt 中的測試和驗證範例：

version: 2
models:
  - name: POSITION_ABC_BANK
    description: The positions we have in the ABC Bank portfolio.
    columns:
      - name: UNREALIZED_PROFIT
        description: The unrealized profit on the position.
        tests:
          - not_null
      - name: UNREALIZED_PROFIT_PCT
        description: The unrealized profit percentage on the position.
        tests:
          - not_null

在這個範例中，我們定義了一個模型叫做 POSITION_ABC_BANK，並且定義了兩個欄位：UNREALIZED_PROFIT 和 UNREALIZED_PROFIT_PCT。對於每個欄位，我們都定義了一個測試，叫做 not_null。這個測試可以確保欄位中的資料不為空。

寫維護性程式碼

在這個章節中，我們將繼續前一章的例子，並引導您組態 dbt 和撰寫基本但功能齊全的程式碼，以建立我們的參考架構的三個層次：暫存/儲存、精煉資料和交付資料倉儲。 我們將從設定 dbt 開始，然後將原則付諸實踐，建立和組態我們的參考架構的層次。 在每一步，我們將分析我們撰寫的模型的關鍵元素，以將原始來源資料轉換為資料倉儲中的事實和維度的可用資訊。事實告訴我們發生了什麼，而維度提供了關於事實中涉及的實體的額外描述資訊。首先，我們將為事實表建立管道，然後在下一章中，我們將為維度建立管道，討論它們之間的區別。 我們還將引入 dbt 的快照功能，允許您輕鬆地儲存實體的更改，並以稱為緩慢變化維度型別二的形式儲存它們。這使我們的平臺可以捕捉來源資料及其更改，從而自給自足。 透過這個章節，您將能夠使用 dbt 的基本功能建立一個工作的資料平臺，具有合理的架構和清晰的組織。 本章將探討以下主題：

從資料載入、轉換、建模、佈署、測試到最後的維護性程式碼撰寫，本文涵蓋了建構完整資料倉儲流程的關鍵步驟。透過 dbt 與 Snowflake 的整合，我們展示瞭如何有效地管理資料流程，從原始資料的落地到最終報表的產出。

深入分析各階段的技術細節，我們不僅關注程式碼的實作，更強調了資料品質的重要性。從定義資料源、建立模型、組態 dbt 專案到資料測試與驗證，每個環節都旨在確保資料的正確性、完整性以及一致性。尤其在資料品質保證方面，本文提出了使用 generic 測試和 singular 測試的策略，並建議定期執行測試和匯入自動化工具，以維持資料的可靠性。

此外，本文也探討了資料倉儲架構的組態，包含資料儲存層、精煉資料層和資料交付層，並闡述瞭如何利用 dbt 快照功能儲存資料的歷史狀態，實作緩慢變化維度。最後，本文強調了撰寫可維護程式碼的重要性，並以建構事實表和維度表為例，說明如何將原始資料轉換為可分析的資訊。

展望未來，隨著資料量的持續增長和商業需求的變化，建構一個穩健且可擴充套件的資料倉儲將變得更加重要。持續整合與持續佈署的實踐、更進階的資料測試方法以及資料治理策略，都將是未來值得深入研究的方向。玄貓認為，掌握 dbt 和 Snowflake 等現代資料工具，並搭配良好的資料工程實務，將能有效提升企業的資料分析能力，從而做出更明智的商業決策。

玄貓

玄貓

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