事實表格的分析工程
如何根據實際分析使用案例，為事實表格建模資料。
資料建模的目標是讓資料檢索 *快速* (對於處理查詢的引擎)，並且 *容易* (對於撰寫這些查詢的人員)。
大多數資料倉儲實務旨在強調速度。**分析工程** (由 dbt 普及的一個術語，有時與 **全端分析** 這個術語捆綁在一起) 是為了可用性而建模資料的過程。即使您不這樣稱呼它，每當您需要為其他人整理精選資料集、區隔或指標 或 儀表板 時，您可能都在實踐分析工程。
本教學課程向您展示如何將分析工程方法應用於資料倉儲層級的資料集，更具體地說，是應用於一種稱為 *事實表格* 的資料集。
簡介
**維度表格** 包含特定時間點的資料快照，例如您在工作日結束時擁有的部分完成的馬克杯數量。
| time | total mugs |
|---------------------|------------|
| 2022-08-16 17:30:00 | 3 |
**事實表格** 包含資訊的歷史記錄，例如您整天喝咖啡的速率。
| time | mug | coffee remaining |
|---------------------|----------|------------------|
| 2022-08-16 08:00:00 | 1 | 100% |
| 2022-08-16 08:01:00 | 1 | 0% |
| 2022-08-16 09:00:00 | 2 | 100% |
| 2022-08-16 12:00:00 | 3 | 100% |
| 2022-08-16 12:30:00 | 3 | 99% |
| 2022-08-16 17:30:00 | 3 | 98% |
事實表格和維度表格一起用於 星狀結構 (或密切相關的 雪花結構)，以組織資料倉儲中的資訊。
在下列情況下，您可能會想要建立事實表格
-
您的資料來源 (產生資料的系統，例如您的應用程式資料庫) 僅透過覆蓋先前的快照來儲存資訊的目前快照。
-
您正在建立資料集，以為您的客戶提供 嵌入式分析。獨立的事實表格非常適合自助式分析，因為它們可以涵蓋 廣泛的使用案例，而無需依賴聯結。
但在我們開始之前，讓我們為您每日的咖啡因總量再添一杯馬克杯吧——我們還有很多事情要做！
| time | total mugs |
|---------------------|------------|
| CURRENT_TIMESTAMP() | n+1 |
概觀
在本教學課程中，我們將使用一個維度表格，我們將其稱為 account，就像您可能從 CRM 取得的維度表格一樣。假設這個 account 維度表格儲存我們客戶的目前狀態，而目前狀態由我們的應用程式更新。
account 表格看起來像這樣
| id | country | type | status |
|------------------|-------------|------------|-----------|
| 941bfb1b2fdab087 | Croatia | Agency | Active |
| dbb64fd5c56e7783 | Singapore | Partner | Active |
| 67aae9a2e3dccb4b | Egypt | Partner | Inactive |
| ced40b3838dd9f07 | Chile | Advertiser | Test |
| ab7a61d256fc8edd | New Zealand | Advertiser | Inactive |
為了根據 account 設計事實表格結構描述，我們需要考慮人們可能會詢問哪些關於客戶帳戶隨時間變更的分析問題。由於 account 表格包含 status 欄位，我們可以回答諸如以下的問題
為了從儲存在 account 中的資料建立 fact_account，我們將撰寫一個 SQL 腳本來
- 使用今天的
account資料初始化fact_account。 - 取得
account中列的快照 (假設它由另一個系統更新)。 - 將每天的
account快照與fact_account中的歷史資料進行比較。 - 為自前一天快照以來已變更的每個帳戶，在
fact_account中插入一個新列。
為了檢查我們的事實表格在實務上是否有用，我們將使用 Metabase 設定它，並嘗試回答我們所有三個範例分析問題。
本教學課程的最後一節讓您了解，當您的事實表格擴展以容納更多歷史記錄 (和更多問題！) 時，迭代會是什麼樣子。
如何跟著本教學課程進行
如果您想要將以下步驟應用於您自己的資料，我們建議您使用由您的來源系統定期更新的維度表格，以及您選擇的資料庫或資料倉儲。
在本教學課程中，我們使用 Firebolt 來測試 他們的合作夥伴驅動程式 與 Metabase。Firebolt 是一個資料倉儲，它採用一些 稍微修改過的 SQL DDL 以 設計用於加速查詢運行的格式 載入資料。
如果您使用自己的資料跟著進行，您的 SQL 語法可能與範例程式碼不完全相同。如需更多資訊，您可以查看 常見 SQL 方言的參考指南。
設計事實表格
基本事實結構描述
首先，我們將為我們的事實表格草擬一個結構描述，我們將其稱為 fact_account。將結構描述放在視覺參考中 (如下表所示) 可以更輕鬆地驗證 fact_account 是否會支援我們想要執行的查詢 (即人們想要回答的分析問題)。視覺參考也可以作為以後對 fact_account 不熟悉的人的有用資源。
在此範例中，我們將保留來自 account 的所有原始欄位。如果我們需要省略任何欄位，我們始終可以透過 Metabase 中的 資料模型頁面 隱藏這些欄位。與一開始就從我們的結構描述中排除太多欄位相比，在 Metabase 中隱藏欄位的破壞性較小，因為每次我們需要還原欄位時，都必須重新產生結構描述。
我們也將包含一個名為 updated_at 的新欄位，以指示將列插入表格的時間戳記。在實務上，updated_at 可以用於近似帳戶變更的日期或時間。
此新增功能基於以下假設：除了 id 之外，所有 account 屬性都可以變更。例如，給定帳戶的狀態可以從 Active 變更為 Inactive，或者帳戶的 Type 從 Partner 變更為 Advertiser。
基本 fact_account 結構描述範例
| Column name | Data type | Description | Expected values |
|-----------------|-----------|--------------------------------------------------------------|---------------------------------------------------|
| id | varchar | The unique id of a customer account. | 16 character string |
| status | varchar | The current status of the account. | Active, Inactive, or Test |
| country | varchar | The country where the customer is located. | Any of the "English short names" used by the ISO. |
| type | varchar | The type of account. | Agency, Partner, or Advertiser |
| updated_at | datetime | The date a row was added to the table | |
更好的事實結構描述
為了檢查結構描述的可用性，我們將為我們的一個分析問題撰寫一個虛擬 SQL 查詢
-- How many new accounts have been added each month?
WITH new_account AS (
SELECT
id,
MIN(updated_at) AS first_added_at -- Infer the account creation date
FROM
fact_account
GROUP BY
id
)
SELECT
DATE_TRUNC('month', first_added_at) AS report_month,
COUNT(DISTINCT id) AS new_accounts
FROM
new_account
GROUP BY
report_month;
目前的 fact_account 結構描述需要額外步驟才能取得 (或估計) 每個帳戶的「建立」時間戳記 (在本例中，對於在我們開始保留歷史記錄之前已處於活動狀態的帳戶，估計是必要的)。
如果我們只是在 fact_account 結構描述中為帳戶的建立日期新增一個欄位，回答有關「新帳戶」的問題會容易得多。但是新增欄位將增加表格的複雜性 (某人理解和查詢表格所需的時間)，以及 SQL 腳本的複雜性 (更新表格所需的時間)。
為了協助我們決定是否值得在我們的 fact_account 結構描述中新增一個欄位，我們將考慮建立時間戳記是否可以用於其他類型的關於帳戶的分析問題。
帳戶的建立時間戳記也可以用於計算
- 帳戶的年齡。
- 重要事件的時間 (例如帳戶流失或變成非使用中所需的天數)。
這些指標可以應用於有趣的 使用案例，例如 減少客戶流失 或 計算 LTV，因此可能值得包含在 fact_account 中。
我們將新增欄位 is_first_record 以保持我們的結構描述精簡。此欄位將標記與帳戶在事實表格中的最早條目相對應的列。
如果您計劃建立一個事實表格來簡化自助服務 (以便事實表格包含通常在維度表格中捕獲的資訊)，您也可以為 is_latest_record 新增一個欄位。此欄位將協助人們篩選 fact_account 以取得目前資料 (除了歷史資料之外)，以便他們可以使用同一個表格快速回答諸如：「我們迄今為止有多少個活動帳戶？」之類的問題。
在首次推出自助服務時，使用此慣例可能會導致查詢速度變慢，但更容易採用 (因此人們不必記住事實表格和維度表格之間的聯結)。
更好的 fact_account 結構描述
| Column name | Data type | Description | Expected values |
|-----------------|-----------|---------------------------------------------------------------------|---------------------------------------------------|
| id | varchar | The unique id of a customer account. | 16 character string |
| status | varchar | The current status of the account. | "Active", "Inactive", "Test", or "Trial" |
| country | varchar | The country where the customer is located. | Any of the "English short names" used by the ISO. |
| type | varchar | The type of account. | "Agency", "Partner", or "Advertiser" |
| ... | ... | ... | |
| updated_at | datetime | The date a row was added to the table | |
| is_first_record | boolean | TRUE if this is the first record in the table for a given id | |
| is_latest_record| boolean | TRUE if this is the most current record in the table for a given id | |
初始化事實表格
為了實作事實結構描述，我們將首先建立一個空的 fact_account 表格，以儲存 account 表格在一段時間內的快照。
我們正在使用 Firebolt 資料倉儲，因此我們將從 Firebolt 主控台 建立事實表格。我們將選取 **SQL 工作區** > **新增腳本**，並撰寫
-- Create an empty fact_account table in your data warehouse.
CREATE FACT TABLE IF NOT EXISTS fact_account
(
id varchar
status varchar
country varchar
type varchar
updated_at timestamp
is_first_record boolean
is_latest_record boolean
);
請注意，Firebolt 的 DDL 包含 FACT 關鍵字 (可以在標準 SQL DDL 中排除)。
如果您在用於擷取資料的同一個 SQL 腳本中建立事實表格，您可以按照可折疊右側邊欄上的 **匯入腳本** 按鈕中的 註解完善的 SQL 腳本範本 進行操作。
接下來，我們將使用目前 account 表格中的所有內容來填入 fact_account。您可以將這些陳述式包含在建立事實表格的同一個 SQL 腳本中
-- Put an initial snapshot of data from "account" into "fact_account".
-- Add "quality of life" columns to make the data model nicer to work with.
INSERT INTO fact_account (
SELECT
*,
CURRENT_TIMESTAMP() AS updated_at,
is_first_record = TRUE,
is_latest_record = TRUE
FROM
account);
增量載入事實表格
為了使用來自 account 的定期快照更新 fact_account，我們將撰寫另一個 SQL 腳本來
- 查詢
account以取得資料的目前快照。 - 將目前資料與
fact_account中上次更新的資料進行比較。 - 為自上次快照以來已變更的記錄，在
fact_account中插入列。
您需要將此 SQL 腳本儲存並排程在您的資料倉儲之外，使用像 dbt 或 Dataform 之類的工具。如需更多資訊，請查看我們在「學習」中的 ETL、ELT 和反向 ETL 教學課程的 轉換資料 章節。
-- Add the latest snapshot from the account table.
-- This assumes that account is regularly updated from the source system.
INSERT INTO fact_account
SELECT
*,
is_first_record = TRUE
FROM
account
WHERE
id = id
AND CURRENT_TIMESTAMP() <> updated_at ();
-- Update the rows from the previous snapshot, if applicable.
WITH previous_snapshot AS (
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS row_number
FROM
fact_account
WHERE
is_first_record = TRUE)
UPDATE
fact_account fa
SET
is_latest_record = FALSE
FROM
previous_snapshot ps
WHERE
ps.row_number = 2;
使用常見使用案例測試事實表格
以下是我們預期 fact_account 在開始使用 account 的每日快照填入後的外觀
| id | country | type | status | updated_at | is_first_record | is_latest_record |
|------------------|-----------|------------|-----------|---------------------|-----------------|------------------|
| 941bfb1b2fdab087 | Croatia | Agency | Active | 2022-02-04 09:02:09 | TRUE | FALSE |
| 941bfb1b2fdab087 | Croatia | Partner | Active | 2022-07-10 14:46:04 | FALSE | TRUE |
| dbb64fd5c56e7783 | Singapore | Partner | Active | 2022-05-10 02:42:07 | TRUE | FALSE |
| dbb64fd5c56e7783 | Singapore | Partner | Inactive | 2022-07-14 14:46:04 | FALSE | TRUE |
| ced40b3838dd9f07 | Chile | Advertiser | Test | 2022-07-02 06:22:34 | TRUE | TRUE |
現在，我們可以將我們的事實表格放入 Metabase，以查看它在回答我們的範例分析問題方面的表現如何
設定 Metabase
如果您尚未將資料庫設定與 Metabase 搭配使用，您可以在幾分鐘內完成設定
- 下載並安裝 Metabase，或註冊 Metabase Cloud 免費試用版。
- 新增包含您的事實資料表的資料庫。
如果您依照本教學課程使用 Firebolt，您將需要用來登入 Firebolt 主控台的使用者名稱和密碼，以及資料庫名稱 (列於主控台首頁)。
- 從 Metabase 首頁的右上角，按一下 New > Question。
新帳戶
假設我們想知道上個月新增的帳戶總數。
這類結果適用於自助式使用案例，例如
- 「靜態數字」視覺化圖表。
- 進度列視覺化圖表，用於衡量上個月的新帳戶數是否達到目標數字。
人們可以使用 Metabase 的查詢產生器自助式取得「過去一個月新增的帳戶數」等指標，步驟如下
- 前往 New > Question。
- 選取
fact_account作為起始資料。 - 從 Pick the metric you want to see，選取 Number of distinct values of > ID。
- 從 Filter 按鈕，按一下 Is First Record 並選取「Is」(預設設定)，值為「True」。
- 從 Filter 按鈕，按一下 Status 並選取「Is Not」，值為「Test」。
- 按一下 Last Updated At 並選取「Last Month」。
或者，他們可以使用類似以下的程式碼片段，從任何 SQL IDE (包括 Metabase SQL 編輯器) 自助式取得相同的值
SELECT
COUNT(DISTINCT id) AS new_accounts
FROM
fact_account
WHERE
is_first_record = TRUE
AND status <> "Test"
AND DATE_TRUNC('month', updated_at) = DATE_TRUNC('month', CURRENT_TIMESTAMP) - INTERVAL '1 MONTH';
已流失帳戶
除了新增到我們業務的新帳戶之外，我們也想追蹤已流失的帳戶。這次，我們不會將結果限制為上個月的資料，而是取得像這樣的每月摘要表
| report_month | churned_accounts |
|--------------|------------------|
| 2022-05-01 | 23 |
| 2022-06-01 | 21 |
| 2022-07-01 | 16 |
這類結果可以協助人們自助式取得
- 長條圖或折線圖，以繪製每個
report_month的churned_accounts變化。 - 「趨勢」視覺化圖表，以顯示每月已流失帳戶數的百分比變化。
- 已儲存的問題或模型，可以依據
report_month加入其他表格。這讓使用者可以在與其他欄位的計算中使用churned_accounts欄位，這些欄位在fact_account中找不到。
人們可以按照以下步驟，從 Metabase 的查詢產生器自助式取得「每月已流失帳戶」摘要表
- 前往 New > Question。
- 選取
fact_account作為起始資料。 - 從 Pick the metric you want to see，選取 Number of distinct values of > ID。
- 從 Pick a column to group by，選取 Updated At: Month。
- 按一下 Filter 按鈕。
- 按一下 Status 並選取 True。
他們也可以使用類似以下的查詢，從任何 SQL IDE (包括 Metabase 的 SQL 編輯器) 取得結果
SELECT
DATE_TRUNC('month', updated_at) AS report_month,
COUNT(DISTINCT id) AS churned_accounts
FROM
fact_account
WHERE
status = 'inactive';
進階使用案例：同類群組表
同類群組表是設計完善的事實資料表可以支援的最複雜使用案例之一。這些表格衡量作為帳戶存續期間函數的流失率，可用於識別特別成功或不成功的客戶群組。
我們想要取得像這樣的結果
| age | churned_accounts | total_accounts | churn_rate |
| --- | ---------------- | -------------- | ---------- |
| 1 | 21 | 436 | = 21 / 436 |
| 2 | 26 | 470 | = 26 / 470 |
| 3 | 18 | 506 | = 18 / 506 |
由於這是進階使用案例，我們將著重於向您展示如何將 fact_account 表格的「形狀」變更為同類群組表。這些步驟可以在 Metabase 中透過建立一系列彼此關聯的已儲存 SQL 問題來完成。
-
建立一個已儲存的問題，以取得每個帳戶的
first_added_month和churned_month範例結果
| id | first_added_month | churned_month | | ---------------- | ----------------- | ------------- | | 941bfb1b2fdab087 | 2022-02-01 | null | | dbb64fd5c56e7783 | 2022-05-01 | 2022-07-01 | | 67aae9a2e3dccb4b | 2022-07-01 | null |SQL 程式碼片段
SELECT id, CASE WHEN is_first_record = TRUE THEN DATE_TRUNC('month', updated_at) END AS first_added_month, CASE WHEN status = 'inactive' THEN DATE_TRUNC('month', updated_at) ELSE NULL END AS churned_month FROM fact_account; -
將步驟 1 中的已儲存問題加入至每個月份各有一列的欄位。您可以在 SQL 中透過產生序列來執行此操作 (或者您可以使用資料倉儲中的現有表格)。請注意月份的聯結條件。
範例結果
| id | first_added_month | churned_month | report_month | age | is_churned | |------------------|-------------------|---------------|--------------|-----|------------| | dbb64fd5c56e7783 | 2022-05-01 | 2022-07-01 | 2022-05-01 | 1 | FALSE | | dbb64fd5c56e7783 | 2022-05-01 | 2022-07-01 | 2022-06-01 | 2 | FALSE | | dbb64fd5c56e7783 | 2022-05-01 | 2022-07-01 | 2022-07-01 | 3 | TRUE |SQL 程式碼片段
WITH date_series AS ( SELECT * FROM GENERATE_SERIES('2022-01-01'::date, '2022-12-31'::date, '1 month'::interval) report_month ) SELECT *, age, CASE WHEN s.churned_month = d.report_month THEN TRUE ELSE FALSE END AS is_churned FROM step_1 s FULL JOIN date_series d ON d.report_month >= s.first_added_month AND (d.report_month <= s.churned_month OR d.report_month <= CURRENT_TIMESTAMP::date); -
現在可以從查詢產生器將步驟 2 中的結果彙整為最終結果 (您可以使用自訂欄位計算流失率)。
範例結果
| age | churned_accounts | total_accounts | churn_rate | | --- | ---------------- | -------------- | ---------- | | 1 | 21 | 436 | = 21 / 436 | | 2 | 26 | 470 | = 26 / 470 | | 3 | 18 | 506 | = 18 / 506 |SQL 程式碼片段
SELECT age, COUNT(DISTINCT CASE WHEN is_churned = TRUE THEN id END) AS churned_accounts, COUNT(DISTINCT CASE WHEN is_churned = FALSE THEN id END) AS total_accounts, churned_accounts / total_accounts AS churn_rate FROM step_2 GROUP BY age;
改善事實資料表效能
一旦我們在生產環境中有了可運作的事實資料表，我們就會想要注意它在以下情況下的擴展方式
- 表格會隨著更多歷史記錄而更新。
- 更多人開始平行執行針對表格的查詢。
假設流失邏輯變得非常熱門，以至於我們的 fact_account 成為許多下游儀表板和彙整的依賴項目 (和瓶頸)。
為了改善針對事實資料表的查詢效能，我們會想要預先計算針對流失計算中最常用欄位的彙整。
在 SQL 資料庫中，有幾種方法可以執行此操作
在我們的 Firebolt 資料倉儲中，我們可以結合這兩種最佳化，方法是使用彙總索引。定義彙總索引會告知您的 Firebolt 引擎建立額外的表格 (在底層)，當 SQL 查詢要求針對給定欄位套用特定彙整時，應該參考這些表格，而不是主要的事實資料表。
彙總索引也可以包含在您用來初始化和載入事實資料表的 SQL 指令碼中 (但是在您有機會觀察人們如何在實務中使用表格之後，選擇正確的索引會比較容易)。
以下是 Firebolt 彙總索引的範例，它有助於加速計算不同報告期間的累計和目前已流失帳戶數
CREATE AGGREGATING INDEX IF NOT EXISTS churned_accounts ON fact_account
(
updated_at,
DATE_TRUNC('day', updated_at),
DATE_TRUNC('week', updated_at),
DATE_TRUNC('month', updated_at),
DATE_TRUNC('quarter', updated_at),
COUNT(DISTINCT CASE WHEN status = 'inactive' then id end),
COUNT(DISTINCT CASE WHEN status = 'inactive' AND is_latest_record = TRUE then id end)
);
延伸閱讀
深入瞭解資料塑模、資料倉儲和使用 SQL
下一步：資料正規化
標準化資料庫的外觀，以及表格結構為何重要。