4. 数仓分层建模实战

数仓分层不是把表按前缀随便分堆，而是让每一层承担明确且不重叠的职责：ODS 负责贴源留存，DWD 负责清洗与一致性维度对齐，DWS 负责按主题做轻度聚合，ADS 负责面向报表和接口的最终结果。本节以电商订单场景为例，从业务过程出发，依次确定事实表粒度、维度、分层落点，写出可执行的建表 DDL，并说明数据如何从 ODS 一路流转到 ADS。

一、先定业务过程与粒度，再谈分层

电商订单链路中，最核心的业务过程是“下单”和“支付”。下单产生订单主记录与订单明细，支付产生支付流水。粒度决定事实表一行代表什么：订单明细事实表一行代表“一个订单中的一个商品行”，支付事实表一行代表“一次支付成功流水”。粒度一旦定错，后续所有聚合都会失真，因此必须在建模第一步就写清楚。

围绕这两个业务过程，一致性维度至少包括：用户维度 dim_user、商品维度 dim_product、门店/店铺维度 dim_shop、时间维度 dim_date。维度表统一放在 DWD 层，供各事实表复用，避免每个主题各建一套用户表导致口径分裂。

二、分层职责与表命名约定

本实战采用如下分层与命名：ODS 层保留原始字段并追加 etl_time；DWD 层做去重、空值处理、维度代理键关联；DWS 层按“用户+日期”“商品+日期”等组合做轻度汇总；ADS 层直接输出可被报表消费的宽表。表名统一为 层_主题_粒度，例如 dwd_trade_order_detail_di、dws_trade_user_order_1d、ads_trade_order_summary_1d。

三、DWD 层建表 DDL

订单明细事实表是后续所有汇总的基础，字段设计要覆盖“谁、买了什么、买了多少、花了多少、什么时候、从哪来”。以下 DDL 以 Hive 语法为例，分区字段为 dt，采用按天增量分区。

CREATE TABLE IF NOT EXISTS dwd_trade_order_detail_di (
  order_id        STRING  COMMENT '订单编号',
  user_id         STRING  COMMENT '用户ID',
  sku_id          STRING  COMMENT '商品SKU',
  shop_id         STRING  COMMENT '店铺ID',
  order_status    STRING  COMMENT '订单状态',
  qty             INT     COMMENT '购买数量',
  price           DECIMAL(18,2) COMMENT '商品单价',
  pay_amt         DECIMAL(18,2) COMMENT '实付金额',
  create_time     STRING  COMMENT '下单时间',
  pay_time        STRING  COMMENT '支付时间',
  etl_time        STRING  COMMENT 'ETL处理时间'
)
COMMENT '订单明细事实表'
PARTITIONED BY (dt STRING COMMENT '业务日期')
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

维度表以 dim_user 为例，采用拉链表或每日全量快照均可，本实战用每日全量快照简化：

CREATE TABLE IF NOT EXISTS dim_user (
  user_id     STRING COMMENT '用户ID',
  user_name   STRING COMMENT '用户名',
  user_level  STRING COMMENT '会员等级',
  city        STRING COMMENT '所在城市',
  reg_time    STRING COMMENT '注册时间'
)
COMMENT '用户维度表'
PARTITIONED BY (dt STRING)
STORED AS ORC;

四、从 ODS 到 DWD 的清洗与关联

ODS 到 DWD 的核心动作是去重、过滤无效订单、统一时间格式、关联维度代理键。以下 SQL 将 ODS 订单明细与订单主表关联，过滤掉已取消订单，并写入 DWD 分区：

INSERT OVERWRITE TABLE dwd_trade_order_detail_di PARTITION (dt='2025-06-01')
SELECT
  d.order_id,
  o.user_id,
  d.sku_id,
  o.shop_id,
  o.order_status,
  d.qty,
  d.price,
  d.qty * d.price AS pay_amt,
  o.create_time,
  o.pay_time,
  from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') AS etl_time
FROM ods_order_detail d
JOIN ods_order_info o
  ON d.order_id = o.order_id
WHERE o.dt = '2025-06-01'
  AND o.order_status <> 'CANCELLED'
  AND d.qty > 0;

这里有两个容易出错的点：一是 pay_amt 用数量乘单价计算，若存在优惠券需在 DWD 层引入优惠分摊字段，否则 DWS 汇总的 GMV 会偏高；二是关联前必须确认 ODS 中同一订单不会重复出现，否则明细会翻倍。可以在子查询中先对 ods_order_info 按 order_id 去重。

五、DWS 轻度汇总与 ADS 结果输出

DWS 层按“用户+日期”汇总下单金额与订单数，供用户画像和复购分析使用：

INSERT OVERWRITE TABLE dws_trade_user_order_1d PARTITION (dt='2025-06-01')
SELECT
  user_id,
  COUNT(DISTINCT order_id) AS order_cnt,
  SUM(qty)                 AS sku_cnt,
  SUM(pay_amt)             AS order_amt
FROM dwd_trade_order_detail_di
WHERE dt = '2025-06-01'
GROUP BY user_id;

ADS 层则把 DWS 结果与维度表关联，输出带用户等级、城市、店铺类型的宽表，供 BI 报表直接查询：

INSERT OVERWRITE TABLE ads_order_summary_1d PARTITION (dt='2025-06-01')
SELECT
  u.user_level,
  u.city,
  s.shop_type,
  SUM(w.order_amt) AS gmv,
  SUM(w.order_cnt) AS order_cnt
FROM dws_trade_user_order_1d w
JOIN dim_user u ON w.user_id = u.user_id AND u.dt = '2025-06-01'
JOIN dim_shop s ON w.shop_id = s.shop_id AND s.dt = '2025-06-01'
WHERE w.dt = '2025-06-01'
GROUP BY u.user_level, u.city, s.shop_type;

六、如何验证数据流转正确

流转完成后，至少做三项校验。第一，行数校验：DWD 订单明细行数应等于 ODS 明细中有效订单的行数，若偏大说明关联产生了笛卡尔积。第二，金额校验：ADS 的 GMV 合计应与 DWD 中 SUM(pay_amt) 一致，若不一致通常是维度关联时丢了未匹配记录，可改用 LEFT JOIN 并检查维度表分区是否完整。第三，分区校验：确认 dt='2025-06-01' 在 ODS、DWD、DWS、ADS 四层均存在且非空，避免上游延迟导致下游空跑。