做电商离线数仓DWD层永远是最费心思的一层。ODS层的原始数据又杂又乱DWS层的指标又高度聚合中间这层事务事实表要是没设计好后面所有统计口径都会跟着歪。我之前刷到尚硅谷数仓搭建项目里第31篇笔记专门讲DWD层一组事务事实表的建表语句和设计思路今天就把这个话题展开聊聊工具域优惠券使用支付、互动域收藏商品、流量域页面浏览、用户域用户注册、用户域用户登录这五张表在实际数仓项目里到底该怎么建、怎么用以及建表之后那些容易踩的坑。这篇内容主要适合三类人正在系统学数仓建模、准备做离线数仓项目、或者已经入行但想核对DWD层建表思路的数据开发同学。我会把每张表的事务粒度、字段设计、ETL加工要点都拆开讲写得不深但保证实用。1. 先想清楚这五张表为什么都叫“事务事实表”1.1 事务事实表和其他事实表的区别很多新手会把“事实表”和“明细表”混着叫但实际上事实表还能细分出三种类型。今天这五张表都属于同一个类型事务事实表也就是记录“每一件发生过的事”的表。一行数据代表一次业务事件比如一次优惠券核销、一次商品收藏、一次页面浏览、一次用户注册、一次用户登录。与它对应的是周期快照事实表典型场景是库存快照、余额快照每天记录一次“当前还剩多少”。还有累积快照事实表常用在订单履约流程里一行订单不断更新下单、支付、发货、完成这些环节的时间字段。用生活化的方式理解就是银行卡流水是事务事实表每一笔进出账都记一行余额是周期快照事实表每天记一次当前余额信用卡账单还款进度则像累积快照表关注的是同一笔业务从开始到结束的完整链路。1.2 为什么不做成状态表或用户维表实际开发中经常有人问用户收藏商品不是有收藏状态吗直接同步一张收藏全量表不就行了注册和登录的信息在用户维表里也有为什么还要单独建表问题在于状态表回答的是“当前是什么”回答不了“某一天发生了什么”。分析优惠券核销率需要知道每天在支付环节用了多少张券、每张券抵扣了多少钱分析用户活跃需要知道一天有多少次登录、分布在哪些时段分析商品收藏需要知道哪天收藏量突然涨了才能去追活动效果。这些都是过程指标只能靠事务事实表还原。DWD层的价值就是把这些业务过程展开成可分析的明细DWS和ADS才能在上面放心聚合。2. 建表语句逐张拆解建表之前先把五张表的核心设计信息列个总览后面展开时不容易乱建议表名业务过程事务粒度主键/唯一键分区字段dwd_tool_coupon_pay优惠券支付抵扣每次支付使用一张券coupon_use_iddtdwd_interaction_favor_add用户收藏商品每次收藏事件favor_id 或 user_id sku_id create_timedtdwd_traffic_page_view用户浏览页面每次页面访问埋点日志唯一IDdtdwd_user_register用户注册每个用户注册一次user_iddtdwd_user_login用户登录每次登录事件login_iddt这五张表里注册表有点特殊它虽然是事务表但一个用户一辈子通常只有一条数据所以更准确地说它是“一次性事件事务表”。登录表和页面浏览表则是典型的高频事务表一天可能产生大量行。2.1 工具域优惠券使用支付事务事实表先看建表语句CREATE TABLE IF NOT EXISTS dwd_tool_coupon_pay ( coupon_use_id STRING COMMENT 优惠券使用记录ID, order_id STRING COMMENT 关联订单ID, user_id STRING COMMENT 用户ID, coupon_id STRING COMMENT 优惠券ID, coupon_amount DECIMAL(16,2) COMMENT 优惠券抵扣金额, payment_time STRING COMMENT 支付时间yyyy-MM-dd HH:mm:ss, province_id STRING COMMENT 收货省份ID, coupon_type STRING COMMENT 优惠券类型满减/立减/折扣, source_type STRING COMMENT 优惠券来源领取/系统发放/活动, etl_time STRING COMMENT ETL处理时间 ) COMMENT 工具域优惠券使用支付事务事实表 PARTITIONED BY (dt STRING COMMENT 日期分区) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);这张表记录的核心事件是“优惠券在支付环节被使用”。最关键的字段是coupon_use_id、order_id、coupon_id和coupon_amount。我建议把coupon_use_id当成唯一业务键因为一张订单可能用多张券如果用order_id做主键数据会直接丢失。字段里冗余了province_id和coupon_type这属于数仓建模里的“维度退化”操作。原本省份信息和券类型都能通过关联维度表拿到但在DWD层直接冗余下来DWS层聚合时就能少好几次join。数仓里性能问题往往就出在join太多所以像这种常用的分析维度能退化就退化。金额字段用DECIMAL(16,2)而不是DOUBLE是为了避免浮点精度问题。做优惠券核销金额汇总时哪怕差一分钱都可能被财务侧找上门。时间字段用STRING存格式化字符串离线分析场景下完全够用而且比TIMESTAMP更直观。2.2 互动域收藏商品事务事实表收藏商品的建表语句相对简洁CREATE TABLE IF NOT EXISTS dwd_interaction_favor_add ( favor_id STRING COMMENT 收藏记录ID, user_id STRING COMMENT 用户ID, sku_id STRING COMMENT 商品SKU ID, spu_id STRING COMMENT 商品SPU ID, create_time STRING COMMENT 收藏时间yyyy-MM-dd HH:mm:ss, is_favor STRING COMMENT 收藏状态1收藏中0已取消, etl_time STRING COMMENT ETL处理时间 ) COMMENT 互动域收藏商品事务事实表 PARTITIONED BY (dt STRING COMMENT 日期分区) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);收藏表很容易被误解成“当前收藏状态表”但这张表的定位是“收藏行为发生记录”。我把收藏和取消收藏都看成收藏域的两种业务事件如果只统计新增收藏就在ETL时过滤出收藏动作如果后续需要分析取消收藏也可以单独做分析。之所以保留is_favor字段是为了让这张表能兼容多种分析场景。但要注意这张表的行不代表当前收藏状态比如用户今天收藏、明天取消如果只查这张事务表会看到两条记录而不是“当前没有收藏”。要实现“当前收藏夹”功能得另外做拉链表或直接同步业务库全量表。冗余spu_id的原因和优惠券表冗余省份一样常见的收藏分析都是按SPU维度看的比如“被收藏最多的商品”直接从表里取spu_id能省一次SKU维表关联。收藏表整体数据量不大多冗余一两个字段对存储影响很小。2.3 流量域页面浏览事务事实表页面浏览表是五张表里数据量最大、字段设计最需要克制的一张CREATE TABLE IF NOT EXISTS dwd_traffic_page_view ( page_id STRING COMMENT 页面ID, page_name STRING COMMENT 页面名称, visit_time STRING COMMENT 页面浏览时间yyyy-MM-dd HH:mm:ss, user_id STRING COMMENT 登录用户ID未登录为空, session_id STRING COMMENT 会话ID, is_entry STRING COMMENT 是否入口页1是 0否, is_exit STRING COMMENT 是否退出页1是 0否, source_type STRING COMMENT 流量来源类型直接/搜索/广告/分享, refer_url STRING COMMENT 来源URL, target_url STRING COMMENT 当前页面URL, province_id STRING COMMENT 地域ID, device_type STRING COMMENT 设备类型PC/APP/H5, os_type STRING COMMENT 操作系统, etl_time STRING COMMENT ETL处理时间 ) COMMENT 流量域页面浏览事务事实表 PARTITIONED BY (dt STRING COMMENT 日期分区) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);页面浏览表的事务粒度是“一次页面访问”。埋点日志里字段非常多设备品牌、网络类型、App版本、分辨率等都能拿到但建表时我只挑最常用的十几个字段。原因很直接页面浏览日志的体量是几亿行起步把所有字段都存进DWD层存储成本会高得离谱而且大部分字段上层分析根本用不到。真要查设备品牌、网络类型可以回ODS原始日志里去捞DWD层只保留高频分析字段。session_id是这张表的灵魂它决定了一次会话内页面浏览怎么串联。如果上游埋点没有给session_idETL阶段就要按用户ID和时间间隔去划分会话通常超过30分钟没有新动作就算新会话。这块逻辑一般放到流量域专门处理。这里要注意未登录用户的user_id是空的但这类浏览日志不能丢。流量分析里匿名用户的行为同样重要后续可以用session_id作为分析主体等用户登录后再做用户关联。所以user_id字段不要设成非空约束DWD层没有强约束但ETL里要保留空值。2.4 用户域用户注册事务事实表注册表的建表语句不是很复杂CREATE TABLE IF NOT EXISTS dwd_user_register ( user_id STRING COMMENT 用户ID, username STRING COMMENT 用户名, mobile STRING COMMENT 手机号已脱敏, email STRING COMMENT 邮箱, register_time STRING COMMENT 注册时间yyyy-MM-dd HH:mm:ss, register_channel STRING COMMENT 注册渠道APP/小程序/H5/PC, register_ip STRING COMMENT 注册IP, province_id STRING COMMENT 注册地省份ID, etl_time STRING COMMENT ETL处理时间 ) COMMENT 用户域用户注册事务事实表 PARTITIONED BY (dt STRING COMMENT 日期分区) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);注册表的事务事件是“一个新用户完成注册”。正常业务里注册是低频事件一个用户只有一次不过它在用户增长分析中地位很高每天新增用户数、各渠道拉新效果都要靠这张表。我看到有些团队会把注册信息直接并进用户维表那样做用户维表确实更完整但要按天统计新增用户时就得翻维表的变更记录非常痛苦。不如单独建一张注册事务表要用户最新信息时再关联用户维表各司其职。手机号和邮箱是敏感字段DWD层建表时就要考虑脱敏。我一般把手机号处理成前3后4的格式邮箱只保留用户名首字符和域名既能支持渠道分析和用户分群又避免把明文隐私落到分析环境。这个操作在ODS到DWD的ETL里完成而不是在下游再脱敏。2.5 用户域用户登录事务事实表登录表字段更简洁CREATE TABLE IF NOT EXISTS dwd_user_login ( login_id STRING COMMENT 登录记录ID, user_id STRING COMMENT 用户ID, login_time STRING COMMENT 登录时间yyyy-MM-dd HH:mm:ss, login_channel STRING COMMENT 登录渠道APP/小程序/H5/PC, login_ip STRING COMMENT 登录IP, device_type STRING COMMENT 设备类型, app_version STRING COMMENT App版本号, etl_time STRING COMMENT ETL处理时间 ) COMMENT 用户域用户登录事务事实表 PARTITIONED BY (dt STRING COMMENT 日期分区) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);登录表和页面浏览表一样是高频事务表一个用户一天可能登录很多次。它最核心的用途是估算DAU、分析活跃时段、统计登录渠道分布。用户维表里的“最近登录时间”只能看单个用户最后一次登录完全无法支撑活跃趋势分析所以登录流水必须单独建表。login_channel和device_type看着有重叠但一个是业务入口一个是硬件环境。比如用户可能在APP里通过微信授权登录渠道是“微信登录”设备类型是“手机”。这两个维度都能单独做分析所以都保留。登录表的数据源一般是业务后端打的登录日志。如果上游只有用户表的last_login_time那说明日志链路没打通需要反向推动业务侧记录登录流水。数仓开发经常要处理这种“上游没数据”的尴尬情况提前知道这个坑能少走很多弯路。3. 从ODS到DWD的ETL加工要点建表语句只是第一步五张表能不能真正落地可用取决于ODS到DWD这段ETL写得到不到位。3.1 公共清洗逻辑离线数仓的ODS层通常按天同步源系统数据或日志数据。写入DWD前我至少会做三件事过滤无效数据、处理时间字段、去重。无效数据包括主键为空的记录、核心业务字段为空的记录、内部测试数据。比如页面浏览表如果page_id为空这日志基本没法分析直接过滤。收藏表如果user_id或sku_id为空也直接丢弃。时间字段处理需要注意时区尤其是埋点日志里的13位时间戳。日志服务端可能记录的是ts毫秒值需要转成北京时间再落分区。我的写法比较固定FROM_UNIXTIME(CAST(ts / 1000 AS BIGINT), yyyy-MM-dd HH:mm:ss) AS visit_time离线任务跑批通常用${bizdate}作为业务日期参数dt分区直接取这个值。ETL里查询ODS时也用dt ${bizdate}保证批次之间不会互相干扰。去重是DWD层最容易出问题的地方。ODS数据因为上游重试、消息重复消费、同步任务重复执行经常有重复记录。常见方案是用ROW_NUMBER()窗口函数按业务主键排序取rn1。比如优惠券使用表INSERT OVERWRITE TABLE dwd_tool_coupon_pay PARTITION (dt ${bizdate}) SELECT coupon_use_id, order_id, user_id, coupon_id, coupon_amount, payment_time, province_id, coupon_type, source_type, etl_time FROM ( SELECT coupon_use_id, order_id, user_id, coupon_id, coupon_amount, payment_time, province_id, coupon_type, source_type, etl_time, ROW_NUMBER() OVER (PARTITION BY coupon_use_id ORDER BY etl_time DESC) AS rn FROM ods_tool_coupon_use WHERE dt ${bizdate} ) t WHERE t.rn 1;如果业务上没有真正的业务主键比如页面浏览日志就用日志埋点ID或者session_id 时间戳 页面ID组合作为去重键。去重键越窄越容易误删数据越宽越容易留重复数据这个尺度要结合上游发送机制来定。3.2 各表加工的差异点五张表虽然都走清洗、转换、去重的套路但细节差异很大。优惠券支付表加工时重点校验payment_time是否为空、coupon_amount是否大于0。如果一张订单用了多张券上游通常会拆成多条记录coupon_use_id必须有独立值不能用order_id当主键否则会漏数据。还需要额外确认这个券到底有没有被支付核销有些券可能只是领取了但没有在支付环节使用这类数据不能进入这张表。收藏表加工时要区分“新增收藏”和“取消收藏”。如果ODS层是收藏全量表在ETL里要对比前一天DWD表中的收藏记录只有新增的收藏才写入这张事务表。实际操作中可以用LEFT JOIN加空值判断也可以用LAG()取上一次状态。我建议用user_id sku_id作为判断唯一键而不是依赖favor_id因为有些业务系统在取消再收藏时可能生成新的favor_id这时候用favor_id判断会漏掉“重新收藏”的事件。页面浏览表加工时日志解析逻辑最重。埋点日志通常是一整条JSON需要用get_json_object把公共字段和页面字段拆出来。拆完字段后再过滤爬虫流量和内部测试流量。比如UserAgent里包含spider、bot关键字的日志要剔除。这里要格外注意过滤条件不能误伤真实用户。登录表加工时重点是保留完整流水不要做“一个用户只保留一条登录记录”的错误处理。登录流水量大SELECT字段要克制不需要带用户名、邮箱只保留能定位用户和登录环境的字段即可。注册表加工时要特别注意跨天回补。凌晨0点到8点的批任务经常处理的是昨天的数据register_time是昨天但同步任务的业务日期可能已经切到明天分区不能用current_date必须用调度参数${bizdate}统一控制。3.3 分区与存储优化这五张事务事实表我都建议用dt单分区每次跑批只覆盖当天分区重跑历史数据也不会污染其他天。存储格式选PARQUET SNAPPY在查询性能和存储成本之间最平衡。如果数据量特别大比如页面浏览表还可以考虑分区内的数据分布优化。INSERT时用DISTRIBUTE BY (user_id)让相同用户的数据落到同一个文件既有利于会话分析也能避免某个Reduce处理过多数据导致倾斜。不建议在这几张表上用ORC之外的格式也不建议为了极致压缩去用高压缩比算法。离线分析最重要是列裁剪和谓词下推PARQUET SNAPPY已经能很好支持。字段类型上所有ID字段我建议统一用STRING。虽然BIGINT更省空间但来源系统的ID经常出现前导零、超长整型、拼接字符串等情况用字符串最稳妥。数仓是给人查数的不是给数据库省空间的。4. 常见问题与排查实录建表和ETL看着不难真正跑数仓任务的时候问题经常藏在细节里。我挑几个自己实际踩过的坑。4.1 优惠券使用流水重复导致补贴金额翻倍有一次跑完DWS层的优惠券核销汇总发现补贴金额比业务后台报表高了接近5%。排查下来发现不是计算逻辑错而是上游在订单支付回调时对优惠券核销接口重试了两次ODS层同一张订单带了两个相同的coupon_use_id。业务库没做唯一索引问题一直潜伏到数仓层才爆发。解决办法就是前面提到的ROW_NUMBER()去重但去重键必须定成coupon_use_id不能图省事用order_id否则一单多券的数据会被误删。另外建议在去重时按etl_time倒序取最新一条不要随便取第一条因为上游可能出现先快照后更新的场景。4.2 收藏表一天重复写入多条收藏事件收藏业务有“收藏”和“取消”两个操作。如果ODS同步的是业务库收藏流水问题不大如果同步的是收藏全量表直接把全量数据覆盖进DWD事务表就会把历史已存在且没有变化的收藏记录再次当成新事件写进来。我的处理方式是在ETL中先按user_id sku_id取全量表最新状态再用当前数据去和已经落地的DWD表做匹配匹配不上的才作为新收藏事件写入。这样能保证事务表只记录“新发生的收藏”而不是把全量快照当流水用。4.3 页面浏览表出现严重数据倾斜页面浏览日志体量大ETL里一旦涉及到按session_id聚合或join很容易因为少数大流量会话导致某个Reduce卡死。我遇到过某种异常脚本用同一个session_id刷了几十万条日志直接把当天任务拖垮。后续加了两个措施第一在ETL里过滤明显异常的session比如短时间内访问次数超过正常阈值的第二加“超大session拆分”逻辑当一个session访问次数超过阈值时把session_id加后缀拆成多个不同session。阈值要看业务正常用户行为分布来定一般50到100次比较合理不能设太低否则会把真实大促期间的用户session拆碎。4.4 注册表和登录表的日期分区对不齐注册表按register_time分区登录表按login_time分区看起来没问题。但曾经有一次上游日志服务时区配置错误导致凌晨时段出现“注册时间在昨天”但调度系统业务日期是“今天”的情况两层数据对不上。后来在ETL里统一加了一层时间规范所有时间字段先转成标准北京时间字符串再根据转换后的时间计算对应的dt分区。这样每条记录都属于它真实发生的日期而不是同步执行的日期。数仓里最怕的就是各表时间口径不一致尤其是分区字段和业务时间字段混用。4.5 小文件过多拖慢查询性能DWD层如果每天跑批而不控制文件数量一年下来小文件数量会非常惊人。特别是页面浏览表一天产生的文件数可能上千查询时频繁扫描文件列表性能下降明显。我现在的流程是每次INSERT OVERWRITE前先DISTRIBUTE BY一个能均匀打散的字段比如user_id让每个Reduce写出的文件大小相对均匀。再配合定期的小文件合并任务把小于一定阈值的小文件重写到更大的文件里。对于页面浏览表这种大表小文件控制尤其重要否则后患无穷。建表这件事看似简单但事务事实表的设计质量直接决定了DWS层好不好写、ADS层准不准。我个人的习惯是每张DWD表建完后一定在表注释里把事务粒度写清楚这张表记录什么事件一行代表什么唯一键是什么。这三个问题想明白了建表语句就是水到渠成的事。尤其是优惠券支付、收藏、页面浏览、注册、登录这五张表业务上看着不复杂但粒度定义差一点后面统计的口径就会差很多。多写几行注释比写十页设计文档都管用。