4. 基于Snowflake的数据剖析实战

数据剖析（Data Profiling）是在正式建模、清洗或迁移之前，对数据本身做一次“体检”。在 Snowflake 中，这一过程几乎完全可以用 SQL 完成，不需要把数据导出到外部工具。本节围绕四个最常用的剖析维度展开：空值率、重复率、值域分布与异常模式，并给出可直接改写使用的 SQL 模板与判读方法。

一、剖析对象与采样策略

假设我们有一张订单明细表 SALES.ORDER_ITEM，包含字段：ORDER_ID、CUSTOMER_ID、PRODUCT_ID、ORDER_TS、QUANTITY、UNIT_PRICE、DISCOUNT、STATUS、SHIP_CITY。剖析前先明确两点：

• 剖析粒度：按列逐一统计，而不是整表笼统判断。
• 采样策略：大表全量扫描成本高，可用 TABLESAMPLE 或按时间分区抽样。例如 SAMPLE (10) 表示按 10% 比例采样，适合先做快速摸底；对关键字段再全量复核。

Snowflake 的 TABLESAMPLE 支持 BERNOULLI（按行随机）与 SYSTEM（按微分区）两种方式。做空值率与分布剖析时，建议用 SYSTEM 采样，速度快且对分布影响较小；做重复率剖析时，采样会低估重复，应尽量全量或按业务主键分区统计。

二、空值率：一次扫描得到所有列

逐列写 COUNT(*) 与 COUNT(col) 效率低。可以用 COUNT_IF 或 SUM(CASE WHEN ...) 在一条 SQL 中同时输出多列空值率：

SELECT
  COUNT(*) AS total_rows,
  ROUND(COUNT_IF(CUSTOMER_ID IS NULL) / COUNT(*) * 100, 2) AS null_customer_pct,
  ROUND(COUNT_IF(PRODUCT_ID  IS NULL) / COUNT(*) * 100, 2) AS null_product_pct,
  ROUND(COUNT_IF(ORDER_TS    IS NULL) / COUNT(*) * 100, 2) AS null_order_ts_pct,
  ROUND(COUNT_IF(QUANTITY    IS NULL) / COUNT(*) * 100, 2) AS null_qty_pct,
  ROUND(COUNT_IF(UNIT_PRICE  IS NULL) / COUNT(*) * 100, 2) AS null_price_pct,
  ROUND(COUNT_IF(SHIP_CITY   IS NULL) / COUNT(*) * 100, 2) AS null_city_pct
FROM SALES.ORDER_ITEM
SAMPLE SYSTEM (10);

判读时不要只看“有没有空值”，而要看空值率是否超过业务容忍阈值。例如主键类字段空值率应为 0；金额字段空值率超过 1% 就应追查上游；城市字段空值率 5% 以内可能属于正常缺失。空值率还常与时间相关，可加 DATE_TRUNC('month', ORDER_TS) 分组，观察空值是否集中在某段时间，从而定位上游采集故障。

三、重复率：区分完全重复与主键重复

重复率要分两种口径。第一种是整行完全重复，通常由重复加载造成：

SELECT COUNT(*) AS dup_rows
FROM (
  SELECT ORDER_ID, PRODUCT_ID, ORDER_TS, QUANTITY, UNIT_PRICE, COUNT(*) AS c
  FROM SALES.ORDER_ITEM
  GROUP BY ALL
  HAVING COUNT(*) > 1
);

第二种是业务主键重复，即同一 ORDER_ID + PRODUCT_ID 出现多次，但其他字段不同。这类重复更隐蔽，往往意味着同一订单被多次写入或状态被覆盖：

SELECT ORDER_ID, PRODUCT_ID, COUNT(*) AS cnt
FROM SALES.ORDER_ITEM
GROUP BY ORDER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY cnt DESC
LIMIT 20;

重复率 = 重复键数量 / 总键数量。若主键重复率大于 0，需要进一步判断是“应去重”还是“应保留多版本”。例如状态变更日志表允许同键多行，而订单明细表通常不允许。

四、值域分布：识别枚举、偏态与越界

对分类字段（如 STATUS、SHIP_CITY）统计频次，对数值字段（如 QUANTITY、UNIT_PRICE）统计分位数与极值：

-- 分类字段频次
SELECT STATUS, COUNT(*) AS cnt,
       ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 2) AS pct
FROM SALES.ORDER_ITEM
GROUP BY STATUS
ORDER BY cnt DESC;

-- 数值字段分位数
SELECT
  MIN(QUANTITY) AS min_q,
  APPROX_PERCENTILE(QUANTITY, 0.5)  AS p50,
  APPROX_PERCENTILE(QUANTITY, 0.95) AS p95,
  APPROX_PERCENTILE(QUANTITY, 0.99) AS p99,
  MAX(QUANTITY) AS max_q
FROM SALES.ORDER_ITEM;

值域分布能暴露三类问题：一是枚举越界，如 STATUS 出现未定义的 'UNKNOWN'；二是极端偏态，如 p99 远大于 p50，说明存在少量超大订单；三是非法值，如 QUANTITY 为负数或 UNIT_PRICE 为 0。对城市字段还可统计不同拼写（如 “Beijing” 与 “BEIJING”）造成的伪多样性。

五、异常模式：把统计结果串成判断链

单列统计只是起点，真正的异常模式往往出现在列与列的组合关系中。例如：DISCOUNT 大于 1、QUANTITY * UNIT_PRICE 与订单总额不一致、ORDER_TS 晚于发货时间等。可以用一条 SQL 同时输出多个异常计数：

SELECT
  COUNT_IF(DISCOUNT < 0 OR DISCOUNT > 1) AS bad_discount,
  COUNT_IF(QUANTITY <= 0)                 AS bad_qty,
  COUNT_IF(UNIT_PRICE <= 0)               AS bad_price,
  COUNT_IF(ORDER_TS > CURRENT_TIMESTAMP()) AS future_ts
FROM SALES.ORDER_ITEM;

下面的 SVG 把本节四个剖析维度组织成一条从采样到判读的信号链，展示每个维度关注的字段、计算方式与典型异常信号，便于在实战中按图逐项排查。

六、把剖析结果落成可执行判断

剖析的终点不是“跑出一堆数字”，而是形成可执行的判断。建议为每个关键字段设定三类阈值：硬约束（如主键空值率必须为 0，违反即阻断）、软告警（如金额空值率超过 1% 触发告警）、观察项（如城市字段的拼写多样性，仅记录趋势）。

在 Snowflake 中，可以把上述统计封装成视图或存储过程，配合 TASK 定时运行，将结果写入 DQ.PROFILE_RESULT 表，字段包括 PROFILE_TS、TABLE_NAME、COLUMN_NAME、METRIC、VALUE、THRESHOLD、SEVERITY。这样每次剖析都有历史可比，异常不再依赖人工记忆。

最后，剖析结果要能回答三个问题：这份数据能不能直接用？需要清洗哪些列？异常是偶发还是系统性？当空值率、重复率、值域分布与跨列异常四类信号同时指向同一字段时，该字段就是优先治理对象。把这一判断链固化成 SQL 与阈值表，数据剖析才能从一次性分析变成可持续的数据质量能力。