数据开发/数仓工程师上手指南(六)CDM-DWD层搭建规范及流程

发布于

前言

前言这次想聊点出工作技术方面外的内容，DWD该层在写CDM层的时候就提及很多次了大差不差，近期让我感悟较多的话那是那句宁愿犯错也不愿意什么都不做，说这句话如果能够做到，那么执行力也远超过其他人，能力和远见也会在历练中得到充分的成长。但是这也是要区分领域来说，一般我认为这句话的适用程度在自己的技术工作领域上面，可以去尝试换个技术，换个主题或者是从事新的第二事业，而不是在目前大环境下面去犯错，因为现在给你的容错机会极少，凡事还是要注意量与度。好了废话不多说，现在开始DWD层的数据建模。

明细粒度事实层（DWD）

明细粒度事实层（DWD）在数据仓库中是非常重要的一个层次，其设计与企业的业务过程紧密相关。通过理解DWD层的特点和设计方法，可以更好地支持企业的数据分析需求。

业务过程驱动建模：

  • DWD层以具体的业务过程为核心，通过分析业务过程的特点和需求，构建出最细粒度的明细事实表。这种建模方法能够更好地反映实际业务中的数据流动和变化。

宽表化处理：

  • 在设计明细粒度事实表时，可以对某些重要的维度属性字段进行适当冗余，即将这些字段直接存储在事实表中。这种处理方式可以加快查询速度，减少关联查询的复杂性。

我们以招标业务案例为例，在招标业务过程中，DWD层建模可以帮助企业深入分析和优化其招标活动：

在一个典型的招标业务过程中，主要步骤包括：

  1. 招标公告发布：企业发布招标公告，邀请供应商参与投标。
  2. 投标文件提交：供应商根据公告要求提交投标文件。
  3. 评标：企业评估投标文件，选择合适的供应商。
  4. 合同签署：与中标供应商签署合同。

1.业务过程驱动建模示例

事实表设计：

  • 粒度：每个投标的单独记录
  • 维度：
    • 时间维度：记录投标的时间点（提交日期、评标日期、合同签署日期）
    • 供应商维度：参与投标的供应商信息（供应商ID、供应商名称）
    • 项目维度：涉及的项目信息（项目ID、项目名称）
    • 状态维度：投标状态（已提交、评标中、中标、未中标）

事实表结构：

投标ID

项目ID

供应商ID

提交日期

评标日期

中标日期

投标金额

状态ID

度量：

  • 投标金额：可以汇总统计各项目的总投标金额、各供应商的投标金额等。

2.宽表化处理

宽表化处理示例

在事实表中进行适当的冗余，减少关联查询，提升查询效率。例如，将供应商和项目的一些重要属性直接存储在事实表中。

宽表化处理后的事实表结构：

投标ID

项目ID

项目名称

供应商ID

供应商名称

提交日期

评标日期

中标日期

投标金额

状态ID

  • 项目名称和供应商名称等重要属性冗余存储在事实表中，以加快查询速度。例如，在查询特定供应商在某个项目中的投标记录时，无需再关联查询项目和供应商维度表。

3.事实表的设计

3.1粒度的定义

  • 粒度表示一条记录在事实表中所表达的业务细节程度。选择合适的粒度是设计事实表的关键，通常可以通过以下两种方式来确定：
    • 维度属性组合：通过多个维度属性的组合来定义粒度。
    • 具体业务含义：直接基于业务过程的需求来定义粒度。

在招标业务中，我们可以通过多个维度属性的组合来定义粒度。例如，每一个投标记录可以由以下几个维度属性组合定义其粒度：

  • 项目ID：标识哪个项目正在招标。
  • 供应商ID：标识哪个供应商提交了投标。
  • 投标日期：标识投标提交的具体时间。

这种粒度定义确保每一条记录都代表一个特定供应商在某一项目中的一次投标行为。

具体业务含义

从业务的角度来看，每个记录代表着一项具体的业务活动——即一个供应商对一个项目的投标。这种业务导向的粒度定义有助于直接反映业务过程中的实际交易活动。

3.2事实的类型

  • 可加性事实：可以在所有相关维度上进行汇总，如销售额、数量等。
  • 半可加性事实：只能在特定维度上汇总，如库存只能在地点或商品维度上汇总。
  • 不可加性事实：如比率型数据，需要通过分解为可加组件进行汇总。

在招标过程中，不同的度量数据可以属于不同的事实类型：

  • 可加性事实：
    • 投标金额：可以对不同维度（如时间、项目、供应商）进行汇总以了解总的投标金额。
  • 半可加性事实：
    • 投标数量：虽然可以对项目和供应商维度进行汇总，但对时间维度的汇总（例如跨月）可能不总是有意义，因为投标活动可能仅限于特定时间段。
  • 不可加性事实：
    • 投标成功率：例如，一个供应商在某项目上的中标率。这些比率不能简单地通过加总来计算，而需要通过分解为可加性组件，如成功和失败的投标次数来推算。

3.3维度退化

维度退化示例

在设计事实表时，一些维度信息可以直接存储在事实表中作为退化维度，以提高查询效率。例如：

  • 项目名称：与项目ID一起存储在事实表中，便于快速查询而不需要关联到项目维度表。
  • 供应商名称：与供应商ID一起存储。

退化维度事实表结构：

投标ID

项目ID

项目名称

供应商ID

供应商名称

投标日期

投标金额

投标状态

通过这种设计，用户在查询某个供应商的所有投标记录时，可以直接通过退化维度字段快速获取相关信息，而无需对维度表进行复杂的关联查询。这种设计能够显著提高查询性能，尤其在处理大量数据时。

应用场景
  • 快速获取某个供应商在所有项目中的投标金额。
  • 统计特定时间段内所有项目的总投标金额和中标率。
  • 分析不同项目的竞争情况，通过投标次数和金额来评估市场热度。

4.明细粒度事实层（DWD）的类型

在招标业务中，我们可以根据不同的业务需求定义明细粒度事实层（DWD）的类型，以满足对数据分析的不同要求。

4.1. 事务事实表

定义：

  • 事务事实表用于描述招标业务过程中的原子事件，记录每次投标活动的详细信息。

示例：

在招标业务中，事务事实表可以记录每个供应商对每个项目的每次投标。表结构可能包括以下字段：

投标ID

项目ID

供应商ID

投标日期

投标金额

投标状态

评标日期

中标日期

应用场景：

  • 分析每个供应商的投标行为，例如某个供应商在过去一段时间内的所有投标记录。
  • 计算每个项目的投标总金额和投标次数。

4.2. 周期快照事实表

定义：

  • 周期快照事实表在固定时间间隔内记录招标业务的状态。例如，每月记录一次所有正在进行的招标项目的状态。

示例：

在招标业务中，可以每月记录所有项目的投标状态和金额，以监控项目进展和市场动态。表结构可能包括：

月份

项目ID

总投标金额

投标次数

中标次数

平均投标金额

应用场景：

  • 评估每月的招标活动情况，帮助管理层了解市场变化趋势。
  • 分析不同月份的投标活跃度和成功率。

4.3.累积快照事实表

定义：

  • 累积快照事实表记录从招标项目开始到结束之间的关键步骤和状态，反映整个项目生命周期的变化。

示例：

在招标业务中，可以用累积快照事实表来跟踪每个项目的全过程，包括从招标公告发布到合同签署的各个关键步骤。表结构可能包括：

项目ID

公告日期

投标开始日期

投标结束日期

评标日期

中标日期

合同签署日期

投标总金额

应用场景：

  • 跟踪每个招标项目的生命周期，识别关键步骤和瓶颈。
  • 统计项目从启动到完成所需的平均时间，以优化招标流程。

明细粒度事实表整体设计流程如下图所示：

在一致性度量中已定义好了交易业务过程及其度量。明细事实表注意针对业务过程进行模型设计。明细事实表的设计可以分为四个步骤：选择业务过程、确定粒度、选择维度、确定事实（度量）。粒度主要是在维度未展开的情况下记录业务活动的语义描述。在建设明细事实表时，需要选择基于现有的表进行明细层数据的开发，清楚所建表记录存储的是什么粒度的数据。

5.明细粒度事实表设计原则

在招标业务中设计明细粒度事实表时，遵循以下原则可以确保数据模型能够有效支持业务需求并优化数据分析的效率。下面详细说明这些设计原则，并结合招标业务进行解释：

5.1一个明细粒度事实表仅和一个维度关联

理解：每个明细粒度事实表应该聚焦于一个核心业务事件，通常只关联一个关键维度。

在招标业务中的应用：例如，一个投标事务事实表可以仅与项目维度关联，因为每个投标记录主要围绕特定项目展开。其他维度（如供应商、时间）作为辅助维度进行描述。

5.2尽可能包含所有与业务过程相关的事实

理解：事实表应尽量全面地反映业务过程中的所有重要数据，以支持多样化的分析需求。

在招标业务中的应用：投标事务事实表应包括所有与投标相关的度量，如投标金额、投标次数、投标状态等，以便进行全面分析。

5.3只选择与业务过程相关的事实

理解：仅选择那些直接与业务过程相关的度量和属性，避免包含无关的数据。

在招标业务中的应用：事实表中仅保留与投标活动直接相关的事实，如投标金额和中标状态，而不是其他不相关的信息，如供应商的财务报表数据。

5.4分解不可加性事实为可加的组件

理解：对于无法直接相加的事实，尝试将其分解为可以汇总的组件，以便更容易进行分析。

在招标业务中的应用：将投标成功率分解为成功投标次数和总投标次数，这样可以通过这两个可加性事实计算出成功率。

5.5在选择维度和事实之前必须先声明粒度

理解：明确事实表的粒度以确保数据的唯一性和完整性，防止不同粒度的事实混杂在一起。

在招标业务中的应用：在创建投标事务事实表之前，声明其粒度为“每个供应商对每个项目的单次投标”，确保表内记录的细节程度一致。

5.6在同一个事实表中不能有多种不同粒度的事实

理解：保持事实表的粒度一致，以避免数据混乱和分析错误。

在招标业务中的应用：确保投标事务事实表中所有记录的粒度一致，例如每条记录代表一个具体的投标事件，而不是有些记录代表单个投标，有些代表项目整体。

5.7事实的单位要保持一致

理解：确保事实表中的所有度量使用一致的单位，以避免在数据分析时出现误解或错误。

在招标业务中的应用：确保所有与金额相关的度量（如投标金额、预算金额）使用相同的货币单位。

5.8谨慎处理Null值

理解：处理Null值时需要小心，以避免在数据计算和分析中出现问题。

在招标业务中的应用：在投标事务事实表中，对于可能出现Null值的字段（如评标日期），可以使用默认值或注释进行处理，以确保分析结果的准确性。

5.9使用退化维度提高事实表的易用性

理解：退化维度是直接存储在事实表中的维度信息，可以加快查询速度和简化数据模型。

在招标业务中的应用：将项目名称和供应商名称作为退化维度存储在投标事务事实表中，使得在查询时无需频繁关联维度表，提升查询效率。

通过遵循这些设计原则，可以在招标业务中创建一个高效且易用的明细粒度事实表，帮助企业更好地分析和优化其招标活动。

6.DWD表命名规则

模型层次

表命名规范

实例表明

实例表说明

dwd

dwd_主题域_可选的主题_事实表相关描述_加工频率+抽取方式

dwd_par_trader_detail_df

dwd为模型层次、par为主题域名、trader为商户主题名、detail为表描述、d代表加工频率、f代表全量抽取方式

例如： dwd_asale_trd_ordcrt_trip_di（A电商公司航旅机票订单下单事实表，日刷新增量）及dwd_asale_itm_item_df（A电商商品快照事实表，日刷新全量）。

  • 交易商品信息事实表：dwd_asale_trd_itm_di。
  • 交易会员信息事实表：dwd_asale_trd_mbr_di。
  • 交易订单信息事实表：dwd_asale_trd_ord_di。

7.招标业务域订单提交事务事实表设计

7.1确定粒度

  • 粒度：每条记录代表一次订单提交事件
    • 每个订单由特定供应商对特定项目的单次提交构成。

7.2关键维度

  • 时间维度：捕捉订单提交、审核、批准等时间点。
  • 供应商维度：描述订单提交的供应商信息。
  • 项目维度：记录订单所属的项目。
  • 产品维度：包含订单中涉及的产品信息。
  • 状态维度：跟踪订单在流程中的状态（如已提交、已审核、已批准等）

7.3事实

  • 订单金额：订单的总金额。
  • 产品数量：订单中设计的产品数量。
  • 折扣金额：任何适用的折扣金额。
  • 税费：订单中涉及的税费。
  • 订单状态：订单的当前状态。

7.4退化维度

  • 订单ID：用于唯一标识每个订单。
  • 供应商名称：便于快速查询而无需关联供应商维度表。
  • 项目名称：便于快速查询而无需关联项目维度表。

7.5事务事实表结构

订单ID

项目ID

项目名称

供应商ID

供应商名称

提交日期

审核日期

批准日期

订单金额

产品数量

折扣金额

税费

订单状态

设计细节说明：

7.5.1粒度一致性：
  • 每条记录只代表一个具体的订单提交事件，确保粒度的一致性。
7.5.2数据全面性
  • 包含所有与订单提交过程相关的度量（如金额、数量、折扣等），以支持全面的业务分析。
7.5.3退化维度
  • 使用退化维度（订单ID、项目名称、供应商名称）提高查询效率，减少复杂关联。
7.5.4处理NULL值
  • 确保日期字段（如审核日期、批准日期）对未完成的步骤能够正确处理Null值，通过设定默认值或注释来防止数据分析误差。
7.5.5事实单位一致性
  • 确保所有货币相关的事实（如订单金额、折扣金额、税费）适用同一的货币单位。

7.6应用场景

  • 订单提交效率分析：
    • 通过分析提交、审核、和批准日期，评估订单处理的效率和瓶颈。
  • 供应商表现评估：
    • 统计不同供应商的订单提交量和成功率，帮助选着优质供应商。
  • 项目成本管理：
    • 分析每个项目的订单金额和数量，监控项目预算和支出情况。

7.7SQL建表语句

在实现招标业务域的订单提交事务事实表时，我们需要编写SQL建表语句，以及定义数据流向、首日装载和每日装载的流程。

7.7.1SQL建表语句

订单提交事务事实表：

代码语言：javascript
复制
CREATE TABLE order_submission_facts (
    order_id INT PRIMARY KEY,
    project_id INT,
    project_name VARCHAR(255),
    supplier_id INT,
    supplier_name VARCHAR(255),
    submission_date DATE,
    review_date DATE,
    approval_date DATE,
    order_amount DECIMAL(18, 2),
    product_quantity INT,
    discount_amount DECIMAL(18, 2),
    tax_amount DECIMAL(18, 2),
    order_status VARCHAR(50),
    FOREIGN KEY (project_id) REFERENCES projects(project_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);
​
7.7.2数据流向

数据流动说明

1.数据源：

  • 原始数据来自业务系统中的订单提交记录、项目管理系统和供应商管理系统。

2.ETL流程：

  • 抽取：从源系统提取订单、项目和供应商相关数据。
  • 转换：
    • 规范化数据格式、项目和供应商相关数据。
    • 计算需要的度量，如折扣和税费。
  • 加载：
    • 将清洗和转换后的数据加载到数据仓库的订单提交事务表中。

数据流向图示例：

代码语言：javascript
复制
[业务系统] --> [ETL工具] --> [数据仓库: order_submission_facts]
7.7.3首日装载

首日装载是指首次将历史数据装载到数据仓库中。

代码语言：javascript
复制
insert overwrite table dwd_order_submission_facts partition (dt)(
    order_id,project_id,project_name,supplier_id,supplier_name,
    submission_date,review_date,approval_date,order_amount,
    product_quantity,discount_amount,tax_amount,order_status
)
SELECT
    o.order_id,
    o.project_id,
    p.project_name,
    o.supplier_id,
    s.supplier_name,
    o.submission_date,
    o.review_date,
    o.approval_date,
    o.order_amount,
    o.product_quantity,
    o.discount_amount,
    o.tax_amount,
    o.order_status
FROM
    source_order o
JOIN
    source_projects p ON o.project_id = p.project_id
JOIN
    source_suppliers s ON o.supplier_id = s.supplier_id
WHERE
    o.submission_date <= CURDATE();
7.7.4每日装载

每日装载是指定期（通常为每日）增量加载新数据。

代码语言：javascript
复制
insert overwrite table dwd_order_submission_facts partition (dt)(
    order_id,project_id,project_name,supplier_id,supplier_name,
    submission_date,review_date,approval_date,order_amount,
    product_quantity,discount_amount,tax_amount,order_status
)
SELECT
    o.order_id,
    o.project_id,
    p.project_name,
    o.supplier_id,
    s.supplier_name,
    o.submission_date,
    o.review_date,
    o.approval_date,
    o.order_amount,
    o.product_quantity,
    o.discount_amount,
    o.tax_amount,
    o.order_status
FROM
    source_order o
JOIN
    source_projects p ON o.project_id = p.project_id
JOIN
    source_suppliers s ON o.supplier_id = s.supplier_id
WHERE
    o.submission_date = CURDATE();

至此我们就完成了DWD表的搭建，以此类推从而搭建所有的DWD层事实表。

#数据分析#大数据存储#云数据仓库 ClickHouse#数据仓库#腾讯技术创作特训营S8