SQL 資料轉換與 DBT 資料平臺建構實務

– 17 分鐘閱讀

SQL 是資料工程的核心工具，本文將深入探討如何運用 SQL 和 DBT 建構現代資料平臺，包含資料轉換流程、資料模型設計、資料品質控管，以及版本控制和 DevOps 的整合。同時，將以 Snowflake 雲端資料函式庫為例，示範如何建立資料函式庫、Schema、表格、檢視，並管理使用者許可權。此外，文章也涵蓋了資料倉儲設計模式，如星形模式、雪花模式和星座模式，並探討如何提升軟體品質、處理技術債務以及敏捷開發的應用。最後，將提供 dbt Cloud 和 Snowflake 建立資料平臺的案例研究，包含 Python 程式碼範例和 Mermaid 語法的架構圖，讓讀者能快速上手實作。

輕量級MDM方法

一種輕量級的MDM方法是使用DBT（Data Build Tool）進行資料管理。DBT是一種根據SQL的資料管理工具，允許使用者定義和管理資料模型、資料轉換和資料品質規則。

儲存歷史資料

在資料管理中，儲存歷史資料是一個重要的方面。歷史資料可以幫助企業瞭解資料的變化和演化，從而做出更好的決策。為了儲存歷史資料，企業可以使用save_history宏。

儲存歷史資料的宏

save_history宏是一種特殊的資料管理工具，允許使用者儲存資料的歷史版本。這個宏可以幫助企業追蹤資料的變化和演化，從而做出更好的決策。

目前歷史資料的宏

目前歷史資料的宏是另一個重要的資料管理工具，允許使用者查詢資料的目前版本和歷史版本。這個宏可以幫助企業瞭解資料的變化和演化，從而做出更好的決策。

軟體品質的提升

軟體品質是企業營運中的另一個重要方面，高品質的軟體可以幫助企業提高效率和降低成本。為了提升軟體品質，企業需要採用有效的技術要求和實施方法。

技術要求

技術要求是軟體品質提升中的關鍵環節，企業需要定義明確的技術要求和標準，以確保軟體的品質和可靠性。

重構和演化模型

重構和演化模型是軟體品質提升中的重要方法，允許企業改進和最佳化軟體模型和程式碼。

處理技術債務

技術債務是軟體品質提升中的另一個重要方面，企業需要採用有效的方法和工具來處理技術債務。

實施實際程式碼和商業規則

實施實際程式碼和商業規則是軟體品質提升中的關鍵環節，企業需要採用有效的方法和工具來實施實際程式碼和商業規則。

替換快照以HIST表

替換快照以HIST表是一種有效的資料管理方法，允許企業儲存和管理資料的歷史版本。

重新命名REF_ABC_BANK_SECURITY_INFO模型

重新命名REF_ABC_BANK_SECURITY_INFO模型是一種有效的資料管理方法，允許企業重新命名和管理資料模型。

處理事實中的孤兒

處理事實中的孤兒是一種有效的資料管理方法，允許企業處理和管理資料中的孤兒。

  flowchart TD
    A[資料管理] --> B[輕量級MDM方法]
    B --> C[儲存歷史資料]
    C --> D[使用save_history宏]
    D --> E[目前歷史資料的宏]
    E --> F[軟體品質提升]
    F --> G[技術要求]
    G --> H[重構和演化模型]
    H --> I[處理技術債務]
    I --> J[實施實際程式碼和商業規則]
    J --> K[替換快照以HIST表]
    K --> L[重新命名REF_ABC_BANK_SECURITY_INFO模型]
    L --> M[處理事實中的孤兒]

圖表翻譯：

此圖表示資料管理和軟體品質提升的流程。資料管理包括輕量級MDM方法、儲存歷史資料、使用save_history宏和目前歷史資料的宏。軟體品質提升包括技術要求、重構和演化模型、處理技術債務、實施實際程式碼和商業規則、替換快照以HIST表、重新命名REF_ABC_BANK_SECURITY_INFO模型和處理事實中的孤兒。

資料倉儲設計模式

資料倉儲是企業資料管理的核心，設計良好的資料倉儲可以大大提升資料分析和商業智慧的效率。以下將介紹一些常見的資料倉儲設計模式和技術要求。

資料倉儲設計模式

資料倉儲設計模式是指設計資料倉儲的架構和組織方式，以滿足企業的資料分析和商業智慧需求。常見的設計模式包括：

  • 星形模式：星形模式是最常見的資料倉儲設計模式，包括一個事實表和多個維度表。
  • 雪花模式：雪花模式是星形模式的變體，包括多個事實表和維度表。
  • 星座模式：星座模式是星形模式和雪花模式的結合，包括多個事實表和維度表。

資料倉儲技術要求

資料倉儲的技術要求包括：

  • 資料來源：資料倉儲需要從各個資料來源中收集資料，包括資料函式庫、檔案、API等。
  • 資料處理：資料倉儲需要對收集到的資料進行處理，包括資料清洗、資料轉換、資料匯總等。
  • 資料儲存：資料倉儲需要將處理好的資料儲存到資料函式庫或檔案中。
  • 資料查詢：資料倉儲需要提供資料查詢功能，允許使用者查詢和分析資料。

資料倉儲設計原則

資料倉儲設計原則包括：

  • 簡單性：資料倉儲設計應該簡單易懂，避免複雜的設計。
  • 靈活性：資料倉儲設計應該靈活，能夠適應企業的變化需求。
  • 可擴充套件性：資料倉儲設計應該可擴充套件，能夠支援企業的成長和發展。

資料倉儲工具

資料倉儲工具包括：

  • 資料倉儲軟體：資料倉儲軟體是指用於設計、建置和管理資料倉儲的軟體，例如Oracle、Microsoft SQL Server等。
  • 資料倉儲框架：資料倉儲框架是指用於建置和管理資料倉儲的框架，例如Apache Hive、Apache Pig等。
  • 資料倉儲服務：資料倉儲服務是指用於提供資料倉儲功能的服務，例如Amazon Redshift、Google BigQuery等。

資料平臺工程入門

資料轉換與資料平臺

在資料工程領域中，資料轉換是一個至關重要的步驟。它涉及將原始資料轉換成更適合分析和報表的格式。為了達到這個目標，資料工程師需要使用各種工具和技術。其中，dbt（data build tool）是一個流行的選擇。dbt允許使用者使用簡單的SQL語言來建構資料平臺，從而簡化資料轉換和分析的過程。

dbt Cloud與資料工程

dbt Cloud是一個根據dbt的雲端平臺，提供了自動化資料轉換、資料品質控制和資料版本控制等功能。它可以幫助資料工程師和分析師更好地合作，共同打造一個簡單、易於維護和未來導向的資料平臺。透過使用dbt Cloud，使用者可以輕鬆地建立一個從資料接收到資料交付的完整資料平臺。

資料模型與資料品質

資料模型是描述資料結構和關係的過程。它涉及了實體、屬性、實體關係圖等概念。資料品質控制則是確保資料的準確性、完整性和一致性的過程。這兩個方面都是建立一個可靠和高效的資料平臺的關鍵組成部分。

資料版本控制和DevOps

資料版本控制是指對資料進行版本管理，以便追蹤資料的變化和修改。DevOps是一種將軟體開發和營運緊密結合的方法論。透過將資料版本控制和DevOps結合起來，資料工程師可以建立一個自動化的資料管道，從而提高資料交付的效率和品質。

案例研究：使用dbt Cloud和Snowflake建立資料平臺

在這個案例研究中，我們將使用dbt Cloud和Snowflake來建立一個簡單的資料平臺。首先，我們需要建立一個Snowflake帳戶和一個dbt Cloud專案。然後，我們可以使用dbt Cloud來定義資料模型、資料轉換和資料品質控制規則。最後，我們可以使用Snowflake來執行資料轉換和分析。

內容解密：

在這個章節中，我們探討了資料轉換、資料模型、資料品質控制和資料版本控制等概念。同時，我們也介紹了dbt Cloud和Snowflake等工具的使用方法。透過這些工具和技術，使用者可以建立一個簡單、易於維護和未來導向的資料平臺。以下是使用dbt Cloud和Snowflake建立資料平臺的步驟：

# 匯入必要的函式庫
import snowflake
from dbt_cloud import DbtCloud

# 建立Snowflake連線
snowflake_conn = snowflake.connector.connect(
    user='你的使用者名稱',
    password='你的密碼',
    account='你的Snowflake帳戶',
    warehouse='你的倉函式庫',
    database='你的資料函式庫',
    schema='你的schema'
)

# 建立dbt Cloud連線
dbt_cloud_conn = DbtCloud(
    api_key='你的dbt Cloud API金鑰',
    api_secret='你的dbt Cloud API金鑰'
)

# 定義資料模型和資料轉換
model = dbt_cloud_conn.create_model(
    name='你的資料模型名稱',
    description='你的資料模型描述',
    columns=[
        {'name': 'column1', 'type': 'string'},
        {'name': 'column2', 'type': 'integer'}
    ]
)

# 執行資料轉換和分析
dbt_cloud_conn.run(
    model=model,
    snowflake_conn=snowflake_conn
)

圖表翻譯：

以下是使用Mermaid語法繪製的資料平臺架構圖：

  graph LR
    A[資料來源] --> B[資料轉換]
    B --> C[資料品質控制]
    C --> D[資料版本控制]
    D --> E[資料分析]
    E --> F[資料視覺化]

這個圖表展示了資料平臺的整體架構，從資料來源到資料視覺化。每個步驟都與前面的步驟相關，形成了一個完整的資料管道。

資料工程的新核心：分析工程

在現代資料架構中，分析工程扮演著核心角色。它涵蓋了資料生命週期的所有階段，從資料收集、處理、儲存到分析和視覺化。分析工程師需要了解資料的來源、品質和結構，以便設計和實作有效的資料處理流程。

使用 DBT 進行資料轉換

DBT（Data Build Tool）是一種流行的資料轉換工具，允許使用者使用 SQL 進行資料轉換和分析。使用 DBT，可以建立資料應用程式，定義資料模型，編寫資料轉換程式碼，並佈署到雲端或本地環境。

撰寫可維護的程式碼

撰寫可維護的程式碼是資料工程中的重要環節。這涉及到編寫清晰、簡潔和易於理解的程式碼，使用適當的命名約定和註解，以便其他開發人員可以輕鬆地理解和維護程式碼。

工作中的維度資料

維度資料是資料倉儲中的重要組成部分。它提供了對資料的額外描述和背景資訊，允許使用者進行更深入的分析和視覺化。使用維度資料，可以建立資料模型，編寫資料轉換程式碼，並將維度資料整合到資料倉儲中。

確保程式碼的一致性

確保程式碼的一致性是資料工程中的重要環節。這涉及到使用標準化的命名約定、註解和程式碼格式，以便其他開發人員可以輕鬆地理解和維護程式碼。

資料可靠性

資料可靠性是資料工程中的重要環節。它涉及到確保資料的準確性、完整性和一致性，以便使用者可以信賴資料的品質。

敏捷開發

敏捷開發是一種軟體開發方法論，強調迭代和增量式開發。使用敏捷開發方法論，可以快速地開發和佈署資料應用程式，同時確保程式碼的品質和可維護性。

協作

協作是資料工程中的重要環節。它涉及到多個開發人員之間的合作和溝通，以便共同完成資料工程專案。

佈署、執行和檔案自動化

佈署、執行和檔案自動化是資料工程中的重要環節。它涉及到自動化資料應用程式的佈署、執行和檔案，以便使用者可以輕鬆地使用和維護資料應用程式。

超越基礎

超越基礎是資料工程中的重要環節。它涉及到使用高階的資料工程技術和工具，以便建立複雜的資料應用程式和資料倉儲。

提高軟體品質

提高軟體品質是資料工程中的重要環節。它涉及到使用高階的軟體工程技術和工具，以便建立高品質的資料應用程式和資料倉儲。

常用案例模式

常用案例模式是資料工程中的重要環節。它涉及到使用預先定義的模式和範本，以便快速地建立資料應用程式和資料倉儲。

下載範例程式碼

下載範例程式碼是資料工程中的重要環節。它涉及到下載和安裝範例程式碼，以便使用者可以輕鬆地學習和使用資料工程技術和工具。

程式碼與書寫風格

在撰寫程式碼時，為了保持一致性和易讀性，需要遵循特定的書寫風格。以下是幾個需要注意的點：

程式碼在文字中的呈現

當在文字中提到程式碼時，應使用特定的格式來區分普通文字。例如，當提到某個函式或變數名稱時，可以使用「函式名稱」的方式來呈現。

程式碼區塊

當需要展示一段程式碼時，應使用特定的標記來區分普通文字。例如：

CREATE TABLE ORDERS (
  ORDER_ID NUMBER,
  CUSTOMER_CODE TEXT,
  TOTAL_AMOUNT FLOAT,
  ORDER_DATE DATE,
  CURRENCY TEXT DEFAULT 'EUR'
);

在這個例子中，程式碼區塊使用「```」標記來開始和結束。

重點程式碼

當需要強調某些程式碼的部分時，應使用特定的格式來區分普通程式碼。例如：

CREATE VIEW BIG_ORDERS AS
SELECT * FROM ORDERS
WHERE TOTAL_AMOUNT > 1000;

在這個例子中，沒有使用特定的格式來強調程式碼的部分。

命令列輸入或輸出

當需要展示命令列輸入或輸出的時候，應使用特定的格式來區分普通文字。例如：

$ mkdir css
$ cd css

在這個例子中，命令列輸入或輸出使用「````」標記來開始和結束。

重要詞彙或新詞彙

當需要強調某些重要詞彙或新詞彙的時候，應使用特定的格式來區分普通文字。例如，新詞彙。

提示或重要注意事項

當需要提示或注意某些重要事項的時候，應使用特定的格式來區分普通文字。例如：

提示或重要注意事項。

資料工程基礎：SQL與資料轉換

資料工程是一個關乎如何建構和管理大規模資料平臺的領域，而SQL（結構化查詢語言）是資料工程師的基本工具。在本章中，我們將從SQL的基礎開始，探討其核心概念和命令，同時也會介紹如何使用Snowflake這個雲端資料函式庫來實踐SQL的應用。

SQL簡介

SQL是一種用於管理關聯式資料函式庫的語言，幾乎所有與資料相關的工作都需要使用SQL。因此，掌握SQL是成為一名合格的資料工程師的必要條件。在本章中，我們將從SQL的基礎開始，包括其核心概念、命令和語法，讓讀者能夠快速上手SQL。

SQL核心概念和命令

SQL的核心概念包括資料函式庫、表格、欄位、記錄等。常用的SQL命令包括SELECT、INSERT、UPDATE、DELETE等。以下是SQL命令的分類：

  • 資料定義語言（DDL）：CREATE、ALTER、DROP等，用於定義和修改資料函式庫結構。
  • 資料操作語言（DML）：INSERT、UPDATE、DELETE等，用於新增、修改和刪除資料。
  • 資料查詢語言（DQL）：SELECT等，用於查詢資料。

Snowflake雲端資料函式庫設定

Snowflake是一個根據雲端的資料函式庫，提供了一個免費的試用期，非常適合用於學習和測試。以下是設定Snowflake資料函式庫的步驟：

  1. 註冊Snowflake帳戶。
  2. 建立新的角色、使用者和資料函式庫。
  3. 組態資料函式庫連線和許可權。

SQL查詢和JOIN子句

SQL查詢是用於從資料函式庫中提取資料的命令。常用的查詢命令包括SELECT、FROM、WHERE、GROUP BY、HAVING等。JOIN子句用於結合多個表格的資料，常用的JOIN型別包括INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN等。

進階功能：視窗函式

視窗函式是SQL中的一種進階功能，允許您對資料進行更複雜的操作。視窗函式可以用於計算排名、累計值、移動平均值等。以下是視窗函式的基本語法：

SELECT 
    欄位1,
    欄位2,
    WINDOW函式(欄位3) OVER (PARTITION BY 欄位4 ORDER BY 欄位5)
FROM 
    表格名稱;

在這個例子中，WINDOW函式可以是ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()、LEAD()等。

SQL 基礎概念與命令

SQL（Structured Query Language）是一種用於管理關係式資料函式庫的語言，於 1970 年代建立，至今已成為資料管理業界的標準。SQL 的核心功能在 1980 年代就已標準化，但它仍然在不斷演進，新增強大的功能以適應資料管理的需求。

SQL 的優點

  • SQL 的核心功能已經標準化，卻仍然在不斷演進。
  • 每個資料函式庫都有其特有的 SQL 方言，但基本邏輯相同，大部分 SQL 程式碼都可以在不同資料函式庫中使用。
  • SQL 的語法簡潔有力，易於理解和使用。
  • SQL 是一種宣告式語言，使用者只需描述想要實作的結果，資料函式庫引擎會自行決定如何實作。

SQL 基礎概念

SQL 的主要目的是允許使用者查詢資料函式庫中的資料，並提供管理資料函式庫的命令。SQL 的核心概念來自於關係式資料函式庫的組織結構：

  • 資料函式庫包含表格，組織在 schema 中。
  • 表格儲存資料在行中，每行有一個值對應每個列。
  • 列有名稱，可以只包含特定資料型別的資料。
  • 許可權被指派給使用者和角色，以規範資料存取。

表格

表格是 SQL 中最核心的概念，因為它包含資料。表格由列和行組成，列代表可以儲存的屬性，行代表例項。每個列定義了資料型別，資料函式庫根據這些定義儲存和管理資料。

SQL 命令

SQL 提供了多種命令用於管理資料函式庫，包括：

  • SELECT：查詢資料。
  • JOIN：合併表格。
  • 分析和視窗函式：進行資料分析。

建立表格

建立表格需要提供表格名稱和定義，包括列名稱和資料型別。資料可以在稍後新增。以下是建立一個訂單表格的範例：

CREATE TABLE ORDERS (
    ORDER_ID NUMBER,
    CUSTOMER_CODE TEXT,
    TOTAL_AMOUNT FLOAT,
    CURRENCY TEXT
);

資料值

所有在一列中的值必須與該列宣告的資料型別相容。NULL是一個特殊值，代表沒有資料，與所有資料型別相容。

SQL 的未來

隨著資料管理的演進，SQL 仍然是一種重要的工具。學習 SQL 不僅能夠幫助您管理資料函式庫，也能夠提升您的資料分析能力。

內容解密：

在這個章節中，我們介紹了 SQL 的基礎概念和命令。SQL 是一種強大的工具，能夠幫助您管理資料函式庫和進行資料分析。透過學習 SQL，您可以提升您的資料管理能力和資料分析能力。

圖表翻譯：

  flowchart TD
    A[SQL 基礎概念] --> B[SQL 命令]
    B --> C[建立表格]
    C --> D[資料值]
    D --> E[SQL 的未來]

這個流程圖展示了 SQL 的基礎概念和命令之間的關係。首先，我們需要了解 SQL 的基礎概念，然後學習 SQL 的命令，包括建立表格和管理資料值。最後，我們可以應用 SQL 的知識來提升資料管理能力和資料分析能力。

Snowflake資料函式倉管理

Snowflake是一種雲端資料函式倉管理系統，提供了一種簡單且強大的方式來管理和分析大規模的資料。以下是Snowflake中的一些基本概念和操作。

資料函式庫和schema

在Snowflake中，資料函式庫（database）是儲存資料的基本單位。一個資料函式庫可以包含多個schema，schema是一個邏輯上的資料容器，包含多個表格和檢視。以下是建立一個新的資料函式庫和schema的範例：

CREATE DATABASE TEST;
CREATE SCHEMA TEST.SOME_DATA;

表格和檢視

表格（table）是儲存資料的基本單位。以下是建立一個新的表格的範例：

CREATE TABLE ORDERS (
  ORDER_DATE DATE,
  CURRENCY TEXT DEFAULT 'EUR'
);

檢視（view）是一個虛擬的表格，根據一個查詢的結果。以下是建立一個新的檢視的範例：

CREATE VIEW BIG_ORDERS AS
SELECT * FROM ORDERS
WHERE TOTAL_AMOUNT > 1000;

使用者和角色

在Snowflake中，使用者（user）和角色（role）是用來控制資料函式庫存取許可權的。使用者代表一個個別的使用者或服務，可以授予存取資料函式庫的許可權。角色（role）是一個命名實體，可以授予特定的許可權。以下是建立一個新的角色和使用者的範例：

CREATE ROLE DBT_SAMPLE_ROLE;
CREATE USER MY_NAME;

許可權管理

在Snowflake中，許可權可以授予使用者和角色。以下是授予使用者一個角色的範例：

GRANT ROLE DBT_SAMPLE_ROLE TO USER MY_NAME;

資料函式庫和schema的管理

Snowflake提供了一種簡單的方式來管理資料函式庫和schema。以下是建立一個新的資料函式庫和schema的範例：

CREATE DATABASE TEST;
CREATE SCHEMA TEST.SOME_DATA;
內容解密：
  • Snowflake是一種雲端資料函式倉管理系統。
  • 資料函式庫是儲存資料的基本單位。
  • Schema是一個邏輯上的資料容器，包含多個表格和檢視。
  • 表格是儲存資料的基本單位。
  • 檢視是一個虛擬的表格，根據一個查詢的結果。
  • 使用者和角色可以用來控制資料函式庫存取許可權。
  • 許可權可以授予使用者和角色。

圖表翻譯：

  graph LR
    A[資料函式庫] --> B[Schema]
    B --> C[表格]
    B --> D[檢視]
    C --> E[資料]
    D --> E
    A --> F[使用者]
    F --> G[角色]
    G --> H[許可權]

在這個圖表中，我們可以看到Snowflake中資料函式庫、schema、表格、檢視、使用者和角色的關係。資料函式庫是儲存資料的基本單位，schema是一個邏輯上的資料容器，包含多個表格和檢視。使用者和角色可以用來控制資料函式庫存取許可權。

SQL命令的分類和使用

SQL命令可以根據其用途分為幾個類別：資料定義語言（DDL）、資料操作語言（DML）、資料查詢語言（DQL）、資料控制語言（DCL）和事務控制語言（TCL）。

輕量級 MDM 方法如 DBT 的興起，反映了資料管理領域對敏捷性、效率和成本效益的追求。透過多維比較分析，DBT 以 SQL 為核心，降低了資料工程師的學習門檻，並能有效管理資料模型、轉換和品質規則，相較於傳統 MDM 方案更具靈活性。然而，DBT 的輕量級特性也意味著在處理極其複雜的資料治理場景時，可能需要整合其他工具或客製化開發，這也是技術團隊需要評估的限制。展望未來，隨著資料平臺工程的發展，預計 DBT 等輕量級工具將與雲端資料倉儲、資料湖等技術更緊密地整合，形成更完整的資料生態系統。玄貓認為，對於追求快速迭代和精益資料管理的企業而言，DBT 值得納入技術選型的考量，並優先應用於核心業務資料的轉換和管理，以最大化其商業價值。

玄貓

玄貓

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