ETL跑完了但数据是错的——数据准确性校验怎么做？

数据治理手册第 11 题约 11 分钟读完

本文目录

先说结论
值域校验：数据是否在合理范围内
逻辑校验：指标之间的勾稽关系
对比校验：和历史数据、外部数据交叉验证
常见的准确性Bug分类
JOIN膨胀的排查和预防
面试高频追问
实战踩坑
总结

先说结论

数据准确性校验的核心思路是「多维度交叉验证」：不能只看一个指标对不对，要用多种方法互相验证。

三种常用的校验方法：① 值域校验（数据是否在合理范围内）→ ② 逻辑校验（指标之间的勾稽关系是否成立）→ ③ 对比校验（和历史数据、外部数据交叉验证）。

准确性问题80%出在ETL逻辑上：JOIN条件写错了、过滤条件遗漏了、聚合方式不对、类型转换丢精度。

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

详细讲解

数据准确性是数据质量四性中最难治理的。完整性可以查NULL率，及时性可以查产出时间，但准确性——你怎么知道一个数字算得对不对？ETL跑完了没报错，任务状态全绿，但业务打开报表一看：数据翻倍了、金额对不上、用户数突然暴涨。这种「静默错误」最可怕，因为没有告警、没有报错，你不知道什么时候就出问题了。我在实际工作中被这种问题坑过很多次，今天分享一套系统化的准确性校验方法。

值域校验：数据是否在合理范围内

最基础的准确性检查——看数据的值是否在业务合理的范围内。比如年龄不应该是负数、订单金额不应该是几百万、经纬度不能超出地球范围。

-- 值域校验规则示例
SELECT ds,
  -- 金额异常
  SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END) AS negative_amount,
  SUM(CASE WHEN amount > 100000 THEN 1 ELSE 0 END) AS extreme_amount,
  -- 时间异常
  SUM(CASE WHEN event_time > NOW() THEN 1 ELSE 0 END) AS future_events,
  SUM(CASE WHEN event_time < '2020-01-01' THEN 1 ELSE 0 END) AS ancient_events,
  -- 枚举值异常
  SUM(CASE WHEN platform NOT IN ('ios','android','web') THEN 1 ELSE 0 END) AS invalid_platform,
  -- 比率异常
  SUM(CASE WHEN pay_rate > 1.0 OR pay_rate < 0 THEN 1 ELSE 0 END) AS invalid_rate,
  COUNT(*) AS total
FROM dwd_order_detail
WHERE ds = '${bizdate}';
-- 任何异常字段 > 0 都需要排查

我在某短剧出海公司搭建数据质量监控时，给每个核心表都配了值域校验规则。规则存在一张配置表里，每天自动跑。

逻辑校验：指标之间的勾稽关系

这是最有效但很多人不做的校验方法。所谓勾稽关系，就是指标之间天然存在的数学关系。如果这些关系不成立，说明某个指标算错了。

-- 逻辑校验：勾稽关系检查
 
-- 规则1: 付费用户数 <= DAU
SELECT ds, paid_users, dau,
  CASE WHEN paid_users > dau THEN 'ERROR' ELSE 'OK' END AS check1
FROM ads_daily_report WHERE ds = '${bizdate}';
 
-- 规则2: 总收入 = 各渠道收入之和
SELECT a.ds, a.total_revenue, b.channel_sum,
  ABS(a.total_revenue - b.channel_sum) AS diff,
  CASE WHEN ABS(a.total_revenue - b.channel_sum) > 1 THEN 'ERROR' ELSE 'OK' END AS check2
FROM ads_daily_report a
JOIN (
  SELECT ds, SUM(revenue) AS channel_sum
  FROM ads_channel_report
  WHERE ds = '${bizdate}' GROUP BY ds
) b ON a.ds = b.ds;
 
-- 规则3: 今日新增 + 昨日存量 ≈ 今日总量（允许±5%误差）
-- 如果偏差太大，说明去重或累计逻辑有问题

勾稽关系需要根据业务逻辑来设计，不能通用化。但一旦设计好，是发现准确性问题最灵敏的手段。我在出海硬件公司给核心报表设计了12条勾稽规则，上线后发现了3个之前没发现的计算错误。

对比校验：和历史数据、外部数据交叉验证

对比校验有两个维度：

① 和历史数据对比：今天的数据和昨天、上周同期、上月同期对比，如果波动超过阈值就告警。这个方法在第一篇讲过。

② 和外部数据对比：和第三方系统的数据交叉验证。比如我们的收入数据和支付系统的数据对比，用户数据和CRM系统对比。

-- 对比校验：多维度波动检测
SELECT
  today.ds,
  today.metric_name,
  today.value AS today_value,
  yesterday.value AS yesterday_value,
  last_week.value AS last_week_value,
  -- 环比
  ROUND((today.value - yesterday.value) * 100.0 / yesterday.value, 2) AS dod_pct,
  -- 周同比
  ROUND((today.value - last_week.value) * 100.0 / last_week.value, 2) AS wow_pct,
  -- 异常判定
  CASE
    WHEN ABS(dod_pct) > 30 AND ABS(wow_pct) > 30 THEN 'CRITICAL'
    WHEN ABS(dod_pct) > 20 OR ABS(wow_pct) > 20 THEN 'WARNING'
    ELSE 'NORMAL'
  END AS alert_level
FROM ads_daily_metrics today
LEFT JOIN ads_daily_metrics yesterday ON today.metric_name = yesterday.metric_name
  AND yesterday.ds = DATE_SUB(today.ds, 1)
LEFT JOIN ads_daily_metrics last_week ON today.metric_name = last_week.metric_name
  AND last_week.ds = DATE_SUB(today.ds, 7)
WHERE today.ds = '${bizdate}';

常见的准确性Bug分类

Bug类型	典型表现	根因	预防方法
JOIN导致行数膨胀	数据突然翻倍	多对多JOIN未去重	JOIN前检查唯一性
过滤条件遗漏	多算了不该算的数据	WHERE条件不完整	代码Review+测试
聚合粒度错误	数字偏大或偏小	GROUP BY漏字段	检查聚合前后行数
类型转换丢精度	金额尾数不对	FLOAT→INT丢小数	统一用DECIMAL
时区未转换	数据偏移一天	UTC vs 本地时间混用	统一用UTC存储
增量逻辑错误	数据重复或遗漏	增量条件写错边界	用INSERT OVERWRITE

其中JOIN膨胀是最常见的准确性Bug。我在实际工作中见过很多次：明明是LEFT JOIN，但JOIN之后行数比左表还多。原因几乎都是右表的JOIN key不唯一——一个key对应多条记录，JOIN之后左表的一条变成了多条。

JOIN膨胀的排查和预防

-- JOIN前必做的唯一性检查
-- 在JOIN之前，先确认右表的JOIN key是唯一的
SELECT join_key, COUNT(*) AS cnt
FROM dim_table
GROUP BY join_key
HAVING cnt > 1;
-- 如果有结果，说明JOIN key不唯一，需要先去重
 
-- 去重方法：用ROW_NUMBER取最新一条
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY update_time DESC) AS rn
  FROM dim_table
) t WHERE rn = 1;
 
-- JOIN后验证：检查行数是否膨胀
-- 原表行数
SELECT COUNT(*) FROM fact_table;  -- 假设100万
-- JOIN后行数
SELECT COUNT(*) FROM fact_table a LEFT JOIN dim_table b ON a.key = b.key;
-- 如果 > 100万，说明JOIN膨胀了

面试高频追问

追问一：你们的准确性校验是上线前做还是上线后做？

两个都做。上线前做的是「验证测试」——在测试环境用样本数据跑一遍，对比结果是否符合预期。上线后做的是「监控校验」——每天自动跑值域检查、勾稽检查、波动检查。上线前验证能发现逻辑错误，上线后监控能发现数据变化导致的新问题。两者缺一不可。

追问二：怎么处理浮点数精度问题？

金额类字段永远不要用FLOAT或DOUBLE，要用DECIMAL(18,2)。FLOAT在进行乘除运算时会有精度丢失，比如0.1+0.2在FLOAT里不等于0.3。我在出海硬件公司就遇到过：汇率转换时用了DOUBLE类型，结果金额尾数总是差几分钱。改成DECIMAL后问题消失了。另外在做金额聚合时，先SUM再ROUND，不要先ROUND再SUM，否则会累积误差。

追问三：数据准确性和数据一致性有什么区别？

准确性是指单个数据值是否正确——金额算对了吗、用户数去重了吗。一致性是指多个数据源之间是否对得上——ODS和DWS的数据一致吗、我们的系统和第三方系统一致吗。一个数据可能是准确的但不一致（两个系统各自算对了但口径不同），也可能一致但不准确（两个系统用了同样的错误逻辑）。

实战踩坑

我在某知名出海硬件品牌遇到过一个非常隐蔽的准确性Bug。有一张DWS层的用户日汇总表，用来统计每个用户每天的行为次数。某天业务说「最近几天的人均行为次数突然从8次涨到了12次」。

排查了两个小时才找到原因：上游DWD表做了一个逻辑变更，把原来的DISTINCT去重改成了非去重。原因是另一个同事优化SQL性能时，为了减少Shuffle数据量把DISTINCT去掉了，没意识到这个去重是业务逻辑需要的，不是多余的。

-- 出Bug的版本（优化后，去掉了DISTINCT）
INSERT OVERWRITE TABLE dws_user_daily PARTITION (ds = '${bizdate}')
SELECT user_id, COUNT(event_id) AS action_cnt  -- 没有DISTINCT，重复事件被重复计数
FROM dwd_user_event
WHERE ds = '${bizdate}'
GROUP BY user_id;
 
-- 正确的版本（必须DISTINCT）
INSERT OVERWRITE TABLE dws_user_daily PARTITION (ds = '${bizdate}')
SELECT user_id, COUNT(DISTINCT event_id) AS action_cnt  -- 事件去重
FROM dwd_user_event
WHERE ds = '${bizdate}'
GROUP BY user_id;
 
-- 教训：任何SQL变更都必须经过Code Review
-- Review时重点检查：去重逻辑、过滤条件、JOIN条件

解决方案：修复了SQL并重跑了受影响的日期。同时建立了两个机制：一是所有ETL SQL变更必须经过Code Review，Review清单里加了一条「是否改变了去重/过滤/聚合逻辑」。二是给核心DWS表加了勾稽校验——人均行为次数如果环比波动超过30%就告警。如果当时有这个校验，问题第一天就能发现，不会拖到几天后才被业务发现。

总结

数据准确性校验三板斧：值域校验（数据在合理范围内）、逻辑校验（指标间勾稽关系成立）、对比校验（和历史/外部数据交叉验证）。面试时重点讲「JOIN膨胀」和「去重逻辑被误删」这两个经典案例，再加上Code Review机制的预防手段，就很完整。

下一篇聊埋点方案的选择：客户端埋点丢了10%的数据——客户端埋点和服务端埋点到底怎么选？

你遇到过最难发现的准确性Bug是什么？评论区聊聊～

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

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