【架构实战】数据仓库分层：ODS、DWD、DWS、ADS最佳实践
【架构实战】数据仓库分层：ODS、DWD、DWS、ADS最佳实践
一、同一个"订单量"，四个团队报了四个数
2020年初，公司月度经营分析会上，COO拍着桌子问：“昨天到底卖了多少单？”
- 运营部说：156,832单（从后台统计）
- 财务部说：152,401单（从支付系统取的）
- BI团队说：158,903单（从数据仓库出的）
- 市场部说：161,200单（自建的实时大屏）
同一家公司，同一个指标，四个答案。问题出在哪？
每个团队用的数据源不同、口径不同、时间窗口不同，数据仓库没有一个统一的分层架构。
这就是数仓分层的价值：统一口径、统一出口、统一对外服务。
二、数据仓库四层架构
2.1 分层全景
┌─────────────────────────────────────────────────────────┐ │ 数据仓库分层架构 │ │ │ │ ┌──────────────────────────────────────────────────┐ │ │ │ ADS (Application Data Service) 应用数据层 │ │ │ │ ├── 销售日报表 │ │ │ │ ├── 用户留存报表 │ │ │ │ ├── 实时大屏数据 │ │ │ │ └── 运营推荐数据 │ │ │ └────────────────┬─────────────────────────────────┘ │ │ │ 聚合加工 │ │ ┌────────────────▼─────────────────────────────────┐ │ │ │ DWS (Data Warehouse Summary) 汇总数据层 │ │ │ │ ├── 用户日汇总表（1人/天/行） │ │ │ │ ├── 商品日汇总表（1品/天/行） │ │ │ │ └── 渠道日汇总表（1渠道/天/行） │ │ │ └────────────────┬─────────────────────────────────┘ │ │ │ 轻度汇总 │ │ ┌────────────────▼─────────────────────────────────┐ │ │ │ DWD (Data Warehouse Detail) 明细数据层 │ │ │ │ ├── 订单事实表（一笔订单一行） │ │ │ │ ├── 支付事实表 │ │ │ │ ├── 用户维度表 │ │ │ │ └── 商品维度表 │ │ │ └────────────────┬─────────────────────────────────┘ │ │ │ 清洗加工 │ │ ┌────────────────▼─────────────────────────────────┐ │ │ │ ODS (Operational Data Store) 原始数据层 │ │ │ │ ├── 业务数据库同步（1:1镜像） │ │ │ │ ├── 日志原文（Nginx/App日志） │ │ │ │ └── 外部数据（第三方API） │ │ │ └──────────────────────────────────────────────────┘ │ └─────────────────────────────────────────────────────────┘2.2 各层职责与原则
| 分层 | 英文全称 | 职责 | 核心原则 | 数据特征 |
|---|---|---|---|---|
| ODS | Operational Data Store | 贴源层，1:1镜像业务数据 | 不做任何加工 | 原始、乱、全量 |
| DWD | Data Warehouse Detail | 明细层，数据清洗标准化 | 业务过程原子化 | 干净、规范、明细 |
| DWS | Data Warehouse Summary | 汇总层，轻度聚合 | 面向主题轻汇总 | 按天/小时汇总 |
| ADS | Application Data Service | 应用层，面向具体产品 | 按需定制 | 即查即用 |
三、DWD层设计——数仓的核心
3.1 事实表设计：星型模型
-- 事实表：订单明细（DWD层）CREATETABLEdwd_order_detail(order_idBIGINTCOMMENT'订单ID',user_idBIGINTCOMMENT'用户ID',product_idBIGINTCOMMENT'商品ID',shop_idBIGINTCOMMENT'店铺ID',category_idBIGINTCOMMENT'类目ID',-- 度量值（事实）original_amountDECIMAL(12,2)COMMENT'原价',discount_amountDECIMAL(12,2)COMMENT'优惠金额',final_amountDECIMAL(12,2)COMMENT'实付金额',quantityINTCOMMENT'购买数量',-- 时间维度create_timeDATETIMECOMMENT'下单时间',pay_timeDATETIMECOMMENT'支付时间',dt STRINGCOMMENT'分区日期')COMMENT'订单明细事实表'PARTITIONEDBY(dt STRING);-- 维度表：用户维度CREATETABLEdim_user(user_idBIGINTCOMMENT'用户ID',user_name STRINGCOMMENT'用户名',register_dateDATECOMMENT'注册日期',city STRINGCOMMENT'城市',channel STRINGCOMMENT'注册渠道',user_level STRINGCOMMENT'会员等级')COMMENT'用户维度表';-- 查询时用星型模型关联SELECTu.city,SUM(o.final_amount)AStotal_gmv,COUNT(DISTINCTo.user_id)ASuvFROMdwd_order_detail oJOINdim_user uONo.user_id=u.user_idWHEREo.dt='2025-06-01'GROUPBYu.city;3.2 数据清洗六步法
DWD层数据清洗标准流程： 1. 去重：按主键去重，保留最新一条 INSERT INTO dwd_order_detail SELECT ... FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) rn FROM ods_order ) t WHERE rn = 1; 2. 空值处理：NULL → '未知' 或 0 COALESCE(user_name, '未知') AS user_name 3. 格式统一：手机号去+86，日期统一yyyy-MM-dd REGEXP_REPLACE(phone, '^\\+86', '') AS phone 4. 非法值过滤：负数金额、未来时间 WHERE amount >= 0 AND create_time <= CURRENT_TIMESTAMP 5. 字段脱敏：手机号中间四位替换 CONCAT(SUBSTR(phone,1,3), '****', SUBSTR(phone,8,4)) AS phone_masked 6. 数据打标：标记数据来源 'mysql_orders' AS source_system四、DWS层设计——汇总的艺术
4.1 汇总粒度选择
-- 错误做法：汇总力度太细，没效果CREATETABLEdws_user_hourly(user_idBIGINT,hourSTRING,order_cntBIGINT);-- 数据量几乎等于DWD层，没有意义-- 正确做法：选择合理粒度CREATETABLEdws_user_daily(user_idBIGINTCOMMENT'用户ID',stat_dateDATECOMMENT'统计日期',order_cntBIGINTCOMMENT'下单数',pay_cntBIGINTCOMMENT'支付数',pay_amountDECIMAL(18,2)COMMENT'支付金额',refund_cntBIGINTCOMMENT'退款数',refund_amountDECIMAL(18,2)COMMENT'退款金额',first_order_timeDATETIMECOMMENT'首次下单时间',last_order_timeDATETIMECOMMENT'末次下单时间')COMMENT'用户日汇总表'PARTITIONEDBY(dt STRING);4.2 DWS聚合SQL模板
-- 每日调度任务INSERTOVERWRITETABLEdws_user_dailyPARTITION(dt='${bizdate}')SELECTuser_id,'${bizdate}'ASstat_date,COUNT(DISTINCTorder_id)ASorder_cnt,COUNT(DISTINCTCASEWHENpay_timeISNOTNULLTHENorder_idEND)ASpay_cnt,SUM(CASEWHENpay_timeISNOTNULLTHENfinal_amountELSE0END)ASpay_amount,COUNT(DISTINCTCASEWHENrefund_status=1THENorder_idEND)ASrefund_cnt,SUM(CASEWHENrefund_status=1THENrefund_amountELSE0END)ASrefund_amount,MIN(create_time)ASfirst_order_time,MAX(create_time)ASlast_order_timeFROMdwd_order_detailWHEREdt='${bizdate}'GROUPBYuser_id;五、ADS层设计——面向应用
5.1 销售日报表
CREATETABLEads_sales_dailyASSELECTstat_date,category_name,-- 本日指标SUM(order_cnt)ASorder_cnt,SUM(order_amt)ASorder_amt,SUM(pay_amt)ASpay_amt,-- 同比（YoY）LAG(SUM(order_amt),365)OVER(PARTITIONBYcategory_nameORDERBYstat_date)ASlast_year_amt,-- 环比（MoM）LAG(SUM(order_amt),1)OVER(PARTITIONBYcategory_nameORDERBYstat_date)ASlast_day_amt,-- 计算增长率ROUND((SUM(order_amt)-LAG(SUM(order_amt),365)OVER(...))/LAG(SUM(order_amt),365)OVER(...)*100,2)ASyoy_rateFROMdws_category_dailyWHEREstat_date>=DATE_SUB(CURRENT_DATE,365)GROUPBYstat_date,category_name;六、数据质量保障
6.1 质量监控SQL
-- 1. 数据量监控SELECTdt,COUNT(*)ASrow_cntFROMdwd_order_detailWHEREdt>=DATE_SUB(CURRENT_DATE,7)GROUPBYdtORDERBYdt;-- 告警规则：今天数据量比昨天少50%-- 2. 空值监控SELECTSUM(CASEWHENuser_idISNULLTHEN1ELSE0END)/COUNT(*)ASnull_rateFROMdwd_order_detailWHEREdt=CURRENT_DATE;-- 告警规则：空值率超过5%-- 3. 环比监控SELECTtoday_cnt,yesterday_cnt,(today_cnt-yesterday_cnt)/yesterday_cntASchange_rateFROM(SELECTSUM(CASEWHENdt=CURRENT_DATETHEN1ELSE0END)AStoday_cnt,SUM(CASEWHENdt=DATE_SUB(CURRENT_DATE,1)THEN1ELSE0END)ASyesterday_cntFROMdwd_order_detailWHEREdt>=DATE_SUB(CURRENT_DATE,1))t;-- 告警规则：环比变化超过30%七、任务调度架构
调度流程（Airflow/DolphinScheduler）: 00:00 ─→ ODS层同步开始 01:00 ─→ ODS同步完成 01:10 ─→ DWD层清洗开始 02:30 ─→ DWD层清洗完成 02:40 ─→ DWS层汇总开始 03:30 ─→ DWS层汇总完成 03:40 ─→ ADS层计算开始 04:30 ─→ ADS层计算完成 05:00 ─→ 数据质量检查 06:00 ─→ 报表就绪，对外提供八、总结
数仓分层的本质是对数据加工过程的规范化管理。分层不是目的，统一口径、方便使用才是。
核心实践：
- ODS贴源不做加工——保留原始数据，为回溯提供基础
- DWD做细做全——事实表+维度表的星型模型是数仓骨架
- DWS按天汇总——平衡查询性能和灵活性
- ADS面向产品定制——即查即用，降低使用门槛
- 数据质量是生命线——量级监控 + 空值监控 + 环比监控 = 三道防线
- 任务要有依赖链——上游失败自动阻断下游，防止脏数据扩散
一个教训：不要一开始就建全部分层。先上ODS+DWD，业务跑通了再加DWS+ADS。分层越早越全的团队，70%的ADS表最终都没人用。
个人观点，仅供参考