Snowflake資料品質監控與分析實務

Snowflake 作為雲端資料倉儲解決方案，具備強大功能與彈性，讓企業能有效管理和分析大量資料。但確保資料品質與系統穩定性仍需持續監控與分析。本文將介紹如何在 Snowflake 中實施資料品質監控流程，包含收集資料函式庫 Metadata、監控資料更新頻率及資料量、分析查詢歷史記錄，以及對關鍵表格進行健康檢查。同時也將探討如何收集與分析資料品質指標，涵蓋欄位層級的指標計算、查詢日誌分析及資料湖中 Metadata 的應用，幫助工程師全面掌握資料品質狀況。

Snowflake 資料品質監控與分析

Snowflake 為現代雲端資料倉儲解決方案，其強大的功能與彈性架構使得企業能夠高效管理與分析龐大資料。不過，要確保資料品質與系統可靠性，仍需進行一系列的監控與分析工作。本篇將探討如何在 Snowflake 中實施資料品質監控，涵蓋資料收集、資料更新頻率監控、查詢歷史分析以及健康檢查等重要步驟。

步驟1：收集資料函式庫元資料

要全面瞭解 Snowflake 中的資料結構與內容，首先需要收集資料函式庫的元資料（Metadata）。這些資訊對於後續的資料品質監控與分析至關重要。Snowflake 提供了 information_schema.tables 檢視來查詢資料函式庫中的表格資訊。

SELECT 
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_OWNER,
    TABLE_TYPE,
    IS_TRANSIENT,
    CLUSTERING_KEY
FROM "ANALYTICS".information_schema.tables
WHERE 
    table_schema NOT IN ('INFORMATION_SCHEMA')
    AND TABLE_TYPE NOT IN ('VIEW', 'EXTERNAL TABLE')
ORDER BY 
    TABLE_CATALOG, 
    TABLE_SCHEMA, 
    TABLE_NAME;

內容解密：

此查詢陳述式用於取得 ANALYTICS 資料函式庫中的表格資訊。透過 information_schema.tables 檢視，我們可以取得表格的目錄、架構、名稱、擁有者、型別等重要資訊。其中，TABLE_TYPE 欄位可用於區分普通表格、檢視（View）與外部表格（External Table）。由於 information_schema.tables 不包含檢視與外部表格的詳細資訊，因此需要額外執行 SHOW VIEWS 和 SHOW EXTERNAL TABLES 命令來取得這些物件的元資料。

此外，為了取得檢視的定義細節，可以使用 SHOW VIEWS 命令：

SHOW VIEWS IN DATABASE "ANALYTICS";

對於外部表格，同樣需要使用特定的命令來取得其相關資訊：

SHOW EXTERNAL TABLES IN DATABASE "ANALYTICS";

步驟2：監控資料的新鮮度與數量

資料的新鮮度與數量是評估資料品質的關鍵指標。Snowflake 提供了豐富的資訊來監控這些指標。透過查詢 information_schema.tables，可以取得表格的列數、位元組數以及最後更新時間等資訊。

SELECT 
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    ROW_COUNT,
    BYTES,
    CONVERT_TIMEZONE('UTC', CREATED) as CREATED,
    CONVERT_TIMEZONE('UTC', LAST_ALTERED) as LAST_ALTERED
FROM "ANALYTICS".information_schema.tables
WHERE 
    table_schema NOT IN ('INFORMATION_SCHEMA')
    AND TABLE_TYPE NOT IN ('VIEW', 'EXTERNAL TABLE')
ORDER BY 
    TABLE_CATALOG, 
    TABLE_SCHEMA, 
    TABLE_NAME;

內容解密：

此查詢用於監控表格的資料新鮮度與數量。透過檢查 ROW_COUNT、BYTES、CREATED 和 LAST_ALTERED 欄位，可以瞭解表格的資料量以及更新頻率。這些資訊對於評估資料管道的健康狀態至關重要。對於檢視和外部表格，由於其特性不同，需要採用不同的監控策略。

步驟3：建立查詢歷史記錄

查詢歷史記錄對於理解資料的使用模式、最佳化查詢效能以及進行問題排查具有重要意義。Snowflake 的 snowflake.account_usage.query_history 檢視提供了詳細的查詢歷史資訊。

SELECT 
    "QUERY_TEXT",
    "DATABASE_NAME",
    "SCHEMA_NAME",
    "QUERY_TYPE",
    "USER_NAME",
    "ROLE_NAME",
    "EXECUTION_STATUS",
    "START_TIME",
    "END_TIME",
    "TOTAL_ELAPSED_TIME",
    "BYTES_SCANNED",
    "ROWS_PRODUCED",
    "SESSION_ID",
    "QUERY_ID",
    "QUERY_TAG",
    "WAREHOUSE_NAME",
    "ROWS_INSERTED",
    "ROWS_UPDATED",
    "ROWS_DELETED",
    "ROWS_UNLOADED"
FROM snowflake.account_usage.query_history
WHERE 
    start_time BETWEEN to_timestamp_ltz('2021-01-01 00:00:00.000000+00:00')
    AND to_timestamp_ltz('2021-01-01 01:00:00.000000+00:00')
    AND QUERY_TYPE NOT IN ('DESCRIBE', 'SHOW')
    AND (DATABASE_NAME IS NULL OR DATABASE_NAME NOT IN ('UTIL_DB', 'SNOWFLAKE'))
    AND ERROR_CODE is NULL
ORDER BY start_time DESC;

內容解密：

此查詢陳述式用於提取 Snowflake 中的查詢歷史記錄。透過分析這些資訊，可以瞭解查詢的使用模式、執行效率以及相關使用者的行為模式。同時，這些資料也有助於進行資料血統分析（Data Lineage），即追蹤資料的來源與流向。

步驟4：健康檢查

對於關鍵表格，進行定期的健康檢查是確保資料品質的重要手段。健康檢查主要包括檢查資料的完整性、檢查欄位中的異常值（如空值或零值）等。

SELECT 
    DATE_TRUNC('HOUR', created_on) as bucket_start,
    DATEADD(hr, 1, DATE_TRUNC('HOUR', created_on)) as bucket_end,
    COUNT(*) as row_count,
    -- string field
    COUNT(account_id) / CAST(COUNT(*) AS NUMERIC) as account_id___completeness,
    COUNT(DISTINCT account_id) as account_id___approx_distinct_count,
    COUNT(DISTINCT account_id) / CAST(COUNT(*) AS NUMERIC) as account_id___approx_distinctness,
    AVG(LENGTH(account_id)) as account_id___mean_length,
    MAX(LENGTH(account_id)) as account_id___max_length,
    MIN(LENGTH(account_id)) as account_id___min_length,
    STDDEV(CAST(LENGTH(account_id) as double)) as account_id___std_length,
    SUM(IFF(REGEXP_COUNT(TO_VARCHAR(account_id), '^([-+]?[0-9]+)$', 1, 'i') != 0, 1, 0)) / CAST(COUNT(*) AS NUMERIC) as account_id___text_int_rate,
    SUM(IFF(REGEXP_COUNT(TO_VARCHAR(account_id), '^[a-zA-Z]+$', 1, 'i') != 0, 1, 0)) / CAST(COUNT(*) AS NUMERIC) as account_id___text_alpha_rate
FROM your_table_name
GROUP BY bucket_start, bucket_end
ORDER BY bucket_start;

內容解密：

此查詢陳述式用於對特定表格進行健康檢查，涵蓋資料完整性、欄位統計資訊（如平均長度、最大/最小長度、標準差）以及特定格式資料的比例等。透過定期執行這些檢查，可以及時發現資料中的異常情況並進行處理。

資料品質指標的收集與分析

在現代資料系統中，資料品質指標的收集與分析是確保資料可靠性和系統穩定性的關鍵步驟。本章節將探討如何在資料倉儲和資料湖中收集和分析資料品質指標。

資料倉儲中的資料品質指標收集

資料倉儲提供了結構化的資料儲存和查詢能力，使得資料品質指標的收集變得更加直接。以下是一些常見的資料品質指標及其收集方法：

欄位層級的資料品質指標

對於字串型別的欄位，如account_id，可以收集以下指標：

完整性（Completeness）：非空值的比例
獨特性（Distinctness）：唯一值的比例
UUID比例：符合UUID格式的記錄比例
全空白比例：全部由空白字元組成的記錄比例
空值關鍵字比例：包含’NULL’, ‘NONE’, ‘NIL’, ‘NOTHING’等關鍵字的記錄比例

對於數值型別的欄位，如num_of_users，可以收集以下指標：

零值比例：值為0的記錄比例
負值比例：值小於0的記錄比例
近似獨特性：唯一值的近似比例
平均值：數值欄位的平均值
最小值和最大值：數值欄位的最小和最大值
標準差：數值欄位的標準差
近似分位數：數值欄位在不同分位數上的值

例項分析

-- 字串欄位
COUNT(account_id) / CAST(COUNT(*) AS NUMERIC) as account_id___completeness,
SUM(IFF(REGEXP_COUNT(TO_VARCHAR(account_id), '^([-+]?[0-9]*[.]?[0-9]+([eE][-+]?[0-9]+)?)$', 1, 'i') != 0, 1, 0)) / CAST(COUNT(*) AS NUMERIC) as account_id___text_number_rate,
SUM(IFF(REGEXP_COUNT(TO_VARCHAR(account_id), '^([0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12})$', 1, 'i') != 0, 1, 0)) / CAST(COUNT(*) AS NUMERIC) as account_id___text_uuid_rate,
SUM(IFF(REGEXP_COUNT(TO_VARCHAR(account_id), '^(\\s+)$', 1, 'i') != 0, 1, 0)) / CAST(COUNT(*) AS NUMERIC) as account_id___text_all_spaces_rate,
SUM(IFF(UPPER(account_id) IN ('NULL', 'NONE', 'NIL', 'NOTHING'), 1, 0)) / CAST(COUNT(*) AS NUMERIC) as account_id___text_null_keyword_rate,

-- 數值欄位
COUNT(num_of_users) / CAST(COUNT(*) AS NUMERIC) as num_of_users___completeness,
SUM(IFF(num_of_users = 0, 1, 0)) / CAST(COUNT(*) AS NUMERIC) as num_of_users___zero_rate,
SUM(IFF(num_of_users < 0, 1, 0)) / CAST(COUNT(*) AS NUMERIC) as num_of_users___negative_rate,
COUNT(DISTINCT num_of_users) / CAST(COUNT(*) AS NUMERIC) as num_of_users___approx_distinctness,
AVG(num_of_users) as num_of_users___numeric_mean,
MIN(num_of_users) as num_of_users___numeric_min,
MAX(num_of_users) as num_of_users___numeric_max,
STDDEV(CAST(num_of_users AS DOUBLE)) as num_of_users___numeric_std,
ARRAY_CONSTRUCT(
    APPROX_PERCENTILE(num_of_users, 0.00),
    APPROX_PERCENTILE(num_of_users, 0.20),
    APPROX_PERCENTILE(num_of_users, 0.40),
    APPROX_PERCENTILE(num_of_users, 0.60),
    APPROX_PERCENTILE(num_of_users, 0.80),
    APPROX_PERCENTILE(num_of_users, 1.00)
) as num_of_users___approx_quantiles
FROM analytics.prod.client_hub
WHERE DATE_TRUNC('HOUR', measurement_timestamp) >= DATEADD(day, -1, CURRENT_TIMESTAMP())
GROUP BY bucket_start, bucket_end
ORDER BY bucket_start ASC;

內容解密：

上述SQL查詢陳述式用於收集client_hub表中account_id和num_of_users欄位的資料品質指標。對於account_id，計算了完整性、數字比例、UUID比例、全空白比例和空值關鍵字比例等指標。對於num_of_users，計算了完整性、零值比例、負值比例、近似獨特性、平均值、最小值、最大值、標準差和近似分位數等指標。這些指標能夠幫助我們識別資料中的常見問題，如缺少ID、重複記錄、錯誤的格式、零值或異常值等。

使用查詢日誌分析資料品質

查詢日誌是資料倉儲中另一種重要的資料來源，可以提供有關資料使用情況和查詢模式的資訊。透過分析查詢日誌，可以回答以下問題：

誰正在存取這些資料？
資料的上游來源和下游去向是什麼？
查詢的頻率和平均執行時間是多少？
查詢的輸入輸出大小是多少？

例項分析

大多數主要資料倉儲供應商都提供了查詢日誌功能，例如Snowflake的QUERY_HISTORY系列表格、BigQuery的AuditLogs資源和Redshift的STL_QUERY表格系列。查詢日誌通常包含以下資訊：

執行查詢的使用者ID
查詢的SQL文字和雜湊值
查詢的總執行時間
錯誤程式碼（如有）
查詢的輸入輸出大小（行數或位元組數）

內容解密：

查詢日誌提供了豐富的資訊，可以用於監控資料使用情況、查詢效能和錯誤率。透過分析查詢日誌，可以識別出資料存取模式、查詢頻率和查詢效能問題，從而最佳化資料倉儲的組態和查詢陳述式。

資料湖中的資料品質分析

資料湖與資料倉儲的主要區別在於資料儲存格式的靈活性。資料湖允許使用“讀時模式（schema-on-read）”，即資料在讀取時才進行結構化處理。雖然這種靈活性帶來了一些好處，但也增加了資料品質控制的難度。

可用的元資料

資料湖通常會自動收集和儲存物件元資料，例如物件的插入時間和負載大小。這些元資料可以被用來回答以下問題：

物件最後一次更新是什麼時候？
特定型別檔案的平均大小是多少？最近是否有所增加？

內容解密：

雖然資料湖的靈活性使得某些資料品質指標更難收集，但仍然可以利用自動收集的物件元資料來監控資料更新情況和檔案大小變化，從而間接評估資料品質。

玄貓

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