电商数仓实战：5张核心表与3层架构的维度建模全解析
从业务需求到数据落地的完整实践路径
在电商行业的数据驱动决策中，一个设计良好的数据仓库（Data Warehouse）能够将分散的业务数据转化为可分析的黄金资产。与传统的理论讲解不同，我们将通过一个真实的电商案例，展示如何从零开始构建符合Kimball维度建模理论的数仓体系。这个实战过程将产出5张核心数据表（用户维度表、商品维度表、下单事实表、支付事实表、订单累计快照事实表），并严格遵循ODS/DWD/DWS三层规范的分层架构。
电商业务的核心分析场景通常围绕"人货场"展开：哪些用户（人）在什么时间（场）购买了哪些商品（货）？他们的购买频次、客单价如何？哪些商品组合经常被一起购买？要回答这些问题，我们需要将业务过程中产生的零散数据，通过维度建模的方法重新组织。维度建模的本质是将业务活动抽象为可度量的"事实"，并将事实发生的环境描述为"维度"，通过星型或雪花模型将两者关联起来。
提示：在电商数仓中，事务事实表记录下单、支付等离散事件，周期快照表捕捉库存等状态变化，而累计快照表则跟踪订单全生命周期
1. 业务梳理与数据域划分
1.1 电商核心业务流程解构
电商业务虽然复杂，但其核心流程可以抽象为几个关键业务过程：
- 用户旅程：注册 → 浏览 → 加购 → 下单 → 支付 → 收货 → 售后
- 商品流转：上架 → 展示 → 交易 → 配送 → 签收
- 资金流动：支付 → 结算 → 退款 → 佣金计提
通过与企业业务专家访谈，我们绘制出以下电商简化业务矩阵：
| 业务过程 | 日期维度 | 用户维度 | 商品维度 | 商家维度 | 地区维度 | 支付方式维度 |
|---|---|---|---|---|---|---|
| 用户注册 | ✓ | ✓ | ✓ | |||
| 商品浏览 | ✓ | ✓ | ✓ | ✓ | ||
| 购物车操作 | ✓ | ✓ | ✓ | |||
| 订单创建 | ✓ | ✓ | ✓ | ✓ | ✓ | |
| 支付完成 | ✓ | ✓ | ✓ | |||
| 物流配送 | ✓ | ✓ |
1.2 数据域划分原则与实践
根据业务矩阵，我们将电商数据划分为以下数据域：
- 用户域：用户基础属性、等级、行为标签等
- 商品域：类目、SPU、SKU、价格、库存等
- 交易域：订单、购物车、优惠券、支付等
- 物流域：仓库、配送、签收等
- 售后域：退货、退款、投诉等
每个数据域包含多个业务过程，例如交易域包含下单、支付、退款等过程。划分数据域的主要价值在于：
- 明确团队分工边界
- 构建一致性维度（如统一用户ID定义）
- 避免重复计算和口径不一致
-- 示例：用户域与交易域的关联关系 SELECT u.user_id, u.register_date, COUNT(DISTINCT o.order_id) AS order_count FROM dim_user u LEFT JOIN fact_order o ON u.user_id = o.user_id GROUP BY u.user_id, u.register_date;2. 维度表设计与实现
2.1 用户维度表（dim_user）
用户维度是电商分析的核心视角之一，包含用户静态属性和缓慢变化的动态属性：
CREATE TABLE dim_user ( user_sk BIGINT COMMENT '代理键', user_id BIGINT COMMENT '业务主键', username STRING COMMENT '用户名', gender STRING COMMENT '性别', birth_date DATE COMMENT '出生日期', register_date DATE COMMENT '注册日期', register_platform STRING COMMENT '注册平台', vip_level INT COMMENT '会员等级', is_active BOOLEAN COMMENT '是否活跃', current_address STRING COMMENT '当前地址', start_date DATE COMMENT '记录生效日期', end_date DATE COMMENT '记录失效日期', current_flag BOOLEAN COMMENT '当前有效标志' ) COMMENT '用户维度表' PARTITIONED BY (dt STRING COMMENT '分区日期');SCD（缓慢变化维）处理方案：
- Type 1：覆盖原值（适用于不关心历史的情况，如用户昵称修改）
- Type 2：新增记录（保留历史版本，如会员等级变化）
- Type 3：添加字段（仅保留上一个版本，如当前和上一次居住地）
2.2 商品维度表（dim_product）
商品维度需要处理多级类目和品牌信息：
CREATE TABLE dim_product ( product_sk BIGINT, product_id BIGINT, product_name STRING, category_id INT, category_name STRING, brand_id INT, brand_name STRING, price DECIMAL(10,2), cost DECIMAL(10,2), status TINYINT, online_time TIMESTAMP, offline_time TIMESTAMP, attributes MAP<STRING,STRING> COMMENT '商品扩展属性' ) COMMENT '商品维度表' PARTITIONED BY (dt STRING);反规范化设计：将商品类目层级（一级类目、二级类目等）平铺到单表中，避免关联查询带来的性能损耗。
3. 事实表设计与实现
3.1 下单事务事实表（fact_order_create）
记录每个订单创建事件，采用事务事实表设计：
CREATE TABLE fact_order_create ( order_id BIGINT, user_sk BIGINT COMMENT '用户代理键', product_sk BIGINT COMMENT '商品代理键', date_sk INT COMMENT '日期代理键', merchant_sk BIGINT COMMENT '商家代理键', quantity INT COMMENT '购买数量', amount DECIMAL(12,2) COMMENT '订单金额', discount DECIMAL(12,2) COMMENT '优惠金额', payment_type STRING COMMENT '支付方式', shipping_address STRING COMMENT '收货地址', order_time TIMESTAMP COMMENT '下单时间' ) COMMENT '订单事实表' PARTITIONED BY (dt STRING COMMENT '订单日期');事务事实表特点：
- 每行代表一个业务事件
- 数据一旦插入不会修改
- 包含可加性度量（数量、金额等）
3.2 支付成功事实表（fact_payment）
记录支付成功事件，与订单表形成事实星座：
CREATE TABLE fact_payment ( payment_id BIGINT, order_id BIGINT, user_sk BIGINT, date_sk INT, payment_amount DECIMAL(12,2), payment_method STRING, payment_time TIMESTAMP, is_first_payment BOOLEAN ) COMMENT '支付事实表' PARTITIONED BY (dt STRING);3.3 订单累计快照事实表（fact_order_snapshot）
跟踪订单全生命周期状态变化：
CREATE TABLE fact_order_snapshot ( order_id BIGINT, user_sk BIGINT, product_sk BIGINT, create_date_sk INT COMMENT '创建日期代理键', pay_date_sk INT COMMENT '支付日期代理键', deliver_date_sk INT COMMENT '发货日期代理键', receive_date_sk INT COMMENT '签收日期代理键', cancel_date_sk INT COMMENT '取消日期代理键', order_amount DECIMAL(12,2), payment_amount DECIMAL(12,2), shipping_fee DECIMAL(10,2), status STRING COMMENT '当前状态', time_to_pay INT COMMENT '下单到支付间隔(分钟)', time_to_deliver INT COMMENT '支付到发货间隔(小时)' ) COMMENT '订单累计快照表' PARTITIONED BY (dt STRING);累计快照表特点：
- 每行代表一个业务实体（如一个订单）
- 包含多个关键时间点的日期维度
- 会随着业务流程更新记录
4. 数仓分层与数据流转
4.1 ODS层：原始数据保留区
ODS（Operation Data Store）层保持源系统数据原貌，不做清洗转换：
CREATE TABLE ods_order_mysql ( order_id BIGINT, user_id BIGINT, product_id BIGINT, quantity INT, price DECIMAL(10,2), total_amount DECIMAL(12,2), create_time TIMESTAMP, update_time TIMESTAMP ) COMMENT '订单原始表' PARTITIONED BY (dt STRING);4.2 DWD层：明细数据层
DWD（Data Warehouse Detail）层对ODS数据进行清洗、转换、维度关联：
CREATE TABLE dwd_order_detail ( order_id BIGINT, user_sk BIGINT, product_sk BIGINT, date_sk INT, quantity INT, amount DECIMAL(12,2), discount DECIMAL(10,2), payment_type STRING, order_time TIMESTAMP, source_comment STRING COMMENT '数据来源标记' ) COMMENT '订单明细表' PARTITIONED BY (dt STRING);4.3 DWS层：汇总数据层
DWS（Data Warehouse Summary）层基于DWD层数据进行轻度汇总：
CREATE TABLE dws_user_order_d ( user_sk BIGINT, date_sk INT, order_count INT COMMENT '当日订单数', order_amount DECIMAL(16,2) COMMENT '当日订单金额', avg_order_amount DECIMAL(12,2) COMMENT '客单价', product_distinct_count INT COMMENT '购买商品种类数' ) COMMENT '用户日粒度订单汇总表' PARTITIONED BY (dt STRING);5. 模型优化与扩展实践
5.1 聚合事实表设计
为提高常用查询性能，设计预聚合表：
CREATE TABLE agg_sales_category_d ( date_sk INT, category_id INT, category_name STRING, order_count INT, user_count INT, sales_amount DECIMAL(16,2), refund_amount DECIMAL(16,2), gmv DECIMAL(16,2) ) COMMENT '类目日粒度销售聚合表' PARTITIONED BY (dt STRING);5.2 维度整合技巧
杂项维度：将多个低基数标志字段组合到一个维度中：
CREATE TABLE dim_order_flag ( flag_sk INT, is_first_order BOOLEAN, is_mobile BOOLEAN, is_promotion BOOLEAN, is_urgent BOOLEAN, flag_desc STRING ) COMMENT '订单标志杂项维度';5.3 实时与离线一体化设计
随着实时分析需求增加，可采用Lambda架构：
离线层（Hive） 实时层（Kafka） │ │ └─────> 服务层 <─────┘ (Doris)在实际电商数仓项目中，我曾遇到商品类目频繁调整导致的历史数据分析难题。通过采用Type 2缓慢变化维技术，我们完整保留了每个类目调整时间点的状态，使得历史报表能够真实反映当时的业务情况。这种设计虽然增加了存储成本，但为业务提供了准确的历史视角。
