Snowflake 雲端資料平台的技術革新
在雲端運算技術快速發展的今日，企業對資料處理平台的需求已從傳統的資料倉儲演進至更全面的資料雲平台。Snowflake 正是這波技術革新的代表，它不僅重新定義了資料倉儲的架構設計，更提供了一套完整的資料生態系統，涵蓋資料工程、資料湖、資料科學、機器學習，以及安全資料分享等功能。
Snowflake 的成功源於其獨特的三層架構設計。最底層是儲存層，採用雲端物件儲存服務如 AWS S3、Azure Blob Storage 或 GCP Cloud Storage，資料以壓縮與加密的形式儲存。中間層是運算層，由虛擬倉儲組成，這些倉儲是獨立的運算叢集，可以根據工作負載動態擴展或縮減。最上層是服務層，負責查詢最佳化、元資料管理、安全控制、交易管理等核心功能。
這種分層架構帶來了幾個革命性的優勢。首先是儲存與運算的完全分離，用戶可以獨立擴展儲存容量或運算能力，不受彼此限制。其次是多叢集共享資料，不同的虛擬倉儲可以同時存取相同的資料，無需複製。再者是彈性擴展能力，虛擬倉儲可以在秒級時間內啟動或關閉，並根據工作負載自動調整大小。最後是效能隔離，不同用戶或工作負載使用獨立的虛擬倉儲，互不干擾。
根據筆者在台灣多家企業協助建置 Snowflake 資料平台的經驗，這個平台特別適合幾類應用場景。第一類是需要彈性擴展的企業資料倉儲，Snowflake 能夠自動處理資料成長與查詢負載增加的挑戰。第二類是跨部門的資料分享與協作，透過安全分享機制，不同部門或外部夥伴能夠存取相同的資料集而無需複製。第三類是結合結構化與半結構化資料的分析需求，Snowflake 原生支援 JSON、Avro、Parquet 等格式。第四類是需要整合機器學習工作流程的資料科學專案，Snowflake 可以與主流 ML 平台無縫整合。
在技術選型時，企業常需要比較 Snowflake 與其他方案。相較於傳統的本地資料倉儲如 Oracle、Teradata，Snowflake 無需硬體投資與維運團隊，能快速部署與擴展。相較於雲端原生的資料倉儲如 Amazon Redshift、Google BigQuery，Snowflake 提供了更好的多雲支援與資料分享能力。相較於開源的大數據平台如 Hadoop、Spark，Snowflake 大幅降低了系統複雜度與維運成本。
Snowflake SQL 核心語法精通
Snowflake 實作了標準的 ANSI SQL，同時提供了許多增強功能與最佳化。掌握 SQL 語法是有效使用 Snowflake 的基礎。雖然基本的 SELECT、FROM、WHERE 語法與傳統資料庫相同，但 Snowflake 在細節上有許多值得注意的特性。
查詢語法的第一個重點是理解 Snowflake 的大小寫不敏感特性。預設情況下，物件名稱不區分大小寫，SELECT * FROM USERS 與 select * from users 是等效的。然而，如果使用雙引號括起物件名稱，則會區分大小寫。這個特性在從其他資料庫遷移時需要特別注意。
第二個重點是 Snowflake 對 NULL 值的處理。在比較運算中，NULL 不等於任何值，包括它自己。這意味著 WHERE column = NULL 永遠不會匹配任何列，正確的寫法是 WHERE column IS NULL。在排序時，NULL 值預設排在最後（升序）或最前（降序），可以使用 NULLS FIRST 或 NULLS LAST 明確控制。
-- Snowflake SQL 基礎語法與特性示範
-- 範例 1：基本查詢結構
-- 展示 SELECT、FROM、WHERE 的標準用法
SELECT
customer_id,
customer_name,
email,
registration_date
FROM customers
WHERE registration_date >= '2024-01-01'
AND email IS NOT NULL
ORDER BY registration_date DESC;
-- 範例 2：大小寫敏感性示範
-- 這兩個查詢是等效的（預設不區分大小寫）
SELECT * FROM CUSTOMERS;
SELECT * FROM customers;
-- 但如果使用雙引號，則會區分大小寫
-- 以下查詢會尋找名為 "Customers" 的表格（注意大小寫）
SELECT * FROM "Customers";
-- 範例 3：NULL 值處理
-- 正確的 NULL 值檢查方式
SELECT
product_id,
product_name,
description,
CASE
WHEN description IS NULL THEN '無商品描述'
ELSE description
END AS description_display
FROM products
WHERE discontinued_date IS NULL; -- 查詢未停產的商品
-- 範例 4：進階排序技巧
-- 使用 NULLS FIRST/LAST 控制 NULL 值的排序位置
SELECT
order_id,
customer_id,
order_date,
shipped_date,
DATEDIFF(day, order_date, shipped_date) AS days_to_ship
FROM orders
ORDER BY
shipped_date NULLS LAST, -- 未出貨的訂單排在最後
days_to_ship ASC; -- 已出貨的按出貨天數升序排列
-- 範例 5：分頁查詢（LIMIT 與 OFFSET）
-- 實現結果集的分頁顯示
SELECT
product_id,
product_name,
unit_price,
stock_quantity
FROM products
ORDER BY product_name
LIMIT 20 OFFSET 40; -- 取得第 41-60 筆資料（每頁 20 筆，第 3 頁）
-- 範例 6：集合運算 - UNION
-- 合併多個查詢結果，自動去除重複列
SELECT customer_id, 'VIP' AS customer_type
FROM vip_customers
UNION
SELECT customer_id, 'Regular' AS customer_type
FROM regular_customers;
-- 使用 UNION ALL 保留重複列（效能更好）
SELECT customer_id FROM orders_2024
UNION ALL
SELECT customer_id FROM orders_2025;
-- 範例 7：集合運算 - INTERSECT
-- 找出同時存在於兩個結果集的資料
SELECT customer_id
FROM customers_who_purchased_product_a
INTERSECT
SELECT customer_id
FROM customers_who_purchased_product_b;
-- 範例 8：集合運算 - EXCEPT（或 MINUS）
-- 找出存在於第一個結果集但不在第二個結果集的資料
SELECT customer_id
FROM all_customers
EXCEPT
SELECT customer_id
FROM customers_who_churned;
-- 範例 9：複雜的條件查詢
-- 結合多種條件運算子
SELECT
order_id,
customer_id,
order_date,
total_amount,
payment_method
FROM orders
WHERE
order_date BETWEEN '2024-01-01' AND '2024-12-31' -- 日期範圍
AND total_amount >= 10000 -- 金額條件
AND payment_method IN ('credit_card', 'line_pay') -- 多值匹配
AND customer_id NOT IN ( -- 子查詢排除
SELECT customer_id
FROM blacklist_customers
)
ORDER BY total_amount DESC;
-- 範例 10：使用 QUALIFY 子句進行視窗函數過濾
-- QUALIFY 是 Snowflake 的特有功能，可以直接在視窗函數結果上過濾
SELECT
customer_id,
order_id,
order_date,
total_amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS order_rank
FROM orders
QUALIFY order_rank <= 3; -- 只保留每位客戶最近的 3 筆訂單
-- 範例 11：使用 SAMPLE 子句進行資料採樣
-- 隨機抽樣 10% 的資料進行分析
SELECT
customer_id,
total_purchase_amount
FROM customer_summary
SAMPLE (10); -- 採樣 10% 的列
-- 或指定採樣的列數
SELECT * FROM large_table
SAMPLE (1000 ROWS); -- 隨機抽樣 1000 列
這些範例展示了 Snowflake SQL 的核心語法特性。QUALIFY 子句是 Snowflake 相對於傳統資料庫的獨特功能，它讓視窗函數的結果過濾變得更加簡潔。SAMPLE 子句則提供了高效的資料採樣能力，在處理大型資料集時特別有用。
進階查詢技巧還包括使用 CTE（Common Table Expressions，通用表格表達式）來組織複雜查詢。CTE 能夠將複雜查詢分解為多個邏輯步驟，提升程式碼可讀性與可維護性。Snowflake 支援遞迴 CTE，可以處理階層式資料結構如組織架構、產品分類等。
-- 使用 CTE 組織複雜查詢
-- 範例：計算客戶的生命週期價值（Customer Lifetime Value）
WITH customer_orders AS (
-- 第一步：計算每位客戶的訂單統計
SELECT
customer_id,
COUNT(DISTINCT order_id) AS order_count,
SUM(total_amount) AS total_spent,
MIN(order_date) AS first_order_date,
MAX(order_date) AS last_order_date,
DATEDIFF(day, MIN(order_date), MAX(order_date)) AS customer_lifetime_days
FROM orders
WHERE order_status = 'completed'
GROUP BY customer_id
),
customer_segments AS (
-- 第二步：根據消費行為分群
SELECT
customer_id,
order_count,
total_spent,
customer_lifetime_days,
CASE
WHEN total_spent >= 100000 THEN 'VIP'
WHEN total_spent >= 50000 THEN 'Gold'
WHEN total_spent >= 20000 THEN 'Silver'
ELSE 'Bronze'
END AS customer_segment,
CASE
WHEN DATEDIFF(day, last_order_date, CURRENT_DATE()) <= 90 THEN 'Active'
WHEN DATEDIFF(day, last_order_date, CURRENT_DATE()) <= 365 THEN 'At Risk'
ELSE 'Churned'
END AS customer_status
FROM customer_orders
)
-- 第三步：產生最終分析報表
SELECT
customer_segment,
customer_status,
COUNT(*) AS customer_count,
AVG(total_spent) AS avg_lifetime_value,
SUM(total_spent) AS total_segment_value
FROM customer_segments
GROUP BY customer_segment, customer_status
ORDER BY
CASE customer_segment
WHEN 'VIP' THEN 1
WHEN 'Gold' THEN 2
WHEN 'Silver' THEN 3
ELSE 4
END,
customer_status;
-- 遞迴 CTE 範例：處理組織階層結構
WITH RECURSIVE org_hierarchy AS (
-- 基礎查詢：找出最高層主管
SELECT
employee_id,
employee_name,
manager_id,
job_title,
1 AS level,
CAST(employee_name AS VARCHAR(1000)) AS hierarchy_path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 遞迴查詢：找出下一層級的員工
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
e.job_title,
oh.level + 1 AS level,
oh.hierarchy_path || ' > ' || e.employee_name AS hierarchy_path
FROM employees e
INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
employee_id,
employee_name,
job_title,
level,
hierarchy_path
FROM org_hierarchy
ORDER BY level, employee_name;
Snowflake 資料型別系統深度解析
Snowflake 支援豐富的資料型別，理解這些型別的特性與適用場景是資料建模的基礎。相較於傳統資料庫，Snowflake 在資料型別的設計上做了許多簡化與最佳化，讓開發者能夠更專注於業務邏輯而非底層細節。
數值型別是最常用的資料型別之一。Snowflake 提供了 NUMBER、INTEGER、FLOAT、DOUBLE 等型別。NUMBER 型別是最通用的數值型別，可以指定精度與小數位數，例如 NUMBER(10,2) 表示總共 10 位數字，其中 2 位是小數。INTEGER 是 NUMBER(38,0) 的別名，適合儲存整數。FLOAT 與 DOUBLE 用於儲存浮點數，但要注意浮點數的精度問題。
字串型別包括 VARCHAR、CHAR、STRING、TEXT 等。在 Snowflake 中，這些型別基本上是等效的，都可以儲存最大長度為 16MB 的字串。VARCHAR 與 CHAR 的長度限制僅作為約束使用，不影響實際儲存。STRING 與 TEXT 是 VARCHAR 的同義詞。對於大部分應用，使用 VARCHAR 即可。
-- Snowflake 資料型別詳解與應用
-- 建立一個展示各種資料型別的表格
CREATE OR REPLACE TABLE data_type_examples (
-- 數值型別
id INTEGER, -- 整數，等同於 NUMBER(38,0)
quantity NUMBER(10,0), -- 10 位整數
unit_price NUMBER(12,2), -- 12 位數字，2 位小數
discount_rate NUMBER(5,4), -- 5 位數字，4 位小數（0.0000-9.9999）
weight FLOAT, -- 單精度浮點數
precise_calculation DOUBLE, -- 雙精度浮點數
-- 字串型別
product_code VARCHAR(20), -- 變長字串，最大 20 字元
product_name STRING, -- 不限長度字串（最大 16MB）
description TEXT, -- TEXT 是 VARCHAR 的別名
fixed_code CHAR(10), -- 固定長度字串
-- 二進位型別
product_image BINARY, -- 儲存二進位資料
file_content VARBINARY, -- 變長二進位資料
-- 布林型別
is_active BOOLEAN, -- TRUE 或 FALSE
is_featured BOOLEAN,
-- 日期與時間型別
created_date DATE, -- 僅日期（YYYY-MM-DD）
created_time TIME, -- 僅時間（HH:MI:SS）
created_timestamp TIMESTAMP_NTZ, -- 時間戳記（無時區）
created_timestamp_ltz TIMESTAMP_LTZ, -- 時間戳記（本地時區）
created_timestamp_tz TIMESTAMP_TZ, -- 時間戳記（含時區）
-- 半結構化資料型別
product_attributes VARIANT, -- 可儲存 JSON、XML 等半結構化資料
tags ARRAY, -- 陣列型別
metadata OBJECT -- 物件型別
);
-- 插入範例資料
INSERT INTO data_type_examples VALUES (
1, -- id
100, -- quantity
1299.99, -- unit_price
0.1500, -- discount_rate
2.5, -- weight
3.141592653589793, -- precise_calculation
'PROD-A-001', -- product_code
'高階商務筆記型電腦', -- product_name
'15.6 吋全高清顯示器，Intel i7 處理器', -- description
'FIXCODE001', -- fixed_code
NULL, -- product_image
NULL, -- file_content
TRUE, -- is_active
FALSE, -- is_featured
'2025-01-15', -- created_date
'10:30:00', -- created_time
'2025-01-15 10:30:00', -- created_timestamp
'2025-01-15 10:30:00', -- created_timestamp_ltz
'2025-01-15 10:30:00 +08:00', -- created_timestamp_tz
PARSE_JSON('{"brand": "ASUS", "warranty_years": 3}'), -- product_attributes
ARRAY_CONSTRUCT('laptop', 'business', 'high-end'), -- tags
OBJECT_CONSTRUCT('category', 'Electronics', 'subcategory', 'Computers') -- metadata
);
-- 範例：數值型別的運算與精度
SELECT
id,
quantity,
unit_price,
discount_rate,
-- 計算折扣後價格（保持精度）
ROUND(unit_price * (1 - discount_rate), 2) AS discounted_price,
-- 計算總金額
ROUND(quantity * unit_price * (1 - discount_rate), 2) AS total_amount,
-- 浮點數運算
weight * 1.5 AS shipping_weight
FROM data_type_examples;
-- 範例：字串型別的操作
SELECT
product_code,
product_name,
-- 字串長度
LENGTH(product_name) AS name_length,
-- 字串擷取
SUBSTR(product_code, 1, 4) AS product_category,
-- 字串連接
product_code || ' - ' || product_name AS full_description,
-- 字串轉換
UPPER(product_code) AS code_upper,
LOWER(product_name) AS name_lower
FROM data_type_examples;
-- 範例：日期與時間型別的操作
SELECT
created_date,
created_timestamp,
-- 日期運算
DATEADD(day, 30, created_date) AS warranty_start_date,
DATEDIFF(day, created_date, CURRENT_DATE()) AS days_since_creation,
-- 時間戳記轉換
DATE(created_timestamp) AS date_part,
TIME(created_timestamp) AS time_part,
-- 格式化輸出
TO_CHAR(created_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS formatted_timestamp,
-- 時區轉換
CONVERT_TIMEZONE('Asia/Taipei', created_timestamp_tz) AS taipei_time
FROM data_type_examples;
-- 範例：VARIANT 型別的 JSON 操作
SELECT
id,
product_name,
product_attributes,
-- 提取 JSON 欄位
product_attributes:brand::STRING AS brand,
product_attributes:warranty_years::INTEGER AS warranty_years,
-- 檢查 JSON 鍵是否存在
IFF(
IS_NULL_VALUE(product_attributes:warranty_years),
'無保固資訊',
'有保固資訊'
) AS warranty_info
FROM data_type_examples;
-- 範例：ARRAY 與 OBJECT 型別的操作
SELECT
id,
tags,
metadata,
-- 取得陣列元素
tags[0]::STRING AS first_tag,
ARRAY_SIZE(tags) AS tag_count,
-- 取得物件屬性
metadata:category::STRING AS category,
metadata:subcategory::STRING AS subcategory
FROM data_type_examples;
-- 範例：型別轉換
SELECT
-- 數字轉字串
TO_VARCHAR(id) AS id_string,
TO_CHAR(unit_price, '999,999.99') AS formatted_price,
-- 字串轉數字
TO_NUMBER('12345') AS number_value,
-- 字串轉日期
TO_DATE('2025-01-15', 'YYYY-MM-DD') AS converted_date,
TO_TIMESTAMP('2025-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS') AS converted_timestamp,
-- 安全轉換（失敗時回傳 NULL）
TRY_TO_NUMBER('abc') AS safe_number,
TRY_TO_DATE('invalid-date') AS safe_date
FROM data_type_examples;
VARIANT 型別是 Snowflake 的特色功能，專門用於儲存半結構化資料如 JSON、Avro、Parquet 等格式。VARIANT 型別的資料保留了原始的結構與型別資訊，可以使用路徑表達式直接存取巢狀資料。這讓 Snowflake 能夠靈活處理結構不固定的資料，是實現資料湖功能的關鍵技術。
日期與時間型別在 Snowflake 中有三種時間戳記變體。TIMESTAMP_NTZ（無時區）儲存絕對時間點，不包含時區資訊。TIMESTAMP_LTZ（本地時區）會根據使用者的會話時區自動轉換。TIMESTAMP_TZ（含時區）儲存時間點與對應的時區資訊。選擇適當的時間戳記型別對於跨時區的應用非常重要。
內建函數的深度應用
Snowflake 提供了數百個內建函數，涵蓋數值運算、字串處理、日期時間操作、聚合分析、視窗計算等各個面向。熟練運用這些函數能夠大幅簡化查詢邏輯，提升開發效率。
數值函數包括基礎的算術運算、三角函數、統計函數等。ROUND 函數用於四捨五入，可以指定小數位數。TRUNC 函數用於截斷，直接捨棄小數部分。CEIL 與 FLOOR 函數分別向上與向下取整。MOD 函數計算餘數。這些函數在財務計算、統計分析中經常使用。
字串函數提供了豐富的文字處理能力。SUBSTR 擷取子字串，CONCAT 連接字串，REPLACE 替換字元。TRIM、LTRIM、RTRIM 移除空白字元。UPPER、LOWER 轉換大小寫。SPLIT 將字串分割為陣列。正則表達式函數如 REGEXP_REPLACE、REGEXP_SUBSTR 提供了更強大的模式匹配能力。
-- Snowflake 內建函數深度應用
-- 數值函數應用範例
WITH sales_data AS (
SELECT
'ORD-001' AS order_id,
12345.6789 AS amount,
0.08 AS tax_rate,
125 AS quantity,
7 AS days_to_deliver
)
SELECT
order_id,
amount,
-- 四捨五入
ROUND(amount, 2) AS rounded_amount,
ROUND(amount, 0) AS rounded_to_integer,
ROUND(amount, -2) AS rounded_to_hundred,
-- 截斷
TRUNC(amount, 2) AS truncated_amount,
-- 向上/向下取整
CEIL(amount) AS ceiling_amount,
FLOOR(amount) AS floor_amount,
-- 絕對值
ABS(amount - 15000) AS difference,
-- 次方與平方根
POWER(quantity, 2) AS quantity_squared,
SQRT(quantity) AS quantity_sqrt,
-- 取餘數
MOD(days_to_deliver, 3) AS delivery_mod,
-- 稅額計算
ROUND(amount * tax_rate, 2) AS tax_amount,
ROUND(amount * (1 + tax_rate), 2) AS total_with_tax
FROM sales_data;
-- 字串函數應用範例
WITH customer_data AS (
SELECT
' 張大明 ' AS customer_name,
'daming.chang@example.com' AS email,
'0912-345-678' AS phone,
'PROD-A-001-2025' AS product_code
)
SELECT
-- 移除空白
TRIM(customer_name) AS trimmed_name,
LTRIM(customer_name) AS left_trimmed,
RTRIM(customer_name) AS right_trimmed,
-- 大小寫轉換
UPPER(customer_name) AS name_upper,
LOWER(customer_name) AS name_lower,
INITCAP(customer_name) AS name_capitalized,
-- 字串長度
LENGTH(customer_name) AS name_length,
LENGTH(TRIM(customer_name)) AS trimmed_length,
-- 字串擷取
SUBSTR(email, 1, POSITION('@' IN email) - 1) AS email_username,
SUBSTR(email, POSITION('@' IN email) + 1) AS email_domain,
-- 字串位置
POSITION('@' IN email) AS at_position,
CHARINDEX('example', email) AS example_position,
-- 字串替換
REPLACE(phone, '-', '') AS phone_no_dash,
REPLACE(phone, '-', ' ') AS phone_with_space,
-- 字串連接
CONCAT(customer_name, ' (', email, ')') AS full_info,
customer_name || ' - ' || phone AS contact_info,
-- 字串分割
SPLIT(product_code, '-') AS code_parts,
SPLIT_PART(product_code, '-', 1) AS category,
SPLIT_PART(product_code, '-', 2) AS subcategory,
SPLIT_PART(product_code, '-', 4) AS year
FROM customer_data;
-- 正則表達式函數範例
WITH text_data AS (
SELECT
'訂單編號：ORD-2025-12345，金額：NT$15,000' AS order_text,
'Email: contact@example.com, Phone: 0912-345-678' AS contact_info
)
SELECT
order_text,
-- 正則表達式匹配
REGEXP_SUBSTR(order_text, 'ORD-[0-9]+-[0-9]+') AS order_id,
REGEXP_SUBSTR(order_text, '[0-9,]+') AS amount_text,
-- 正則表達式替換
REGEXP_REPLACE(order_text, 'NT\\$', '新台幣 ') AS formatted_text,
REGEXP_REPLACE(contact_info, '[0-9]{4}-[0-9]{3}-[0-9]{3}', '***-***-***') AS masked_phone,
-- 正則表達式計數
REGEXP_COUNT(contact_info, '[0-9]') AS digit_count
FROM text_data;
-- 日期時間函數應用範例
SELECT
CURRENT_DATE() AS today,
CURRENT_TIMESTAMP() AS now,
CURRENT_TIME() AS current_time,
-- 日期加減
DATEADD(day, 7, CURRENT_DATE()) AS next_week,
DATEADD(month, -1, CURRENT_DATE()) AS last_month,
DATEADD(year, 1, CURRENT_DATE()) AS next_year,
-- 日期差異
DATEDIFF(day, '2025-01-01', CURRENT_DATE()) AS days_since_new_year,
DATEDIFF(month, '2024-01-01', CURRENT_DATE()) AS months_since_last_year,
-- 日期部分擷取
YEAR(CURRENT_DATE()) AS current_year,
MONTH(CURRENT_DATE()) AS current_month,
DAY(CURRENT_DATE()) AS current_day,
DAYOFWEEK(CURRENT_DATE()) AS day_of_week, -- 0=週日, 6=週六
DAYNAME(CURRENT_DATE()) AS day_name,
QUARTER(CURRENT_DATE()) AS current_quarter,
WEEKOFYEAR(CURRENT_DATE()) AS week_of_year,
-- 日期截斷
DATE_TRUNC('month', CURRENT_DATE()) AS month_start,
DATE_TRUNC('year', CURRENT_DATE()) AS year_start,
DATE_TRUNC('week', CURRENT_DATE()) AS week_start,
-- 最後一天
LAST_DAY(CURRENT_DATE()) AS month_end,
LAST_DAY(CURRENT_DATE(), 'year') AS year_end,
-- 工作日計算
CASE DAYOFWEEK(CURRENT_DATE())
WHEN 0 THEN '週日'
WHEN 1 THEN '週一'
WHEN 2 THEN '週二'
WHEN 3 THEN '週三'
WHEN 4 THEN '週四'
WHEN 5 THEN '週五'
WHEN 6 THEN '週六'
END AS weekday_name_chinese;
-- 條件函數應用範例
WITH order_data AS (
SELECT
'ORD-001' AS order_id,
15000 AS amount,
'completed' AS status,
NULL AS discount_code
)
SELECT
order_id,
amount,
status,
-- CASE 表達式
CASE status
WHEN 'pending' THEN '待處理'
WHEN 'processing' THEN '處理中'
WHEN 'completed' THEN '已完成'
WHEN 'cancelled' THEN '已取消'
ELSE '未知狀態'
END AS status_chinese,
-- IFF 函數（三元運算子）
IFF(amount >= 10000, '大額訂單', '一般訂單') AS order_type,
-- COALESCE 函數（回傳第一個非 NULL 值）
COALESCE(discount_code, '無折扣碼') AS discount_display,
-- NULLIF 函數（兩值相等時回傳 NULL）
NULLIF(status, 'cancelled') AS active_status,
-- GREATEST 與 LEAST
GREATEST(amount, 5000) AS adjusted_amount,
LEAST(amount, 50000) AS capped_amount
FROM order_data;
-- 轉換函數應用範例
SELECT
-- TO_VARCHAR / TO_CHAR
TO_VARCHAR(12345) AS number_to_string,
TO_CHAR(CURRENT_DATE(), 'YYYY-MM-DD') AS formatted_date,
TO_CHAR(12345.67, '999,999.99') AS formatted_number,
-- TO_NUMBER / TO_DECIMAL
TO_NUMBER('12345') AS string_to_number,
TO_DECIMAL('123.45', 10, 2) AS string_to_decimal,
-- TO_DATE / TO_TIMESTAMP
TO_DATE('2025-01-15', 'YYYY-MM-DD') AS string_to_date,
TO_TIMESTAMP('2025-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS') AS string_to_timestamp,
-- TRY 系列函數（失敗時回傳 NULL）
TRY_TO_NUMBER('abc') AS try_number,
TRY_TO_DATE('invalid') AS try_date,
-- CAST 轉換
CAST('123' AS INTEGER) AS cast_to_integer,
CAST(123.45 AS VARCHAR) AS cast_to_string;
這些內建函數涵蓋了日常開發中絕大部分的需求。在實務應用中，靈活組合這些函數能夠解決複雜的資料處理問題。例如，在處理客戶資料清理時，可以組合使用 TRIM、UPPER、REGEXP_REPLACE 來標準化姓名格式。在財務報表生成時，可以使用 ROUND、TO_CHAR 來格式化金額顯示。
Snowflake 還提供了許多進階函數如 HASH、MD5、SHA2 用於資料雜湊，ENCRYPT、DECRYPT 用於資料加密，COMPRESS、DECOMPRESS 用於資料壓縮。這些函數在資料安全與效能最佳化方面非常有用。
視窗函數的進階分析技巧
視窗函數是 SQL 中最強大的分析工具之一。它允許我們在保留原始列的同時，進行跨列的計算與分析。Snowflake 全面支援 ANSI SQL 標準的視窗函數，並提供了一些增強功能。
視窗函數的核心概念是定義一個資料視窗，然後在這個視窗上執行計算。視窗由 OVER 子句定義，可以包含 PARTITION BY（分區）、ORDER BY（排序）、ROWS/RANGE（框架）等元素。PARTITION BY 將資料分成多個獨立的群組，每個群組內獨立計算。ORDER BY 定義視窗內的排序。ROWS/RANGE 定義當前列的計算框架。
@startuml
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 120
package "視窗函數運作機制" {
[原始資料表] as RawData
[PARTITION BY 分區] as Partition
[ORDER BY 排序] as OrderBy
[視窗框架定義] as Frame
[視窗函數計算] as Calculation
[結果集] as Result
}
RawData --> Partition : 依指定欄位分組
Partition --> OrderBy : 各分區內排序
OrderBy --> Frame : 定義計算框架
Frame --> Calculation : 執行視窗函數
Calculation --> Result : 回傳計算結果
note right of Partition
PARTITION BY 將資料
分成多個獨立群組
每個群組獨立計算
end note
note right of OrderBy
ORDER BY 定義群組內
的資料排列順序
影響排名與累計計算
end note
note right of Frame
ROWS/RANGE 定義
當前列的計算範圍
預設為整個分區
end note
note right of Calculation
在定義的視窗上
執行聚合、排名
或分析函數
end note
@enduml這個流程圖展示了視窗函數的工作原理。資料首先依 PARTITION BY 分組，然後在各組內依 ORDER BY 排序，接著定義視窗框架，最後執行視窗函數計算並回傳結果。
-- Snowflake 視窗函數進階應用
-- 建立範例資料表
CREATE OR REPLACE TEMP TABLE sales_data (
sales_date DATE,
region VARCHAR(20),
product_category VARCHAR(30),
sales_amount NUMBER(12,2)
);
-- 插入範例資料
INSERT INTO sales_data VALUES
('2025-01-01', '北部', '電子產品', 150000),
('2025-01-01', '北部', '家電用品', 80000),
('2025-01-01', '中部', '電子產品', 120000),
('2025-01-01', '南部', '電子產品', 90000),
('2025-01-02', '北部', '電子產品', 180000),
('2025-01-02', '中部', '電子產品', 130000),
('2025-01-03', '北部', '電子產品', 160000),
('2025-01-03', '南部', '家電用品', 70000);
-- 範例 1：排名函數的比較
SELECT
sales_date,
region,
product_category,
sales_amount,
-- ROW_NUMBER：唯一序號（即使值相同也不重複）
ROW_NUMBER() OVER (
PARTITION BY sales_date
ORDER BY sales_amount DESC
) AS row_num,
-- RANK：排名可能不連續（相同值排名相同，下一個跳號）
RANK() OVER (
PARTITION BY sales_date
ORDER BY sales_amount DESC
) AS rank_num,
-- DENSE_RANK：排名連續（相同值排名相同，下一個不跳號）
DENSE_RANK() OVER (
PARTITION BY sales_date
ORDER BY sales_amount DESC
) AS dense_rank_num,
-- NTILE：將資料分成 N 個桶
NTILE(3) OVER (
PARTITION BY sales_date
ORDER BY sales_amount DESC
) AS tile_group
FROM sales_data
ORDER BY sales_date, sales_amount DESC;
-- 範例 2：聚合視窗函數
SELECT
sales_date,
region,
sales_amount,
-- 計算每日總銷售額（不使用 GROUP BY）
SUM(sales_amount) OVER (PARTITION BY sales_date) AS daily_total,
-- 計算佔比
ROUND(
sales_amount / SUM(sales_amount) OVER (PARTITION BY sales_date) * 100,
2
) AS percentage_of_daily_total,
-- 計算每日平均銷售額
AVG(sales_amount) OVER (PARTITION BY sales_date) AS daily_average,
-- 與平均值的差異
sales_amount - AVG(sales_amount) OVER (PARTITION BY sales_date) AS diff_from_avg,
-- 計算每日最大與最小值
MAX(sales_amount) OVER (PARTITION BY sales_date) AS daily_max,
MIN(sales_amount) OVER (PARTITION BY sales_date) AS daily_min,
-- 計算記錄數
COUNT(*) OVER (PARTITION BY sales_date) AS records_per_day
FROM sales_data
ORDER BY sales_date, region;
-- 範例 3：累計計算
SELECT
sales_date,
region,
sales_amount,
-- 累計銷售額（從第一筆到當前筆）
SUM(sales_amount) OVER (
ORDER BY sales_date, region
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_sales,
-- 累計平均
AVG(sales_amount) OVER (
ORDER BY sales_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_average,
-- 移動平均（當前筆與前兩筆的平均）
AVG(sales_amount) OVER (
ORDER BY sales_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3days,
-- 移動總和（前 3 筆的總和）
SUM(sales_amount) OVER (
ORDER BY sales_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_sum_3days
FROM sales_data
ORDER BY sales_date, region;
-- 範例 4：LAG 與 LEAD 函數（存取前後列的值）
SELECT
sales_date,
region,
sales_amount,
-- 取得前一天的銷售額
LAG(sales_amount, 1) OVER (
PARTITION BY region
ORDER BY sales_date
) AS previous_day_sales,
-- 計算日增長額
sales_amount - LAG(sales_amount, 1) OVER (
PARTITION BY region
ORDER BY sales_date
) AS daily_growth,
-- 計算日增長率
ROUND(
(sales_amount - LAG(sales_amount, 1) OVER (
PARTITION BY region
ORDER BY sales_date
)) / NULLIF(LAG(sales_amount, 1) OVER (
PARTITION BY region
ORDER BY sales_date
), 0) * 100,
2
) AS growth_rate_pct,
-- 取得後一天的銷售額
LEAD(sales_amount, 1) OVER (
PARTITION BY region
ORDER BY sales_date
) AS next_day_sales
FROM sales_data
ORDER BY region, sales_date;
-- 範例 5：FIRST_VALUE 與 LAST_VALUE
SELECT
sales_date,
region,
sales_amount,
-- 取得該區域第一天的銷售額
FIRST_VALUE(sales_amount) OVER (
PARTITION BY region
ORDER BY sales_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS first_day_sales,
-- 取得該區域最後一天的銷售額
LAST_VALUE(sales_amount) OVER (
PARTITION BY region
ORDER BY sales_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_day_sales,
-- 與第一天相比的成長
sales_amount - FIRST_VALUE(sales_amount) OVER (
PARTITION BY region
ORDER BY sales_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS growth_since_first_day
FROM sales_data
ORDER BY region, sales_date;
-- 範例 6：QUALIFY 子句簡化視窗函數過濾
-- 取得每個地區銷售額最高的記錄
SELECT
sales_date,
region,
product_category,
sales_amount
FROM sales_data
QUALIFY ROW_NUMBER() OVER (
PARTITION BY region
ORDER BY sales_amount DESC
) = 1;
-- 範例 7：複雜的視窗框架定義
SELECT
sales_date,
region,
sales_amount,
-- 當前列與前後各一列的平均（3 列視窗）
AVG(sales_amount) OVER (
ORDER BY sales_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS centered_moving_avg,
-- 從分區開始到當前列的總和
SUM(sales_amount) OVER (
PARTITION BY region
ORDER BY sales_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
-- 當前列到分區結束的計數
COUNT(*) OVER (
PARTITION BY region
ORDER BY sales_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS remaining_records
FROM sales_data
ORDER BY region, sales_date;
-- 範例 8：實務應用 - 客戶留存率分析
WITH customer_orders AS (
SELECT
customer_id,
order_date,
LAG(order_date, 1) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS previous_order_date,
DATEDIFF(day,
LAG(order_date, 1) OVER (
PARTITION BY customer_id
ORDER BY order_date
),
order_date
) AS days_since_last_order
FROM orders
)
SELECT
customer_id,
order_date,
previous_order_date,
days_since_last_order,
CASE
WHEN days_since_last_order IS NULL THEN '新客戶'
WHEN days_since_last_order <= 30 THEN '高活躍'
WHEN days_since_last_order <= 90 THEN '中活躍'
WHEN days_since_last_order <= 180 THEN '低活躍'
ELSE '流失風險'
END AS customer_status
FROM customer_orders;
視窗函數的強大之處在於它能夠在單一查詢中完成複雜的分析。相較於使用子查詢或自我連接，視窗函數通常有更好的效能與可讀性。在實務應用中，視窗函數常用於排名分析、時間序列分析、同期比較、移動平均計算等場景。
在台灣某零售業客戶的專案中，我們使用視窗函數建構了完整的銷售分析儀表板。透過 LAG/LEAD 函數計算同期成長率，透過 NTILE 函數進行客戶分群，透過累計視窗計算年度目標達成率。這些分析原本需要複雜的程序邏輯，但使用視窗函數後，整個查詢邏輯變得簡潔且高效。
自訂函數與儲存程序的開發
當內建函數無法滿足特定業務需求時，Snowflake 允許開發自訂函數（User-Defined Functions, UDF）與儲存程序（Stored Procedures）。這些可程式化物件讓我們能夠封裝複雜邏輯，提升程式碼的模組化與重用性。
Snowflake 支援三種 UDF 類型。SQL UDF 使用 SQL 語法定義，效能最好但功能有限。JavaScript UDF 使用 JavaScript 語法，提供更豐富的程式設計能力。Java UDF 使用 Java 語言，適合需要高效能或整合 Java 函式庫的場景。選擇適當的 UDF 類型取決於功能需求與效能考量。
-- Snowflake 自訂函數開發
-- 範例 1：SQL UDF - 計算台灣營業稅
CREATE OR REPLACE FUNCTION calculate_taiwan_vat(
amount NUMBER,
vat_rate NUMBER DEFAULT 0.05
)
RETURNS NUMBER(12,2)
AS
$$
ROUND(amount * vat_rate, 2)
$$;
-- 使用 SQL UDF
SELECT
order_id,
subtotal,
calculate_taiwan_vat(subtotal) AS vat_amount,
subtotal + calculate_taiwan_vat(subtotal) AS total_amount
FROM orders;
-- 範例 2：SQL UDF - 格式化台灣電話號碼
CREATE OR REPLACE FUNCTION format_taiwan_phone(phone_number VARCHAR)
RETURNS VARCHAR
AS
$$
CASE
-- 行動電話格式：0912-345-678
WHEN LENGTH(phone_number) = 10 AND LEFT(phone_number, 2) = '09'
THEN SUBSTR(phone_number, 1, 4) || '-' ||
SUBSTR(phone_number, 5, 3) || '-' ||
SUBSTR(phone_number, 8, 3)
-- 市內電話格式：02-2345-6789
WHEN LENGTH(phone_number) = 10 AND LEFT(phone_number, 2) = '02'
THEN SUBSTR(phone_number, 1, 2) || '-' ||
SUBSTR(phone_number, 3, 4) || '-' ||
SUBSTR(phone_number, 7, 4)
ELSE phone_number
END
$$;
-- 使用電話格式化 UDF
SELECT
customer_name,
format_taiwan_phone('0912345678') AS formatted_mobile,
format_taiwan_phone('0223456789') AS formatted_landline
FROM customers;
-- 範例 3：JavaScript UDF - 複雜的商業邏輯
CREATE OR REPLACE FUNCTION calculate_loyalty_points(
purchase_amount NUMBER,
customer_tier VARCHAR
)
RETURNS NUMBER
LANGUAGE JAVASCRIPT
AS
$$
// 基礎點數：每 100 元 1 點
var basePoints = Math.floor(PURCHASE_AMOUNT / 100);
// 根據客戶等級調整倍率
var multiplier = 1.0;
switch(CUSTOMER_TIER) {
case 'VIP':
multiplier = 3.0;
break;
case 'Gold':
multiplier = 2.0;
break;
case 'Silver':
multiplier = 1.5;
break;
default:
multiplier = 1.0;
}
// 特殊活動：滿 10000 元額外送 500 點
var bonusPoints = (PURCHASE_AMOUNT >= 10000) ? 500 : 0;
return Math.floor(basePoints * multiplier) + bonusPoints;
$$;
-- 使用 JavaScript UDF
SELECT
customer_id,
customer_name,
membership_level,
order_amount,
calculate_loyalty_points(order_amount, membership_level) AS earned_points
FROM customer_orders;
-- 範例 4：JavaScript UDF - JSON 資料處理
CREATE OR REPLACE FUNCTION extract_product_brands(products_json VARCHAR)
RETURNS ARRAY
LANGUAGE JAVASCRIPT
AS
$$
try {
var products = JSON.parse(PRODUCTS_JSON);
var brands = products.map(function(product) {
return product.brand;
});
// 去除重複並排序
return [...new Set(brands)].sort();
} catch(e) {
return [];
}
$$;
-- 使用 JSON 處理 UDF
SELECT
order_id,
extract_product_brands('[
{"name": "筆電", "brand": "ASUS"},
{"name": "滑鼠", "brand": "Logitech"},
{"name": "鍵盤", "brand": "ASUS"}
]') AS unique_brands;
-- 範例 5：表格函數（Table Function, UDTF）
-- 表格函數可以回傳多列資料
CREATE OR REPLACE FUNCTION generate_date_range(
start_date DATE,
end_date DATE
)
RETURNS TABLE (date_value DATE)
AS
$$
SELECT DATEADD(day, seq4(), start_date) AS date_value
FROM TABLE(GENERATOR(ROWCOUNT => 10000))
WHERE date_value <= end_date
$$;
-- 使用表格函數
SELECT date_value
FROM TABLE(generate_date_range('2025-01-01', '2025-01-31'))
ORDER BY date_value;
-- 範例 6：儲存程序 - 資料處理自動化
CREATE OR REPLACE PROCEDURE refresh_daily_summary(report_date DATE)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
// 刪除舊資料
var delete_stmt = snowflake.createStatement({
sqlText: "DELETE FROM daily_sales_summary WHERE report_date = ?",
binds: [REPORT_DATE]
});
delete_stmt.execute();
// 插入新資料
var insert_stmt = snowflake.createStatement({
sqlText: `
INSERT INTO daily_sales_summary
SELECT
? AS report_date,
region,
product_category,
SUM(sales_amount) AS total_sales,
COUNT(DISTINCT order_id) AS order_count,
CURRENT_TIMESTAMP() AS processed_at
FROM sales_data
WHERE DATE(order_date) = ?
GROUP BY region, product_category
`,
binds: [REPORT_DATE, REPORT_DATE]
});
var result = insert_stmt.execute();
// 取得插入的列數
var count_stmt = snowflake.createStatement({
sqlText: "SELECT COUNT(*) AS cnt FROM daily_sales_summary WHERE report_date = ?",
binds: [REPORT_DATE]
});
count_stmt.execute();
count_stmt.next();
var record_count = count_stmt.getColumnValue(1);
return "成功處理 " + record_count + " 筆記錄";
$$;
-- 執行儲存程序
CALL refresh_daily_summary('2025-01-15');
-- 範例 7：儲存程序 - 錯誤處理與交易控制
CREATE OR REPLACE PROCEDURE process_monthly_closing(month_end_date DATE)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
try {
// 開始交易
snowflake.execute({sqlText: "BEGIN TRANSACTION"});
// 步驟 1：凍結該月資料
snowflake.execute({
sqlText: `
UPDATE orders
SET is_locked = TRUE
WHERE DATE_TRUNC('month', order_date) =
DATE_TRUNC('month', ?)
`,
binds: [MONTH_END_DATE]
});
// 步驟 2：產生月度彙總
snowflake.execute({
sqlText: `
INSERT INTO monthly_summary
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS total_revenue,
COUNT(*) AS order_count,
CURRENT_TIMESTAMP() AS closed_at
FROM orders
WHERE DATE_TRUNC('month', order_date) =
DATE_TRUNC('month', ?)
AND is_locked = TRUE
GROUP BY DATE_TRUNC('month', order_date)
`,
binds: [MONTH_END_DATE]
});
// 步驟 3：更新客戶統計
snowflake.execute({
sqlText: `
MERGE INTO customer_stats cs
USING (
SELECT
customer_id,
SUM(total_amount) AS month_spending
FROM orders
WHERE DATE_TRUNC('month', order_date) =
DATE_TRUNC('month', ?)
AND is_locked = TRUE
GROUP BY customer_id
) o
ON cs.customer_id = o.customer_id
WHEN MATCHED THEN
UPDATE SET
total_lifetime_value = total_lifetime_value + o.month_spending,
last_updated = CURRENT_TIMESTAMP()
`,
binds: [MONTH_END_DATE]
});
// 提交交易
snowflake.execute({sqlText: "COMMIT"});
return "月結作業成功完成";
} catch(err) {
// 發生錯誤時回滾交易
snowflake.execute({sqlText: "ROLLBACK"});
return "月結作業失敗: " + err.message;
}
$$;
-- 執行月結程序
CALL process_monthly_closing('2025-01-31');
儲存程序相較於 UDF 有更強大的能力。它可以包含多個 SQL 陳述式，支援流程控制（if/else、迴圈），可以執行 DDL 與 DML 操作，還能夠進行交易管理。這讓儲存程序成為實作複雜業務流程的理想選擇。
在實務應用中，UDF 與儲存程序通常用於幾個場景。首先是封裝複雜的商業邏輯，例如價格計算、折扣規則、積分累計等。其次是資料標準化處理，例如地址格式化、電話號碼清理、身分證驗證等。再者是定期的資料處理任務，例如日結、月結、報表產生等。最後是與外部系統的整合，例如呼叫 API、資料同步等。
在某台灣金融機構的專案中，我們開發了一套完整的儲存程序來處理每日的交易對帳流程。這個流程涉及多個步驟：資料提取、格式轉換、比對驗證、差異處理、報表產生。透過儲存程序的交易控制與錯誤處理機制，確保了對帳流程的原子性與可靠性。
微分區與 Clustering 效能最佳化
Snowflake 的效能最佳化策略與傳統資料庫有顯著差異。沒有索引、沒有分區表，取而代之的是微分區與 Clustering 機制。理解這些機制是實現高效能查詢的關鍵。
微分區是 Snowflake 組織資料的基本單位。每個微分區包含 50MB 到 500MB 的壓縮資料，相當於未壓縮的 1GB 到 2GB 資料。微分區會自動維護元資料，記錄包含的資料範圍、唯一值數量、NULL 值計數等統計資訊。查詢最佳化器利用這些元資料來執行修剪，跳過不相關的微分區。
Clustering 是控制資料在微分區中排列方式的機制。透過定義 Clustering Key，我們可以引導 Snowflake 將相關資料儲存在同一個或鄰近的微分區中。這讓範圍查詢、JOIN 操作、GROUP BY 聚合等能夠更高效地執行。
-- Snowflake 微分區與 Clustering 最佳化
-- 範例 1：查看表格的微分區資訊
SELECT
table_name,
row_count,
bytes / (1024 * 1024 * 1024) AS size_gb,
micropartition_count,
clustering_key
FROM TABLE(INFORMATION_SCHEMA.TABLE_STORAGE_METRICS(
TABLE_NAME => 'LARGE_SALES_TABLE'
));
-- 範例 2：設定 Clustering Key
-- 對於經常依日期範圍查詢的表格，設定日期欄位為 Clustering Key
ALTER TABLE sales_transactions
CLUSTER BY (transaction_date);
-- 對於需要多欄位組合查詢的場景，可以設定多欄位 Clustering Key
ALTER TABLE customer_orders
CLUSTER BY (order_date, customer_id);
-- 範例 3：查看 Clustering 深度（評估 Clustering 效果）
-- Clustering Depth 越低表示 Clustering 效果越好
SELECT SYSTEM$CLUSTERING_DEPTH('sales_transactions', '(transaction_date)');
-- 詳細的 Clustering 資訊
SELECT SYSTEM$CLUSTERING_INFORMATION('sales_transactions', '(transaction_date)');
-- 範例 4：監控查詢的微分區修剪效果
-- 執行查詢後，檢視查詢效能統計
SELECT
query_id,
query_text,
execution_status,
total_elapsed_time / 1000 AS execution_seconds,
partitions_scanned,
partitions_total,
-- 修剪率：跳過的微分區比例
ROUND((partitions_total - partitions_scanned) /
NULLIF(partitions_total, 0) * 100, 2) AS pruning_rate_pct,
bytes_scanned / (1024 * 1024 * 1024) AS gb_scanned
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_id = LAST_QUERY_ID()
ORDER BY start_time DESC
LIMIT 1;
-- 範例 5：使用 SEARCH OPTIMIZATION SERVICE
-- 這是 Snowflake 的進階功能，自動建立最佳化索引
ALTER TABLE customer_master
ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id, email);
-- 也可以針對特定欄位啟用
ALTER TABLE products
ADD SEARCH OPTIMIZATION ON SUBSTRING(product_name);
-- 查看 Search Optimization 狀態
SHOW TABLES LIKE 'customer_master';
SELECT "search_optimization" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
-- 範例 6：手動觸發 Clustering 維護
-- Snowflake 會自動維護 Clustering，但也可以手動觸發
-- 注意：這會消耗運算資源
ALTER TABLE sales_transactions RECLUSTER;
-- 範例 7：實務案例 - 日期分區最佳化策略
-- 問題：大型交易表，經常依日期範圍查詢但效能不佳
-- 解決方案 1：設定日期 Clustering Key
ALTER TABLE large_transactions
CLUSTER BY (transaction_date);
-- 解決方案 2：如果資料量極大，考慮建立多個按月份分割的表格
-- 建立 2025 年 1 月的交易表
CREATE TABLE transactions_2025_01
CLONE large_transactions
WHERE DATE_TRUNC('month', transaction_date) = '2025-01-01';
-- 建立 UNION ALL 檢視表整合所有月份表格
CREATE OR REPLACE VIEW all_transactions AS
SELECT * FROM transactions_2025_01
UNION ALL
SELECT * FROM transactions_2025_02
UNION ALL
SELECT * FROM transactions_2025_03;
-- ... 其他月份
-- 範例 8：物化檢視表（Materialized Views）
-- 預先計算並儲存查詢結果，適合頻繁執行的複雜查詢
CREATE OR REPLACE MATERIALIZED VIEW daily_sales_summary AS
SELECT
DATE(order_date) AS sales_date,
region,
product_category,
SUM(order_amount) AS total_sales,
COUNT(DISTINCT customer_id) AS customer_count,
COUNT(*) AS order_count,
AVG(order_amount) AS avg_order_value
FROM orders
WHERE order_status = 'completed'
GROUP BY
DATE(order_date),
region,
product_category;
-- 查詢物化檢視表時，直接使用預先計算的結果
SELECT * FROM daily_sales_summary
WHERE sales_date = '2025-01-15'
ORDER BY total_sales DESC;
-- 範例 9：資料載入最佳化
-- 使用 COPY INTO 搭配檔案格式選項
COPY INTO target_table
FROM @my_stage/data_files/
FILE_FORMAT = (
TYPE = 'CSV'
FIELD_DELIMITER = ','
SKIP_HEADER = 1
COMPRESSION = 'GZIP'
)
ON_ERROR = 'CONTINUE'; -- 遇到錯誤時繼續處理其他檔案
-- 使用 Snowpipe 進行即時資料載入
-- Snowpipe 會自動偵測新檔案並載入
CREATE OR REPLACE PIPE my_pipe
AUTO_INGEST = TRUE
AS
COPY INTO target_table
FROM @my_stage
FILE_FORMAT = my_csv_format;
-- 範例 10：Query Profile 分析
-- 使用 Query Profile 檢視查詢執行計畫與效能瓶頸
-- 在 Snowflake Web UI 中：
-- 1. 執行查詢
-- 2. 點選 "Query ID" 連結
-- 3. 檢視 "Profile" 標籤
-- 4. 分析各個操作節點的時間與資料量
-- 範例 11：結果快取利用
-- Snowflake 會快取查詢結果 24 小時
-- 相同的查詢在快取期間內會立即回傳結果，不消耗運算資源
SELECT COUNT(*) FROM large_table; -- 第一次執行：掃描資料
SELECT COUNT(*) FROM large_table; -- 第二次執行：使用快取
-- 可以使用特殊語法繞過快取
SELECT COUNT(*) FROM large_table WHERE RANDOM() >= 0;
Clustering 的設定需要根據實際查詢模式來決定。如果查詢主要依單一欄位過濾，設定該欄位為 Clustering Key 即可。如果查詢涉及多個欄位的組合條件，可以設定多欄位 Clustering Key，但要注意順序。高基數欄位（唯一值多）應該放在前面，低基數欄位放在後面。
在某台灣電商平台的專案中，我們面臨訂單表效能問題。該表包含數億筆訂單資料，查詢通常依訂單日期範圍進行過濾。我們設定 CLUSTER BY (order_date) 後，查詢效能提升了 10 倍以上。微分區修剪率從原本的 20% 提升至 95%，這意味著大部分不相關的資料都被跳過了。
物化檢視表是另一個強大的效能最佳化工具。它預先計算並儲存查詢結果，查詢時直接讀取儲存的結果而非重新計算。物化檢視表會自動維護，當基礎表資料變更時，Snowflake 會在背景更新物化檢視表。這讓我們能夠在不修改應用程式的情況下，大幅提升複雜報表查詢的效能。
結語：建立企業級 Snowflake 資料平台
Snowflake 代表了雲端資料倉儲的新世代。它不僅提供了強大的 SQL 查詢能力，更重要的是降低了資料平台的建置與維運複雜度。透過分層架構、微分區技術、Clustering 機制，Snowflake 讓企業能夠專注在資料分析與業務價值創造上，而非基礎設施管理。
本文系統性地介紹了 Snowflake SQL 的進階技術，從核心語法到效能調校，從內建函數到自訂開發，從視窗分析到儲存程序。這些技術構成了 Snowflake 開發的完整知識體系。掌握這些技能，能夠讓開發者在 Snowflake 平台上構建高效、可靠的資料解決方案。
對於正在評估或學習 Snowflake 的台灣讀者，筆者建議從實務專案出發，在解決真實問題的過程中深化技術理解。Snowflake 的免費試用方案提供了充足的資源進行學習實驗。同時，Snowflake 的社群與文件資源非常豐富，遇到問題時通常能快速找到解答。
雲端資料平台正在重塑企業的資料架構。Snowflake 以其創新的技術與完整的生態系統，成為這波浪潮中的領導者。期望本文能為讀者在 Snowflake 技術學習與應用上提供實用的指引，協助大家在資料工程的道路上走得更遠更穩。
