数据分层实战：ODS、DW、DM三层架构设计与落地指南

1. 数据分层到底在分什么从一堆乱表到一套可维护的数据体系刚入行那会儿我最怕听到的一句话就是“帮我从数仓里取个数”。因为那时候所谓的“数仓”其实就是几百张命名毫无规律的宽表堆在一起有的叫tmp_20230101_final有的叫user_info_new_v2还有的叫aaa_test_不要删。每次取数都像考古得先问三四个同事这张表到底能不能用、字段有没有坑、口径是不是最新的。后来团队痛定思痛做了一次彻底的数据分层改造把 ODS、DW、DM 这三层体系搭起来取数效率直接翻了好几倍口径扯皮的事也少了一大半。数据分层这件事说白了就是给数据建一栋有楼层、有房间号的楼。ODS 是一楼仓库原封不动地接收从业务系统搬过来的原始货物DW 是二楼的加工车间把原材料清洗、拆分、组装成标准件DM 是三楼的展厅按不同客户的需求把标准件摆成一个个可以直接拿走用的成品。没有分层的时候所有东西堆在一个大平层里找东西靠翻改东西怕碰倒别的时间一长必然失控。这套体系能解决的核心问题有三个第一是口径统一同一个“活跃用户”的定义只在一个地方维护不会出现三个部门三个算法第二是血缘清晰任何一个指标出问题都能顺着层级往上追溯快速定位是哪一步算错了第三是成本可控原始数据只存一份中间层可以按需保留应用层可以针对性地做聚合和缓存存储和计算资源不会无脑膨胀。适合看这篇内容的人我大致分三类一是刚接触数仓、被 ODS/DW/DM 这些缩写搞得有点懵的新人二是正在推动团队做数据治理、需要一套可落地分层方案的负责人三是日常要写 SQL 取数、但总觉得表太多太乱、想搞清楚背后组织逻辑的分析师。不管你用的是哪种技术栈分层的思路是通用的理解了这套逻辑换任何工具都能快速上手。2. 三层架构的设计逻辑与选型考量2.1 为什么是 ODS、DW、DM 这三层而不是两层或五层很多人会问分层到底分几层合适。我见过只分两层的团队也见过硬生生拆出六七层的团队。两层的问题是 ODS 直接对接应用所有清洗逻辑都堆在应用层改一个口径要动十几张报表六七层的问题是链路太长数据从源头到应用要经过五六次落表延迟高、排查难、维护成本爆炸。三层是一个比较舒服的平衡点ODS 负责“接进来”DW 负责“洗干净并标准化”DM 负责“按场景组装好”。ODS 层的核心原则是贴源。业务系统里是什么样ODS 里就尽量保持什么样不做业务逻辑的加工只做必要的技术处理比如字段类型统一、敏感信息脱敏、增量抽取的时间戳标记。这样做的好处是一旦上游数据出问题ODS 可以作为“原始证据”来核对不会因为中间做了加工而说不清楚。DW 层是真正体现数据建模能力的地方。这一层通常又会细分为 DWD明细数据层和 DWS汇总数据层。DWD 做的是清洗、去重、维度补全、码值转换这些“标准化”工作把不同业务系统里五花八门的表达统一成一套标准。DWS 则是在 DWD 的基础上做轻度聚合比如按天、按用户、按品类做汇总为上层应用准备好“半成品”。DM 层是面向应用的。不同部门、不同场景对数据的需求差异很大DM 层就是把这些差异消化掉给每个场景提供一张或几张“开箱即用”的表。比如营销部门要看用户画像风控部门要看交易异常运营部门要看日活留存这些需求在 DM 层各自有对应的表互不干扰。2.2 分层带来的核心收益与常见误区分层最直接的收益是复用。没有分层的时候每个报表都从原始表开始写同样的清洗逻辑重复几十遍改一处漏一处。有了 DW 层之后清洗逻辑只写一次所有下游共享改口径只需要改一个地方。我粗略算过我们团队做完分层之后新增一张报表的开发时间从平均两天缩短到半天大部分时间花在确认需求而不是写 SQL 上。第二个收益是可追溯。数据出问题的时候排查路径非常清晰先看 DM 层的指标对不对如果不对就看 DW 层的明细再不对就看 ODS 层的原始数据。每一层都有明确的输入和输出问题定位从“大海捞针”变成“按图索骥”。但分层也有常见的误区。第一个误区是为了分层而分层把本来简单的链路硬拆成很多层结果每层的数据量都很小维护成本却很高。第二个误区是ODS 层做太多加工比如在 ODS 里就把一些字段清洗了导致后面想追溯原始值时发现已经被改过了。第三个误区是DM 层直接读 ODS跳过了 DW 层短期看省事长期看就是给自己挖坑因为一旦 ODS 结构变化所有 DM 表都要跟着改。提示分层的层数不是固定的三层是通用起点。如果业务简单、数据量小ODS 加 DM 两层也能跑如果业务复杂、合规要求高在 DW 里再拆出 DWD 和 DWS 也很常见。关键是每一层要有明确的职责边界不能互相串。2.3 不同技术栈下的分层落地差异虽然分层的逻辑是通用的但不同技术栈在落地时会有一些差异。传统关系型数据库做数仓的时候ODS 通常是一张张跟源表结构一致的表DW 用视图或者物化视图来实现DM 用宽表或者聚合表。这种方案的好处是 SQL 兼容性好坏处是数据量大了之后性能会吃力。大数据平台上的分层就更灵活一些。ODS 一般用 Parquet 或者 ORC 格式存在分布式文件系统上按天分区。DW 层用 Hive 或者 Spark SQL 来做清洗和聚合DWD 保持明细粒度DWS 做轻度汇总。DM 层可以同步到关系型数据库或者分析型数据库里供报表和接口直接查询。这种方案能扛住很大的数据量但链路更长对调度和监控的要求也更高。还有一些团队用数据湖的方案ODS 和 DW 都放在数据湖里用统一的元数据管理来维护血缘。这种方案灵活度高但如果没有好的治理机制很容易退化成“数据沼泽”。我的经验是不管用什么技术栈元数据管理和数据质量监控都是分层能不能落地的关键。没有这两样分层就只是把表换了个地方放而已。3. 核心细节拆解每一层到底该怎么建表3.1 ODS 层建表规范与抽取策略ODS 层的建表我的原则是“结构对齐、命名可读、分区合理”。结构对齐是指字段名和类型尽量跟源系统保持一致不要自作主张改名字否则后面核对数据的时候会很痛苦。命名可读是指表名要能看出是哪张源表、什么抽取频率比如ods_order_info_di表示订单信息表的日增量ods_user_profile_df表示用户画像表的日全量。分区合理是指按时间分区通常是按天这样增量抽取和回溯都很方便。抽取策略上全量和增量要分开处理。全量表每天覆盖一次适合数据量不大、变化频繁的维度表增量表按时间戳或者自增 ID 抽取适合数据量大、只追加的事实表。增量抽取的关键是幂等也就是说同一天的数据重复跑多次结果应该是一样的。实现方式通常是在抽取前先删除目标分区再重新写入或者用 merge 的方式按主键更新。-- ODS 层增量抽取示例伪代码展示逻辑 INSERT OVERWRITE TABLE ods_order_info_di PARTITION (dt ${bizdate}) SELECT order_id, user_id, order_amount, order_status, create_time, update_time FROM source_order_info WHERE update_time ${bizdate} 00:00:00 AND update_time ${bizdate} 23:59:59;这段 SQL 里INSERT OVERWRITE保证了幂等PARTITION按业务日期分区WHERE条件用update_time来圈定当天变化的数据。注意这里用的是update_time而不是create_time因为订单状态可能会变用创建时间会漏掉更新。注意ODS 层不要做业务逻辑的清洗比如不要把“男/女”转换成“1/0”不要把金额单位从分转成元。这些转换放在 DW 层做ODS 保持原始面貌方便核对。3.2 DW 层建模维度建模与范式建模的取舍DW 层是分层的核心也是建模工作量最大的地方。这里绕不开一个经典问题用维度建模还是范式建模。我的经验是DWD 层用维度建模的思路DWS 层用轻度聚合的思路两者结合既保证灵活性又保证性能。维度建模的核心是事实表和维度表。事实表存业务过程比如订单、支付、点击每条记录代表一次业务事件。维度表存描述信息比如用户、商品、门店每条记录代表一个实体。事实表通过外键关联维度表查询的时候做 join 就能拿到完整的业务视图。这种建模方式的优点是直观、查询友好缺点是维度变化的时候处理起来麻烦需要用到缓慢变化维的技术。范式建模则是把数据拆得很细消除冗余保证一致性。这种方式的优点是存储省、更新快缺点是查询的时候要 join 很多表对使用者的要求高。在数仓场景下纯范式建模很少见通常是维度建模为主、范式建模为辅在 DWD 层做一些适度的规范化。-- DWD 层订单明细表示例 CREATE TABLE dwd_order_detail_di ( order_id STRING COMMENT 订单ID, user_id STRING COMMENT 用户ID, product_id STRING COMMENT 商品ID, category_id STRING COMMENT 品类ID, order_amount DECIMAL(18,2) COMMENT 订单金额, discount_amount DECIMAL(18,2) COMMENT 优惠金额, pay_amount DECIMAL(18,2) COMMENT 实付金额, order_status STRING COMMENT 订单状态, create_time STRING COMMENT 创建时间, pay_time STRING COMMENT 支付时间 ) PARTITIONED BY (dt STRING COMMENT 业务日期) STORED AS ORC;这张表里order_status已经做了码值转换把源系统的各种状态统一成了标准值。pay_amount是计算字段等于order_amount减去discount_amount。这些加工都在 DWD 层完成下游直接用就行。3.3 DM 层面向应用的组装策略DM 层的设计思路跟 DW 层完全不同。DW 层追求的是通用和规范DM 层追求的是好用和快。同一个指标在不同场景下可能有不同的维度和粒度要求DM 层就是把这些要求消化掉给每个场景提供最合适的表。举个例子运营部门要看日活和留存那 DM 层就建一张dm_user_active_di按天存活跃用户数和次日、七日留存率。营销部门要看用户分群那就建一张dm_user_segment_df按用户标签存分群结果。风控部门要看异常交易那就建一张dm_trade_risk_di按交易存风险评分和规则命中情况。DM 层的表通常会有一些冗余这是故意的。比如用户分群表里可能会冗余用户的年龄、性别、城市这些维度这样营销部门用的时候不用再 join 用户表。冗余带来的存储成本跟查询性能的提升比起来通常是值得的。提示DM 层的表要控制数量不能每个报表都建一张。我的做法是按主题域来组织比如用户主题、交易主题、商品主题每个主题下建几张核心表覆盖大部分场景。特殊需求用视图或者临时表来解决不要轻易往 DM 层加表。4. 实操过程从零搭建一套分层体系的完整步骤4.1 第一步梳理数据源和业务过程动手建表之前先要把数据源摸清楚。我通常会做一张清单列出所有业务系统、每个系统里的核心表、表的更新频率、数据量级、有没有主键、有没有时间戳字段。这张清单是后续所有工作的基础宁可多花两天时间梳理也不要急着动手建表。梳理业务过程的时候我会跟业务方聊问他们“你们最关心哪些事情”。比如电商业务核心过程就是浏览、加购、下单、支付、退款。每个过程对应一张事实表过程的度量值就是事实表的字段过程的描述信息就是维度表的字段。这个过程不需要很精确先有个大概的框架后面再逐步完善。4.2 第二步设计分层结构和命名规范结构和规范要在动手之前定好否则后面改起来很痛苦。我的命名规范大致是这样的ODS 层用ods_前缀DW 层用dwd_和dws_前缀DM 层用dm_前缀。表名用英文小写加下划线能表达业务含义不要用拼音缩写。分区字段统一用dt格式是yyyyMMdd。分层结构上ODS 层按源系统分库DW 层按主题域分库DM 层按应用场景分库。比如ods_ecommerce、dwd_trade、dws_user、dm_operation。这样组织的好处是找表的时候先定位到库再定位到表路径很清晰。# 目录结构示例以 Hive 为例 /user/hive/warehouse/ ├── ods_ecommerce.db/ │ ├── ods_order_info_di/ │ ├── ods_user_info_df/ │ └── ods_product_info_df/ ├── dwd_trade.db/ │ ├── dwd_order_detail_di/ │ └── dwd_payment_detail_di/ ├── dws_user.db/ │ └── dws_user_action_1d/ └── dm_operation.db/ ├── dm_user_active_di/ └── dm_user_retention_di/4.3 第三步逐层实现并验证数据一致性建表之后要逐层验证数据。我的做法是每建完一层就跑几个核对 SQL确认数据量和关键指标跟上一层对得上。比如 ODS 层抽了 100 万条订单DWD 层清洗之后应该还是 100 万条除非有去重逻辑DWS 层按天汇总之后总金额应该跟 DWD 层一致。核对的时候要特别注意边界情况。比如跨天更新的订单在 ODS 层可能出现在两个分区里DWD 层去重之后只保留一条这时候数据量会对不上需要确认去重逻辑是否正确。还有空值处理源系统里某些字段可能为空DWD 层是保留空值还是给默认值这个要跟业务方确认清楚。-- 数据一致性核对示例 -- 核对 DWD 层和 ODS 层的订单金额是否一致 SELECT dwd AS layer, dt, COUNT(*) AS order_cnt, SUM(pay_amount) AS total_amount FROM dwd_order_detail_di WHERE dt 20240101 GROUP BY dt UNION ALL SELECT ods AS layer, dt, COUNT(*) AS order_cnt, SUM(order_amount - discount_amount) AS total_amount FROM ods_order_info_di WHERE dt 20240101 GROUP BY dt;这个核对 SQL 跑出来如果两层的order_cnt和total_amount一致说明清洗逻辑没问题。如果不一致就要去查是哪一步出了问题。4.4 第四步配置调度和监控分层体系建好之后要靠调度来驱动。通常的依赖关系是 ODS 先跑跑完触发 DWDW 跑完触发 DM。调度工具可以用开源的也可以用云上的服务关键是依赖配置要准确不能出现上游没跑完下游就开始跑的情况。监控要覆盖三个方面数据量监控如果某天数据量突然比平时少很多或者多很多要告警数据质量监控比如主键重复率、空值率、枚举值分布超过阈值要告警任务时效监控如果某个任务跑得比平时慢很多要提前发现避免影响下游。注意调度依赖不要配得太死比如 ODS 有十张表DW 只依赖其中五张那就只配这五张的依赖不要等所有 ODS 表都跑完才跑 DW。这样能缩短整体链路的时间也能避免因为一张不相关的表延迟导致整个链路卡住。5. 常见问题与排查技巧实录5.1 数据量对不上从分区到主键的排查路径数据量对不上是最常见的问题排查路径我总结了一个顺序先看分区再看主键最后看过滤条件。分区问题通常是上游写了多个分区下游只读了一个分区或者分区格式不一致导致读不到数据。主键问题通常是去重逻辑写错了比如用了错误的字段做 distinct导致数据被误删。过滤条件问题通常是 where 条件写得太严把一些边界数据过滤掉了。我遇到过一个典型案例ODS 层某天有 100 万条记录DWD 层只有 80 万条。查了半天发现是 DWD 层的 SQL 里有一个WHERE user_id IS NOT NULL而源数据里有 20 万条记录的user_id是空的。这些空值记录其实是游客行为业务上是有效的不应该被过滤掉。后来把条件改成WHERE user_id IS NOT NULL OR device_id IS NOT NULL数据量就对上了。5.2 口径不一致同一个指标在不同层算出不同结果口径不一致的问题根源通常是计算逻辑没有统一下沉。比如“活跃用户”这个指标ODS 层可能按登录算DWD 层可能按有行为算DM 层可能按有交易算三个层算出来三个数。解决的办法是把指标的定义和计算逻辑统一放在 DW 层DM 层只做引用不做重新计算。具体操作上我会在 DWS 层建一张“指标中间表”把常用指标的计算逻辑固化下来。比如dws_user_active_1d这张表每天算好活跃用户数、新增用户数、留存用户数DM 层直接用这些字段不再自己算。这样口径就统一了改逻辑也只需要改一个地方。问题现象可能原因排查方法解决方案数据量突然减半上游分区缺失或抽取条件变更检查 ODS 分区和抽取 SQL补数据并修复抽取逻辑指标数值偏高重复计算或 join 膨胀检查 join 条件和去重逻辑调整 join 方式或加 distinct指标数值偏低过滤条件过严或空值处理不当检查 where 条件和空值分布放宽条件或补默认值任务延迟数据倾斜或资源不足查看任务执行计划和资源队列优化 SQL 或调整资源配比5.3 性能瓶颈分层之后查询反而变慢了怎么办分层之后查询变慢通常是因为链路太长或者中间层数据量太大。链路长的问题可以通过在 DM 层做预聚合来解决把常用的聚合结果提前算好查询的时候直接读结果。中间层数据量大的问题可以通过分区裁剪、列裁剪、谓词下推这些优化手段来缓解。我自己的经验是DWS 层的表要控制好粒度。如果粒度太细比如按用户按小时汇总数据量可能比 DWD 层还大查询的时候反而更慢。粒度太粗比如按月汇总又满足不了按天查询的需求。通常按天、按用户、按核心维度汇总是一个比较合适的粒度能覆盖大部分场景。还有一个容易被忽略的点是小文件问题。分层之后每层每天都会产生一批文件如果分区太多、文件太小查询的时候打开文件的开销会很大。解决的办法是定期做小文件合并或者在写入的时候控制 reduce 的数量。这个在数据量大的时候特别明显我见过一个任务因为小文件太多查询时间从几秒变成了几分钟。5.4 避坑清单分层改造中容易踩的五个坑第一个坑是ODS 层做清洗。前面说过ODS 要保持原始清洗放 DW。我见过团队在 ODS 层就把手机号脱敏了结果后面要做用户画像的时候发现原始手机号没了没法做关联。第二个坑是DM 层直接读 ODS。短期看省事长期看是灾难。一旦 ODS 结构变化所有 DM 表都要改而且绕过了 DW 层的清洗逻辑数据质量没法保证。第三个坑是命名不规范。表名用拼音、用缩写、用日期后缀时间一长没人记得住。命名规范要在项目开始就定好并且严格执行。第四个坑是没有数据质量监控。分层之后链路变长问题发现得晚修复成本高。监控要在每一层都配上尤其是 ODS 到 DW 这一层是数据质量的关键卡点。第五个坑是过度设计。不是所有数据都要分三层有些临时数据、日志数据直接放 ODS 加一个简单的 DM 就够了。分层是为了解决问题不是为了好看。6. 分层体系的扩展方向与个人实践体会分层体系建好之后不是就一劳永逸了。业务在变数据在变分层体系也要跟着演进。我自己的做法是每半年做一次复盘看看哪些表没人用了可以下线哪些层的职责边界模糊了需要调整哪些新的数据源需要纳入体系。下线没人用的表特别重要我见过太多团队只建表不删表数仓里躺着几千张没人用的表维护成本高得吓人。扩展方向上有两个趋势值得关注。一个是数据湖和数仓的融合ODS 和 DW 的边界在变得模糊很多团队用数据湖来存原始数据和明细数据用数仓来做聚合和应用两者通过统一的元数据打通。另一个是实时分层传统的分层是按天调度的现在越来越多的场景需要实时数据ODS 用消息队列接DW 用流计算做DM 用分析型数据库提供查询整套链路是实时的。实时分层的复杂度比离线高很多建议先把离线分层做扎实再考虑实时。我个人在实际操作中的体会是分层这件事技术只占三成管理和规范占七成。工具再好如果团队不遵守命名规范、不维护元数据、不做数据质量监控分层体系很快就会退化成另一个“乱表堆”。反过来哪怕工具简陋一点只要规范执行到位分层体系也能跑得很好。所以如果你正在推动分层改造先把规范和流程定下来再选工具顺序不要反了。最后分享一个小技巧建分层体系的时候可以先从一两个核心业务过程开始跑通 ODS 到 DM 的完整链路验证没问题之后再逐步扩展到其他业务过程。不要一上来就全面铺开那样风险太大一旦某个环节出问题整个项目都可能被叫停。小步快跑逐步迭代是我试过最稳的推进方式。