简介本资源是一份面向互联网行业数据工程师与数仓架构师的《数据仓库模型建设规范1.0》实操型技术文档聚焦解决中大型企业级数据仓库物理建模混乱、分层职责不清、命名不统一等落地难题。文档系统定义了数聚模型的三层架构L0准备层、L1原子层、L2应用层详述各层表类型临时表L0_TMP、接口表L0_DCI、维度表DW_DIM、原子事实表L1_DW_FACT、宽表L2_FACT等的加载逻辑、历史处理策略、命名规范及开发要点并对比Inmon与Kimball建模方法的适用场景强调维度建模在稳定性、自适应性与可扩展性上的工程实践价值。资源为单个249KB的Word文档.docx内容完整覆盖从数据抽取清洗到OLAP支撑的全链路设计约束结构清晰、术语规范、示例具体便于团队快速对齐建模标准并落地实施。目前已有137人学习下载适合正开展数仓体系建设或需统一建模口径的中高级数据研发人员参考使用。1. 这份《数据仓库模型建设规范1.0》不是文档模板而是数仓团队的“施工红线图”你刚接手一个互联网公司新立项的数据中台项目ODS层刚接完12个业务系统的日志和数据库Binlog但开发组在建L1层时卡住了维表要不要加代理键事实表的粒度定到“用户单次点击”还是“用户日汇总”临时表命名该用TMP_POS_ORDER还是L0_TMP_POS_ORDER没人敢拍板——因为没人知道哪个选择会把后续3个月的ETL任务拖进性能泥潭。这份《数据仓库模型建设规范1.0》要解决的正是这种“技术决策真空”它不教你怎么写SQL而是用可执行的命名规则、强制的字段清单、分层的数据流向图把模糊的“应该怎么做”变成明确的“必须怎么建”。它面向的是正在落地真实互联网业务如电商订单履约、用户行为分析、广告效果归因的数仓工程师尤其适合那些已跑通数据接入但正面临模型混乱、口径不一、查询变慢的中型团队。规范里没有空泛的“高可用”“高性能”口号所有条款都指向一个结果当BI同事问“上月华东区新客复购率是多少”你能5分钟内从L1原子事实表组织维时间维精准定位到那张表、那个字段、那个分区。2. 数聚三层架构L0/L1/L2不是概念分层而是数据生命周期的硬性阶段切分数聚模型将数据仓库划分为准备层L0、原子层L1和应用层L2这三层不是逻辑抽象而是物理隔离的数据处理阶段每一层都有不可绕过的数据结构、强制的命名规则和明确的加工边界。跳过某一层或混淆层间职责是导致后续模型崩坏的最常见原因。2.1 L0准备层临时表与接口表的“双轨制”数据落地机制L0层的核心矛盾是源系统数据原始性与下游加工可用性之间的张力。规范强制采用“临时表→接口表→转换表”的三段式落地流程杜绝直接从源库抽取后直连L1的做法。临时表L0_TMP_仅保存本次抽取的快照数据无历史保留。全量抽取时存源表全量增量抽取时只存自上次抽取以来变更的记录依赖源系统流水号或时间戳。其存在意义是为清洗提供“无污染”的原始沙盒。接口表L0_DCI_必须保存完整历史。即使源系统做全量抽取接口表也需通过MERGE或UPSERT逻辑保证历史数据不丢失。这是L0层最关键的契约下游任何模块包括L1维度建模都只能读取接口表不得跨层访问临时表。转换表L0_MAP_纯中间产物用于复杂清洗逻辑如多源地址标准化、敏感字段脱敏、空值填充策略。规范要求其命名必须体现映射关系例如L0_MAP_USER_CONTACT表示用户联系方式清洗逻辑。提示互联网场景下高频出现的“用户设备ID跨天漂移”问题必须在L0_MAP层解决。不能等到L1再处理——因为L1事实表粒度若定为“日”则漂移ID会导致同一天产生两条冲突记录。正确做法是在L0_MAP_USER_DEVICE中增加device_id_fingerprint字段用UAIP设备特征生成稳定指纹此逻辑必须固化在转换表脚本中。2.1.1 L0层命名规范与实操校验表规范对L0层命名有强约束违反即视为模型缺陷。以下为关键命名规则及验证命令以Oracle为例表类型命名格式示例验证SQL检查是否符合临时表L0_TMP_[源系统]_[业务]或L0_TMP_[主题]_[业务]L0_TMP_POS_SALESORDERSELECT table_name FROM user_tables WHERE REGEXP_LIKE(table_name, ^L0_TMP_[A-Z]_[A-Z_]$);接口表L0_DCI_[主题]_[业务]L0_DCI_SALES_SALESORDERSELECT table_name FROM user_tables WHERE REGEXP_LIKE(table_name, ^L0_DCI_[A-Z]_[A-Z_]$);转换表L0_MAP_[业务]L0_MAP_SALESSELECT table_name FROM user_tables WHERE REGEXP_LIKE(table_name, ^L0_MAP_[A-Z_]$);执行上述SQL后若返回空集说明存在命名违规表必须立即整改。互联网业务中订单、支付、用户行为等核心主题的接口表命名一致性直接影响后续L1层维度关联的准确性。2.2 L1原子层维度表与事实表的“宪法级”字段清单L1层是模型稳定性的基石。规范对维度表和事实表提出不可协商的字段要求这些字段不是“建议添加”而是“缺失即不可上线”。2.2.1 维度表强制字段与互联网典型实现维度表必须包含以下6个字段缺一不可字段名类型必填互联网场景说明DIM_KEYINTEGER✓代理键全局唯一整型ID禁止用UUID或业务码。电商用户维中DIM_KEY1000001对应某用户与源系统user_idU123456解耦。KEY_STARTDATEDATE✓生效起始时间精确到秒。用户维中某用户手机号变更新记录KEY_STARTDATE2023-10-01 08:30:00。KEY_ENDDATEDATE✓生效截止时间当前有效记录设为9999-12-31。避免NULL值影响索引效率。CURRENT_FLAGCHAR(1)✓Y/N标识当前有效。BI工具常以此字段快速过滤最新维度。BUSINESS_KEYVARCHAR2(50)✓源系统业务主键如user_id、product_sku。用于溯源和问题排查。DIM_NAMEVARCHAR2(100)✓展示名称如用户昵称、商品标题。避免在报表中直接拼接字段。-- 创建电商用户维度表的标准DDLOracle CREATE TABLE DW_DIM_USER ( DIM_KEY INTEGER PRIMARY KEY, KEY_STARTDATE DATE NOT NULL, KEY_ENDDATE DATE NOT NULL, CURRENT_FLAG CHAR(1) NOT NULL CHECK (CURRENT_FLAG IN (Y,N)), BUSINESS_KEY VARCHAR2(50) NOT NULL, DIM_NAME VARCHAR2(100) NOT NULL, GENDER VARCHAR2(10), CITY VARCHAR2(50), REGISTER_TIME DATE, -- 其他属性... CONSTRAINT PK_DIM_USER PRIMARY KEY (DIM_KEY) ); -- 强制索引位图索引适用于低基数字段如GENDER、CURRENT_FLAG CREATE BITMAP INDEX IDX_DIM_USER_GENDER ON DW_DIM_USER(GENDER); CREATE BITMAP INDEX IDX_DIM_USER_CURRFLAG ON DW_DIM_USER(CURRENT_FLAG);注意互联网用户维常含“设备类型”“渠道来源”等动态属性规范要求这些必须作为维度属性非代理键存在而非拆分成独立小维表。例如DEVICE_TYPE字段值为IOS/ANDROID/WEB直接放在DW_DIM_USER中避免为每个属性建维表导致关联爆炸。2.2.2 事实表类型选择事务事实表 vs 快照事实表的决策树事实表类型决定数据粒度和存储成本。规范给出明确判断路径选事务事实表当业务过程是离散事件且需分析事件链路时。✅ 适用场景用户点击流click_id,page_url,event_time、订单创建order_id,product_id,create_time、支付成功pay_id,amount,pay_time。❌ 禁止场景计算“账户余额”——因为余额是状态非事件。选快照事实表当需分析某个时间点的状态快照且状态变化频率可控时。✅ 适用场景每日用户留存快照stat_date,user_key,is_retained_7d、每月商品库存快照stat_month,product_key,stock_qty。❌ 禁止场景实时风控评分——因快照周期无法满足毫秒级响应。-- 电商用户日点击事务事实表L1_DW_FACT_USER_CLICK CREATE TABLE L1_DW_FACT_USER_CLICK ( FACT_KEY INTEGER PRIMARY KEY, USER_KEY INTEGER NOT NULL, -- 关联DW_DIM_USER.DIM_KEY PAGE_KEY INTEGER NOT NULL, -- 关联DW_DIM_PAGE.DIM_KEY CLICK_TIME DATE NOT NULL, SESSION_ID VARCHAR2(100), DURATION_SEC NUMBER(10), -- 事实指标完全可加性 CLICK_COUNT NUMBER(5) DEFAULT 1, -- 外键索引位图索引提升OLAP查询 CONSTRAINT FK_FACT_USER_CLICK_USER FOREIGN KEY (USER_KEY) REFERENCES DW_DIM_USER(DIM_KEY), CONSTRAINT FK_FACT_USER_CLICK_PAGE FOREIGN KEY (PAGE_KEY) REFERENCES DW_DIM_PAGE(DIM_KEY) ); -- 创建位图索引针对高并发聚合查询 CREATE BITMAP INDEX IDX_FACT_CLICK_USERKEY ON L1_DW_FACT_USER_CLICK(USER_KEY); CREATE BITMAP INDEX IDX_FACT_CLICK_PAGEKEY ON L1_DW_FACT_USER_CLICK(PAGE_KEY); CREATE INDEX IDX_FACT_CLICK_TIME ON L1_DW_FACT_USER_CLICK(CLICK_TIME) LOCAL;提示互联网埋点数据量极大事务事实表必须按时间分区如PARTITION BY RANGE (CLICK_TIME)。规范要求分区粒度与业务分析需求对齐——若90%查询按“日”过滤则用日分区若需高频查“小时趋势”则必须细化到小时分区否则全表扫描将拖垮集群。3. 维度建模实战从电商订单业务过程到星形模式的四步推演维度建模不是画ER图而是将业务语言翻译成可执行的数据结构。规范强调的“四步法”必须严格遵循跳步即埋坑。我们以互联网电商“订单履约”业务过程为例演示如何落地。3.1 第一步锁定业务过程——拒绝模糊需求定义原子事件业务方说“我们要看订单数据”。这不够。规范要求必须拆解为不可再分的业务过程。电商订单履约链条包含ORDER_CREATED订单创建PAYMENT_RECEIVED支付成功WAREHOUSE_PICKED仓库拣货LOGISTICS_DISPATCHED物流发货CUSTOMER_RECEIVED客户签收关键决策本模型聚焦ORDER_CREATED过程。理由它是所有后续状态的源头且互联网订单创建事件具有强时效性秒级、高价值GMV核心、低歧义支付前状态最稳定。注意若同时建PAYMENT_RECEIVED事实表必须确保其ORDER_KEY与ORDER_CREATED表中的ORDER_KEY完全一致同一代理键否则多事实表关联将产生笛卡尔积。规范强制要求所有订单相关事实表共享同一订单维度代理键体系。3.2 第二步声明粒度——用一句话定义事实表每一行的含义粒度声明是建模成败的分水岭。错误示例“订单数据”——太模糊。正确声明规范要求书面写入设计文档“L1_DW_FACT_ORDER_CREATED事实表的每一行代表一个用户在某一时刻创建的一个订单粒度为‘单订单单商品’即一个订单含多个商品时拆分为多行。”此声明直接决定维度选择必须包含USER_KEY用户、TIME_KEY创建时间、PRODUCT_KEY商品、ORDER_KEY订单主键事实选择ORDER_AMOUNT订单金额、QUANTITY商品数量必须是单行可加的排除项ORDER_TOTAL_AMOUNT订单总金额不能作为事实因为它在“单商品行”上无意义。3.3 第三步选定维度——从粒度反推最小维度集合根据“单订单单商品”粒度强制存在的维度有DW_DIM_USER用户维度提供用户画像新老客、地域、设备DW_DIM_TIME时间维度提供DAY_KEY、HOUR_KEY、WEEK_KEYDW_DIM_PRODUCT商品维度提供类目、品牌、价格带DW_DIM_ORDER订单维度提供订单类型普通/团购/预售、渠道APP/小程序/H5提示互联网场景中“订单维度”常被忽略。但规范强调订单类型、优惠类型满减/折扣券/红包、配送方式快递/同城急送等强分析属性必须沉淀为独立维度表而非塞进事实表。原因维度表可建立层次结构如ORDER_TYPE → SUB_TYPE支持钻取分析而事实表字段无法分层。3.4 第四步确定事实——区分可加性、半可加性与非可加性事实必须严格匹配粒度。对ORDER_CREATED事实表完全可加性事实可任意维度聚合QUANTITY商品数量、DISCOUNT_AMOUNT单品优惠额——按用户、时间、商品类目求和均有业务意义。半可加性事实仅部分维度可加ORDER_AMOUNT订单金额——按用户、商品类目可加但按时间维度加总“日订单金额”有意义加总“小时订单金额”可能失真因跨小时订单被重复计算。非可加性事实禁止放入事实表ORDER_STATUS订单状态码、PROMOTION_NAME促销名称——这些是描述性属性必须移入DW_DIM_ORDER维度表。-- L1_DW_FACT_ORDER_CREATED标准结构关键事实字段 CREATE TABLE L1_DW_FACT_ORDER_CREATED ( FACT_KEY INTEGER PRIMARY KEY, ORDER_KEY INTEGER NOT NULL, -- 订单维度代理键 USER_KEY INTEGER NOT NULL, -- 用户维度代理键 PRODUCT_KEY INTEGER NOT NULL, -- 商品维度代理键 TIME_KEY INTEGER NOT NULL, -- 时间维度代理键如20231001 QUANTITY NUMBER(10) NOT NULL, -- 完全可加 DISCOUNT_AMOUNT NUMBER(12,2), -- 完全可加 ORDER_AMOUNT NUMBER(12,2), -- 半可加按订单维度聚合时需去重 -- 外键约束 CONSTRAINT FK_FACT_ORD_USER FOREIGN KEY (USER_KEY) REFERENCES DW_DIM_USER(DIM_KEY), CONSTRAINT FK_FACT_ORD_PROD FOREIGN KEY (PRODUCT_KEY) REFERENCES DW_DIM_PRODUCT(DIM_KEY), CONSTRAINT FK_FACT_ORD_TIME FOREIGN KEY (TIME_KEY) REFERENCES DW_DIM_DATE(DATE_KEY) );4. 缓慢变化维SCD的五种实现模式互联网高频变更场景的选型指南互联网业务中用户属性手机号、地址、商品属性价格、类目、组织属性部门归属、汇报线频繁变更。若不处理历史分析将失真。规范定义了5种SCD模式每种对应特定变更场景选错即导致数据口径灾难。4.1 SCD Type 1覆盖更新——仅适用于“不关心历史”的元数据适用场景维度表中纯粹的元数据修正如用户维中DIM_NAME拼写错误张三丰→张三丰、商品维中BRAND_NAME笔误苹国→苹果。操作直接UPDATE原记录不新增行不修改KEY_STARTDATE。风险若误用于业务属性如用户城市变更将导致历史订单全部归属到新城市。-- 修正用户昵称Type 1 UPDATE DW_DIM_USER SET DIM_NAME 张三丰, KEY_MODIFYDATE SYSDATE WHERE BUSINESS_KEY U123456 AND CURRENT_FLAG Y;4.2 SCD Type 2新增版本——互联网最常用模式支撑“时间旅行”分析适用场景用户城市变更、商品类目调整、员工部门调动等需保留历史轨迹的变更。核心机制新记录插入原记录KEY_ENDDATE置为变更时间CURRENT_FLAGN新记录KEY_STARTDATE为变更时间CURRENT_FLAGY。互联网特化规范要求KEY_STARTDATE/KEY_ENDDATE必须精确到秒并建立组合索引。-- 用户U123456从北京调至上海Type 2 -- 步骤1失效原记录 UPDATE DW_DIM_USER SET KEY_ENDDATE TO_DATE(2023-10-01 09:15:22, YYYY-MM-DD HH24:MI:SS), CURRENT_FLAG N, KEY_MODIFYDATE SYSDATE WHERE BUSINESS_KEY U123456 AND CURRENT_FLAG Y; -- 步骤2插入新记录 INSERT INTO DW_DIM_USER ( DIM_KEY, KEY_STARTDATE, KEY_ENDDATE, CURRENT_FLAG, BUSINESS_KEY, DIM_NAME, CITY ) VALUES ( SEQ_DIM_USER.NEXTVAL, TO_DATE(2023-10-01 09:15:22, YYYY-MM-DD HH24:MI:SS), TO_DATE(9999-12-31, YYYY-MM-DD), Y, U123456, 张三丰, 上海 );提示Type 2模式下事实表关联维度必须带上时间条件。例如查“2023年9月北京用户订单”SQL必须为FROM L1_DW_FACT_ORDER_CREATED f JOIN DW_DIM_USER u ON f.USER_KEY u.DIM_KEY AND f.TIME_KEY BETWEEN u.KEY_STARTDATE AND u.KEY_ENDDATE若漏掉时间条件将关联到所有版本导致订单重复计数。4.3 SCD Type 3新旧字段并存——适用于“最多两次变更”的轻量场景适用场景用户手机号变更通常一生换1-2次、商品主图URL更新。结构在维度表中增加OLD_PHONE、NEW_PHONE、PHONE_CHANGE_DATE字段。优势无需关联历史表查询简单存储开销小。限制规范明文禁止用于超过2次变更的属性如用户地址变更超2次必须用Type 2。-- 用户维扩展Type 3 ALTER TABLE DW_DIM_USER ADD ( OLD_MOBILE VARCHAR2(20), NEW_MOBILE VARCHAR2(20), MOBILE_CHANGE_DATE DATE );4.4 SCD Type 4历史拉链表——专治“层级结构深度变更”的互联网顽疾适用场景组织架构调整如事业部拆分、区域行政划分变更如“江苏南京”→“江苏南京市玄武区”、产品类目树重构。机制单独建DW_DIM_ORG_HIST历史表记录每次组织变更的快照主维表DW_DIM_ORG只存当前结构。互联网价值解决“某订单归属哪个组织”的历史性难题。例如2023年Q1订单应归属原“华东大区”Q2后归属新“华东销售部”历史表可精准回溯。-- 组织历史表Type 4 CREATE TABLE DW_DIM_ORG_HIST ( HIST_KEY INTEGER PRIMARY KEY, ORG_KEY INTEGER NOT NULL, -- 关联主维表ORG_KEY ORG_CODE VARCHAR2(20), ORG_NAME VARCHAR2(100), PARENT_ORG_CODE VARCHAR2(20), EFFECTIVE_DATE DATE NOT NULL, EXPIRE_DATE DATE NOT NULL, IS_CURRENT CHAR(1) CHECK (IS_CURRENT IN (Y,N)) );4.5 SCD Type 6混合模式——互联网复杂场景的终极方案适用场景需同时满足“快速查当前值”“精准查历史值”“支持多版本对比”的高阶需求如风控模型中的用户信用等级变更、推荐算法中的用户兴趣标签演化。结构融合Type 1当前值、Type 2历史版本、Type 3最近两次变更于一体。规范要求必须包含CURRENT_VALUE、HIST_VERSION、PREV_VALUE三字段。-- 用户信用等级维Type 6 CREATE TABLE DW_DIM_CREDIT_LEVEL ( DIM_KEY INTEGER PRIMARY KEY, BUSINESS_KEY VARCHAR2(50) NOT NULL, CURRENT_LEVEL VARCHAR2(20), -- Type 1当前等级 PREV_LEVEL VARCHAR2(20), -- Type 3上一次等级 LEVEL_CHANGE_DT DATE, -- Type 3上次变更时间 HIST_VERSION INTEGER, -- Type 2历史版本号 START_DT DATE, -- Type 2生效时间 END_DT DATE, -- Type 2失效时间 CURRENT_FLAG CHAR(1) -- Type 2当前标识 );提示Type 6虽强大但规范强制要求“仅在业务方书面确认需多版本对比时启用”。因其维护成本最高互联网团队常因滥用导致维度表膨胀。实际项目中80%场景用Type 2即可覆盖。5. 数据库物理设计位图索引、分区与统计信息的互联网级调优实践规范第6章的数据库设计条款不是纸上谈兵。在互联网海量数据场景下一个索引类型选错、一个分区键设偏、一次统计信息未更新都可能让TB级查询从秒级退化到小时级。5.1 维度表索引位图索引Bitmap Index是OLAP查询的加速器互联网分析常需多维交叉过滤如“上海女性用户在iOS端点击首页Banner”此时B-tree索引效率低下。规范强制要求所有低基数维度属性GENDER、DEVICE_TYPE、CURRENT_FLAG必须建位图索引高基数字段USER_NAME、EMAIL禁用位图索引易引发锁争用维度表主键DIM_KEY必须建B-tree唯一索引。-- 电商用户维索引策略Oracle -- 位图索引低基数字段支持AND/OR高效合并 CREATE BITMAP INDEX IDX_DIM_USER_GENDER ON DW_DIM_USER(GENDER); CREATE BITMAP INDEX IDX_DIM_USER_DEVICE ON DW_DIM_USER(DEVICE_TYPE); CREATE BITMAP INDEX IDX_DIM_USER_CURRFLAG ON DW_DIM_USER(CURRENT_FLAG); -- B-tree索引主键和高基数字段 CREATE UNIQUE INDEX PK_DIM_USER ON DW_DIM_USER(DIM_KEY); CREATE INDEX IDX_DIM_USER_EMAIL ON DW_DIM_USER(EMAIL); -- 仅当需按邮箱查询时注意位图索引在高并发DML场景下有锁粒度问题。规范要求互联网业务中维度表加载必须在业务低峰期如凌晨2-4点批量执行并在加载后立即重建位图索引避免在线更新导致索引失效。5.2 事实表分区按时间分区是互联网数据的生命线TB级事实表若不分区单次全表扫描将耗尽集群资源。规范规定事务事实表必须按TIME_KEY如CLICK_TIME范围分区粒度与业务分析周期一致快照事实表按STAT_DATE如20231001列表分区分区键必须是事实表中真实存在的字段禁止用函数如TRUNC(CLICK_TIME)。-- 用户点击事实表日分区 CREATE TABLE L1_DW_FACT_USER_CLICK ( FACT_KEY INTEGER, USER_KEY INTEGER, CLICK_TIME DATE, ... ) PARTITION BY RANGE (CLICK_TIME) ( PARTITION P_20230901 VALUES LESS THAN (TO_DATE(2023-09-02, YYYY-MM-DD)), PARTITION P_20230902 VALUES LESS THAN (TO_DATE(2023-09-03, YYYY-MM-DD)), PARTITION P_20230903 VALUES LESS THAN (TO_DATE(2023-09-04, YYYY-MM-DD)), PARTITION P_MAX VALUES LESS THAN (MAXVALUE) );5.3 装载后统计信息ANALYZE TABLE不是可选项是上线必检项数据库优化器依赖统计信息生成执行计划。互联网场景中ETL任务常批量插入百万级数据若未更新统计信息优化器仍按旧数据量估算导致选择全表扫描而非索引扫描。-- 规范强制要求每次L1层表装载完成后执行 ANALYZE TABLE L1_DW_FACT_USER_CLICK COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO; ANALYZE TABLE DW_DIM_USER COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO; -- 验证统计信息是否更新Oracle SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name IN (L1_DW_FACT_USER_CLICK, DW_DIM_USER);提示互联网团队常因疏忽遗漏此步。规范要求将ANALYZE语句嵌入ETL作业末尾并设置告警——若last_analyzed时间早于当前ETL任务开始时间则触发企业微信告警。这是保障查询性能的最后一道防线。本文还有配套的精品资源点击获取 互联网数据仓库模型建设规范：L0/L1/L2分层与SCD实战
简介本资源是一份面向互联网行业数据工程师与数仓架构师的《数据仓库模型建设规范1.0》实操型技术文档聚焦解决中大型企业级数据仓库物理建模混乱、分层职责不清、命名不统一等落地难题。文档系统定义了数聚模型的三层架构L0准备层、L1原子层、L2应用层详述各层表类型临时表L0_TMP、接口表L0_DCI、维度表DW_DIM、原子事实表L1_DW_FACT、宽表L2_FACT等的加载逻辑、历史处理策略、命名规范及开发要点并对比Inmon与Kimball建模方法的适用场景强调维度建模在稳定性、自适应性与可扩展性上的工程实践价值。资源为单个249KB的Word文档.docx内容完整覆盖从数据抽取清洗到OLAP支撑的全链路设计约束结构清晰、术语规范、示例具体便于团队快速对齐建模标准并落地实施。目前已有137人学习下载适合正开展数仓体系建设或需统一建模口径的中高级数据研发人员参考使用。1. 这份《数据仓库模型建设规范1.0》不是文档模板而是数仓团队的“施工红线图”你刚接手一个互联网公司新立项的数据中台项目ODS层刚接完12个业务系统的日志和数据库Binlog但开发组在建L1层时卡住了维表要不要加代理键事实表的粒度定到“用户单次点击”还是“用户日汇总”临时表命名该用TMP_POS_ORDER还是L0_TMP_POS_ORDER没人敢拍板——因为没人知道哪个选择会把后续3个月的ETL任务拖进性能泥潭。这份《数据仓库模型建设规范1.0》要解决的正是这种“技术决策真空”它不教你怎么写SQL而是用可执行的命名规则、强制的字段清单、分层的数据流向图把模糊的“应该怎么做”变成明确的“必须怎么建”。它面向的是正在落地真实互联网业务如电商订单履约、用户行为分析、广告效果归因的数仓工程师尤其适合那些已跑通数据接入但正面临模型混乱、口径不一、查询变慢的中型团队。规范里没有空泛的“高可用”“高性能”口号所有条款都指向一个结果当BI同事问“上月华东区新客复购率是多少”你能5分钟内从L1原子事实表组织维时间维精准定位到那张表、那个字段、那个分区。2. 数聚三层架构L0/L1/L2不是概念分层而是数据生命周期的硬性阶段切分数聚模型将数据仓库划分为准备层L0、原子层L1和应用层L2这三层不是逻辑抽象而是物理隔离的数据处理阶段每一层都有不可绕过的数据结构、强制的命名规则和明确的加工边界。跳过某一层或混淆层间职责是导致后续模型崩坏的最常见原因。2.1 L0准备层临时表与接口表的“双轨制”数据落地机制L0层的核心矛盾是源系统数据原始性与下游加工可用性之间的张力。规范强制采用“临时表→接口表→转换表”的三段式落地流程杜绝直接从源库抽取后直连L1的做法。临时表L0_TMP_仅保存本次抽取的快照数据无历史保留。全量抽取时存源表全量增量抽取时只存自上次抽取以来变更的记录依赖源系统流水号或时间戳。其存在意义是为清洗提供“无污染”的原始沙盒。接口表L0_DCI_必须保存完整历史。即使源系统做全量抽取接口表也需通过MERGE或UPSERT逻辑保证历史数据不丢失。这是L0层最关键的契约下游任何模块包括L1维度建模都只能读取接口表不得跨层访问临时表。转换表L0_MAP_纯中间产物用于复杂清洗逻辑如多源地址标准化、敏感字段脱敏、空值填充策略。规范要求其命名必须体现映射关系例如L0_MAP_USER_CONTACT表示用户联系方式清洗逻辑。提示互联网场景下高频出现的“用户设备ID跨天漂移”问题必须在L0_MAP层解决。不能等到L1再处理——因为L1事实表粒度若定为“日”则漂移ID会导致同一天产生两条冲突记录。正确做法是在L0_MAP_USER_DEVICE中增加device_id_fingerprint字段用UAIP设备特征生成稳定指纹此逻辑必须固化在转换表脚本中。2.1.1 L0层命名规范与实操校验表规范对L0层命名有强约束违反即视为模型缺陷。以下为关键命名规则及验证命令以Oracle为例表类型命名格式示例验证SQL检查是否符合临时表L0_TMP_[源系统]_[业务]或L0_TMP_[主题]_[业务]L0_TMP_POS_SALESORDERSELECT table_name FROM user_tables WHERE REGEXP_LIKE(table_name, ^L0_TMP_[A-Z]_[A-Z_]$);接口表L0_DCI_[主题]_[业务]L0_DCI_SALES_SALESORDERSELECT table_name FROM user_tables WHERE REGEXP_LIKE(table_name, ^L0_DCI_[A-Z]_[A-Z_]$);转换表L0_MAP_[业务]L0_MAP_SALESSELECT table_name FROM user_tables WHERE REGEXP_LIKE(table_name, ^L0_MAP_[A-Z_]$);执行上述SQL后若返回空集说明存在命名违规表必须立即整改。互联网业务中订单、支付、用户行为等核心主题的接口表命名一致性直接影响后续L1层维度关联的准确性。2.2 L1原子层维度表与事实表的“宪法级”字段清单L1层是模型稳定性的基石。规范对维度表和事实表提出不可协商的字段要求这些字段不是“建议添加”而是“缺失即不可上线”。2.2.1 维度表强制字段与互联网典型实现维度表必须包含以下6个字段缺一不可字段名类型必填互联网场景说明DIM_KEYINTEGER✓代理键全局唯一整型ID禁止用UUID或业务码。电商用户维中DIM_KEY1000001对应某用户与源系统user_idU123456解耦。KEY_STARTDATEDATE✓生效起始时间精确到秒。用户维中某用户手机号变更新记录KEY_STARTDATE2023-10-01 08:30:00。KEY_ENDDATEDATE✓生效截止时间当前有效记录设为9999-12-31。避免NULL值影响索引效率。CURRENT_FLAGCHAR(1)✓Y/N标识当前有效。BI工具常以此字段快速过滤最新维度。BUSINESS_KEYVARCHAR2(50)✓源系统业务主键如user_id、product_sku。用于溯源和问题排查。DIM_NAMEVARCHAR2(100)✓展示名称如用户昵称、商品标题。避免在报表中直接拼接字段。-- 创建电商用户维度表的标准DDLOracle CREATE TABLE DW_DIM_USER ( DIM_KEY INTEGER PRIMARY KEY, KEY_STARTDATE DATE NOT NULL, KEY_ENDDATE DATE NOT NULL, CURRENT_FLAG CHAR(1) NOT NULL CHECK (CURRENT_FLAG IN (Y,N)), BUSINESS_KEY VARCHAR2(50) NOT NULL, DIM_NAME VARCHAR2(100) NOT NULL, GENDER VARCHAR2(10), CITY VARCHAR2(50), REGISTER_TIME DATE, -- 其他属性... CONSTRAINT PK_DIM_USER PRIMARY KEY (DIM_KEY) ); -- 强制索引位图索引适用于低基数字段如GENDER、CURRENT_FLAG CREATE BITMAP INDEX IDX_DIM_USER_GENDER ON DW_DIM_USER(GENDER); CREATE BITMAP INDEX IDX_DIM_USER_CURRFLAG ON DW_DIM_USER(CURRENT_FLAG);注意互联网用户维常含“设备类型”“渠道来源”等动态属性规范要求这些必须作为维度属性非代理键存在而非拆分成独立小维表。例如DEVICE_TYPE字段值为IOS/ANDROID/WEB直接放在DW_DIM_USER中避免为每个属性建维表导致关联爆炸。2.2.2 事实表类型选择事务事实表 vs 快照事实表的决策树事实表类型决定数据粒度和存储成本。规范给出明确判断路径选事务事实表当业务过程是离散事件且需分析事件链路时。✅ 适用场景用户点击流click_id,page_url,event_time、订单创建order_id,product_id,create_time、支付成功pay_id,amount,pay_time。❌ 禁止场景计算“账户余额”——因为余额是状态非事件。选快照事实表当需分析某个时间点的状态快照且状态变化频率可控时。✅ 适用场景每日用户留存快照stat_date,user_key,is_retained_7d、每月商品库存快照stat_month,product_key,stock_qty。❌ 禁止场景实时风控评分——因快照周期无法满足毫秒级响应。-- 电商用户日点击事务事实表L1_DW_FACT_USER_CLICK CREATE TABLE L1_DW_FACT_USER_CLICK ( FACT_KEY INTEGER PRIMARY KEY, USER_KEY INTEGER NOT NULL, -- 关联DW_DIM_USER.DIM_KEY PAGE_KEY INTEGER NOT NULL, -- 关联DW_DIM_PAGE.DIM_KEY CLICK_TIME DATE NOT NULL, SESSION_ID VARCHAR2(100), DURATION_SEC NUMBER(10), -- 事实指标完全可加性 CLICK_COUNT NUMBER(5) DEFAULT 1, -- 外键索引位图索引提升OLAP查询 CONSTRAINT FK_FACT_USER_CLICK_USER FOREIGN KEY (USER_KEY) REFERENCES DW_DIM_USER(DIM_KEY), CONSTRAINT FK_FACT_USER_CLICK_PAGE FOREIGN KEY (PAGE_KEY) REFERENCES DW_DIM_PAGE(DIM_KEY) ); -- 创建位图索引针对高并发聚合查询 CREATE BITMAP INDEX IDX_FACT_CLICK_USERKEY ON L1_DW_FACT_USER_CLICK(USER_KEY); CREATE BITMAP INDEX IDX_FACT_CLICK_PAGEKEY ON L1_DW_FACT_USER_CLICK(PAGE_KEY); CREATE INDEX IDX_FACT_CLICK_TIME ON L1_DW_FACT_USER_CLICK(CLICK_TIME) LOCAL;提示互联网埋点数据量极大事务事实表必须按时间分区如PARTITION BY RANGE (CLICK_TIME)。规范要求分区粒度与业务分析需求对齐——若90%查询按“日”过滤则用日分区若需高频查“小时趋势”则必须细化到小时分区否则全表扫描将拖垮集群。3. 维度建模实战从电商订单业务过程到星形模式的四步推演维度建模不是画ER图而是将业务语言翻译成可执行的数据结构。规范强调的“四步法”必须严格遵循跳步即埋坑。我们以互联网电商“订单履约”业务过程为例演示如何落地。3.1 第一步锁定业务过程——拒绝模糊需求定义原子事件业务方说“我们要看订单数据”。这不够。规范要求必须拆解为不可再分的业务过程。电商订单履约链条包含ORDER_CREATED订单创建PAYMENT_RECEIVED支付成功WAREHOUSE_PICKED仓库拣货LOGISTICS_DISPATCHED物流发货CUSTOMER_RECEIVED客户签收关键决策本模型聚焦ORDER_CREATED过程。理由它是所有后续状态的源头且互联网订单创建事件具有强时效性秒级、高价值GMV核心、低歧义支付前状态最稳定。注意若同时建PAYMENT_RECEIVED事实表必须确保其ORDER_KEY与ORDER_CREATED表中的ORDER_KEY完全一致同一代理键否则多事实表关联将产生笛卡尔积。规范强制要求所有订单相关事实表共享同一订单维度代理键体系。3.2 第二步声明粒度——用一句话定义事实表每一行的含义粒度声明是建模成败的分水岭。错误示例“订单数据”——太模糊。正确声明规范要求书面写入设计文档“L1_DW_FACT_ORDER_CREATED事实表的每一行代表一个用户在某一时刻创建的一个订单粒度为‘单订单单商品’即一个订单含多个商品时拆分为多行。”此声明直接决定维度选择必须包含USER_KEY用户、TIME_KEY创建时间、PRODUCT_KEY商品、ORDER_KEY订单主键事实选择ORDER_AMOUNT订单金额、QUANTITY商品数量必须是单行可加的排除项ORDER_TOTAL_AMOUNT订单总金额不能作为事实因为它在“单商品行”上无意义。3.3 第三步选定维度——从粒度反推最小维度集合根据“单订单单商品”粒度强制存在的维度有DW_DIM_USER用户维度提供用户画像新老客、地域、设备DW_DIM_TIME时间维度提供DAY_KEY、HOUR_KEY、WEEK_KEYDW_DIM_PRODUCT商品维度提供类目、品牌、价格带DW_DIM_ORDER订单维度提供订单类型普通/团购/预售、渠道APP/小程序/H5提示互联网场景中“订单维度”常被忽略。但规范强调订单类型、优惠类型满减/折扣券/红包、配送方式快递/同城急送等强分析属性必须沉淀为独立维度表而非塞进事实表。原因维度表可建立层次结构如ORDER_TYPE → SUB_TYPE支持钻取分析而事实表字段无法分层。3.4 第四步确定事实——区分可加性、半可加性与非可加性事实必须严格匹配粒度。对ORDER_CREATED事实表完全可加性事实可任意维度聚合QUANTITY商品数量、DISCOUNT_AMOUNT单品优惠额——按用户、时间、商品类目求和均有业务意义。半可加性事实仅部分维度可加ORDER_AMOUNT订单金额——按用户、商品类目可加但按时间维度加总“日订单金额”有意义加总“小时订单金额”可能失真因跨小时订单被重复计算。非可加性事实禁止放入事实表ORDER_STATUS订单状态码、PROMOTION_NAME促销名称——这些是描述性属性必须移入DW_DIM_ORDER维度表。-- L1_DW_FACT_ORDER_CREATED标准结构关键事实字段 CREATE TABLE L1_DW_FACT_ORDER_CREATED ( FACT_KEY INTEGER PRIMARY KEY, ORDER_KEY INTEGER NOT NULL, -- 订单维度代理键 USER_KEY INTEGER NOT NULL, -- 用户维度代理键 PRODUCT_KEY INTEGER NOT NULL, -- 商品维度代理键 TIME_KEY INTEGER NOT NULL, -- 时间维度代理键如20231001 QUANTITY NUMBER(10) NOT NULL, -- 完全可加 DISCOUNT_AMOUNT NUMBER(12,2), -- 完全可加 ORDER_AMOUNT NUMBER(12,2), -- 半可加按订单维度聚合时需去重 -- 外键约束 CONSTRAINT FK_FACT_ORD_USER FOREIGN KEY (USER_KEY) REFERENCES DW_DIM_USER(DIM_KEY), CONSTRAINT FK_FACT_ORD_PROD FOREIGN KEY (PRODUCT_KEY) REFERENCES DW_DIM_PRODUCT(DIM_KEY), CONSTRAINT FK_FACT_ORD_TIME FOREIGN KEY (TIME_KEY) REFERENCES DW_DIM_DATE(DATE_KEY) );4. 缓慢变化维SCD的五种实现模式互联网高频变更场景的选型指南互联网业务中用户属性手机号、地址、商品属性价格、类目、组织属性部门归属、汇报线频繁变更。若不处理历史分析将失真。规范定义了5种SCD模式每种对应特定变更场景选错即导致数据口径灾难。4.1 SCD Type 1覆盖更新——仅适用于“不关心历史”的元数据适用场景维度表中纯粹的元数据修正如用户维中DIM_NAME拼写错误张三丰→张三丰、商品维中BRAND_NAME笔误苹国→苹果。操作直接UPDATE原记录不新增行不修改KEY_STARTDATE。风险若误用于业务属性如用户城市变更将导致历史订单全部归属到新城市。-- 修正用户昵称Type 1 UPDATE DW_DIM_USER SET DIM_NAME 张三丰, KEY_MODIFYDATE SYSDATE WHERE BUSINESS_KEY U123456 AND CURRENT_FLAG Y;4.2 SCD Type 2新增版本——互联网最常用模式支撑“时间旅行”分析适用场景用户城市变更、商品类目调整、员工部门调动等需保留历史轨迹的变更。核心机制新记录插入原记录KEY_ENDDATE置为变更时间CURRENT_FLAGN新记录KEY_STARTDATE为变更时间CURRENT_FLAGY。互联网特化规范要求KEY_STARTDATE/KEY_ENDDATE必须精确到秒并建立组合索引。-- 用户U123456从北京调至上海Type 2 -- 步骤1失效原记录 UPDATE DW_DIM_USER SET KEY_ENDDATE TO_DATE(2023-10-01 09:15:22, YYYY-MM-DD HH24:MI:SS), CURRENT_FLAG N, KEY_MODIFYDATE SYSDATE WHERE BUSINESS_KEY U123456 AND CURRENT_FLAG Y; -- 步骤2插入新记录 INSERT INTO DW_DIM_USER ( DIM_KEY, KEY_STARTDATE, KEY_ENDDATE, CURRENT_FLAG, BUSINESS_KEY, DIM_NAME, CITY ) VALUES ( SEQ_DIM_USER.NEXTVAL, TO_DATE(2023-10-01 09:15:22, YYYY-MM-DD HH24:MI:SS), TO_DATE(9999-12-31, YYYY-MM-DD), Y, U123456, 张三丰, 上海 );提示Type 2模式下事实表关联维度必须带上时间条件。例如查“2023年9月北京用户订单”SQL必须为FROM L1_DW_FACT_ORDER_CREATED f JOIN DW_DIM_USER u ON f.USER_KEY u.DIM_KEY AND f.TIME_KEY BETWEEN u.KEY_STARTDATE AND u.KEY_ENDDATE若漏掉时间条件将关联到所有版本导致订单重复计数。4.3 SCD Type 3新旧字段并存——适用于“最多两次变更”的轻量场景适用场景用户手机号变更通常一生换1-2次、商品主图URL更新。结构在维度表中增加OLD_PHONE、NEW_PHONE、PHONE_CHANGE_DATE字段。优势无需关联历史表查询简单存储开销小。限制规范明文禁止用于超过2次变更的属性如用户地址变更超2次必须用Type 2。-- 用户维扩展Type 3 ALTER TABLE DW_DIM_USER ADD ( OLD_MOBILE VARCHAR2(20), NEW_MOBILE VARCHAR2(20), MOBILE_CHANGE_DATE DATE );4.4 SCD Type 4历史拉链表——专治“层级结构深度变更”的互联网顽疾适用场景组织架构调整如事业部拆分、区域行政划分变更如“江苏南京”→“江苏南京市玄武区”、产品类目树重构。机制单独建DW_DIM_ORG_HIST历史表记录每次组织变更的快照主维表DW_DIM_ORG只存当前结构。互联网价值解决“某订单归属哪个组织”的历史性难题。例如2023年Q1订单应归属原“华东大区”Q2后归属新“华东销售部”历史表可精准回溯。-- 组织历史表Type 4 CREATE TABLE DW_DIM_ORG_HIST ( HIST_KEY INTEGER PRIMARY KEY, ORG_KEY INTEGER NOT NULL, -- 关联主维表ORG_KEY ORG_CODE VARCHAR2(20), ORG_NAME VARCHAR2(100), PARENT_ORG_CODE VARCHAR2(20), EFFECTIVE_DATE DATE NOT NULL, EXPIRE_DATE DATE NOT NULL, IS_CURRENT CHAR(1) CHECK (IS_CURRENT IN (Y,N)) );4.5 SCD Type 6混合模式——互联网复杂场景的终极方案适用场景需同时满足“快速查当前值”“精准查历史值”“支持多版本对比”的高阶需求如风控模型中的用户信用等级变更、推荐算法中的用户兴趣标签演化。结构融合Type 1当前值、Type 2历史版本、Type 3最近两次变更于一体。规范要求必须包含CURRENT_VALUE、HIST_VERSION、PREV_VALUE三字段。-- 用户信用等级维Type 6 CREATE TABLE DW_DIM_CREDIT_LEVEL ( DIM_KEY INTEGER PRIMARY KEY, BUSINESS_KEY VARCHAR2(50) NOT NULL, CURRENT_LEVEL VARCHAR2(20), -- Type 1当前等级 PREV_LEVEL VARCHAR2(20), -- Type 3上一次等级 LEVEL_CHANGE_DT DATE, -- Type 3上次变更时间 HIST_VERSION INTEGER, -- Type 2历史版本号 START_DT DATE, -- Type 2生效时间 END_DT DATE, -- Type 2失效时间 CURRENT_FLAG CHAR(1) -- Type 2当前标识 );提示Type 6虽强大但规范强制要求“仅在业务方书面确认需多版本对比时启用”。因其维护成本最高互联网团队常因滥用导致维度表膨胀。实际项目中80%场景用Type 2即可覆盖。5. 数据库物理设计位图索引、分区与统计信息的互联网级调优实践规范第6章的数据库设计条款不是纸上谈兵。在互联网海量数据场景下一个索引类型选错、一个分区键设偏、一次统计信息未更新都可能让TB级查询从秒级退化到小时级。5.1 维度表索引位图索引Bitmap Index是OLAP查询的加速器互联网分析常需多维交叉过滤如“上海女性用户在iOS端点击首页Banner”此时B-tree索引效率低下。规范强制要求所有低基数维度属性GENDER、DEVICE_TYPE、CURRENT_FLAG必须建位图索引高基数字段USER_NAME、EMAIL禁用位图索引易引发锁争用维度表主键DIM_KEY必须建B-tree唯一索引。-- 电商用户维索引策略Oracle -- 位图索引低基数字段支持AND/OR高效合并 CREATE BITMAP INDEX IDX_DIM_USER_GENDER ON DW_DIM_USER(GENDER); CREATE BITMAP INDEX IDX_DIM_USER_DEVICE ON DW_DIM_USER(DEVICE_TYPE); CREATE BITMAP INDEX IDX_DIM_USER_CURRFLAG ON DW_DIM_USER(CURRENT_FLAG); -- B-tree索引主键和高基数字段 CREATE UNIQUE INDEX PK_DIM_USER ON DW_DIM_USER(DIM_KEY); CREATE INDEX IDX_DIM_USER_EMAIL ON DW_DIM_USER(EMAIL); -- 仅当需按邮箱查询时注意位图索引在高并发DML场景下有锁粒度问题。规范要求互联网业务中维度表加载必须在业务低峰期如凌晨2-4点批量执行并在加载后立即重建位图索引避免在线更新导致索引失效。5.2 事实表分区按时间分区是互联网数据的生命线TB级事实表若不分区单次全表扫描将耗尽集群资源。规范规定事务事实表必须按TIME_KEY如CLICK_TIME范围分区粒度与业务分析周期一致快照事实表按STAT_DATE如20231001列表分区分区键必须是事实表中真实存在的字段禁止用函数如TRUNC(CLICK_TIME)。-- 用户点击事实表日分区 CREATE TABLE L1_DW_FACT_USER_CLICK ( FACT_KEY INTEGER, USER_KEY INTEGER, CLICK_TIME DATE, ... ) PARTITION BY RANGE (CLICK_TIME) ( PARTITION P_20230901 VALUES LESS THAN (TO_DATE(2023-09-02, YYYY-MM-DD)), PARTITION P_20230902 VALUES LESS THAN (TO_DATE(2023-09-03, YYYY-MM-DD)), PARTITION P_20230903 VALUES LESS THAN (TO_DATE(2023-09-04, YYYY-MM-DD)), PARTITION P_MAX VALUES LESS THAN (MAXVALUE) );5.3 装载后统计信息ANALYZE TABLE不是可选项是上线必检项数据库优化器依赖统计信息生成执行计划。互联网场景中ETL任务常批量插入百万级数据若未更新统计信息优化器仍按旧数据量估算导致选择全表扫描而非索引扫描。-- 规范强制要求每次L1层表装载完成后执行 ANALYZE TABLE L1_DW_FACT_USER_CLICK COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO; ANALYZE TABLE DW_DIM_USER COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO; -- 验证统计信息是否更新Oracle SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name IN (L1_DW_FACT_USER_CLICK, DW_DIM_USER);提示互联网团队常因疏忽遗漏此步。规范要求将ANALYZE语句嵌入ETL作业末尾并设置告警——若last_analyzed时间早于当前ETL任务开始时间则触发企业微信告警。这是保障查询性能的最后一道防线。本文还有配套的精品资源点击获取