【架构实战】数据仓库分层：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 各层职责与原则分层英文全称职责核心原则数据特征ODSOperational Data Store贴源层1:1镜像业务数据不做任何加工原始、乱、全量DWDData Warehouse Detail明细层数据清洗标准化业务过程原子化干净、规范、明细DWSData Warehouse Summary汇总层轻度聚合面向主题轻汇总按天/小时汇总ADSApplication 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_idu.user_idWHEREo.dt2025-06-01GROUPBYu.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_status1THENorder_idEND)ASrefund_cnt,SUM(CASEWHENrefund_status1THENrefund_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,-- 同比YoYLAG(SUM(order_amt),365)OVER(PARTITIONBYcategory_nameORDERBYstat_date)ASlast_year_amt,-- 环比MoMLAG(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_dateDATE_SUB(CURRENT_DATE,365)GROUPBYstat_date,category_name;六、数据质量保障6.1 质量监控SQL-- 1. 数据量监控SELECTdt,COUNT(*)ASrow_cntFROMdwd_order_detailWHEREdtDATE_SUB(CURRENT_DATE,7)GROUPBYdtORDERBYdt;-- 告警规则今天数据量比昨天少50%-- 2. 空值监控SELECTSUM(CASEWHENuser_idISNULLTHEN1ELSE0END)/COUNT(*)ASnull_rateFROMdwd_order_detailWHEREdtCURRENT_DATE;-- 告警规则空值率超过5%-- 3. 环比监控SELECTtoday_cnt,yesterday_cnt,(today_cnt-yesterday_cnt)/yesterday_cntASchange_rateFROM(SELECTSUM(CASEWHENdtCURRENT_DATETHEN1ELSE0END)AStoday_cnt,SUM(CASEWHENdtDATE_SUB(CURRENT_DATE,1)THEN1ELSE0END)ASyesterday_cntFROMdwd_order_detailWHEREdtDATE_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面向产品定制——即查即用降低使用门槛数据质量是生命线——量级监控 空值监控 环比监控 三道防线任务要有依赖链——上游失败自动阻断下游防止脏数据扩散一个教训不要一开始就建全部分层。先上ODSDWD业务跑通了再加DWSADS。分层越早越全的团队70%的ADS表最终都没人用。个人观点仅供参考