大数据

dwd层建模思考01

Posted by

问题：当前离线数仓dwd层建模 基本上都是按照ods贴源层的数据，去掉逻辑删除的的数据。表字段保持不变，另外也只是添加了字典码值。感觉这模型层的构建太简单了，不成体系。

一、DWD 层的核心价值

DWD 层不是“ODS 去删 + 加码值”，而是：

✅ 面向分析主题的原子粒度建模层
✅ 统一业务口径、清洗脏数据、标准化命名与结构
✅ 为后续 DWS/ADS 层提供高质量、可复用、可关联的明细数据

二、当前做法的问题

问题说明
❌ 未做业务建模仅保留源表结构，未按业务过程、维度建模（如订单、支付、用户行为等）重构
❌ 未统一口径不同源系统相同字段命名不一致、单位不统一、空值处理不一致
❌ 未做数据质量处理仅过滤逻辑删除，未处理异常值、缺失值、重复值、脏数据
❌ 未解耦业务系统仍强依赖源系统表结构，源系统一变，下游全崩
❌ 未建立一致性维度没有统一的维度主键（如 user_id, sku_id），导致后续无法关联分析

三、DWD 层建模的正确打开方式（建议体系化构建）

1. 按业务过程建模（Kimball 维度建模思想）

  • 识别核心业务过程：下单、支付、发货、登录、浏览、加购等
  • 每个业务过程对应一张 DWD 表，字段围绕“事实 + 维度退化”组织
  • 示例：
-- dwd_trade_order_inc (交易订单事实表)
order_id, user_id, sku_id, order_amount, order_time, 
province_id, city_id, -- 退化维度
payment_type_code, order_status_code, -- 退化维度+码值标准化
...

2. 统一口径 & 标准化

  • 字段命名统一：如 user_id, create_time, amount（避免 uid, createtime, amt 混用）
  • 单位统一：金额统一为“分”，时间统一为 yyyy-MM-dd HH:mm:ss
  • 空值统一：NULL 或特定默认值（如 -1、'unknown'）
  • 码值映射：不仅加码值，还要统一业务含义（如性别：0=男,1=女，而不是源系统的 M/F/1/2）

3. 数据清洗 & 质量保障

  • 去重（根据业务主键）
  • 异常值处理（如金额为负、时间戳未来值）
  • 缺失值填充或标记
  • 关联维度补全（如通过 user_id 补充用户等级、注册渠道等）

4. 建立一致性维度（Conformed Dimension）

  • 维度表下沉到 DWD（如 dwd_dim_user, dwd_dim_sku）
  • 事实表通过外键关联（或退化常用维度字段）
  • 保证跨业务过程维度一致性（如用户在订单、支付、评论中 user_id 含义一致）

5. 分层解耦 & 可扩展性

  • DWD 层应屏蔽源系统变更：源表加字段、改名、拆表，不应影响 DWD 输出结构
  • 使用中间映射层或配置化抽取逻辑，提高可维护性

四、推荐建模步骤

  1. 梳理业务过程（和业务方对齐）
  2. 识别事实与维度
  3. 设计 DWD 表结构（命名、字段、主键、分区）
  4. 制定清洗规则 & 映射规则
  5. 开发 + 数据质量监控
  6. 文档化 & 元数据管理

五、进阶建议

  • 引入 数据血缘 & 影响分析 工具，追踪字段来源
  • 建立 DWD 层数据质量 SLA（完整性、准确性、及时性）
  • 使用 数据契约（Data Contract） 约束上下游输入输出
  • 考虑 缓慢变化维（SCD） 处理历史变化（如用户等级变更）

六、参考架构示例

ODS（贴源）  
  ↓ 清洗、过滤、码值映射、去重  
DWD（明细模型层）→ 按业务过程建模，统一口径，退化维度  
  ↓ 聚合、关联、指标计算  
DWS（汇总层）  
  ↓ 应用层指标、报表、API  
ADS（应用层）

总结

你感觉“太简单、不成体系”，是因为当前 DWD 层没有承担起“建模”和“标准化”的责任。建议推动团队：

  • 从“搬运工思维”转向“建模师思维”
  • 引入维度建模方法论（Kimball / Inmon）
  • 制定《DWD 层建模规范》
  • 逐步重构核心业务表，树立标杆

这样，DWD 才能真正成为数仓的“基石层”，支撑上层灵活、稳定、高效的数据应用。

问题：我这边主要是对企业做高企咨询服务。涉及到公司主表以及联系人主表。公司又有采集过来的对应公司的专利、知识产权表，联系人也有对应 的联系方式表、联系方式来源和联系方式标签表。如何按照上面提到的“重构与整合”构建一个成体系的DWD层

一、明确业务过程 & 分析主题

在高企咨询场景中，核心业务过程包括：

  1. 🎯 企业资质评估过程（围绕企业+知识产权）
  2. 📞 客户触达与跟进过程（围绕联系人+联系方式+标签）
  3. 🔄 客户转化与服务过程（可后续扩展）

我们先聚焦前两个，构建 DWD 层。

二、DWD 层建模设计（按业务过程划分）

✅ DWD 层表1：dwd_company_qualification_inc
企业资质评估明细表（增量表，按天分区）
粒度：每个企业每天一条评估快照（或每个企业每个专利关联一条，根据分析需求）

字段设计：

company_id                    -- 企业唯一标识（统一ID，非源系统ID）
company_name
unified_social_credit_code    -- 统一社会信用代码（标准化）
registration_province_code    -- 注册省份编码（标准化）
registration_city_code
industry_category_code        -- 行业分类（映射国标）
establishment_date
registered_capital            -- 注册资本（统一单位：万元）
is_high_tech_enterprise       -- 是否高新企业（0/1，统一口径）
-- 知识产权聚合字段（退化维度，避免频繁关联）
patent_count                  -- 有效专利总数
invention_patent_count        -- 发明专利数
software_copyright_count      -- 软件著作权数
last_patent_apply_date        -- 最近专利申请日期
-- 时间 & 数据来源
data_date                     -- 数据快照日期（分区字段）
source_system                 -- 数据来源系统
etl_time

💡 说明：

  • 此表是“企业+知识产权”的聚合快照，每日更新，支撑“企业资质评分、高新潜力分析”
  • 专利数据从 ODS 专利表聚合而来，避免下游重复 JOIN
  • 所有码值标准化（如省份编码用国标、行业用 GB/T 4754）

✅ DWD 层表2：dwd_contact_behavior_inc

联系人触达行为明细表（增量表，按天分区）
粒度：每个联系人每天的触达行为或属性快照

字段设计：

contact_id                    -- 联系人唯一标识
company_id                    -- 所属企业ID（关联企业表）
contact_name
position                      -- 职位（标准化：如“法人”、“技术负责人”、“财务”）
-- 联系方式退化（取主联系方式或最新有效方式）
main_phone
main_email
main_wechat
-- 联系方式标签 & 来源（退化常用标签）
contact_source_code           -- 来源：1=爬取, 2=客户录入, 3=展会, 4=转介绍
contact_tag_codes             -- 标签数组或逗号分隔：如 "决策人, 技术背景, 意向高"
last_contact_time             -- 最后联系时间
contact_frequency_last_30d    -- 近30天联系次数（可选，聚合指标）
-- 时间 & 数据来源
data_date                     -- 快照日期（分区）
source_system
etl_time

💡 说明：

  • 此表整合了“联系人主表 + 联系方式表 + 标签表 + 来源表”
  • 标签可设计为数组或逗号分隔字符串，便于下游筛选（如 WHERE contact_tag_codes LIKE '%决策人%'）
  • 支撑“客户分层、触达策略、转化漏斗”分析

三、配套维度表（一致性维度）

虽然 DWD 是明细层，但为了保证关联一致性，建议下沉部分维度到 DWD：

✅ dwd_dim_company
企业维度表（缓慢变化维 SCD Type 2 可选）

company_sk                    -- 代理键（推荐）
company_id
company_name
unified_social_credit_code
registration_province_name
industry_category_name
...
start_date, end_date, is_current

✅ dwd_dim_contact
联系人维度表

contact_sk
contact_id
contact_name
position
company_id
...

⚠️ 注意：维度表变化不频繁，可全量更新或 SCD 处理。事实表用 company_id / contact_id 关联即可，不一定强制用代理键。

四、数据处理逻辑（从 ODS → DWD）

企业资质表构建逻辑：

INSERT OVERWRITE dwd_company_qualification_inc PARTITION (data_date='2025-09-22')
SELECT
  c.company_id,
  c.company_name,
  c.unified_social_credit_code,
  -- 标准化映射
  dict_map('province', c.province_raw) AS registration_province_code,
  dict_map('industry', c.industry_raw) AS industry_category_code,
  c.establishment_date,
  c.registered_capital / 10000 AS registered_capital, -- 统一为万元
  CASE WHEN c.cert_flag = 'Y' THEN 1 ELSE 0 END AS is_high_tech_enterprise,
  -- 关联聚合专利
  COALESCE(p.patent_count, 0) AS patent_count,
  COALESCE(p.invention_count, 0) AS invention_patent_count,
  COALESCE(p.copyright_count, 0) AS software_copyright_count,
  p.last_apply_date AS last_patent_apply_date,
  '2025-09-22' AS data_date,
  'SOURCE_SYSTEM_A' AS source_system,
  CURRENT_TIMESTAMP() AS etl_time
FROM ods_company c
LEFT JOIN (
  SELECT 
    company_id,
    COUNT(*) AS patent_count,
    SUM(CASE WHEN patent_type = '发明专利' THEN 1 ELSE 0 END) AS invention_count,
    SUM(CASE WHEN patent_type = '软件著作权' THEN 1 ELSE 0 END) AS copyright_count,
    MAX(apply_date) AS last_apply_date
  FROM ods_patent
  WHERE is_valid = 1  -- 仅统计有效专利
  GROUP BY company_id
) p ON c.company_id = p.company_id
WHERE c.is_deleted = 0;  -- 过滤逻辑删除

联系人行为表构建逻辑：

-- 先构建联系方式+标签聚合
WITH contact_info AS (
  SELECT
    contact_id,
    MAX(CASE WHEN channel_type = 'phone' THEN channel_value END) AS main_phone,
    MAX(CASE WHEN channel_type = 'email' THEN channel_value END) AS main_email,
    MAX(CASE WHEN channel_type = 'wechat' THEN channel_value END) AS main_wechat,
    MAX(source_code) AS contact_source_code,  -- 假设一个联系人一个来源
    CONCAT_WS(',', COLLECT_SET(tag_code)) AS contact_tag_codes  -- 标签聚合
  FROM (
    SELECT 
      c.contact_id,
      ch.channel_type,
      ch.channel_value,
      s.source_code,
      t.tag_code
    FROM ods_contact c
    LEFT JOIN ods_contact_channel ch ON c.contact_id = ch.contact_id AND ch.is_primary = 1
    LEFT JOIN ods_contact_source s ON c.source_id = s.source_id
    LEFT JOIN ods_contact_tag_relation tr ON c.contact_id = tr.contact_id
    LEFT JOIN ods_contact_tag t ON tr.tag_id = t.tag_id
    WHERE c.is_deleted = 0
  ) tmp
  GROUP BY contact_id
)

INSERT OVERWRITE dwd_contact_behavior_inc PARTITION (data_date='2025-09-22')
SELECT
  c.contact_id,
  c.company_id,
  c.contact_name,
  dict_map('position', c.position_raw) AS position,  -- 标准化职位
  ci.main_phone,
  ci.main_email,
  ci.main_wechat,
  ci.contact_source_code,
  ci.contact_tag_codes,
  c.last_contact_time,
  -- 可选：计算近30天联系频次（需关联日志表）
  0 AS contact_frequency_last_30d,
  '2025-09-22' AS data_date,
  'SOURCE_SYSTEM_B' AS source_system,
  CURRENT_TIMESTAMP() AS etl_time
FROM ods_contact c
LEFT JOIN contact_info ci ON c.contact_id = ci.contact_id
WHERE c.is_deleted = 0;

五、后续可扩展方向

  1. DWS 层：
    • dws_company_tag_statistic：企业标签统计（如“有发明专利企业数”、“近1年申请专利企业数”）
    • dws_contact_conversion_funnel：联系人转化漏斗（触达 → 意向 → 签约）
  2. ADS 层：
    • 高企申报成功率预测模型输入表
    • 客户价值分层报表（RFM 模型）
    • 销售区域热力图（按企业注册地聚合）
  3. 数据质量监控：
    • 企业信用代码非空率
    • 专利数突降预警
    • 联系人主联系方式缺失率

六、建模规范建议（团队落地）

项目规范
命名dwd_业务域_表名_更新模式，如dwd_company_qualification_inc
分区按data_date日分区，保留365天
主键业务主键（如 company_id + data_date）
空值字符串用 ''，数值用 -1 或 0，布尔用 0/1
码值统一字典映射表，ETL 中调用
文档每张表必须有字段说明、口径、负责人、更新频率

总结

你现在的 DWD 层“太简单”，是因为它还是 ODS 的影子。
通过以上重构：

✅ 企业资质评估 → dwd_company_qualification_inc
✅ 联系人触达行为 → dwd_contact_behavior_inc
✅ 统一维度 → dwd_dim_company, dwd_dim_contact
✅ 标准化 + 退化维度 + 聚合预计算 → 提升下游效率

这样构建的 DWD 层，才能真正支撑“高企客户筛选、资质评分、销售跟进、资源匹配”等核心业务场景，形成体系化、可分析、易扩展的数据底座。

问题：当前ods层企业主表有90个字段，是保留当前90个字段的基础上 再维度退化、添加专利和软著的统计吗？轻度聚合不是再dws层吗？

✅ 一、核心原则：DWD ≠ ODS 的简单复制，也不是 DWS 的聚合层

层级定位是否聚合字段数量趋势
ODS贴源、保留原始结构、保留脏数据❌ 不聚合最多（90字段）
DWD清洗、标准化、建模、退化常用维度❌不聚合业务指标（但可退化维度）精简 + 重构（如 50~60 字段）
DWS按主题轻度/高度聚合✅ 聚合指标更少（如 20~30 字段）

📌 重点澄清：

  • DWD 可以“退化维度”（如把 province_name 冗余进来），但不应对专利数、软著数做 COUNT 聚合 —— 除非是为了“避免下游重复 JOIN”而做的预关联计算，且需明确标注。
  • 真正的“统计聚合”（如近30天专利申请趋势、年均增长率）必须放在 DWS 层。

✅ 二、你当前 ODS 企业主表 90 个字段，DWD 如何处理？

❌ 错误做法：

  • 原封不动保留 90 个字段 + 新增 5 个聚合字段 → DWD 膨胀到 95 个字段 → 失去建模意义，变成“ODS Pro”。

✅ 正确做法：重构、筛选、标准化、退化

步骤1：字段分类筛选（从90个中筛选出核心字段）

类别处理方式字段示例
✅ 核心业务字段保留 + 标准化命名/码值company_id,company_name,credit_code,establish_date,registered_capital
✅ 高频分析维度保留 + 退化常用维度名province_code→ 同时冗余province_name（避免下游反复关联）
⚠️ 低频/极少用字段移除或归档到 ODS如“企业英文名”、“备用联系人2”、“历史变更备注”等
⚠️ 重复/衍生字段删除或合并如create_time和gmt_create保留一个；status和status_name保留码值+映射
❌ 敏感/无分析价值字段删除如“操作人工号”、“临时备注”、“系统内部ID”

💡 目标：DWD 企业表保留 40~60 个高价值、标准化、常用字段，不是越多越好！

步骤2：标准化 & 映射

  • 字段命名统一：registration_capital → registered_capital
  • 码值统一：province_id: 'BJ' → province_code: '110000'（国标）
  • 单位统一：registered_capital: 500000元 → registered_capital: 50.0（单位：万元）

步骤3：维度退化（非聚合！）

退化的是维度属性，不是聚合指标：

-- ✅ 维度退化（允许）：
province_code, province_name,  -- 从地区维表退化
industry_code, industry_name,  -- 从行业维表退化
company_type_code, company_type_name  -- 从企业类型维表退化

-- ❌ 聚合统计（应谨慎，或放DWS）：
patent_count,                   -- ← 这是聚合！不是退化！
soft_copyright_count            -- ← 同上

📌 重要区分：

  • 退化维度（Degenerate Dimension）：把维度表的描述性字段（如名称、分类）冗余到事实表，不改变粒度，只为减少 JOIN。
  • 聚合（Aggregation）：对多行数据做 COUNT/SUM/AVG，改变粒度，如“一个企业对应多专利 → 计算专利总数”。

✅ 三、那“专利数、软著数”放哪？—— 看业务场景！

场景1：【下游高频使用 + JOIN 成本高】→ 可在 DWD 企业表中预关联计算（标注清楚！）

-- dwd_company_base_inc （企业基础明细表，粒度：企业）
company_id,
company_name,
...,
-- ❗ 预关联计算字段（非标准聚合，仅为性能优化）
pre_calculated_patent_count,      -- 注释：来自 ods_patent 关联统计
pre_calculated_copyright_count,   -- 注释：来自 ods_copyright
last_patent_apply_date,           -- 最近专利申请日（最大值，非聚合统计）
...
  • 适用场景：
    • 下游 80% 报表都需要“企业专利总数”
    • 专利表数据量大，每天 JOIN 成本高
    • 你愿意牺牲 DWD 一点“纯洁性”换下游性能
  • ⚠️ 必须做到：
    • 字段名加 pre_calculated_ 前缀 或 _stat 后缀，明确标识
    • 文档中注明“此字段为预计算，非实时精确值”
    • 保证每日全量/增量更新逻辑正确

场景2：【需要灵活分析、时间趋势、多维度下钻】→ 放到 DWS 层做真正的聚合建模

-- dws_company_patent_1d （企业专利日汇总）
company_id,
data_date,
patent_apply_count,      -- 当日申请数
patent_grant_count,      -- 当日授权数
patent_valid_count,      -- 当前有效总数（累计快照）
...

-- dws_company_patent_nd （企业专利N日汇总）
company_id,
stat_date,
last_7d_apply_count,
last_30d_grant_count,
...

✅ 这才是真正的“轻度聚合层”，支持时间维度、趋势分析、对比分析。

✅ 四、推荐架构：分表处理，职责清晰

方案A（推荐）：拆分为两张 DWD 表

表名用途是否含聚合字段数
dwd_company_base_inc企业基础属性 + 退化维度❌ 无聚合50~60 字段
dwd_company_patent_relation_inc企业-专利关联事实表（粒度：企业×专利）❌ 原子事实10~20 字段

后续在 DWS 层聚合：

  • dws_company_patent_stat：企业专利统计（count, trend, type 分布）

方案B（折中）：DWD 企业表包含“预计算专利数”，但明确标注

-- dwd_company_profile_inc
company_id,
company_name,
...,
-- === 预计算字段（非标准DWD，仅为性能妥协） ===
patent_count_snapshot,        -- 注释：每日快照，来自前一日关联计算
copyright_count_snapshot,
...

适合过渡期或资源受限场景，但长期建议用方案A。

✅ 五、总结 & 建议

你的疑问正确答案
“保留90字段 + 加统计？”❌ 不要！DWD 要做减法、标准化、建模，不是堆字段
“轻度聚合在DWS？”✅ 是的！但 DWD 可为性能做“预关联计算”，需明确标识
“专利数放哪？”👉 高频简单统计 → DWD 预计算（谨慎）
👉 灵活分析、趋势 → DWS 聚合层

🚀 落地建议：

  1. 先梳理90个字段：和业务方一起标注“核心字段”、“低频字段”、“无用字段”
  2. 制定《DWD 字段准入规范》：不是所有 ODS 字段都能进 DWD
  3. 拆分 DWD 表：dwd_company_base + dwd_company_patent_relation
  4. DWS 层做聚合：按天、按周、按月统计专利、软著、增长率等
  5. 文档化“预计算字段”：如果放了，必须写清楚计算逻辑和更新频率

这样构建的 DWD 层，才能真正“成体系、可复用、易扩展”，而不是 ODS 的翻版或 DWS 的半成品。

下一篇
dwd层建模思考02
上一篇 数据治理方案