简介这是一份面向数据治理、数据仓库与大数据分析从业者的数据质量管理参考资料适合数据工程师、数据分析师及数据治理岗人员用于搭建质量检查体系、排查脏数据。内容围绕准确性、合规性、完备性、及时性、一致性、重复性六个维度展开并给出50个可直接落地的检查项逐条说明检查对象、检查方式与判定依据覆盖单字段、多列、跨表及汇总层面的比对规则同时整理了缺省值、异常值、不一致值与重复数据的成因、影响和处理办法。资源包共1个PDF文件约442KB篇幅紧凑、结构清晰便于打印或作为团队内部规范模板使用。目前已有1055人学习下载可用于快速梳理数据评估报告要点、设计ETL清洗规则与质量监控指标。1. 数据质量管理的6个维度先决定先查谁再决定查什么数据质量管理这个词听起来很宽落到项目上往往只剩一个具体问题这张表今天的数据能不能用。把这个问题拆开就得到固定数量的观察方向——完整性、唯一性、及时性、有效性、准确性、一致性也就是常说的6个维度。它们各自对应一种失效模式该有的没有、该唯一的重复、该准时到的没到、格式不合规、值和事实对不上、两处口径互相打架。50个检查项做的事是把这个框架钉到具体表、具体字段、具体阈值上让判断从我觉得差不多变成一条能跑出数字的 SQL。这套东西写给数仓开发、数据治理岗和指标口径负责人最合适上线前拦脏数据上线后能用一个数字回答这表到底能不能信。真正难的不是列清单而是让50条规则每天自己跑完、误报压得住、规则改了不返工。2. 把6个维度拆成50个检查项的字段映射50这个数字不是硬指标但它是工程上比较舒服的量级低于20条覆盖不了字段级的坑高于100条跑一遍要几十分钟误报多到没人看。下面的拆法是按维度分组的每个检查项都要能落到一张表 一个字段 一个判定表达式落不下去的说明它只是口号不该进清单。2.1 完整性、唯一性、及时性先卡住有没有、重不重、来没来这三类是数据质量的底线。完整性坏掉下游所有聚合都是错的唯一性坏掉指标会翻倍及时性坏掉报表准但不新业务照样不敢用。它们的共同特点是判定简单、SQL 便宜、必须零容忍或接近零容忍适合放在调度链路最前面跑。编号维度检查项判定方式DQ-01完整性主键字段非空率空值/空串占比必须为 0DQ-02完整性必填业务字段空值率订单号、客户号等阈值按字段分档DQ-03完整性表级行数非零当日分区行数 0DQ-04完整性分区存在性目标分区路径可读且有数据DQ-05完整性占位值识别N/A、-、未知 计入缺失DQ-06完整性外键维表命中率事实表关联维表未命中占比DQ-07完整性联合缺失省市区三字段同时为空DQ-08完整性关键属性填充率如客户手机号填充率基线对比DQ-09完整性宽表字段填充率偏离与 7 日滚动均值对比DQ-10唯一性单列主键重复GROUP BY 主键 HAVING COUNT1DQ-11唯一性联合业务键重复如 门店日期商品DQ-12唯一性全行重复全字段 GROUP BY 计数DQ-13唯一性软删后仍重复过滤 is_deleted 后复查DQ-14唯一性一实体多编码同一手机号对应多个客户 IDDQ-15唯一性跨分区重复相邻两天分区主键交集DQ-16唯一性字典映射一对多码值表同码多义DQ-17及时性分区到达时间实际产出时间 vs SLA 时间点DQ-18及时性数据新鲜度now() - max(etl_time) 秒数DQ-19及时性上游任务完成依赖依赖任务结束时间校验DQ-20及时性字段更新滞后状态字段 max(update_time) 滞后DQ-21及时性增量延迟源端时间戳与落库时间差DQ-22及时性快照缺失日快照表当日无记录唯一性的检查有个细节要注意GROUP BY ... HAVING COUNT(1) 1在亿级表上很贵通常的做法是先按主键 hash 取模抽样或者在写入时用ROW_NUMBER()打标事后直接查标记位把 O(n log n) 的排序摊到写入阶段。2.2 有效性、准确性、一致性再卡住对不对、准不准、合不合这三类才是真正花时间的部分因为它们的判定规则来自业务不存在通用模板。有效性是字面上合规准确性是数值上和事实一致一致性是两处说法不冲突。它们的共同点是必须配白名单和基线否则误报压不住。编号维度检查项判定方式DQ-23有效性字段长度超限LENGTH(col) 定义长度DQ-24有效性日期合法性排除 2024-02-30 类非法日期DQ-25有效性手机号/邮箱正则正则匹配失败占比DQ-26有效性数值范围金额 ≥ 0比率 0~1DQ-27有效性枚举白名单码值不在字典内DQ-28有效性编码前缀规范如订单号必须以指定字符开头DQ-29有效性金额小数位超出 2 位小数DQ-30有效性时间戳单位秒/毫秒混用检测DQ-31有效性布尔取值收敛非 0/1/NULL 的取值DQ-32有效性JSON 可解析解析异常计数DQ-33准确性明细重算汇总明细 SUM vs 汇总值偏差DQ-34准确性源系统行数对账ODS 与源库 COUNT 差异DQ-35准确性源系统金额对账金额 SUM 差异容忍 0.01DQ-36准确性计算字段公式单价×数量 金额DQ-37准确性单位换算一致分/元、千克/克混用DQ-38准确性均值漂移当日均值偏离基线 3σDQ-39准确性分位数漂移P50/P95 同比变化DQ-40准确性离群值比例IQR 之外记录占比DQ-41准确性回刷后重算一致重跑结果与首次结果比对DQ-42准确性抽样人工复核随机 N 条人工判定DQ-43一致性同名字段跨表口径两表同字段值分布对比DQ-44一致性主外键属性一致关联后维度属性冲突DQ-45一致性字典版本一致上下游映射表版本号DQ-46一致性状态流转顺序下单→支付→发货时序DQ-47一致性汇总与明细口径两套口径结果差异DQ-48一致性时区与粒度天粒度对齐方式DQ-49一致性币种与单位多币种未换算混算DQ-50一致性多报表同指标同指标跨报表差异DQ-33 到 DQ-42 这一类准确性检查本质都是两个独立路径算出同一个数再比对所以最容易踩的坑是两条路径共用了同一段有问题的加工逻辑比对永远通过。经验做法是让对账的一端直接读源系统或 ODS 原始层不经过任何二次加工。DQ-41 的回刷一致性检查也值得单独提一句它不是每天跑而是在数据回刷、代码发版之后触发一次比对同一业务日期重跑前后的结果摘要。2.3 用一张 dq_rule 表把50个检查项存下来把检查项写死在脚本里改阈值就得发版50 条规则会迅速腐烂。常见做法是把规则本身当数据存进一张元数据表调度器只负责读表、拼 SQL、执行、回写结果。CREATE TABLE dq_rule ( rule_id VARCHAR(16) NOT NULL COMMENT 规则编号如 DQ-01, rule_name VARCHAR(128) NOT NULL COMMENT 规则中文名, dimension VARCHAR(32) NOT NULL COMMENT completeness/uniqueness/timeliness/validity/accuracy/consistency, target_table VARCHAR(128) NOT NULL COMMENT 目标表含库名, target_column VARCHAR(128) DEFAULT NULL COMMENT 字段级规则填写表级留空, check_type VARCHAR(32) NOT NULL COMMENT null_rate/duplicate/range/regex/recon 等, check_sql TEXT NOT NULL COMMENT 必须返回单行含 ${bizdate} 占位符, metric_field VARCHAR(64) NOT NULL DEFAULT bad_rate COMMENT 取哪一列作为指标值, threshold DECIMAL(20,6) NOT NULL COMMENT 阈值比例类填小数, compare_op VARCHAR(8) NOT NULL DEFAULT COMMENT 或 , severity VARCHAR(8) NOT NULL DEFAULT P2 COMMENT P0/P1/P2, owner VARCHAR(64) NOT NULL COMMENT 责任人用于告警路由, schedule_cron VARCHAR(32) NOT NULL DEFAULT 0 6 * * *, whitelist TEXT DEFAULT NULL COMMENT 豁免条件 SQL 片段, enabled TINYINT(1) NOT NULL DEFAULT 1, version INT NOT NULL DEFAULT 1, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (rule_id), KEY idx_dim_enabled (dimension, enabled) ) COMMENT数据质量规则元数据;这张表有几个字段是刻意留的。check_sql强制要求返回单行单指标是为了让执行器不用理解规则语义只管取数比较代价是复杂规则要写成子查询再包一层。metric_field让同一个规则既能返回bad_rate也能返回lag_seconds避免为每种检查类型写一个执行分支。whitelist存的是拼接进 WHERE 的豁免片段比如大促期间某几个门店允许行数波动它必须由人评审后写入不能由业务方自助修改。version用于规则变更留痕配合下一章的回归比对方便定位是规则改了还是数据变了。3. 50个检查项的 SQL 落地从单表校验到跨表对账有了规则表接下来是给每类 check_type 写一个能直接套用的 SQL 骨架。骨架的价值在于写第 51 条规则时只需要改表名、字段名和阈值不用重新想一遍语法。3.1 空值率与唯一性的通用模板空值率是最常用的骨架它必须同时返回总量、坏量和比例否则阈值判断会失真——100 行里 3 行坏和 1 亿行里 3 行坏是两回事。-- DQ-02 必填字段空值率返回单行 SELECT ${bizdate} AS stat_date, COUNT(1) AS total_rows, SUM(CASE WHEN customer_id IS NULL OR TRIM(customer_id) OR UPPER(TRIM(customer_id)) IN (N/A,NULL,-) THEN 1 ELSE 0 END) AS bad_rows, ROUND( SUM(CASE WHEN customer_id IS NULL OR TRIM(customer_id) OR UPPER(TRIM(customer_id)) IN (N/A,NULL,-) THEN 1 ELSE 0 END) / NULLIF(COUNT(1), 0), 6) AS bad_rate FROM dwd_order_di WHERE dt ${bizdate};NULLIF(COUNT(1), 0)是为了避免空分区时除零报错这个细节在补数据期间经常救场。占位值N/A、NULL、-必须显式列入很多系统导出的空不是真正的 NULL而是这些字符串只判IS NULL会完全漏掉。bad_rate作为metric_field返回阈值填 0.005 表示允许千分之五的缺失。唯一性模板要限制返回行数否则一条规则可能拉回几十万行明细把执行器打挂-- DQ-10 主键重复检查最多返回 100 个重复样本 SELECT order_id, COUNT(1) AS dup_cnt FROM dwd_order_di WHERE dt ${bizdate} GROUP BY order_id HAVING COUNT(1) 1 ORDER BY dup_cnt DESC LIMIT 100;这条 SQL 返回的是明细而不是单行指标所以要么在执行器里包一层SELECT COUNT(1) AS bad_rows FROM (...)要么把metric_field设成dup_cnt并让执行器判断结果集为空即通过。我一般选后者因为拿到的重复样本可以直接贴进告警消息排错时省一轮查询。3.2 跨表对账与一致性检查的写法对账类检查的核心是FULL OUTER JOIN既要比值也要比谁多了谁少了。只用 INNER JOIN 的话源系统多出来的记录会被静默忽略。-- DQ-34/35 行数与金额对账返回差异明细 SELECT COALESCE(a.order_id, b.order_id) AS order_id, COALESCE(a.dw_amt, 0) AS dw_amt, COALESCE(b.src_amt, 0) AS src_amt, COALESCE(a.dw_amt, 0) - COALESCE(b.src_amt, 0) AS diff_amt FROM ( SELECT order_id, SUM(pay_amt) AS dw_amt FROM dwd_order_pay_di WHERE dt ${bizdate} GROUP BY order_id ) a FULL OUTER JOIN ( SELECT order_id, SUM(pay_amt) AS src_amt FROM ods_pay_di WHERE dt ${bizdate} GROUP BY order_id ) b ON a.order_id b.order_id WHERE ABS(COALESCE(a.dw_amt, 0) - COALESCE(b.src_amt, 0)) 0.01 OR a.order_id IS NULL OR b.order_id IS NULL LIMIT 200;差额阈值 0.01 不是随便定的金额字段通常是 DECIMAL(18,2)浮点转字符串再转回来会产生 1 分以内的误差把阈值设成 0 会让对账每天报几十条假差异。如果两边的金额单位不同一方是分、一方是元要先在子查询里统一别指望下游比对时自动发现——DQ-49 就是专门抓这个的。时间流转类一致性检查DQ-46用窗口函数更直观-- DQ-46 状态流转顺序校验发货时间早于支付时间 SELECT order_id, pay_time, ship_time FROM dwd_order_status_di WHERE dt ${bizdate} AND pay_time IS NOT NULL AND ship_time IS NOT NULL AND ship_time pay_time LIMIT 100;这类检查最容易出的问题是把允许为空和允许乱序混为一谈。正确写法是先要求两个时间都存在再比较先后否则未支付订单会全部命中。3.3 用一段 Python 把规则表跑起来执行器的职责很小读规则、替换占位符、执行、取指标、比阈值、写结果。它不应该包含任何业务逻辑。import time, json import pandas as pd from sqlalchemy import create_engine, text # 执行账号只读业务表对 dq_result 有写权限 engine create_engine( mysqlpymysql://dq_runner:***meta-host:3306/dq?charsetutf8mb4, pool_size5, pool_pre_pingTrue, connect_args{connect_timeout: 10}) def load_rules(dimensionNone): sql (SELECT rule_id, dimension, check_sql, metric_field, threshold, compare_op, severity, owner, whitelist FROM dq_rule WHERE enabled 1) params {} if dimension: sql AND dimension :dim params[dim] dimension return pd.read_sql(text(sql), engine, paramsparams) def run_rule(rule, bizdate): sql rule[check_sql].replace(${bizdate}, bizdate) if rule[whitelist]: # 豁免片段由人评审后写入 sql fSELECT * FROM ({sql}) t WHERE {rule[whitelist]} started time.time() df pd.read_sql(text(sql), engine) # 大表规则建议加 statement_timeout cost round(time.time() - started, 2) if df.empty: # 明细类规则空结果即通过 return {rule_id: rule[rule_id], bizdate: bizdate, metric_value: 0.0, passed: True, cost_sec: cost, bad_sample: None} value float(df.iloc[0][rule[metric_field]]) passed value float(rule[threshold]) if rule[compare_op] \ else value float(rule[threshold]) sample df.head(20).to_dict(records) return {rule_id: rule[rule_id], bizdate: bizdate, metric_value: value, passed: passed, cost_sec: cost, bad_sample: json.dumps(sample, ensure_asciiFalse, defaultstr)}几个参数值得说明。connect_args{connect_timeout: 10}防止某条慢规则把连接池占满df.empty分支处理的是明细类规则这类规则不返回指标行用空结果表达通过如果漏掉这个分支代码会在df.iloc[0]上抛异常而异常又容易被误判成规则失败。bad_sample只截前 20 行是为了避免 JSON 字段过大撑爆结果表。cost_sec单独记录事后可以按耗时排序把排在前面的规则优化掉——50 条规则里通常有 3 到 5 条吃掉了 80% 的时间。4. 让50个检查项每天跑完调度、阈值与告警分级规则能跑通只是第一步。50 条规则串行执行可能要一两个小时串行还会让单条失败阻断后面所有检查所以调度上要按维度拆并发并且把规则执行和告警推送彻底解耦。4.1 按维度拆分的调度 DAG一个可行的安排是6 个维度对应 6 个并发任务每个任务内部串行跑该维度的规则任务之间互不阻塞。这样即使准确性维度的对账 SQL 跑得很慢完整性维度的结果也能先出来。from datetime import datetime, timedelta from airflow import DAG from airflow.operators.python import PythonOperator from dq_runner import load_rules, run_rule, save_result DIMENSIONS [completeness, uniqueness, timeliness, validity, accuracy, consistency] def run_dimension(dim, **ctx): bizdate ctx[ds] # 逻辑日期由调度注入 for rule in load_rules(dim).to_dict(records): result run_rule(rule, bizdate) save_result(result, rule) # 幂等写入唯一键 rule_id bizdate with DAG( dag_iddq_50_checks, schedule_interval0 6 * * *, # 上游产出后 1 小时 start_datedatetime(2024, 1, 1), catchupFalse, max_active_tasks6, default_args{retries: 1, retry_delay: timedelta(minutes5), execution_timeout: timedelta(minutes30)}, tags[dq], ) as dag: for d in DIMENSIONS: PythonOperator(task_idfdq_{d}, python_callablerun_dimension, op_kwargs{dim: d})调度时间点选在上游产出后留一小时缓冲是给补数据和重跑留窗口如果和上游挨得太近每天都会因为上游慢几分钟而误报及时性。retries1只重试一次重试太多次会让告警延迟到没人关心的时候才发出。save_result必须用(rule_id, bizdate)做唯一键并做 upsert否则重试会写入重复结果后续统计通过率时数字会翻倍。4.2 阈值怎么设四种类型对应四种检查项阈值设错是数据质量平台被弃用的主要原因。全设成 0 会天天报几百条全设成宽松值又什么都发现不了。阈值类型适用检查项设置方法主要风险绝对零容忍DQ-01、DQ-03、DQ-10threshold 0上游重跑造成临时重复固定比例DQ-02、DQ-25、DQ-27取历史 30 天 P95 上浮 20%季节性业务波动同环比DQ-09、DQ-38、DQ-39波动 ±10%大促放宽到 ±30%需要维护日历表对账容忍DQ-34、DQ-35、DQ-47金额差 0.01行数差 0精度问题被误判绝对零容忍只适用于主键类检查而且要配合重跑窗口逻辑任务重跑期间同一个分区会被写两次这时的重复是正常的需要靠分区覆盖写或按批次号去重来规避。同环比阈值的日历表维护成本不低如果团队没有专门的活动日历退而求其次的做法是按最近 7 天中位数做基线比固定阈值稳也比日历表便宜。4.3 结果表与告警分级结果必须先落表再告警。只发消息不留痕的结果是三天后有人问上周这个字段到底空了多少没人答得上来。CREATE TABLE dq_result ( id BIGINT AUTO_INCREMENT PRIMARY KEY, bizdate DATE NOT NULL, rule_id VARCHAR(16) NOT NULL, dimension VARCHAR(32) NOT NULL, metric_value DECIMAL(20,6) DEFAULT NULL, threshold DECIMAL(20,6) DEFAULT NULL, passed TINYINT(1) NOT NULL, severity VARCHAR(8) NOT NULL, owner VARCHAR(64) NOT NULL, bad_sample JSON DEFAULT NULL, cost_sec DECIMAL(10,2) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_rule_date (rule_id, bizdate), KEY idx_date_dim (bizdate, dimension) ) COMMENT数据质量检查结果;告警按severity分级路由P0 只留给主键重复、主键为空、对账行数不一致这三类直接电话或群内 责任人P1 走每日汇总消息把当天所有未通过规则列成一张表P2 只入库不推送供周会看趋势。这样分级之后50 条规则每天真正打扰人的通常不超过 3 条。提示告警消息里必须带bad_sample的前 5 行和对应 SQL否则值班的人第一步还是要自己去查分级就白做了。5. 检查项写完之后的误报压制与规则版本管理规则上线第一周未通过数量通常会从 0 跳到几十条这不是数据突然变差而是历史遗留问题被一次性翻出来了。这时候最容易犯的错是直接放宽阈值把真问题也一起放走。更稳的做法是给每条规则加一个冷启动期新规则上线后先只记录不告警跑满 7 天用这 7 天的metric_value分布确定阈值再打开告警开关。5.1 豁免名单要单独一张表而不是塞进阈值豁免和阈值是两件事。阈值调宽是所有数据都放宽豁免是只放过特定范围内的数据比如新接入的三个门店允许 30 天空值率偏高。把豁免写成dq_rule.whitelist里的一段 SQL 片段虽然方便但没人能一眼看出到底豁免了什么。更清楚的做法是单独建一张dq_exemption表记录规则、生效日期区间、豁免对象和审批人执行器在比对前先查一次豁免表命中就把结果标记成exempted而不是passed两者的区别在周报里非常重要——豁免会过期通过不会。5.2 规则变更要走回归而不是直接改改check_sql或阈值时光看新规则跑出来的数字没有意义因为不知道这个数字是因为数据变了还是规则变了。可行的做法是每次改规则时把version加一同时用过去 7 天的历史分区各跑一次新旧两版规则把两份结果并排存进dq_result的对比视图。如果某天只有新版未通过、旧版通过说明是规则收紧抓到了新问题如果某天两版都未通过但metric_value差异超过 20%说明规则语义被改坏了。这个回归不需要多复杂的工具两个规则版本、七个分区、十四次查询就够了代价远低于一次口径事故的排查时间。5.3 用通过率和耗时两个指标做规则体检规则也是代码会腐烂。每个月底按rule_id聚合一次看两个数字一是通过率长期 100% 且metric_value恒为 0 的规则这类规则大概率已经失效——要么目标字段早就不写了要么判定条件写得永远不会命中该下线就下线二是cost_sec排在前 5 的规则把它们的时间拿出来单独看通常能发现某条规则忘了加分区过滤全表扫了一遍。把这两张清单放在一起过一遍50 条规则能砍掉 5 条左右剩下的跑得更快也更有人看。本文还有配套的精品资源点击获取