数据仓库建模完整手册笔记：方法、流程、业务场景、技术痛点与解决方案

一、四大主流数仓建模方法体系

1.1 Inmon 3NF范式建模（企业统一EDW）

1.1.1 核心设计思想

• 自上而下建设，先搭建集团全局标准化底层数仓，严格遵循第三范式消除数据冗余，衍生雪花模型
• 统一企业实体标准后，再拆分各业务线数据集市

1.1.2 优劣势总结

• 优势：全局口径高度统一、历史数据完整可追溯、源系统迭代改动范围小
• 劣势：多表关联查询性能极差、业务人员理解门槛高、项目建设周期漫长

1.1.3 适配业务场景

• 国有银行监管底层数仓、集团财务统一数据中心、保险全渠道保单整合平台

1.1.4 模型结构文本示意图

【雪花模型-Inmon范式结构】
          fact_trade（交易主事实表）
       ↙        ↓        ↘
dim_user    dim_goods    dim_shop
    ↓           ↓
dim_user_vip  dim_category

1.2 Kimball维度建模（行业通用星型模型）

1.2.1 核心设计思想

• 自下而上以业务分析场景为核心，适度冗余换取查询效率，优先采用星型模型
• 核心组成：事务事实表、周期快照事实表、累积快照事实表、多类型维度表

1.2.2 优劣势总结

• 优势：关联表少查询速度快、业务可读性强、落地迭代速度快、适配报表自助分析
• 劣势：缺少统一管控时易出现口径冲突，强依赖一致性维度体系

1.2.3 适配业务场景

• 电商交易经营分析、线下连锁零售、政企经营报表、互联网用户行为分析

1.2.4 模型结构文本示意图

【星型模型-Kimball维度建模】
               fact_order_item（订单明细事实）
     ┌───────────┬───────────┬───────────┬───────────┐
dim_date    dim_user     dim_goods    dim_shop    dim_promotion
日期维度    用户维度    商品维度    店铺维度    活动维度

1.3 Data Vault建模（多源异构数据融合）

1.3.1 核心设计思想

• 三层分层架构：Hub业务主键表、Link关联关系表、Satellite属性快照表
• 天然存储全量历史快照，兼容多套结构差异巨大的业务源系统

1.3.2 优劣势总结

• 优势：横向扩展能力极强、源系统结构变更无需重构模型、完整留存全历史数据
• 劣势：模型抽象难懂、查询需大量多表JOIN、计算资源消耗高

1.3.3 适配业务场景

• 多子集团数据融合、制造业多产线异构设备数据、金融多渠道客户数据整合

1.3.4 模型结构文本示意图

【Data Vault三层架构示意图】
Hub层：hub_user(user_id),hub_order(order_id),hub_goods(goods_id)
Link层：link_order_user(order_id,user_id,load_dt)
Sat层：sat_user_info(user_id,name,phone,start_dt,end_dt)
       sat_order_detail(order_id,pay_amt,status,start_dt)

1.4 宽表建模（实时数仓专用）

1.4.1 核心设计思想

• 极致反范式设计，将事实指标与全部关联维度平铺至单张大宽表，消除查询JOIN操作
• 基于Flink流计算实时关联维度，存储于ClickHouse/Doris/StarRocks等OLAP引擎

1.4.2 优劣势总结

• 优势：毫秒级查询响应、适配实时大屏、自助实时指标快速查询
• 劣势：数据冗余度极高、维度变更需全量重刷宽表、存储成本翻倍上涨

1.4.3 适配业务场景

• 电商大促实时GMV监控、短视频流量实时看板、线上营销活动实时效果监控

1.4.4 模型结构文本示意图

【实时交易大宽表字段结构】
dws_trade_real_time_wide
dt,hour,order_id,user_id,user_name,vip_level,goods_id,goods_name,
category_name,shop_name,pay_amount,order_cnt,refund_amount,pay_channel

二、数仓建模全落地标准流程

2.1 阶段1：业务调研与主题域划分

2.1.1 执行步骤

• 对接业务、产品、运营、财务收集报表、看板、取数需求清单
• 梳理全量业务源系统、数据表、同步方式、更新频率
• 按业务边界拆分一级主题域，向下拆分二级业务过程

2.1.2 电商行业主题域拆分示例

• 交易域：下单、支付、退款、发货、售后
• 用户域：注册、登录、会员等级、用户画像
• 商品域：商品上架、分类、库存、价格变更
• 营销域：优惠券、限时活动、满减补贴
• 物流域：揽收、中转、签收、物流异常

2.1.3 阶段输出交付物

业务流程图、源系统ER清单、全局指标需求字典

2.2 阶段2：业务过程梳理与粒度定义

2.2.1 执行步骤

• 提取每个主题下全部可量化业务事件（业务过程）
• 为每个业务过程定义最小粒度，粒度决定事实表存储层级

2.2.2 粒度划分实战案例（电商交易）

• 订单明细事实：粒度=单商品单笔下单记录（最细粒度，可拆分品类、单品指标）
• 订单主事实：粒度=一张完整订单
• 支付快照事实：粒度=单次支付流水
• 日库存周期快照：粒度=单商品单店铺每日库存存量

2.3 阶段3：总线矩阵设计，区分事实与维度

2.3.1 执行步骤

• 提取全局一致性维度，全业务主题统一复用
• 梳理每个业务过程关联维度，生成总线矩阵
• 区分维度实体（描述型属性）、事实实体（可量化度量指标）

2.3.2 电商全局一致性维度清单

dim_date（日期）、dim_user（用户SCD2）、dim_goods（商品SCD2）、dim_shop（店铺）

2.3.3 总线矩阵文本示例

业务过程|关联维度|事实表类型
订单明细下单|日期/用户/商品/店铺/活动|事务事实表
日商品库存|日期/商品/仓库|周期快照事实表
订单全生命周期|日期/用户/商品|累积快照事实表

2.4 阶段4：维度专项设计（SCD缓慢变化维核心）

2.4.1 SCD1 直接覆盖更新

• 适用场景：源数据错误修正，无需留存历史记录（用户手机号录入错误修正）
• 更新逻辑SQL代码

UPDATE dim_user_full_d
SET user_phone = '13800138999'
WHERE user_id = 10001 AND dt = '2026-06-24';

2.4.2 SCD2 新增历史行（行业标准方案）

• 适用场景：商品名称变更、用户会员等级升级、店铺经营范围变更，需完整留存历史
• 核心维度表字段：业务主键、属性字段、start_dt生效日期、end_dt失效日期、is_current是否当前有效
• 完整Hive建表代码

CREATE TABLE dim_goods_full_d (
    goods_id BIGINT COMMENT '商品唯一主键',
    goods_name STRING COMMENT '商品名称',
    category_id BIGINT COMMENT '商品分类ID',
    category_name STRING COMMENT '分类名称',
    sale_price DECIMAL(18,2) COMMENT '商品售价',
    start_dt STRING COMMENT '属性生效日期',
    end_dt STRING COMMENT '属性失效日期',
    is_current TINYINT COMMENT '1=当前有效，0=历史数据'
) COMMENT '商品缓慢变化维度SCD2'
PARTITIONED BY (dt STRING)
STORED AS ORC TBLPROPERTIES ('orc.compress'='snappy');

2.4.3 SCD3 新增旧值字段（极少使用）

• 适用场景：仅需保留上一版本属性，无需多版本追溯
• 设计方案：新增old_member_level字段存储上一次会员等级

2.4.4 退化维度落地规则

• 单据编号（订单号、支付单号）不单独建维度表，直接下沉至事实表减少关联

2.5 阶段5：OneData五层分层模型落地

2.5.1 分层流转文本示意图

ODS贴源层（原始未清洗数据）
    ↓清洗、脱敏、标准化、去脏数据
DWD明细层（最细粒度事务事实）
    ↓轻度聚合、预计算高频指标
DWM中间汇总层
    ↓多维度拼接、全量指标聚合宽表
DWS汇总宽表层
    ↓业务定制筛选、衍生指标二次计算
ADS应用集市层（对外报表/API输出）

2.5.2 ODS 贴源层

• 定位：镜像业务源库，无清洗逻辑，完整保留原始字段
• 同步方式：DataX全量同步、Canal Binlog增量同步
• 电商表名示例：ods_t_order_item_inc_d（订单明细增量表）

2.5.3 DWD 明细事实层

• 定位：脏数据过滤、敏感信息脱敏、字段标准化、构建星型明细事实
• 订单明细事实表完整建表代码

CREATE TABLE dwd_fact_order_item_inc_d (
    order_item_id BIGINT COMMENT '订单明细主键',
    order_id BIGINT COMMENT '订单编号(退化维)',
    dt STRING COMMENT '下单日期分区',
    user_id BIGINT COMMENT '用户维度外键',
    goods_id BIGINT COMMENT '商品维度外键',
    shop_id BIGINT COMMENT '店铺维度外键',
    buy_num INT COMMENT '下单件数',
    origin_amount DECIMAL(18,2) COMMENT '商品原价总额',
    real_pay_amount DECIMAL(18,2) COMMENT '实付金额',
    discount_amount DECIMAL(18,2) COMMENT '优惠抵扣金额',
    order_status TINYINT COMMENT '订单状态 1待支付 2已支付 3已退款'
) COMMENT '交易域-订单明细增量事实表'
PARTITIONED BY (dt STRING)
CLUSTER BY (user_id) INTO 32 BUCKETS
STORED AS ORC TBLPROPERTIES ('orc.compress'='snappy');

2.5.4 DWM 轻度汇总层

• 定位：按用户、商品、门店等高频维度预聚合，减少上层重复计算
• 示例表：dwm_user_day_order_inc（用户每日下单汇总表）

2.5.5 DWS 汇总宽表层

• 定位：多维度平铺聚合，单表覆盖绝大多数报表查询需求
• 店铺日交易宽表建表代码

CREATE TABLE dws_trade_shop_day_wide (
    dt STRING COMMENT '统计日期',
    shop_id BIGINT COMMENT '店铺ID',
    shop_name STRING COMMENT '店铺名称',
    first_category STRING COMMENT '一级商品分类',
    total_order_cnt BIGINT COMMENT '有效订单总数',
    gmv_amount DECIMAL(18,2) COMMENT '当日GMV总额',
    total_pay_amt DECIMAL(18,2) COMMENT '实际支付总金额',
    total_refund_amt DECIMAL(18,2) COMMENT '当日退款总金额',
    pay_user_count BIGINT COMMENT '支付去重用户数'
) COMMENT '交易域-店铺日交易汇总宽表'
PARTITIONED BY (dt STRING)
STORED AS ORC TBLPROPERTIES ('orc.compress'='snappy');

2.5.6 ADS 应用集市层

• 定位：面向独立业务需求定制，输出报表、数据API、业务库同步数据
• 示例表：ads_operation_daily_gmv_report（运营日报GMV报表）

2.6 阶段6：物理建模规范与数据调度开发

2.6.1 物理建模通用规范

• 分区规则：离线表按dt日期分区；实时表按小时分区
• 分桶规则：大事实表按user_id、goods_id分桶优化JOIN性能
• 存储引擎：Hive离线使用ORC+Snappy压缩；实时选用ClickHouse/Doris
• 字段规范：金额统一decimal(18,2)，ID统一BIGINT，状态值使用TINYINT

2.6.2 完整调度依赖链路文本

ODS数据同步任务 → DWD明细清洗任务 → DWM轻度汇总任务 → DWS宽表聚合任务 → ADS报表产出任务
调度工具：Airflow/Azkaban/DataWorks，每日凌晨6点完成全量指标产出

2.7 阶段7：数据服务输出与迭代治理

2.7.1 数据输出应用方式

• BI看板直连DWS/ADS宽表，展示经营趋势、品类销量排行
• 封装ADS专题表为数据API，供给业务系统实时查询
• DataX同步ADS报表至MySQL，运营后台直接读取

2.7.2 迭代维护流程

• 源系统新增字段：同步更新ODS、DWD层兼容新增字段
• 新增业务指标：DWS层扩展指标字段，ADS同步产出对应报表
• 数据治理：定期清理脏数据、统一维度编码、下线长期无用冗余字段

三、全行业细分业务场景+建模落地方案

3.1 场景1：综合电商平台（淘宝/京东类）

3.1.1 核心业务需求

• 经营分析：GMV、销量、客单价、品类排行、退款率、复购率
• 用户分析：分层用户消费频次、会员转化、生命周期价值
• 实时需求：618/双11实时GMV大屏、活动优惠券实时核销监控

3.1.2 建模组合方案

• 离线分析：Kimball星型维度建模 + OneData五层分层架构
• 实时大屏：Flink流计算宽表建模，存储引擎选用Doris
• 集团多子商城底层：Data Vault整合多套异构订单库

3.1.3 指标计算SQL示例（DWS层日店铺汇总）

INSERT OVERWRITE TABLE dws_trade_shop_day_wide PARTITION(dt='2026-06-24')
SELECT
    t.dt,
    t.shop_id,
    d.shop_name,
    d2.first_category,
    COUNT(DISTINCT t.order_id) AS total_order_cnt,
    SUM(t.origin_amount) AS gmv_amount,
    SUM(t.real_pay_amount) AS total_pay_amt,
    SUM(IF(t.order_status=3,t.real_pay_amount,0)) AS total_refund_amt,
    COUNT(DISTINCT t.user_id) AS pay_user_count
FROM dwd_fact_order_item_inc_d t
LEFT JOIN dim_shop_full_d d ON t.shop_id = d.shop_id AND d.is_current=1 AND d.dt='2026-06-24'
LEFT JOIN dim_goods_full_d d2 ON t.goods_id = d2.goods_id AND d2.is_current=1 AND d2.dt='2026-06-24'
WHERE t.dt='2026-06-24' AND t.order_status IN (2,3)
GROUP BY t.dt,t.shop_id,d.shop_name,d2.first_category;

3.2 场景2：线下连锁零售（超市/服装门店）

3.2.1 核心业务需求

• 门店日销、库存周转、会员消费、节假日促销效果评估
• 每日门店库存快照、跨门店商品调拨损耗分析

3.2.2 建模组合方案

• 主体采用Kimball维度建模
• 使用周期快照事实表存储每日门店库存数据
• SCD2会员维度记录会员等级、手机号、地址变更完整历史

3.2.3 零售星型模型文本示意图

【线下零售星型模型】
fact_store_snap_daily(门店日库存快照事实)
    ↙        ↓        ↘
dim_store(门店) dim_goods(商品SCD2) dim_date(日期)

3.3 场景3：国有银行金融核心数仓

3.3.1 核心业务需求

• 监管报送报表、客户资产流水、贷款全生命周期跟踪、资金风险追溯
• 强数据一致性约束，永久完整留存全量历史数据

3.3.2 建模组合方案

• 底层EDW采用Inmon 3NF范式建模+Data Vault双模型存储原始流水
• 上层报表集市使用Kimball维度建模产出监管指标
• 禁用宽表模型，规避数据一致性风险

3.4 场景4：短视频内容APP（抖音/快手类）

3.4.1 核心业务需求

• 实时播放量、点赞、评论、直播间在线人数、流量转化漏斗
• 低延迟实时数据大屏，秒级刷新流量指标

3.4.2 建模组合方案

• 全链路实时宽表建模，Flink CDC读取埋点日志，流JOIN维度表
• 存储引擎选用ClickHouse，不使用复杂星型/雪花模型

3.5 场景5：智能制造多工厂集团

3.5.1 核心业务需求

• 多产线设备运行指标、工单生产记录、设备故障全历史追溯
• 各工厂系统数据库结构差异大，源系统频繁迭代更新

3.5.2 建模组合方案

• 底层统一接入层使用Data Vault整合设备、工单异构数据
• 上层DWS生产汇总宽表供给生产部门分析产能、设备故障率

四、数仓建模高频技术痛点、根因分析与落地解决方案

4.1 痛点1：多业务线维度口径不统一，指标对账差异巨大

4.1.1 痛点现象

• 同一份GMV指标，交易、运营、财务部门产出数值不一致
• 用户、商品、店铺编码多套标准，跨主题联表查询匹配失败

4.1.2 根因分析

• 未落地一致性维度体系，各业务线独立开发维度表
• 无统一指标字典，原子指标、计算逻辑无标准化约束

4.1.3 完整解决方案

• 搭建全局维度总线矩阵，统一dim_date、dim_user、dim_goods等核心维度全业务复用
• 建立指标管理平台，统一定义原子指标、衍生指标、复合指标口径
• 强制DWD层关联全局统一维度表，禁止各业务线自建维度

4.1.4 落地示例代码（统一关联标准维度）

-- DWD层必须关联当日有效全局商品维度，禁止本地维度表
SELECT
    t.*,
    d.goods_name,
    d.category_name
FROM dwd_fact_order_item_inc_d t
LEFT JOIN dim_goods_full_d d
    ON t.goods_id = d.goods_id
    AND d.is_current = 1
    AND d.dt = '${dt}';

4.2 痛点2：缓慢变化维历史数据丢失，无法追溯变更记录

4.2.1 痛点现象

• 商品改价、用户会员升级后，无法查询变更前的历史属性，复盘分析失真
• 使用SCD1直接覆盖更新，历史数据永久丢失

4.2.2 根因分析

• 维度设计仅采用SCD1覆盖更新方案，未做历史快照留存
• 无start_dt、end_dt、is_current版本管控字段

4.2.3 完整解决方案

• 核心实体维度统一采用SCD2模型，新增行保存历史版本
• 每日全量刷新维度表，自动切割旧数据失效时间，新增当前生效数据
• 提供视图封装当前有效维度，简化上层查询逻辑

4.2.4 SCD2每日合并更新代码

-- 步骤1：将旧维度数据失效时间更新为前一日
INSERT OVERWRITE TABLE dim_goods_full_d PARTITION(dt='${dt}')
SELECT
    goods_id,
    goods_name,
    category_id,
    category_name,
    sale_price,
    start_dt,
    CASE WHEN is_current=1 THEN date_sub('${dt}',1) ELSE end_dt END AS end_dt,
    CASE WHEN is_current=1 THEN 0 ELSE is_current END AS is_current
FROM dim_goods_full_d
WHERE dt = date_sub('${dt}',1)

UNION ALL

-- 步骤2：插入当日最新变更商品数据（当前有效）
SELECT
    goods_id,
    goods_name,
    category_id,
    category_name,
    sale_price,
    '${dt}' AS start_dt,
    '9999-12-31' AS end_dt,
    1 AS is_current
FROM ods_t_goods_full_d;

4.3 痛点3：大宽表存储冗余过高，维度变更全量重刷成本极高

4.3.1 痛点现象

• 实时宽表字段超200个，存储容量翻倍；商品分类变更需重刷近30天全量宽表
• 流计算任务Checkpoint过大，任务频繁重启、延迟上涨

4.3.2 根因分析

• 所有维度全量平铺进宽表，未区分高频/低频变更维度
• 无分层拆分设计，单一宽表承载全部维度与指标

4.3.3 完整解决方案

• 分层拆分实时模型：明细流事实表 + 轻度维度宽表 + 上层聚合宽表
• 低频变更维度（店铺、分类）离线预加载至维表，减少流JOIN压力
• 冷热数据分层归档，超过7天历史宽表归档至Hive离线存储

4.3.4 分层实时模型文本示意图

分层实时宽表优化架构
1. dwd_real_order_item：纯明细流事实（仅主键、指标，无冗余维度）
2. dws_real_order_dim_small：高频小维度流JOIN（用户、支付渠道）
3. ads_real_trade_wide：离线低频维度左联聚合最终宽表

4.4 痛点4：底层3NF范式多表JOIN，报表查询超时、资源耗尽

4.4.1 痛点现象

• 基于Inmon雪花模型开发报表，单SQL关联8张以上表，查询执行超30分钟
• 集群计算资源被大SQL占满，日常调度任务大面积延迟

4.4.2 根因分析

• 底层直接对外提供查询，未搭建DWM/DWS预聚合汇总层
• 模型过度范式化，未做适度反范式汇总优化

4.4.3 完整解决方案

• 底层EDW仅用于数据存储，所有报表查询统一读取DWM/DWS预聚合层
• 常用分析维度提前按日/小时预聚合，减少线上多表关联
• 对超复杂报表单独构建ADS专属汇总表，直接输出最终指标

4.5 痛点5：源系统频繁迭代，底层模型重构成本高、断数风险大

4.5.1 痛点现象

• 业务库新增字段、分库分表、字段重命名后，底层模型需全链路修改，极易出现断数
• 多源异构系统结构差异大，统一整合难度高

4.5.2 根因分析

• 采用Kimball/3NF底层模型，源系统变更直接冲击上层所有表
• 无兼容层隔离源系统与业务分析模型

4.5.3 完整解决方案

• 多源异构场景底层采用Data Vault模型，源系统变更仅修改Sat卫星表，上层无感知
• ODS层增加兼容适配逻辑，字段新增/重命名做映射转换，隔离上层模型
• 分阶段灰度同步新源数据，新旧模型并行运行校验数据一致性

4.6 痛点6：数据粒度混乱，明细、汇总指标口径重叠，对账困难

4.6.1 痛点现象

• 部分事实表粒度混用，一张表同时存储订单粒度、商品明细粒度数据
• 明细层、汇总层指标重复计算，数值无法对齐，对账工作量巨大

4.6.2 根因分析

• 建模初期未明确定义最小粒度，一张事实表承载多种业务过程
• 分层职责模糊，DWD/DWM/DWS指标重复开发

4.6.3 完整解决方案

• 一事实表对应单一业务过程与唯一粒度，禁止混合多粒度数据
• 分层职责严格隔离：DWD仅存明细、DWM轻度聚合、DWS高度汇总、ADS业务定制
• 统一指标计算入口，所有聚合指标仅在DWM/DWS层计算，ADS不再二次聚合

五、建模项目完整交付物清单

• 1.业务调研文档、企业主题域划分文档
• 2.总线矩阵文档、星型/雪花/DataVault逻辑ER文本示意图
• 3.维度专项设计文档（SCD1/SCD2/SCD3落地规范）
• 4.OneData分层设计说明书（ODS/DWD/DWM/DWS/ADS分层职责）
• 5.全量物理建表SQL脚本、全局字段字典、数据地图
• 6.调度依赖流程图、任务执行规范文档
• 7.全局指标字典（原子指标、衍生指标、复合指标口径定义）
• 8.数据生命周期、存储压缩、冷热归档治理规范
• 9.建模痛点解决方案落地操作手册、数据对账校验脚本