資料建模由淺入深實務

– 10 分鐘閱讀

從傳統的 ETL 流程到現代的 ELT 架構，資料處理方式不斷演變，以滿足日益增長的資料分析需求。Airflow 作為工作流程管理平台，結合 dbt 的資料轉換能力，有效提升了資料管道的效率和可靠性。資料建模作為資料分析的根本，透過標準化和結構化的設計，確保資料的完整性和一致性，為後續分析提供可靠的資料基礎。SQL 和 dbt 則提供了強大的工具和框架，協助開發者構建和維護高效的資料模型，並透過測試和檔案功能提升模型的可靠性和可維護性。

資料建模流程解析：從概念到實踐

資料建模是建構有效率資料函式庫的關鍵步驟，它能確保資料的結構化和組織化，以支援有效的分析。資料建模流程通常分為三個階段：概念階段、邏輯階段和實體階段。概念階段著重於理解業務需求和定義實體及其關係，邏輯階段將概念模型轉換為邏輯結構，而實體階段則關注資料函式庫的物理設計和實作。以書籍出版商為例，概念階段會定義書籍、作者和類別等實體，以及它們之間的關係，例如書籍與作者的多對多關係，以及書籍與類別的一對多關係。這些關係可以透過實體關係圖 (ERD) 來視覺化呈現，以便更好地理解和溝通資料函式庫的結構。

現代資料處理：從傳統ETL到ELT的演變

在資料分析的世界中，資料處理是一個關鍵步驟。傳統的ETL（提取、轉換、載入）流程已經被廣泛使用多年，但近年來，ELT（提取、載入、轉換）正逐漸成為新的趨勢。ELT提供更大的靈活性，能夠支援比傳統ETL正規化更廣泛的資料應用。

ELT的優勢與挑戰

ELT的主要優勢在於其靈活性，能夠在目標系統中直接進行各種資料轉換和即時洞察。這使得組織能夠更快速地從資料中取得可行的洞察，並適應不斷變化的分析需求。然而，ELT也帶來了更高的儲存和攝取成本，因為它需要儲存原始或最小轉換的資料。許多企業認為這些成本是合理的，因為ELT為他們的操作帶來了巨大的價值。

傳統ETL流程的侷限性

傳統的ETL流程通常複雜、耗時，需要專門的技能來開發、實施和維護。它們還需要大量的手動編碼和資料操作，使得它們容易出錯且難以擴充套件。此外，這些流程通常缺乏靈活性，無法適應不斷變化的業務需求或新的資料來源。

使用SQL和儲存過程進行ETL/ELT

過去，某些資料平台使用儲存過程在關係型資料函式倉管理系統（RDBMS）中進行ETL。儲存過程是預先準備好的SQL程式碼，可以儲存在資料函式庫引擎中，以便重複使用。下面是一個簡單的儲存過程示例，用於從一個表中提取資料，轉換資料，並將其載入到另一個表中。

示例1-1：SQL儲存過程

CREATE PROCEDURE etl_example AS
BEGIN
    -- 從源表中提取資料
    SELECT * INTO #temp_table FROM source_table;
    
    -- 轉換資料
    UPDATE #temp_table
    SET column1 = UPPER(column1),
        column2 = column2 * 2;
    
    -- 將資料載入到目標表中
    INSERT INTO target_table
    SELECT * FROM #temp_table;
END

內容解密：

  1. CREATE PROCEDURE etl_example AS BEGIN ... END：建立一個名為etl_example的儲存過程。
  2. SELECT * INTO #temp_table FROM source_table;：從source_table中提取所有資料並儲存在臨時表#temp_table中。
  3. UPDATE #temp_table SET column1 = UPPER(column1), column2 = column2 * 2;：將column1的值轉換為大寫，並將column2的值加倍。
  4. INSERT INTO target_table SELECT * FROM #temp_table;：將#temp_table中的資料載入到target_table中。

使用ETL工具

ETL工具是軟體應用程式，透過提供視覺化介面、軟體開發工具包（SDK）或具有預包裝程式碼和工件的程式設計函式庫，加速構建攝取和轉換管道的過程。Airflow是一個流行的開源平台，用於管理和排程資料管道。下面是一個簡單的Airflow DAG示例。

示例1-2：Airflow DAG

from airflow import DAG
from airflow.operators.bash_operator import BashOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'me',
    'start_date': datetime(2022, 1, 1),
    'depends_on_past': False,
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
}

dag = DAG(
    'simple_dag',
    default_args=default_args,
    schedule_interval=timedelta(hours=1),
)

task1 = BashOperator(
    task_id='print_date',
    bash_command='date',
    dag=dag,
)

內容解密：

  1. from airflow import DAG：匯入Airflow的DAG類別。
  2. default_args = {...}：定義預設引數，包括所有者、開始日期、重試次數等。
  3. dag = DAG('simple_dag', default_args=default_args, schedule_interval=timedelta(hours=1))：建立一個名為simple_dag的DAG，每小時執行一次。
  4. task1 = BashOperator(task_id='print_date', bash_command='date', dag=dag)：建立一個任務，使用BashOperator執行date命令。

Airflow 與 dbt 在資料管道管理中的協同作用

在資料分析領域，工作流程的管理與排程是確保資料處理流程順暢的關鍵。Apache Airflow 和 dbt 是兩個在資料處理和分析領域中廣泛使用的開源工具，它們分別在工作流程協調和資料轉換方面發揮著重要作用。

Airflow：工作流程的協調與排程

Airflow 是一個強大的工作流程管理平台，能夠以程式設計的方式建立、排程和監控工作流程。以下是一個簡單的 Airflow DAG 定義範例：

from datetime import datetime, timedelta
from airflow import DAG
from airflow.operators.bash_operator import BashOperator

default_args = {
    'owner': 'airflow',
    'depends_on_past': False,
    'start_date': datetime(2023, 3, 20),
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
}

dag = DAG(
    'simple_dag',
    default_args=default_args,
    schedule_interval=timedelta(hours=1),
)

task1 = BashOperator(
    task_id='print_date',
    bash_command='date',
    dag=dag,
)

task2 = BashOperator(
    task_id='sleep',
    bash_command='sleep 5',
    retries=3,
    dag=dag,
)

task1 >> task2

內容解密：

  • 此範例定義了一個名為 simple_dag 的 DAG，每小時執行一次。
  • task1 執行 date 命令，輸出當前日期和時間。
  • task2 執行 sleep 5 命令，使任務暫停五秒，並設定重試次數為 3 次。
  • task1 >> task2 表示 task2 依賴於 task1，只有在 task1 成功完成後才會執行 task2。

dbt：資料轉換的最佳實踐

dbt 是一個開源的命令列工具，專注於簡化和最佳化資料轉換和建模的過程。它提供了一套最佳實踐和規範，使得資料轉換變得更加簡單和可控。

以下是一個簡單的 dbt 模型範例，用於計算公司的總收入：

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

SELECT 
    SUM(orders.revenue) AS total_revenue
FROM {{ ref('orders') }} AS orders

內容解密：

  • 此模型定義了一個名為 total_revenue 的資料表，透過對 orders 表中的 revenue 欄位求和來計算總收入。
  • {{ config(materialized='table') }} 指定了模型的物化方式為表。
  • {{ ref('orders') }} 用於參照名為 orders 的模型或表。

Airflow 與 dbt 的協同作用

當 Airflow 與 dbt 結合使用時，可以實作資料管道的完整管理和自動化。Airflow 可以用於排程 dbt 的執行，而 dbt 則負責執行資料轉換任務。這種結合使得資料管道的管理變得更加高效和可靠。

資料建模在分析中的關鍵角色

在當今資料驅動的世界中，組織越來越依賴資料分析來獲得有價值的洞察並做出明智的決策。資料建模在這個過程中扮演著至關重要的角色，為結構化和組織資料提供堅實的基礎，以支援有效的分析。此外，瞭解資料建模和標準化的概念對於實作分析的全部潛力以及從複雜的資料集中獲得可行的洞察至關重要。

資料建模的基礎

資料建模是關於定義系統內資料實體的結構、關係和屬性。資料建模的一個重要方面是資料的標準化。資料標準化是一種消除資料冗餘和提高資料完整性的技術。它涉及將資料分解為邏輯單元並將其組織到單獨的表中，這減少了資料重複並提高了整體資料函式庫效率。標準化確保資料以結構化和一致的方式儲存，這對於準確的分析和可靠的結果至關重要。

資料標準化的重要性

透過將資料分解為更小的邏輯單元，標準化減少了資料冗餘並提高了資料完整性。例如，在一個未經標準化的資料函式庫中，客戶資訊可能在多個表中重複出現。如果客戶的地址發生變化，則需要在多個地方更新，這增加了出錯的風險。標準化透過將客戶資訊儲存在單獨的表中，避免了這種冗餘，從而簡化了資料維護。

-- 未標準化的客戶訂單表
CREATE TABLE 客戶訂單 (
    訂單ID INT,
    客戶名稱 VARCHAR(255),
    客戶地址 VARCHAR(255),
    訂單日期 DATE
);

-- 標準化後的客戶表和訂單表
CREATE TABLE 客戶 (
    客戶ID INT PRIMARY KEY,
    客戶名稱 VARCHAR(255),
    客戶地址 VARCHAR(255)
);

CREATE TABLE 訂單 (
    訂單ID INT PRIMARY KEY,
    客戶ID INT,
    訂單日期 DATE,
    FOREIGN KEY (客戶ID) REFERENCES 客戶(客戶ID)
);

內容解密：

上述範例展示瞭如何透過標準化來減少資料冗餘。在未標準化的客戶訂單表中，每個訂單都重複儲存了客戶名稱和地址。經過標準化後，客戶資訊被儲存在客戶表中，而訂單表僅包含與訂單相關的資訊和對應的客戶ID。這種設計避免了資料冗餘，並確保了資料的一致性。

資料建模在分析中的作用

資料建模為建立分析模型提供了堅實的基礎。透過瞭解實體和資料結構之間的關係，分析師可以設計出有效的模型，捕捉相關資訊並支援所需的分析目標。換句話說，一個設計良好的資料模型使分析師能夠執行複雜的查詢、連線表並聚合資料，以產生有意義的洞察。

SQL和dbt在資料建模中的優勢

本文強調SQL和dbt是維持有效的分析工程專案的兩項核心技術，這同樣適用於設計和實施有效的資料模型。SQL能夠定義表、運算元據並透過其強大的查詢功能檢索資訊。它的無與倫比的靈活性和多功能性使其成為構建和維護資料模型的絕佳工具，讓使用者能夠清晰地表達複雜的關係並輕鬆存取特定的資料子集。

dbt在這裡扮演著核心角色，將資料建模提升到一個全新的高度。它為構建和協調複雜的資料管道提供了一個全面的框架。在這個框架內，使用者可以定義轉換邏輯、應用必要的業務規則，並建立可重用的模組化程式碼元件，即所謂的模型。值得注意的是，dbt的功能遠不止於此：它與版本控制系統無縫整合，使協作變得輕而易舉，並確保資料模型保持一致性、可稽核性和無需重複的工作。

dbt的測試和檔案功能

SQL和dbt在資料建模中的另一個關鍵方面是它們對測試和檔案的重視。dbt提供了驗證查詢，用於檢查資料品質、資料完整性和對定義規則的遵循，從而增強了對模型輸出的信心。此外，dbt在檔案方面表現出色，為分析師和利益相關者提供了寶貴的資源。這種檔案簡化了對驅動資料模型的底層邏輯和假設的理解，從而增強了透明度並促進了有效的協作。

-- 使用dbt定義一個簡單的模型
{{ config(
    materialized='table'
) }}

SELECT 
    客戶ID,
    COUNT(訂單ID) AS 訂單數量,
    SUM(訂單金額) AS 總訂單金額
FROM 
    {{ ref('訂單') }}
GROUP BY 
    客戶ID;

內容解密：

這個dbt模型範例展示瞭如何根據訂單表建立一個新的表，該表匯總了每個客戶的訂單數量和總訂單金額。{{ config(materialized='table') }}組態指示dbt將這個模型物化為一個物理表。{{ ref('訂單') }}是一個參照，指向另一個名為訂單的dbt模型或源表。這種模組化的設計使得模型的構建和維護變得更加靈活和可管理。

資料建模基礎

在建立資料函式庫藍圖之前，必須深入瞭解業務的運作模式、術語和流程，以確保資料模型的準確性和實用性。透過訪談、檔案分析和流程研究來收集需求，可以深入瞭解業務需求和資料需求。在此過程中，應著重於自然的溝通方式——書面語言，以簡潔明瞭的句子表達業務事實，避免歧義和誤解。將複雜的句子分解為簡單的結構，包括主詞、動詞和直接受詞，有助於準確捕捉業務現實。

資料建模的三個基本階段

完成了解階段後，接下來是資料函式庫建模的三個基本步驟：

  • 概念階段
  • 邏輯階段
  • 物理階段

這些步驟構成了建立強健且有組織的資料函式庫結構的基礎。以下將以一家書籍出版商為例來說明這個過程。

概念階段的建模

概念階段的資料函式庫建模需要幾個關鍵步驟。首先，需要確定資料函式庫的目的和目標，明確它需要解決的問題或滿足的需求。接下來是透過訪談利害關係人和領域專家來收集需求，以全面瞭解所需的資料元素、關係和約束。然後進行實體分析和定義，識別資料函式庫中需要表示的關鍵物件或概念，並定義它們的屬性和關係。

簡單的概念設計範例

  • 類別（Category）：代表書籍的類別。屬性可能包括 category_id 和 category_name。

實體間的關係

接下來是識別實體之間的關係。在資料函式庫設計中，實體之間可能存在多種型別的關係，其關係型別被稱為關係的基數。例如：

  • 一對一關係：一個實體與另一個實體一一對應。
  • 一對多關係：一個實體可以對應多個另一個實體。例如，一個 Category 可以有多本 Book，但每本 Book 只屬於一個 Category。
  • 多對一關係：多個實體對應到一個實體。例如，多本 Book 可以由同一位 Author 撰寫，但這裡更常見的是多對多關係。
  • 多對多關係：一個實體可以對應多個另一個實體，反之亦然。例如，一本 Book 可以有多位 Author，而一位 Author 也可以撰寫多本 Book。

在我們的例子中，識別出了兩個明確的關係：

  1. 書籍-類別（Book-Category）關係：建立書籍和類別之間的聯絡。這是一種一對多的關係，因為一本文只能屬於一個類別，而一個類別可以包含多本文。
  2. 書籍-作者（Book-Author）關係：建立書籍和作者之間的聯絡。這是一種多對多的關係，因為一本文可以有多位作者，而一位作者也可以撰寫多本文。

使用 ERD 視覺化資料函式庫設計

有了實體、屬性和關係的概念後，我們可以使用實體關係圖（ERD）來視覺化地表示這些資訊。ERD 是用來描述資料函式庫結構的重要工具，可以清晰地展示實體之間的關係和基數。

@startuml
note
  無法自動轉換的 Plantuml 圖表
  請手動檢查和調整
@enduml

圖表翻譯： 此圖示展示了書籍管理系統的ERD模型。其中包含三個主要實體：Book、Author 和 Category，以及它們之間的關係。Book 和 Author 之間是多對多的關係，因此引入了一個關聯表 BookAuthor 來表示這種關係。Category 和 Book 之間是一對多的關係，表示一個類別可以包含多本文。

玄貓 BlackCat

玄貓 BlackCat

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