数仓开发手册（1）--数仓分层与建模标准

一：背景

• 缺乏统一的标准，如：开发规范、指标口径等。
• 缺乏统一数据质量监控，如：数据缺失，字段数据不完整和不准确，数据发散等。
• 业务知识体系混乱，导致数据开发人员开发成本增加。
• 数据架构不合理，层级之间分工不明显，数据流向混乱。
• 缺失统一维度和指标管理。

二：目的

• 建立规范 ：规范是指群体所确立的行为标准。它们可以由组织正式规定，也可以是非正式形成。好的建模规范在前期可能会投入较大的成本，但在后期的收益必定会大于前期的投入。
• 建立参照物 ：在实际生产中指导同学开发，建设高质量的模型。
• 提供更通用性的解读 ： 目前主流的（the data warehouse toolkit,data warehouse,大数据之路等) 可能存在解读困难的问题,意在用 更易理解的话术解释。
• 适应场景开发：不同的公司类型在外部可能方法论类似，但是具体的细节会有巨大的差异，最适合的才是更好，部分细节需要根据公司特定场景定制。
• 给新同学学习 ：在新人入职后，可以通过阅读，了解具体的方法论，从而改变开发习惯，降低学习成本，达成复用。

三：数据仓库特点

3.1：面向主题

普通的操作型数据库主要面向事务性处理（oltp)，而数据仓库中的所有数据一般按照主题进行划分(olap)。主题是对业务数据的一种抽象，是从较高层次上对信息系统中的数据进行归纳和整理。 面向主题的数据可以划分成两部分----根据原系统业务数据的特点进行主题的抽取和确定每个主题所包含的数据内容。例如客户主题、产品主题、财务主题等；而客户主题包括客户基本信息、客户信用信息、客户资源信息等内容。分析数据仓库主题的时候，一般方法是先确定几个基本的主题，然后再将范围扩大，最后再逐步求精。

3.2：集成性

面向操作型的数据库通常是异构的、并且相互独立，所以无法对信息进行概括和反映信息的本质。而数据仓库中的数据是经过数据的抽取、清洗、切换、加载得到的，所以为了保证数据不存在二义性，必须对数据进行编码统一和必要的汇总，以保证数据仓库内数据的一致性。数据仓库在经历数据集成阶段后，使数据仓库中的数据都遵守统一的编码规则，并且消除许多冗余数据。

3.3：相对稳定性

数据仓库中的数据反映的都是一段历史时期的数据内容，它的主要操作是查询、分析而不进行一般意义上的更新（数据集成前的操作型数据库主要完成数据的增加、修改、删除、 查询），一旦某个数据进入到数据仓库后，一般情况下数据会被长期保留，当超过规定的期限才会被删除。通常数据仓库需要做的工作就是加载、查询和分析，一般不进行任何修改操作，是为了企业高层人员决策分析之用。

3.4：反应历史变化

数据仓库不断从操作型数据库或其他数据源获取变化的数据，从而分析和预测需要的历史数据，所以一般数据仓库中数据表的键码（维度）都含有时间键，以表明数据的历史时期信息，然后不断增加新的数据内容。通过这些历史信息可以对企业的发展历程和趋势做出分析和预测。数据仓库的建设需要大量的业务数据作为积累，并将这些宝贵的历史信息经过加工、整理，最后提供给决策分析人员，这是数据仓库建设的根本目的。

四： 数仓建模：

4.1 概述 ：

数据建模是一套方法论，主要是对数据的整合和存储做一些指导，强调从各个角度合理的存储数据。

4.2作用：

• 进行全面的业务梳理，改进业务流程

在业务模型建设的阶段，能够帮助我们的企业或者是管理机关对本单位的业务进行全面的梳理。通过业务模型的建设，我们应该能够全面了解该单位的业务架构图和整个业务的运行情况，能够将业务按照特定的规律进行分门别类和程序化，同时，帮助我们进一 步的改进业务的流程，提高业务效率，指导我们的业务部门的生产。

• 建立全方位的数据视角，消灭信息孤岛和数据差异

通过数据仓库的模型建设，能够为企业提供一个整体的数据视角，不再是各个部门只是关注自己的数据，而且通过模型的建设，勾勒出了部门之间内在的联系，帮助消灭各个部门之间的信息孤岛的问题，更为重要的是，通过数据模型的建设，能够保证整个企业的数据的一致性，各个部门之间数据的差异将会得到有效解决。

• 解决业务的变动和数据仓库的灵活性

通过数据模型的建设，能够很好的分离出底层技术的实现和上层业务的展现。当上层业务发生变化时，通过数据模型，底层的技术实现可以非常轻松的完成业务的变动，从而达到整个数据仓库系统的灵活性。

• 帮助数据仓库系统本身的建设

通过数据仓库的模型建设，开发人员和业务人员能够很容易的达成系统建设范围的界定，以及长期目标的规划，从而能够使整个项目组明确当前的任务，加快整个系统建设的速度。

4.3建模原则：

• 高内聚和低耦合

将业务相近或者相关、粒度相同的数据设计为一个逻辑或者物理模型：将高概率同 时访问的数据放一起 ，将低概率同时访问的数据分开存储。

• 核心模型与扩展模型分离

建立核心模型与扩展模型体系，核心模型包括的宇段支持常用的核心业务，扩展模型 包括的字段支持个性化或少量应用的需要 ，不能让扩展模型的宇段过度侵人核心模 型，以免破坏核心模型的架构简洁性与可维护性。

• 公共处理逻辑下沉及单一

越是底层公用的处理逻辑越应该在数据调度依赖的底层进行封装与实现，不要让公用 的处理逻辑暴露给应用层实现，不要让公共逻辑多处同时存在。

• 成本与性能平衡

适当的数据冗余可换取查询和刷新性能，不宜过度冗余与数据复制。

• 数据可回滚

处理逻辑不变，在不同时间多次运行数据结果确定不变。

• 一致性

具有相同含义的字段在不同表中的命名必须相同，必须使用规范定义中的名称。

• 命名清晰、可理解

表命名需清晰、一致，表名需易于消费者理解和使用。

4.4建模阶段划分

五：数仓分层

5.1 分层作用

• 清晰数据结构：每一个数据分层都有它的作用域，这样我们在使用表的时候能更方便地 定位和理解。

• 数据血缘追踪：简单来讲可以这样理解，我们最终给业务呈现的是一张能直接使用的业务表，但是它的来源有很多，如果有一张来源表出问题了，我们希望能够快速准确地定位到问题，并清楚它的危害范围。

• 减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算。

• 把复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。

5.2层级流向

ODS->DWD->DWB->DWS->ADS

正常流向：ODS->DWD->DWB->DWS->ADS，当出现ODS->DWD->DWS->ADS这种关系时，说明主题域未覆盖全。应将DWD数据落到DWB中，对于使用频度非常低的表允许DWD->DWS。

尽量避免出现DWS宽表中使用DWD又使用（该DWD所归属主题域）DWB的表。

同一主题域内对于DWB生成DWB的表，原则上要尽量避免，否则会影响ETL的效率。

DWB、DWS和ADS中禁止直接使用ODS的表， ODS的表只能被DWD引用。

禁止出现反向依赖，例如DWB的表依赖DWS的表。

5.3分层架构

暂无图

5.3.1缓冲层 EXT

对应与数仓中的ext层，一般只存放最近的数据，字段全是STRING类型，主要提供缓冲，且剥离源系统与仓内的差异，可以在清洗到贴源层的适合对源数据非结构化数据进行处理，一般在较为经典的数仓分成架构中使用，目前为采用该层。

5.3.2贴源层 ODS

作用：

主要完成业务系统、日志等结构化和半结构化数据引入到数据中台，保留业务系统原始数据，包括增量明细和全量明细。

规范：

• 不允许被查询，只允许被后续层级 etl 任务调用。

• 对源系统数据不做更改，作为业务库与数仓的隔离。

• 通常需要保留历史数据，作为回滚数据时需要

表命名规范

[模式名].ods_[业务板块缩写][源表名或者文件名][同步周期/同步策略]

「业务板块缩写」：可能设计到不同app的不同业务板块，需要通过多个下划线区分

5.3.3模型层 CDM

通用数据模型，主要完成公共数据加工与整合，建立一致性的维度，构建可复用面向分析和统计的明细事实表以及汇总公共粒度的指标。

事实表

事实表设计表完全依赖于现实中的物理活动，往往是一个物理活动对应一张表，,趋向于行上的增长，除数字度量外，事实表总是包含外键，用于关联与之相关的纬度表，也可能包含退化纬度或日期/时间戳。

一：明细事实层 公共明细层 DWD

定义

以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细层事实表，结合企业的数据使用特点，将明细事实表的某些重要维度属性字段做适当冗余，也就是宽表化处理。对数据进行规范化编码转换，清洗，统一格式，脱敏等，不做 横向整合。需要将 非脏数据 纬度外键为空或null的事实行 外键设为 “-99”, 对 为null 的事实不处理，现在处理可能会引起后面聚集的时候发生数据倾斜。

注： 最细粒度 可以是事实表的粒度无法继续往下拆解，也可以是认为定义该表的事实已是最细粒度。

表命名规范

[模式名].dwd_[数据域缩写][业务过程][表类型/同步周期/同步策略]

「数据域缩写」，「业务过程」 : 一般从预先定义的总线矩阵中获取。

1.事务事实表｜原子事实表

• 单事务

只取单个业务过程，多业务过程不冗余建设，浪费存储，但增强理解性

• 多事务

取多个类似的业务过程合并到同一物理表，冗余建设

• 实现方式：

• 通过增加不同的列代表不同的业务过程所带的事实

• 通过增加纬度判断区分，不冗余列

2. 累积事实表

累积事实表的行汇总了发生在过程开始和结束之间可预测步骤内的度量事件，管道或工作流流程，具有定义的开始点，中间过程和结束点。

适用于短生命周期的数据，如订单，运单。

是事实表中唯一允许被不断更新记录的表。通常会用来计算业务过程间的时长或迟到的事实。

累积事实表还有一个作用是作为线上镜像表的备份（流水表，日志表不需要），把每天增量的数据进行merge。

业务过程取舍：

在累计事实表中，需要多业务过程中进行取舍，将粒度相同的业务过程组成里程碑

无事实的事实表

不包含事实或度量的事实表称为“无事实的事实表“,虽然没有明确的事实，但可以用来支持业务过程的度量。

二：聚集事实层｜轻度汇总层 DWB | DW

定义

是原子事实表上的汇总数据，也称为汇总事实表。即新建立一个事实表，它的维度表是比原维度表要少，或者某些维度表是原维度表的子集，如用月份维度表代替日期维度表；事实数据是相应事实的汇总，即求和或求平均值等。

可以大幅的提升查询性能，但增加了存储成本，且限制了用户的自定义查询。如果发生与之关联的纬度发生缓慢变化，可能需要重刷聚集事实的所有分区；

表命名规范

[模式名].dwd_[数据域][粒度][采样周期][业务过程][同步周期/同步策略]

「数据域缩写」，「业务过程」 : 一般从预先定义的总线矩阵中获取。

「 粒度」 : 一般为事实表中纬度的外键代表组合而成

规范

• 只允许内部用户查询，如数仓侧，bi分析师等

• 一般只取实体进行聚集，非纬度属性，非杂项

• 需要将空事实置换为0

1.周期快照事实表

快照事实表的设计有一些区别于事务事实表设计的性质。事务事实表的粒度能以多种方式表达，但快照事实表的粒度通常以维度形式声明,事务事实表是稀疏的，但快照事实表是稠密的,事务事实表中的事实是完全可加的，但快照模型将至少包含一个用来展示半可加性质的事实。

适用于长生命周期的数据，如会员，商品

产出方式

• 基于明细表加工而成

• 基于ods表的快照

三：合并事实层 ｜ 融合模型层｜公共计算层 DWS

1.定义

以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表，以宽表化手段物理化模型。构建命名规范、口径一致的统计指标，为上层提供公共指标，建立汇总宽表、明细事实表。

• 不跨数据域，跨业务过程，进行横向整合。

2.表命名规范

[模式名].dws_[数据域缩写][粒度][分析主题自定义]_[同步周期/同步策略]

「数据域缩写」 : 一般从预先定义的总线矩阵中获取。

3. 规范

• 不跨数据域，跨业务过程，进行横向整合。

• 由于没有DW 集市层，该层开发的同时，需要做好数据权限的管理。

• 进行跨表合并的时候尽量采用mapjoin，将纬度表放在内存中。

• 该层需要将可能用到的纬度属性都退化在表中。

• 表的字段需要控制在250个字段内，如果超过，则需要考虑是否需要建设拓展模型，分离字段。

4.事实表技术

业务主键作自然键

• 在业务库中存在如商品id，用户id,同步到事实表中变成自然键 用NK标示。

退化纬度

• 退化业务主键

  当一个维度没有数据仓库需要的任何数据时就可以退化此维度。需要把退化维度的相关数据迁移到事实表中，然后删除退化的维度。

• 退化纬度属性

  维度属性也可以存储到事实表中，这种存储到事实表中的维度列被称为“退化维度”。与其他存储在维表中的维度一样 ，退化维度也可以用来进行事实表的过滤查询、实现聚合操作等

联合主键

• 在事实表中，粒度是由纬度组合或所代表的业务含义表达，可能是由一堆纬度的主键来组成联合主键。

多时区

• 应在事实表中设置双外键，对日期维进行多角色扮演。

宽表

• 宽表从字面意义上讲就是字段比较多的表。通常是指业务主题相关的指标、维度、属性 关联在一起的表。

通常出现在dws层，为了提升模型的复用性及减少查询时关联所带的消耗

手段通常为：冗余指标，冗余纬度属性

个性化模型

在实际场景中，往往会出现一些模型与其他大部分模型差异较大，如产出时间，统计口径等，这个时候可以对应产出个性化模型，如投放数据，可能需要根据投放时间 按天汇总，产出指标，则可建立对应的 个性化模型。

5. 维度表技术

每个维度表都包含单一的主键列。纬度表的主键可以作为任何与之关联的事实表的外键，纬度表通常比较宽，是扁平型非规范表，包含大量的低粒度的文本属性。

注：事实表更趋向于行的增长，而纬度表非如此，如果发生如事实表中增加一行，纬度表中也需要的增加一行的情况，则需要考虑 是否需要将该纬度的主键退化到事实表中，并将其所附带的纬度属性拆分到对应的纬度表中。

SCD｜缓慢变化维

• 保持原样

  纬度属性不发生变化，如日期，地域，用户首次注册信息

• 重写

  覆盖纬度属性：可能需要将对应的聚集事实后的层重刷，具体需要结合业务场景

• 增加新行 --需维护代理键

增加新行，分配新代理键，保持原有的自然键，或设定超持久自然键，需要添加行开始结束标示及当前状态。

​ 这种方法需要用代理键的方式构建维表，会增加很多的etl任务，以hive构建的数仓中不建议

• 增加新属性 --需维护代理键

不分配新行，在原表中改目前的纬度属性，适合需要关注之前与现在纬度的场景

​ 这种场景不太多，不建议使用

• 增加微型纬度 --需维护代理键

将变化或不变及变化较快的纬度进行剥离，拆出新的微型纬度，其主键需要维护在事实表中

• 增加微型纬度及重写标签 --需维护代理键

为快速变化的纬度建立微型纬度，当纬度变化时重写主维中的外键

• 重写标签到新增新行 --需维护代理键

• 双类型重写及新增新行 --需维护代理键

• 天全量快照 --需采用快照的方式

• 基于纬度属性的变化对天全量快照做拉链 --需采用快照的方式

<待补充>

原样查询： 通过限制 start_date 和 end_date 进行查询

极限存储 ：

• 杂项维

  大部分文本描述符将冗余在事实表中，将部分常用，可枚举的杂项冗余到一张杂项维中，杂项维需要绑定数据域

  业务库的数据字典一般可以当作杂项维存储

  规范

l 基于反范式及层级结构冗余构建

l 将业务库指示符，转为描述性词

l 使用unknown代替未关联到或没有的纬度属性

l 依据参照完整性，应在维表中用-99代表事实表中未关联到的记录，用unknown代替null值

一致性纬度

• 交叉探查 两个纬度中具有相同的纬度属性，可以按照此纬度属性进行关联

• 共用纬度 公用一个纬度表

• 一致性子集及上卷 其中一张维表 是另一张的子集 比如商品表中已经冗余了类目的信息，则可以进行跨钻

超自然持久键，持久键及自然键

• 自然键，如员工编号。
• 持久键：数据仓库为员工编号创建一个单一键，这个单一键保持永久性不会发生变化。有时也被叫做超自然持久键。

​ 最后的持久键应该独立于原始的业务过程。

多值纬度属性，非规范化属性

• 降低维表粒度

• 预先构建多列

• 使用支架表

• 抽象纬度

抽象纬度

例：将扫描员，派件员，员工等都归到“人”的纬度

热可交换纬度

上卷,下钻：

• 下钻：数据明细，粗粒度到细粒度的过程，会细化某些维度 下钻是商业用户分析数据的最基本的方法。下钻仅需要在查询上增加一个维度属性，附 加在 SQL 的 GROUP BY 语句中。属性可以来自任何与查询使用的事实表关联的维度。下钻不 需要存在层次的定义或是下钻路径。

• 上卷：数据的汇总聚合，细粒度到粗粒度的过程,会无视某些维度

拉链表制作

1.目标表A 2.新增数据表B

2.选出目标表 2999-12-31 分区数据

A有B无 不动

A没有B有：

​ 新增开始日期为数据日期 结束时间为2999-12-31

A有B有：

​ A表中数据结束日期改为统计日前一天，B中的数据开始日期为数据日期，结束日期为2999-12-31

3.结果集采用全关联，并且再union all

• 作用

通用数据模型，主要完成公共数据加工与整合，建立一致性的维度，构建可复用面向分析和统计的明细事实表以及汇总公共粒度的指标。

6. 模型层技术

数据冗余

• 冗余纬度

  冗余字段的引入不应造成其本身的刷新完成时间过多后延

  需要被查询命中，常当做分组或表头标示

• 冗余指标

  冗余字段与表中其它字段高频率同时访问

  冗余字段的引入不应造成其本身的刷新完成时间过多后延

数据拆分与合并

• 水平拆分 将经常用到的 where 条件按记录行进行水平切分或者冗余

• 水平合并 将业务过程类似或相同的数据在水平上进行合并

• 垂直拆分 在物理上划分核心模型和扩展模型，将其字段进行垂直划分；将访问相关度较高的列在一个表存储，将访问相关度较低相关的字段分开存储

• 垂直合并 将业务过程类似或相同的表但是拥有的不同列，但又必须带的字段进行冗余

5.3.4 应用层 | ADS

作用

应用层数据，提供直接面向业务应用的数据。为方便实现数据应用、数据消费的诉求，进行数据形式的组装，进行面向应用逻辑的数据加工处理。

表命名规范

[模式名].ads_([分析域])[专题主题自定义][数据周期/表类型]

「分析域]」：一般会预先在文档中的分析域中定义，有数仓侧和PD一起定制。

5.3.5临时表层｜ TMP

作用

用于存放临时数据，通常为bi部门需要自定义查询的数据集或etl任务中由于数据量太大或临时表拆解下逻辑 需要中间层落地。

表命名规范

[模式名].tmp_[实体自定义][建表人][日期]