数据仓库建模实战：事实表与维度表的拆分、粒度与SCD策略

写这篇文章之前我在脑海里先过了一遍这些年接触过的数仓项目。说实话事实表和维度表这六个字几乎是所有数据从业者的入门第一课但也是很多人在实际建模时最容易翻车的地方。面试里能把概念背得滚瓜烂熟的人不少可真到业务部门甩过来一张乱七八糟的订单表能稳住阵脚把它拆成规范的事实表和维度表并且让下游报表不出幺蛾子的还真不多。这篇文章我不打算按教科书的路子讲。咱们直接从为什么非拆不可说起把一个电商订单场景从零到一拆给你看再把我踩过的坑、总结的排查思路一并倒出来。不管你是刚转行做数仓的还是被数据口径问题折磨的分析师这篇应该都能给你一些能直接上手的干货。1. 为什么一张订单表活不下去从一次报表事故说起先讲个真事。有个电商项目早期为了图省事业务库的表结构基本是宽表万能的思路。订单表里有用户昵称、收货地址、商品名称、类目、供应商名字甚至还有用户注册时间。当时觉得挺好查数据一select全出来不用join速度快得很。结果运营要做一次用户复购分析需求是统计每个用户在不同商品类目下的购买频次和金额。SQL写到一半就卡住了——商品名称变了怎么办一个订单里有多件商品怎么拆用户改了昵称历史订单算谁的更麻烦的是技术这边想统计华东大区的季度GMV结果发现同一批订单在不同报表里口径对不上因为有的报表join了最新的用户维度表有的报表直接用的订单表里的冗余字段。这其实就是事实表和维度表没有分离导致的典型事故。我后来跟很多同行聊过大家都遇到过类似的情况一开始觉得冗余字段方便后面发现所有麻烦都来自当初那个方便。分开建模的核心逻辑用大白话说就是把发生了什么事和这件事发生在谁/什么/哪里身上彻底拆开。发生了什么事——订单金额、下单数量、支付时间这些是会随着每一次业务行为不断产生的它们适合放进事实表。发生在谁/什么/哪里身上——用户是谁、商品是什么、属于哪个类目、发货到哪个城市这些是相对稳定的描述性信息它们适合放进维度表。为什么会这样设计我举个例子你就明白了。假设你一个月产生10万条订单但你的活跃用户可能只有1万。如果你在每一条订单里都冗余一遍用户昵称和用户等级意味着这1万个用户的属性变化要同步更新到10万条订单里。而事实表和维度表分离后用户属性变了只需要改维度表里那1条记录所有订单的关联查询自动就拿到最新属性了。这就是维度建模在数据一致性维护上的核心收益。再说空间。一个用户昵称平均10个字符10个字符在UTF-8编码下占30字节左右。10万条订单如果全部冗余昵称就是3MB。听着不多是吧那如果是10亿条订单呢光一个字段就多出30GB。维度表拆出来后订单表里只存一个用户ID这个ID用bigint只占8字节10亿条订单也就8GB。更关键的是查询性能完全不一样——事实表变瘦了扫描的数据量少了聚合自然就快了。所以拆分的本质不是为了规范而规范而是用空间换一致性用join换灵活性。维度表虽然需要join但换来的是口径统一和存储成本的大幅下降。关于两类表的数量变化我在实际项目里通常会告诉团队这么记表类型数据量变化规律实际案例事实表持续快速增长几乎不更新历史记录订单事实表、流量日志事实表维度表增长缓慢偶尔更新属性值用户维度表、商品维度表、日期维度表这个差异直接决定了后面抽取策略、存储策略的设计方向后面我会细讲。2. 维度表没你想的那么简单退化维度、角色维度与SCD策略很多人觉得维度表不就是一张字典表嘛把ID和名称对应上就完了。真这么想就太小看它了。实际建模过程中维度表才是最容易让数仓工程师挠头的地方。2.1 退化维度不建表反而是最优解刚学维度建模的时候我特别迷信每个维度都要单独建表这个原则。直到有一次做订单分析发现订单表里有个订单号字段按理论它应该是一个维度——因为订单本身有下单时间、支付时间、订单状态这些描述属性。但实际做的时候你会发现如果把订单维度单独拆出来这张维度表的行数和事实表一样多join的时候等于把事实表又复制了一份。不仅没有省空间反而让查询多了一次大join性能直接崩。这个问题的标准解法就是退化维度——把订单号直接留在事实表里不单独建维度表。因为订单号本身虽然是维度属性但它的基数极高、几乎每条事实都不一样单独建模的收益趋近于零。我现在的判断准则是如果一个维度字段在事实表里就能直接满足90%的分析需求且它的基数逼近事实表行数就别纠结直接留在事实表里当退化维度用。订单号、交易流水号、发票号都属于这一类。2.2 角色维度一个用户表撑起买家卖家两个角色再讲一个高频场景。一个订单事实表既关联买家ID又关联卖家ID。但用户维度表只有一张你不能拆成买家维度表和卖家维度表两张因为数据来源都是同一张用户表。这时就要用角色维度在事实表里分别存buyer_id和seller_id在生成数据集市层时把同一张用户维度表join两次通过别名区分角色。我在SQL里通常是这么处理的SELECT f.order_id, buyer.user_name AS buyer_name, seller.user_name AS seller_name, f.order_amount FROM dwd_order_fact f LEFT JOIN dim_user buyer ON f.buyer_id buyer.user_id LEFT JOIN dim_user seller ON f.seller_id seller.user_id;这里有个特别容易踩的坑如果用户维度表里已经有用户类型字段比如普通用户/商家用户你直接拿buyer_id去关联可能会关联出一条本身就不是买家的用户。所以角色维度在ETL时通常要加过滤条件比如买家角色必须user_typenormal卖家角色必须user_typemerchant。不加这个条件指标就会悄悄算错。2.3 SCD策略历史订单和最新维度的拉锯战再说维度表的更新。用户改了手机号、商品换了类目这些在业务系统里就是update一条记录但在数仓里是个大问题——历史订单上的维度属性到底跟着变还是不变第一种做法是直接覆盖SCD Type 1简单粗暴但历史订单关联出来的用户手机号会变成最新的。如果分析上个月订单联系方式的地区分布结果全被最新的手机号污染了。第二种做法是保留历史SCD Type 2每次属性变更就新增一条记录维度表里同一个用户会有多行用生效时间区分。这样历史订单join维度表时通过时间切片能拿到当时真实的快照。我见过很多团队一上来就上SCD Type 2结果维度表行数膨胀、ETL复杂度飙升最后维护不下去。我的实际建议是分情况分析侧几乎不关注历史属性的字段比如用户VIP等级用Type 1覆盖就行。分析侧强依赖历史状态的字段比如商品所属类目、用户所属大区用Type 2扛住。实在拿不准的先Type 1上线等真实需求来了再升级Type 2也不迟。提示SCD Type 2在实现时一定要保留三件套——start_date、end_date、is_current。没有这三个字段你根本没法做时间切片维度表就会退化成一张看起来有历史实际查不了历史的表。3. 事实表粒度这是整个数仓建模里最需要较真的地方如果说维度表决定了分析的角度事实表就决定了分析的精度。这个精度业界有个专门术语叫粒度Grain。每次评审模型的时候我第一个问的问题永远是这张事实表的粒度是什么3.1 粒度是什么以及为什么它决定一切简单说粒度就是一行数据代表一次什么样的业务事件。同样是一张订单事实表粒度可以是一行 一个订单适合看订单维度的汇总。一行 订单 一个商品明细适合看商品维度的分析。一行 订单 商品 一次状态流转适合做订单状态全链路追踪。粒度不同同一张表里的同一行数据含义就完全不同。最典型的翻车现场就是——分析师拿着订单事实表去统计商品销量结果发现这张表一行是一个订单而不是订单商品明细如果一个订单里有3件商品他就只数到了1。所以我在建模规范里强制要求事实表的第一行注释必须写明粒度定义。甚至表名里我也习惯带上粒度标识比如dwd_order_fact一行一个订单和dwd_order_item_fact一行一个订单商品就能清楚区分。3.2 三种事实表怎么选事务、周期快照、累积快照业务事件不止一种形态事实表也跟着分成三类。我直接用电商场景给你捋清楚。事务事实表Transactional Fact Table一行对应一个业务事件比如每次下单、每次支付、每次退款都记一条。它的特点是只增不改——事件发生了就insert过去了就永远留在那里。适合做流量分析、交易分析这类行为流水统计。周期快照事实表Periodic Snapshot Fact Table按固定周期比如每天记录某个对象的当前状态。最典型的就是每日库存快照表——库存不是事件它是个状态每天结束时记录一次当时的库存量。天数一多这张表的行数就等于对象数 × 天数用于分析库存趋势、余额变化这类状态类指标。累积快照事实表Accumulating Snapshot Fact Table),专门用来跟踪一个业务过程从开始到结束的完整生命周期。比如订单从下单→支付→发货→签收→退款可选同一笔订单在主键上只占一行每次状态变化就update对应的里程碑时间字段。这种表算平均履约时长支付转化耗时特别方便因为所有关键时间点都在一行里。我在项目里的选型表是这样的业务场景推荐事实表类型核心原因订单明细、页面点击事务事实表事件流只增不改保留完整行为每日余额、每日库存周期快照事实表状态量按周期记录快照订单履约全链路累积快照事实表多个里程碑时间字段方便算时长3.3 半加性事实与不可加事实指标口径之争的根源事实表里存的不只是数量、金额这种可直接相加的指标还有两类特殊指标。半加性事实比如账户余额。你按日期维度去sum所有账户的日终余额可以得到全账户总余额这个是有意义的但你按账户维度去sum这个账户一个月内每一天的余额得到的月累计余额毫无意义。这类指标只能用最后一条或平均来聚合不能无条件sum。不可加事实比如比率、温度、单价。你把两个订单的单价加起来是没有任何业务含义的。这类指标通常要在事实表里拆成分子和分母两个可加字段到报表层再相除。我记得有个项目分析师把商品折扣率直接在SQL里sum了聚合出来的折扣率超过100%业务方直接炸了。这不是分析师不专业是建模时没告诉他哪些字段不可加。所以我在事实表注释里会明确标注每个指标的聚合方式——SUM、AVG还是MAX/LAST。这个习惯帮我挡掉了很多数对不上的扯皮。4. 实战落地从零拆解电商订单建模全过程概念讲了一堆估计你已经手痒了。这一节我带你把一个真实的电商订单场景从原始数据走一遍到完整模型你可以直接抄作业。4.1 业务口径先定清楚再谈建表动手建表前第一件事永远是跟业务方对齐口径。我拿到原始订单数据后会拉着运营、财务一起确认以下几个问题订单金额包含运费吗退款订单还计入GMV吗取消的订单怎么处理保留还是过滤下单时间、支付时间、发货时间分别用什么时区不夸张地说这4个问题能引发80%的指标口径冲突。所以我在很多项目里都坚持出一份指标口径文档把每个口径定死再开始建表。4.2 原始表长什么样我给它改造成什么样假设原始业务库的订单表长这样做了一定简化CREATE TABLE business_orders ( order_id BIGINT COMMENT 订单ID, order_no VARCHAR(64) COMMENT 订单编号, user_id BIGINT COMMENT 买家ID, seller_id BIGINT COMMENT 卖家ID, item_id BIGINT COMMENT 商品ID, item_name VARCHAR(255) COMMENT 商品名称, category_id BIGINT COMMENT 类目ID, category_name VARCHAR(255) COMMENT 类目名称, order_amount DECIMAL(10,2) COMMENT 订单金额, item_num INT COMMENT 商品数量, order_status TINYINT COMMENT 订单状态, create_time DATETIME COMMENT 下单时间, pay_time DATETIME COMMENT 支付时间 );这看着好像没什么问题但其实全是问题。item_name、category_name都是冗余描述字段category_id和item_name如果变了历史订单就跟着变了统计就乱了。改造思路是拆成一张事实表加几张维度表事实表dw_order_fact一行 一个订单CREATE TABLE dw_order_fact ( order_id BIGINT COMMENT 订单ID, user_id BIGINT COMMENT 买家ID, seller_id BIGINT COMMENT 卖家ID, item_id BIGINT COMMENT 商品ID, category_id BIGINT COMMENT 类目ID, order_amount DECIMAL(10,2) COMMENT 订单金额(含运费), item_num INT COMMENT 商品数量, order_status TINYINT COMMENT 订单状态编码, status_desc VARCHAR(20) COMMENT 订单状态描述, create_time DATETIME COMMENT 下单时间, pay_time DATETIME COMMENT 支付时间, dt VARCHAR(10) COMMENT 分区日期 ) PARTITIONED BY (dt) ;这里我把订单状态编码和描述都留在事实表里。为什么状态描述也冗余进去因为状态本身是事务的属性不是维度属性单独建订单状态维度表属于脱裤子放屁直接放事实表最省事。另外为了控制粒度如果一个订单里有多个商品我会在订单事实表里保留一行同时拆一张dw_order_item_fact按订单商品一行来存商品级别的明细。维度表dim_userCREATE TABLE dim_user ( user_id BIGINT PRIMARY KEY, user_name VARCHAR(64), mobile_area VARCHAR(32) COMMENT 手机号归属地, user_level TINYINT COMMENT 用户等级, is_merchant TINYINT COMMENT 是否商家, start_date VARCHAR(10), end_date VARCHAR(10), is_current TINYINT ) ;注意这里加了start_date/end_date/is_current这就是前面说的SCD Type 2。用户等级和大区这类字段如果分析侧需要回看历史就用这种结构存。维度表dim_itemCREATE TABLE dim_item ( item_id BIGINT PRIMARY KEY, item_name VARCHAR(255), category_id BIGINT, category_name VARCHAR(255), brand_name VARCHAR(128), shelf_status TINYINT ) ;商品名称、类目、品牌全部移到维度表。类目如果经常调整归属关系可以考虑把category_id拆成单独的dim_category这块看你们的实际需求。4.3 ETL映射从业务模型到数仓模型的转换细节表结构定了ETL怎么写我习惯的思路是先映射后清洗。第一步是类型映射。业务库的DATETIME要不要原样保留order_amount DECIMAL(10,2)要不要转成DOUBLE我强烈建议保留DECIMAL不要在数仓里用FLOAT存金额否则到后面算总账的时候浮点误差会让财务抓狂。第二步是编码统一。业务库可能用order_status1表示待支付但另一张历史表用order_statusUNPAID。入库时统一转成同一套编码体系避免后续每次查询都要翻译。第三步是时间切分。下单时间、支付时间、发货时间最好各存各的字段同时把日期分区字段dt单独拎出来。这样既能支持当天增量抽取也能支持历史全量回刷。第四步是退化维度处理。像订单号order_no这种虽然理论上是维度属性但基数太高单独建表收益低直接留在事实表即可。经验法则是当维度表的行数接近事实表行数且除了业务编号外几乎没有其他描述属性时优先考虑退化维度。下面给一个增量抽取的简化示例INSERT OVERWRITE TABLE dw_order_fact PARTITION (dt 20240520) SELECT order_id, user_id, seller_id, item_id, category_id, order_amount, item_num, order_status, CASE order_status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已发货 WHEN 4 THEN 已完成 WHEN 5 THEN 已取消 END AS status_desc, create_time, pay_time, 20240520 AS dt FROM business_orders WHERE create_time 20240520 00:00:00 AND create_time 20240521 00:00:00;增量抽取的原理很简单每天只捞当天新增或变更的数据降低ETL压力。但要注意订单状态是会发生变化的今天创建的订单明天可能完成支付。所以如果只按create_time抽状态变更就丢了。这就需要一个变更捕获的机制——通常做法是业务侧提供一个update_time字段数仓每天按update_time 当天开始 AND update_time 当天结束去捞同时做幂等重刷先删除当天分区的旧数据再写入最新数据。4.4 汇总层指标我为什么推荐一次建模、多处复用事实表和维度表只是明细层最后业务看的通常是汇总指标。做汇总层时我最反感的就是每个报表各自写一套SQL去聚合明细这样同一个GMV每个报表查出来都不一样。正确做法是把汇总指标固化成一张或多张汇总表所有下游报表统一读汇总表。CREATE TABLE dws_trade_daily ( dt VARCHAR(10) COMMENT 分区日期, category_id BIGINT COMMENT 类目ID, category_name VARCHAR(255) COMMENT 类目名称, order_cnt BIGINT COMMENT 订单数, item_cnt BIGINT COMMENT 商品件数, gmv_amount DECIMAL(16,2) COMMENT GMV, pay_amount DECIMAL(16,2) COMMENT 支付金额, refund_cnt BIGINT COMMENT 退款单数, refund_amount DECIMAL(16,2) COMMENT 退款金额 ) ;这张日汇总表可以按类目维度做多维汇总也可以扩展出按dt、user_level、seller_id等维度组合的多张汇总表。关键是同一层级的指标必须在一个SET里生成不能这个报表自己算一遍、那个报表再算一遍。5. 数不准了怎么办一套可复用的排查链路建模再规范也挡不住业务系统各种骚操作。数据对不上、明细和汇总不一致、历史数据被篡改——这类问题我在每个项目里都遇到过。我把排查思路整理成一套链路你照着走基本能定位到问题。5.1 第一步核对口径和维度是数仓的问题还是定义的问题收到数不对的反馈不要急着查SQL。先问业务方三个问题你统计的时间范围是什么你统计的指标定义是什么你预期的数值大概在什么量级很多时候业务方说的GMV和你数仓的GMV根本不是一回事。他们可能算的是含退款订单的GMV你算的是剔除退款后的净GMV这种不一致不是你代码修复能解决的必须先对齐口径。5.2 第二步从明细表逐层定位是抽数的问题还是建模的问题口径确认没问题就开始从底层查。我的查法是从事实表明细开始看抽查当天的订单事实数据跟业务系统对账看business_orders里的订单有没有丢、有没有多、有没有字段错位。确认明细无误后再对汇总表看是不是聚合逻辑的问题。比如SUM和COUNT混用、DISTINCT加错位置、join导致的行数膨胀。行数膨胀是个经典bug——一张事实表join多张维度表如果维度表里某个user_id重复了订单数会被放大好几倍。排查join膨胀的土办法是先单独count事实表行数再join维度表后count一次两个数字对不上基本就是维度表有重复键。5.3 第三步重点排查增量抽取的边界条件增量抽取最容易出问题的地方是时间边界。create_time 20240520 00:00:00和create_time 20240521 00:00:00看起来没问题但如果业务库的create_time带毫秒、时区不对边界数据就会漏抽或重抽。我的建议是每次增量任务跑完后输出一个affect_rows校验指标连续观察几天看行数波动是否符合业务量级。如果某天行数突然异常优先检查是不是抽取任务重复执行了——幂等没做好数据就会翻倍。5.4 第四步明细和汇总的差异查时间字段用的是哪一个最后一步也是最隐蔽的一步同一个订单你用的是下单时间、支付时间、还是发货时间事实表里三个时间都在但汇总表如果有的SQL用支付时间、有的用下单时间同一天的GMV就会差出好几个小时。我在建表规范里会明确要求汇总表的每个指标必须注明使用的时间字段。6. 几个让我少吃哑巴亏的实操习惯最后说几个我这些年沉淀下来的习惯它们不写在任何建模文档里但每次都能帮我省下大把时间。第一维度表数据量不大直接全量重刷别搞增量。用户表几百万行商品表几十万行每天全量覆盖也就几十秒的事比增量逻辑SCD维护省心太多。只有上了千万行以上才值得去设计增量策略。第二建模文档要写字段血缘和指标口径不写表结构。表结构代码里都有文档最重要的是记录这个字段从哪个业务库哪张表来这个指标口径当时跟谁确认的。我见过太多数仓代码写得挺漂亮一查口径就抓瞎因为当初确认的人离职了。第三任何累计指标比如余额快照、库存快照先确认对象标识稳定再建模。如果一个商品ID被复用、一个账户ID发生过合并快照表的行数就会出错后续所有趋势分析都会歪。这类校验最好在ETL里做成质量规则每天跑数前先做COUNT和DISTINCT校验过不了就报警别让脏数据流入汇总层。第四事实表命名带粒度粒度定义写进注释第一行。这个习惯帮我解决了无数次这张表是干嘛的的疑问也让新来的同事能快速上手。一句话宁可在命名上多敲几个字符也别让后人拿着表名猜来猜去。这套方法论是我从一个又一个报表错数、一个又一个重复返工的项目里折腾出来的。事实表和维度表听着基础真要建模建得顺手、建得能扛住业务变化还是得在实战里摔打几回。希望这篇能让你少走几步弯路。