数据质量稽核实战：从口头对账到规则化校验落地

dbaai

数据质量稽核实战：从口头对账到规则化校验落地

一、具体的问题

先说一个我每年都要碰见几次的场景。月底财务来对账，报表上的销售额比业务系统少了几十万。数据团队拉群排查，两个人对着两套系统导 Excel，一行一行用眼睛比对，查了大半天，最后发现是三天前一次 ETL 重跑把某几个门店的数据覆盖了两遍——重跑时没有按分区覆盖，而是把增量又追加了一遍。

最后数据修好了，但整个过程暴露的问题没人解决：质量问题是被人工对账"碰"出来的，不是被系统"报"出来的。从数据出错到有人发现，中间隔了三天；从发现到修好，又花了两天。这五天里，基于错数据的每一个经营决策都是错的，而且没有人知道它们是错的。

多数团队的痛点不是"不会修数据"，而是三个更根本的问题：

• 发现晚。没有系统化的稽核，质量错误平均要等下游使用者（业务、财务、老板）投诉才暴露，发现的时机完全取决于运气。
  
• 定位慢。没有规则化的校验，每次排查都靠人工导表比对，同一个坑踩第二遍还是要从头查。
  
• 不敢信。因为曾经出过事，业务对数据永远将信将疑，于是人人都自己导一份原始数据再算一遍，数据团队辛苦建的报表反而没人用。
  

这篇文章讲的是怎么把上面三件事变成系统：用一套规则化的质量稽核体系，让错误在产生后几分钟内就被报警，而不是三天后被财务发现。

二、核心原理

质量问题的大头是"迟到"，不是"修不完"

很多人以为数据质量是"发现一个修一个"的体力活。实际统计过几次事故复盘后你会发现，真正损失大的从来不是错误本身——错误修复通常半小时就够——而是错误存活的时间：错误数据被下游加工、被汇报引用、被写进决策文档，等发现时已经污染了一整条链路。

所以稽核体系的第一目标不是"防住所有错误"（防不住），而是把发现时间从"天"压到"分钟"。错误在 T+0 的 ETL 窗口里就被拦住，下游根本没机会拿到脏数据。

六类规则，覆盖九成常见错误

不用发明复杂的东西，业界把数据质量规则归成六类，你按这个清单盘点一遍，绝大多数事故都能提前拦住：

规则类别	检查什么	典型错误
完整性	该有的行/列在不在	ETL 漏跑、分区缺失、上游断流
唯一性	主键/业务键是否重复	重跑追加、JOIN 发散
准确性	数值是否合理	汇率错、单位错、口径错
一致性	跨表/跨系统是否对得上	双写不一致、缓存脏读
及时性	数据是否按时到位	任务延迟、调度依赖断链
有效性	格式/枚举是否合法	脏值、越界、格式漂移

每张核心表不必全部六类都上，按业务重要性挑三到五条最痛的规则就够。订单表先上唯一性和完整性，汇率维度表先上准确性和及时性。

唯一性检查有一个反直觉的坑

检查重复，多数人第一反应是：

2. SELECT COUNT(*) - COUNT(DISTINCT order_id) FROM t_order;

复制代码

这个写法查不出最常见的一类重复：如果你的表是 INSERT ... ON DUPLICATE KEY UPDATE（UPSERT）写入的，同一行被更新后物理上只有一行，COUNT 永远相等。但业务上的错误可能是"两笔不同的业务共用了同一个业务单号"——这种情况要看的是业务键组合的唯一性（比如 user_id + sku_id + create_date），而不是物理主键。稽核规则里两类都要有，而且组合唯一性往往才是真正出事的那类。

跨系统一致性必须双向对账

业务库和数仓对账，只算"数仓有多少"是不够的，要两边各算一次，互相比对：

• 业务库按天聚合的订单数 vs 数仓同一天的订单数，差异数、差异率；
  
• 差异金额要单独算——行数对得上金额对不上（典型如负数冲销、退款漏算）往往比行数差异更致命。
  

双向对账才能区分两类完全不同的问题：源端本来就少（业务侧 bug，找业务团队）和同步过程丢了（ETL 问题，找数据团队）。单向对账发现差异后你要花一小时定位责任方，双向对账直接告诉你答案。

告警必须分级，否则等于没有告警

稽核规则一多，最大的风险是"狼来了"：每天几百条告警，没有人看。上线规则之前就定好分级：

• 阻断级（P0）：完整性缺失、主键重复、行数波动超阈值——任务失败并停止下游调度，人必须立即处理；
  
• 警告级（P1）：差异率在容忍范围内但不为零——入库放行，发消息给值班群；
  
• 记录级（P2）：格式漂移、越界值占比低于阈值——只落表不告警，周报里看趋势。
  

一条规则没有明确的处置动作，就不要让它进告警通道。

三、实例参考（动手步骤）

下面是一套可以照做的最小落地路径，以 MySQL 源 + MySQL/PG 数仓为例，全部用 SQL + 一个调度脚本就能跑起来，不依赖任何商业工具。

步骤 0：确认现状基线

先搞清楚你现在最痛的是哪类问题。把最近三次数据事故翻出来，各回答一个问题：错误在产生后多久被发现？被谁发现？如果有两次以上答案是"下游投诉"，那这套稽核体系对你就是刚需。

同时盘点核心表：挑出 5~10 张被下游引用最多的表（可以从报表 SQL 和调度依赖反推），作为第一批稽核对象。不要一上来就全量铺规则。

步骤 1：建稽核结果表

2. CREATE TABLE dq_check_result (
3.   id BIGINT AUTO_INCREMENT PRIMARY KEY,
4.   check_date DATE NOT NULL,
5.   table_name VARCHAR(128) NOT NULL,
6.   rule_code VARCHAR(64) NOT NULL,
7.   rule_level TINYINT NOT NULL COMMENT '0阻断 1警告 2记录',
8.   metric_value DECIMAL(18,4) NOT NULL,
9.   threshold DECIMAL(18,4),
10.   passed TINYINT NOT NULL,
11.   detail VARCHAR(512),
12.   created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
13.   KEY idx_date_table (check_date, table_name)
14. );

复制代码

所有规则的结果统一落这一张表，这是后面做趋势分析和告警的基础。

步骤 2：写核心稽核 SQL（六类各举一例）

完整性（分区/日期行数非零 + 环比波动）：

2. SELECT COUNT(*) AS cnt
3. FROM dws_order_df
4. WHERE dt = '${yesterday}';
6. -- 波动率规则：与 7 日均值比，偏差超过 ±30% 判失败
7. SELECT ABS(a.cnt - h.avg7) / h.avg7 AS drift
8. FROM (SELECT COUNT(*) cnt FROM dws_order_df WHERE dt='${yesterday}') a
9. CROSS JOIN (
10.   SELECT AVG(c) avg7 FROM (
11.     SELECT dt, COUNT(*) c FROM dws_order_df
12.     WHERE dt BETWEEN DATE_SUB('${yesterday}', INTERVAL 7 DAY)
13.                  AND DATE_SUB('${yesterday}', INTERVAL 1 DAY)
14.     GROUP BY dt) t
15. ) h;

复制代码

唯一性（物理主键 + 业务组合键，两种都要）：

2. SELECT COUNT(*) AS dup_cnt FROM (
3.   SELECT order_id FROM dws_order_df WHERE dt='${yesterday}'
4.   GROUP BY order_id HAVING COUNT(*) > 1
5. ) d;
7. SELECT COUNT(*) AS biz_dup FROM (
8.   SELECT user_id, sku_id, create_date FROM dwd_order_di
9.   WHERE dt='${yesterday}'
10.   GROUP BY user_id, sku_id, create_date HAVING COUNT(*) > 1
11. ) d;

复制代码

准确性（枚举与边界）：

2. -- 状态值超出枚举、金额为负、时间越界
3. SELECT COUNT(*) FROM dwd_order_di
4. WHERE dt='${yesterday}'
5.   AND (order_status NOT IN ('CREATED','PAID','SHIPPED','DONE','CANCELLED')
6.        OR pay_amount < 0
7.        OR pay_time > NOW());

复制代码

一致性（双向对账）：

2. -- 源端
3. SELECT SUM(pay_amount) FROM biz_db.t_order
4. WHERE create_time >= '${yesterday}' AND create_time < '${today}';
6. -- 仓端
7. SELECT SUM(pay_amount) FROM dwd_order_di WHERE dt='${yesterday}';
9. -- 两个值、两边的 COUNT 都拿到后，差异率 = ABS(a-b)/NULLIF(a,0)

复制代码

及时性： 不查数据查调度——检查稽核脚本启动时，当天分区目录或 _SUCCESS 标记文件是否存在，不存在即判 P0。

有效性： 手机号长度、身份证格式这类用正则抽样 1% 检查即可，全表正则开销太大。

步骤 3：脚本化 + 调度

把上面的 SQL 挂进一个 Python 脚本，规则写成配置而不是硬编码：

2. RULES = [
3.   # (rule_code, level, sql, pass_condition)
4.   ("row_count_nonzero", 0,
5.    "SELECT COUNT(*) FROM dws_order_df WHERE dt='{yesterday}'",
6.    lambda v: v > 0),
7.   ("row_drift_7d", 0,
8.    "...波动率 SQL...",
9.    lambda v: v <= 0.30),
10.   ("pk_dup", 0, "...", lambda v: v == 0),
11.   ("biz_dup", 0, "...", lambda v: v == 0),
12.   ("invalid_status", 1, "...", lambda v: v == 0),
13.   ("src_warehouse_diff", 1, "...", lambda v: v <= 0.001),
14. ]

复制代码

执行逻辑：逐条跑，结果写 dq_check_result；level=0 且失败 → 非零退出码让调度器（crontab / DolphinScheduler / Airflow 均可）中止下游任务并推 P0 告警；level=1 失败 → 只发群消息；level=2 → 只写表。

告警消息里必须带四样东西：表名、规则、当前值、阈值，让人不用打开数据库就知道要不要起床。

步骤 4：接入阻塞，形成卡点

只在 ETL 之后"顺手跑一下"的稽核形同虚设。关键是把 P0 规则嵌进调度依赖：稽核节点失败 → 下游所有任务不启动。跑两周之后，业务会慢慢习惯"数仓没数据是因为稽核拦了"，而不是"数仓数据又不对了"——信任就是这样一点点建回来的。

步骤 5：复盘与规则迭代

每月看一次 dq_check_result 的通过率趋势：一条规则连续一个月全通过且从未拦过东西，考虑它是否值得继续跑（成本问题）；拦过东西的规则，把当时的坑写进规则注释。规则库是活的，随事故增长。

四、实操检查清单

• [ ] 挑出 5~10 张被下游引用最多的核心表，作为第一批稽核对象
  
• [ ] 每张表至少覆盖：完整性（非零+波动率）、唯一性（物理主键+业务组合键）、一致性（双向对账）三类规则
  
• [ ] 稽核结果统一落 dq_check_result 表，含规则分级与阈值
  
• [ ] P0 规则失败 → 调度器中止下游任务（真实卡点，不是"建议"）
  
• [ ] P1 告警消息含表名/规则/当前值/阈值四要素
  
• [ ] 双向对账同时算行数与金额，差异率阈值明确（建议 ≤0.1%）
  
• [ ] 及时性规则用调度产物（分区目录/_SUCCESS 文件）判断，不依赖人工
  
• [ ] 每月复盘规则通过率，清理无效规则、补充新事故对应的规则
  
• [ ] 对账 SQL 与稽核结果保留 90 天以上，供事故回溯
  

几个容易踩的坑

1. 全量铺规则，上线一周告警爆炸，然后整个体系被废弃。 第一批只上 5 张表、每表 3~5 条规则，把 P0 告警控制在每天 0~2 条的噪音水平，再逐步扩面。

2. 阈值拍脑袋。 波动率阈值不要照抄别人的 30%，先用历史 30~60 天数据算出各表的自然波动区间（节假日、大促本身就会让行数翻倍），阈值定在自然波动之外。有条件的按星期几分别设基线。

3. 只对账行数不对账金额。 行数一致、金额差异的案例我至少见过三次：退款负数记录被 ETL 的 WHERE 条件过滤掉了，行数少了但两边 SUM 接近，其实是最大的坑。行数、金额、关键维度的 DISTINCT 数，三个一起对。

4. 稽核脚本自己挂了没人知道。 稽核进程要喂狗：调度器上给稽核节点本身设置超时和失败告警。最尴尬的事故是"稽核任务三天前就挂了，下游一直在跑没稽核过的数据"。

5. 把质量工具当成一次性项目。 上线大平台然后规则没人维护，三个月后形同虚设。一个 200 行的 Python 脚本 + 规则配置表，只要有人月度复盘，比没人用的商业平台有效得多。

治理前后对比

以某个电商数仓团队（核心表 8 张、日均 ETL 60+ 任务）接入这套稽核后的实际效果为例：

指标	治理前	治理后
错误平均发现时间	2~3 天（下游投诉）	8 分钟（ETL 窗口内告警）
月度数据事故（下游引用错误数据）	4~6 起	0~1 起（连续三个月 0 起）
月底人工对账工时	约 16 人时	约 1 人时（只看稽核报告）
业务侧"自己导数再算一遍"	普遍	基本消失
稽核规则数量	0	46 条（P0 12 / P1 19 / P2 15）
P0 误报率	—	<1 条/周

最后强调一句：这套体系的本质不是那 46 条 SQL，而是让每个错误都有明确的发现者和处置动作。规则可以慢慢加，卡点必须先立起来——稽核失败就停下游，这一条做到了，其余的都是时间问题。