【架构实战】数据仓库分层：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 各层职责与原则

三、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 ─→ 报表就绪，对外提供

八、总结

数仓分层的本质是对数据加工过程的规范化管理。分层不是目的，统一口径、方便使用才是。

核心实践：

1. ODS贴源不做加工——保留原始数据，为回溯提供基础
2. DWD做细做全——事实表+维度表的星型模型是数仓骨架
3. DWS按天汇总——平衡查询性能和灵活性
4. ADS面向产品定制——即查即用，降低使用门槛
5. 数据质量是生命线——量级监控 + 空值监控 + 环比监控 = 三道防线
6. 任务要有依赖链——上游失败自动阻断下游，防止脏数据扩散

一个教训：不要一开始就建全部分层。先上ODS+DWD，业务跑通了再加DWS+ADS。分层越早越全的团队，70%的ADS表最终都没人用。

个人观点，仅供参考