# 数据仓库分层设计指南

从 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_timeTIMESTAMPCOMMENTETL 创建时间,etl_update_timeTIMESTAMPCOMMENTETL 更新时间)PARTITIONEDBY(dt STRING)STOREDASPARQUET;同步策略表类型同步方式分区策略保留周期业务表增量每日增量按天分区永久业务表全量每日全量按天分区最近 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 全量表事实表设计-- 交易域订单事实表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(dt2026-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.status1THEN10-- 状态标准化WHENt1.status2THEN20ELSE0ENDASorder_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_idt2.user_idANDt2.is_current1LEFTJOINdwd_dim_product_info_di t3ONt1.product_idt3.product_idANDt3.is_current1WHEREt1.dt2026-03-24ANDt1.order_idISNOTNULL-- 数据清洗去空ANDt1.pay_amount0;-- 数据清洗去异常 第三层DWS汇总数据层定位全称Data Warehouse Service作用按主题轻度聚合统一指标口径特点面向分析场景提升查询性能核心思想❌ 错误做法每个报表单独计算 报表 ASUM(amount) GROUP BY user_id 报表 BSUM(amount) GROUP BY user_id 报表 CCOUNT(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)COMMENTGMV下单金额,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(dt2026-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_status1,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_status1,create_time,NULL))ASfirst_pay_time,MAX(IF(pay_status1,pay_time,NULL))ASlast_pay_timeFROMdwd_trade_order_info_dfWHEREdt2026-03-24GROUPBYuser_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-- 商品销量 TOP100GMV 日报表设计CREATETABLEads_trade_gmv_daily_report_df(-- 统计维度stat_date STRINGCOMMENT统计日期,dimension_type STRINGCOMMENT维度类型day/week/month,-- 核心指标gmvDECIMAL(18,2)COMMENTGMV,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,dayASdimension_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_gmvWHEREdtDATE_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 每层职责清晰变更隔离坑 2DWD 层过度聚合❌ 错误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} 设计规范速查表命名规范层级格式示例ODSods_{源系统}{表名}{频率}ods_mysql_order_dfDWDdwd_{业务域}{数据域}{表名}_{频率}dwd_trade_order_info_dfDWSdws_{业务域}{维度}{周期}_{指标}dws_trade_user_1d_gmvADSads_{业务场景}{报表名}{频率}ads_trade_gmv_daily_df分区规范表类型分区字段格式日表dtyyyy-MM-dd小时表dt, hryyyy-MM-dd, HH月表monthyyyy-MM字段规范类型规范金额DECIMAL(18,2)单位元时间TIMESTAMP 或 STRING(yyyy-MM-dd HH:mm:ss)布尔INT0/1状态INT附字典说明IDSTRING兼容数字和字母✅ 总结四层架构核心职责层级职责关键动作ODS原样同步分区、增量/全量DWD清洗标准化去脏、统一、退化DWS轻度聚合统一口径、复用ADS高度聚合面向场景、结果导向设计原则1. 高内聚低耦合 每层职责单一层与层之间松耦合 2. 数据不冗余 公共逻辑下沉避免重复计算 3. 口径要统一 核心指标在 DWS 层统一定义 4. 命名须规范 见名知意降低沟通成本 5. 文档要及时 表结构、字段含义、更新逻辑要文档化 下一篇预告《维度建模实战从 0 设计电商数仓》星型模型 vs 雪花模型事实表设计事务/周期/累积快照维度表设计代理键/退化维度/拉链表完整案例从需求到表结构 你在数仓分层中遇到过哪些坑欢迎评论区交流