数仓DWD层加购事务事实表建模详解：从建表到踩坑

做数仓的朋友应该都有感受：一到交易域，加购表往往是DWD层里“看着最简单、写起来最纠结”的一张表。说它简单，是因为购物车加购这个动作，在业务库就是一行记录，字段不复杂；说它纠结，是因为它横跨事务事实表和周期快照表两种建模思路，还牵扯维度退化、度量固化、分区策略、口径定义，一个不小心，下游漏斗分析就会得出两个互相打架的加购人数。

这是我数仓搭建学习笔记的第28篇，跟着项目完整走一遍交易域DWD层加购事务事实表的建表语句和逐段分析。会讲清楚每个字段为什么存在、每个存储参数为什么这么选，也会把ODS到DWD的数据加工逻辑和实际环境中容易踩的坑一起复盘。适合正在学数仓分层建模、准备自己动手搭离线数仓的同学参考。

1. DWD层到底在解决什么问题——加购数据这样建模才合理

1.1 数仓分层中DWD的定位

数仓经典分层是ODS、DWD、DWS、ADS，每一层解决不同的问题。ODS层原样接入业务系统和日志的数据，基本不做处理，保留最原始的状态。DWS层面向主题做汇总，把明细聚合成宽表。中间的DWD层很多人理解为“做一遍数据清洗”，这个理解太浅了。

DWD真正做的事有三件：第一是清洗，把空值、异常值、格式杂乱的数据处理掉；第二是维度退化，把ODS里孤零零的ID变成可以直接分析、筛选的维度属性；第三是规范化业务过程，让每一行数据的含义在同一张表内可以准确解释，而不是让分析师去ODS的某个接口文档里猜某个字段代表什么。

加购数据恰恰是这三件事的典型场景。ODS层的购物车表是业务库按MySQL规范设计的，主键、外键、业务状态字段齐全，但它面向的是业务系统，不是分析。业务系统关心的是“当前购物车有哪些商品”，数仓关心的是“用户什么时候把什么商品加入了购物车”。关注点不同，建模方式就不同。

1.2 为什么加购要建模成事务事实表

事务事实表对应的是一个业务过程中的每一个事件，一行数据就是一个事件发生时的快照。周期快照表则是定期记录某个时刻的累计状态。加购这个动作本身就是一个事件，用户每次点击“加入购物车”，就应该产生一行事实记录。

有一个常见的误区：ODS层已经有购物车表了，为什么不能直接拿它分析？因为在业务库里，购物车表是可变状态的。用户加了商品A，再点一次加购，数量从1变成2，这一行的主键不会变，之前的记录被覆盖。如果直接用这张表分析加购行为，你只能看到购物车的最终状态，看不到“用户是几点几分加购的”“加购了几次”“当时的价格是多少”。

所以DWD层要把业务库的“状态表”转成“事件流水表”，用事务事实表来建模。事实表的特点是只追加、不更新，每一行对应一次独立的加购事件。这样下游无论是做漏斗分析、行为序列分析，还是计算加购转化率，都有据可依。

事务事实表和周期快照表的取舍，我整理了一个简单的对照：

1.3 度量为什么要固化在事件里

事务事实表里最核心的是度量字段。加购表涉及的度量有三个：加购数量、加购时的单价、加购总金额。

为什么单价和总金额必须在建表时固化，而不是分析时再临时关联商品表去算？因为商品价格是会变的。用户周二晚上加购了一个商品，价格是99元，到了周五商家改价成89元，如果分析时再去关联商品维表，拿到的是最新价格，算出来的加购金额和用户当时看到的价格完全不是一回事。任何事务事实表，度量都必须反映事件发生时的事实，这是事务表建模的铁律。

因此在加工DWD时，加购表会把当时的sku单价冗余下来，并直接算出数量乘单价的金额。这样后续做用户加购金额分布、价格敏感度分析时，拿到的都是准确的历史事实。这也是DWD层做“规范化度量”的意义所在。

2. 建表语句逐段拆解——每个字段和参数都不是随便写的

2.1 完整DDL先摆上来

先给出建表语句。这个版本是离线数仓常用的标准写法，基于Hive语法。项目里表名用了dwd_trade_cart_add_inc，含义是交易域加购事务事实表。

CREATE EXTERNAL TABLE IF NOT EXISTS dwd_trade_cart_add_inc ( id STRING COMMENT '加购记录主键id', user_id STRING COMMENT '用户id', sku_id STRING COMMENT '商品sku_id', spu_id STRING COMMENT '商品spu_id', category1_id STRING COMMENT '一级品类id', category2_id STRING COMMENT '二级品类id', category3_id STRING COMMENT '三级品类id', tm_id STRING COMMENT '品牌id', sku_num DECIMAL(16, 2) COMMENT '加购商品数量', sku_price DECIMAL(16, 2) COMMENT '加购时商品单价', split_total_amount DECIMAL(16, 2) COMMENT '加购总金额', create_time STRING COMMENT '加购时间', operate_time STRING COMMENT '操作时间', province_id STRING COMMENT '省份id', is_checked STRING COMMENT '是否勾选', source_type STRING COMMENT '来源类型', source_id STRING COMMENT '来源编号' ) COMMENT '交易域加购事务事实表' PARTITIONED BY (dt STRING) STORED AS ORC LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc' TBLPROPERTIES ('orc.compress' = 'snappy');

建表语句看着不长，但每一行都有值得展开的地方。下面拆开讲。

2.2 字段分组解读：维度、度量、时间与业务状态

这张表的字段可以分成四组，理解了分组逻辑，后面遇到类似的DWD表都能举一反三。

第一组是事件标识类字段，包括id。它来自ODS层购物车表的主键id。在事务事实表中，主键保证每一行有唯一标识，后续做去重、关联、问题排查都靠它。

第二组是维度字段，包括user_id、sku_id、spu_id、category1_id、category2_id、category3_id、tm_id、province_id、source_type、source_id。其中user_id和sku_id是业务过程自然带出的，品类、品牌、SPU则是在DWD层通过关联商品维表退化的属性。

这里有一个重要的设计思路：在ODS层，购物车表通常只存sku_id，你要知道用户加购的商品属于哪个品类、哪个品牌，必须去关联商品维表。如果让每个下游分析师都去做这个关联，第一是麻烦，第二是容易关联错，第三是维表数据每天变化，不同人拿到的维度属性可能不一致。所以DWD层一次性把这些维度退化进来，把商品维表、用户维表的信息整合成宽表，下游拿到就能直接用。

第三组是度量字段，包括sku_num、sku_price、split_total_amount。这类字段是分析的核心。sku_num代表加购数量，sku_price是加购时的商品单价，split_total_amount是加购总金额。前面说过，这三个值都必须反映事件发生当时的情况，特别是价格，千万不能留到下游实时去商品表获取。

第四组是时间字段和业务状态字段，包括create_time、operate_time、is_checked。create_time是加购发生的时间，operate_time是这条购物车记录最后一次被修改的时间，is_checked表示购物车中的商品是否被用户勾选。勾选状态是一个容易被忽略但实际很有用的字段，因为很多用户在购物车里勾选部分商品后才会去结算，它是分析用户加购后是否进入结算流程的重要参考。

2.3 外部表、ORC、Snappy、dt分区的选型理由

建表语句里几个关键参数再单独说说。

为什么用EXTERNAL TABLE外部表。外部表的特点是元数据由Hive管理，但数据文件放在独立的HDFS目录中。数仓里ODS、DWD层的表统一用外部表，好处是如果表结构需要重构或废弃，直接删除元数据不会误删底层数据文件，安全性更好。另外，数仓的数据通常还要被其他计算引擎读取，使用独立目录也更灵活。

为什么分区字段选dt。离线数仓按天调度，几乎所有的任务都是以“天”为粒度跑的。每天处理前一天的数据，写入当天的分区。分区字段用dt，格式统一yyyy-MM-dd，这样下游在写SQL时能清晰区分日期范围，也方便按分区做数据生命周期管理。这个方案简单、直观，最大的优势是排查问题时能一眼定位某个日期分区的数据是否正常。

为什么存储格式用ORC，压缩用Snappy。ORC是Hive生态下成熟的列式存储格式，列式存储对于分析型查询非常友好，因为分析场景经常只取少数几个字段，列存可以只读取需要的列块。Snappy压缩的压缩比不算最高，但解压速度快，适合Hive查询这种需要频繁读取数据的场景。实际生产中可以对比一下Zlib和Snappy，数据量特别大、磁盘紧张时用Zlib，查询性能优先时用Snappy。这个项目选Snappy是合理的。

字段类型方面，时间和金额值得专门说。所有时间字段统一用STRING，格式为yyyy-MM-dd HH:mm:ss，而不是用TIMESTAMP。原因在于Hive的TIMESTAMP处理带有时区解析逻辑，当集群时区配置不一致时会出现时间偏移，而字符串格式可控、直观、与分区字段dt的判断逻辑一致，不会因为时区问题导致数据错误。金额字段统一用DECIMAL(16, 2)，精确到分，这是金额字段的通用约定。用DOUBLE存金额是很多人犯过的错误，浮点数在计算累加时会产生误差，做金额汇总时会出现“差一分钱”的尴尬问题，DECIMAL是定点数，不存在这个隐患。

3. 从ODS到DWD：加购数据加工链路中的关键处理

3.1 数据来源与ODS层的初步清洗

建完表，接下来就是把数据从ODS层加工进DWD。加购数据的来源通常有两种：一种是从业务库的购物车表通过同步工具采集，另一种是从用户行为日志中解析加购事件。这个项目以业务库购物车表为主，ODS层对应表一般是ods_cart_info。

ODS层表的结构与业务库基本一致，但数据质量参差不齐。DWD层加工前，需要先做一轮清洗，主要包括三类操作。一是空值处理，比如user_id为空的加购记录基本是异常数据，需要过滤掉；二是时间格式规范化，业务库的时间有可能是datetime类型，在同步后要统一转成字符串格式；三是对同一分区内的数据做去重，确保主键唯一，防止业务库的重复同步或同步任务重复执行造成的数据冗余。

这一步通常可以直接在加工SQL的where条件中完成，不需要单独建一张清洗中间表，减少链路长度和调度复杂度。

3.2 维度退化SQL与关联逻辑说明

完成基础清洗后，核心操作是把维度属性退化进来。下面是一段典型的加工SQL，按天写入某个分区：

insert overwrite table dwd_trade_cart_add_inc partition (dt = '2024-06-15') select ci.id, ci.user_id, ci.sku_id, sku.spu_id, sku.category1_id, sku.category2_id, sku.category3_id, sku.tm_id, ci.sku_num, sku.price as sku_price, ci.sku_num * sku.price as split_total_amount, ci.create_time, ci.operate_time, ci.province_id, ci.is_checked, ci.source_type, ci.source_id from ods_cart_info ci left join dim_sku_info sku on ci.sku_id = sku.id and sku.dt = '2024-06-15' where ci.dt = '2024-06-15' and ci.user_id is not null and ci.sku_id is not null;

这段SQL的关键点有几个。

为什么join维度表时要带上sku.dt='2024-06-15'这个条件。商品维度表通常是每日全量快照分区，每天的维度数据可能不同，比如商品改价、改类目、改品牌。关联时必须用同一天的维度快照，才能与加购事件发生在同一天保持口径一致。如果用维度表的最新分区去关联历史加购数据，就会出现历史加购记录被贴上最新分类、最新价格的错误。

为什么用left join而不是inner join。教学项目中偏好保留全部加购记录，即使某些商品在维度表中没有关联到，也先把事实数据保留下来，避免因为维度表缺失导致整个事实行丢失。但生产环境更建议在关联后做一次扫描，重点排查那些关联为空的记录占比高不高，如果占比过高，说明维度表同步有问题，需要修复后再跑任务。

为什么金额的计算放在DWD而不是DWS。split_total_amount用ci.sku_num * sku.price计算，直接固化在DWD。这样DWS层做汇总时拿到的是已经计算好的金额，不需要再回头关联商品价格。价格字段是不断变化的，越早固化，口径越稳定，这和省钱记账必须记录购买当时的商品价格是一个道理。

3.3 调度依赖与任务配置建议

加工SQL写好后，调度配置是另一个容易出问题的地方。加购DWD任务的依赖逻辑按顺序应为：ODS层购物车表同步完成、商品维度表同步完成、用户维度表同步完成，然后才能启动DWD加工任务。如果商品维度表还没跑完，DWD任务启动后会出现大量维度关联为空的记录，而且因为是insert overwrite，会把之前没问题的分区数据也覆盖掉，造成不可逆的数据质量事故。

有一点需要强调：任务失败重跑时，要确认依赖的上游表也都已经成功运行，否则单独重跑DWD没有意义。离线数仓的“跑批失败”大多不是父任务失败，而是下游在前置条件不满足的情况下强行启动。调度系统里把这些依赖关系配好，能省去大量排查时间。

4. 加购事务事实表与订单事实表的粒度差异——两个表千万别搞混

4.1 一张表行数的含义完全不同

拿到建好的加购表后，第一个要养成的习惯是搞清楚表粒度。加购事务事实表的粒度是“一次加购事件”，一行记录代表用户某一次加购某个商品。订单事实表的粒度则是“某个订单中的某一条商品明细”，一行记录代表一个订单中的一行商品明细。

两张表行数含义的区别非常关键。如果拿dwd_trade_cart_add_inc求加购数量，直接count(1)得到的是加购事件次数。拿dwd_trade_order_detail求订单量，count(1)得到的是订单明细条数，不是订单数。一个是事件量，一个是明细量，两者在衡量交易规模时口径完全不同。

这也意味着下游做关联分析时，不能简单地认为“加购表的一行对应订单表的一行”。一个用户可能把同一个商品加了多次购，最后只下一次单；一个订单也可能包含多个商品，每个商品对应独立的一行。行与行不是一一对应的关系。

4.2 漏斗分析的正确join姿势

最常见的加购分析是“加购-下单-支付”漏斗。很多人第一步就写错：直接把加购表和订单表按用户ID关联，然后用count(distinct user_id)统计。这样出来的数字往往是错的，因为在多对多关联下会产生笛卡尔积式的数据膨胀，导致加购记录被放大。

一个用户加了3次购物车，下了2个订单，如果直接把两张表join，可能产生6条组合结果，而不是实际的3条加购和2个订单。正确的做法是先确定分析粒度，再在对应粒度上做去重。比如分析用户维度的加购到下单转化率，就应该先按user_id去重统计“有加购行为的用户数”和“有下单行为的用户数”，再计算转化率。如果一定要把两张表join后再算，也必须先分别按分析粒度去重，再关联，而不是让底层明细表直接join。

4.3 指标口径才是真正的坑

这里必须展开说说加购三件套：加购次数、加购人数、加购转化率。

加购次数的定义是从加购事实表中直接count(1)，代表加购事件的次数。加购人数的定义是count(distinct user_id)，代表有多少用户产生了加购行为。这两者在语义上差很多，如果只看加购次数而忽略人数，大促期间一个“囤货型”用户连续加购50件商品，就会让指标看起来非常夸张。

加购转化率更要先明确分子分母口径。是“加购用户到下单用户的转化率”，还是“加购商品到下单商品的转化率”，或者是“加购次数到下单次数的转化率”？三种口径算出来的数字完全不同，如果不写清楚，运营同学和数据分析师会拿着不同的数字开一上午会。

实践中的建议是：在出具指标时，把口径定义直接写在表注释或指标说明里。比如“加购转化率（用户口径）= 当日下单用户数 / 当日加购用户数”。等下游使用这张表时，不会因为口径不统一而出现争论。

5. 建表之外的真实踩坑记录——加购表建模的五个高频问题

5.1 维度关联不上导致NULL扩散

第一批数据跑完后，习惯性查了一下各类目加购分布，发现结果里有一批记录显示NULL。排查过程不算复杂，先抽了几条NULL记录去ODS层查源数据，发现这些记录的商品ID在商品维度表中查不到。

原因是当时商品维度表同步任务比DWD加工任务晚启动了几分钟，DWD任务启动时商品维度表当天分区还没生成，left join全部落空。这个问题光靠SQL解决不了，必须在调度系统里确认依赖关系。此外，更稳妥的做法是加工SQL中对核心维度字段（品类、品牌）增加一个简单的空值拦截，空值率超过阈值直接告警，让数据负责人第一时间介入，而不是等下游报表出现异常才发现。

5.2 重复加购到底保留还是合并

购物车业务里，用户对同一个商品连点两次“加入购物车”，业务上可能表现为数量+1，也可能生成两条加购记录，取决于接口设计。在事务事实表中，我建议保留每一次原始事件，不在DWD层做合并。用户的连续点击行为本身是有价值的分析数据，它体现了用户的犹豫、比较、囤货等心理。

如果下游要做“有多少用户加购了商品A”这类去重统计，可以在指标计算层根据实际业务需要去重。但如果DWD层就早早合并掉，后面想分析用户加购频次分布、单次加购到下单的时间间隔，就完全没有数据基础了。DWD做明细保留，DWS/ADS做口径加工，各司其职。

5.3 时间口径不统一：业务库和日志差在哪

加购表关联行为日志时，经常遇到时间对不上的情况。业务库的create_time记录的是真正写入数据库的时间，用户点击“加入购物车”按钮后，请求经过网络到达服务器，再写入MySQL，这个时间可能已经有几秒到几十秒的延迟。行为日志记录的是用户点击行为发生的时间，通常在客户端埋点时就带上了。

两种时间的差异在秒级或毫秒级，单条记录看不出来，但做时间窗口分析（比如“加购后5分钟内是否下单”）时，几秒钟的偏差也会影响统计结果。在DWD加工之前，建议把加购表的create_time和日志中的行为时间各保留一个字段，并在表注释中明确说明两者的来源和语义，不要混用。

5.4 ORC格式下的小文件问题

这里分享一个调优经验。如果每天同步数据的频率很高，比如每小时同步一次，而加工任务又是按天写入一个分区，一天下来分区内会有多个小文件。ORC格式下小文件过多会严重影响查询性能，因为每次读取要打开大量文件，NameNode和DataNode的压力都会增加。

解决思路有几种。最简单的是在调度配置中合并输出文件，比如在insert任务中设置hive.merge.mapfiles=true等参数；或者每完成一轮同步后，对分区执行一次合并操作。如果觉得运维复杂度太高，更推荐的做法是按照业务数据量调整同步频率，数据量不大的情况下，每天同步一次就够了，没必要每小时都往ODS里灌一小批。

5.5 大促场景下的热点SKU倾斜

最后一坑，是大促期间加购数据量的剧烈波动。某款爆品在促销开始后一分钟内的加购量，可能超过平时一天的数据量。这些记录在按SKU维度做统计时，会集中在某几个Reduce任务上，出现节点忙死、其他节点空闲的情况，也就是数据倾斜。

在建表阶段能做的预防，是把分区字段和常用的过滤字段设计好，比如sku_id、dt这些字段一定要有，这样下游分析时可以先用分区裁剪缩小数据范围。加工任务层面，可以给热点SKU的key加随机前缀或者采用两阶段聚合的思路。DWD层本身不需要为一次大促做过度优化，但要有预案，知道最可能倾斜的地方在哪里。

加购事务事实表是整个交易域DWD层里性价比非常高的一张表，表结构不算复杂，但承载了下游不少核心指标。把它建好，把口径定义清楚，后面做DWS层汇总、ADS层报表都会顺手很多。