从0开始搭建数据仓库：维度建模实践笔记

本文基础：全文以 Kimball 维度建模方法论 为基准展开——自底向上，从业务过程出发，通过维度建模四步法（选择业务过程→声明粒度→确定维度→确定事实）构建数据仓库。所有分层设计、域划分、建表实践均基于此方法。

如果你只知道要搭数仓但不知道从何下手，这篇文章按下面这个顺序讲：先搞懂术语 → 理解分层每层干什么 → 掌握以什么为基准搭建 → 学维度建模四步法 → 看电商实战案例。按这个流程走，能建立起从0到1的完整思路。

不知道大家有这个疑问吗？数据仓库里的概念很多，数据域、主题域、业务过程、总线矩阵……单看每个词单独看都能理解，但串在一起就不知道从哪里下手了。针对该疑问，这篇文章把我自己搭建数仓的思路和步骤整理出来，供大家参考。

---

一、名词速查表

先快速过一遍数仓里常见的术语，后面用到的时候不回溯。

1.1 数仓分层

名词	是什么	一句话记住
ODS	贴源层，业务系统的数据原样搬过来	原材料，先存着
DWD	明细层，数据清洗+按业务过程组织	半成品，最细粒度的事实
DWS	汇总层，按主题汇总好的指标	成品，直接拿来分析
ADS	应用层，面向具体报表/接口	定制产品，给业务用
DIM	维度层，用户/商品/时间等维度表	字典，解释编码含义

1.2 维度建模核心概念

名词	是什么	举例
维度	看问题的角度	时间、地区、用户、商品
事实/度量	业务过程中可统计的数值	订单金额、商品件数
事实表	存业务事件数据的表	订单表、支付流水表
维度表	存维度属性描述的表	用户表、商品表
粒度	事实表里一行代表什么	一行=一个订单，或一行=一个订单里的一件商品
业务过程	企业里不可再拆的业务活动	下单、支付、发货、退款
星型模型	一个事实表周围围一圈维度表	查询快，最常用
雪花模型	维度表又关联其他维度表	省空间但关联多，一般不推荐
星座模型	多个事实表共享一套维度表	实际数仓中最常见的结构
事务事实表	记录单个业务事件	一行=一个事件，只增不改
周期快照事实表	定期记录状态快照	一行=某时刻的状态，每日/每月拍一次
累积快照事实表	跟踪一个流程的全过程	一行=一个流程，会被多次更新
无事实事实表	没有数值度量的事实表	只记录关联关系或事件是否发生
维度层次	维度的层级结构	年→月→日，省→市→区
一致维度	多个事实表共用的维度	确保不同域的维度定义统一

1.3 数据域与主题域

名词	是什么	怎么理解
数据域	按业务功能模块划分的大块	交易域、商品域、会员域——这是业务视角
主题域	按分析角度划分的大块	用户主题、商品主题、销售主题——这是分析视角
总线矩阵	业务过程×维度的关联表	用来检查维度定义是否一致

1.4 指标相关

名词	是什么	举例
原子指标	最基础的指标，不可再拆	“支付金额”
派生指标	原子指标+时间+限定条件	“最近7天无线端支付金额”
修饰词	对指标的限定	无线端、VIP用户、天猫店
时间周期	统计的时间范围	最近1天、自然周、自然月

1.5 其他

名词	是什么
缓慢变化维(SCD)	维度属性会随时间变化，如用户改手机号
SCD Type 0	固定不变，永远不更新（如注册日期）
SCD Type 1	直接覆盖旧值，不保留历史
SCD Type 2	新增一行保留历史，最常用
SCD Type 3	加列存旧值，只保留最近一次变化
SCD Type 4	当前值存主表，历史值存历史表
SCD Type 6	Type 1+2+3的组合（1+2+3=6）
实体	有独立业务含义的对象，对应维度表的一行
自然键	业务系统的原始主键（如user_id）
代理键	维度表自增的无业务意义的主键（如user_sk）
退化维度	维度属性太少，直接放事实表里
拉链表	用生效/失效日期记录历史变化的表
星座模型	多个事实表共享维度表的模型结构
一致维度	不同事实表共用的同一定义的维度
角色扮演维度	同一个维度表被多次使用（如时间维度）
垃圾维度	多个低基数标志位打包成的小维度表
微型维度	从大维度拆出的频繁变化属性小表
桥接表	解决事实与维度多对多关系的中间表
可加性	度量值能否跨维度直接相加的特性

---

从这里开始：本文核心主线

上面是工具箱里的工具，下面是从0开始搭数仓的实操路线。先给你一张地图，知道每一步要干什么、以什么为准：

本文核心问题速答

Q1：本文以什么为基准搭建数仓？

Kimball 维度建模方法论，自底向上路线。
基准是"业务过程"——从业务过程出发，用四步法建模，先建DWD明细层，再建DWS汇总层，最后建ADS应用层。数据域按业务功能模块划分，主题域按分析角度划分，用总线矩阵保证维度一致性。

Q2：从0开始搭建数仓的具体步骤是什么？

Step 1: 梳理业务 → 按功能模块划分数据域（交易域/商品域/会员域…）
Step 2: 拆业务过程 → 每个域里有哪些不可再拆的业务活动（下单/支付/发货…）
Step 3: 建DWD层 → 每个业务过程一张事实表，做维度建模（四步法），保持最细粒度
Step 4: 建DIM层 → 定义维度表（用户/商品/时间…），SCD Type 2处理变化
Step 5: 建DWS层 → 按主题域汇总，建宽表，计算原子指标
Step 6: 建ADS层 → 面向具体报表/接口，按需输出
Step 7: 用总线矩阵检查 → 确保维度在各业务过程中定义一致

Q3：每一层干什么、怎么干？

往下看第二章（分层详解）和第五章（四步法详解），每层都有"功能定位→做什么→怎么做→设计要点→代码示例"的完整说明。

---

二、数仓分层的核心逻辑

分层不是为了分层而分层。核心目的是解耦——每一层只关心自己的输入和输出，不关心其他层怎么实现的。

具体好处：

• ODS层和业务系统解耦：业务库删了数据，数仓还有备份
• DWD层和ODS解耦：上游表结构变了，只改DWD的接入逻辑
• DWS层和DWD解耦：下游用指标，不用关心底层明细怎么算
• ADS层和DWS解耦：报表改了，不需要重新算基础指标

下面一层一层展开说。

2.1 ODS层：贴源层

功能定位：数据入口，从业务系统原样接入数据。

做什么：

• 从MySQL、MongoDB、日志系统等业务数据源同步数据
• 数据原样存储，几乎不做清洗和转换
• 保留原始字段名、原始数据类型、原始编码格式

怎么做：

• 通常用Sqoop、DataX、Flink CDC等工具同步
• 表结构和源系统保持一致，方便溯源
• 增量同步为主，全量同步为辅（如维度表每天全量）

为什么需要这一层：

• 数据备份：业务系统可能只保留最近3个月数据，数仓可以存更久
• 数据溯源：下游数据出问题，可以回到ODS排查
• 减少对业务库的查询压力

设计要点：

• 表名规范：ods_源系统_表名_i_d（增量日同步）或 ods_源系统_表名_f_d（全量日同步）
• 增加ETL时间字段，记录数据同步时间
• 不做数据质量校验（校验放到DWD做），但同步失败要告警

示例：

-- ODS层订单表（和业务库orders表结构基本一致）
CREATE TABLE ods_mysql_orders_i_d (
    order_id BIGINT COMMENT '订单ID',
    user_id BIGINT COMMENT '用户ID',
    order_amount DECIMAL(18,4) COMMENT '订单金额',
    order_status INT COMMENT '订单状态',
    create_time TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP COMMENT '更新时间',
    etl_time TIMESTAMP COMMENT '数据同步时间'
)
COMMENT 'ODS层-订单表（MySQL增量同步）'
PARTITIONED BY (dt STRING);

2.2 DWD层：明细层

功能定位：数仓的核心层，做数据清洗和维度建模。

做什么：

• 数据清洗：去重、空值处理、格式转换、异常值处理
• 数据标准化：统一编码、统一单位、统一时间格式
• 维度建模：按照Kimball方法论，构建事实表和维度表
• 数据脱敏：敏感字段加密或打码

怎么做：

第一步：从ODS取数据做清洗

-- 清洗示例：处理空值、格式化时间、去重复订单
INSERT OVERWRITE TABLE dwd_trade_order_create_di PARTITION(dt='2024-01-01')
SELECT
    order_id,
    COALESCE(user_id, 0) AS user_id,        -- 空值处理
    COALESCE(order_amount, 0) AS order_amount,
    DATE_FORMAT(create_time, 'yyyy-MM-dd HH:mm:ss') AS create_time,  -- 时间格式化
    -- ...
FROM ods_mysql_orders_i_d
WHERE dt = '2024-01-01'
  AND order_id IS NOT NULL                  -- 去脏数据
GROUP BY order_id, user_id, order_amount, create_time;  -- 去重

第二步：做维度建模

每张DWD事实表对应一个业务过程。建表时要确定四件事：

1. 粒度：一行代表什么？（订单商品级别 = 最细）
2. 维度外键：关联哪些维度表？
3. 退化维度：哪些维度字段直接放事实表里？
4. 事实/度量：统计什么数值？

设计要点：

• 事实表保持最细粒度，不要提前汇总
• 维度表用代理键做主键，SCD Type 2处理变化
• 每张事实表只记录一个业务过程
• 表名规范：dwd_数据域_业务过程_描述_di（增量日）

2.3 DWS层：汇总层

功能定位：面向分析主题，把DWD的事实数据按维度汇总成指标。

做什么：

• 按维度汇总事实数据，计算原子指标
• 构建主题宽表，把常用维度属性冗余进来
• 支持不同时间周期的统计（1天、7天、30天等）

怎么做：

DWS层的输入是DWD事实表，输出是主题宽表。建一张DWS表要确定三件事：

1. 主题：这张表分析什么？（用户主题、商品主题、销售主题）
2. 统计粒度：一行代表什么？（一个用户的一天、一个商品的一天）
3. 指标：哪些指标？（订单数、支付金额、退款率等）

-- DWS用户主题日汇总：从DWD下单、支付、退款事实表汇总
INSERT OVERWRITE TABLE dws_member_user_trade_1d PARTITION(dt='2024-01-01')
SELECT
    u.user_sk,
    u.user_id,
    '2024-01-01' AS stat_date,
    u.user_level,
    u.city_name,
    -- 下单指标
    COUNT(DISTINCT oc.order_id) AS order_cnt_1d,
    SUM(oc.order_amount) AS order_amt_1d,
    -- 支付指标
    COUNT(DISTINCT op.order_id) AS pay_order_cnt_1d,
    SUM(op.pay_amount) AS pay_amt_1d,
    -- 退款指标
    COUNT(DISTINCT orf.order_id) AS refund_cnt_1d,
    SUM(orf.refund_amount) AS refund_amt_1d
FROM dim_user u
LEFT JOIN dwd_trade_order_create_di oc 
    ON u.user_sk = oc.user_sk AND oc.dt = '2024-01-01'
LEFT JOIN dwd_trade_order_pay_di op 
    ON u.user_sk = op.user_sk AND op.dt = '2024-01-01'
LEFT JOIN dwd_trade_order_refund_di orf 
    ON u.user_sk = orf.user_sk AND orf.dt = '2024-01-01'
WHERE u.dt = '2024-01-01' AND u.is_current = 1
GROUP BY u.user_sk, u.user_id, u.user_level, u.city_name;

设计要点：

• 宽表设计：把维度属性冗余进来，减少查询时的JOIN操作
• 一张DWS表聚焦一个主题，不要塞太多不相关的指标
• 时间周期按需建：1天、7天、30天、90天，分别建不同的表或不同的分区
• 表名规范：dws_数据域_主题_粒度_周期，如 dws_member_user_trade_1d

2.4 ADS层：应用层

功能定位：面向具体业务场景，直接给报表、BI、API用。

做什么：

• 从DWS层取数，按业务需求做最终计算
• 输出格式适配具体应用（报表格式、接口格式）
• 复杂场景可能需要跨主题关联计算

怎么做：

ADS没有固定模式，完全看业务需求。常见场景：

• 运营日报：从DWS汇总，一行=一天的平台整体数据
• 用户画像：从DWS用户主题表取数，打标签
• 商品分析报表：从DWS商品主题表取数，做排名、趋势
• 接口数据：按前端需求组装JSON格式数据

设计要点：

• 按需建设，不要提前建一堆用不上的ADS表
• 直接从DWS取数，尽量不跨多层计算
• 表名规范：ads_应用名_主题_描述

2.5 分层总结对比

层级	功能	输入	输出	核心问题
ODS	数据接入	业务系统	原始数据镜像	数据有没有丢？
DWD	清洗+建模	ODS	干净的事实表+维度表	业务过程拆对了吗？粒度够细吗？
DWS	汇总指标	DWD	主题宽表（含指标）	主题划分合理吗？指标算对了吗？
ADS	应用输出	DWS	报表/接口数据	满足业务需求了吗？

---

三、维度建模 vs Kimball vs Inmon

很多人把"维度建模"和"Kimball建模"当成一回事，实际上两者有明确区别。搞清这个关系，才知道这篇文章到底在讲什么。

3.1 三者的关系

维度建模（Dimensional Modeling） 是一种数据组织的技术方法——用事实表和维度表来组织数据，以星型/雪花/星座模型呈现。它是"术"，是工具。

Kimball方法论 是 Ralph Kimball 提出的一套完整的数据仓库建设方法论，包含了维度建模技术，但远不止于此。它是"道"，是体系。

Inmon方法论 是 Bill Inmon 提出的另一套数据仓库建设方法论，底层用第三范式（3NF）建模，前端展示时才转维度模型。

三者的关系：

对比项	维度建模	Kimball	Inmon
是什么	一种数据组织技术	一套完整建数仓的方法论	另一套完整建数仓的方法论
提出者	Ralph Kimball	Ralph Kimball	Bill Inmon
建模方式	事实表+维度表	维度建模（核心）	第三范式（核心），前端用维度模型
建设路径	—	自底向上	自顶向下
从哪里开始	—	从业务过程出发	从企业全局模型出发
先建什么	—	先建数据集市，再用总线架构整合	先建企业级数据仓库（EDW），再建集市
数据一致性	—	通过一致维度+总线矩阵保证	通过企业级统一模型保证

3.2 Kimball 详细展开

Kimball 的核心主张：自底向上，从业务过程开始，快速出成果。

具体步骤：

1. 选一个业务过程（如下单）
2. 用维度建模建事实表+维度表，形成一个数据集市
3. 再选下一个业务过程（如支付），重复上述过程
4. 不同集市之间通过一致维度和总线矩阵保证统一
5. 最终所有集市整合成完整的数据仓库

优点：

• 见效快，第一个集市几周就能上线
• 和业务紧密结合，不容易脱离实际需求
• 方法简单，团队容易掌握

缺点：

• 长期来看可能存在冗余（不同集市有重复计算）
• 如果没有总线矩阵约束，容易各自为战、口径不一

3.3 Inmon 详细展开

Inmon 的核心主张：自顶向下，先建企业级统一数据仓库，再建集市。

具体步骤：

1. 先做企业级的全局数据模型（ER模型，第三范式）
2. 按第三范式建企业级数据仓库（EDW），消除一切冗余
3. 在EDW之上，按需构建数据集市（此时才用维度建模）
4. 所有集市的数据来源都是EDW，天然一致

优点：

• 数据一致性天然有保障（只有一个源头）
• 企业级模型完整，没有信息孤岛
• 数据冗余最少，存储效率高

缺点：

• 前期投入大，全局建模耗时数月甚至数年
• 见效慢，业务方可能等不及
• 对建模团队要求极高，需要理解全公司业务

3.4 一句话总结

场景	选哪个
互联网公司，业务变化快，需要快速出结果	Kimball
大型传统企业，数据治理要求高，不差时间	Inmon
底层用Inmon做EDW，上层用Kimball做集市	混合架构（现在很多大厂这样做）

维度建模是两者的交集：Kimball用维度建模做核心，Inmon用维度建模做前端展示。学会维度建模，两种路线都能走。

本文讲的是 Kimball 方法论的维度建模实践——从业务过程出发，用四步法建模，自底向上建设。

---

四、数据域和主题域：怎么划分

这是最容易搞混的两个概念。很多人知道"要划分数据域"，但拿到一个实际业务后，不知道从何下手。

3.1 数据域的划分方法

划分依据：业务功能模块

拿到一个业务系统（比如电商平台），先看它有哪些功能模块。每个大的功能模块就是一个数据域。

具体操作步骤：

第一步：梳理业务系统的功能模块

打开你的电商后台（或者产品文档），看看左边导航栏有什么：

• 商品管理 → 商品域
• 订单管理 → 交易域
• 会员管理 → 会员域
• 物流管理 → 物流域
• 营销活动 → 营销域
• 财务管理 → 财务域

这就是数据域的来源——从业务系统的功能模块来。

第二步：检查数据域的完整性

每个数据域应该满足两个条件：

1. 内部高内聚：域内的数据关联紧密（交易域里的订单、支付、退款紧密相关）
2. 外部低耦合：不同域之间尽量独立（商品域和物流域各自独立）

第三步：产出数据域清单

数据域	覆盖的业务	对应的业务系统
交易域	下单、支付、退款、结算	订单系统、支付系统
商品域	商品、类目、品牌、库存	商品中心、库存系统
会员域	注册、登录、等级、积分	用户中心、会员系统
物流域	发货、配送、签收、退货	物流系统、仓储系统
营销域	优惠券、满减、秒杀、广告	营销系统、广告系统
行为域	浏览、搜索、点击、收藏	日志系统、埋点系统

划分数据域的作用：

1. 分工协作：不同小组负责不同域，各管一摊，互不干扰
2. 权限管控：敏感数据域（如财务域）可以单独控制访问权限
3. 并行开发：域之间解耦，可以多个域同时建设
4. 方便定位：出问题时能快速定位到具体域

3.2 主题域的划分方法

划分依据：分析角度

数据域是按业务功能分的，主题域是按分析视角分的。同一个数据域的数据，可以从不同主题角度来分析。

具体操作步骤：

第一步：确定分析需求

和业务方沟通，他们平时要看什么报表？常见的分析角度：

• 我想看每个用户的消费情况 → 用户主题
• 我想看每个商品卖得好不好 → 商品主题
• 我想看每天的整体销售额 → 销售主题
• 我想看用户从哪来的 → 流量主题

第二步：从数据域映射到主题域

一个数据域可以支撑多个主题，一个主题也可能用到多个域的数据：

主题域	用到的数据域	分析什么
用户主题	会员域、交易域	用户价值、留存、RFM
商品主题	商品域、交易域	商品销量、动销率、热度
销售主题	交易域、营销域	GMV、转化率、客单价
流量主题	行为域、会员域	UV、PV、跳出率、来源
物流主题	物流域、交易域	时效、满意度、异常率

第三步：确定主题宽表

每个主题域对应一类DWS宽表。比如"用户主题"对应 dws_member_user_trade_1d，"商品主题"对应 dws_product_sale_1d。

3.3 两者的关系

一句话总结：

• 数据域 = 业务视角的划分 → 指导DWD层怎么建事实表
• 主题域 = 分析视角的划分 → 指导DWS层怎么建宽表

从0到1的划分流程：

业务系统功能模块 → 数据域（DWD层）
       ↓
业务方分析需求 → 主题域（DWS层）
       ↓
数据域 + 主题域 → 总线矩阵 → 确定每张表建什么

---

五、维度建模四步法

Kimball提出的方法，数仓建模的事实标准。

第一步：选择业务过程

从数据域里挑一个业务过程来建模。比如交易域里有：下单、支付、发货、退款。

每个业务过程对应一张事实表。

第二步：声明粒度

粒度就是一行代表什么。原则是取最细粒度。

电商订单要拆成两张事实表：

• 订单头表：一行 = 一个订单（存订单总金额、运费）
• 订单商品表：一行 = 一个订单里的一件商品（存商品金额、数量）

为什么拆？因为一张订单可能包含多个商品，粒度不一样。如果只做订单级别，就看不到每个商品的情况了。

第三步：确定维度

围绕这个业务过程，列出所有分析角度：

维度	有什么用
时间	什么时候发生的？
用户	谁操作的？
商品	买的什么？
地区	从哪个地方下单的？
店铺	哪个商家？
渠道	APP还是小程序？

第四步：确定事实

这个业务过程能统计什么数值：

事实	说明
订单金额	这个订单/商品花了多少钱
商品件数	买了几件
优惠金额	减了多少钱
订单次数	计数

---

六、总线矩阵与指标体系

5.1 总线矩阵

总线矩阵就是一张大表，横轴是维度，纵轴是业务过程，标记它们之间的关系。

它的实际作用是：确保同一个维度在不同业务过程中的定义是一致的。比如"用户维度"在下单、支付、退款里用的是同一套用户数据，不会出现下单表里的VIP定义和支付表里的VIP定义不一样的情况。

5.2 指标体系

口径不统一是数仓最常见的问题。A部门算的GMV和B部门算的GMV对不上，根源在于没有统一的指标体系。

做法分两步：

第一步：定义原子指标

原子指标 = 业务过程 + 度量，不可再拆分。

原子指标	业务过程	度量
支付金额	支付	金额
下单件数	下单	件数
退款笔数	退款	次数

第二步：定义派生指标

派生指标 = 时间周期 + 修饰词 + 原子指标

派生指标	时间周期	修饰词	原子指标
最近7天无线端支付金额	最近7天	无线端	支付金额
双11当天新用户下单件数	双11当天	新用户	下单件数

这样做的好处是：同一个原子指标只算一次，口径统一。比如"支付金额"在DWD层定义好，DWS层和ADS层都引用同一个定义，不会出现各算各的。

---

七、电商数仓实战

6.1 业务背景

一个B2C电商平台，核心业务：用户注册→浏览商品→下单→支付→发货→收货→评价。有商品中心、订单系统、支付系统、物流系统、会员系统。

6.2 划分数据域

按业务系统的功能模块划分：

数据域	来源系统	包含的业务过程
交易域	订单系统、支付系统	下单、支付、退款、结算
商品域	商品中心、库存系统	商品上架、类目变更、库存变动
会员域	用户中心、会员系统	注册、登录、等级变更
物流域	物流系统、仓储系统	发货、揽收、配送、签收、退货
营销域	营销系统、优惠券系统	领券、用券、参与活动
行为域	埋点系统、日志系统	浏览、搜索、点击、收藏、加购

6.3 梳理业务过程（交易域为例）

交易域拆出5个业务过程，每个对应一张DWD事实表：

业务过程	对应事实表	粒度
下单	dwd_trade_order_create_di	订单商品级别
支付	dwd_trade_order_pay_di	订单商品级别
发货	dwd_trade_order_ship_di	订单级别
签收	dwd_trade_order_receive_di	订单级别
退款	dwd_trade_order_refund_di	订单商品级别

6.4 定义维度

维度表	主键	核心属性	SCD处理
dim_date	date_sk	日期、星期、节假日、年月	预生成，无变化
dim_user	user_sk	用户ID、性别、年龄、等级、城市	Type 2
dim_product	product_sk	商品ID、名称、类目、品牌、价格	Type 2
dim_region	region_sk	省、市、区、邮编	预生成
dim_shop	shop_sk	店铺ID、名称、等级、类目	Type 2
dim_payment_type	payment_type_sk	支付方式编码、名称	Type 1

6.5 DWD层建表

下单事实表：

CREATE TABLE dwd_trade_order_create_di (
    -- 代理键
    order_create_sk BIGINT COMMENT '代理键',
    
    -- 业务主键
    order_id BIGINT COMMENT '订单ID',
    order_item_id BIGINT COMMENT '子订单ID',
    
    -- 维度外键
    date_sk BIGINT COMMENT '时间维度代理键',
    user_sk BIGINT COMMENT '用户维度代理键',
    product_sk BIGINT COMMENT '商品维度代理键',
    region_sk BIGINT COMMENT '地区维度代理键',
    shop_sk BIGINT COMMENT '店铺维度代理键',
    
    -- 退化维度
    source_type STRING COMMENT '来源（APP/小程序/H5）',
    order_status INT COMMENT '订单状态',
    
    -- 事实/度量
    order_amount DECIMAL(18,4) COMMENT '订单金额',
    goods_amount DECIMAL(18,4) COMMENT '商品金额',
    discount_amount DECIMAL(18,4) COMMENT '优惠金额',
    freight_amount DECIMAL(18,4) COMMENT '运费',
    goods_count INT COMMENT '商品件数',
    
    -- 技术字段
    create_time TIMESTAMP COMMENT '下单时间',
    etl_time TIMESTAMP COMMENT 'ETL时间'
)
COMMENT '交易域-下单事实表'
PARTITIONED BY (dt STRING);

支付事实表：

CREATE TABLE dwd_trade_order_pay_di (
    pay_sk BIGINT COMMENT '代理键',
    
    order_id BIGINT COMMENT '订单ID',
    pay_id BIGINT COMMENT '支付流水号',
    
    -- 维度外键
    date_sk BIGINT COMMENT '时间维度代理键',
    user_sk BIGINT COMMENT '用户维度代理键',
    product_sk BIGINT COMMENT '商品维度代理键',
    region_sk BIGINT COMMENT '地区维度代理键',
    shop_sk BIGINT COMMENT '店铺维度代理键',
    payment_type_sk BIGINT COMMENT '支付渠道代理键',
    
    -- 事实/度量
    pay_amount DECIMAL(18,4) COMMENT '支付金额',
    goods_amount DECIMAL(18,4) COMMENT '商品金额',
    discount_amount DECIMAL(18,4) COMMENT '优惠金额',
    
    -- 技术字段
    pay_time TIMESTAMP COMMENT '支付时间',
    etl_time TIMESTAMP COMMENT 'ETL时间'
)
COMMENT '交易域-支付事实表'
PARTITIONED BY (dt STRING);

6.6 DWS层建表

用户主题日汇总：

CREATE TABLE dws_member_user_trade_1d (
    -- 维度
    user_sk BIGINT COMMENT '用户代理键',
    user_id BIGINT COMMENT '用户ID',
    dt STRING COMMENT '统计日期',
    
    -- 冗余维度属性（查的时候不用join）
    user_level STRING COMMENT '用户等级',
    city_name STRING COMMENT '城市',
    register_date STRING COMMENT '注册日期',
    
    -- 下单指标
    order_create_cnt_1d INT COMMENT '下单笔数',
    order_create_amt_1d DECIMAL(18,4) COMMENT '下单金额',
    order_create_sku_cnt_1d INT COMMENT '下单商品件数',
    
    -- 支付指标
    order_pay_cnt_1d INT COMMENT '支付笔数',
    order_pay_amt_1d DECIMAL(18,4) COMMENT '支付金额',
    
    -- 退款指标
    order_refund_cnt_1d INT COMMENT '退款笔数',
    order_refund_amt_1d DECIMAL(18,4) COMMENT '退款金额',
    
    -- 技术字段
    etl_time TIMESTAMP COMMENT 'ETL时间'
)
COMMENT '会员域-用户交易主题日汇总'
PARTITIONED BY (dt STRING);

商品主题日汇总：

CREATE TABLE dws_product_sale_1d (
    -- 维度
    product_sk BIGINT COMMENT '商品代理键',
    product_id BIGINT COMMENT '商品ID',
    dt STRING COMMENT '统计日期',
    
    -- 冗余维度属性
    product_name STRING COMMENT '商品名称',
    category_name STRING COMMENT '类目',
    brand_name STRING COMMENT '品牌',
    
    -- 行为指标
    pv_1d BIGINT COMMENT '浏览量',
    uv_1d BIGINT COMMENT '浏览人数',
    cart_cnt_1d INT COMMENT '加购次数',
    
    -- 交易指标
    order_cnt_1d INT COMMENT '下单次数',
    order_sku_cnt_1d INT COMMENT '下单件数',
    pay_cnt_1d INT COMMENT '支付笔数',
    pay_amt_1d DECIMAL(18,4) COMMENT '支付金额',
    pay_sku_cnt_1d INT COMMENT '支付件数',
    
    -- 技术字段
    etl_time TIMESTAMP COMMENT 'ETL时间'
)
COMMENT '商品域-商品销售主题日汇总'
PARTITIONED BY (dt STRING);

6.7 ADS层建表

-- 运营日报表
CREATE TABLE ads_ops_daily_report (
    report_date STRING COMMENT '报表日期',
    
    -- 流量
    dau BIGINT COMMENT '日活',
    new_user_cnt INT COMMENT '新增用户',
    
    -- 交易
    order_cnt INT COMMENT '订单笔数',
    pay_order_cnt INT COMMENT '支付笔数',
    gmv DECIMAL(18,4) COMMENT 'GMV',
    pay_amt DECIMAL(18,4) COMMENT '实付金额',
    refund_amt DECIMAL(18,4) COMMENT '退款金额',
    
    -- 转化
    order_pay_rate DECIMAL(5,4) COMMENT '下单到支付转化率',
    
    -- 用户
    arpu DECIMAL(18,4) COMMENT '人均消费',
    
    etl_time TIMESTAMP COMMENT 'ETL时间'
)
COMMENT '运营日报表'
PARTITIONED BY (dt STRING);

6.8 命名规范

层级	命名格式	示例
ODS	ods_源系统_表名_策略_周期	ods_mysql_orders_i_d
DWD	dwd_数据域_业务过程_描述_策略_周期	dwd_trade_order_create_di
DWS	dws_数据域_主题_粒度_周期	dws_member_user_trade_1d
ADS	ads_应用_主题_描述	ads_ops_daily_report
DIM	dim_维度名	dim_product
TMP	tmp_目标表_日期	tmp_dws_user_20240101

---

八、一些实践经验

1. 不要追求完美模型，先跑起来。建模没有唯一正确答案，业务能跑通、查询性能OK、口径统一，就是好的模型。

2. DWD粒度宁细勿粗。汇总可以从细粒度算出来，但粗粒度没法拆细。一开始做粗了，后面想细分就要返工。

3. 用户、商品这类维度尽早用SCD Type 2。用户的等级、商品的类目这些属性一定会变，一开始就用拉链表（生效/失效日期），后面省大事。

4. DWS宽表不要怕冗余。存储比计算便宜，把常用的维度属性冗余到宽表里，查询的时候少做JOIN，性能更好。

5. 指标口径文档必须写。每个DWS字段怎么算的，数据从哪来，公式是什么，都要写清楚。不然三个月之后连你自己都忘了这个字段的业务含义。

---

---

九、容易遗漏的概念

前面把主线讲完了，这部分补充一些实际工作中经常遇到但容易被忽略的概念。

8.1 实体（Entity）

实体就是你要描述的一个对象。在维度建模里，实体通常对应一张维度表。

实体类型	举例	对应什么
人	用户、员工、客户	用户维度表
物	商品、设备、资产	商品维度表
地	省份、城市、门店	地区维度表
时	日期、时间	时间维度表
事	订单、合同	事实表

实体的关键是：它有独立的业务含义，可以被单独描述。一个用户有自己的姓名、年龄、手机号——这些属性描述了"用户"这个实体。实体对应到表设计上，就是维度表里的一行。

8.2 自然键 vs 代理键

对比项	自然键（Natural Key）	代理键（Surrogate Key）
是什么	业务系统的原始主键	数仓自己生成的自增ID
举例	user_id = 10086（业务库的ID）	user_sk = 1, 2, 3（无业务含义）
从哪来	从业务系统同步过来	数仓ETL时自动生成
会变吗	可能变（业务系统重新发号）	不变，一旦生成永不动
为什么用它	和源系统保持一致	隔离业务变化，支持SCD Type 2

必须同时保留：维度表里自然键和代理键都要有。代理键做主键用来关联事实表，自然键用来溯源和去重。

CREATE TABLE dim_user (
    user_sk BIGINT PRIMARY KEY COMMENT '代理键-数仓自增',
    user_id BIGINT COMMENT '自然键-业务系统用户ID',
    user_name STRING COMMENT '用户名',
    -- ...
);

8.3 星座模型

前面讲了星型模型（一个事实表+多个维度表）和雪花模型（维度表再关联维度表）。实际数仓里最常见的是星座模型——多个事实表共享同一套维度表。

星座模型就是星型模型的扩展。电商场景里，订单事实表、支付事实表、退款事实表共用时间维度、用户维度、商品维度——这整体就是一张星座模型图。

为什么实际中都是星座模型：

• 业务过程天然是多个（下单、支付、发货……）
• 维度天然是共享的（用户就一套、商品就一套）
• 星型模型只是星座模型的一个子集

8.4 一致维度（Conformed Dimension）

一致维度的意思是：同一个维度，在不同的数据域、不同的事实表里，定义必须完全一样。

举个例子：

• 交易域的订单事实表用了"用户维度"
• 营销域的优惠券事实表也用了"用户维度"
• 这两个用户维度必须是同一张表（或完全相同的定义）

如果不一致会出现什么问题：

• 交易域里用户A是VIP，营销域里用户A不是VIP——同一个人有两个身份
• 算出来的"VIP用户消费金额"和"VIP用户领券金额"对不上

一致维度的实际做法：

• 全公司只维护一份用户维度表（通常由数据中台团队维护）
• 所有数据域都引用同一份
• 维度表更新时，下游所有事实表统一生效

8.5 事实表的三种类型

很多人以为事实表只有一种。实际上Kimball定义了三种，用法完全不同。

事务事实表（Transaction Fact Table）

一行 = 一个业务事件。最常用的类型。

• 例：订单表，一行 = 一笔订单
• 特点：只增不改，增量追加
• 适用：记录离散的业务事件（下单、支付、退款）

周期快照事实表（Periodic Snapshot Fact Table）

一行 = 某个周期结束时的状态快照。

• 例：库存表，每天拍一张照片，记录每个商品当天的库存量
• 特点：定期全量覆盖，可以看历史变化趋势
• 适用：记录状态类数据（库存、账户余额、用户等级）

-- 库存周期快照：每天记录每个商品的库存
CREATE TABLE dwd_product_stock_1d (
    product_sk BIGINT COMMENT '商品代理键',
    stock_qty INT COMMENT '库存数量',
    dt STRING COMMENT '快照日期'
);

累积快照事实表（Accumulating Snapshot Fact Table）

一行 = 一个流程的完整生命周期，这一行会被多次更新。

• 例：订单生命周期表，一行跟踪一个订单从下单到收货的全过程
• 特点：一行会被反复更新，每到一个节点填一个时间字段
• 适用：跟踪有明确流程的业务（订单流转、贷款审批）

-- 订单累积快照：一行跟踪整个订单生命周期
CREATE TABLE dwd_trade_order_lifecycle (
    order_id BIGINT COMMENT '订单ID',
    user_sk BIGINT COMMENT '用户代理键',
    order_amount DECIMAL(18,4) COMMENT '订单金额',
    -- 流程各节点的时间
    create_time TIMESTAMP COMMENT '下单时间',
    pay_time TIMESTAMP COMMENT '支付时间（未支付为空）',
    ship_time TIMESTAMP COMMENT '发货时间（未发货为空）',
    receive_time TIMESTAMP COMMENT '签收时间（未签收为空）',
    -- 当前状态
    current_status INT COMMENT '当前节点（1下单/2已付/3已发/4已收）'
);

三种事实表怎么选：

场景	选哪种
记录发生了什么事	事务事实表
每天看一次状态变化	周期快照
跟踪一个流程走到哪了	累积快照

一个业务过程可以同时建多种事实表。比如订单：事务事实表记录每笔订单明细，累积快照表跟踪订单生命周期，两者互补不冲突。

8.6 无事实事实表（Factless Fact Table）

没有数值度量的事实表。只记录某个事件是否发生。

常见用途：

• 记录关联关系：用户和优惠券的领取关系（谁领了哪张券）
• 记录覆盖范围：店铺和商品的铺货关系（哪个店卖哪个商品）

-- 用户领券记录：没有金额字段，只记录关联
CREATE TABLE dwd_marketing_coupon_claim (
    claim_sk BIGINT COMMENT '代理键',
    user_sk BIGINT COMMENT '用户代理键',
    coupon_sk BIGINT COMMENT '优惠券代理键',
    date_sk BIGINT COMMENT '领取日期代理键',
    -- 没有金额、数量等度量字段
    claim_time TIMESTAMP COMMENT '领取时间'
);

8.7 角色扮演维度（Role-Playing Dimension）

同一个物理维度表，在事实表里通过不同的外键扮演不同的角色。

最常见的例子是时间维度：

• 订单事实表里，order_date_sk 关联时间维度 → 这是"下单日期"
• 同一张订单事实表里，pay_date_sk 也关联时间维度 → 这是"支付日期"
• 物理上只有一张 dim_date 表，逻辑上扮演了两个角色

CREATE TABLE dwd_trade_order_di (
    -- 同一个 dim_date 表被用了三次，扮演三个角色
    order_date_sk BIGINT COMMENT '下单日期（角色1）',
    pay_date_sk BIGINT COMMENT '支付日期（角色2）',
    ship_date_sk BIGINT COMMENT '发货日期（角色3）',
    -- ...
);

关键理解：角色扮演维度不是建了多张维度表，而是一张物理表被多次引用。查询时根据业务需要选择用哪个外键。

8.8 垃圾维度（Junk Dimension）

事实表里经常有一堆低基数的标志位和状态码（比如 is_vip、source_type、device_type），每个都建一个维度表太碎，直接放事实表里字段又太多。垃圾维度就是把它们打包成一张小维度表。

做法	问题
每个标志位都建一张维度表	维度表太多，维护成本高
直接放事实表里	事实表字段太多，可读性差
打包成垃圾维度	一张小表搞定，用外键关联

-- 垃圾维度表：把多个低基数标志位打包
CREATE TABLE dim_junk_order_flags (
    junk_sk BIGINT COMMENT '代理键',
    is_vip TINYINT COMMENT '是否VIP下单（0/1）',
    source_type STRING COMMENT '来源（APP/小程序/H5）',
    device_type STRING COMMENT '设备（iOS/Android/PC）',
    is_first_order TINYINT COMMENT '是否首单（0/1）',
    is_promotion TINYINT COMMENT '是否参与活动（0/1）'
);

-- 事实表只存一个外键
CREATE TABLE dwd_trade_order_di (
    -- ...
    junk_sk BIGINT COMMENT '垃圾维度外键',
    -- 不再单独存 is_vip、source_type 等字段
);

什么时候用：事实表里有5个以上低基数（取值少于50个）的标志位时，考虑建垃圾维度。

8.9 微型维度（Mini-Dimension）

用户维度表通常很大（千万级甚至亿级），而且某些属性变化频繁（如用户等级、活跃度标签）。如果用SCD Type 2处理，每次变化都要新增一行，维度表体积会爆炸。

解决方案：把频繁变化的属性从大维度表里拆出来，单独成一张"微型维度"表。

-- 主维度表：只存稳定属性
CREATE TABLE dim_user (
    user_sk BIGINT COMMENT '代理键',
    user_id BIGINT COMMENT '业务用户ID',
    gender STRING COMMENT '性别（稳定）',
    register_city STRING COMMENT '注册城市（稳定）',
    -- 频繁变化属性不在这里！
);

-- 微型维度表：存频繁变化的属性
CREATE TABLE dim_user_profile (
    profile_sk BIGINT COMMENT '代理键',
    user_level STRING COMMENT '当前等级（常变）',
    vip_level STRING COMMENT 'VIP等级（常变）',
    life_cycle STRING COMMENT '生命周期（常变）',
    rfm_tag STRING COMMENT 'RFM标签（常变）'
);

-- 事实表关联两个维度
CREATE TABLE dwd_trade_order_di (
    user_sk BIGINT COMMENT '关联主维度',
    profile_sk BIGINT COMMENT '关联微型维度',
    -- ...
);

效果：主维度表体积极大减小，微型维度表因为基数低（等级就那么几种）所以很小。SCD Type 2只在微型维度上生效，避免主维度表膨胀。

8.10 桥接表（Bridge Table）

解决多值维度问题——一个事实记录关联多个维度值。

经典场景：一笔订单使用了多张优惠券。怎么存？

方案	做法	问题
方案1	事实表里加 coupon_sk 字段	一个订单有多张券，字段不够放
方案2	优惠券信息拼成字符串存	没法做分析和统计
方案3	建桥接表	推荐，一张中间表解决

-- 桥接表：一笔订单可以关联多张优惠券
CREATE TABLE bridge_order_coupon (
    order_id BIGINT COMMENT '订单ID',
    coupon_sk BIGINT COMMENT '优惠券代理键',
    coupon_amount DECIMAL(18,4) COMMENT '该券抵扣金额',
    -- 权重字段（用于分摊计算）
    weight DECIMAL(5,4) COMMENT '权重（如按券面额占比）'
);

查询时：事实表 → 桥接表 → 维度表。通过权重字段可以把订单金额按券分摊。

8.11 维度层次（Hierarchy）

维度天然有层级关系。比如地区维度：

国家 → 省份 → 城市 → 区县

时间维度：

年 → 季度 → 月 → 周 → 日

商品维度：

一级类目 → 二级类目 → 三级类目 → SPU → SKU

层次的作用：

• 上卷（Roll-up）：从城市汇总到省份，看省级的整体数据
• 下钻（Drill-down）：从省份下钻到城市，看城市级的明细数据

在维度表里怎么存：

CREATE TABLE dim_region (
    region_sk BIGINT COMMENT '代理键',
    country STRING COMMENT '国家',
    province STRING COMMENT '省份',
    city STRING COMMENT '城市',
    district STRING COMMENT '区县',
    -- 每一级都存一个字段，方便直接GROUP BY
);

8.12 可加性（Additivity）

事实表里的度量值，不是都能随便相加的。分为三种：

类型	说明	举例
完全可加	任何维度都可以汇总	销售金额、商品件数
半可加	部分维度可加，部分不可加	库存数量（按时间加没意义，按商品加有意义）
不可加	任何维度都不能直接加	单价、比率、百分比

注意事项：

• 完全可加的度量可以直接SUM，这是最常见的
• 半可加的度量要注意汇总维度，库存按商品汇总有意义，按日期汇总没意义（会重复计算）
• 不可加的度量（如客单价 = 总金额/订单数）必须在最细粒度计算，不能先SUM再除

-- 错误：先SUM再除，结果不对
SELECT SUM(pay_amt) / SUM(order_cnt) AS wrong_avg  -- 错！

-- 正确：在最细粒度算，再取平均
SELECT AVG(user_avg_amt) AS right_avg
FROM (
    SELECT user_sk, SUM(pay_amt)/COUNT(DISTINCT order_id) AS user_avg_amt
    FROM dwd_trade_order_pay_di
    GROUP BY user_sk
) t;

8.13 SCD的其他类型

前面讲了SCD Type 1/2/3，实际还有几种变体：

类型	做法	什么时候用
Type 0	固定不变，永远不更新	注册日期、首次来源这种确定不变的属性
Type 2	新增行保留历史（主流）	需要完整历史追踪
Type 4	当前值存主表，历史值存历史表	历史查询少、关注当前值的场景
Type 6	Type 1 + Type 2 + Type 3 的组合	需要同时看当前值和历史值

Type 6 展开说明（因为名字怪所以单独说）：

Type 6 不是第六种独立方法，而是"Type 1 + Type 2 + Type 3"的组合（1+2+3=6）。

一张Type 6的维度表同时有：

• current_city（Type 1，总是最新值）
• historical_city + effective_date + expiry_date（Type 2，历史记录）
• previous_city（Type 3，上一次值）

CREATE TABLE dim_user_type6 (
    user_sk BIGINT COMMENT '代理键',
    user_id BIGINT COMMENT '业务ID',
    
    -- Type 1: 总是最新值
    current_city STRING COMMENT '当前城市（最新）',
    
    -- Type 2: 历史行
    city STRING COMMENT '该行的城市',
    effective_date STRING COMMENT '生效日期',
    expiry_date STRING COMMENT '失效日期',
    is_current TINYINT COMMENT '是否当前有效',
    
    -- Type 3: 上一次值
    previous_city STRING COMMENT '上一个城市'
);

实际建议：大部分场景SCD Type 2足够。除非业务同时要求看"最新值"“历史值”“上一次值”，才用Type 6。

8.14 衍生指标的分类

前面讲了原子指标和派生指标，实际工作中指标还可以按计算方式细分：

指标类型	怎么算的	举例
基础指标	直接从事实表SUM/COUNT	支付金额、下单笔数
复合指标	多个基础指标加减乘除	客单价 = 支付金额 / 支付笔数
比率指标	A / B * 100%	转化率 = 支付笔数 / 下单笔数
占比指标	部分 / 整体	无线端占比 = 无线端GMV / 总GMV
排名指标	按某个指标排序	销售额TOP10商品
同环比指标	本期 vs 上期/去年同期	同比增长率、环比增长率

注意：比率指标和占比指标属于不可加度量，不能直接SUM。必须在最细粒度算好再汇总。

---

本期就说到这里，欢迎大家指正～