05.10.1 维度建模
所属:全栈开发面试题库 / 05 数据库与存储 / 05.10 数仓建模与 ETL
覆盖范围
- 星型模型与雪花模型
- 事实表类型:事务、周期快照、累积快照
- 维度表与缓慢变化维 SCD
- 一致性维度与总线矩阵
- 范式建模 vs 维度建模
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.10.1-001 | 星型模型和雪花模型区别 | 基础 | ★★★★★ |
| 05.10.1-002 | 事实表有哪几种类型 | 进阶 | ★★★★ |
| 05.10.1-003 | 缓慢变化维 SCD 怎么处理 | 进阶 | ★★★★★ |
| 05.10.1-004 | 一致性维度是什么 | 进阶 | ★★★ |
| 05.10.1-005 | 范式建模和维度建模区别 | 基础 | ★★★ |
05.10.1-001 星型模型和雪花模型有什么区别?
频率 ★★★★★ | 难度 基础
一句话结论
星型模型维度表反范式化(一张大宽维度表),事实表直接关联维度,JOIN 少、查询快;雪花模型维度表进一步范式化拆分,存储省但 JOIN 多;生产数仓优先星型。
常见误区
- 误区:雪花更规范更好。正解:OLAP 查询星型 JOIN 少性能好,雪花是 OLTP 思维。
05.10.1-002 事实表有哪几种类型?
频率 ★★★★ | 难度 进阶
一句话结论
事务事实表(一笔业务一行,如订单)、周期快照事实表(按天/月对状态拍照,如每日库存)、累积快照事实表(业务全生命周期一行,如订单从下单到签收的各时间戳)。
常见误区
- 误区:所有事实表都存明细。正解:周期快照和累积快照是不同用途。
05.10.1-003 缓慢变化维 SCD 怎么处理?
频率 ★★★★★ | 难度 进阶
一句话结论
SCD 处理维度属性随时间变化:SCD1 直接覆盖(不保留历史)、SCD2 新增一行并加生效/失效时间与版本号(保留完整历史)、SCD3 保留当前和上一个值;生产一般 SCD2。
代码 / 示例
-- SCD2:客户维度
customer_id name city valid_from valid_to is_current
100 张三 北京 2023-01-01 2024-03-01 0
100 张三 上海 2024-03-01 9999-12-31 1
常见误区
- 误区:SCD2 用 update 改旧行。正解:新增一行,旧行 valid_to 设为失效时间。
- 误区:事实表关联维度用自然键。正解:用代理键(surrogate key)关联,SCD2 多版本时才能正确匹配当时的维度属性。
05.10.1-004 一致性维度是什么?
频率 ★★★ | 难度 进阶
一句话结论
一致性维度是跨业务过程复用的统一维度(如日期、客户、商品),保证不同报表按同一维度口径汇总;总线矩阵(Bus Matrix)定义一致性维度与各事实表的关系。
05.10.1-005 范式建模和维度建模有什么区别?
频率 ★★★ | 难度 基础
一句话结论
范式建模(3NF)面向数据集成,减少冗余、灵活,但查询需多表 JOIN;维度建模面向分析,星型/雪花,预聚合,查询快;数仓 ODS 贴源,DWD/DWS 维度建模。
05.10.1-006 事实表和维度表怎么区分?
频率 ★★★★ | 难度 基础
一句话结论
事实表存业务度量(数量、金额、次数),外键关联维度表,粒度细;维度表存描述性属性(客户名、商品类目、地区),相对宽;事实表通常大且长,维度表小且宽。
概念解释
- 事实表:一行一条业务事件,外键多,数值列多。
- 维度表:一行一个实体,描述列多。
常见误区
- 误区:事实表就是明细表。正解:周期快照和累积快照也是事实表,只是粒度不同。
05.10.1-007 代理键有什么用?
频率 ★★★ | 难度 进阶
一句话结论
代理键是无业务含义的自增 ID(如 dim_user_id),替代自然键关联事实表;SCD2 多版本时,代理键能让事实记录精确匹配当时的维度版本。
常见误区
- 误区:用业务主键关联就行。正解:SCD2 后自然键对应多行,必须用代理键匹配历史版本。
05.10.1-008 退化维度是什么?
频率 ★★★ | 难度 进阶
一句话结论
退化维度是没有维度表、直接放在事实表中的维度键(如订单号),用于分组和下钻,不需要单独建维度表。
05.10.1-009 多值维度怎么处理?
频率 ★★ | 难度 高级
一句话结论
一个事实对应多个维度值(如一个商品多个标签),用桥接表(bridge table)或维度表拆行,查询时聚合;不要在事实表存逗号分隔字符串。
05.10.1-010 维度建模为什么反范式化?
频率 ★★★ | 难度 基础
一句话结论
OLAP 查询频率高、并发低,反范式化减少 JOIN,用空间换时间;3NF 范式化是 OLTP 思维,不适合分析场景。
典型场景与代码示例
星型模型示例
事实表:fact_order(订单事实表)
order_id, user_id, product_id, dept_id, amount, pay_time
维度表:dim_user
user_id, user_name, city, age
维度表:dim_product
product_id, product_name, category, price
查询:
SELECT u.city, p.category, SUM(f.amount)
FROM fact_order f
JOIN dim_user u ON f.user_id = u.user_id
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY u.city, p.category;
SCD2 示例
dim_user 表:
surrogate_key | user_id | city | valid_from | valid_to | is_current
1 | 100 | 北京 | 2023-01-01 | 2024-03-01 | 0
2 | 100 | 上海 | 2024-03-01 | 9999-12-31 | 1
事实表关联:
fact_order.user_key = 1 (下单时用户在北京)
事实表类型
事务事实表:一笔订单一行
order_id, user_id, amount, pay_time
周期快照事实表:每日库存拍照
dt, product_id, stock_count
累积快照事实表:订单全生命周期一行
order_id, created_at, paid_at, shipped_at, delivered_at
面试高频追问
问:星型模型和雪花模型生产用哪个?
答:生产数仓优先星型,反范式化减少 JOIN;雪花是 OLTP 思维,OLAP 不适合。
问:SCD2 为什么用代理键?
答:SCD2 后自然键对应多行,事实表用代理键才能精确匹配当时的维度版本。
问:事实表和维度表怎么区分?
答:事实表存业务度量(数量、金额),外键多;维度表存描述性属性(客户名、类目),相对宽。
问:维度建模为什么反范式化?
答:OLAP 查询频率高、并发低,反范式化减少 JOIN,用空间换时间。
核心要点速记
| 模型 | 特点 | 适用 |
|---|---|---|
| 星型 | 维度反范式、JOIN 少 | 生产数仓 |
| 雪花 | 维度范式化、JOIN 多 | 不推荐 |
| SCD | 处理 | 适用 |
|---|---|---|
| SCD1 | 直接覆盖 | 不保留历史 |
| SCD2 | 新增行+版本 | 保留历史(常用) |
| SCD3 | 保留当前+上一值 | 简单对比 |
一句话记忆:生产用星型+SCD2;代理键匹配历史版本。
相关概念辨析
星型 vs 雪花模型
| 维度 | 星型模型 | 雪花模型 |
|---|---|---|
| 维度表 | 一张 | 多张拆分 |
| JOIN | 少 | 多 |
| 查询性能 | 快 | 慢 |
| 空间 | 大 | 小 |
| 适合 | 查询为主 | 空间敏感 |
事实表 vs 维度表
- 事实表:业务度量,数字,可加。
- 维度表:业务描述,文字,不可加。
- 选择:业务过程建模用事实表;描述性属性用维度表。
05.10.1-009 SCD2 怎么实现?
频率 ★★★★ | 难度 进阶
一句话结论
SCD2(缓慢变化维类型2)保留历史,每行加 effective_from/effective_to/is_current;更新时关闭旧行(is_current=0),插入新行(is_current=1)。
概念解释
- SCD1:覆盖历史,不保留变化。
- SCD2:保留历史,加有效时间范围。
- SCD3:只保留前值和当前值。
原理流程
- 用户地址变化:旧行设置 effective_to='2026-01-01', is_current=0。
- 插入新行:effective_from='2026-01-01', is_current=1。
- 查询历史:WHERE effective_from <= date AND effective_to > date。
代码 / 示例
-- 用户维度表 SCD2
user_sk | user_id | address | effective_from | effective_to | is_current
1 | 1001 | 北京 | 2025-01-01 | 2026-01-01 | 0
2 | 1001 | 上海 | 2026-01-01 | 9999-12-31 | 1
常见误区
- 误区:SCD2 数据量不大。正解:用户频繁变化时,SCD2 行数膨胀。
追问延伸
- 代理键:user_sk 是自增代理键,不是 user_id。
- 拉链表:SCD2 也叫拉链表。
05.10.1-010 事实表粒度怎么确定?
频率 ★★★ | 难度 进阶
一句话结论
事实表粒度 = 业务过程的一次事件;粒度越细越好(原子事实表),上层汇总由 DWS 层完成。
概念解释
- 粒度:事实表一行代表什么业务事件。
- 原子粒度:一行 = 一次下单/支付/发货。
- 汇总粒度:一行 = 一天一个部门的汇总。
原理流程
- 业务过程:用户下单。
- 原子事实表:一行 = 一个订单的一个商品行。
- DWS 汇总:按天+部门汇总订单数和金额。
常见误区
- 误区:事实表直接做汇总。正解:DWD 明细要原子,DWS 才汇总。
追问延伸
- 粒度选择:原子事实表最灵活,上层可任意汇总。
- 过早汇总:DWD 做汇总会丢失细节,无法重新分析。
05.10.1-011 一致性维度怎么构建?
频率 ★★ | 难度 高级
一句话结论
一致性维度:多个事实表共享同一维度表(如日期、用户、商品),避免重复定义和口径不一致;维度表在 DWD 层统一构建。
概念解释
- 一致性维度:全公司统一的维度表,所有事实表 JOIN 同一张。
- 维度一致性:同一用户在订单事实表和浏览事实表中属性一致。
- 构建:DWD 层统一处理维度,下游事实表引用。
原理流程
- DWD 层构建用户维度表:user_dim。
- 订单事实表 JOIN user_dim。
- 浏览事实表也 JOIN 同一个 user_dim。
常见误区
- 误区:每个事实表自己处理维度。正解:维度要统一,否则用户属性不一致。
追问延伸
- 维度一致性:日期维度全公司统一。
- 一致性维度是数据仓库的核心原则。
待整理清单
- [ ] 总线矩阵设计
- [ ] 桥接表与多值维度
- [ ] 大数据下维度建模的实践(宽表代替雪花)