数据质量与对账

本章目标：把“我觉得数据没问题”变成“有检查 SQL、有基准值、有告警分级、有处理结论”，并写出一份自己的质量报告。 前置知识：聚合查询、LEFT JOIN、HAVING、主键与外键；已完成 02 和 03 预计用时：70 分钟。

0. 本章你要产出什么

1. 7 组检查 SQL：主键、外键、行数、空值、状态、金额、时间/业务规则。
2. 一份 project/quality_report_lab.md：写清范围、行数对账、指标对账、异常清单、分级和结论。
3. 一张分级表：哪些问题必须阻断构建，哪些只记录告警，哪些只是信息。
4. 一句能讲清的话：为什么“全部支付 - 有效明细”会多出 268,655.19。

完成标志：下面 6 个数字全部能对上，并且能说出每个数字属于错误、告警还是信息。

1. 质量标准

一个数据结果可信，至少要满足四件事：

1. 可复现：同一批数据、同一套 SQL，每次跑出的数字一样。
2. 可对账：和源表、支付流水、业务规则能对上。
3. 可解释：异常不是“删掉就完事”，要能说清为什么。
4. 可监控：下次数据变化时，能自动发现，而不是等人肉看报表。

项目用 scripts/check_data.py 做 10 类体检，并把结果写入 data/metrics.json；project/build_warehouse.py 在构建末尾做指标对账。两者共用同一份基线，避免“文档写一套、脚本跑一套”。

本章的顺序是：先做硬检查 → 再做空值和状态 → 再做金额对账 → 最后写报告。硬检查不通过，后面的数字都没有意义。

2. 动手一：硬检查（错误级）

2.1 主键唯一

SELECT
    (SELECT COUNT(*) FROM (
        SELECT order_id FROM ods_orders GROUP BY 1 HAVING COUNT(*) > 1
    )) AS duplicate_orders,
    (SELECT COUNT(*) FROM (
        SELECT order_id, order_item_id FROM ods_order_items
        GROUP BY 1, 2 HAVING COUNT(*) > 1
    )) AS duplicate_items,
    (SELECT COUNT(*) FROM (
        SELECT order_id, payment_sequential FROM ods_payments
        GROUP BY 1, 2 HAVING COUNT(*) > 1
    )) AS duplicate_payments;

结果 0 / 0 / 0。三张核心表主键都干净。

评价表例外：789 个 review_id 重复，多出 814 行；一单多条评价的订单有 547 个。评价汇总前必须先按 order_id 聚合，避免 JOIN 后行数膨胀。评价重复属于告警，不是错误，因为同一条评价可能对应不同订单。

2.2 外键完整

SELECT
    (SELECT COUNT(*) FROM ods_order_items i
     LEFT JOIN ods_products p USING (product_id)
     WHERE p.product_id IS NULL) AS items_without_product,
    (SELECT COUNT(*) FROM ods_order_items i
     LEFT JOIN ods_sellers s USING (seller_id)
     WHERE s.seller_id IS NULL) AS items_without_seller,
    (SELECT COUNT(*) FROM ods_orders o
     LEFT JOIN ods_customers c USING (customer_id)
     WHERE c.customer_id IS NULL) AS orders_without_customer,
    (SELECT COUNT(*) FROM ods_order_reviews r
     LEFT JOIN ods_orders o USING (order_id)
     WHERE o.order_id IS NULL) AS reviews_without_order,
    (SELECT COUNT(*) FROM ods_payments p
     LEFT JOIN ods_orders o USING (order_id)
     WHERE o.order_id IS NULL) AS payments_without_order;

结果全部 0。明细→商品、明细→卖家、订单→客户、评价→订单、支付→订单，全部没有孤儿。Olist 在维度完整性上比较好。

2.3 行数守恒

SELECT
    (SELECT COUNT(*) FROM ods_orders)                        AS ods_orders,
    (SELECT COUNT(*) FROM dwd_order)                         AS dwd_order,
    (SELECT COUNT(*) FROM ods_order_items)                   AS ods_items,
    (SELECT COUNT(*) FROM dwd_order_detail)                  AS dwd_items,
    (SELECT COUNT(DISTINCT order_id) FROM dwd_order_detail)  AS dwd_item_orders;

前三列必须和 ODS 完全一致；dwd_order 少 1,234 是排除了取消/不可用；dwd_items 少 549 是不属于有效订单的明细。如果 dwd_items 反而比 ODS 多，几乎一定是 JOIN 写成了 fan-out。

3. 动手二：空值与状态（信息/告警）

3.1 空值

SELECT
    COUNT(*) FILTER (WHERE order_approved_at IS NULL)              AS approved_null,
    COUNT(*) FILTER (WHERE order_delivered_carrier_date IS NULL)   AS carrier_null,
    COUNT(*) FILTER (WHERE order_delivered_customer_date IS NULL)  AS delivered_null,
    COUNT(*) FILTER (
        WHERE order_status = 'delivered'
          AND order_delivered_customer_date IS NULL
    ) AS delivered_status_missing_date,
    COUNT(*) FILTER (
        WHERE order_status NOT IN ('canceled', 'unavailable')
          AND order_delivered_customer_date IS NULL
    ) AS valid_missing_date
FROM ods_orders;

前三个大部分是业务正常：未发货、未签收。真正异常的是 8 条 delivered 却没有签收时间，需要单独标记。1,737 条有效订单缺签收时间不是异常，因为 shipped、processing 等状态本来就没签收；它们只是不进履约分母。

3.2 状态分布

SELECT order_status, COUNT(*) AS order_cnt
FROM ods_orders
GROUP BY order_status
ORDER BY order_cnt DESC;

canceled + unavailable = 1,234，项目统一视为无效订单。shipped、invoiced、processing 等仍在履约中，保留在有效订单里，但不进入履约时长和迟到率分母。

4. 动手三：金额对账

4.1 先对齐口径，再谈差异

先只看“有效订单中同时有明细和支付”的订单：

WITH item AS (
    SELECT oi.order_id, SUM(oi.price + oi.freight_value) AS item_total
    FROM ods_order_items oi
    JOIN ods_orders o USING (order_id)
    WHERE o.order_status NOT IN ('canceled', 'unavailable')
    GROUP BY oi.order_id
), pay AS (
    SELECT order_id, SUM(payment_value) AS pay_total
    FROM ods_payments
    GROUP BY order_id
)
SELECT COUNT(*) AS matched_orders,
       COUNT(*) FILTER (WHERE ABS(pay_total - item_total) > 0.01) AS mismatch_orders,
       ROUND(SUM(item_total), 2) AS item_total,
       ROUND(SUM(pay_total), 2)  AS pay_total,
       ROUND(SUM(pay_total - item_total), 2) AS diff
FROM item JOIN pay USING (order_id);

这个差额很小，来源可能是优惠券、分期、手续费或多支付方式。项目不调整订单金额，只在报告里披露。

4.2 为什么“全部支付 - 有效明细”会多出 268,655.19

很多人第一次对账会这样写：

SELECT
    (SELECT SUM(payment_value) FROM ods_payments)
    - (SELECT SUM(price + freight_value)
       FROM ods_order_items i
       JOIN ods_orders o USING (order_id)
       WHERE o.order_status NOT IN ('canceled', 'unavailable'));

结果是 +268,655.19，然后误以为支付表多算了 26 万。拆开看：

SELECT
    (SELECT ROUND(SUM(payment_value), 2) FROM ods_payments) AS all_pay,
    (SELECT ROUND(SUM(price + freight_value), 2)
     FROM ods_order_items i JOIN ods_orders o USING (order_id)
     WHERE o.order_status NOT IN ('canceled', 'unavailable')) AS valid_item,
    (SELECT ROUND(SUM(p.payment_value), 2)
     FROM ods_payments p JOIN ods_orders o USING (order_id)
     WHERE o.order_status IN ('canceled', 'unavailable')) AS canceled_pay,
    (SELECT ROUND(SUM(i.price + i.freight_value), 2)
     FROM ods_order_items i JOIN ods_orders o USING (order_id)
     WHERE o.order_status IN ('canceled', 'unavailable')) AS canceled_item;

也就是说，差额主要来自 范围不一致，不是数据错误。正确做法是：

1. 同口径对账：只比同时有明细和支付的订单，得到 -1,851.52；
2. 单独统计取消/不可用订单的支付，作为告警；
3. 在报告里写清“支付含取消单，GMV 只含有效单”，不要混在一起。

这是本项目质量章最值得讲的一个点：对账先对齐口径，再谈差异。

4.3 取消/不可用订单仍有支付

SELECT COUNT(DISTINCT p.order_id) AS canceled_with_payment
FROM ods_payments p
JOIN ods_orders o USING (order_id)
WHERE o.order_status IN ('canceled', 'unavailable');

结果：1,234 单。

这不能简单判成“错误”。取消订单可能已经支付，随后退款；数据里没有退款表，无法判断是否已退。所以项目把它列为 告警，不计入有效 GMV，也不删除支付记录。

5. 动手四：时间倒挂与业务规则

5.1 时间倒挂

SELECT
    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
FROM ods_orders;

结果：发货早于下单 166 条，签收早于发货 23 条。数量很小，但说明时间戳存在采集或时区问题。项目保留这些订单，履约分析时只要求“签收时间和预计时间都不为空”，不额外剔除倒挂行。

5.2 业务规则

SELECT
    (SELECT COUNT(*) FROM ods_orders o
     WHERE NOT EXISTS (
         SELECT 1 FROM ods_order_items i WHERE i.order_id = o.order_id
     )) AS all_orders_without_items,
    (SELECT COUNT(*) FROM ods_orders o
     WHERE o.order_status NOT IN ('canceled', 'unavailable')
       AND NOT EXISTS (
           SELECT 1 FROM ods_order_items i WHERE i.order_id = o.order_id
       )) AS valid_orders_without_items,
    (SELECT COUNT(*) FROM ods_products
     WHERE product_category_name IS NULL) AS products_missing_category,
    (SELECT COUNT(*) FROM ods_products
     WHERE product_weight_g IS NULL OR product_length_cm IS NULL
        OR product_height_cm IS NULL OR product_width_cm IS NULL) AS products_missing_size,
    (SELECT COUNT(*) FROM ods_order_reviews r
     JOIN ods_orders o USING (order_id)
     WHERE r.review_creation_date < o.order_delivered_customer_date) AS reviews_before_delivery;

6. 动手五：给问题分级

项目把错误做成构建失败，把告警写进质量报告，把信息留在体检输出里。

7. 动手六：写质量报告

新建 project/quality_report_lab.md，按下面六节写。数字用你实验室库的结果，不要直接抄参考报告。

# Olist 数仓质量报告（实验室版）

## 1. 检查范围
- 数据：Olist 8 张表，550,759 行
- 窗口：2016-09-04 ~ 2018-10-17，分析窗口 2017-01 ~ 2018-08
- 构建：project/run_sql.py + 实验室库

## 2. 行数对账

| 表 | 期望 | 实际 | 结论 |
|---|---:|---:|---|
| ods_orders | 99,441 | 99,441 | 通过 |
| dwd_order_detail | 112,101 | 112,101 | 通过 |
| dwd_order | 98,207 | 98,207 | 通过 |

## 3. 指标对账
GMV 15,740,216.93 / AOV 160.29 / 复购率 3.04% / 迟到率 8.11%

## 4. 异常清单

| 问题 | 数量 | 级别 | 处理 |
|---|---:|---|---|
| 441 单支付与明细差 >0.01 | 441 | 告警 | 披露，总差额 -1,851.52 |
| 取消/不可用仍有支付 | 1,234 | 告警 | 不计入 GMV |
| delivered 无签收时间 | 8 | 告警 | 不进履约分母 |
| 商品缺品类 | 610 | 信息 | 归为 unknown |
| 评价早于签收 | 8,320 | 信息 | 保留并披露 |

## 5. 口径说明
- GMV = 有效订单的 price + freight_value
- AOV = GMV / 98,199 个有明细的有效订单
- 复购按 customer_unique_id
- 迟到 = 签收时间 > 预计送达时间

## 6. 结论
- 核心表主键、外键、行数全部通过；
- 金额差异来自口径和真实业务，不是 ETL 错误；
- 8 条 delivered 无签收时间需要单独标记；
- 数据局限：无数量字段、无行为日志、止于 2018-10。

写完自查：

• 每个“告警”都有处理方式，不是只列数字；
• 行数、GMV、AOV、复购率、迟到率五项都有基准值；
• 没有把“取消单有支付”写成“数据错误”；
• 写清了分析窗口和全量口径的区别。

8. 对答案：和自动构建报告比

运行参考构建：

python project/build_warehouse.py

它会生成 project/quality_report.md，包含行数漏斗、已知质量问题、40+ 项构建对账。把你的 quality_report_lab.md 和它逐节对比：

1. 行数漏斗是否一致；
2. 6 个关键数字是否一致；
3. 告警项的级别和处理是否一致；
4. 有没有你漏掉的检查。

也可以在同一条查询里直接对比实验室和参考库：

ATTACH 'project/warehouse.duckdb' AS ref (READ_ONLY);

SELECT
    (SELECT COUNT(*) FROM dwd_order_detail)     AS lab_detail,
    (SELECT COUNT(*) FROM ref.dwd_order_detail) AS ref_detail,
    (SELECT ROUND(SUM(item_amount_with_freight), 2) FROM dwd_order_detail) AS lab_gmv,
    (SELECT ROUND(SUM(item_amount_with_freight), 2) FROM ref.dwd_order_detail) AS ref_gmv;

两个数字完全一致，说明你的清洗和聚合口径和参考答案对齐了。不一致时，先比较 dwd_order_detail 的行数，再比较 GMV，最后才看 DWS/ADS。

9. 隔离策略

不建议在 ODS 直接删异常行。更稳的做法：

1. ODS 原样保留；
2. 真实生产会在 DWD 增加标记，例如 is_valid_order、has_delivery_date；
3. 质量结果单独落一张 dq_issue 表，记录表名、主键、问题类型、处理建议；
4. 下游按标记过滤，而不是改源数据。

本项目数据量小，没有建标记列：DWD 直接排除取消/不可用订单，时间空值保留，履约指标只取有签收时间的订单。真实生产会保留问题明细，供业务复核。

10. 本章验收

• 能写出主键、外键、空值、金额对账四类 SQL。
• 能解释 8 条 delivered 无签收时间和 1,737 条有效订单缺签收时间的区别。
• 能解释为什么“全部支付 - 有效明细”会多出 268,655.19。
• 能说出 441 单支付差异的可能原因。
• 能区分错误、告警、信息三级，并给每个异常定级。
• 能不删除原始数据的前提下设计隔离方案。
• 已写出 project/quality_report_lab.md，并与 project/quality_report.md 完成对比。

11. 面试延伸

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

是什么：质量保障不是一条 SQL，而是“检查 + 基线 + 分级 + 处理”的闭环。

解决什么问题：主键、空值、外键、状态、金额、时间、业务规则、波动八类检查能覆盖大部分问题；基线让每次构建可对比；分级避免把业务正常现象误判为错误。

我的实操边界：项目把 10 类体检写进 check_data.py，构建时做 40+ 项自动对账（行数、GMV、AOV、复购率、迟到率、评分、日活买家、RFM 客户数等），失败会退出并丢弃临时结果；没有接入生产级调度告警和数据血缘。

面试题：支付和明细对不上，怎么排查？

是什么：先对齐口径，再拆差异。

解决什么问题：先只比“同时有明细和支付”的订单，得到 -1,851.52；再单独看取消/不可用订单的支付，发现 269,735.11；最后按订单拆，找差异最大的订单和支付方式。

我的实操边界：项目里 441 单差异超过 0.01，我没有逐单联系业务确认原因；报告里如实写了“可能来自优惠、分期、手续费，数据中没有退款/优惠明细，无法进一步归因”。

面试题：异常数据应该删除吗？

是什么：删除是最危险的处理方式。

解决什么问题：删除会让行数和金额对不上源系统，也无法回溯。正确做法是保留原始数据、增加标记、单独隔离，下游按需过滤。

我的实操边界：项目只排除 canceled/unavailable 两类无效订单，其他异常全部保留并披露；没有做物理删除。