📚 课程目录第3章维度建模基础与Snowflake实现

3. 维度建模基础与Snowflake实现

维度建模的核心任务，是把业务过程拆成两类对象：事实与维度。事实回答“发生了什么、发生了多少”，通常由可加度量（金额、数量、时长）和外键组成；维度回答“谁、什么、何时、何地、如何”，提供描述性上下文。在Snowflake中落地维度模型时，真正需要决策的不是“要不要建维度表”，而是维度该规范化到什么程度，以及这种选择如何影响查询性能与维护成本。

一、识别事实与维度：从业务过程出发

识别事实表的第一步是确定业务过程，而不是先看现有表结构。以电商订单为例，业务过程是“下单支付”，其粒度是“一行订单明细”。事实表 FACT_SALES 的每一行对应一个订单明细，度量包括 QUANTITY、UNIT_PRICE、DISCOUNT_AMT、NET_AMT，外键指向日期、商品、客户、门店、促销等维度。粒度一旦确定，所有度量必须与该粒度一致；若把订单头金额和明细数量放在同一行，就会产生重复计数。

维度的识别则围绕“描述性属性”展开。商品维度 DIM_PRODUCT 包含商品名称、品类、品牌、供应商；客户维度 DIM_CUSTOMER 包含姓名、城市、会员等级、注册渠道。判断一个属性属于事实还是维度，可以问三个问题：它是否可加？它是否用于分组筛选？它是否随时间频繁变化？可加且用于聚合的是度量；用于筛选、分组、展示的是维度属性。

在Snowflake中，维度表通常使用代理键（如 PRODUCT_SK）而非自然键作为事实表外键，这样可以在不改变事实表的前提下处理缓慢变化维度（SCD）。

二、星型模式与雪花模式的结构差异

星型模式把维度保持为反规范化的宽表：商品维度直接包含品类名称、品牌名称、供应商名称，不再拆出 DIM_CATEGORY、DIM_BRAND。事实表位于中心，维度表围绕四周，连接路径只有一层。雪花模式则把维度进一步规范化：DIM_PRODUCT 只保留 CATEGORY_ID，品类名称放在 DIM_CATEGORY，品牌放在 DIM_BRAND，查询时需要多跳 JOIN。

两种模式没有绝对优劣。星型模式 JOIN 少、SQL 简单、对 Snowflake 的列式扫描和微分区裁剪更友好；雪花模式节省存储、减少冗余更新，但查询路径变长，且在多维分析中容易引入额外 JOIN 开销。实际项目中常见的是“核心星型 + 局部雪花”：高频查询的维度保持反规范化，低频、层级深、更新频繁的维度适度规范化。

三、在Snowflake中创建规范化与反规范化表

下面给出同一商品维度的两种建表方式。反规范化版本把品类、品牌、供应商名称直接放入 DIM_PRODUCT：

CREATE OR REPLACE TABLE dim_product_denorm (
  product_sk      NUMBER AUTOINCREMENT,
  product_id      VARCHAR(32),
  product_name    VARCHAR(200),
  category_name   VARCHAR(100),
  brand_name      VARCHAR(100),
  supplier_name   VARCHAR(200),
  list_price      NUMBER(12,2),
  effective_from  DATE,
  effective_to    DATE,
  is_current      BOOLEAN
);

规范化版本则拆成三张表，通过外键关联：

CREATE OR REPLACE TABLE dim_category (
  category_id     NUMBER,
  category_name   VARCHAR(100),
  parent_category VARCHAR(100)
);

CREATE OR REPLACE TABLE dim_brand (
  brand_id        NUMBER,
  brand_name      VARCHAR(100)
);

CREATE OR REPLACE TABLE dim_product_norm (
  product_sk      NUMBER AUTOINCREMENT,
  product_id      VARCHAR(32),
  product_name    VARCHAR(200),
  category_id     NUMBER,
  brand_id        NUMBER,
  supplier_id     NUMBER,
  list_price      NUMBER(12,2)
);

事实表在两种模式下结构一致，区别只在维度外键指向：

CREATE OR REPLACE TABLE fact_sales (
  sales_sk        NUMBER AUTOINCREMENT,
  date_sk         NUMBER,
  product_sk      NUMBER,
  customer_sk     NUMBER,
  store_sk        NUMBER,
  quantity        NUMBER(10,2),
  unit_price      NUMBER(12,2),
  discount_amt    NUMBER(12,2),
  net_amt         NUMBER(14,2)
);

四、查询性能评估：用真实执行计划判断

在Snowflake中评估两种模式，不能只看“JOIN 数量”，而要看扫描字节数、分区裁剪比例、JOIN 溢出情况。以下查询统计“2024 年第一季度各品类净销售额”：

-- 星型（反规范化）
SELECT p.category_name, SUM(f.net_amt) AS net_sales
FROM fact_sales f
JOIN dim_product_denorm p ON f.product_sk = p.product_sk
JOIN dim_date d ON f.date_sk = d.date_sk
WHERE d.year = 2024 AND d.quarter = 1
GROUP BY p.category_name;

-- 雪花（规范化）
SELECT c.category_name, SUM(f.net_amt) AS net_sales
FROM fact_sales f
JOIN dim_product_norm p ON f.product_sk = p.product_sk
JOIN dim_category c ON p.category_id = c.category_id
JOIN dim_date d ON f.date_sk = d.date_sk
WHERE d.year = 2024 AND d.quarter = 1
GROUP BY c.category_name;

执行后应重点观察 Query Profile 中的三个指标：Bytes scanned（扫描字节数）、Partitions scanned / total（分区裁剪率）、Join 节点的溢出与重分布。在示例数据规模下（事实表约 5000 万行、商品维度约 20 万行），星型模式通常扫描更少字节，因为品类名称已在维度宽表中，无需额外 JOIN；雪花模式多一次 dim_category 关联，若该表未做聚簇或缓存，可能增加 10%～30% 的编译与执行时间。但若品类层级很深、更新频繁，雪花模式能显著降低维度表的更新冗余。

不要用单次查询的耗时下结论。Snowflake 的缓存、仓库规模和并发会显著影响结果。建议在相同仓库规格、相同数据量、关闭结果缓存（ALTER SESSION SET USE_CACHED_RESULT = FALSE;）的条件下对比，并至少重复 3 次取中位数。

五、选择建议与落地检查

实际建模时，可以按以下顺序决策：先确定事实粒度与度量，再列出所有维度属性；对高频查询路径上的维度优先反规范化；对层级深、更新频繁、被多个事实表共享的维度，保留规范化子表。Snowflake 的列式存储和自动微分区让宽维度表的存储成本并不高，因此“星型为主、雪花为辅”通常是更稳妥的起点。

判断维度	优先星型	优先雪花
查询频率	高频、面向 BI 看板	低频、探索式分析
维度层级	层级浅、属性少	层级深、多级分类
更新频率	缓慢变化、更新少	频繁更新、需减少冗余
共享程度	单事实表使用	多事实表共享同一维度
性能关注点	减少 JOIN、提升扫描效率	降低存储与更新成本

最后，无论选择哪种模式，都应在 Snowflake 中为事实表的外键列和维度表的主键列建立合适的聚簇或排序策略，并用 EXPLAIN 与实际 Query Profile 验证分区裁剪是否生效。维度建模不是一次性设计，而是随着查询模式和数据增长持续调整的过程。