数仓概念与分层

本章目标：理解数据仓库为什么存在，能说清 ODS、DWD、DWS、ADS 四层各自做什么，并把一个电商业务库的 8 张源表映射到四层。 前置知识：数据开发入门、SQL 高级查询 预计用时：90 分钟

数据仓库不是一个软件，而是一套“把原始数据加工成可信指标”的组织方式。学完本章，你要能画出一条数据从业务库到 ADS 表的完整路线。具体到公开电商数据集怎么建仓，放在独立的项目线里做，本章只讲通用方法。

1. 为什么需要数据仓库

1.1 一个每天都会发生的场景

业务同学问：“上个月 GMV 是多少？”你打开订单表和订单明细表直接算，发过去一个数。十分钟后，隔壁同事发来另一个数。

对账后发现差异来自四件事：一个算了取消单、一个没算；一个把运费算进 GMV、一个没算；一个用订单去重、一个按明细行求和；评价表一单多行，JOIN 后把金额放大了。这不是 SQL 写得不对，而是每次现算，就每次都要重新决定一遍口径。

1.2 OLTP 和 OLAP

一句话区分：OLTP 关心“这一笔业务有没有正确完成”，OLAP 关心“很多笔业务合起来说明了什么”。OLTP 为了不重复存储会把数据拆得很规范；数仓反而不怕冗余，多存一份宽表能换掉下游成百上千次 JOIN。

1.3 烟囱式开发的问题

每个需求单独拉一条链路，直接从源表查、自己清洗，叫“烟囱式开发”：

GMV 报表      → orders + order_items → 自己清洗
品类排行      → orders + order_items → 自己清洗
复购率报表    → orders + customers → 自己清洗
履约报表      → orders + reviews     → 自己清洗

它带来五个问题：重复开发、口径不一致、重复扫描浪费性能、出错无法追溯、源表一改全部报表挂掉。

数据仓库的思路是：把清洗和聚合从“每次现算”变成“一次算好、分层存放、按需复用”，交付唯一口径、复用、可追溯和可扩展四件事。

2. 四层架构与职责

2.1 全景图

数据源（业务库 8 张表 / 日志 / 第三方接口）
        │
        ▼
┌──────────────── ODS 贴源层 ────────────────┐
│ 原样保存，保留脏数据，只做落地和分区          │
└───────────────────┬────────────────────────┘
                    ▼
┌──────────────── DWD 明细层 ────────────────┐
│ 清洗、去重、规范化、维度退化，一行一个事实     │
└───────────────────┬────────────────────────┘
                    ▼
┌──────────────── DWS 汇总层 ────────────────┐
│ 按主题和粒度轻度聚合，例如“月 × 品类”        │
└───────────────────┬────────────────────────┘
                    ▼
┌──────────────── ADS 应用层 ────────────────┐
│ 面向报表和看板的成品指标表，开箱即用           │
└────────────────────────────────────────────┘

2.2 四层各自做什么

ODS 的字段名、类型尽量和源系统一致，脏数据也保留。因为 ODS 要能和源系统逐行对账，一旦落地时就改数据，出现差异时无法证明“到底是源系统给的这样，还是我们加工错了”。

DWD 做清洗（排除取消订单）、规范化（时间字符串转 DATE、统一命名）、维度退化（把客户州、品类、卖家州直接冗余进明细）、评价先聚合再 JOIN。它是数仓最核心、面试问得最细的一层。

DWS 按主题聚合，例如日交易、月交易、月 × 品类、客户 RFM、履约服务。日粒度能往上滚成月粒度，月粒度却拆不回日粒度，所以汇总通常从最细的常用粒度开始建。

ADS 直接服务业务场景。字段名最好就是指标名，业务方打开就能用，不需要再算一次。

2.3 逐层依赖规则

正确：ODS → DWD → DWS → ADS
错误：ADS 直接读 ODS
错误：DWD 回头读 ADS
错误：ADS 绕过 DWS，自己从 DWD 重新聚合一套

规则的价值在排错：报表异常时，按“ADS 分组值 → DWS 汇总值 → DWD 明细加总 → ODS 与源系统”逐层对账，每层都能独立判断责任。分层不是为了好看，是为了让每一层都能单独对账。

提示：维度表是 DWD 的公共资产。ADS 需要城市、品类等描述信息时，应让 DWS 宽表提前带上，而不是在 ADS 里重新 JOIN 一堆维表。

3. 数据集市、主题域与维度建模

3.1 数据集市与主题域

数据集市（Data Mart）是数仓面向某个部门或主题的子集，例如营销集市、财务集市、供应链集市。它通常复用同一套 ODS、DWD、DWS，只是在 ADS 层按部门组织表，不是另建一个数仓。

主题域按业务过程划分。一个电商业务库通常可分为交易（orders、order_items）、支付（payments）、客户（customers）、商品（products）、卖家（sellers）、评价（order_reviews）。分主题是为了让 DWS 的聚合边界清晰。

3.2 事实与维度：先建立直觉

判断口诀：能加总的数字放事实表，用来分组和筛选的文字放维度表。 粒度、星型模型、总线矩阵下一章展开：维度建模。

4. 8 张源表如何映射到四层

4.1 源表到四层

下面是一套通用的电商映射。表名和粒度不随数据集变化，换成任何一家电商的源库都能套用。项目线使用的公开数据集就是这张映射表的一个具体实例。

注意：dwd_order 会保留没有明细的有效订单，但 dwd_order_detail 里没有它们。所以“有效订单数”和“有明细的订单数”可能差几行，金额字段也要区分 NULL 和 0。讲订单数时一定要说明是哪一个口径。

4.2 三个口径要分开对账

建仓时至少要对三组数：源表原始行数、过滤取消/不可用后的有效订单数、有效订单对应的明细行数。三者对不上时，先确认是过滤条件写错，还是关联时把行放大了，而不是直接改结果。

具体到公开数据集的 8 张表、真实行数和逐层对账，放在项目 01 概览和项目数据字典里，跟着项目线动手时再看。

5. 动手示例：把四层串起来

5.1 用 12 行示例数据跑通四层

本章用 data/sample/ 下的两份小 CSV 演示，完整数据集留给项目线。在项目根目录打开 DuckDB（CLI 或 Python 都可以），依次执行下面的 SQL。示例数据只有 12 行订单、12 行明细，跑完只要几秒。

5.2 ODS：原样落地

CREATE OR REPLACE TABLE ods_orders AS
SELECT * FROM read_csv_auto('data/sample/orders_demo.csv', header = true);

CREATE OR REPLACE TABLE ods_order_items AS
SELECT * FROM read_csv_auto('data/sample/order_items_demo.csv', header = true);

这一步不做过滤。order_date 仍是字符串，取消单和不可用单也都保留。

注意：Hive 里对应的做法是先建外部表，再把文件放到表目录下，见 Hive 基础。

5.3 DWD：清洗和维度退化

CREATE OR REPLACE TABLE dwd_order_detail AS
SELECT
    o.order_id,
    CAST(o.order_date AS DATE) AS order_date,
    o.status,
    o.city,
    i.product_id,
    COALESCE(i.category, 'unknown') AS category,
    CAST(i.price AS DECIMAL(12, 2)) AS item_amount,
    CAST(i.price + i.freight AS DECIMAL(12, 2)) AS item_amount_with_freight
FROM ods_order_items i
JOIN ods_orders o USING (order_id)
WHERE o.status NOT IN ('canceled', 'unavailable');

执行后 SELECT COUNT(*) FROM dwd_order_detail 应为 9：12 行原始明细里，取消单和不可用单名下的 3 行被排除。

注意：真实业务里评价表常常一单多行，要在 DWD 里先按 order_id 聚合再 JOIN，否则明细行会被放大。示例数据没有评价表，这一步先省略。

5.4 DWS 和 ADS：最后两步

CREATE OR REPLACE TABLE dws_category_1m AS
SELECT
    CAST(date_trunc('month', order_date) AS DATE) AS month,
    category,
    COUNT(DISTINCT order_id) AS order_cnt,
    COUNT(*) AS item_cnt,
    SUM(item_amount_with_freight) AS gmv
FROM dwd_order_detail
GROUP BY 1, 2;

CREATE OR REPLACE TABLE ads_gmv_overview AS
SELECT COUNT(DISTINCT order_id) AS valid_orders,
       ROUND(SUM(item_amount_with_freight), 2) AS gmv,
       ROUND(SUM(item_amount), 2) AS goods_gmv,
       ROUND(SUM(item_amount_with_freight)
             / COUNT(DISTINCT order_id), 2) AS aov
FROM dwd_order_detail;

查询 ads_gmv_overview，结果应为 9 单、GMV 1,509.00、仅商品 1,250.00、客单价 167.67。数字能对上，说明四层链路已经跑通；换成项目的完整数据集后，方法和 SQL 结构完全一样。

6. 全量、增量、分区与幂等

6.1 全量与增量

全量是每天把源表完整拉一遍覆盖目标表，适合商品、卖家这类变化不频繁的小表，逻辑简单且能修正历史错误。增量是只同步新增和变化数据，适合持续增长的订单和明细。

增量必须回答三个问题：用什么字段判断增量、迟到数据怎么办、重复执行会不会翻倍。只有 order_purchase_timestamp 时，只能感知新增订单，感知不到历史订单的状态变化，需要用全量快照对比或 CDC 补齐。

6.2 分区

分区是把表按某个字段拆成多个目录，离线数仓最常用日期分区：

dwd_order_detail/
  dt=2024-01-01/
  dt=2024-01-02/
  dt=2024-01-03/

查询 WHERE dt = '2024-01-01' 时只扫一个目录，这叫分区裁剪。分区字段不参与业务计算，只是存储组织方式。

6.3 幂等

幂等是指同一批数据跑一次和跑十次，结果完全一样：

-- 不幂等：重跑会重复
INSERT INTO dwd_order_detail PARTITION (dt = '2024-01-01')
SELECT ...;

-- 幂等：重跑会覆盖当天分区
INSERT OVERWRITE TABLE dwd_order_detail PARTITION (dt = '2024-01-01')
SELECT ...;

全量重建的构建脚本也是幂等的：每次从源数据重新生成同一份结果。上线后要改成“按天分区 + 覆盖写”，这样补数时不会把金额翻倍。

提示：Hive 的 INSERT OVERWRITE 覆盖整个表或指定分区。Spark 需要设置 spark.sql.sources.partitionOverwriteMode=dynamic 才能只覆盖命中的动态分区。

7. 常见坑

1. 在 ODS 层做业务清洗：对不上源系统，无法追责。
2. ADS 直接读 ODS：脏数据绕过 DWD，报表数字不可信。
3. 明细粒度和订单粒度混算：把 order_items 的金额和 orders 的金额直接相加，金额翻倍。
4. 分区写入用 INSERT INTO：任务重跑一次，数据重复一份。
5. 把正常空值当脏数据：未签收订单没有签收时间属于业务事实；状态已完成却缺关键时间才是异常。
6. 没有指标字典：每个人都在自己的 SQL 里定义 GMV。

8. 在线练习

自测：

• 用电商的 8 张源表，各举一张维度表和一张事实表，并说清粒度。
• 能解释为什么 ODS 原样保留、清洗放在 DWD。
• 能说出“每个城市每月 GMV”应该建在哪一层、粒度是什么。
• 能写出按天分区 + INSERT OVERWRITE 的幂等写入思路。

9. 面试延伸

面试题：为什么要对数据仓库分层？

是什么：分层是把数据加工拆成 ODS、DWD、DWS、ADS 四个职责明确的阶段，每层只依赖下一层。

解决什么问题：避免烟囱式开发带来的重复计算、口径不一致、无法追溯和性能浪费。分层后，报表异常可以逐层对账，新增需求可以复用已有汇总。

我的实操边界：我在本地用 DuckDB 完整建过四层，数据量约 55 万行；没有在生产集群上维护过分层数仓，但清楚上线后需要补调度、增量、监控和权限。

面试题：数据仓库和 OLTP 数据库有什么区别？

是什么：OLTP 面向事务处理，OLAP 面向分析查询。数仓属于 OLAP 场景。

解决什么问题：OLTP 强调单行读写的低延迟和一致性，通常规范化、行存；数仓强调大批量扫描聚合，常用列存、分区、冗余宽表。两者目标不同，不能互相替代。

我的实操边界：项目里用 DuckDB 模拟数仓，理解 MySQL 类 OLTP 库的基本使用；没有做过大规模 OLTP 库的调优。

面试题：全量同步、增量同步和幂等怎么配合？

是什么：全量是每次拉完整数据，增量是只拉变化数据，幂等是重复执行结果不变。

解决什么问题：小表用全量简单可靠；大表用增量降低成本；无论哪种方式，写入都必须幂等，否则补数或重跑会导致数据重复。

我的实操边界：项目脚本是全量重建，天然幂等。我理解生产上常用“按天分区 + INSERT OVERWRITE”实现增量幂等，但没有在调度系统里实际配置过补数流程。

10. 本章验收

• 能用自己的话解释 OLTP 和 OLAP 的区别，并各举一个例子
• 能画出 ODS、DWD、DWS、ADS 四层图，说清每层职责
• 能说出为什么每层只能依赖下一层，以及分层对账的排查顺序
• 能独立写出 8 张源表到四层的映射表，并解释原始订单、有效订单、有效明细三个口径的差异
• 能写出一条幂等的 Hive 分区覆盖写入 SQL
• 能回答“为什么 ODS 保留脏数据”“为什么 ADS 不能直接读 ODS”