简介本资源是一份面向数据工程师、数仓开发人员及大数据初学者的系统性数据模型设计指南聚焦数据仓库建设中的核心难点——如何科学分层与规范建模。文档深入解析ODS、CDM含DWD明细层、DWS汇总层、DIM维表层及ADS应用层的定位、边界与协同逻辑详述数据域划分、总线矩阵构建、原子/派生指标定义、一致性维表与事实表设计等关键方法并针对分层混乱、命名不统一、主键失当等高频问题提供可落地的解决方案与设计原则。资源为单文件Word文档.doc大小77KB内容结构清晰、术语准确、实操性强适合作为日常开发参考或团队建模规范宣贯材料。目前已有182人学习下载涵盖从模型设计入门到分层治理进阶的完整知识链。1. 数据仓库模型设计不是画ER图而是给企业建一座“数据立交桥”你有没有见过这样的场景运营同学要查“过去30天新用户在首单后7天内的复购率”提了需求等了三天被告知“这个指标底层没建模得从ODS重新跑”BI工程师做一张销售看板发现“省份”字段在用户表里叫province_name在订单表里叫prov_code在营销活动表里又变成region_id硬着头皮写三段JOIN结果某天源系统把prov_code字段类型从字符串改成整数整张看板直接报错更常见的是——数据团队开了十几次对齐会最后发现大家说的“活跃用户”A认为是“当日登录下单”B理解为“近7日有任意行为”C坚持必须包含“完成实名认证”而D默默在ADS层已经建了三个同名不同义的dws_user_active_7d表……这不是人的问题是数据模型没设计好。数据仓库模型设计根本不是程序员坐在工位上画几张星型模型图、敲几行CREATE TABLE就完事的事。它更像是城市规划师设计一座“数据立交桥”ODS是进城的原始国道车流混杂、标牌不一CDM是主干高架分快慢车道、设统一出口编号、限速明确ADS是直达商场的地下车库每个入口只通一个品牌专柜停车即用。桥没设计好车数据就堵在匝道DWS层导航BI查询总绕路送货指标交付永远迟到。这份《数据仓库数据模型该如何设计》文档就是那份被无数团队翻烂、边角卷起、贴满便签纸的“立交桥施工白皮书”——它不教你怎么写SQL而是告诉你哪条路该修成高速哪个出口必须预留扩容接口为什么“复购率”不能直接从订单表算而要先在DWD层把“用户-订单-商品”三者关系钉死成一张宽表。搞懂它你才能从“取数民工”变成“数据基建工程师”。2. 分层不是为了画PPT而是给每类数据配一把专属“钥匙”数据仓库分层常被误解为“领导喜欢的架构图装饰”。但真实世界里每一层的存在都对应着一类不可妥协的数据治理诉求谁负责维护、谁有权修改、谁来兜底质量、出问题时找谁。就像医院分诊台——不是为了好看而是让发烧病人不用和骨折患者挤同一部电梯。2.1 ODS层数据的“海关入境处”只登记不改写ODSOperational Data Store的本质是数据的原始护照。它不做逻辑判断不修正错误甚至不翻译字段名。它的唯一使命把业务系统ERP、CRM、小程序后台产生的原始数据像海关扫描集装箱一样原样、带时间戳、带来源标记地存进来。-- 典型ODS建表逻辑以MySQL为例 CREATE TABLE ods_order_2024 ( id BIGINT COMMENT 订单ID来自源系统order_id, user_id STRING COMMENT 用户ID来自源系统uid, status TINYINT COMMENT 订单状态0待支付,1已支付,2已发货...照搬源系统枚举值, create_time STRING COMMENT 字符串格式时间如2024-03-15 14:22:05不转为DATE类型, __source_system STRING COMMENT 标记来源crm_v2.3 / miniapp_v1.8 / erp_core, __load_time STRING COMMENT 本记录入库时间用于后续核对 ) PARTITIONED BY (dt STRING) -- 按天分区保留历史快照提示ODS表名必须带ods_前缀且后缀体现来源系统如ods_user_crm。字段注释里强制写明“来自源系统XXX”这是日后排查数据漂移的救命索引。为什么不能在ODS层清洗举个血泪经验某公司曾在ODS把user_id为空的订单直接过滤掉结果三个月后发现这批“空ID订单”其实是小程序未登录用户的匿名订单业务方正要用它们分析游客转化路径——数据已永久丢失。ODS的哲学是“你给我什么我存什么你要怎么用那是下一层的事”。2.2 CDM层数据的“中央厨房”把生肉ODS加工成标准半成品DWD/DWS/DIMCDMCommon Data Model是整个数仓的“心脏区”它由DWD、DWS、DIM三层组成分工明确层级全称核心任务类比生活关键约束DWD明细层基于业务过程建模生成最细粒度事实表如每笔订单、每次点击冗余关键维度属性如把province_id换成province_name菜市场摊主把活鱼宰杀去鳞按部位切块鱼头、鱼身、鱼尾每块贴上产地标签只能引用ODS、DIM禁止计算派生指标如“复购率”DIM维表层构建一致性维度表用户、商品、地域、时间确保全仓库“省份”只有一个定义城市标准地图所有楼盘、公交站、学校都按同一套行政区划编码标注主键必须全局唯一如dim_user.user_sk用MD5生成属性变更必须拉链处理start/end_timeDWS汇总层基于DWDDIM按公共粒度如“日/用户/省份”聚合指标生成宽表如dws_user_daily_summary中央厨房预制菜把鱼块、蔬菜、调料按固定比例配好装进统一餐盒只能引用DWD、DIM指标命名必须含原子指标名如pay_amt_sum而非total_money注意DWS不是“越多越好”。某电商曾建了200张DWS表结果发现80%的查询只用到其中7张。DWS存在的唯一理由下游至少3个ADS应用需要相同粒度的同一组指标。否则宁可在ADS层直接JOIN DWD。2.3 ADS层数据的“外卖专送站”直抵业务最后一公里ADSApplication Data Service是离业务最近的一层它不追求复用只追求快、准、易懂。这里没有“应该建什么”只有“业务现在要什么”。-- 某运营活动看板专用ADS表命名即含义 CREATE TABLE ads_promotion_conversion_dashboard ( dt DATE COMMENT 统计日期, promo_id STRING COMMENT 活动ID, promo_name STRING COMMENT 活动名称, new_user_cnt BIGINT COMMENT 新增用户数, pay_user_cnt BIGINT COMMENT 支付用户数, conversion_rate DECIMAL(5,4) COMMENT 转化率 pay_user_cnt / new_user_cnt, avg_order_amt DECIMAL(10,2) COMMENT 客单价 ) COMMENT 大促活动转化漏斗看板供BI拖拽使用;关键特征表名即业务语言ads_promotion_conversion_dashboard比dws_promo_summary更能让人一眼看懂字段即业务口径conversion_rate的计算逻辑必须在注释里写死避免BI工程师自行理解拒绝通用化这张表只为“大促看板”服务绝不允许其他部门拿去当“用户增长分析”的基础表。ADS层的黄金法则宁可多建10张专表也不建1张“万能表”。因为业务需求永远在变而“万能表”的字段会越加越多最终变成没人敢动的黑匣子。3. 模型设计不是自由创作而是遵守一套“数据宪法”很多团队模型混乱的根源在于把建模当成写作文——想怎么写就怎么写。但数据模型本质是一套契约它约定好了“用户”是什么、“订单”怎么定义、“复购”如何计算。违反契约的代价是全仓库的数据信任崩塌。3.1 高内聚低耦合把“一家人”关进同一间房把“邻居”隔开这是软件工程最古老的原则在数据建模中却常被忽视。错误做法把所有用户相关字段注册信息、行为日志、支付记录塞进一张dwd_user_all大宽表。结果呢注册字段更新频繁导致整张表每天全量重刷行为日志字段极多埋点JSON展开后上百列但90%的ADS只用其中5个某天支付系统增加“分期期数”字段为兼容老逻辑不得不给全表加默认值引发下游计算错误。正确解法按业务过程和访问模式拆分-- ✅ 高内聚用户注册事实表DWD CREATE TABLE dwd_user_register_fct ( user_sk STRING COMMENT 用户代理键, register_time DATETIME, channel STRING COMMENT 注册渠道微信/APP/网页, device_type STRING COMMENT 设备iOS/Android/H5 ); -- ✅ 高内聚用户支付事实表DWD CREATE TABLE dwd_user_pay_fct ( user_sk STRING, order_id STRING, pay_time DATETIME, pay_amt DECIMAL(10,2), installments TINYINT COMMENT 分期期数新字段不影响注册表 ); -- ✅ 低耦合两表通过user_sk关联但物理存储分离 -- 下游ADS若只需注册数据只读dwd_user_register_fct需支付数据则JOIN两者核心判断标准如果两个字段总是被一起查询、一起更新、业务含义强绑定才放同一张表。否则宁可多一次JOIN也要保持模型轻量。3.2 核心模型与扩展模型分离给“主干道”和“辅路”划清车道线所有模型都该有“主干”和“枝叶”之分。主干模型Core Model承载80%的通用需求必须稳定、简洁、高性能扩展模型Extension Model满足长尾需求允许试错、可随时下线。典型反例某公司dwd_order_fct表里既有order_id、user_id、pay_amt等核心字段又有delivery_address_json配送地址JSON、coupon_detail_json优惠券明细JSON等半结构化字段。结果核心字段查询因JSON解析拖慢3倍某次优惠券规则变更JSON结构升级导致所有依赖该表的ADS全部报错新增“订单履约时效”指标时开发被迫在核心表加first_pickup_time字段破坏了“订单事实”的单一职责。正确姿势-- ✅ 核心事实表只含原子字段 CREATE TABLE dwd_order_fct_core ( order_id STRING, user_id STRING, shop_id STRING, pay_amt DECIMAL(10,2), create_time DATETIME, pay_time DATETIME ); -- ✅ 扩展表独立存储按需JOIN CREATE TABLE dwd_order_fct_ext ( order_id STRING, delivery_address_json STRING, coupon_detail_json STRING, first_pickup_time DATETIME );血泪经验扩展字段上线前必须回答三个问题① 是否影响核心指标计算② 是否被超过2个ADS高频使用③ 是否有明确的下线计划任一答案为“否”就放进扩展表。3.3 公共逻辑下沉别让每个ADS都重复造轮子最典型的“重复造轮子”场景10个ADS都需要“用户最近一次下单时间”。如果每个ADS都写一遍ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY pay_time DESC)后果是计算资源浪费300%10次相同窗口函数某天业务要求“最近一次下单”改为“最近一次有效下单排除退款订单”要改10处代码其中3个ADS忘了改导致数据不一致。解决方案把公共逻辑封装进DWS层。-- ✅ 在DWS层统一计算dws_user_last_order_d CREATE TABLE dws_user_last_order_d AS SELECT user_id, MAX(pay_time) AS last_pay_time, COUNT(*) AS total_order_cnt FROM dwd_order_fct_core WHERE pay_status paid -- 此处统一定义“有效订单” GROUP BY user_id;原则凡是涉及业务规则、状态判断、复杂计算的逻辑必须下沉到CDM层。ADS层只做简单筛选WHERE、轻量聚合SUM/COUNT、业务定制化拼接如把用户昵称头像URL拼成user_profile字段。4. 常见误区你以为的“规范”可能是团队最大的技术债初学者最容易掉进的坑往往披着“高效”“灵活”“快速”的外衣。这些误区不会立刻暴雷但会在6个月后让整个数仓变成不敢动的“瓷器店”。4.1 误区一ADS可以直接读ODS省事又快为什么混刚上线时业务催得紧开发觉得“ODS里就有订单表直接查它不就完了”怎么区分✅可以临时救火如老板凌晨要一份数据明天就上线❌绝对禁止作为长期方案。ODS字段名、类型、枚举值随源系统变更ADS直接依赖等于把命交给业务系统。真实案例某金融公司ADS层有张ads_risk_monitor表直接JOINods_loan_apply。某天信贷系统升级把apply_status从0/1/2改为APPLYING/APPROVED/REJECTED导致风控看板所有状态显示为NULL实时监控中断4小时。正确姿势所有ADS必须通过DWD或DWS层获取数据。哪怕DWD还没建好也应先建一张最小化的dwd_loan_apply_fct只映射关键字段再让ADS依赖它。4.2 误区二DIM维表用源系统主键如user_id当代理键为什么混源系统主键天然唯一何必多此一举生成user_sk怎么区分✅源系统主键可用于查询关联JOIN条件但绝不能作为维度主键❌DIM主键必须是代理键Surrogate Key如MD5(CONCAT(user_id, source_system))。原因有三解决多源冲突用户A在CRM里user_id1001在小程序里user_id1001代理键可区分支持缓慢变化用户所在省份从“广东”变“深圳”代理键不变用拉链表记录变更屏蔽源系统变更源系统user_id类型从INT改为BIGINT代理键完全不受影响。4.3 误区三DWS汇总表越“宽”越好把所有可能用到的字段都冗余进去为什么混宽表查询快BI拖拽方便何乐不为怎么区分✅合理冗余维度属性如province_name,category_name且极少变更❌禁止冗余事实字段如pay_amt,order_cnt、半结构化字段JSON、高频更新字段如user_balance。性能真相某电商DWS宽表冗余了200字段单表大小超500GB。一次简单SELECT province_name, SUM(pay_amt)查询因需扫描全部列耗时47秒而拆分为dws_province_daily仅含地域指标后同样查询仅0.8秒。口诀DWS宽表只冗余“静态维度名”不冗余“动态事实值”。4.4 误区四分层边界模糊“DWS和ADS到底差在哪”为什么混文档写了定义但实际开发中有人把ADS表命名为dws_xxx有人把DWS表当ADS用。怎么区分用一张表诊断判断项DWS层ADS层建设目标支持≥3个ADS的公共指标服务1个具体业务场景数据粒度全局统一如“日用户省份”场景定制如“活动ID渠道小时”字段命名含原子指标名pay_amt_sum含业务术语gmv_today更新频率固定周期T1按需实时/分钟级/活动期间下游依赖多个ADS表仅BI看板/某个API自查工具如果一张表的注释里写着“供XX部门XX看板使用”它就是ADS如果写着“支撑用户域公共指标”它就是DWS。5. 什么时候该建DWS什么时候该砍掉DWS——用一张决策表终结纠结建DWS不是技术选择而是成本收益权衡。很多团队陷入“为建而建”的陷阱结果DWS成了吃资源不产出的“数据盆景”。下面这张表是我带过的5个团队踩坑后总结的DWS生死线。5.1 DWS建设决策表5个问题答错1个就暂停问题是否决策Q1这个指标是否被≥3个ADS应用高频使用高频每周查询≥5次或支撑核心报表✓✗否→跳过DWS让ADS直接JOIN DWDQ2这些ADS是否对指标口径、计算逻辑、更新时效有完全一致的要求如都要求“T1”都定义“复购7日内二次支付”✓✗否→必须拆分成多个DWS如dws_user_rebuy_7d_t1和dws_user_rebuy_7d_rtQ3DWD层是否已稳定提供所需原子数据DWD表无重大BUGSLA达标✓✗否→先修复DWDDWS是“果”DWD是“根”Q4当前DWS层同类粒度的表是否已超负荷如“日用户”粒度DWS表已达15张单日计算耗时超2小时✗✓是→优先优化现有DWS合并、分区裁剪而非新建Q5业务方是否愿意为DWS投入维护成本明确指定负责人承诺参与口径评审接受DWS变更通知✓✗否→暂缓DWS不是“技术自嗨”没有业务Owner的DWS注定废弃真实案例某直播平台想建dws_anchor_live_summary主播直播汇总表。按表自查Q1仅运营看板和主播激励系统2个应用使用 →否Q2运营要看“开播时长”激励系统要“有效开播时长观众≥10人” →否Q3DWD层dwd_live_session_fct存在数据延迟问题 →否。结论不建DWS。改为在ADS层各自实现等业务需求收敛、DWD稳定后再评估。5.2 DWS“砍伐指南”这3种DWS请立即下线不是所有DWS都值得保留。以下三类建议每季度巡检发现即下线幽灵DWS建表后6个月内无任何ADS或BI查询记录可通过数仓元数据系统查last_access_time孤儿DWS依赖的DWD表已下线或上游源系统已停运僵尸DWS字段中超过50%为NULL值或90%的查询只用其中3个字段说明设计过度冗余。操作步骤① 在调度系统中将该DWS任务置为“暂停”② 发邮件通知所有潜在使用者抄送数据负责人③ 设置30天观察期期间无反馈则执行DROP TABLE④ 将建表语句、血缘关系存入归档库备查。从那以后我每次设计DWS前都会打开这张表逐条打钩。不是为了“显得规范”而是因为少建一张无用的DWS就能省下2TB存储、每天1.7小时计算资源、以及未来3个工程师排查问题的200小时。数据基建不是堆砌而是克制不是画更大的饼而是切更准的刀。希望帮到你。本文还有配套的精品资源点击获取