数据仓库分层架构设计ODS-DWD-DWS-ADS 每层到底该做什么大家好我是朱大喜。数据仓库分层这个概念听起来高大上实际落地的时候很多人会懵我的表到底该放哪一层 今天用一篇文章把 ODS、DWD、DWS、ADS 四层讲透——每层该干什么、不该干什么、怎么判断放对没放对。一、为什么需要分层一个厨房的比喻数据仓库分层就像餐厅后厨的分工ODS操作数据层食材仓库——从供应商拉来的菜直接堆在这儿没洗没切保持原样DWD明细数据层切配区——洗菜、削皮、去烂叶子、切成统一规格按菜谱备料DWS汇总数据层料理台——把常用的配菜提前备好洋葱丁、蒜末、高汤炒菜时直接抓ADS应用数据层出餐区——最终的成品菜直接端给客人。graph TB subgraph 业务系统 A1[MySQL 用户库] A2[MongoDB 日志] A3[Kafka 实时流] A4[API 外部数据] end subgraph ODS: 原样接入 B1[ods_user_infobr/和MySQL字段完全一致] B2[ods_behavior_logbr/JSON原封不动] end subgraph DWD: 清洗标准化 C1[dwd_user_infobr/去重、脱敏、字段标准化] C2[dwd_behavior_detailbr/JSON解析、枚举值统一] C3[dwd_order_detailbr/金额单位统一为分] end subgraph DWS: 汇总宽表 D1[dws_user_behavior_1dbr/每日用户行为汇总] D2[dws_user_wide_tablebr/用户宽表几十个指标] end subgraph ADS: 直接给业务 E1[ads_gmv_dashboardbr/看板专用表] E2[ads_user_churn_reportbr/周报专用] E3[ads_model_featuresbr/模型特征表] end A1 -- B1 A2 -- B2 A3 -- B2 A4 -- B2 B1 -- C1 B2 -- C2 B1 -- C3 B2 -- C3 C1 -- D1 C2 -- D1 C3 -- D1 C1 -- D2 C2 -- D2 C3 -- D2 D1 -- E1 D2 -- E2 D1 -- E3 D2 -- E3每一层只做自己该做的事不越界。越界了就会导致牵一发动全身——上游改一个字段下游几百张表全崩。为什么分层不是多了一层就多一次计算的负担而是保护很多新手觉得直接 ODS → ADS 一步到位多省事少了一道 ETL 就少了一个小时的调度时间。但这种省事在上游系统改了一个字段类型时如user_status从 TINYINT 变成 VARCHAR会加倍偿还直接 ODS→ADS 模式下所有 ADS 表里引用了user_status的地方全部要改 SQL。而四层架构下你只需要在 DWD 层改一次类型转换逻辑下游 DWS 和 ADS 因为通过 DWD 间接消费不受任何影响。这就是解耦的价值——不是让系统跑得更快而是让系统改得更少、坏得更慢。二、ODS 层别想太多保持原样就是最好的设计ODS 层有且只有一个职责把源系统的数据搬进来不做任何加工。ODS 的设计原则-- ✅ 好的 ODS 设计和源表一模一样 -- 为什么不做加工任何加工都意味着信息损失。 -- 比如源表有 user_status 字段存的是 0/1/2 三个值 -- 如果你在 ODS 层把它翻译成 正常/冻结/注销 -- 万一以后新增了状态 3你的翻译逻辑就要改 CREATE TABLE ods.ods_user_account ( -- 业务系统里叫 user_id这里也叫 user_id user_id BIGINT COMMENT 用户ID, -- 业务系统里是 varchar(11)这里改成 stringHive没有varchar mobile STRING COMMENT 手机号, user_status TINYINT COMMENT 用户状态0正常 1冻结 2注销, -- dt 是 Hive 分区字段不是业务字段 dt STRING COMMENT 数据日期分区 ) PARTITIONED BY (dt) STORED AS ORC;ODS 不能做的事-- ❌ 不要在 ODS 层做 JOIN -- JOIN 意味着耦合了两张源表一旦源表结构变化ODS 就要改 SELECT a.*, b.user_name FROM ods_order a JOIN ods_user b ON a.user_id b.user_id; -- ❌ 不要在 ODS 层做聚合 -- 聚合意味着损失明细后面的分析想下钻都没机会 SELECT dt, channel, COUNT(*) FROM ods_order GROUP BY dt, channel; -- ❌ 不要在 ODS 层做过滤 -- 今天你觉得某个 source 的数据没用明天发现它是风控的关键字段 SELECT * FROM ods_behavior WHERE source ! test;ODS 的数据保留策略-- ODS 层数据量最大必须有裁剪策略 -- 为什么保留 30 天大多数数据质量问题能在 30 天内发现 -- 如果需要更久的历史用冷存储如对象存储归档 ALTER TABLE ods.ods_behavior_log SET TBLPROPERTIES ( retention.days 30, -- 30天后自动清理 orc.compress ZLIB -- ODS 用高压缩比查得少不用追求速度 );三、DWD 层清洗 标准化这是最磨人的一层DWD 是数据仓库质量的第一道防线。它的核心工作是清洗把脏数据挡在门外-- DWD 层的 ETL 脚本示例 INSERT OVERWRITE TABLE dwd.dwd_order_detail PARTITION (dt${bizdate}) SELECT -- 1. 异常值处理 -- 为什么用 CASE WHEN 而不是过滤掉过滤会丢掉整行改成一个合理的默认值 -- 至少保留了有一条记录的事实 CASE WHEN order_amount 0 THEN NULL -- 负数金额标记为空下游分析时单独关注 WHEN order_amount 10000000 THEN NULL -- 超过千万的订单大概率是异常数据 ELSE order_amount END as order_amount, -- 2. 字段脱敏ODS 层保留原始手机号DWD 层必须脱敏 -- 为什么在 DWD 层脱敏ODS 是原样备份不能改ADS 是给业务用的不能有敏感信息 CONCAT(SUBSTR(mobile, 1, 3), ****, SUBSTR(mobile, 8, 4)) as mobile_masked, -- 3. 枚举值标准化 -- 为什么统一源系统可能用 Android/android/ANDROID 三种写法 UPPER(TRIM(platform)) as platform, -- 4. NULL 值填充 -- 为什么不能留 NULL维度字段的 NULL 会让 GROUP BY 产生一个额外的未知分组 COALESCE(channel, UNKNOWN) as channel, -- 5. 日期标准化 -- 源系统可能是 timestamp/date/string 各种格式DWD 统一成标准格式 DATE_FORMAT(create_time, yyyy-MM-dd HH:mm:ss) as create_time FROM ods.ods_order_raw WHERE dt ${bizdate};维度建模星型模型 vs 雪花模型DWD 层要做数据建模最常见的是星型模型-- 事实表订单明细存度量和外键 CREATE TABLE dwd.dwd_order_fact ( order_id BIGINT COMMENT 订单ID, user_id BIGINT COMMENT 用户ID关联用户维度, product_id BIGINT COMMENT 商品ID关联商品维度, shop_id BIGINT COMMENT 店铺ID关联店铺维度, -- 事实表中的度量字段 order_amount DECIMAL(18,2) COMMENT 订单金额, discount_amount DECIMAL(18,2) COMMENT 优惠金额, quantity INT COMMENT 购买数量, dt STRING COMMENT 订单日期 ) PARTITIONED BY (dt) STORED AS ORC; -- 维度表用户维度存描述性字段 CREATE TABLE dwd.dim_user ( user_id BIGINT COMMENT 用户ID, user_name STRING COMMENT 用户名, register_date STRING COMMENT 注册日期, city STRING COMMENT 城市, user_level STRING COMMENT 用户等级, is_vip BOOLEAN COMMENT 是否VIP ) STORED AS ORC;事实表存发生了什么金额、数量、次数维度表存以什么角度去看用户是谁、商品是什么、什么时间。这个区分是数据建模的核心。为什么事实表和维度表要分开而不是做成一张大宽表宽表的终极诱惑把所有字段放一张表里分析师一条 SQL 搞定一切——不需要 JOIN。但代价是存储爆炸和更新噩梦一个用户改了城市信息从北京变成上海如果所有事实表都冗余存了城市字段你需要更新几十张表。而维度表模式下只要更新dim_user一张表所有关联的事实表通过 JOIN 自动拿到最新城市信息。这里有一个取舍查询性能宽表快vs 数据一致性星型模型一致。大多数数仓的选择是DWD 保持星型模型保证一致性DWS 层再做成宽表提供查询性能。四、DWS 层从明细到汇总为查询做预计算DWS 层的设计哲学空间换时间。把常用的聚合提前算好让 ADS 和数据分析师的查询从扫全表聚合变成直接读汇总结果。轻度汇总表设计-- DWS 每日用户行为汇总 -- 设计的核心思想这张表里每个用户每天一行 -- 包含了当天最重要的行为指标90% 的分析需求从这儿出发就够了 CREATE TABLE dws.dws_user_behavior_1d ( user_id BIGINT COMMENT 用户ID, -- PV/UV 类指标 login_times INT COMMENT 登录次数, -- 时长类指标 total_online_seconds BIGINT COMMENT 总在线时长(秒), avg_session_seconds DOUBLE COMMENT 平均会话时长, -- 消费类指标 order_cnt INT COMMENT 下单次数, pay_cnt INT COMMENT 支付次数, pay_amount DECIMAL(18,2) COMMENT 支付金额, -- 互动类指标 comment_cnt INT COMMENT 评论次数, share_cnt INT COMMENT 分享次数, -- 标记类 is_new_user BOOLEAN COMMENT 是否新用户, is_active BOOLEAN COMMENT 是否活跃用户当天有行为 ) PARTITIONED BY (dt STRING COMMENT 日期分区) STORED AS ORC;用户宽表一次 JOIN终身受益-- DWS 层最实用的产物用户宽表 -- 为什么叫宽表把用户维度 行为指标 消费指标全平铺在一行 -- 数据分析师要的任何用户画像字段从这张表里 SELECT 就行了 CREATE TABLE dws.dws_user_wide AS SELECT -- 用户维度从 DWD 维度表来 u.user_id, u.register_date, u.city, u.user_level, -- 行为指标从 DWS 行为汇总来 COALESCE(b.login_times, 0) as login_times_1d, COALESCE(b.avg_session_seconds, 0) as avg_session_1d, -- 消费指标从 DWS 消费汇总来 COALESCE(p.pay_amount, 0) as pay_amount_1d, COALESCE(p.pay_cnt, 0) as pay_cnt_1d, -- 衍生指标 -- 为什么在这里算一次这些衍生指标在 ADS 层会被反复使用 -- 在 DWS 层算好ADS 直接取避免重复计算 CASE WHEN COALESCE(p.pay_amount, 0) 0 THEN high_value WHEN COALESCE(b.login_times, 0) 0 THEN active WHEN u.register_date DATE_SUB(${bizdate}, 7) THEN new ELSE silent END as user_segment FROM dwd.dim_user u LEFT JOIN dws.dws_user_behavior_1d b ON u.user_id b.user_id AND b.dt ${bizdate} LEFT JOIN dws.dws_user_pay_1d p ON u.user_id p.user_id AND p.dt ${bizdate};五、ADS 层把数据变成产品ADS 是数据仓库的门面直接面向业务用户、BI 看板、AI 模型。它的设计原则是一张表一个用途。-- ADS 看板专用表 -- 设计原则字段名字让业务人员一眼就能看懂 -- 不要出现 user_level_code3 这种需要查字典的字段 CREATE TABLE ads.ads_gmv_dashboard_1d ( report_date STRING COMMENT 报表日期, channel_name STRING COMMENT 渠道名称中文, gmv DECIMAL(18,2) COMMENT 成交总额, order_cnt BIGINT COMMENT 订单量, pay_user_cnt BIGINT COMMENT 支付用户数, new_user_cnt BIGINT COMMENT 新用户数, -- 直接算好对比值看板不用再算 gmv_dod DECIMAL(10,4) COMMENT GMV日环比, gmv_wow DECIMAL(10,4) COMMENT GMV周同比, -- 标注是否异常由 AI 异常检测引擎打标 is_anomaly BOOLEAN COMMENT 是否异常日, anomaly_reason STRING COMMENT 异常原因AI归因结果 ) PARTITIONED BY (dt STRING) STORED AS ORC;每层的操作规范速查表层次该做什么严禁做什么数据保留ODS原样同步、加 dt 分区JOIN/聚合/过滤/脱敏30~90天DWD清洗、标准化、维度建模、脱敏直接给业务用1~3年DWS汇总聚合、宽表、衍生指标粒度低于日度3~5年ADS面向需求、中文命名、预计算对比值跨业务混用按需一般长期五、总结数据仓库分层的核心心法就一句话每一层只往上抽象一个精度层级不跳级。ODS 保持原始 → DWD 清洗标准化 → DWS 汇总宽表 → ADS 面向应用宁可多建一层ODS → STG → DWD不要跳层ODS 直接到 ADS每一层都应该能独立重跑不依赖其他层的中间状态下游依赖上游但上游不知道下游的存在单向依赖如果你正在搭仓库或重构旧仓库我的建议是先建 ODS 和 DWD确认数据质量过关了再往上建 DWS 和 ADS。质量不过关建再多上层都是空中楼阁。 踩坑提醒ODS 层的user_status存 TINYINT 而不是翻译成中文但下游 DWD 必须翻译很多人误以为ODS 不加工意味着整个链路都不翻译状态码。ODS 确实不该翻译保持原样但 DWD 层必须做这一步——分析师不该去查代码字典才知道user_status 2是冻结。DWD 的职责之一就是把枚举值翻译为可读的字符串。DWS 层不要做跨度的汇总DWS 的dws_user_behavior_1d是按天汇总的。如果你在同一层里又建了一个dws_user_behavior_1w按周汇总当日的逻辑改了周的也必须改。跨粒度的汇总应该放在 ADS 层基于 DWS 的日度数据SUM而成而不是独立维护——否则一个指标三层各自算一遍口径不一致让你怀疑人生。每层的表名和字段名必须体现粒度dws_user_behavior_1d的_1d后缀告诉你这是日度表login_times前面不加1d_前缀的话下游写 SQL 时就搞不清这个 login_times 到底是一天的还是累计的。建议在字段上加粒度前缀或后缀如login_times_1d、pay_amount_7d防止跨层引用时混淆粒度。