一张新表上线前必做的「体检清单」——数据剖析（Data Profiling）实战

数据质量手册第 5 题约 10 分钟读完

本文目录

先说结论
为什么Profiling是新表上线的必经环节？
Profiling的6个核心维度（含SQL实战）
维度4-6：关联性、时间趋势、异常值
一键Profiling工具对比
我的「新表体检清单」（含Wood实战版）
面试高频追问
实战踩坑：跳过Profiling直接迁移导致的回退事故
总结

先说结论

数据剖析的核心产出：每个字段的「画像」。包括6个维度：

• 基础统计：行数、空值率、非空率

• 分布特征：最大、最小、均值、分位数、唯一值数

• 数据格式：长度分布、正则模式、编码

• 关联性：与其他字段的相关性、外键命中率

• 时间趋势：行数/空值率随时间的变化

• 异常值：超过3σ的离群点、罕见枚举值

有了这套画像，DQC规则不用拍脑袋——直接从画像生成。

先把这段讲顺；面试官追问时，再展开下面的原理和细节。

详细讲解

Data Profiling（数据剖析）是数据质量里最被低估的一个环节。新表上线、接入新数据源、迁移历史数据——这些场景下，先做一次完整的Profiling，能把90%的潜在质量问题暴露出来，比上线后被动救火高效10倍。我在出海硬件公司做EMR到Databricks迁移时，1000+张表全部先Profile再迁移，零数据故障完成。今天聊聊Profiling到底怎么做。

为什么Profiling是新表上线的必经环节？

我见过太多团队的新表上线流程：拿到上游需求 → 写SQL → 上线 → 出问题。少了关键一步：搞清楚源数据到底长什么样。

-- 一个真实的「不Profile就上线」翻车案例
-- 业务方说：上游user表有user_id、phone、email、country等字段
-- 数据开发：好的，写ETL同步过来
INSERT OVERWRITE TABLE dwd_user PARTITION(dt='${bizdate}')
SELECT user_id, phone, email, country FROM ods_user;
 
-- 上线后下游做country维度报表
-- 发现：country字段有20%是空值、30%是""空串、
-- 5%是中文（『美国』而不是US）、还有1%是大小写混乱（us/Us/US）
-- 全部清洗规则都要补，下游报表上线推迟一周

Profiling的6个核心维度（含SQL实战）

维度1：基础统计——10秒看懂一张表

-- 一行SQL搞定所有字段的基础统计
SELECT
  COUNT(*) AS total_rows,
  COUNT(user_id) AS user_id_non_null,
  COUNT(*) - COUNT(user_id) AS user_id_null_cnt,
  ROUND(1 - COUNT(user_id)/COUNT(*), 4) AS user_id_null_rate,
  COUNT(DISTINCT user_id) AS user_id_distinct,
  COUNT(*) - COUNT(DISTINCT user_id) AS user_id_dup_cnt
FROM ods_user;

维度2：分布特征——是否符合业务预期

-- 数值字段的分布画像
SELECT
  MIN(amount) AS min_v, MAX(amount) AS max_v,
  AVG(amount) AS avg_v, STDDEV(amount) AS std_v,
  PERCENTILE_APPROX(amount, 0.5) AS p50,
  PERCENTILE_APPROX(amount, 0.95) AS p95,
  PERCENTILE_APPROX(amount, 0.99) AS p99,
  -- 离群点：超过3σ
  SUM(CASE WHEN amount > AVG(amount) OVER() + 3*STDDEV(amount) OVER()
           THEN 1 ELSE 0 END) AS outlier_cnt
FROM ods_order;
 
-- 类别字段：枚举值分布
SELECT status, COUNT(*) AS cnt, COUNT(*)*1.0/SUM(COUNT(*)) OVER() AS pct
FROM ods_order GROUP BY status ORDER BY cnt DESC;

维度3：数据格式——文本字段最容易出问题

-- 长度分布
SELECT LENGTH(phone) AS len, COUNT(*) AS cnt
FROM ods_user WHERE phone IS NOT NULL
GROUP BY LENGTH(phone) ORDER BY cnt DESC;
-- 期望：phone长度应该在10-15之间，如果出现长度=0或>50的记录，要警觉
 
-- 正则模式
SELECT
  SUM(CASE WHEN email RLIKE '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$'
           THEN 1 ELSE 0 END) AS valid_email_cnt,
  COUNT(*) AS total
FROM ods_user WHERE email IS NOT NULL;

维度4-6：关联性、时间趋势、异常值

-- 维度4：外键命中率（参照完整性）
SELECT
  COUNT(o.order_id) AS total,
  COUNT(u.user_id) AS hit,
  ROUND(COUNT(u.user_id)*1.0/COUNT(o.order_id), 4) AS hit_rate
FROM ods_order o LEFT JOIN dim_user u ON o.user_id = u.user_id;
-- 期望：hit_rate >= 99.9%，过低说明上游数据有问题
 
-- 维度5：时间趋势（行数随天变化）
SELECT dt, COUNT(*) AS cnt,
       LAG(COUNT(*)) OVER(ORDER BY dt) AS prev_cnt,
       (COUNT(*) - LAG(COUNT(*)) OVER(ORDER BY dt))
         * 1.0 / LAG(COUNT(*)) OVER(ORDER BY dt) AS pct_change
FROM ods_user GROUP BY dt ORDER BY dt;
 
-- 维度6：异常值（罕见枚举）
SELECT country, COUNT(*) AS cnt FROM ods_user
GROUP BY country HAVING COUNT(*) < 10 ORDER BY cnt;
-- 出现频次极低的值往往是脏数据：拼写错误、测试数据

一键Profiling工具对比

工具	适用场景	输出形式	优点
pandas-profiling/ydata-profiling	Python+小数据	HTML报告	最美观、免费、内置异常检测
Great Expectations Profiler	Spark/Pandas	Expectation Suite	直接生成DQC规则
Deequ Analyzer	Spark大数据	Constraint+Metric	性能最强、增量Profile
DataWorks 数据探查	阿里云	可视化页面	零开发、和DQC打通
自写SQL	通用	数据表	灵活、可定制

我的「新表体检清单」（含Wood实战版）

我做EMR到Databricks迁移时，给团队定了一个12项体检清单，每张表上线前必须全部通过：

✅ 1. 总行数和过去7天对比，差异<5%

✅ 2. 主键字段无重复（COUNT = COUNT DISTINCT）

✅ 3. 主键字段空值率 = 0

✅ 4. 必填业务字段空值率 < 业务上限（如用户ID = 0%、邮箱 < 50%）

✅ 5. 数值字段在合理范围（amount > 0、age 0-150）

✅ 6. 枚举字段在白名单内（status属于已知列表）

✅ 7. 时间字段无未来值（event_time <= now）

✅ 8. 字符串字段长度合理（无超长截断）

✅ 9. 关键外键命中率 >= 99%

✅ 10. 数据量按天/小时趋势平稳，无突变

✅ 11. 业务勾稽校验通过（金额、库存等）

✅ 12. 字段编码一致（统一UTF-8、无乱码）

配合一个自动化脚本，跑完一张表只要5-10分钟。我在出海硬件公司用这套清单做完1000+表迁移，零数据故障。

面试高频追问

追问一：Profile结果怎么用？不就是看看吗？

Profiling的产出绝对不是「看一眼就完事」。三个用法：第一，自动生成DQC规则——用过去7天的P95、空值率、枚举集合作为DQC基线；第二，驱动数据契约——把字段类型、范围、枚举沉淀成Data Contract，下游可消费；第三，驱动数据治理——空值率高的字段是不是要废弃？冷字段是不是可以下线？Profile是治理的输入信号。

追问二：千亿行的表怎么Profile？

两个核心策略：第一，采样——10亿行采样100万行已经能反映分布；第二，增量Profile——只对今日分区Profile，结合历史指标做趋势对比，不要每次全表扫。Deequ的增量Metric机制就是为这个设计的。我在出海硬件公司处理过单日100亿行的事件表，采样1%+增量Profile，5分钟出报告。

追问三：Profiling和DQC的关系？

Profiling是「认识数据」，DQC是「持续监控数据」。Profiling一次性给你完整画像，DQC是把画像里的关键指标做成持续监控。两者的边界是：Profiling适合一次性深度分析（新表、迁移、调研），DQC适合长期运行的轻量级监控。生产实践里，Profile结果转成DQC规则是标配链路。

实战踩坑：跳过Profiling直接迁移导致的回退事故

EMR到Databricks迁移期间，有一张「设备激活表」业务方说很简单、不用Profile直接迁。我图省事就跳过了。结果迁移完发现：

• device_id字段有3%重复（业务方说应该是唯一的）——后来排查是测试设备没过滤

• country字段有15%是NULL（业务方以为100%有值）——上游某SDK版本没上报

• activate_time里2%是1970-01-01（明显的时间戳错误）

下游报表上线后立刻爆炸，被迫回退迁移、重新清洗、再次迁移，多花了两周时间。

教训：永远不要相信「这张表很简单」。Profiling的成本是几小时，不Profile的代价可能是几周。我从那次之后定了个铁律：所有上游迁移、所有新接入数据源、所有大版本变更，必须先Profile再动。

总结

Data Profiling = 数据画像的6个维度（统计、分布、格式、关联、趋势、异常）+ 工具（GE/Deequ/pandas-profiling）+ 12项体检清单。它是新表上线、数据迁移、源接入的必经环节。面试时讲清楚6个维度+体检清单+一个跳过Profile踩坑的故事，证明你不只懂DQC运行时监控，还懂质量左移的工程化思维。

下一篇聊一个数据科学和数据质量交叉的话题：指标突增200%是真的还是脏数据？统计学异常检测（3σ、MAD、Z-score）落地。

你们新表上线前会做Profiling吗？用什么工具？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长

数据质量面试通关手册

第6-10篇：异常检测与对账

Wood聊大数据

9年大数据老兵，聊技术、聊职场、聊成长

刷题进度保存在当前浏览器