从 0 搭建企业级数仓架构，ODS/DWD/DWS/ADS 分层详解
📌 前言
为什么你的 SQL 越来越难维护？
为什么每次加需求都要改一堆表？
为什么数据口径对不上？
根本原因：没有分层设计！
这篇文章带你从零设计一套完整的数据仓库分层架构，包含：
- ✅ 4 层架构详解（ODS/DWD/DWS/ADS）
- ✅ 每层设计规范与命名规则
- ✅ 电商数仓完整案例
- ✅ 避坑指南
学完就能用，直接抄作业！
🎯 为什么要分层？
不分层的后果
❌ 问题 1：烟囱式开发
需求 A → 表 A1, A2, A3
需求 B → 表 B1, B2, B3
需求 C → 表 C1, C2, C3
结果：100 个需求 = 300 张表，数据冗余严重
❌ 问题 2：口径不一致
GMV 指标在 10 个表中有 5 种计算方式
老板问：哪个数字是对的？
❌ 问题 3：维护成本高
源表结构变更 → 修改 50 个下游任务
改一个字段，加班一周
分层的好处
✅ 清晰的数据血缘
ODS → DWD → DWS → ADS
数据从哪来、到哪去，一目了然
✅ 口径统一
核心指标在 DWS 层统一定义
下游直接复用，不会出错
✅ 降低耦合
源表变更 → 只改 ODS→DWD
下游无感知
✅ 复用性强
公共逻辑下沉到 DWS
新需求开发效率提升 50%
🏗️ 四层架构详解
┌─────────────────────────────────────────────────────────┐
│ ADS 应用层 │
│ 面向业务场景，直接支撑报表、大屏、数据分析 │
│ 例：电商 GMV 日报、用户留存分析表 │
└─────────────────────────────────────────────────────────┘
↓ ↑
┌─────────────────────────────────────────────────────────┐
│ DWS 汇总层 │
│ 面向主题，轻度聚合，公共指标统一计算 │
│ 例：用户日汇总表、商品日汇总表 │
└─────────────────────────────────────────────────────────┘
↓ ↑
┌─────────────────────────────────────────────────────────┐
│ DWD 明细层 │
│ 面向业务过程，清洗标准化，保持明细粒度 │
│ 例：订单事实表、用户维度表 │
└─────────────────────────────────────────────────────────┘
↓ ↑
┌─────────────────────────────────────────────────────────┐
│ ODS 原始层 │
│ 贴近源系统，原样同步，保留历史快照 │
│ 例：ods_order_db、ods_user_log │
└─────────────────────────────────────────────────────────┘
📊 第一层：ODS（原始数据层）
定位
- 全称： Operational Data Store
- 作用： 原样同步源系统数据，不做或少做处理
- 特点： 与源表结构基本一致，保留历史变更
设计规范
-- 命名规范
ods_{源系统名}_{表名}_{更新频率}
-- 示例
ods_mysql_order_info_df -- 日增量表
ods_mysql_user_info_di -- 日全量表
ods_log_app_start_df -- 日志日表
表结构设计
CREATE TABLE ods_order_info_df (
-- 业务字段（原样同步）
order_id STRING COMMENT '订单 ID',
user_id STRING COMMENT '用户 ID',
amount DECIMAL(18,2) COMMENT '订单金额',
status INT COMMENT '订单状态',
create_time STRING COMMENT '创建时间',
-- 分区字段
dt STRING COMMENT '日期分区，格式：yyyy-MM-dd',
-- 系统字段（新增）
etl_create_time TIMESTAMP COMMENT 'ETL 创建时间',
etl_update_time TIMESTAMP COMMENT 'ETL 更新时间'
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET;
同步策略
| 表类型 | 同步方式 | 分区策略 | 保留周期 |
|---|---|---|---|
| 业务表（增量） | 每日增量 | 按天分区 | 永久 |
| 业务表（全量） | 每日全量 | 按天分区 | 最近 30 天 |
| 日志表 | 实时/批量 | 按小时/天 | 最近 90 天 |
| 配置表 | 变更时同步 | 单分区 | 永久 |
注意事项
✅ 字段类型尽量与源系统保持一致
✅ 时间字段统一转为 STRING（避免时区问题）
✅ 必须添加分区字段 dt
✅ 必须添加 ETL 系统字段
❌ 不要在 ODS 层做数据清洗
❌ 不要在 ODS 层做关联操作
📊 第二层：DWD（明细数据层）
定位
- 全称： Data Warehouse Detail
- 作用： 数据清洗、标准化、维度退化
- 特点： 保持明细粒度，数据质量高
核心工作
1. 数据清洗
- 去除空值、异常值
- 统一字段格式（日期、金额）
- 处理脏数据
2. 标准化
- 统一字典映射（性别：0/1 → M/F）
- 统一单位（分→元、字节→MB）
- 统一命名规范
3. 维度退化
- 将常用维度字段冗余到事实表
- 减少下游关联次数
设计规范
-- 命名规范
dwd_{业务域}_{数据域}_{表名}_{更新频率}
-- 示例
dwd_trade_order_info_df -- 交易域订单表
dwd_user_login_log_df -- 用户域登录日志
dwd_product_sku_info_di -- 商品域 SKU 全量表
事实表设计
-- 交易域订单事实表
CREATE TABLE dwd_trade_order_info_df (
-- 业务主键
order_id STRING COMMENT '订单 ID',
-- 维度退化字段
user_id STRING COMMENT '用户 ID',
user_name STRING COMMENT '用户名（冗余）',
user_level INT COMMENT '用户等级（冗余）',
product_id STRING COMMENT '商品 ID',
product_name STRING COMMENT '商品名（冗余）',
category_id STRING COMMENT '类目 ID',
category_name STRING COMMENT '类目名（冗余）',
-- 度量字段
original_amount DECIMAL(18,2) COMMENT '原始金额',
discount_amount DECIMAL(18,2) COMMENT '优惠金额',
pay_amount DECIMAL(18,2) COMMENT '实付金额',
freight_amount DECIMAL(18,2) COMMENT '运费',
-- 状态字段
order_status INT COMMENT '订单状态',
pay_status INT COMMENT '支付状态',
-- 时间字段（统一格式）
create_time TIMESTAMP COMMENT '创建时间',
pay_time TIMESTAMP COMMENT '支付时间',
finish_time TIMESTAMP COMMENT '完成时间',
-- 分区字段
dt STRING COMMENT '日期分区'
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET;
维度表设计
-- 用户维度表（拉链表）
CREATE TABLE dwd_dim_user_info_di (
user_id STRING COMMENT '用户 ID',
user_name STRING COMMENT '用户名',
gender STRING COMMENT '性别',
age INT COMMENT '年龄',
city STRING COMMENT '城市',
user_level INT COMMENT '用户等级',
-- 拉链字段
start_date STRING COMMENT '生效开始日期',
end_date STRING COMMENT '生效结束日期',
is_current INT COMMENT '是否当前版本：1-是，0-否'
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET;
数据清洗示例
-- 从 ODS 到 DWD 的 ETL 处理
INSERT OVERWRITE TABLE dwd_trade_order_info_df PARTITION (dt='2026-03-24')
SELECT
t1.order_id,
t1.user_id,
t2.user_name, -- 维度退化
t2.user_level, -- 维度退化
t1.product_id,
t3.product_name, -- 维度退化
t3.category_id,
t3.category_name, -- 维度退化
t1.amount / 100, -- 单位转换：分→元
t1.discount_amount / 100,
t1.pay_amount / 100,
CASE
WHEN t1.status = 1 THEN 10 -- 状态标准化
WHEN t1.status = 2 THEN 20
ELSE 0
END AS order_status,
FROM_UNIXTIME(t1.create_time, 'yyyy-MM-dd HH:mm:ss') AS create_time, -- 时间格式化
FROM_UNIXTIME(t1.pay_time, 'yyyy-MM-dd HH:mm:ss') AS pay_time
FROM ods_order_info_df t1
LEFT JOIN dwd_dim_user_info_di t2 ON t1.user_id = t2.user_id AND t2.is_current = 1
LEFT JOIN dwd_dim_product_info_di t3 ON t1.product_id = t3.product_id AND t3.is_current = 1
WHERE t1.dt = '2026-03-24'
AND t1.order_id IS NOT NULL -- 数据清洗：去空
AND t1.pay_amount > 0; -- 数据清洗：去异常
📊 第三层：DWS（汇总数据层）
定位
- 全称： Data Warehouse Service
- 作用： 按主题轻度聚合，统一指标口径
- 特点： 面向分析场景，提升查询性能
核心思想
❌ 错误做法：每个报表单独计算
报表 A：SUM(amount) GROUP BY user_id
报表 B：SUM(amount) GROUP BY user_id
报表 C：COUNT(order_id) GROUP BY user_id
结果：同样的逻辑计算 3 次
✅ 正确做法：DWS 层统一聚合
DWS 层：用户日汇总表（包含 GMV、订单数、支付次数等）
报表 A/B/C：直接查询 DWS 表，简单加工即可
设计规范
-- 命名规范
dws_{业务域}_{聚合维度}_{时间范围}_{指标}
-- 示例
dws_trade_user_1d_gmv -- 交易域用户日粒度 GMV 汇总
dws_trade_product_7d_stat -- 交易域商品周粒度统计
dws_user_login_30d_agg -- 用户域登录月粒度聚合
用户日汇总表设计
CREATE TABLE dws_trade_user_1d_gmv (
-- 维度字段
user_id STRING COMMENT '用户 ID',
user_level INT COMMENT '用户等级',
city STRING COMMENT '城市',
-- 交易指标
gmv DECIMAL(18,2) COMMENT 'GMV（下单金额）',
pay_amount DECIMAL(18,2) COMMENT '实付金额',
order_count BIGINT COMMENT '下单次数',
pay_order_count BIGINT COMMENT '支付订单数',
refund_count BIGINT COMMENT '退款次数',
refund_amount DECIMAL(18,2) COMMENT '退款金额',
-- 商品指标
product_count BIGINT COMMENT '购买商品数',
category_count BIGINT COMMENT '购买类目数',
-- 时间指标
first_pay_time TIMESTAMP COMMENT '首次支付时间',
last_pay_time TIMESTAMP COMMENT '最后支付时间',
-- 分区字段
dt STRING COMMENT '日期分区'
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET;
聚合逻辑
-- 从 DWD 到 DWS 的 ETL
INSERT OVERWRITE TABLE dws_trade_user_1d_gmv PARTITION (dt='2026-03-24')
SELECT
user_id,
MAX(user_level) AS user_level,
MAX(city) AS city,
SUM(gmv) AS gmv,
SUM(pay_amount) AS pay_amount,
COUNT(order_id) AS order_count,
COUNT(IF(pay_status = 1, order_id, NULL)) AS pay_order_count,
COUNT(IF(order_status = -1, order_id, NULL)) AS refund_count,
SUM(IF(order_status = -1, pay_amount, 0)) AS refund_amount,
COUNT(DISTINCT product_id) AS product_count,
COUNT(DISTINCT category_id) AS category_count,
MIN(IF(pay_status = 1, create_time, NULL)) AS first_pay_time,
MAX(IF(pay_status = 1, pay_time, NULL)) AS last_pay_time
FROM dwd_trade_order_info_df
WHERE dt = '2026-03-24'
GROUP BY user_id;
常见汇总维度
| 维度类型 | 时间范围 | 示例表 |
|---|---|---|
| 用户维度 | 1 日/7 日/30 日 | dws_trade_user_1d_gmv |
| 商品维度 | 1 日/7 日/30 日 | dws_trade_product_1d_stat |
| 类目维度 | 1 日/7 日/30 日 | dws_trade_category_1d_stat |
| 地区维度 | 1 日/7 日/30 日 | dws_trade_city_1d_gmv |
| 渠道维度 | 1 日/7 日/30 日 | dws_trade_channel_1d_stat |
📊 第四层：ADS（应用数据层）
定位
- 全称： Application Data Store
- 作用： 面向具体业务场景，直接支撑报表
- 特点： 高度聚合，结果导向
设计规范
-- 命名规范
ads_{业务场景}_{报表名称}_{更新频率}
-- 示例
ads_trade_gmv_daily_report_df -- 交易域 GMV 日报
ads_user_retention_analysis_df -- 用户留存分析
ads_product_rank_top100_df -- 商品销量 TOP100
GMV 日报表设计
CREATE TABLE ads_trade_gmv_daily_report_df (
-- 统计维度
stat_date STRING COMMENT '统计日期',
dimension_type STRING COMMENT '维度类型：day/week/month',
-- 核心指标
gmv DECIMAL(18,2) COMMENT 'GMV',
gmv_day_over_day DECIMAL(10,4) COMMENT '环比（日）',
gmv_week_over_week DECIMAL(10,4) COMMENT '环比（周）',
gmv_year_over_year DECIMAL(10,4) COMMENT '同比',
pay_amount DECIMAL(18,2) COMMENT '实付金额',
order_count BIGINT COMMENT '订单数',
pay_user_count BIGINT COMMENT '支付用户数',
avg_order_value DECIMAL(18,2) COMMENT '客单价',
-- 更新时间
update_time TIMESTAMP COMMENT '更新时间'
);
报表计算逻辑
-- 从 DWS 到 ADS 的 ETL
INSERT INTO TABLE ads_trade_gmv_daily_report_df
SELECT
'${stat_date}' AS stat_date,
'day' AS dimension_type,
SUM(gmv) AS gmv,
(SUM(gmv) - LAG(SUM(gmv), 1) OVER ()) / LAG(SUM(gmv), 1) OVER () AS gmv_day_over_day,
(SUM(gmv) - LAG(SUM(gmv), 7) OVER ()) / LAG(SUM(gmv), 7) OVER () AS gmv_week_over_week,
(SUM(gmv) - LAG(SUM(gmv), 365) OVER ()) / LAG(SUM(gmv), 365) OVER () AS gmv_year_over_year,
SUM(pay_amount) AS pay_amount,
SUM(order_count) AS order_count,
COUNT(DISTINCT user_id) AS pay_user_count,
SUM(pay_amount) / SUM(order_count) AS avg_order_value,
NOW() AS update_time
FROM dws_trade_user_1d_gmv
WHERE dt >= DATE_SUB('${stat_date}', 365)
GROUP BY stat_date;
🏗️ 电商数仓完整案例
业务场景
某电商平台，日订单量 100 万，需要搭建数据仓库支撑：
- 每日 GMV 报表
- 用户行为分析
- 商品销量排行
- 运营活动效果分析
整体架构
┌─────────────────────────────────────────────────────────────┐
│ ADS 应用层 │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ GMV 日报表 │ │用户留存分析 │ │商品销量排行 │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
└─────────────────────────────────────────────────────────────┘
↓ ↑
┌─────────────────────────────────────────────────────────────┐
│ DWS 汇总层 │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │用户日汇总 │ │商品日汇总 │ │类目日汇总 │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
└─────────────────────────────────────────────────────────────┘
↓ ↑
┌─────────────────────────────────────────────────────────────┐
│ DWD 明细层 │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │订单事实表 │ │用户维度表 │ │商品维度表 │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
└─────────────────────────────────────────────────────────────┘
↓ ↑
┌─────────────────────────────────────────────────────────────┐
│ ODS 原始层 │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │order_db │ │user_db │ │product_log │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
└─────────────────────────────────────────────────────────────┘
表依赖关系
ods_order_info_df
↓
dwd_trade_order_info_df ──┬──→ dws_trade_user_1d_gmv ──→ ads_trade_gmv_daily_report_df
├──→ dws_trade_product_1d_stat ──→ ads_product_rank_top100_df
└──→ dws_trade_category_1d_stat
ods_user_info_df
↓
dwd_dim_user_info_di ────→ dws_trade_user_1d_gmv
ods_product_info_df
↓
dwd_dim_product_info_di ──→ dws_trade_product_1d_stat
⚠️ 避坑指南
坑 1：分层不清晰
❌ 错误：ODS 层直接出报表
ods_order → ads_gmv_report
后果：源表变更，报表直接挂掉
✅ 正确：完整四层
ods → dwd → dws → ads
每层职责清晰，变更隔离
坑 2：DWD 层过度聚合
❌ 错误：DWD 层就按天聚合
dwd_order_1d_summary
后果：下游无法做明细分析
✅ 正确：DWD 保持明细
dwd_order_info（明细）
dws_order_1d_summary（聚合）
坑 3：指标口径不统一
❌ 错误：每个报表自己算 GMV
报表 A：下单金额
报表 B：支付金额
报表 C：签收金额
后果：老板问"哪个是对的"
✅ 正确：DWS 层统一定义
dws_trade_user_1d_gmv.gmv = 下单金额
dws_trade_user_1d_gmv.pay_amount = 支付金额
所有报表复用
坑 4：命名不规范
❌ 错误：随意命名
table1, table2, tmp_order, test_user
✅ 正确：统一规范
ods_{source}_{table}_{freq}
dwd_{domain}_{area}_{table}_{freq}
dws_{domain}_{dim}_{period}_{metric}
ads_{scene}_{report}_{freq}
📋 设计规范速查表
命名规范
| 层级 | 格式 | 示例 |
|---|---|---|
| ODS | ods_{源系统}{表名}{频率} | ods_mysql_order_df |
| DWD | dwd_{业务域}{数据域}{表名}_{频率} | dwd_trade_order_info_df |
| DWS | dws_{业务域}{维度}{周期}_{指标} | dws_trade_user_1d_gmv |
| ADS | ads_{业务场景}{报表名}{频率} | ads_trade_gmv_daily_df |
分区规范
| 表类型 | 分区字段 | 格式 |
|---|---|---|
| 日表 | dt | yyyy-MM-dd |
| 小时表 | dt, hr | yyyy-MM-dd, HH |
| 月表 | month | yyyy-MM |
字段规范
| 类型 | 规范 |
|---|---|
| 金额 | DECIMAL(18,2)，单位：元 |
| 时间 | TIMESTAMP 或 STRING(yyyy-MM-dd HH:mm:ss) |
| 布尔 | INT（0/1） |
| 状态 | INT，附字典说明 |
| ID | STRING（兼容数字和字母） |
✅ 总结
四层架构核心职责
| 层级 | 职责 | 关键动作 |
|---|---|---|
| ODS | 原样同步 | 分区、增量/全量 |
| DWD | 清洗标准化 | 去脏、统一、退化 |
| DWS | 轻度聚合 | 统一口径、复用 |
| ADS | 高度聚合 | 面向场景、结果导向 |
设计原则
1. 高内聚低耦合
每层职责单一，层与层之间松耦合
2. 数据不冗余
公共逻辑下沉，避免重复计算
3. 口径要统一
核心指标在 DWS 层统一定义
4. 命名须规范
见名知意，降低沟通成本
5. 文档要及时
表结构、字段含义、更新逻辑要文档化
🔗 下一篇预告
《维度建模实战：从 0 设计电商数仓》
- 星型模型 vs 雪花模型
- 事实表设计（事务/周期/累积快照）
- 维度表设计（代理键/退化维度/拉链表）
- 完整案例：从需求到表结构
💬 你在数仓分层中遇到过哪些坑？欢迎评论区交流！
