数据质量与治理

本章目标：能按完整性、唯一性、一致性、准确性、及时性五个维度设计检查，写出 6 类核心校验 SQL，并给出丢弃、填充、告警、隔离的处理方案。 前置知识：数据仓库入门、指标口径 预计用时：90 分钟

真实公司里，数据开发有相当一部分时间在处理数据质量。报表晚半小时可以忍，数字错了会直接导致决策错误。本章讲通用检查方法，真实数据里的异常案例和具体数量放在独立的项目线里处理。

1. 为什么数据质量必须前置

电商数据里常见的问题包括：

不在 DWD 层挡住，评价重复会让评分虚高、样本量虚增；时间倒挂会让履约时长出现负值；无明细订单会让“订单数”和“明细行数”永远对不上。错误还会顺着 ODS → DWD → DWS → ADS 一路传染，最后没人说得清是哪一步坏的。

注意：签收时间为空不一定是脏数据。未送达的有效订单没有签收时间，属于正常业务状态；只有状态已经是 delivered 却没有签收时间才是缺陷。能区分“业务空值”和“质量缺陷”，是数据开发的入门功。

2. 数据质量的五个维度

2.1 完整性

完整性看“应该有的值有没有”。空值要分三类：

• 业务空值：有效订单还没送到，delivered_date 为空，保留。
• 质量缺陷：状态已是 delivered，签收时间却为空，需要告警。
• 可填充空值：商品品类为空，可以补 unknown，但要在报表中单独展示，不能混进正常品类排名。

2.2 唯一性

唯一性看主键和业务键是否重复。order_reviews 的 review_id 本应唯一，一旦重复，会让评价均分被重复样本带偏。

注意“一单多明细”“一单多支付”都是正常的多行：同一个订单可以有多件商品、多次支付，这是业务事实，不是重复。只有业务主键（如 review_id）重复才是问题。

2.3 一致性

一致性包括：

• 外键一致：明细、支付、评价的 order_id 必须能在 orders 找到。
• 状态一致：取消或不可用的订单通常不应有支付记录，发现后作为告警而非错误（可能涉及退款、分期或先支付后取消）。
• 金额一致：订单支付总额应等于明细金额（商品价 + 运费）汇总，差异超过阈值的要逐单核查。
• 口径一致：DWD 行数、GMV 必须和项目维护的指标基线对得上。

2.4 准确性

准确性看值是否符合业务规则：

• 价格、运费、支付金额不能为负。
• 运费为 0 是合法的包邮，不能当异常。
• 时间顺序必须合理：下单 ≤ 审核 ≤ 承运 ≤ 签收。
• 评价时间不应早于签收时间。
• 枚举值必须在允许列表中，状态和支付方式的取值以业务字典为准。

2.5 及时性

及时性看数据是否按时到达：

• 当天分区是否生成。
• 任务是否在 SLA 时间前完成。
• 行数是否比历史均值异常偏低。
• 上游是否延迟。

及时性差的数据即使准确，也会让报表开天窗。

3. 六类核心检查 SQL

以下 SQL 以 ODS 表为例，用来发现入仓前的问题。生产上会把这些检查固化成任务，结果写入质量报告表。

3.1 主键唯一

SELECT order_id, COUNT(*) AS cnt
FROM ods_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

预期返回 0 行。评价表则按 review_id 检查：

SELECT review_id, COUNT(*) AS cnt
FROM ods_order_reviews
GROUP BY review_id
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

返回重复的 ID 和重复次数。明细表没有天然单列主键，同一订单多行是正常业务，只能按整行检查完全重复。

3.2 外键完整

SELECT '明细→订单' AS fk, COUNT(*) AS orphan_cnt
FROM ods_order_items i
LEFT JOIN ods_orders o USING (order_id)
WHERE o.order_id IS NULL

UNION ALL
SELECT '明细→商品', COUNT(*)
FROM ods_order_items i
LEFT JOIN ods_products p USING (product_id)
WHERE p.product_id IS NULL

UNION ALL
SELECT '明细→商家', COUNT(*)
FROM ods_order_items i
LEFT JOIN ods_sellers s USING (seller_id)
WHERE s.seller_id IS NULL

UNION ALL
SELECT '支付→订单', COUNT(*)
FROM ods_payments p
LEFT JOIN ods_orders o USING (order_id)
WHERE o.order_id IS NULL

UNION ALL
SELECT '评价→订单', COUNT(*)
FROM ods_order_reviews r
LEFT JOIN ods_orders o USING (order_id)
WHERE o.order_id IS NULL;

五项都返回 0。外键检查不通过时，先判断是源系统不一致还是采集丢数，不要直接删明细。

3.3 金额非负与金额对账

SELECT
    COUNT(*) FILTER (WHERE price < 0)             AS negative_price_cnt,
    COUNT(*) FILTER (WHERE freight_value < 0)     AS negative_freight_cnt,
    COUNT(*) FILTER (WHERE freight_value = 0)     AS free_freight_cnt,
    COUNT(*) FILTER (WHERE payment_value < 0)     AS negative_pay_cnt
FROM (
    SELECT price, freight_value, NULL AS payment_value FROM ods_order_items
    UNION ALL
    SELECT NULL, NULL, payment_value FROM ods_payments
) t;

非负检查应全部为 0；运费为 0 是包邮，不是错误。

支付与明细的金额对账：

WITH item_total AS (
    SELECT i.order_id, SUM(i.price + i.freight_value) AS amt
    FROM ods_order_items i
    JOIN ods_orders o USING (order_id)
    WHERE o.order_status NOT IN ('canceled', 'unavailable')
    GROUP BY i.order_id
),
pay_total AS (
    SELECT order_id, SUM(payment_value) AS amt
    FROM ods_payments
    GROUP BY order_id
)
SELECT COUNT(*) AS mismatch_cnt,
       ROUND(SUM(p.amt - i.amt), 2) AS net_diff
FROM item_total i
JOIN pay_total p USING (order_id)
WHERE ABS(p.amt - i.amt) > 0.01;

同一份数据，只算有效订单和把取消/不可用订单也算进来，会得到不同的差异单数和净差。报数字前先说口径，否则两个都对、却永远对不上。逐单差异应披露给业务，不要直接改订单金额。

3.4 日期合理

SELECT
    COUNT(*) FILTER (WHERE order_approved_at < order_purchase_timestamp)          AS approved_before_purchase,
    COUNT(*) FILTER (WHERE order_delivered_carrier_date < order_purchase_timestamp) AS carrier_before_purchase,
    COUNT(*) FILTER (WHERE order_delivered_customer_date < order_delivered_carrier_date) AS delivered_before_carrier,
    COUNT(*) FILTER (WHERE order_status = 'delivered'
                       AND order_delivered_customer_date IS NULL)                  AS delivered_missing_date
FROM ods_orders;

正常情况下这四项都应为 0。空值不参与比较，所以“有效订单但还没签收”不会被算成时间倒挂。

评价与签收的时间顺序：

SELECT COUNT(*) AS review_before_delivery
FROM ods_order_reviews r
JOIN ods_orders o USING (order_id)
WHERE o.order_delivered_customer_date IS NOT NULL
  AND r.review_creation_date < o.order_delivered_customer_date;

返回评价早于签收的记录数。这类记录可以保留，但分析“迟到与评分关系”时只按订单聚合 review_score，不要用评价时间做因果推断，避免把提前评价当成签收后的反馈。

3.5 枚举值合法

SELECT order_status, COUNT(*) AS cnt
FROM ods_orders
WHERE order_status NOT IN (
    'approved', 'canceled', 'created', 'delivered',
    'invoiced', 'processing', 'shipped', 'unavailable'
)
GROUP BY order_status;

预期 0 行。支付方式同理：

SELECT payment_type, COUNT(*) AS cnt
FROM ods_payments
WHERE payment_type NOT IN (
    'credit_card', 'debit_card', 'voucher', 'balance', 'unknown'
)
GROUP BY payment_type;

也返回 0。unknown 本身在允许列表里，但它出现时就意味着支付方式缺失，报表要单独统计。

3.6 波动监控

行数波动用“当天值”对比“过去 7 天均值”：

WITH daily AS (
    SELECT order_date AS dt, COUNT(DISTINCT order_id) AS order_cnt
    FROM dwd_order
    WHERE order_date >= DATE '2024-01-01'
      AND order_date < DATE '2024-02-01'
    GROUP BY order_date
),
base AS (
    SELECT dt, order_cnt,
           AVG(order_cnt) OVER (ORDER BY dt
                                ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS avg_7d
    FROM daily
)
SELECT dt, order_cnt, ROUND(avg_7d, 1) AS avg_7d
FROM base
WHERE avg_7d IS NOT NULL
  AND (order_cnt < avg_7d * 0.7 OR order_cnt > avg_7d * 1.3);

阈值 30% 只是示例，要按业务波动调整。大促期间订单暴涨属于正常，需要维护“预期波动日历”，否则每天都会误报。另外，日期边界要写死为数据范围：上界用 < 数据最后一天 + 1（左闭右开），不要写成未来年份，否则不存在的日期也会被当成正常数据。

4. 异常处理：丢弃、填充、告警、隔离

发现脏数据只是第一步，怎么处理决定了下游能不能信任数仓。

4.1 丢弃

丢弃只用于规则明确、且原始数据仍在 ODS 可回溯的情况，例如完全重复的日志按主键去重。对重复的 review_id 不要做简单行级去重：同一条评价可能关联不同订单，直接保留一行会丢信息；正确做法是在 DWD 按 order_id 聚合评分。不要静默丢弃，要记录被丢弃的行数和原因。

4.2 填充

缺品类补 unknown 是合理填充，因为“未知”本身就是一个可解释的分类。但订单没有明细不能凭空补金额，否则 GMV 会被伪造。填充的前提是默认值有业务含义。

4.3 告警

以下情况适合告警而不是自动处理：

• 取消或不可用订单出现支付（可能是退款或分期）。
• 支付总额与明细差异超过阈值。
• 状态为 delivered 却没有签收时间。
• 当天订单行数比 7 天均值低 30%（与 3.6 的示例阈值一致，实际按业务调整）。
• 分区到 SLA 时间还没生成。

4.4 隔离

隔离表保存原始行、错误类型、发现时间，供修复后重放：

CREATE TABLE dq_order_time_error (
    batch_date        DATE,
    error_type        STRING,
    order_id          STRING,
    purchase_at       TIMESTAMP,
    carrier_at        TIMESTAMP,
    delivered_at      TIMESTAMP,
    detail            STRING,
    created_at        TIMESTAMP
);

写入时用 INSERT INTO ... SELECT 把承运早于下单、签收早于承运的行原样搬进去，标记 error_type = 'time_inversion'。下游只读 DWD，不读隔离表；修复源数据后按批次重跑对应分区，再把隔离记录标记为已处理。

4.5 常见脏数据的处理决定

提示：订单没有明细不必从“有效订单数”里删掉，因为订单确实存在；但算 AOV、品类占比时必须用有明细的有效订单。同一份数据，不同指标不同口径，这正是 DWD 宽表和 ADS 层要分别对账的原因。

5. 数据治理的五个抓手

数据质量是治理的一部分。治理解决的是“长期有人负责、规则可查、成本可控”。

5.1 元数据

元数据分两类：

• 技术元数据：表结构、字段类型、分区、存储格式、文件大小、任务依赖。
• 业务元数据：指标定义、负责人、更新频率、敏感级别、使用说明。

项目线里的数据说明记录来源、许可、字段字典和重下命令，指标基线则用机器可读的文件保存，两者都属于最基础的元数据。

5.2 血缘

血缘描述数据从哪里来、经过哪些任务、流到哪里去：

ods_orders ─┐
ods_order_items ─┼→ dwd_order_detail → dws_category_1m → ads_category_rank
ods_products ─┘

血缘的用途：上游改字段时评估影响范围；报表出错时快速定位；下线表时确认没有下游依赖。

5.3 数据安全与 PII

PII（Personally Identifiable Information，个人可识别信息）包括手机号、身份证号、银行卡号、详细地址、邮箱等。数据开发每天都在接触这些字段，安全不是“安全部门的事”。

四条基本规则：

• 最小权限：按角色授权，只给完成工作必需的库表；不按个人堆权限，离职及时回收。
• 列级脱敏：敏感字段在 ADS 层对外前脱敏，例如手机号只保留后四位，身份证只保留前六位和后四位。
• 环境隔离：测试环境用脱敏数据，不直接复制生产库；开发同学不拿生产全量数据做实验。
• 可审计：生产数据访问要审批、留日志；导出数据要登记用途和保留期限。

脱敏 SQL 示例（示意写法，假设源表有 phone、id_card 字段；示例数据没有这两个字段）：

SELECT customer_id,
       CONCAT(SUBSTR(phone, 1, 3), '****', SUBSTR(phone, 8, 4)) AS phone_masked,
       CONCAT(SUBSTR(id_card, 1, 6), '********', SUBSTR(id_card, 15, 4)) AS id_masked
FROM ods_customers;

分层要求：

面试怎么答：先讲“最小权限 + 列级脱敏 + 环境隔离 + 审计”，再举一个具体例子，例如“报表只展示手机号后四位，明细表不向分析同学开放”。不要只说“我们会注意安全”。

注意：脱敏不是简单地把字段删掉。很多指标需要用户唯一标识，正确做法是保留哈希后的 user_key，既能关联又无法反推原值。

5.4 成本

• 存储：设置生命周期，ODS 原始数据按合规要求保留，临时表定期清理。
• 计算：避免全表扫描，分区裁剪和列裁剪必须生效。
• 小文件：过多小文件会增加 NameNode 压力和任务启动开销。
• 重复建设：同一指标不要在多个 ADS 表里各算一遍。

5.5 SLA

SLA 是数据交付承诺，例如“每天 07:00 前产出昨日 GMV”。要配套：

• 任务完成时间监控。
• 失败重试和告警。
• 值班人和升级路径。
• 核心表与非核心表分级，资源优先保障核心链路。

6. 把体检结果落成质量报告

6.1 质量报告表

CREATE TABLE dq_check_result (
    check_date  DATE,
    table_name  STRING,
    check_name  STRING,
    rule_desc   STRING,
    error_cnt   BIGINT,
    status      STRING,
    owner       STRING
);

每次跑完检查，把结果写一行。status 用 pass、warn、fail 三档，看板按表和日期展示趋势。

6.2 清洗后的对账基线

每次重跑后，行数和 GMV 必须与项目维护的指标基线一致。若不一致，先查清洗规则是否被改动，再查源数据是否变化。

6.3 一次完整的质量巡检流程

1. 检查 ODS 文件是否到齐、行数和 sha256 是否匹配
2. 检查主键唯一、外键完整（唯一性、一致性）
3. 检查金额、时间顺序、枚举（准确性）
4. 检查空值率和维度覆盖（完整性）
5. 对比昨日行数和 GMV（波动监控）
6. 生成质量报告和 metrics.json，失败项告警
7. 异常数据进隔离表，修复后按分区重跑
8. 用基线数字对账，确认下游恢复

7. 常见坑

1. 把业务空值当脏数据：未送达订单没有签收时间是正常的，全部删除会丢单。
2. 只做一次体检：上线后不监控，源系统变了也不知道。
3. 静默丢弃：不记录丢弃行数和原因，出了问题无法追溯。
4. 报差异不说口径：有效订单口径和全部订单口径的差异单数不同，混着说就永远对不上。
5. 告警阈值拍脑袋：不区分大促和日常，告警很快会被忽略。
6. 只查单表不查跨表：明细行数全对，但支付金额和明细对不上，报表照样错。
7. 把包邮当异常：运费为 0 是合法业务，不能按 freight <= 0 过滤。

8. 在线练习

自测：

• 能把“评价重复、支付差异、时间倒挂、无明细订单、缺品类”分别归到五个维度。
• 能写出空值率和主键重复检查 SQL。
• 能区分“错误、告警、信息”三级，并说明哪一级应该阻断构建。
• 能解释支付与明细的差异为什么只披露、不直接修改订单金额。

9. 面试延伸

面试题：你怎么保证数据质量？

是什么：数据质量要覆盖完整性、唯一性、一致性、准确性、及时性五个维度，并贯穿入仓前、加工中、上线后三个阶段。

解决什么问题：入仓前用探查 SQL 摸清脏数据；DWD 层做硬性校验，异常数据隔离而不是静默丢弃；上线后配监控告警，检查主键、外键、行数波动和核心指标，发现问题按分层逐级回溯。

我的实操边界：项目里我处理过评价去重、支付与明细对账、时间倒挂、缺品类、无明细订单 5 类问题，并做过行数和 GMV 对账；没有在生产环境搭建过完整的质量平台。

面试题：数据治理具体做什么？

是什么：数据治理包括元数据、血缘、权限、成本、SLA 和质量管理。

解决什么问题：让数据可发现、可追溯、可授权、可控制成本、可承诺交付。没有治理，数仓会变成“表很多、没人知道哪张能用”的数据沼泽。

我的实操边界：项目里维护了字段字典和分层血缘，做过简单的质量报告表设计；没有参与过公司级权限体系和成本治理。

面试题：数据延迟了怎么办？

是什么：延迟是指数据没有在 SLA 时间内到达，可能来自上游采集、计算任务或调度依赖。

解决什么问题：先看分区和任务状态定位卡点，再评估是否用上一日数据兜底，同时通知下游；恢复后按分区补数，确保幂等。核心报表要有降级方案，不能因为一张表延迟让整个看板空白。

我的实操边界：我理解补数和幂等的原理，项目用全量重跑保证一致性；没有在生产调度系统里处理过跨天延迟。

10. 本章验收

• 能说出数据质量五个维度，并各举一个例子
• 能默写主键唯一、外键完整、金额非负、日期合理、枚举合法、波动监控 6 类 SQL
• 能区分业务空值和脏数据，正确解释“未签收所以签收时间为空”
• 能针对时间倒挂设计“隔离、过滤、修复、重放”的完整流程
• 能说出元数据、血缘、权限、成本、SLA 各自解决什么问题
• 能背出示例数据清洗后的对账基线：9 个有效订单、1,509.00 GMV、167.67 AOV