数仓设计与 ETL 规范

数仓设计

划分ETL阶段工作重心，便于管理
降低开发和维护成本
减少需求变化带来的冲击
便于数据问题追踪

数仓分层规范

层级	层级全拼	中文说明	层级描述
DIM	dimension	维度层	该层为维度数据，维度是对具体分析对象的分析角度，维度要具备丰富的属性，历史信息的可追溯性，对通用的维表要保持一致性，避免维度二义性。
STG	stage	接口层	用于存储源系统主动推送到数仓的数据，存放未经过处理的原始数据至数据仓库系统，结构上与源系统保持一致，考虑后续可能追溯数据，因此对这一层不建议做过多的数据清洗工作，原封不动接入源数据即可，是数据仓库的数据准备区。
ODS	operation data store	源数据存储层	用于存储数仓主动抽取源系统的数据，存放未经过处理的原始数据至数据仓库系统，结构上与源系统保持一致，考虑后续可能追溯数据，因此对这一层不建议做过多的数据清洗工作，原封不动接入源数据即可，是数据仓库的数据准备区。
DWD	data warehouse detail	明细粒度事实层	以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细层事实表。也可以将明细事实表的某些重要维度属性字段做适当冗余，即宽表化处理。
DWS	data warehouse service	数据服务层（汇总）	以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表。构建命名规范、口径一致的统计指标，为上层提供公共指标。
DM	data market	数据集市层	按业务主题组织高度汇总数据，产出通用的指标数据，统一指标计算口径和方法，避免指标冗余计算导致数据不一致。面向具体统计需求的；按业务主题划分;对数据进行高度汇总；尽量避免dm层表依赖dm表。
ADS	application data store	数据应用层	存放数据产品个性化的统计指标数据。以宽表方式存放汇总拉通数据，关联维度表DIM与DWS层事实表，直接支持前端分析应用

各层物理表前缀

层名	物理表前缀	备注
STG	stg_	用于存储源系统主动推送到数仓或手工上传的数据
ODS	ods_	用于存储数仓主动抽取源系统的数据
DWD	dwd_	最细粒度的明细层事实表
DWS	dws_	公共粒度的汇总指标事实表
DIM	dim_	维度表
ADS	ads_	宽表方式存放汇总拉通数据

源系统命名规范

系统名称	全称命名	简称命名
SAP系统	System Applications and Products	SAP
CRM系统	Customer Relationship Management	CRM
POS系统	Point Of Sale	POS
积分宝系统	Cumulative Scoring	CS
主数据系统	Master Data System	MD
DMS系统	Dealer Management System	DMS
填报系统	Filling System	FS
手工文档数据	Manual File	MF

更新频率及增量方式规范

增量方式	备注	更新频率	备注
f	full-全量同步	h	hour 每小时更新
i	increment-增量同步	d	day 每天更新
i	increment-增量同步	w	week 每周更新
i	increment-增量同步	m	month 每月更新

数仓建表规范

数仓层级名称	数仓库表命名	审计字段命名	备注
STG	`schema_源系统_源系统表名_更新频率及增量方式` stg_tc_pos_item_info_df	create_time(创建时间) modify_time(修改时间) source(来源系统) delete_flag(删除标记)	表名统一小写行存表指定分布键(建议主键)和分区字段
ODS	`schema_源系统_源系统表名_更新频率及增量方式` ods_tc_pos_item_info_df	create_time(创建时间) modify_time(修改时间) source(来源系统) delete_flag(删除标记)	表名统一小写行存表指定分布键(建议主键)和分区字段
DWD	`schema_主题名称_交易表` dwd_tc_pos_item_info	create_time(创建时间) modify_time(修改时间) delete_flag(删除标记)	表名统一小写列存表指定分布键(建议主键)和分区字段
DWS	`schema_主题名称_聚合方式` dws_sales_info	create_time(创建时间) modify_time(修改时间) delete_flag(删除标记)	表名统一小写列存表指定分布键(建议主键)和分区字段
ADS	`schema_主题名称_聚合方式` ads_sales_info	create_time(创建时间) modify_time(修改时间) delete_flag(删除标记)	表名统一小写列存表指定分布键(建议主键)和分区字段
DIM	`schema_维度名称` dim_product		表名统一小写列存表指定分布键(建议主键)和分区字段

ETL 设计规范

目录规范

目录层级划分能更清晰地对众多的开发程序进行分类和管理。
仅使用字母、数字、下划线组成，不能用任何特殊字符。
目录层级名称应遵循短小精悍原则，目录名统一全部大写。
目录层级规范适用于工作流、数据流和数据集的层级划分。

项目目录

若项目上存在多个项目同时使用数据开发平台，那么建议在根目录下创建项目目录。如果一个项目中有多个子项目可以再进行子项目目录划分。

环境目录

一般情况下在项目不同的阶段会使用不同的环境开展对应的工作（较多的如开发环境、测试环境和生产环境等）对资源、账号和权限等进行隔离，以保证程序和数据的安全性。此时可不进行专门的环境目录区分。假如项目上数据开发平台只有一台服务器，那么建议设置不同的环境目录划分，具体环境目录由项目情况决定

开发环境：DEV
测试环境：TEST
生产环境：PRD

注意事项：对于不同的环境，为保证程序的一致性，可提前约定好开发内容迭代流程。

程序发布流程为 DEV -> TEST -> PRD，不允许从开发环境直接发布到生产环境的操作。

程序迭代流程为：

DEV 环境进行程序开发，开发完成后（包括功能性测试）发布到 TEST
TEST 环境进行充分测试，发现问题后返回DEV环境进行程序修改
TEST 环境测试无误后发布到 PRD，由 ETL Leader 统一做集成和调度

ETL任务文件放置目录以目标表所属分层为导向，即目标表属于ODS层，则任务文件存储位置为ODS目录。例如从SAP抽取数据到ODS和从CRM抽取数据到ODS层的ETL过程的所属数仓层级都是ODS层：

数据落地层：ODS
公共维度层：DIM
数据明细层：DWD
数据聚合层：DWS
数据宽表层：ADS

数据来源目录

根据不同的数据来源或源系统在每个环境的 ODS 层目录下都创建对应的目录，根据源业务系统名称进行命名。DWD及以上层级会进行数据整合，所以不需要再创建来源目录。以下为一些源业务系统名称（如有新增来源请在以下补充）：

SAP系统：SAP
OMS(旺店通)：WDT
淘数据：TAOSJ
生意参谋：SYCM
达摩盘：DMP
淘宝API：TB
京东API：JD
唯品会API：WPH
FTP上传手工数据：FTP
CRM系统：CRM
DMS分销商：DMS
分销SAP系统：DIST_SAP
主数据系统：SFA
防窜系统：IS
积分宝系统：JFB
爬虫中间库：PgSQL中间库
填报系统：填报系统FS

注：如有新增来源或系统的数据目录请在该文档中补充完善，以便于企业统一命名和文档传递。

ETL 命名规范

本节所述的是具体的个体的命名规范，如果需要划分目录进行管理请参照上节的目录层级划分和目录命名规范进行设计。

数据源命名规范

数据源链接名称规范:统一以“CNN_源库类型_数据库名_系统名”。
在数据源描述中写：源库业务名称全称或说明，如有详细描述需写入描述中，中间以“；”号分隔。
数据源类型及对应的连接串如下：

Connection Type	Connection Name
ORACLE	CNN_ORA_SAPSR3_SAP
MYSQL	CNN_MySQL_CRM_CRM
GreenPlum	CNN_GP_ETL_DW

工作流命名规范

ETL 任务设计以目标表为导向，目标表名称与任务名称一一对应，原则上，一个目标表只对应一个 ETL 任务：

ETL 工作流命名：命名方式是以 workflow 的缩写 wf 开头，后面跟上此工作流中的目标表名，最后跟上加载方式。
- 增量任务命名格式：wf_目标表名_i
- 全量任务命名格式：wf_目标表名_f
- 删除任务命名格式：wf_目标表名_d
非ETL类工作流命名：工作流执行的是一些表的更新、删除等非数据抽取的操作，以 wf 加上执行操作表的表名
- 命名格式：wf_主要操作表名
工作流描述中写入创建人、工作流的详细描述和说明，描述信息格式为：“从源库 xxx T+1增量抽取 xxx 表数据到目标库 xxx 的 xxx 表中”，工作流如果有逻辑处理需要简单说明处理逻辑，中间以“；”号分隔。

数据流命名规范

ETL 数据流命名：命名方式是以 dataflow 的缩写 df 开头，后面加上数据流中操作的目标表名，最后跟上加载方式。
- 增量任务命名格式：df_目标表名_i
- 全量任务命名格式：df_目标表名_f
- 删除任务命名格式：df_目标表名_d
数据流描述中写入创建人、工作流的详细描述和说明，描述信息格式为：“从源库 xxx T+1增量抽取 xxx 表数据到目标库 xxx 的 xxx 表中”，工作流如果有逻辑处理需要简单说明处理逻辑，中间以“；”号分隔。

参数命名规范

为了提高 ETL 程序的灵活性，需要设置一些公共的环境参数供任务在运行过程中动态使用，这些参数在运行过程中可以由运行人员根据实际的运行情况进行调整。

参数命名以 var 开头，后面加上参数含义的简写 var_参数含义
同一工作流中的参数名称不能重复
不同工作流中的相同参数名称要保持一致
数据开发平台中参数引用方式为 ${参数名}

数据集命名规范

数据开发平台的数据集通常用做工作流中数据输出和数据流中的数据源，有以下几种应用场景：

工作流中输出数据集用做数据流中的数据源
数据流中输出数据集用作业务层级进一步的分析
ETL 开发时非常复杂的数据逻辑中间可以使用数据集进行各个环节的逻辑验证
需要多次复用的 ETL 中间处理过程数据可以落入数据集

数据集命名规范使用 data set 的首字母 ds 作为前缀，后面加上数据集对应的表名（数仓中经过统一规范命名之后的表名）或者数据集的业务含义，命名的方式为：ds_数据集对应表名 或者 ds_数据集业务含义 如：门店维表 ds_dim_store。

ETL 开发规范

ODS 层开发规范

ODS 层 ETL 开发可以直接在工作流中完成。 ODS 层开发过程相对较简单，只需在抽取数据的同时加上审计字段（见下表）和增量条件即可，可在源表取数的 SQL 中完成。

数仓层级	数据集命名设计规范	审计字段命名规范	备注
ods	`分层schema_源系统_源系统表名` 示例：ods_wdt_ordernew	dw_create_dt(创建时间，默认当前时间) dw_modify_dt(修改时间，默认当前时间) data_source(数据源系统) delete_flag(删除标记，默认为 0)	表名统一小写指定去重主键

ODS 层创建目标表

字段类型：ODS层的字段类型参考数仓模型中的源跟目标表的字段类型对应关系
审计字段：添加数仓模型中 ODS 层所需要的4个审计字段
表类型：由于 ODS 层数据不进行分析，可以考虑建成行表
字符集：注意字符集统一，通常统一使用 UTF-8 字符集编码数据抽取

创建工作流

ODS 层目标表创建完成之后可以在数据开发平台对应的目录下创建工作流，工作流命名和描述要符合命名规范。

获取源表数据

拖入一个 SQL 组件到工作流中用于读取源表数据，主要工作是抽取数据，整个过程在不改变数据的本质情况下，做如下简单处理：

添加审计字段
添加增量条件，通常ETL通过时间戳方式增量抽取数据
统一日期格式和数据清洗等

原则上审计字段的值和增量条件的范围都需要使用参数进行统一规范。

数据在进入 ODS 层之前需要先进行清洗，结合数据梳理文档、指标体系文档等，确定清洗字段及清洗方式。将每个字段转换为遵循 ODS 标准的数据格式，对数据类型和数据格式进行转换，并对空字段赋予适当的缺省值，形成规整的数据结构。

源数据到 ODS 表需要先进行这部分操作，数据清洗规范：

问题分类	清洗数据	备注
格式统一	时间格式不统一	可统一为 yyyy-mm-dd hh:mi:ss
格式统一	时间格式错误	可处理为固定值 9999-12-31 00:00:00
格式统一	日期格式不统一	可统一为 yyyy-mm-dd
格式统一	日期格式错误	可处理为固定值 9999-12-31
格式统一	门店编码商品编码统一	创建编码对应关系表
格式统一	特殊符号空格	可去除特殊符号，除非有特殊含义
一致性	逻辑关系检查	优先级低
一致性	字典值统一，形成通用字典表	优先级低
准确性	极值问题	优先级低
完整性	缺失值问题、null 值问题	优先级低
唯一性	重复值问题	优先级低

DWD/DIM 层开发规范

DWD 层的 ETL 开发也推荐使用工作流的方式实现，因为数据从 ODS 到 DWD 是进行表之间按照主题的合并与划分，也就是表之间的关联，并没有涉及到复杂的逻辑转换和聚合等操作，直接通过编写 SQL 的方式就能够快速高效地实现DWD 层的数据逻辑。

DWD 层的工作流程序通常也是采用一个读取数据的 SQL 节点和一个插入数据的 SQL 节点来实现。

本项目中在 ODS 层跟 DWD 层是在同一个数据库不同 schema 的前提下，具体实施过程中考虑到类实时（如 hourly）数据更新频率的时间限制要求，可以根据数据仓库的配置、性能和并发负载等综合能力情况，在工作流的单个 SQL 节点的非查询功能中通过类存储过程的方式直接编写插入查询语句 INSERT … SELECT … 的方式实现 DWD 层的 ETL 逻辑。

DWS 层开发规范

DWS 层包括数据的纵向汇总和横向拉通两部分

事实表数据的纵向汇总就是在 DWD 层明细事实表的基础上直接进行各种不同粒度的汇总，每张汇总表只需要通过一个简单的分组汇总语句来实现，所以可以直接使用工作流来完成。

事实表汇总数据的横向拉通则会涉及到不同主题域数据的复杂关联和指标逻辑计算等内容，如指标值的差异、占比和同环比等。涉及到复杂关系的 ETL 过程建议使用数据开发平台的数据流，包括 DWS 层汇总后数据横向拉通和 ADS 层大宽表的 ETL 开发都使用数据流来完成。

DWS 拉通层开发步骤：

在相应的目录下创建数据流
根据数据来源数量拖入相应的输入数据集组件，选择前面生成的数据集
拖入需要的数据关联和数据编辑组件进行逻辑编辑
使用插入数据算子（组件）把结果数据插入到目标表
拖入输出数据集算子把结果数据生成一个数据集，用作 ADS 层的数据源

ADS 层开发规范

按照数仓规范，ADS 层是存放 BI 前端分析应用的个性化的统计指标数据，以宽表方式存放汇总拉通数据，关联维度表 DIM 与 DWS 层事实表。

为方便分析师能在项目上线后自助的进行 ADS 层宽表的扩展，ADS 层统一通过数据流来开发的。

对于相同的数据源，比如数仓 GP 库，DIM/DWD/DWS 这几层采用工作流开发使用到的两个 SQL 节点（先获取、再插入）可以替换为 1 个 SQL 节点 - “非查询模式”。这样就没有中间数据的落地过程，相当于 SQL 执行直接提交给 GP库上执行，因此性能会快一些，尤其是获取数据量较大的情况。因此对于如果没有注册数据集的情况，建议都使用“非查询”模式：insert into xxx select * xxx

作业集成、依赖和调度

集成类和调度类工作流命名：以 wf 加上集成的工作流层级、业务含义、调度频率等命名：wf_ods_sales_order_daily。

ETL 作业之间存在很多依赖，关系到相关数据表的加载顺序，本设计从两个方面体现 ETL 作业的依赖关系：一是每个工作流完成以后接着运行后续需要依赖该工作流完成的工作流，首先应等待第一个工作流完成之后再继续。二是将一组在同一时间开始运行并具有依赖关系的数据流组合在一起，开发成网状关系的工作流以供 ETL 的 schedule 调度用。

在 ETL 程序的抽取过程运行之前一定要保证其抽取的源数据已经准备就绪，根据不同的源系统可能需要分别采用不同的接口方式，通常采用时间约定相结合的方式，部分源系统可能增加数据就绪通知方式。

工作流依赖和并发控制

本项目中的 ETL 工作流采用层级依赖的方式来控制数据的加载顺序，即在 ODS 层的工作流完成之后再运行 DWD 和 DIM 层的 ETL 程序，同时不同源系统的数据可以并行加载。

但是由于源系统来源表数量较多，基于读取源系统数据的并发量、ETL 调度程序执行的并发量和写入目标数据库的并发量考虑，并发量过大时会对源系统、数据开发平台和目标系统都会造成较大压力，所以本项目中调度遵循以下原则：

不同源系统错开时间段进行数据抽取，以减轻开发平台和目标系统的负载压力
同一源系统的 ETL 程序本来没有依赖关系，通过设置依赖关系来控制并发数量，以减轻源系统的负载压力
同一时间点并行运行的 ETL 程序控制在 10 个以内

作业调度设置

只有日常数据加载才需要考虑进程调度的问题。而对于初始数据加载，由于是一次性的工作，将采取手工启动加载的方式。

ETL 作业进程调度的功能比较单纯，就是在规定的时刻启动程序，并记录系统运行情况和运行结果。不同数据表的更新周期不同，因此，进程调度需要能够支持日周月等多种不同的启动周期，并通过设定启动时间来确定每个任务在何时启动运行。定时调度中可以选择每天、每周、每月等定时策略，每天的时间点支持多选；也可以选择间隔时间策略。