数仓维度建模入门：事实表、维度表、粒度和SCD一次讲清

做数据这一行带新人最头疼的一件事就是刚讲完“我们这个数仓是按维度建模设计的”对方立刻被迎面砸来的满屏术语打懵——事实表、维度表、粒度、度量、自然键、代理键、退化维度、缓慢变化维、星型模型、雪花模型……每个词单独读都认识连起来就是说人话。我自己当年也是这么被砸过来的后来做了几年数仓才慢慢发现维度数据建模里的这些概念和术语本质上是把“业务是怎么发生的”和“分析要怎么看”翻译成表结构的一套方法论。术语不是拿来背的每一个背后都有具体的业务考量。这篇文章就把这块最核心的概念和术语拆开了讲清楚适合刚接触数仓开发、BI分析、数据中台的同学也适合那些和数仓对接、想搞懂上游到底在做什么的开发。1. 维度建模在解决什么问题从一张购物小票说起1.1 为什么看起来正常的业务表不够用先别急着背定义回到源头想一件事业务系统的表为什么不适合直接拿来做分析。业务系统比如订单系统它的表是按“业务操作”来设计的。一张订单表、一张订单明细表、一张客户表、一张商品表为了尽量减少数据重复通常会按第三范式拆分得很细。客户基本信息存一份商品信息存一份订单只存客户ID和商品ID。这样设计的好处是下单时更新快、不容易出错这是OLTP在线事务处理的思路。但分析的场景完全反过来。分析师想问的是“上个季度华东区女装品类卖了多少”这个问题的答案散落在七八张表里订单表、明细表、商品表、品类表、门店表、地区表、时间维度……每次要现查现关联数据量大一点查询就慢得让人崩溃而且不同团队对“华东区”“上个季度”的定义还不一定一致。这就是OLAP在线分析处理和OLTP最大的矛盾OLTP为写入优化一张表一个职责OLAP为读取优化希望所有和某个分析过程相关的信息都能一次性拿到。维度建模就是在这个矛盾里被提出来的一套解法。它的核心思路不是把表拆得更干净而是从“业务过程”出发围绕要分析的事件来组织数据。所以你会看到维度建模的表往往有意识地做一定“冗余”在数仓里这叫反范式设计。很多人第一次看到维度表会问这不是把数据重复存了吗对就是重复存因为分析场景下读取速度和易用性比存储效率重要得多。1.2 “事实加维度”的两层视角是怎么来的维度建模最基本的视角可以拿一张超市购物小票来理解。小票上有两部分信息。一部分是“发生了什么”比如买了三件商品、合计消费128.5元、实付118元这是业务过程本身产生的数字是可以用金额、数量来描述的。另一部分是“这笔业务发生的背景”比如在哪个门店买的、哪天几点买的、是哪个会员、收银员是谁这些内容描述了业务事件的场景和上下文。前者在维度建模里叫事实Fact后者叫维度Dimension。一张小票天然就是这个结构中间是消费动作四周是各种描述消费环境的标签。维度建模说白了就是把这种人和业务都天然熟悉的视角落成数仓里的表结构。事实表记录“发生了什么”维度表记录“这件事发生在什么样的上下文里”分析的时候通过维度表的标签去过滤、分组再对事实表的数字做聚合就得到了业务上要看的指标。这套视角最大的好处在于业务人员理解起来完全没有障碍。你说“把订单事实表和客户维度表关联一下按客户等级统计销售额”这句话哪怕没有技术背景的人也能大概猜出意思。后面所有术语都是围绕这个简单的“两层视角”延伸出来的。2. 事实表数仓里“记流水账”的那张表2.1 事实Fact到底指什么什么东西不是事实事实表是维度建模里最核心的那张表。它记录的是业务过程中的一个事件、一笔交易、一次行为通常包含两类东西外键和度量值。外键用来关联维度表比如日期键、商品键、门店键、客户键度量值就是业务事件产生的可以量化的数值比如销售金额、销售数量、优惠金额、运费。每一行一般对应一个业务动作。订单事实表一行就是一个订单或者一个订单明细访问日志事实表一行就是一次访问。有一个比较容易踩的坑是不是数值列都算事实不是。判断标准是看它是不是业务动作直接产生的、能用来聚合计算的数值。比如“客户评分”这种数字量化的是客户这个维度本身的属性不是一笔交易产生的严格来说它更应该放在客户维度表里。再比如订单里的“商品单价”从事实表的角度它如果是商品维度里的一个属性就不应该在事实表里单独作为可加度量否则你按订单求和单价会得到一个毫无业务意义的数。这个区分一开始可能觉得无所谓等到做汇总报表发现数字对不上再回头排查就很痛苦。2.2 粒度Grain先定“一行代表什么”再谈建模讲事实表必须先讲粒度因为粒度是所有事实表设计里最先定、也是最不能含糊的一个概念。粒度就是事实表里一行数据代表什么。同样是销售事实表可以有三种不同粒度一行代表一个订单、一行代表一张订单里的一个商品行、一行代表某个商品在某一天内的销售汇总。这三种粒度代表了不同的分析精细程度。粒度越细能回答的问题越具体数据量也越大粒度越粗存储和处理越省但能回答的问题越有限。我自己带项目时最常遇到的问题是事实表的粒度没有定清楚就开始写数写到一半发现需求又要看明细又要看汇总两张表逻辑还矛盾。所以第一个原则是粒度必须在设计阶段就写进文档里而且要写得让业务都看得懂。比如“本表粒度为订单行级即同一订单号下每个SKU一行”这句话写清楚后面所有开发、验证、评审都围绕它来。粒度还会直接影响你能不能用这张表做下钻。如果一张销售事实表粒度已经是“按天按门店汇总”那么业务问“某个门店下午两点到三点的销售情况”这张表就答不了除非重新从明细级加载数据。反过来如果你存的是明细粒度那任何时候做上卷汇总都很容易。这就是为什么数仓建设中明细层事实表的设计尤其重要——它是所有上层指标的底板。2.3 度量Measure与可加性能不能直接SUM是关键事实表里的数值列术语上叫度量Measure。但度量之间有一个很重要却常被忽略的区别可加性。可加度量在所有维度上都可以直接相加。销售金额、销售数量就是典型不管按日期加、按门店加还是按商品加都是有意义的。半可加度量只在部分维度上可以相加。最典型的是账户余额、库存量。把今天所有账户的余额加起来是“总余额”有意义但把一个月每天的总余额加起来那是什么没有业务含义。所以余额这类度量在时间维度上不可加在其他维度上可以加。不可加度量在任何维度上直接相加都没有意义。最常见的是比率类指标比如利润率、转化率。三个订单的利润率分别是10%、20%、30%加起来是60%这个数字什么也说明不了。正确处理方式是先求分子分母利润总和、销售收入总和再相除。这个分类直接影响你建汇总表时的计算方式。很多报表算错不是SQL写错而是拿了一个半可加或不可加度量直接SUM。划个重点拿到任何一张事实表先看它的度量列各属于哪一类再决定怎么聚合。在定义事实表字段时我习惯在字段注释里直接标注“可加”“半可加时间不可加”“不可加”这个习惯能让后面接手的人少踩很多坑。2.4 退化维度藏在事实表里的“临时演员”还有一个事实表相关但很容易被忽略的术语叫退化维度Degenerate Dimension。它的场景很典型订单表里有个订单号这个订单号是业务系统里真实存在的编码也是分析时经常会用到的一个过滤或标记条件。但如果为订单号单独做一张维度表你会发现这张表除了订单号本身几乎没有什么别的属性可放。为它单独建维度表既浪费空间关联查询时也没有意义反而多了一次没必要的JOIN。所以处理方式是把订单号直接留在事实表里作为一个普通维度列。它不关联任何维度表用途就是让用户能按订单号去定位、筛选或去重。这种直接存在于事实表中、不单独建维度表的维度就是退化维度。类似的还有发票号、物流单号、流水号等等。这算是一个很小但很实用的概念。新人刚接触数仓时经常疑惑“事实表里怎么还混着编码字段”其实就是这个原因。3. 维度表业务视角的“字典”也是分析的入口3.1 维度属性分析时用来“筛”和“标签”的文字信息维度表回答的是“这件事发生在什么样的背景里”。它存放的是围绕事实的上下文描述信息这些描述字段在维度建模里叫维度属性Dimension Attribute。比如客户维度表的属性可能有客户姓名、注册日期、客户等级、所属城市、首次渠道来源。商品维度表的属性可能有商品名称、品牌、一级类目、二级类目、上架日期、供应商。时间维度表的属性有年、季度、月、周、日、是否节假日。这些属性最大的价值不是用来计算而是用来筛数据、做分组标签。分析报表里那些“按品牌看销量”“按客户等级看复购率”“按月份看趋势”本质上都是拿维度属性做分组或过滤条件。所以维度表中的属性质量直接决定分析的自由度。同一个业务事实如果维度属性越丰富、越完整能切出的分析视角就越多。这也是为什么老数仓都会强调维度表的建设是重中之重——事实表决定你能算哪些指标维度表决定你能从哪些角度看这些指标。在实际建模中有一个经验维度属性的来源通常不只在业务主数据里也可能在业务流程中的多个环节。比如“客户等级”可能不是客户表里的而是营销系统根据消费行为算出来的。设计维度表时要把这些散落的属性尽可能收拢进来而不是用到哪个再去关联哪个。3.2 自然键与代理键最值得掰扯清楚的一组术语维度表里有两个键的概念一个叫自然键Natural Key一个叫代理键Surrogate Key这是新手最容易搞混、也最容易在设计上吃亏的地方。自然键是业务系统里本来就存在的、用来代表实体的编码。比如商品的条码、客户的手机号、门店的门店编号。这些键在业务系统里是主键但在数仓维度表里我不建议直接拿它当主键用。原因有几个第一自然键可能发生变化。客户手机号可能换商品条码在商品重组后可能改。业务系统会允许这个改动但如果维度表主键用的是它那事实表里存的旧编码就没法正确关联回去了。 第二自然键的格式五花八门可能包含字母、短横线、前导零作为主键在关联性能上不占优势。 第三多个业务系统的自然键可能冲突。你从CRM系统拿客户ID从ERP系统也拿客户ID两边都从1开始编直接拿自然键当维度表主键数据一合并就串了。所以维度建模的常规做法是在数仓内部为维度表生成一套独立的主键通常是从1自增的整型与业务系统完全无关。它就叫代理键Surrogate Key。事实表里存的维度外键用的也是这个代理键。这样做的好处立刻就能看到业务系统的自然键变了我们只需要在维度表里维护一行记录事实表完全不用动因为事实表关联的是稳定的代理键。真实场景里商品重新编码、客户更换手机号是很常见的事没有代理键数据修复会让你怀疑人生。代理键是维度建模里一个很小但很值钱的设计。3.3 维度层次、下钻与上卷换个分组字段而已维度表里还有一个分析中经常出现、但概念上容易含糊的术语维度层次Hierarchy。维度层次指维度属性之间存在一个从粗到细的层级关系。最典型的是地理层次国家、省份、城市时间层次年份、季度、月份、日期组织层次公司、事业部、部门。维度层次背后是两个分析动作下钻Drill-down和上卷Roll-up。下钻就是从粗粒度往细粒度看比如从“全国销售”看到“各省销售”再到“各市销售”上卷反过来从细粒度往粗粒度汇总。虽然叫法很高级但落到SQL上其实就是换个分组字段从按年份分组改成按月份分组。之所以单独起名字是因为在多维分析工具里这是两个高频的交互动作约定俗成了。做维度表的时候要特别注意将这种层级关系建模成字段。比如城市维度表里可以既保留城市ID也保留城市所属省份的字段这样上卷下钻都只需要改GROUP BY字段就行。有些理论会建议把层级关系单独建模成一张父子表但对绝大多数场景把层级字段冗余进维度表里就够用了分析速度还更快。这也是维度建模“面向查询优化”理念的体现。4. 维度会变化于是有了缓慢变化维SCD这一族术语4.1 事实不变、维度会变这是维度建模的核心矛盾有一件事业务系统不会主动提醒你但它一直在发生订单一旦生成它的事实基本就固定了但订单关联的维度属性会不断变化。比如说一位客户昨天还是普通会员今天升级成金卡会员。那么上周他下的那笔订单算普通会员订单还是金卡会员订单再比如说某个商品调整了类目归属从“男装”挪到了“运动服饰”那上个月该商品的销售数据在“男装”报表里还该不该出现这个问题的本质是维度表里的属性是会发生变化的。而在分析中我们经常需要知道“订单发生那一刻”的属性是什么而不是“现在”的属性是什么。数仓里处理这种维度属性随时间变化的一整套策略就叫缓慢变化维Slowly Changing Dimension缩写SCD。为什么叫“缓慢”因为这类变化不像事实表那样频繁到每秒都在发生而是偶尔变一下但确实会变。4.2 三种SCD策略覆盖、加行、加列处理维度属性变化业界总结出了几种经典策略分别叫Type 1、Type 2、Type 3。Type 1覆盖/直接更新当维度属性变化时直接把旧值改成新值。优点是实现简单缺点是历史被覆盖掉之前所有的历史分组都会按新值算。这种策略适合处理只是“纠错”类的变化比如发现客户性别录错了直接改掉就好。Type 2新增一行当属性变化时在维度表里新增一行新的维度记录并用有效的起止时间去标记这一行记录的生效范围。旧的那行保留不动事实表按当时的代理键关联到对应那一行。这种策略能完美保留历史状态也是数仓建模里最常用的方案。缺点是维度表会越来越大同一实体可能有多行记录。Type 3新增一列在维度表里增加一个列来保存变化前的原值。这样一行里同时能看到“当前值”和“原值”。它只能保留有限的变化历史适合那种变化次数很少、且需要对比“变化前/变化后”的场景比如客户改过一次姓名需要做前后对比分析。我把三种策略放一起对比下策略实现方式历史保留适用场景Type 1更新字段值不保留数据纠错、无回溯需求的属性Type 2新增记录并标记有效期完整保留需要追溯历史状态的核心维度Type 3新增字段存原值仅保留一版变化次数少、需前后对比实际项目中最常见的做法是客户、商品这类核心维度用Type 2一些低价值辅助属性用Type 1尽量少用Type 3因为它的扩展性差。4.3 Type 2怎么落地代理键加有效时间的组合Type 2是重点我展开说下落地方式。要支撑Type 2维度表通常需要三样东西代理键、有效时间、当前标志。代理键是数仓唯一主键有效时间包括生效日期start_date和失效日期end_date标识这一行记录的适用时间段当前标志is_current标识该实体当前生效的那一行。当维度属性变化时把当前行的end_date更新为变化当天再插入一行新记录start_date设为变化当天end_date设为9999-12-31或某个极大日期is_current置为1。查询历史状态时用事实表的业务日期去匹配维度的起止时间查询当前状态时直接用is_current1过滤。这套逻辑不难但有两个坑要提醒。第一个坑是日期边界。生效和失效日期的口径如果是一个闭区间、一个开区间很容易在边界日算出两条记录。我见过很多次因为区间定义不清导致事实表关联维表时出现重复行。建议统一用“左闭右开”区间即start_date 业务日期 end_date并且写清楚。第二个坑是多个属性同时变化时的处理粒度。如果客户同时换了手机号和地址是算一次变化还是两次变化如果生成两条维度记录会导致事实表按同一业务日期关联到两条历史记录。我的经验是以“业务上是否视为一次变更事件”为准一次变更事件只生成一条新记录。这件事需要和业务确认不能拍脑袋。5. 事实表与维度表的三种组合形态星型、雪花、星座5.1 为什么不能把事实表和维度表“压”成一大张表理解了事实表和维度表各自是什么下一个自然的问题就是它们怎么拼起来用有人会想既然维度表给事实表提供描述信息那能不能直接把维度属性全部冗余到事实表里做成一个大宽表这样查起来连JOIN都不用。确实有人这么做但会产生一个严重问题事实表一行就是一个业务事件如果把商品名、品牌、类目、供应商、门店城市、门店经理这些维度属性全部塞进去每个维度属性一变化事实表里的存量行全是错的。所以事实表和维度表必须分开存通过外键关联。至于怎么组织它们的关系业界沉淀出了三种经典形态星型模型、雪花模型、星座模型。这三者是维度建模里讨论最多的“组合形态”术语。5.2 星型模型一张事实表直接挂多张维度表星型模型是维度建模最经典、也是我个人最推荐的默认形态。它长这样中间一张事实表周围辐射状挂若干张维度表每张维度表都直接和事实表关联不再往下衍生。为什么叫星型因为把这种结构画出来事实表在中间、维度表向四周散开像一个星星。星型模型的关键特征是维度表是“扁平”的对应某个维度的所有属性都放在同一张表里不再做拆分。它的思路和查SQL的体验完全一致——查询路径最短从事实表出发JOIN一次就能拿到需要的维度描述没有“JOIN维度表的维度表”这种中间层。这套设计在分析性能和易用性上都很友好。分析师写“按品牌统计销量”就是从事实表JOIN商品维度表然后按品牌字段分组一条SQL搞定。而且数仓底层在JOIN单张维度表时因为只涉及一次关联执行计划的优化空间也大。如果你刚开始设计数仓我建议无脑用星型模型起步它能覆盖绝大多数BI报表场景。5.3 雪花模型维度表再拆一层查询路径变长雪花模型和星型模型的区别在于维度表是不是也被规范化拆分了。举个例子星型模型里一张地区维度表可能直接包含“省、市、区县”三个字段雪花模型则可能拆成三张表区县表、市表、省表区县表里存市ID市表里存省ID。从图形上看维度表继续往下分叉像雪花的结晶所以叫雪花模型。雪花模型的优点很明确减少了数据冗余维度表存储空间更小并且如果维度层级属性经常变化比如行政区划调整只需要改层级表不需要改所有维度记录。但缺点也很明显分析查询时每次访问维度信息都要多JOIN一层甚至几层查询路径变长SQL复杂度上升性能也会受一定影响。在列式存储和分布式计算普及的今天存储成本已经远没有查询易用性重要了所以雪花模型在传统BI数仓里用得越来越少。什么场景适合用雪花模型如果某个维度非常庞大、属性层级非常稳定而且业务分析对维度层级本身有很强的查询需求比如做非常复杂的组织架构分析可以考虑。否则优先保留星型。5.4 星座模型与一致性维度多张事实表共享一套“字典”把视角从一张事实表放大到整个数仓你会发现企业里不只有一个业务过程。销售有销售事实表库存有库存事实表物流有物流事实表。这些不同的业务过程有不少维度是相同的比如时间维度、商品维度、门店维度。这就引出了星座模型也叫事实星座模型多张事实表共享同一套维度表的形态。画出来像多个星星连在一个星座里。星座模型不是一种另起炉灶的设计而是企业级维度建模的自然形态——一张商品维度表被销售事实表、库存事实表共用。星座模型背后有两个术语很关键一致性维度和总线架构。一致性维度指在多张事实表中同一个维度的内容、字段口径、颗粒度都要保持一致。为什么重要因为只有共享了同一条商品维度表销售报表和库存报表里“商品维度的统计口径”才可能对齐。如果两个表各自建了语义不一致的商品维度那交叉分析时就会得出互相矛盾的结论。总线架构则是一种更高层的组织模式它指先在企业里统一规划好这套共享维度再围绕着它们建设各业务过程的事实表而不是每个团队各做各的。之所以叫“总线”是因为这套共享维度像总线一样贯穿并连通了所有的业务过程。6. 想把这些术语真正变成自己的抓住一条线就够了概念拆完了最后给你一条我自己带新人时一直在用的理解路径。不要从头到尾去背术语定义而是抓一条业务线串起来先找一张业务单据比如订单顺着它画出事实和维度再想清楚粒度然后试着设计物理表结构跑两个指标最后模拟一次维度属性变更用SCD策略去处理。这一套流程走下来前面所有术语基本都能落到你自己的项目里。拿一个真实电商订单来练手几天就能建立比看十篇概念文章更扎实的体感。另外送一个个人习惯建每一张事实表时写清楚粒度建每一张维度表时明确主键是代理键在面对所有会变的维度属性时先问业务一句“这个变化需要追溯历史吗”。把这三件事问完90%的表结构问题都不会走偏。