在資料倉儲開發的後期階段，驗證資料模型的準確性、可靠性和可用性至關重要。本文將探討如何透過 dbt 進行測試和檔案編寫，以及如何將資料倉儲佈署到生產環境。同時，我們將示範如何使用 SQL 查詢進行資料分析，以取得業務洞察。測試方面，我們會涵蓋單一測試和通用測試，確保資料模型的各個方面都得到驗證。檔案編寫則著重於使用 YAML 檔案，清晰地記錄模型的結構、用途和相關資訊。佈署階段將涵蓋生產環境的設定、資料管道建立和資料更新排程。最後，我們將運用 SQL 查詢，結合星型結構模型，分析實際業務問題，例如計算季度銷售額、各通路平均停留時間和暢銷產品等，展示如何從資料中提取有價值的資訊。
測試、檔案與佈署：確保資料倉儲的可靠性與可用性
當資料倉儲的開發接近尾聲時，確保所實作的資料模型的準確性、可靠性和可用性變得至關重要。本章節將重點介紹測試、檔案編寫以及如何將資料倉儲上線。
如同之前所述，在使用 dbt 進行開發時，測試和檔案編寫應該與模型開發同步進行。雖然我們採取了這種方法，但為了清晰起見，將其分為兩個部分。這種劃分使我們能夠更清楚地瞭解在 dbt 中為模型開發所完成的內容，以及我們的測試和檔案編寫流程。
測試的重要性
測試對於驗證資料模型的功能性和完整性至關重要。測試可以驗證維度表和事實表之間的關聯，檢查資料的一致性，並驗證計算指標的準確性。透過進行測試，可以識別並糾正資料中的任何問題或差異，從而確保分析輸出的可靠性。
進行單一測試和通用測試非常重要。單一測試針對資料模型的特定方面，例如驗證特定指標計算的準確性或驗證特定維度表和事實表之間的關聯。這些測試提供了對資料模型各個元件的深入瞭解。
另一方面，通用測試涵蓋了更廣泛的場景，並監控資料模型的整體行為。這些測試旨在確保資料模型在各種維度、時間段和使用者互動中正確執行。通用測試有助於發現實際使用過程中可能出現的問題，並提供對資料模型處理各種場景的能力的信心。
檔案編寫的必要性
同時，記錄資料模型和相關流程對於知識傳承、協作和未來維護是必要的。記錄資料模型涉及捕捉有關模型的目的、結構、關聯和假設的資訊。它包括有關源系統、轉換邏輯、應用業務規則以及其他相關資訊的詳細資訊。
使用 YAML 檔案進行檔案編寫
建議更新相應的 YAML 檔案，以提供全面的解釋和後設資料，從而記錄資料模型。YAML 檔案作為 dbt 模型的組態和檔案集中位置，使跟蹤變更和了解每個模型的目的和用法變得更加容易。
佈署前的準備
一旦測試和檔案編寫完成，本章節的最後一步是準備將資料倉儲上線。這涉及將資料模型佈署到生產環境，建立資料管道，並設定定時資料更新。在此階段監控資料倉儲的效能、可用性和資料品質非常重要。在類別似生產環境中進行徹底測試並獲得終端使用者反饋，可以幫助在資料倉儲完全投入營運之前識別任何剩餘問題。
執行通用測試
首先，我們將重點進行通用測試。第一個使用案例是確保所有維度表的代理鍵是唯一的且不為空。第二個使用案例是確保事實表中的每個代理鍵都存在於指定的維度表中。
version: 2
models:
- name: dim_customers
columns:
- name: sk_customer
tests:
- unique
- not_null
- name: dim_channels
columns:
- name: sk_channel
tests:
- unique
- not_null
- name: dim_date
columns:
- name: date_day
tests:
- unique
- not_null
- name: dim_products
columns:
- name: sk_product
tests:
- unique
- not_null
- name: fct_purchase_history
columns:
- name: sk_customer
tests:
- relationships:
to: ref('dim_customers')
field: sk_customer
- name: sk_channel
tests:
- relationships:
to: ref('dim_channels')
field: sk_channel
- name: sk_product
tests:
- relationships:
to: ref('dim_products')
field: sk_product
- name: fct_visit_history
columns:
- name: sk_customer
tests:
- relationships:
to: ref('dim_customers')
field: sk_customer
- name: sk_channel
tests:
- relationships:
to: ref('dim_channels')
field: sk_channel
內容解密：
此 YAML 檔案組態定義了對各個資料模型的測試。對維度表（如 dim_customers、dim_channels 等）的測試確保了其代理鍵的唯一性和非空值。對事實表（如 fct_purchase_history 和 fct_visit_history）的測試則檢查了其代理鍵與相應維度表之間的關聯性。
執行 dbt 測試命令
接下來，我們執行 dbt 測試命令以檢視所有測試是否成功執行。如果日誌如 圖示 1 所示，則表示一切正常。
此圖示顯示了通用測試的日誌輸出，表明所有測試均已成功透過。
圖表翻譯： 此圖示呈現了 dbt 測試命令的執行結果，展示了所有測試案例的透過情況，用於確認資料模型的正確性和一致性。
開發單一測試
接下來，我們將開發一些單一測試。這裡，我們將重點放在事實表的指標上。第一個單一測試使用案例是確保 fct_purchase_history 中的 mtr_total_amount_gross 指標僅包含正值。為此，我們在 tests 資料夾中建立一個新的測試檔案 assert_mtr_total_amount_gross_is_positive.sql。
-- assert_mtr_total_amount_gross_is_positive.sql
SELECT
COUNT(*)
FROM
{{ ref('fct_purchase_history') }}
WHERE
mtr_total_amount_gross <= 0;
內容解密：
此 SQL 查詢檢查 fct_purchase_history 表中 mtr_total_amount_gross 指標的值是否均為正。如果查詢結果大於 0，表示存在非正值，該測試將失敗。此測試用於確保指標的正確性。
使用 dbt 進行資料測試、檔案編寫與佈署
在資料倉儲開發過程中，確保資料的正確性與完整性至關重要。dbt 提供了一個完整的測試框架，讓我們能夠進行單一測試（singular tests）和通用測試（generic tests），以驗證資料模型的各個方面。
進行單一測試
單一測試允許我們針對特定的業務規則或資料條件進行自定義查詢。以下是一些範例：
檢查 mtr_total_amount_gross 是否為正數
-- assert_mtr_total_amount_gross_is_positive.sql
SELECT
sk_customer,
sk_channel,
sk_product,
SUM(mtr_total_amount_gross) AS mtr_total_amount_gross
FROM {{ ref('fct_purchase_history') }}
GROUP BY 1, 2, 3
HAVING mtr_total_amount_gross < 0
內容解密：
此查詢檢查 fct_purchase_history 表中的 mtr_total_amount_gross 是否存在負數。如果存在，表示資料可能有誤。查詢透過 GROUP BY 對客戶、通路和產品進行分組，並計算每組的總金額，最後使用 HAVING 子句篩選出總金額小於零的記錄。
檢查 mtr_unit_price 是否小於或等於 mtr_total_amount_gross
-- assert_mtr_unit_price_is_equal_or_lower_than_mtr_total_amount_gross.sql
SELECT
sk_customer,
sk_channel,
sk_product,
SUM(mtr_total_amount_gross) AS mtr_total_amount_gross,
SUM(mtr_unit_price) AS mtr_unit_price
FROM {{ ref('fct_purchase_history') }}
GROUP BY 1, 2, 3
HAVING mtr_unit_price > mtr_total_amount_gross
內容解密：
此查詢驗證 mtr_unit_price 是否大於 mtr_total_amount_gross。在正常情況下，單價不應該超過總金額。如果出現這種情況，表示資料可能存在問題。查詢同樣透過分組和匯總檢查不符合條件的記錄。
檢查 mtr_length_of_stay_minutes 是否為正數
-- assert_mtr_length_of_stay_is_positive.sql
SELECT
sk_customer,
sk_channel,
SUM(mtr_length_of_stay_minutes) AS mtr_length_of_stay_minutes
FROM {{ ref('fct_visit_history') }}
GROUP BY 1, 2
HAVING mtr_length_of_stay_minutes < 0
內容解密：
此查詢檢查 fct_visit_history 表中的 mtr_length_of_stay_minutes 是否存在負數。停留時間為負數是不合理的，因此該測試用於捕捉可能的日期錯誤或資料異常。
執行單一測試
使用以下命令執行單一測試：
dbt test --select test_type:singular
此命令會執行所有標記為 singular 的測試，而忽略通用測試。
編寫檔案
在 dbt 中，檔案編寫主要透過 YAML 檔案完成。以下是一個範例：
# _omnichannel_marts.yml
version: 2
models:
- name: dim_customers
description: 所有客戶的詳細資訊。包含使用訪客結帳的匿名使用者。
columns:
- name: sk_customer
description: 客戶維度的代理鍵。
tests:
- unique
- not_null
- name: fct_purchase_history
description: 客戶訂單歷史記錄。
columns:
- name: sk_customer
description: 客戶維度的代理鍵。
tests:
- relationships:
to: ref('dim_customers')
field: sk_customer
生成檔案
更新 YAML 檔案後，使用以下命令生成檔案：
dbt docs generate
生成的網頁將顯示模型的詳細資訊和測試結果。
佈署到生產環境
建立生產環境：在 dbt 中建立一個名為「Production」的環境，並指定目標資料集（例如
omnichannel_analytics）。組態作業：建立一個新的作業，選擇剛建立的「Production」環境，並在命令中新增
dbt test和dbt build。執行作業：手動執行作業並檢查日誌，以確保一切正常執行。
透過上述步驟，我們完成了資料模型的測試、檔案編寫和佈署。這不僅提高了資料品質，也確保了生產環境中的資料正確性和完整性。
資料分析與SQL應用
在完成星型結構模型後，我們可以開始進行分析探索階段，並開發查詢來回答特定的業務問題。如前所述，這種型別的資料建模技術使得從事實表中選取特定指標，並使用來自維度表的屬性進行豐富化變得更加容易。
每季度總銷售金額（含折扣）查詢
在範例6-29中，我們首先建立了一個查詢，用於計算「每季度總銷售金額（含折扣）」。該查詢從fct_purchase_history和dim_date兩個表中擷取資料，並對檢索到的資料進行計算。該查詢旨在取得每年每個季度的總金額總和。
SELECT
dd.year_number,
dd.quarter_of_year,
ROUND(SUM(fct.mtr_total_amount_net), 2) AS sum_total_amount_with_discount
FROM
`omnichannel_analytics`.`fct_purchase_history` fct
LEFT JOIN `omnichannel_analytics`.`dim_date` dd ON dd.date_day = fct.sk_order_date
GROUP BY
dd.year_number, dd.quarter_of_year
內容解密：
SELECT dd.year_number, dd.quarter_of_year：選擇日期維度表中的年份和季度欄位。ROUND(SUM(fct.mtr_total_amount_net), 2)：計算事實表中的總銷售金額（含折扣），並四捨五入到小數點後兩位。LEFT JOIN：將事實表與日期維度表進行左連線，以取得完整的訂單日期資訊。GROUP BY：根據年份和季度對結果進行分組，以統計每個季度的總銷售金額。
透過分析執行此查詢的結果，我們可以得出2023年第二季度表現最佳，而2024年第一季度表現最差的結論。
各通路平均停留時間查詢
在範例6-30中，我們利用星型結構模型計算每個通路的平均停留時間（以分鐘為單位）。該查詢選擇了通路名稱（dc.dsc_channel_name）和平均停留時間（以分鐘為單位），後者是透過ROUND(AVG(mtr_length_of_stay_minutes), 2)函式計算得出的。
SELECT
dc.dsc_channel_name,
ROUND(AVG(mtr_length_of_stay_minutes), 2) AS avg_length_of_stay_minutes
FROM
`omnichannel_analytics.fct_visit_history` fct
LEFT JOIN `omnichannel_analytics.dim_channels` dc ON fct.sk_channel = dc.sk_channel
GROUP BY
dc.dsc_channel_name
內容解密：
SELECT dc.dsc_channel_name：選擇通路維度表中的通路名稱欄位。ROUND(AVG(mtr_length_of_stay_minutes), 2)：計算事實表中每個通路的平均停留時間，並四捨五入到小數點後兩位。LEFT JOIN：將事實表與通路維度表進行左連線，以取得完整的通路資訊。GROUP BY：根據通路名稱對結果進行分組，以統計每個通路的平均停留時間。
透過分析執行此查詢的結果，我們可以得出使用者在網站上花費的時間比在手機應用程式或公司Instagram帳戶上花費的時間更多的結論。
各通路前三名產品查詢
在範例6-31中，我們進一步利用CTE（公用表表達式）和視窗函式來取得每個通路的前三名產品。首先，我們建立了一個基礎查詢，用於計算每個產品和通路的總銷售金額。然後，我們使用RANK()函式對每個通路的產品銷售金額進行排名。
WITH base_cte AS (
SELECT
dp.dsc_product_name,
dc.dsc_channel_name,
ROUND(SUM(fct.mtr_total_amount_net), 2) AS sum_total_amount
FROM
`omnichannel_analytics`.`fct_purchase_history` fct
LEFT JOIN `omnichannel_analytics`.`dim_products` dp ON dp.sk_product = fct.sk_product
LEFT JOIN `omnichannel_analytics`.`dim_channels` dc ON dc.sk_channel = fct.sk_channel
GROUP BY
dc.dsc_channel_name, dp.dsc_product_name
),
ranked_cte AS (
SELECT
base_cte.dsc_product_name,
base_cte.dsc_channel_name,
base_cte.sum_total_amount,
RANK() OVER (PARTITION BY dsc_channel_name ORDER BY sum_total_amount DESC) AS rank_total_amount
FROM
base_cte
)
SELECT *
FROM ranked_cte
WHERE rank_total_amount <= 3
內容解密：
base_cte：計算每個產品和通路的總銷售金額。ranked_cte：使用RANK()函式對每個通路的產品銷售金額進行排名。SELECT * FROM ranked_cte WHERE rank_total_amount <= 3：選擇排名前三名的產品。
透過分析執行此查詢的結果，我們可以得出手機應用程式上的暢銷產品是「Men Bomber Jacket with Detachable Hood」，網站上的暢銷產品是「Leather Crossbody Handbag」，而Instagram上的暢銷產品是「Unisex Running Sneakers」。
行動應用程式上2023年頂級客戶查詢
在範例6-32中，我們結合CTE和ORDER BY子句與LIMIT修飾符來取得2023年行動應用程式上的前三名客戶。
WITH base_cte AS (
SELECT
dcu.dsc_name,
dcu.dsc_email_address,
dc.dsc_channel_name,
ROUND(SUM(fct.mtr_total_amount_net), 2) AS sum_total_amount
FROM
`omnichannel_analytics`.`fct_purchase_history` fct
LEFT JOIN `omnichannel_analytics`.`dim_customers` dcu ON dcu.sk_customer = fct.sk_customer
LEFT JOIN `omnichannel_analytics`.`dim_channels` dc ON dc.sk_channel = fct.sk_channel
GROUP BY
dcu.dsc_name, dcu.dsc_email_address, dc.dsc_channel_name
)
SELECT *
FROM base_cte
WHERE dsc_channel_name = 'Mobile App' AND year_number = 2023
ORDER BY sum_total_amount DESC
LIMIT 3
內容解密：
base_cte：計算每個客戶在各通路上的總消費金額。SELECT * FROM base_cte WHERE ... ORDER BY sum_total_amount DESC LIMIT 3：選擇2023年行動應用程式上的前三名客戶，按總消費金額降序排列。