项目设计与架构

本章目标：先把项目想清楚：数据集、分层、粒度、核心口径和五章构建路线。数据和实验室库在 00 章已经准备好。 前置知识：项目 00 章：从零开始；建议先读数据仓库分层、维度建模和SQL 基础，但项目正文会把用到的写法讲清楚。 预计用时：本章 50 分钟；02–05 章每章 60–90 分钟。

0. 本章你要产出什么

读完并动手后，你应该有：

1. 一张项目设计卡：写清数据、分层、粒度、核心口径和最终要得到的数字。
2. 一个已经验证过的实验室库 project/warehouse_lab.duckdb：00 章已创建，后面四章都在这里建表，不碰参考答案。
3. 一张构建计划：知道每章结束时该得到哪些表、哪些校验数字。

完成标志：合上教程，能画出 CSV → ODS → DWD → DWS → ADS，并说出 98,207 / 112,101 / 98,199 三个行数分别是什么。

0.1 两条通道，别混着用

这个项目有两种跑法：

实验室库是你自己从空库开始建的，可以随便改、随便删；参考库由构建脚本一次生成，只读打开。先自己写，写不出来或数字对不上，再展开每章的「参考答案」或直接看 project/sql/*.sql。

0.2 设计卡模板

先不要看后面的表结构，自己填一遍，再对照本文修正：

项目名：Olist 电商数仓
数据：8 张 CSV，550,759 行，2016-09-04 ~ 2018-10-17
分析窗口：2017-01-01 ~ 2018-08-31
分层：ODS(8) → DWD(2) → DWS(6) → ADS(8)
核心事实表粒度：dwd_order_detail = 订单 × 商品行；dwd_order = 有效订单
核心口径：GMV / AOV / 复购率 / 迟到率（先写自己的理解，再对照第 5 节）
必须记住的数字：98,207 / 112,101 / 98,199
交付物：README + 质量报告 + 3 分钟讲述

1. 这个项目要证明什么

面试官看项目，通常只关心四件事：

1. 数据是不是真的，能不能复现。
2. 分层和建模有没有业务含义，还是为了分层而分层。
3. 指标口径是否稳定，边界情况怎么处理。
4. 你能不能讲清楚取舍和不足。

所以本项目不追求“表越多越好”，而是用 Olist 巴西电商公开数据集 做一套可本地复现的四层数仓，重点交付：

• 8 张源表的 ODS 贴源层；
• 2 张 DWD 宽表（明细粒度 + 订单粒度）；
• 6 张 DWS 主题汇总表；
• 8 张 ADS 结果表；
• 一份可自动对账的质量检查；
• 一套能在 3 分钟内讲清的项目说明。

2. 为什么选 Olist

Olist 是真实的巴西电商平台数据，来自 Kaggle，许可为 CC BY-NC-SA 4.0，仅用于个人学习。本项目只使用其中 8 个 CSV，不包含 61 MB 的 geolocation 表。

本站已经把 8 个 CSV 打包好，不用注册 Kaggle，直接在第 8.2 节下载。

3. 数据集概览

合计 550,759 行。下单时间范围 2016-09-04 ~ 2018-10-17，共 634 个下单日。

3.1 分析窗口

• 全量有效订单：用于总览、复购、履约等稳定指标；
• 月度趋势与品类/卖家分析：只取 2017-01-01 ~ 2018-08-31，避开 2016 年的冷启动和 2018-09 之后的数据尾部；
• 窗口内 GMV 为 15,688,396.64，全量有效 GMV 为 15,740,216.93。

这两个数不一样不是错误，是范围不同。报告里必须写清楚用的是哪个。

4. 业务问题

项目围绕六个问题展开：

1. 平台整体卖了多少、客单价多少、复购怎么样？
2. 哪些品类和卖家贡献了主要 GMV，集中度如何？
3. 履约是否达标，迟到对评分有没有影响？
4. 客户能不能分层，高价值客户占比多少？
5. 数据里有哪些脏数据，哪些必须清洗，哪些只能告警？
6. 这套口径能不能被自动校验，换一批数据会不会算错？

5. 指标字典

所有指标以 data/metrics.json 为唯一基线，由 scripts/check_data.py 生成。

5.1 四个最容易讲错的口径

AOV 的分母：不是 99,441 原始订单，也不是 98,207 有效订单，而是 98,199 个有明细的有效订单。有 8 个有效订单没有任何明细，无法计算客单价。

GMV 含不含运费：本项目默认含运费，写作 price + freight_value。如果面试官问“商品交易总额为什么含运费”，回答：Olist 的支付流水包含运费，为了让 GMV 与支付对账，主口径含运费；同时单独保留仅商品 GMV 作为辅助口径。

复购按谁算：Olist 的 customer_id 是“订单收货地址”维度，同一个人换地址会变 ID；复购必须用 customer_unique_id。用错会把复购率算低。

迟到怎么定义：只看 order_delivered_customer_date > order_estimated_delivery_date，且两个时间都不为空。取消单、未签收单不进入分母。

6. 四层架构

data/raw/olist/*.csv
        │  下载 zip 解压（或 fetch_olist.py）
        ▼
ODS  ods_orders / ods_order_items / ods_payments / ods_customers
     ods_products / ods_sellers / ods_order_reviews / ods_category_translation
        │  清洗、关联、派生
        ▼
DWD  dwd_order_detail（一行一件商品）
     dwd_order（一行一个有效订单）
        │  按主题聚合
        ▼
DWS  dws_trade_1d / dws_trade_1m / dws_category_1m
     dws_seller_1m / dws_customer_rfm / dws_service_1m
        │  面向场景
        ▼
ADS  ads_gmv_overview / ads_month_trend / ads_category_rank / ads_seller_rank
     ads_customer_rfm / ads_repurchase / ads_delivery_sla / ads_review_lateness

每一层的职责：

7. 数据模型

核心事实表是 dwd_order_detail，粒度是 订单 × 商品行；dwd_order 是订单粒度，方便算订单数、AOV、履约和复购。

dim_customer(customer_id) ─┐
dim_product(product_id) ───┼── fact_order_item(order_id, item_seq)
dim_seller(seller_id) ─────┤        │
dim_date(order_date) ──────┘        │
                                    ▼
                           dwd_order(order_id)

维度退化的字段：customer_unique_id、customer_city、customer_state、category、seller_state、review_score 都直接写进 DWD，减少下游 JOIN。

7.1 为什么 DWD 不做 payments / reviews 的明细 JOIN

payments 一单可能多行，order_reviews 一单也可能多条。如果直接 JOIN 到明细行，会发生 fan-out（行数膨胀），GMV 和评价均分都会被重复计算。

正确做法是先聚合到订单粒度，再 JOIN：

WITH review AS (
    SELECT order_id, AVG(CAST(review_score AS INTEGER)) AS review_score
    FROM ods_order_reviews
    GROUP BY order_id
)
SELECT i.order_id, i.price, r.review_score
FROM ods_order_items i
LEFT JOIN review r USING (order_id)
LIMIT 5;

支付表同理。项目里支付只用于质量对账，不进入 GMV 主口径。

8. 目录约定

数据下载、工具检查、实验室库创建和第一次 Git 提交都在00 章完成。本章只说明后面的文件放在哪，不再重复安装步骤。

8.1 目录

data/raw/olist/             8 个 CSV（解压得到，不提交）
data/metrics.json           标准答案
scripts/make_dataset_bundle.py  把 8 个 CSV 打成站点下载包
scripts/fetch_olist.py      备用下载通道 + 行数/sha256 校验
scripts/check_data.py       10 类体检，生成 metrics.json
project/run_sql.py          实验室 SQL 运行器（默认写 lab 库）
project/lab/01_ods.sql      第 02 章的 ODS 建表答案
project/sql/*.sql           DWD/DWS/ADS 参考答案
project/build_warehouse.py  一键构建参考库 + 质量报告
project/warehouse_lab.duckdb  自己建出来的实验室库（运行后生成）
project/warehouse.duckdb      参考答案库（build 后生成）
project/ads/*.csv           ADS 导出结果（build 后生成）

8.2 运行器怎么用

实验室库在 00 章已建好。后面统一用三种方式执行 SQL：

python project/run_sql.py                              # 交互模式，输入 SQL，以 ; 结束
python project/run_sql.py project/lab/01_ods.sql       # 执行 SQL 文件
python project/run_sql.py -c "SELECT COUNT(*) FROM ods_orders"

想随时对答案，可以单独建参考库（不影响实验室）：

python project/build_warehouse.py
python project/run_sql.py --db project/warehouse.duckdb -c "SELECT COUNT(*) FROM dwd_order_detail"

运行器打开 project/warehouse.duckdb 时会自动只读，防止把参考答案改掉。

提示：如果你只想练 SQL、不打算自己建表，直接跑 build_warehouse.py 用参考库即可；但只要目标是“会做项目”，就按 02–05 章在实验室里自己写。

9. 项目局限

这些限制要主动写进 README，面试时也不要回避：

1. 没有数量字段：Olist 明细一行就是一件商品，项目把 quantity 固定为 1。真实零售数仓需要从订单中心拿数量。
2. 没有用户行为日志：没有浏览、加购、登录，所以不做 DAU、转化漏斗、连续登录；只做“日活买家”和“连续下单”。
3. 客户等级是派生字段：源数据没有会员等级，DWD 里用累计消费分档，只用于展示，不是平台真实等级。
4. 品类是英文翻译：葡萄牙语品类通过翻译表映射，缺失的归为 unknown，目前有 610 个商品缺品类。
5. 数据止于 2018-10：所有“截止日”都是数据最大下单时间，不是今天。
6. 支付与明细不完全一致：441 单差异超过 0.01，总差额 -1,851.52；另有 1,234 单取消/不可用订单仍有支付。这是真实数据特征，质量章单独解释。

10. 学习路线

这个项目按 “先建 → 再对 → 最后讲” 推进。每章开头先给产出和校验数字，正文给步骤，答案折叠在 project/sql/*.sql。不要一上来就跑构建脚本，那样只能“看懂”，不能“会做”。

建议节奏：

1. 今天：确认 00 章已完成，然后读完本章，填设计卡。
2. 第 1 天：完成 02 章。每写一步就执行一次 COUNT(*)，数字不对先别往下走。
3. 第 2 天：完成 03 章。先写 DWS，再写 ADS；ADS 的结果要和第 3 章的校验表对上。
4. 第 3 天：完成 04 章。把检查结果写成 project/quality_report_lab.md。
5. 第 4 天：完成 05 章。最后跑一次 python project/build_warehouse.py，把参考库、参考报告和自己的结果并排对一遍。

卡住时：先看报错和本章校验数字，再展开「参考答案」，最后才直接运行 project/sql/*.sql。运行答案之前，先猜一下结果行数，猜错的地方就是没理解的地方。

11. 本章验收

• 能说出 8 张源表各是什么粒度、怎么关联。
• 能背出 98,207 / 112,101 / 98,199 三个行数，并解释为什么不同。
• 能解释 AOV 为什么除以 98,199。
• 能解释 GMV 含运费和不含运费的区别。
• 能说出至少三个项目局限，且不把它们说成优点。
• 能画出四层架构，并说明每层为什么存在。
• 00 章已完成：check_data.py 显示 8 张表 / 550,759 行，实验室库能用 run_sql.py 执行查询。
• 写完了设计卡，知道每章结束要交什么。

12. 面试延伸

面试题：为什么用 DuckDB，而不是 Hive / Spark？

是什么：DuckDB 是嵌入式分析型数据库，单机就能跑列存和 SQL；Hive/Spark 是分布式方案。

解决什么问题：项目数据只有 55 万行，DuckDB 零部署、启动快，适合本地复现和面试演示。生产上数据到 TB 级才需要 Hive/Spark，项目里另外给了 Hive 风格 DDL，说明迁移路径。

我的实操边界：我在本地用 DuckDB 跑通了 ODS-DWD-DWS-ADS 和对账；Hive 脚本只做了语法级演示，没有在真实集群验证。

面试题：这个项目的难点是什么？

是什么：不是写 SQL，而是口径和边界。

解决什么问题：比如复购必须用 customer_unique_id，AOV 分母必须排除无明细订单，评价和支付必须先聚合再 JOIN，否则数字会被 fan-out 放大。

我的实操边界：我用 metrics.json 固定了基线，并把行数、GMV、AOV、复购率、迟到率写进构建脚本自动对账；没有做过生产级调度和权限管理。