★ FEATURED ARTICLE 1. 先把这两个模型的样子刻在脑子里做数据开发或BI的同行多少都有过这种经历接到一个报表需求业务方想看订单按商品分类、按地域、按月份的销售情况。打开源系统一看好家伙商品分类分成三级地域分省市区客户信息分布在好几张表里。这时候到底是把该拆的拆开、做成雪花模型还是干脆全塞到一张宽表里做个星型模型就成了第一个决定后续所有工作量的岔路口。星型模型和雪花模型简单说就是数据仓库里两种最常见的维度建模组织方式。星型模型长什么样最中间一张事实表周围围着一圈维度表事实表通过外键关联各张维度表从结构上看像一颗星星的四角辐射所以叫星型。事实表里放的是业务过程的度量值比如订单金额、销售数量、成交笔数维度表里放的是描述环境的属性比如商品名称、商品分类、城市名称、买家等级。维度表和事实表之间是一对多关系一张订单事实可以对应一个商品维度、一个买家维度、一个日期维度。雪花模型则在星型模型的基础之上把维度表再做了规范化拆分。比如商品维度表里原来有“商品一级分类”“商品二级分类”“商品三级分类”雪花模型就把这三个层级单独拆成一张分类维度表再由商品维度表通过外键去关联分类表。层级越多、拆分越细表结构就越像一片雪花的分支一层层向外延展所以叫雪花模型。这两个模型解决的是同一个问题如何把面向业务操作、通常高度范式化的源系统数据重新组织成面向分析、方便报表查询的模型结构。它们之间的差异不是谁对谁错而是不同业务场景下的一体两面。如果你是刚接触数仓建模的人先把这两个结构的图示看明白对后续理解事实表粒度、维度退化、维度一致性这些概念会顺畅很多。2. 选型背后的考量性能、维护成本与使用场景2.1 查询性能JOIN少就是快但不绝对星型模型最大的优势在于查询性能。因为它只有一层维度表所有维度的属性都冗余在各自维度表里。分析人员写SQL时JOIN一张事实表加一张商品维度表按商品分类筛选或分组只需要一次JOIN就完成。维度表字段都是宽表结构该冗余的属性都冗余好了查询引擎不需要在运行时再去多级JOIN拆出的层级子表。雪花模型则相反。因为维度被进一步拆成多层分析时要从事实表出发先JOIN商品维度表再JOIN分类维度表再JOIN一级分类的上级分类维度表。JOIN链条一长查询计划会变得复杂中间结果集也会膨胀。对传统关系型数据库来说这种多级JOIN确实会拖慢查询速度。数据量一旦上去几千万行的事实表带着分级维度一圈圈JOIN响应时间很容易从秒级变成十几秒甚至分钟级。不过这里有个时代变化要说清楚现在很多OLAP引擎对星型模型做了高度优化同时也有引擎对雪花模型做了自动折叠处理。也就是说你建表时可以按雪花模型规范化去建但引擎在查询层面会提前把层级关联物化好或者自动生成更优的JOIN计划。所以性能差距在新型MPP数据库和列存引擎里没有想象中那么大。但在传统数仓和BI工具直连数据库的场景下星型模型依然更稳妥。2.2 维护成本冗余与规范化的拉锯星型模型的劣势正好是雪花模型的优势。维度表里冗余了所有属性一旦源系统里商品分类发生变化比如原来三级分类“手机配件”归到“数码”下面现在调整成归到“智能设备”下面星型模型下你需要直接更新商品维度表里的分类字段。如果维度表里还冗余了分类的上级分类名那就要一起改。冗余的层级越多更新时覆盖的字段就越多改错一个字段就可能造成上下游报表数据不一致。雪花模型把分类拆成独立表商品的分类归属变化只需要在主数据表里维护分类与商品的映射关系分类表本身的名称和层级结构独立管理。分类调整时商品表里维护的分类ID发生变化分类名称自动跟着变。用主数据治理的视角看雪花模型更贴近“单一数据源”的理念分类名称这种描述性属性只存在一张表里不会出现商品维度表和分类维度表里名称对不上的情况。2.3 使用场景根据自己的实际业务来定从我经手的项目看星型模型更适合那些分析入口固定、维度丰富度适中、查询要求快速响应的场景。比如销售明细分析、流量日志分析事实表关联几个关键维度就够用业务方也不太需要频繁调整维度的层级归属。这类场景下用星型模型把冗余字段直接铺开能换来更快的查询和更简单的SQL。雪花模型则更适合维度层级非常清晰、且层级关系经常调整的场景。比如组织架构分析、商品类目运营、财务科目体系。这类维度往往有很强的树形结构而且层级变动是常态。用雪花模型把层级独立建模维护层级关系比在宽表里持续更新冗余字段要安全得多。还有一个关键场景我会建议直接用雪花模型数据从第三方或者多个源系统接入时源数据本身就是多张规范化表维护的如果硬要在数仓层做一张星型宽维度表ETL需要做大量关联合并和去重反而增加出错风险。这种情况下顺着源系统的规范化结构做雪花拆分再在应用层做宽表加速是更务实的做法。3. 建模细节事实表与维度表怎么设计才不踩坑3.1 事实表先把粒度定死再说任何事实表设计的第一步都是定粒度。粒度就是事实表里每一行代表什么。同样是订单数据按订单头粒度一行就是一个订单的主信息按订单行粒度订单明细一行是订单中的一个商品同一个订单有多行关联多个商品维度。粒度决定了事实表里度量字段的含义也决定了后续所有查询能做和不能做的事情。定了粒度之后事实表里应只放与粒度强相关的度量字段。订单行粒度的表放商品数量、行金额、折扣金额订单头粒度的表放订单总金额、运费、支付金额。把不同粒度的度量混在一张表里是新手最容易犯的错误比如在订单行粒度的事实表里直接加一个“订单总金额”字段汇总时把每个商品行的总金额再累加一遍数据就会翻倍失真。事实表的外键设计也要注意。每个维度外键应当和事实粒度形成对应关联如果是订单行粒度商品维度外键、日期维度外键、买家维度外键都是必要的地址维度如果按收件地址拆省市区建议做成三个独立外键而不是塞一个组合键。3.2 维度表代理键、自然键与属性冗余维度表的核心要素有几个代理键、自然键、描述属性、层级字段。代理键是数仓内部的替代键通常用自增ID或哈希ID与业务系统主键无关。自然键是源系统的业务主键比如商品编码、类目编码。为什么非要区分这两个键因为业务主键可能会被复用或者修改比如商品编码在源系统里被删了之后又新建单靠自然键关联事实表会出现串数据的问题。代理键由数仓统一管理自然键变化时代理键不变历史事实能稳定关联。描述属性是维度表里最容易被忽视的部分。分析人员经常需要按各种属性切片属性能给多少就尽量给多少。品牌、产地、保质期这些看起来和分析不直接相关的字段都可能在后续做经营分析时被用上。我一般建议至少把源系统里维度表的所有静态属性都纳入再根据报表需求补充衍生属性。3.3 层级维度星型和雪花的本质分歧点维度属性里最常见的两类是“扁平属性”和“层级属性”。扁平属性比如商品的颜色、产地直接挂在商品维度上是星型做法。层级属性比如商品分类的一级、二级、三级处理方式就成了星型和雪花的分界线。星型做法的思路是“冗余层级”把商品一级分类、二级分类、三级分类都作为普通字段挂在商品维度表里。查询时直接按“一级分类”字段过滤或者分组SQL简洁高效。缺点前面也提到过分类层级调整时更新商品维度表的字段数量多容易遗漏。雪花做法的思路是“拆层级关系”单独建商品分类维度表里面包含分类ID、分类名称、上级分类ID商品维度表只保留商品关联的分类ID。查询商品一级分类时需要向上递归或多层JOIN找到根节点。层级关系清晰维护方便但查询路径变长。有没有折中方案有。很多团队会做“层级冗余表”既有商品维度表冗余了常用的一级分类字段也有完整的分类层级维度表两表并存各取所需。这个要看你所在平台的存储和计算成本能不能接受。3.4 命名规范与字段类型能省以后很多麻烦建模时命名规范最好从第一天就定好。事实表建议用“f_业务过程名”前缀比如f_order_detail、f_payment。维度表建议用“d_维度名”前缀比如d_product、d_customer、d_date。字段类型方面度量字段统一用DECIMAL(18,2)或BIGINT金额别用FLOAT浮点精度问题在累计汇总时会暴露得很明显。维度表的代理键用BIGINT不要用INT数据量上来INT很容易溢出。日期字段统一用DATE类型不要混用字符串和DATE。4. 完整实操从需求到落地建一张星型模型4.1 需求分析与业务过程梳理假设手头有一个电商订单分析需求。业务方原始诉求是看每天的销售额、订单量、买家数支持按商品分类、按收货省份、按日期维度钻取。我第一步会梳理业务过程。电商业务过程有很多下单、支付、发货、签收、售后。这次分析核心是“下单”过程所以事实表的主题要围绕下单业务展开。你不可能把支付状态的度量也硬塞进来那是另一个业务过程的事。把业务过程理清之后再确定事实表的度量列销售额订单行金额、订单量可用行数计数、买家数依赖买家维度的去重计数。接着梳理维度。商品维度的自然键是商品编码属性有商品名称、一级分类、二级分类、三级分类、品牌、上下架状态买家维度的自然键是买家ID属性有注册城市、会员等级、注册时长日期维度主要是日历属性地域维度的自然键是省市区编码属性有省、市、区名称。4.2 星型模型的表结构落地我先按星型模型的方式给出表结构。事实表命名f_order_detail粒度定为订单行粒度核心字段如下CREATE TABLE f_order_detail ( order_id BIGINT COMMENT 订单号, product_key BIGINT COMMENT 商品代理键, customer_key BIGINT COMMENT 买家代理键, date_key INT COMMENT 下单日期代理键, region_key BIGINT COMMENT 收货地区代理键, quantity INT COMMENT 商品数量, line_amount DECIMAL(18,2) COMMENT 行金额, discount_amount DECIMAL(18,2) COMMENT 优惠金额, pay_amount DECIMAL(18,2) COMMENT 实付金额 )商品维度表d_product按星型模型冗余分类层级CREATE TABLE d_product ( product_key BIGINT COMMENT 代理键, product_code VARCHAR(32) COMMENT 自然键商品编码, product_name VARCHAR(128) COMMENT 商品名称, brand VARCHAR(64) COMMENT 品牌, category_l1 VARCHAR(64) COMMENT 一级分类, category_l2 VARCHAR(64) COMMENT 二级分类, category_l3 VARCHAR(64) COMMENT 三级分类 )日期维度表d_date、地域维度表d_region、买家维度表d_customer结构都不复杂关键是日期维度把年、季度、月、周、工作日/节假日都做成字段方便业务方直接用“月”字段分组而不是每次从日期里截取字符串。4.3 拆分维度从星型到雪花的演化路径现在假设业务方提出一个新需求商品分类需要频繁调整而且源系统维护了一张独立的category层级表希望分类管理走主数据流程。这时候再把一级分类、二级分类、三级分类冗余在d_product表里就不合适了分类调整时更新面太大。我们把分类维度拆出来商品维度表改造成雪花结构CREATE TABLE d_category ( category_id BIGINT COMMENT 分类代理键, category_code VARCHAR(32) COMMENT 分类编码, category_name VARCHAR(128) COMMENT 分类名称, parent_category_id BIGINT COMMENT 上级分类代理键顶级为NULL, category_level INT COMMENT 层级 ) CREATE TABLE d_product ( product_key BIGINT, product_code VARCHAR(32), product_name VARCHAR(128), brand VARCHAR(64), category_id BIGINT COMMENT 关联分类表 )这样改造后d_product与d_category之间是一对多关系d_category内部通过parent_category_id自关联形成层级。查询时若要按一级分类汇总需要从商品关联到分类表再找到该分类的根节点。为了降低查询复杂度很多实践会额外在d_category里增加一级分类名、二级分类名冗余字段本质上是在雪花模型里做一个局部星型化。这也是很多成熟数仓团队实际在用的“雪花骨架、星型血肉”方案。4.4 查询示例与参数选择逻辑在星型模型下按月统计每个一级分类的销售额和订单量的SQL是SELECT d.category_l1, d_date.month, SUM(f.line_amount) AS sales_amount, COUNT(DISTINCT f.order_id) AS order_cnt FROM f_order_detail f JOIN d_product d ON f.product_key d.product_key JOIN d_date ON f.date_key d_date.date_key GROUP BY d.category_l1, d_date.month;这个SQL里只能JOIN维度表一次查询引擎的执行计划很清晰。如果换成雪花模型就需要额外JOIN d_category而d_category内部还要递归到一级分类SELECT c1.category_name AS category_l1, d_date.month, SUM(f.line_amount) AS sales_amount, COUNT(DISTINCT f.order_id) AS order_cnt FROM f_order_detail f JOIN d_product dp ON f.product_key dp.product_key JOIN d_category c3 ON dp.category_id c3.category_id JOIN d_category c2 ON c3.parent_category_id c2.category_id JOIN d_category c1 ON c2.parent_category_id c1.category_id JOIN d_date ON f.date_key d_date.date_key GROUP BY c1.category_name, d_date.month;JOIN层数一下就上去了。这里的关键决策点是这个SQL是每天跑上百次的报表还是偶尔跑一次的分析前者我百分百建议用星型模型或者至少把一级分类冗余进d_product后者偶尔跑一次雪花模型可维护性的优势就值得保留。参数选择还有个细节折扣金额在事实表里保留明细值还是直接在ETL里算好净额我的习惯是保留原始行金额和折扣金额两个字段实付金额作为计算列或第三字段。为什么不只留实付金额因为业务方后续可能问“折扣贡献了多少销售额”没有原始字段就只能望洋兴叹。4.5 ETL填充代理键与自然键的映射代理键填充是建模落地的关键环节。ETL过程一般这样处理先抽取出源系统的订单明细关联商品业务主键在d_product表里找到对应的product_key找不到就插入新商品记录并生成代理键。日期维度、买家维度、地域维度同理。这个过程看着简单但并发拉数据、源主键修改、维度表里面重复记录这几种情况都会让代理键映射出错必须做好幂等处理。还有一点容易漏维度表一定要有“未知成员”记录比如product_key为-1的商品未知商品、date_key为19000101的日期未知日期事实表里出现无法关联的孤儿记录时固定指向未知成员比直接留空好得多。留空会导致事实表JOIN维度时直接丢行报表数据无故变少排查起来极其痛苦。5. 踩坑实录常见问题与排查思路速查表5.1 维度表主键重复导致事实行膨胀这是最隐蔽的坑。某次跑数订单量凭空多了一倍。排查到最后发现d_product表里同一个product_code出现了两条记录一条是原有的另一条是ETL重跑时因源系统“商品编码批次”被当成了新维度插入生成的新代理键。事实表里同一个order_id的部分行关联到旧product_key部分关联到新product_key分组JOIN时数据被拆成了两份。对策给自然键建立唯一约束ETL里写入前先做“自然键存在则更新不存在则插入”的合并逻辑而不是简单全量插新。5.2 雪花层级JOIN太深导致查询计划膨胀有一回在雪花模型下做组织架构分析层级做到了六层查询写出来要JOIN六次加上事实表本身数据量大跑一次要几分钟。后续优化方案不是把表结构推翻重做而是增加冗余列在层级表里预计算每个节点的“根路径一级名称”“二级名称”查询时只JOIN两层就能拿到完整路径。这也验证了纯雪花建模在真实场景里往往需要局部冗余来换取效率。5.3 缓慢变化维度的处理要提前定策略商品换分类、买家换等级这是维度属性更新的典型场景。如果用户想保留历史事实关联当时维度的状态就需要做SCD缓慢变化维度处理。常用策略类型二维度表新增一条记录新代理键对应新属性历史事实关联历史代理键新事实用新代理键。但类型二策略有个副作用维度表记录会膨胀。频繁变化的维度属性比如会员等级每个月变一次两年下来一张买家维度表记录数远远超过买家数。对这种高频变化属性建议拆出来单独建模或者降级处理别用一张宽维度表硬扛。5.4 粒度不一致的最典型翻车现场有同事做“买家数”统计直接COUNT(DISTINCT customer_key)这个没问题。但有人把事实表里同一个买家的多条订单行都算了一次再SUM一下买家数就变成订单线条数了。粒度意识是要反复强调的。建议在事实表建成后就写一套口径文档把每个度量字段的统计口径、是否去重、按什么粒度汇总写清楚贴到模型文档里。5.5 由雪花转星型的平滑操作如果建模初期选了雪花运行半年后发现性能确实撑不住转而想换成星型。不要试图删表重建。实际操作路径在维度表上增加冗余字段然后通过回填历史维度记录的ETL把字段补全最后调整视图层或BI层模型让查询走宽维度表。整个过程可以分批灰度先切一个核心报表验证跑通后再全量切换。我在实际项目里还遇到过一个问题BI口径和字典口径对不上可视化看板上写的是“按一级分类汇总”底下的SQL却用的是二级分类。这种问题靠模型本身无法解决只能靠模型文档、指标口径文档、代码评审三重保障。6. 关于模型演进我自己的一些体会做了这些年数据建模我最深的感受是星型模型和雪花模型从来不是非此即彼的二选一而是同一套维度建模思想在不同约束下的表现形式。数据量、查询频率、维度稳定性、团队维护能力、目标平台特性每一个变量都会影响最终选择。我个人的习惯是第一版尽量按业务过程把事实表和维度表拆干净维度属性该给的都给全层级关系先按星型做冗余存储等出现频繁的层级变更需求时再局部雪花化。这种“先星型后雪花”的路径前期查询快、报表上线快后期遇到维护痛点再精修业务方体验最好。最后再分享一个小技巧不管用什么模型定期检查ETL日志里有没有维度关联失败的告警。很多事实表数据质量问题最初都是从一行关联不上开始的。建模只是起点让模型在真实数据流里稳定运转才是真正考验功力的事。
阅读完成 · 觉得有帮助？
【中等】)
