# 数据仓库分层设计指南

从 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-- 日志日表

表结构设计

CREATETABLEods_order_info_df(-- 业务字段（原样同步）order_id STRINGCOMMENT'订单 ID',user_id STRINGCOMMENT'用户 ID',amountDECIMAL(18,2)COMMENT'订单金额',statusINTCOMMENT'订单状态',create_time STRINGCOMMENT'创建时间',-- 分区字段dt STRINGCOMMENT'日期分区，格式：yyyy-MM-dd',-- 系统字段（新增）etl_create_timeTIMESTAMPCOMMENT'ETL 创建时间',etl_update_timeTIMESTAMPCOMMENT'ETL 更新时间')PARTITIONEDBY(dt STRING)STOREDASPARQUET;

同步策略

注意事项

✅ 字段类型尽量与源系统保持一致 ✅ 时间字段统一转为 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 全量表

事实表设计

-- 交易域订单事实表CREATETABLEdwd_trade_order_info_df(-- 业务主键order_id STRINGCOMMENT'订单 ID',-- 维度退化字段user_id STRINGCOMMENT'用户 ID',user_name STRINGCOMMENT'用户名（冗余）',user_levelINTCOMMENT'用户等级（冗余）',product_id STRINGCOMMENT'商品 ID',product_name STRINGCOMMENT'商品名（冗余）',category_id STRINGCOMMENT'类目 ID',category_name STRINGCOMMENT'类目名（冗余）',-- 度量字段original_amountDECIMAL(18,2)COMMENT'原始金额',discount_amountDECIMAL(18,2)COMMENT'优惠金额',pay_amountDECIMAL(18,2)COMMENT'实付金额',freight_amountDECIMAL(18,2)COMMENT'运费',-- 状态字段order_statusINTCOMMENT'订单状态',pay_statusINTCOMMENT'支付状态',-- 时间字段（统一格式）create_timeTIMESTAMPCOMMENT'创建时间',pay_timeTIMESTAMPCOMMENT'支付时间',finish_timeTIMESTAMPCOMMENT'完成时间',-- 分区字段dt STRINGCOMMENT'日期分区')PARTITIONEDBY(dt STRING)STOREDASPARQUET;

维度表设计

-- 用户维度表（拉链表）CREATETABLEdwd_dim_user_info_di(user_id STRINGCOMMENT'用户 ID',user_name STRINGCOMMENT'用户名',gender STRINGCOMMENT'性别',ageINTCOMMENT'年龄',city STRINGCOMMENT'城市',user_levelINTCOMMENT'用户等级',-- 拉链字段start_date STRINGCOMMENT'生效开始日期',end_date STRINGCOMMENT'生效结束日期',is_currentINTCOMMENT'是否当前版本：1-是，0-否')PARTITIONEDBY(dt STRING)STOREDASPARQUET;

数据清洗示例

-- 从 ODS 到 DWD 的 ETL 处理INSERTOVERWRITETABLEdwd_trade_order_info_dfPARTITION(dt='2026-03-24')SELECTt1.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,CASEWHENt1.status=1THEN10-- 状态标准化WHENt1.status=2THEN20ELSE0ENDASorder_status,FROM_UNIXTIME(t1.create_time,'yyyy-MM-dd HH:mm:ss')AScreate_time,-- 时间格式化FROM_UNIXTIME(t1.pay_time,'yyyy-MM-dd HH:mm:ss')ASpay_timeFROMods_order_info_df t1LEFTJOINdwd_dim_user_info_di t2ONt1.user_id=t2.user_idANDt2.is_current=1LEFTJOINdwd_dim_product_info_di t3ONt1.product_id=t3.product_idANDt3.is_current=1WHEREt1.dt='2026-03-24'ANDt1.order_idISNOTNULL-- 数据清洗：去空ANDt1.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-- 用户域登录月粒度聚合

用户日汇总表设计

CREATETABLEdws_trade_user_1d_gmv(-- 维度字段user_id STRINGCOMMENT'用户 ID',user_levelINTCOMMENT'用户等级',city STRINGCOMMENT'城市',-- 交易指标gmvDECIMAL(18,2)COMMENT'GMV（下单金额）',pay_amountDECIMAL(18,2)COMMENT'实付金额',order_countBIGINTCOMMENT'下单次数',pay_order_countBIGINTCOMMENT'支付订单数',refund_countBIGINTCOMMENT'退款次数',refund_amountDECIMAL(18,2)COMMENT'退款金额',-- 商品指标product_countBIGINTCOMMENT'购买商品数',category_countBIGINTCOMMENT'购买类目数',-- 时间指标first_pay_timeTIMESTAMPCOMMENT'首次支付时间',last_pay_timeTIMESTAMPCOMMENT'最后支付时间',-- 分区字段dt STRINGCOMMENT'日期分区')PARTITIONEDBY(dt STRING)STOREDASPARQUET;

聚合逻辑

-- 从 DWD 到 DWS 的 ETLINSERTOVERWRITETABLEdws_trade_user_1d_gmvPARTITION(dt='2026-03-24')SELECTuser_id,MAX(user_level)ASuser_level,MAX(city)AScity,SUM(gmv)ASgmv,SUM(pay_amount)ASpay_amount,COUNT(order_id)ASorder_count,COUNT(IF(pay_status=1,order_id,NULL))ASpay_order_count,COUNT(IF(order_status=-1,order_id,NULL))ASrefund_count,SUM(IF(order_status=-1,pay_amount,0))ASrefund_amount,COUNT(DISTINCTproduct_id)ASproduct_count,COUNT(DISTINCTcategory_id)AScategory_count,MIN(IF(pay_status=1,create_time,NULL))ASfirst_pay_time,MAX(IF(pay_status=1,pay_time,NULL))ASlast_pay_timeFROMdwd_trade_order_info_dfWHEREdt='2026-03-24'GROUPBYuser_id;

常见汇总维度

📊 第四层：ADS（应用数据层）

定位

• 全称：Application Data Store
• 作用：面向具体业务场景，直接支撑报表
• 特点：高度聚合，结果导向

设计规范

-- 命名规范ads_{业务场景}_{报表名称}_{更新频率}-- 示例ads_trade_gmv_daily_report_df-- 交易域 GMV 日报ads_user_retention_analysis_df-- 用户留存分析ads_product_rank_top100_df-- 商品销量 TOP100

GMV 日报表设计

CREATETABLEads_trade_gmv_daily_report_df(-- 统计维度stat_date STRINGCOMMENT'统计日期',dimension_type STRINGCOMMENT'维度类型：day/week/month',-- 核心指标gmvDECIMAL(18,2)COMMENT'GMV',gmv_day_over_dayDECIMAL(10,4)COMMENT'环比（日）',gmv_week_over_weekDECIMAL(10,4)COMMENT'环比（周）',gmv_year_over_yearDECIMAL(10,4)COMMENT'同比',pay_amountDECIMAL(18,2)COMMENT'实付金额',order_countBIGINTCOMMENT'订单数',pay_user_countBIGINTCOMMENT'支付用户数',avg_order_valueDECIMAL(18,2)COMMENT'客单价',-- 更新时间update_timeTIMESTAMPCOMMENT'更新时间');

报表计算逻辑

-- 从 DWS 到 ADS 的 ETLINSERTINTOTABLEads_trade_gmv_daily_report_dfSELECT'${stat_date}'ASstat_date,'day'ASdimension_type,SUM(gmv)ASgmv,(SUM(gmv)-LAG(SUM(gmv),1)OVER())/LAG(SUM(gmv),1)OVER()ASgmv_day_over_day,(SUM(gmv)-LAG(SUM(gmv),7)OVER())/LAG(SUM(gmv),7)OVER()ASgmv_week_over_week,(SUM(gmv)-LAG(SUM(gmv),365)OVER())/LAG(SUM(gmv),365)OVER()ASgmv_year_over_year,SUM(pay_amount)ASpay_amount,SUM(order_count)ASorder_count,COUNT(DISTINCTuser_id)ASpay_user_count,SUM(pay_amount)/SUM(order_count)ASavg_order_value,NOW()ASupdate_timeFROMdws_trade_user_1d_gmvWHEREdt>=DATE_SUB('${stat_date}',365)GROUPBYstat_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}

📋 设计规范速查表

命名规范

分区规范

字段规范

✅ 总结

四层架构核心职责

设计原则

1. 高内聚低耦合 每层职责单一，层与层之间松耦合 2. 数据不冗余 公共逻辑下沉，避免重复计算 3. 口径要统一 核心指标在 DWS 层统一定义 4. 命名须规范 见名知意，降低沟通成本 5. 文档要及时 表结构、字段含义、更新逻辑要文档化

🔗 下一篇预告

《维度建模实战：从 0 设计电商数仓》

• 星型模型 vs 雪花模型
• 事实表设计（事务/周期/累积快照）
• 维度表设计（代理键/退化维度/拉链表）
• 完整案例：从需求到表结构

💬 你在数仓分层中遇到过哪些坑？欢迎评论区交流！