dwd层建模思考03

Posted by 蓝染君

2025年9月23日

On 2025年9月23日

问题：我有个思路为什么不能添加dwt层主题层，在主题层中，将dws层轻度聚合的数据进行拼接。比如dwt层company表字段有：专利数、软著数、联系人数、联系方式数、不同类型联系方式数等。这些字段都来自dws层。在ads层只需要结合dwd层和dwt层针对不同的需求，组合dwt层不同的字段指标即可。类似拼装积木的效果。如果这样做就变成了我现在的，ods层变dwd层只对删除数据过滤、对字典码值关联名称，数据清洗、标准化、退化维度，其他不做。dws轻度聚合，dwt汇聚dws聚合结果，ads层根据业务需求关联dwd层明细以及dwt指标。

✅ 一、你的思路总结（DWT 层设计）

你提出的架构：

ODS →（仅过滤删除+码值）→ DWD  
DWD →（轻度聚合）→ DWS  
DWS →（横向拼接指标）→ DWT（主题宽表）  
ADS →（按需组合 DWD + DWT）→ 报表/API

ODS →（仅过滤删除+码值）→ DWD  
DWD →（轻度聚合）→ DWS  
DWS →（横向拼接指标）→ DWT（主题宽表）  
ADS →（按需组合 DWD + DWT）→ 报表/API

举例：
dwt_company_theme 表包含：

patent_count ← 来自 dws_company_patent_stat
copyright_count ← 来自 dws_company_copyright_stat
valid_contact_count ← 来自 dws_company_contact_effectiveness
wechat_channel_count ← 来自 dws_company_channel_type_dist
...

ADS 层只需：

SELECT 
  dwd.company_name,
  dwt.patent_count,
  dwt.valid_contact_count,
  ...
FROM dwd_company_base dwd
JOIN dwt_company_theme dwt ON dwd.company_id = dwt.company_id

SELECT 
  dwd.company_name,
  dwt.patent_count,
  dwt.valid_contact_count,
  ...
FROM dwd_company_base dwd
JOIN dwt_company_theme dwt ON dwd.company_id = dwt.company_id

✅ 二、这个思路的优点（为什么有吸引力）

优点	说明
🧩灵活组装	ADS 层像搭积木，自由组合不同主题指标，响应业务需求快
🔁复用性强	DWT 作为“指标中间层”，避免 ADS 层重复 JOIN 多个 DWS 表
📊宽表友好	适合 BI 工具（如 Tableau、QuickBI）直接拖拽字段，降低使用门槛
🧱职责分离	DWS 负责“垂直聚合”，DWT 负责“横向拼接”，逻辑清晰

💡 本质上，你设计的 DWT 层 = 指标宽表（Metric Wide Table）或逻辑数据集市（Logical Data Mart）

⚠️ 三、潜在问题 & 风险（为什么经典架构不推荐）

1. ❌ DWT 层容易变成“上帝表”（God Table）

一个表包含几十甚至上百个指标 → 字段爆炸
维护困难：新增一个指标，要改 DWT 表结构 + 重跑历史数据
查询性能下降：即使只查2个字段，也要扫描整张宽表

📌 举例：
未来新增“近30天专利增长率”、“联系人响应率”等指标，DWT 表不断膨胀，最终变成“谁都不敢动的巨无霸”。

2. ❌ DWT 依赖多个 DWS，数据一致性难保障

DWS_A 今日更新，DWS_B 昨日更新 → DWT 拼接后数据口径不一致
调度依赖复杂：DWT 必须等所有依赖的 DWS 都 ready 才能跑
出问题难排查：某个指标异常，要逐层回溯到源头 DWS

3. ❌ 过度依赖 DWT，丧失灵活性

业务想分析“专利类型 × 联系人职位”的交叉分布 → DWT 没这个组合，只能回 DWS 或 DWD 重算
DWT 是“预设组合”，无法应对“临时、探索性分析”

4. ❌ DWD 层过于简单，失去“建模”价值

你提到：

DWD 层只对删除数据过滤、对字典码值关联名称，数据清洗、标准化、退化维度，其他不做”

→ 这会导致：

DWD 表字段过多（90个）、未筛选、未重构 → 下游 JOIN 性能差
未做业务建模（如识别“企业资质评估”、“客户触达”等过程）→ 无法支撑灵活分析
本质上，你把“建模”的责任推给了 DWS/DWT，导致上游混乱

📌 DWD 层的核心价值是“面向分析主题的原子建模”，不是“ODS 美化版”。

✅ 四、如何优化你的思路？—— 融合经典架构 + 你的创新

我们不否定你的“DWT 积木思路”，而是把它放在正确的位置，用正确的方式实现。

🎯 方案：保留 DWT 层，但重新定义其角色 + 优化 DWD/DWS

✅ Step 1: 强化 DWD 层（必须做！）
筛选字段：90 → 40~50 核心字段
业务建模：按“企业资质”、“客户触达”等过程拆分 DWD 表
退化维度：企业名称、行业、地区等高频维度
清洗标准化：统一单位、码值、空值处理

💡 DWD 是地基，地基不牢，DWT 再漂亮也会塌。

✅ Step 2: DWS 层做轻度聚合（保持不变）

按主题、按维度、按时间聚合
如：dws_company_patent_by_type, dws_contact_channel_dist

✅ Step 3: DWT 层改为“可选宽表”或“物化视图”（关键优化！）

不作为必经分层，而是按需构建的“加速层”
只为高频、固定报表服务（如 CEO 看板、销售日报）
使用 Doris 物化视图（MV）或 Rollup Table 实现，自动同步，无需手动维护

📌 示例：

-- 创建物化视图（自动同步，查询自动路由）
CREATE MATERIALIZED VIEW mv_company_dashboard AS
SELECT
  dwd.company_id,
  dwd.company_name,
  dws1.patent_count,
  dws2.copyright_count,
  dws3.valid_contact_count,
  dws4.wechat_count
FROM dwd_company_base dwd
LEFT JOIN dws_company_patent_stat dws1 ON dwd.company_id = dws1.company_id
LEFT JOIN dws_company_copyright_stat dws2 ON dwd.company_id = dws2.company_id
LEFT JOIN dws_company_contact_effectiveness dws3 ON dwd.company_id = dws3.company_id
LEFT JOIN dws_company_channel_type_dist dws4 ON dwd.company_id = dws4.company_id
WHERE dwd.data_date = '2025-09-22'
  AND dws1.data_date = '2025-09-22'
  ...;

-- 创建物化视图（自动同步，查询自动路由）
CREATE MATERIALIZED VIEW mv_company_dashboard AS
SELECT
  dwd.company_id,
  dwd.company_name,
  dws1.patent_count,
  dws2.copyright_count,
  dws3.valid_contact_count,
  dws4.wechat_count
FROM dwd_company_base dwd
LEFT JOIN dws_company_patent_stat dws1 ON dwd.company_id = dws1.company_id
LEFT JOIN dws_company_copyright_stat dws2 ON dwd.company_id = dws2.company_id
LEFT JOIN dws_company_contact_effectiveness dws3 ON dwd.company_id = dws3.company_id
LEFT JOIN dws_company_channel_type_dist dws4 ON dwd.company_id = dws4.company_id
WHERE dwd.data_date = '2025-09-22'
  AND dws1.data_date = '2025-09-22'
  ...;

✅ 优势：

ADS 层查询 mv_company_dashboard 像查普通表，性能极佳
Doris 自动维护 MV，数据一致性有保障
不影响原始 DWD/DWS 结构，灵活性仍在

✅ Step 4: ADS 层自由选择数据源

简单固定报表 → 查 DWT（物化视图）
复杂临时分析 → 查 DWD + DWS 自由组合
探索性分析 → 直接查 DWD 原子表

✅ 五、最终推荐架构（融合版）

ODS（贴源）
  ↓ 清洗、标准化、建模、退化维度（必须做！）
DWD_company_base_inc          → 企业基础（原子粒度）
DWD_contact_channel_inc       → 联系方式（原子粒度，Array标签）
  ↓ 按主题聚合
DWS_company_patent_stat       → 专利统计
DWS_company_contact_stat      → 联系统计
DWS_company_channel_dist      → 渠道分布
  ↓ （可选）物化视图 / 宽表（按需构建）
DWT_company_dashboard_mv      → 高频指标宽表（物化视图）
  ↓ 应用层（自由选择）
ADS → 
  - 固定报表：SELECT * FROM DWT_company_dashboard_mv
  - 临时分析：JOIN DWD + DWS
  - 探索分析：直接查 DWD

ODS（贴源）
  ↓ 清洗、标准化、建模、退化维度（必须做！）
DWD_company_base_inc          → 企业基础（原子粒度）
DWD_contact_channel_inc       → 联系方式（原子粒度，Array标签）
  ↓ 按主题聚合
DWS_company_patent_stat       → 专利统计
DWS_company_contact_stat      → 联系统计
DWS_company_channel_dist      → 渠道分布
  ↓ （可选）物化视图 / 宽表（按需构建）
DWT_company_dashboard_mv      → 高频指标宽表（物化视图）
  ↓ 应用层（自由选择）
ADS → 
  - 固定报表：SELECT * FROM DWT_company_dashboard_mv
  - 临时分析：JOIN DWD + DWS
  - 探索分析：直接查 DWD

✅ 六、为什么这个方案更好？

你的原方案	优化后方案
DWD 过于简单 → 下游性能差、难维护	✅ DWD 强化建模 → 地基牢固
DWT 是必经层 → 易膨胀、难维护	✅ DWT 是可选物化视图 → 按需构建，自动维护
依赖链长 → 调度复杂	✅ DWS 独立更新，MV 自动同步 → 解耦
丧失灵活性	✅ ADS 可自由选择数据源 → 兼顾灵活与性能

🧭 总结：你的“积木思路”很有价值，只需稍作调整
✅ 可以有 DWT 层，但应该是“物化宽表”或“加速视图”，不是核心分层
✅ DWD 层必须做强，不能只是“ODS 美化版”
✅ 用 Doris 物化视图实现 DWT，自动同步、查询透明、性能极佳
✅ ADS 层自由选择：简单查 DWT，复杂查 DWD+DWS

这样，你既能享受“积木式组装”的灵活性，又能保证系统长期可维护、高性能、易扩展。

问题：但是这样存在一个问题，由于dwt都是组装好的，这样使用的频率肯定很高，这样慢慢的也会大量的ads去查dwt，这样又回到了我之前dwt必经的线路了

✅ 一、为什么“DWT 被滥用”是必然？


🚀性能好、查询简单	`SELECT * FROM dwt_company`比`JOIN 5张表`快10倍，谁不爱？
👶使用门槛低	业务/BI/新人直接拖拽字段，无需理解底层模型
📈初期ROI极高	1张宽表解决80%报表，老板觉得“数仓真牛”
🔄路径依赖	一旦用上，没人愿意改回复杂查询，即使需求变了

→ 结果：DWT 从“加速视图”沦为“唯一入口”，架构僵化，扩展性归零。

✅ 二、如何破解？—— 架构治理 + 技术约束 + 组织协同

我们不阻止“用 DWT”，而是引导它用在正确的地方，同时保留灵活出口。以下是经过验证的解决方案：

🛠️ 方案一：【架构治理】明确 DWT 的“服务边界”
📌 DWT 只服务“高频、稳定、核心”指标，其他需求必须走 DWD/DWS

如何落地？

制定《DWT 宽表准入规范》
- 只允许 TOP 10 高频指标入 DWT（如专利总数、有效联系人）
- 新增指标必须评审：是否高频？是否稳定？是否跨部门通用？
- 禁止“临时需求”、“探索性分析”直接入 DWT
建立“宽表生命周期管理”
- 每季度 Review：哪些字段没人用？哪些需求已过时？
- 对低频字段归档或移除，防止膨胀
文档化“替代方案”
- 在 DWT 表注释中写明：“如需按专利类型分析，请查询 dws_company_patent_by_type”
- 提供 SQL 模板，引导用户走正确路径

🛠️ 方案二：【技术约束】用“视图”代替“物理表”，强制解耦

📌 不建物理 DWT 表，而是创建“逻辑视图（View）”，底层仍指向 DWS

示例：

-- 创建逻辑视图（非物化）
CREATE VIEW dwt_company_theme AS
SELECT
  dwd.company_id,
  dwd.company_name,
  dws1.patent_count,
  dws2.copyright_count,
  dws3.valid_contact_count
FROM dwd_company_base dwd
LEFT JOIN dws_company_patent_stat dws1 ON dwd.company_id = dws1.company_id AND dws1.data_date = '2025-09-22'
LEFT JOIN dws_company_copyright_stat dws2 ON dwd.company_id = dws2.company_id AND dws2.data_date = '2025-09-22'
LEFT JOIN dws_company_contact_effectiveness dws3 ON dwd.company_id = dws3.company_id AND dws3.data_date = '2025-09-22'
WHERE dwd.data_date = '2025-09-22';

-- 创建逻辑视图（非物化）
CREATE VIEW dwt_company_theme AS
SELECT
  dwd.company_id,
  dwd.company_name,
  dws1.patent_count,
  dws2.copyright_count,
  dws3.valid_contact_count
FROM dwd_company_base dwd
LEFT JOIN dws_company_patent_stat dws1 ON dwd.company_id = dws1.company_id AND dws1.data_date = '2025-09-22'
LEFT JOIN dws_company_copyright_stat dws2 ON dwd.company_id = dws2.company_id AND dws2.data_date = '2025-09-22'
LEFT JOIN dws_company_contact_effectiveness dws3 ON dwd.company_id = dws3.company_id AND dws3.data_date = '2025-09-22'
WHERE dwd.data_date = '2025-09-22';

优势：

优势	说明
✅无物理存储	不占空间，无维护成本
✅强制走 DWS	查询视图 = 查询底层 DWS，口径永远一致
✅可随时重构	改底层 DWS 不影响视图接口
⚠️ 性能略低	每次查询都实时 JOIN，适合中小数据量或 Doris MPP 优化

💡 如果性能不够，再对高频视图按需创建物化视图（见方案三）

🛠️ 方案三：【技术升级】用“动态物化视图”或“查询路由”

📌 利用 Doris 的“智能物化视图”或“查询改写”能力，自动选择最优路径

Doris 2.0+ 方案：

创建多个细粒度物化视图（非大宽表）

-- 物化视图1：专利相关
CREATE MATERIALIZED VIEW mv_company_patent AS
SELECT company_id, patent_count, invention_count
FROM dws_company_patent_stat;

-- 物化视图2：联系人相关
CREATE MATERIALIZED VIEW mv_company_contact AS
SELECT company_id, valid_contact_count, wechat_count
FROM dws_company_contact_effectiveness;

-- 物化视图1：专利相关
CREATE MATERIALIZED VIEW mv_company_patent AS
SELECT company_id, patent_count, invention_count
FROM dws_company_patent_stat;

-- 物化视图2：联系人相关
CREATE MATERIALIZED VIEW mv_company_contact AS
SELECT company_id, valid_contact_count, wechat_count
FROM dws_company_contact_effectiveness;

创建逻辑宽表视图

CREATE VIEW dwt_company_theme AS
SELECT 
  dwd.company_id,
  dwd.company_name,
  mv1.patent_count,
  mv2.valid_contact_count
FROM dwd_company_base dwd
LEFT JOIN mv_company_patent mv1 ON dwd.company_id = mv1.company_id
LEFT JOIN mv_company_contact mv2 ON dwd.company_id = mv2.company_id;

CREATE VIEW dwt_company_theme AS
SELECT 
  dwd.company_id,
  dwd.company_name,
  mv1.patent_count,
  mv2.valid_contact_count
FROM dwd_company_base dwd
LEFT JOIN mv_company_patent mv1 ON dwd.company_id = mv1.company_id
LEFT JOIN mv_company_contact mv2 ON dwd.company_id = mv2.company_id;

Doris 自动查询路由
- 当用户查 dwt_company_theme 时，Doris 自动识别哪些字段有物化视图，优先走 MV
- 无 MV 的字段，走原始 DWS
- 用户无感知，性能最优，架构不僵化

✅ 这才是“积木式”的终极形态：

用户看到的是“宽表”
底层是“独立物化积木”
系统自动组装最优查询路径

🛠️ 方案四：【组织协同】建立“数据产品”思维

📌 把 DWT 当成“数据产品”，不是“技术表”

如何做？

为 DWT 定义 SLA 和 Owner
- 谁负责维护？谁审批新增字段？谁监控性能？
- 像管理一个“API 产品”一样管理 DWT
提供“数据目录 + 使用指南”
- 哪些字段适合什么场景？
- 哪些需求应该走其他路径？
- 示例 SQL、性能预期、更新频率
培训 + 考核
- 培训业务方“何时用 DWT，何时用 DWS”
- 考核数仓团队“宽表膨胀率”、“物化视图覆盖率”

✅ 五、终极架构推荐（防滥用版）

ODS
  ↓ 强化建模、清洗、退化
DWD（原子事实 + 退化维度）
  ↓ 按主题聚合
DWS（轻度聚合，独立更新）
  ↓ （可选）细粒度物化视图（非宽表）
MV_patent, MV_contact, MV_channel...
  ↓ 逻辑宽表视图（自动路由）
DWT_company_theme (VIEW)  ← 用户主要入口
  ↓ 应用层
ADS → 
  - 90% 场景：查 DWT_company_theme（自动走 MV，性能好）
  - 10% 复杂场景：直接查 DWS 或 DWD（灵活）

ODS
  ↓ 强化建模、清洗、退化
DWD（原子事实 + 退化维度）
  ↓ 按主题聚合
DWS（轻度聚合，独立更新）
  ↓ （可选）细粒度物化视图（非宽表）
MV_patent, MV_contact, MV_channel...
  ↓ 逻辑宽表视图（自动路由）
DWT_company_theme (VIEW)  ← 用户主要入口
  ↓ 应用层
ADS → 
  - 90% 场景：查 DWT_company_theme（自动走 MV，性能好）
  - 10% 复杂场景：直接查 DWS 或 DWD（灵活）

📌 关键控制点：

DWT 是 VIEW，不是 TABLE
底层是 多个独立 MV，不是单一大宽表
Doris 自动查询改写，用户无感知
团队有 治理规范，防止无序扩张

🧭 总结：你不是在和架构斗争，而是在和“人性”与“惯性”斗争

你的担忧完全正确 —— “好用的东西会被滥用”是铁律。

✅ 破解之道不是“禁止使用”，而是“引导 + 约束 + 自动化”：

用“逻辑视图 + 细粒度物化”代替“物理宽表” → 防止膨胀
用 Doris 查询改写实现“无感知最优路径” → 兼顾性能与灵活
建立治理规范和数据产品思维 → 控制无序增长

这样，你既能享受“积木式组装”的敏捷，又能避免“上帝表”的噩梦。

问题：我的理解是dwt可以看作企业画像、联系人画像、这里只记录画像该有的东西，没有的还是要从dwd或者dws出

✅ “DWT 可以看作企业画像、联系人画像，这里只记录画像该有的东西，没有的还是要从 DWD 或 DWS 出

✅ 一、为什么这个理解是“黄金标准”？

1. 🖼️ DWT = 画像层 = 数据产品层

不是“大杂烩宽表”，而是围绕特定主体（企业、联系人）的“特征快照”
只包含稳定、高频、通用、预计算的画像标签/指标
目标是：“一眼看清这个企业/联系人长什么样”

举例：

企业画像：是否高新、专利总数、行业竞争力等级、联系人活跃度
联系人画像：是否关键人、主联系方式有效性、最近联系时间、意向等级

2. 🚫 “没有的，从 DWD/DWS 出” —— 保持灵活性

画像层不追求“大而全”，而是“准而快”
深度分析、临时需求、交叉下钻 → 回到原子层（DWD）或聚合层（DWS）
避免把 DWT 变成“万能表”，而是“入口表”

📌 这和互联网大厂的“用户画像系统”设计完全一致：

Profile 层：性别、年龄、RFM 分层、LTV 预估（稳定特征）
临时分析：行为序列、交叉漏斗、AB 实验 → 走明细日志或聚合表

✅ 二、DWT 画像层设计原则（防滥用指南）

原则	说明	示例
✅只放“画像特征”，不放“过程明细”	画像是“结果”，不是“过程”	✔️`专利总数` ❌`每条专利的申请号`
✅只放“稳定指标”，不放“临时指标”	避免频繁变更表结构	✔️`企业规模等级` ❌`昨日新增专利数`（放DWS）
✅只放“通用标签”，不放“业务专属”	避免烟囱化	✔️`是否有效联系人` ❌`销售A的私有打标`
✅明确“更新频率”和“口径”	避免误用	注释：`每日凌晨更新，基于前一日快照`
✅提供“下钻路径”文档	引导深度分析	注释：`如需按专利类型分析，请查 dws_company_patent_by_type`

✅ 三、推荐表结构示例（DWT 画像层）

📌 `dwt_company_profile` —— 企业画像宽表

company_id BIGINT COMMENT '企业ID',
company_name VARCHAR(200) COMMENT '企业名称',
industry_category VARCHAR(50) COMMENT '行业分类',
province_name VARCHAR(50) COMMENT '注册省份',
company_scale_level TINYINT COMMENT '企业规模等级（1~5）',
is_high_tech BOOLEAN COMMENT '是否高新技术企业',
total_patent_count INT COMMENT '有效专利总数',
total_copyright_count INT COMMENT '软件著作权总数',
contact_valid_rate FLOAT COMMENT '有效联系方式占比',
last_contact_days_ago INT COMMENT '距最近联系天数',
customer_value_score FLOAT COMMENT '客户价值评分（RFM模型）',
profile_update_time DATETIME COMMENT '画像更新时间',
-- 分区
data_date DATE COMMENT '画像快照日期'

company_id BIGINT COMMENT '企业ID',
company_name VARCHAR(200) COMMENT '企业名称',
industry_category VARCHAR(50) COMMENT '行业分类',
province_name VARCHAR(50) COMMENT '注册省份',
company_scale_level TINYINT COMMENT '企业规模等级（1~5）',
is_high_tech BOOLEAN COMMENT '是否高新技术企业',
total_patent_count INT COMMENT '有效专利总数',
total_copyright_count INT COMMENT '软件著作权总数',
contact_valid_rate FLOAT COMMENT '有效联系方式占比',
last_contact_days_ago INT COMMENT '距最近联系天数',
customer_value_score FLOAT COMMENT '客户价值评分（RFM模型）',
profile_update_time DATETIME COMMENT '画像更新时间',
-- 分区
data_date DATE COMMENT '画像快照日期'

💡 说明：

所有字段都是预计算、稳定、高频使用的画像特征
不包含“专利类型分布”、“联系人明细”等过程数据
每个字段都有注释，说明来源和更新逻辑

📌 `dwt_contact_profile` —— 联系人画像宽表

contact_id BIGINT,
company_id BIGINT,
contact_name VARCHAR(100),
position_name VARCHAR(50),
is_key_person BOOLEAN COMMENT '是否关键决策人',
main_channel_type VARCHAR(20) COMMENT '主联系方式类型',
main_channel_valid BOOLEAN COMMENT '主联系方式是否有效',
last_contact_time DATETIME COMMENT '最近联系时间',
contact_frequency_30d INT COMMENT '近30天联系次数',
intent_level TINYINT COMMENT '意向等级（1~5）',
tag_list ARRAY<VARCHAR(50)> COMMENT '标签列表：["技术背景","高意向"]',
profile_update_time DATETIME,
data_date DATE

contact_id BIGINT,
company_id BIGINT,
contact_name VARCHAR(100),
position_name VARCHAR(50),
is_key_person BOOLEAN COMMENT '是否关键决策人',
main_channel_type VARCHAR(20) COMMENT '主联系方式类型',
main_channel_valid BOOLEAN COMMENT '主联系方式是否有效',
last_contact_time DATETIME COMMENT '最近联系时间',
contact_frequency_30d INT COMMENT '近30天联系次数',
intent_level TINYINT COMMENT '意向等级（1~5）',
tag_list ARRAY<VARCHAR(50)> COMMENT '标签列表：["技术背景","高意向"]',
profile_update_time DATETIME,
data_date DATE

💡 说明：

tag_list 用 Array 存储，便于 Doris 的 ARRAY_CONTAINS 查询
不存储“每个标签的打标时间”（那是 DWD 的事）
intent_level 是模型计算结果，非原始打标

✅ 四、如何与 DWD/DWS 协同？

🔄 数据流向：

ODS →（清洗建模）→ DWD（原子事实）  
DWD →（聚合）→ DWS（轻度汇总）  
DWS + DWD →（特征工程）→ DWT（画像宽表）  
ADS →  
  ├─ 简单画像查询 → DWT  
  └─ 复杂分析 → JOIN DWD + DWS

ODS →（清洗建模）→ DWD（原子事实）  
DWD →（聚合）→ DWS（轻度汇总）  
DWS + DWD →（特征工程）→ DWT（画像宽表）  
ADS →  
  ├─ 简单画像查询 → DWT  
  └─ 复杂分析 → JOIN DWD + DWS

🎯 查询示例：
场景1：销售看“高意向企业清单” → 查 DWT

SELECT 
  company_name,
  total_patent_count,
  contact_valid_rate,
  customer_value_score
FROM dwt_company_profile
WHERE customer_value_score > 80
  AND data_date = '2025-09-22';

SELECT 
  company_name,
  total_patent_count,
  contact_valid_rate,
  customer_value_score
FROM dwt_company_profile
WHERE customer_value_score > 80
  AND data_date = '2025-09-22';

场景2：分析“发明专利 vs 联系人职位的关系” → 查 DWS + DWD

SELECT 
  dwd.position_name,
  dws.patent_type,
  COUNT(*) as cnt
FROM dwd_contact_base dwd
JOIN dws_company_patent_by_type dws ON dwd.company_id = dws.company_id
WHERE dws.data_date = '2025-09-22'
GROUP BY dwd.position_name, dws.patent_type;

SELECT 
  dwd.position_name,
  dws.patent_type,
  COUNT(*) as cnt
FROM dwd_contact_base dwd
JOIN dws_company_patent_by_type dws ON dwd.company_id = dws.company_id
WHERE dws.data_date = '2025-09-22'
GROUP BY dwd.position_name, dws.patent_type;

✅ 完美分工：

DWT：“是什么”（What）—— 快速获取主体特征
DWD/DWS：“为什么”（Why）—— 深度归因分析

✅ 五、技术实现建议（Doris）

使用物化视图自动构建画像

CREATE MATERIALIZED VIEW dwt_company_profile
DISTRIBUTED BY HASH(company_id)
AS
SELECT
  dwd.company_id,
  dwd.company_name,
  dwd.industry_category,
  dwd.province_name,
  -- 从 DWS 获取聚合指标
  dws1.total_patent_count,
  dws2.contact_valid_rate,
  -- 计算画像标签
  CASE 
    WHEN dws1.total_patent_count > 10 THEN 5
    WHEN dws1.total_patent_count > 5 THEN 4
    ELSE 3
  END AS company_scale_level,
  ...
FROM dwd_company_base dwd
LEFT JOIN dws_company_ipr_stat dws1 ON dwd.company_id = dws1.company_id
LEFT JOIN dws_company_contact_effectiveness dws2 ON dwd.company_id = dws2.company_id
WHERE dwd.data_date = '2025-09-22'
  AND dws1.data_date = '2025-09-22';

CREATE MATERIALIZED VIEW dwt_company_profile
DISTRIBUTED BY HASH(company_id)
AS
SELECT
  dwd.company_id,
  dwd.company_name,
  dwd.industry_category,
  dwd.province_name,
  -- 从 DWS 获取聚合指标
  dws1.total_patent_count,
  dws2.contact_valid_rate,
  -- 计算画像标签
  CASE 
    WHEN dws1.total_patent_count > 10 THEN 5
    WHEN dws1.total_patent_count > 5 THEN 4
    ELSE 3
  END AS company_scale_level,
  ...
FROM dwd_company_base dwd
LEFT JOIN dws_company_ipr_stat dws1 ON dwd.company_id = dws1.company_id
LEFT JOIN dws_company_contact_effectiveness dws2 ON dwd.company_id = dws2.company_id
WHERE dwd.data_date = '2025-09-22'
  AND dws1.data_date = '2025-09-22';

对 tag_list 字段建倒排索引

CREATE INDEX idx_tag_list ON dwt_contact_profile (tag_list) USING INVERTED;

CREATE INDEX idx_tag_list ON dwt_contact_profile (tag_list) USING INVERTED;

→ 加速 SELECT * WHERE ARRAY_CONTAINS(tag_list, '高意向')

分区 + 生命周期

PARTITION BY RANGE(data_date) (
  PARTITION p202509 VALUES LESS THAN ('2025-10-01'),
  ...
)
-- 保留最近365天

PARTITION BY RANGE(data_date) (
  PARTITION p202509 VALUES LESS THAN ('2025-10-01'),
  ...
)
-- 保留最近365天

🧭 总结：你已掌握数仓分层设计的“道”与“术”

DWT = 画像层 = 数据产品层
✅ 只放稳定、高频、通用的画像特征
✅ 深度分析回 DWD/DWS，保持灵活性
✅ 用物化视图 + 倒排索引实现高性能
✅ 用文档和规范防止滥用

问题：还有一个问题 dwd层在存储联系方式来源使用数组，那么存储的是来源的id还是退化后来源的名字

✅ 在 DWD 层存储联系方式来源（或标签）时，使用 Array 类型，应该存储来源ID（code），而不是退化后的名字（name）！

✅ 一、为什么存 ID（code）而不是 name？

原因	说明
🔄维度属性可能变更	来源名称可能修改（如 “爬虫” → “网络采集”），如果存 name，历史数据语义会错乱
📏存储更小、性能更好	ID 通常是 INT 或 SHORT VARCHAR，比名称字符串更省空间，JOIN/过滤更快
🧩便于后续灵活映射	下游可根据不同场景映射不同名称（如中文/英文/缩写），或做国际化
🧭符合维度建模规范	Kimball 维度建模中，事实表存外键（ID），维度表存描述（name）
🛠️ETL 更健壮	如果来源名称拼错或未映射，存 ID 仍可追溯，存 name 则直接脏数据

📌 举例：

来源表：source_id=1, source_name='爬虫'
今天存了 ['爬虫'] → 明天来源表改成 source_name='网络采集'
历史数据仍显示“爬虫”，但新数据是“网络采集” → 口径不一致，报表对不齐！

而如果存 ID：

存 ['1']
无论名称怎么改，1 始终代表同一个来源
下游展示时再 JOIN 维度表 → 语义永远一致

✅ 二、Doris 中的最佳实践（Array + ID + 运行时映射）

DWD 表结构（存 ID）

-- dwd_contact_channel_inc
CREATE TABLE dwd_contact_channel_inc (
  channel_id BIGINT,
  contact_id BIGINT,
  company_id BIGINT,
  channel_type VARCHAR(20),
  channel_value VARCHAR(100),
  source_codes ARRAY<VARCHAR(10)>,   -- 存来源ID，如 ['1', '3']
  tag_codes ARRAY<VARCHAR(20)>,      -- 存标签ID，如 ['valid', 'not_company']
  is_valid BOOLEAN AS ARRAY_CONTAINS(tag_codes, 'valid'), -- 用ID判断
  ...
) ENGINE=OLAP
PARTITION BY RANGE(data_date) (...)
DISTRIBUTED BY HASH(channel_id);

-- dwd_contact_channel_inc
CREATE TABLE dwd_contact_channel_inc (
  channel_id BIGINT,
  contact_id BIGINT,
  company_id BIGINT,
  channel_type VARCHAR(20),
  channel_value VARCHAR(100),
  source_codes ARRAY<VARCHAR(10)>,   -- 存来源ID，如 ['1', '3']
  tag_codes ARRAY<VARCHAR(20)>,      -- 存标签ID，如 ['valid', 'not_company']
  is_valid BOOLEAN AS ARRAY_CONTAINS(tag_codes, 'valid'), -- 用ID判断
  ...
) ENGINE=OLAP
PARTITION BY RANGE(data_date) (...)
DISTRIBUTED BY HASH(channel_id);

维度表（存 name）

-- dim_source（来源维度表）
CREATE TABLE dim_source (
  source_code VARCHAR(10) COMMENT '来源ID',
  source_name VARCHAR(50) COMMENT '来源名称',
  source_category VARCHAR(20) COMMENT '来源分类',
  is_active BOOLEAN,
  etl_time DATETIME
) ENGINE=OLAP
DUPLICATE KEY(source_code);

-- dim_source（来源维度表）
CREATE TABLE dim_source (
  source_code VARCHAR(10) COMMENT '来源ID',
  source_name VARCHAR(50) COMMENT '来源名称',
  source_category VARCHAR(20) COMMENT '来源分类',
  is_active BOOLEAN,
  etl_time DATETIME
) ENGINE=OLAP
DUPLICATE KEY(source_code);

查询时动态映射名称（推荐两种方式）
方式1：LATERAL VIEW + JOIN（灵活，适合分析）

-- 查询时展开并映射名称
SELECT 
  c.channel_id,
  c.channel_value,
  s.source_name,
  t.tag_name
FROM dwd_contact_channel_inc c
LATERAL VIEW EXPLODE(c.source_codes) tmp1 AS source_code
LATERAL VIEW EXPLODE(c.tag_codes) tmp2 AS tag_code
JOIN dim_source s ON tmp1.source_code = s.source_code
JOIN dim_tag t ON tmp2.tag_code = t.tag_code
WHERE c.data_date = '2025-09-22';

-- 查询时展开并映射名称
SELECT 
  c.channel_id,
  c.channel_value,
  s.source_name,
  t.tag_name
FROM dwd_contact_channel_inc c
LATERAL VIEW EXPLODE(c.source_codes) tmp1 AS source_code
LATERAL VIEW EXPLODE(c.tag_codes) tmp2 AS tag_code
JOIN dim_source s ON tmp1.source_code = s.source_code
JOIN dim_tag t ON tmp2.tag_code = t.tag_code
WHERE c.data_date = '2025-09-22';

方式2：创建视图封装（简化下游使用）

-- 创建带名称的视图
CREATE VIEW dwd_contact_channel_with_name AS
SELECT 
  c.*,
  -- 用 ARRAY_MAP + JOIN 映射名称（Doris 2.1+）
  ARRAY_MAP(x -> (SELECT source_name FROM dim_source WHERE source_code = x), c.source_codes) AS source_names,
  ARRAY_MAP(x -> (SELECT tag_name FROM dim_tag WHERE tag_code = x), c.tag_codes) AS tag_names
FROM dwd_contact_channel_inc c;

-- 创建带名称的视图
CREATE VIEW dwd_contact_channel_with_name AS
SELECT 
  c.*,
  -- 用 ARRAY_MAP + JOIN 映射名称（Doris 2.1+）
  ARRAY_MAP(x -> (SELECT source_name FROM dim_source WHERE source_code = x), c.source_codes) AS source_names,
  ARRAY_MAP(x -> (SELECT tag_name FROM dim_tag WHERE tag_code = x), c.tag_codes) AS tag_names
FROM dwd_contact_channel_inc c;

⚠️ 注意：ARRAY_MAP + 子查询 在 Doris 中可能性能不佳，建议用方式1或物化视图。

方式3：在 DWS/ADS 层做映射（推荐用于报表）

-- 在聚合层或应用层做最终映射
SELECT 
  s.source_name,
  COUNT(*) as cnt
FROM dwd_contact_channel_inc c
LATERAL VIEW EXPLODE(c.source_codes) tmp AS source_code
JOIN dim_source s ON tmp.source_code = s.source_code
GROUP BY s.source_name;

-- 在聚合层或应用层做最终映射
SELECT 
  s.source_name,
  COUNT(*) as cnt
FROM dwd_contact_channel_inc c
LATERAL VIEW EXPLODE(c.source_codes) tmp AS source_code
JOIN dim_source s ON tmp.source_code = s.source_code
GROUP BY s.source_name;

✅ 三、什么情况下可以存 name？

极少情况，仅当：

维度极小且永不变更（如性别：'M'/'F'，但即使这样也建议存 code）
无维度表，且 name 是业务主键（如某些系统直接用 name 当唯一标识）
临时分析，不入生产数仓

🚫 在企业级数仓中，强烈不建议在 DWD 存 name。

✅ 四、标签（tag）同理！

标签也应存 tag_code，而不是 tag_name：

-- 正确
tag_codes: ['valid', 'not_company', 'high_intent']

-- 错误（未来名称变更会导致历史数据语义混乱）
tag_names: ['有效', '非本公司', '高意向']

-- 正确
tag_codes: ['valid', 'not_company', 'high_intent']

-- 错误（未来名称变更会导致历史数据语义混乱）
tag_names: ['有效', '非本公司', '高意向']

维度表：

-- dim_tag
tag_code    | tag_name     | tag_category
'valid'     | '有效'       | '有效性'
'not_company'| '非本公司'   | '归属'
'high_intent'| '高意向'     | '意向等级'

-- dim_tag
tag_code    | tag_name     | tag_category
'valid'     | '有效'       | '有效性'
'not_company'| '非本公司'   | '归属'
'high_intent'| '高意向'     | '意向等级'

✅ 五、性能优化（Doris）

对 source_codes, tag_codes 建倒排索引

CREATE INDEX idx_source_codes ON dwd_contact_channel_inc (source_codes) USING INVERTED;
CREATE INDEX idx_tag_codes ON dwd_contact_channel_inc (tag_codes) USING INVERTED;

CREATE INDEX idx_source_codes ON dwd_contact_channel_inc (source_codes) USING INVERTED;
CREATE INDEX idx_tag_codes ON dwd_contact_channel_inc (tag_codes) USING INVERTED;

→ 加速 ARRAY_CONTAINS(source_codes, '1') 查询

维度表用 DUPLICATE KEY 或 PRIMARY KEY 模型
→ 加速 JOIN

在 DWS 层预聚合 + 映射名称

-- dws_channel_source_dist
CREATE MATERIALIZED VIEW dws_channel_source_dist AS
SELECT 
  s.source_name,
  COUNT(*) as channel_count
FROM dwd_contact_channel_inc c
LATERAL VIEW EXPLODE(c.source_codes) tmp AS source_code
JOIN dim_source s ON tmp.source_code = s.source_code
GROUP BY s.source_name;

-- dws_channel_source_dist
CREATE MATERIALIZED VIEW dws_channel_source_dist AS
SELECT 
  s.source_name,
  COUNT(*) as channel_count
FROM dwd_contact_channel_inc c
LATERAL VIEW EXPLODE(c.source_codes) tmp AS source_code
JOIN dim_source s ON tmp.source_code = s.source_code
GROUP BY s.source_name;

🧭 总结：DWD 层 Array 字段必须存 ID（code）！

存 ID（code）：保证语义稳定、存储高效、映射灵活
❌ 存 name：埋下数据口径不一致的定时炸弹

文章