事实表、维度表与星型模型
维度建模把可度量业务过程放事实表,把描述性上下文放维度表。第一步是声明事实表粒度;粒度不清会造成重复计数,任何主键和BI工具都无法自动修复。
1. 数据流与决策边界
flowchart LR
A["源事实/事件"] --> B["传输与处理"]
B --> C["模型/存储"]
C --> D["查询与指标"]
D --> E["质量、对账与重建"]维度模型首先固定 grain:事实表每一行代表什么业务事件,决定可加指标、唯一键和允许连接的维度版本。事实必须在该粒度上可对账;SCD2 的有效时间区间与 surrogate key 用来保留历史语义,而不是给表增加形式上的字段。
2. Grain
机制:事实表一行准确代表什么。
实际用途:每订单行/每支付尝试/每日快照。
失败方式:混合订单与订单项粒度。
验证:用自然业务键和示例声明。
取舍:细粒度灵活但数据量大。
Grain 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
3. Fact
机制:可加、半可加或不可加度量及维度外键。
实际用途:amount_cents、quantity、duration。
失败方式:存百分比后直接求和。
验证:按度量聚合规则测试。
取舍:原子事实可重算但查询更重。
Fact 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
4. Dimension
机制:描述谁/什么/何时/哪里/为什么。
实际用途:用户、商品、渠道、日期。
失败方式:把频繁变化高基数事件塞维度。
验证:唯一业务键和属性历史测试。
取舍:宽维度易用但变化治理。
Dimension 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
5. Star schema
机制:事实表直接连接反规范化维度。
实际用途:BI查询简单、join少。
失败方式:把规范化OLTP表原样当星型。
验证:执行计划与用户语义验证。
取舍:重复属性换查询便利。
Star schema 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
6. Surrogate key
机制:仓库生成维度版本键。
实际用途:支持SCD和多源业务键。
失败方式:只用可复用业务ID。
验证:映射唯一/unknown行测试。
取舍:增加ETL映射步骤。
Surrogate key 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
7. SCD Type 1
机制:覆盖属性历史。
实际用途:修正拼写/不需历史。
失败方式:用于地区历史导致过去报表改变。
验证:变更前后历史查询测试。
取舍:简单但丢历史。
SCD Type 1 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
8. SCD Type 2
机制:新维度行保存有效期/当前标志。
实际用途:保留客户地区、计划版本。
失败方式:有效区间重叠/空洞。
验证:排除约束/质量测试。
取舍:历史准确但join与存储增加。
SCD Type 2 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
9. Date dimension
机制:日期属性、财年、节假日等。
实际用途:按业务日历分析。
失败方式:只从timestamp临时提取导致定义不一。
验证:日历范围/时区/财年测试。
取舍:集中语义但需维护。
Date dimension 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
10. Degenerate dimension
机制:业务单号留在事实表而无独立维表。
实际用途:订单号筛选/下钻。
失败方式:把大量描述字段都称退化维度。
验证:查询使用和唯一性验证。
取舍:减少join但不承载属性。
Degenerate dimension 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
11. Snapshot fact
机制:周期快照或累积快照描述状态。
实际用途:库存日结、订单里程碑。
失败方式:与交易事实混合后重复求和。
验证:按snapshot_date和grain测试。
取舍:趋势方便但会重复存储。
Snapshot fact 的生产契约还要定义输入schema、业务/事件时间、幂等键、水位、迟到/删除和重跑行为;成功状态必须对应已发布且通过质量门禁的数据。
12. 方案比较
| 方案 | 主要能力 | 边界 |
|---|---|---|
| Transaction fact | 每业务事件 | 量大最灵活 |
| Periodic snapshot | 每周期状态 | 不可跨日简单求和 |
| Accumulating snapshot | 流程一行多里程碑 | 更新与迟到复杂 |
| SCD1 | 覆盖 | 无历史 |
| SCD2 | 版本区间 | join复杂 |
13. 完整案例:订单收入星型模型
输入与约束
订单含多商品、退款和多币种;需要按商品/地区/日收入,金额精确。
处理步骤
- grain定义每订单项每次财务事件,而非每订单。
- fact保存原币/本位币整数金额、quantity、event_type。
- 连接date、product、customer_region、currency_rate_version维度。
- 退款用独立负向事实/明确类型,不覆盖原销售。
- 用ledger总额按日/tenant对账。
输出
聚合不会因订单多项或维度join重复,退款可追踪。
验证
sum事实与财务ledger守恒;每事实业务键唯一;未知维度落unknown。
失败分支
把订单总额复制到每item会按商品求和倍增;必须按grain分配/保存item金额。
恢复与重跑
以“订单项 + 财务事件 ID”作为稳定事实键重建受影响日期,重复销售或退款事件用该键拒绝。修正先写 shadow fact 分区,分别对账原币、本位币、数量和退款净额;确认每个事实只连接一个维度版本后再交换分区,旧版本保留用于报表回滚。
14. 完整案例:客户地区SCD2
输入与约束
客户7月从上海迁北京,历史订单应保留当时地区,新订单用北京。
处理步骤
- 维度行含customer_sk、business_id、valid_from/to、is_current。
- 变更关闭上海版本并插北京版本,同一业务时间轴不重叠。
- 事实按订单发生时刻查有效维度sk。
- 迟到订单重新按event time映射,不用当前行。
- 质量检查区间和一个current。
输出
六月收入属于上海,八月属于北京。
验证
边界时刻、迟到、重复变更、相同时间更新。
失败分支
事实只存customer_id并运行时join current维度会改写历史;保存版本surrogate key。
恢复与重跑
客户变更以 business ID、source version 和 valid_from 幂等应用。迟到变更先在隔离维度中重新切分受影响时间区间,再重映射该区间事实;检查无重叠、无意外空档且恰有一个 current 后原子替换客户时间轴,不能直接修改现有区间造成历史事实暂时多重匹配。
15. 失败注入矩阵
| 注入 | 预期信号与恢复 | 禁止结果 |
|---|---|---|
| 重复输入 | fact_row_count 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
| 乱序和迟到 | grain_duplicates 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
| 源schema变化 | unknown_dimension_rate 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
| 任务中途崩溃 | scd_overlap 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
| 下游429/503 | scd_gap 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
| checkpoint损坏 | ledger_reconciliation 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
| 质量规则失败 | late_fact_rate 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
| 回填与实时并发 | dimension_change 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
| 权限撤销 | query_scan 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
| 成本超预算 | semantic_test_fail 变化可解释,按水位/版本恢复 | 静默丢行、重复计量、越权或覆盖已发布版本 |
16. 数据质量与对账
- 为每张事实表声明 grain 和组成唯一键;任何重复键都阻断发布,而不是用
DISTINCT隐藏。 - 按财务事件类型分别对账销售、退款、税费和净额,避免正负抵消掩盖漏数。
- 可加指标在事实粒度上检查单位和范围;汇率版本、币种与原币金额缺一不可。
- 每个事实 surrogate key 必须命中一个维度行;缺失业务键进入 unknown 行并单独统计,不能 inner join 丢事实。
- SCD2 对每个 business ID 检查有效区间不重叠、边界规则一致且只有一个 current。
- 事实发生时刻必须落入所连接维度版本的
[valid_from, valid_to),迟到事实也使用 event time。 - 维度晚到率和 unknown 回填量分别监控;补齐维度后重映射需保持事实金额与行数不变。
- 日期维度覆盖事实最小到最大日期,财务周、节假日等业务属性由受控版本维护。
- snapshot fact 检查每主体每快照日最多一行,并区分存量余额与期间流量,禁止跨日直接求和半可加指标。
- 用小型 golden dataset 验证多商品订单、部分退款、客户迁移和边界时刻的预期聚合。
17. 调试与观测
fact_row_count:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。grain_duplicates:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。unknown_dimension_rate:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。scd_overlap:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。scd_gap:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。ledger_reconciliation:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。late_fact_rate:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。dimension_change:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。query_scan:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。semantic_test_fail:明确单位、采样点、聚合窗口和低基数维度,并与run ID、水位和代码版本关联。
聚合异常先写出事实 grain,选一个订单项或客户,检查事实唯一键、金额符号、event time、surrogate key 和 SCD2 有效区间,再观察 SQL join 后行数是否膨胀。收入翻倍通常用 join 前后计数和键基数定位;历史地区变化则比较事实保存的维度 key,而不是只看 current 维度。
18. 安全、成本与运维边界
- 源凭据最小权限;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
- PII分层访问和脱敏;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
- 原始层不可被普通BI任意下载;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
- 重跑/回填有资源配额;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
- 流批共享sink有容量仲裁;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
- 删除请求传播到派生层;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
- schema发布兼容门禁;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
- checkpoint/manifest备份;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
- 灾备恢复演练;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
- 成本按pipeline/dataset/tenant归集;事实表、维度表与星型模型 的实现要提供owner、runbook、停止阈值和审计记录。
19. 综合练习与验收
实现“订单收入星型模型”,再以“客户地区SCD2”验证另一类时效/治理约束。提交数据样例、模型、质量测试、故障注入、lineage和成本面板。
- [ ] Grain 的定义、应用、失败和验证均能用真实数据复现。
- [ ] Fact 的定义、应用、失败和验证均能用真实数据复现。
- [ ] Dimension 的定义、应用、失败和验证均能用真实数据复现。
- [ ] Star schema 的定义、应用、失败和验证均能用真实数据复现。
- [ ] Surrogate key 的定义、应用、失败和验证均能用真实数据复现。
- [ ] SCD Type 1 的定义、应用、失败和验证均能用真实数据复现。
- [ ] SCD Type 2 的定义、应用、失败和验证均能用真实数据复现。
- [ ] Date dimension 的定义、应用、失败和验证均能用真实数据复现。
- [ ] 两个案例包含输入、步骤、输出、验证、失败与重跑。
- [ ] 源与目标按业务分片完成count/sum/version/hash对账。
- [ ] 历史发布版本可回退,回填不压垮在线事实系统。
来源
- Kimball Group dimensional modeling techniques(访问日期:2026-07-17)
- dbt dimensional modeling guide(访问日期:2026-07-17)
- ClickHouse JOIN guide(访问日期:2026-07-17)
- PostgreSQL 18 constraints(访问日期:2026-07-17)