大数据

数仓建模体系04

Posted by

问题：建模的思路有了，如何一步步落实dwd层建模，比如 做企业dwd模型层，需要先划分企业业务域，制定表字段维度退化、清洗规则、标准化（元数据要求），哪些字段保留，哪些字段需要转换等等 流程化的操作指导。

太好了！你已经掌握了建模的“道”，现在需要的是“术” —— 一套可落地、可复制、流程化的 DWD 层建模操作指南。

下面我为你梳理一套 “企业 DWD 模型层建模七步法”，从“业务域划分”到“字段落地”，每一步都有：

  • ✅ 输入（你需要准备什么）
  • ✅ 输出（这一步产出什么）
  • ✅ 操作步骤（具体怎么做）
  • ✅ 交付物模板（可直接套用）
  • ✅ 避坑指南（常见错误）

🧱 企业 DWD 模型层建模七步法（流程化操作指南）

📌 适用场景：从 ODS 企业主表（90+字段）构建 dwd_company_base_full
📌 目标：输出一张清洗过、标准化、退化维度、字段精简的 DWD 企业表

🔹 第一步：划分业务域 & 明确建模目标

✅ 输入：

  • 业务方需求文档（如“高企资质评估需要哪些字段”）
  • ODS 企业表字段清单（90+字段）
  • 数据字典（源系统字段含义）

✅ 输出：

  • 企业业务域定义
  • DWD 表核心目标（如“支撑资质评估、客户分层”）

🛠️ 操作步骤：

  1. 与业务方对齐“企业分析主题”
    • 资质评估？客户分层？区域分析？行业对标？
    • 示例：“我们需要评估企业是否符合高企申报条件，核心看：行业、规模、知识产权、联系人有效性”
  2. 定义“企业业务域”边界
    • 包含：企业基础属性、资质标签、区域/行业维度
    • 不包含：专利明细、联系人明细（这些是独立业务域）
  3. 明确 DWD 表目标
    • 示例：“dwd_company_base_full 目标：提供企业最新基础画像，支撑资质评分、区域分布、行业分析”

📝 交付物模板：

# 企业业务域定义

## 1. 分析主题
- 高企资质评估
- 客户价值分层
- 区域/行业分布分析

## 2. 包含范围
- 企业基础属性（名称、信用代码、注册资本等）
- 企业分类维度（行业、区域、规模）
- 企业状态标签（是否高新、是否活跃）

## 3. 不包含范围
- 专利/软著明细（属“知识产权域”）
- 联系人/联系方式（属“客户触达域”）

## 4. DWD 表目标
- 表名：`dwd_company_base_full`
- 用途：为 DWS/DWT/ADS 提供干净、标准化、退化维度的企业基础数据

⚠️ 避坑指南：

  • ❌ 不要试图“一个表解决所有问题” → 按业务域拆分
  • ❌ 不要跳过业务对齐 → 否则建完没人用

🔹 第二步：字段筛选 —— 从90+字段中选出核心字段

✅ 输入：

  • ODS 企业表字段清单（含字段名、类型、示例值、业务含义）
  • 业务需求（第一步输出）

✅ 输出：

  • 保留字段清单（约40~50个）
  • 删除字段清单（约40~50个）

🛠️ 操作步骤：

  1. 四象限筛选法（按“使用频率”和“数据质量”划分）：
高频使用低频使用
高质量✅ 保留（核心字段）⚠️ 保留（备用）
低质量⚠️ 清洗后保留❌ 删除

具体筛选标准：

类型处理方式示例
✅ 核心业务字段保留company_name,credit_code,registered_capital
✅ 高频分析维度保留 + 退化province,industry→ 退化province_name
⚠️ 低频字段保留（放最后）english_name,website
⚠️ 脏数据字段清洗后保留phone→ 标准化为11位手机号
❌ 无分析价值删除temp_remark,operator_id,system_flag
❌ 敏感字段删除或脱敏法人身份证号→ 删除
  1. 与业务方确认清单
    • 用 Excel 共享筛选结果，业务方标注“必须保留”或“可删”

📝 交付物模板：

# DWD 企业表字段筛选清单

## 保留字段（45个）

| 字段名 | 来源字段 | 说明 | 处理方式 |
|--------|----------|------|----------|
| company_id | company_id | 企业ID | 直接保留 |
| company_name | ent_name | 企业名称 | 清洗空格、特殊字符 |
| unified_social_credit_code | credit_code | 统一信用代码 | 校验格式，空值填'' |
| registered_capital | reg_capital | 注册资本 | 转为万元，DECIMAL(10,2) |
| province_code | province | 注册省份 | 映射为国标编码 |
| province_name | - | 省份名称 | 退化维度，从 dim_province 映射 |
| ... | ... | ... | ... |

## 删除字段（45个）

| 字段名 | 来源字段 | 删除原因 |
|--------|----------|----------|
| temp_flag | temp_flag | 临时标记，无业务含义 |
| operator_id | op_id | 操作人工号，不用于分析 |
| ... | ... | ... |

⚠️ 避坑指南：

  • ❌ 不要“贪多” → 字段越多，ETL 越慢，下游越不敢用
  • ❌ 不要“凭感觉删” → 用四象限法 + 业务确认

🔹 第三步：制定清洗 & 标准化规则

✅ 输入：

  • 保留字段清单（第二步输出）
  • 数据质量报告（如空值率、异常值分布）

✅ 输出：

  • 每个字段的清洗规则
  • 标准化映射表（如省份、行业码值映射）

🛠️ 操作步骤：

  1. 逐字段制定清洗规则：
字段问题清洗规则
company_name含空格、乱码TRIM(REPLACE(name, '　', ''))
credit_code格式不一、空值校验18位，空值填''
registered_capital单位混乱（元/万元）统一转为“万元”，除以10000
establish_date时间戳、字符串混用统一转为DATE类型

制定码值映射规则：

字段映射规则示例
province_code映射国标北京→110000
industry_code映射 GB/T 4754科技推广→M75
company_type映射标准类型有限责任公司→1

建立“数据字典.xlsx”（团队共享）

📝 交付物模板：

# 字段清洗 & 标准化规则

## 1. 清洗规则

| 字段名 | 原始类型 | 目标类型 | 清洗规则 | 示例 |
|--------|----------|----------|----------|------|
| company_name | STRING | STRING | TRIM(REPLACE(name, '　', '')) | "　百度　" → "百度" |
| unified_social_credit_code | STRING | STRING | 校验18位，否则'' | "123" → "" |
| registered_capital | BIGINT | DECIMAL(10,2) | / 10000 | 500000 → 50.00 |
| establish_date | STRING | DATE | TRY_CAST TO DATE | "2020-01-01" → 2020-01-01 |

## 2. 码值映射表

| 维度 | 原始值 | 映射值 | 来源 |
|------|--------|--------|------|
| province | 北京 | 110000 | 国家统计局 |
| industry | 信息传输 | I61 | GB/T 4754-2017 |
| ... | ... | ... | ... |

⚠️ 避坑指南：

  • ❌ 不要“跳过清洗” → 脏数据会污染整个数仓
  • ❌ 不要“硬编码映射” → 用配置表或字典表，便于维护

🔹 第四步：设计维度退化 & 表结构

✅ 输入：

  • 保留字段清单
  • 清洗规则
  • 业务常用筛选条件（如“按省份筛选”）

✅ 输出：

  • DWD 表结构（含字段名、类型、注释）
  • 退化维度清单

🛠️ 操作步骤：

  1. 识别“高频筛选维度” → 退化
    • 如：province_code → 同时冗余 province_name
    • 如：industry_code → 同时冗余 industry_name
  2. 设计表结构（Doris 语法）：
CREATE TABLE realtime_dwd.dwd_company_base_full (
  company_id BIGINT COMMENT '企业ID',
  company_name STRING COMMENT '企业名称',
  unified_social_credit_code STRING COMMENT '统一社会信用代码',
  registered_capital_wan DECIMAL(10,2) COMMENT '注册资本（万元）',
  establish_date DATE COMMENT '成立日期',
  -- 退化维度
  province_code VARCHAR(10) COMMENT '注册省份编码',
  province_name STRING COMMENT '注册省份名称',
  industry_code VARCHAR(10) COMMENT '行业分类编码',
  industry_name STRING COMMENT '行业分类名称',
  -- 状态标签
  is_high_tech TINYINT COMMENT '是否高新技术企业 0/1',
  is_active TINYINT COMMENT '是否活跃企业 0/1',
  etl_time DATETIME COMMENT 'ETL时间'
) ENGINE=OLAP
UNIQUE KEY(company_id)
DISTRIBUTED BY HASH(company_id) BUCKETS 10;
  1. 明确“不退化”的字段
    • 易变字段（如评分、状态）→ 不退化
    • 低频字段 → 不退化

📝 交付物模板：

# DWD 表结构设计

## 表名
`realtime_dwd.dwd_company_base_full`

## 退化维度

| 维度字段 | 退化描述字段 | 说明 |
|----------|--------------|------|
| province_code | province_name | 用于按省份筛选/分组 |
| industry_code | industry_name | 用于按行业筛选/分组 |

## 表结构

| 字段名 | 类型 | 注释 | 来源/计算逻辑 |
|--------|------|------|---------------|
| company_id | BIGINT | 企业ID | 直接来自 ODS |
| company_name | STRING | 企业名称 | 清洗后保留 |
| ... | ... | ... | ... |

⚠️ 避坑指南：

  • ❌ 不要“过度退化” → 只退化真正高频的维度
  • ❌ 不要“退化易变字段” → 如“最新评分”，会导致数据不一致

🔹 第五步：编写 ETL 逻辑 & 测试用例

✅ 输入：

  • 表结构设计
  • 清洗规则
  • 映射表

✅ 输出：

  • ETL SQL 脚本
  • 测试用例（含预期结果）

🛠️ 操作步骤：

编写 ETL SQL（示例）：

INSERT INTO realtime_dwd.dwd_company_base_full
SELECT
  company_id,
  TRIM(REPLACE(company_name, '　', '')) AS company_name,
  CASE 
    WHEN LENGTH(credit_code) = 18 THEN credit_code 
    ELSE '' 
  END AS unified_social_credit_code,
  CAST(reg_capital AS DECIMAL(20,2)) / 10000 AS registered_capital_wan,
  TRY_CAST(establish_date AS DATE) AS establish_date,
  -- 退化维度
  dict_map('province', province_raw) AS province_code,
  (SELECT province_name FROM dim_province WHERE province_code = dict_map('province', province_raw)) AS province_name,
  dict_map('industry', industry_raw) AS industry_code,
  (SELECT industry_name FROM dim_industry WHERE industry_code = dict_map('industry', industry_raw)) AS industry_name,
  -- 状态标签
  CASE WHEN high_tech_flag = 'Y' THEN 1 ELSE 0 END AS is_high_tech,
  CASE WHEN last_contact_time > DATE_SUB(NOW(), 90) THEN 1 ELSE 0 END AS is_active,
  NOW() AS etl_time
FROM realtime_ods.ods_crm_company_full
WHERE is_deleted = 0;

编写测试用例：

# 测试用例

## 用例1：企业名称清洗
- 输入：company_name = "　百度在线网络技术（北京）有限公司　"
- 预期：company_name = "百度在线网络技术（北京）有限公司"

## 用例2：信用代码校验
- 输入：credit_code = "123"
- 预期：unified_social_credit_code = ""

## 用例3：注册资本转换
- 输入：reg_capital = 500000
- 预期：registered_capital_wan = 50.00

⚠️ 避坑指南：

  • ❌ 不要“写完不测试” → 至少覆盖空值、异常值、边界值
  • ❌ 不要“硬编码映射” → 用 dict_map 函数或子查询

🔹 第六步：建立元数据 & 文档

✅ 输入：

  • 表结构
  • ETL 逻辑
  • 清洗规则

✅ 输出：

  • 字段级元数据（血缘、口径、负责人）
  • 表级文档（更新频率、负责人、下游依赖）

🛠️ 操作步骤：

  1. 字段级元数据（示例）：
字段名业务口径技术口径负责人下游使用
registered_capital_wan注册资本（单位：万元）ODS.reg_capital / 10000张三DWS, ADS

表级文档：

# dwd_company_base_full 文档

## 1. 表说明
- 用途：企业基础画像，支撑资质评估、客户分层
- 更新频率：每小时（DolphinScheduler 调度）
- 负责人：数据团队-李四

## 2. 下游依赖
- dws_company_summary
- dwt_company_profile
- ads_sales_company_list

## 3. 变更记录
- 2025-09-01：新增 is_active 字段

⚠️ 避坑指南：

  • ❌ 不要“无文档” → 新人接手时会崩溃
  • ❌ 不要“血缘缺失” → 出问题无法追溯

🔹 第七步：上线 & 监控

✅ 输入：

  • ETL 脚本
  • 测试报告
  • 文档

✅ 输出：

  • 上线的 DWD 表
  • 监控告警规则

🛠️ 操作步骤：

  1. 上线 Checklist：
    • 表结构创建
    • ETL 任务配置（DolphinScheduler）
    • 测试数据验证
    • 文档归档
    • 通知下游
  2. 监控指标：
指标监控方式告警阈值
数据量波动COUNT(*) vs 昨日> ±30%
空值率COUNT_IF(company_name='') / COUNT(*)> 5%
时效性MAX(etl_time) vs NOW()> 1小时
  1. 告警通知：
    • 企业微信群 / 邮件 / 短信

🧭 总结：七步法让你从“混乱”到“体系化”

通过这套“七步法”，你可以：

✅ 标准化：每个字段都有清晰的清洗、映射规则
✅ 可复制：新表建模直接套用流程
✅ 可追溯：元数据、文档、血缘齐全
✅ 可监控：数据质量、时效性有保障

问题：按照上面的思路帮我出下dws、dwt、ads的建模流程化操作指南

📊 DWS、DWT、ADS 三层建模标准化流程（流程化操作指南）

📌 适用场景：基于已建好的 DWD 层，构建 DWS（轻度聚合）、DWT（主题画像）、ADS（应用出口）
📌 目标：每一层都有明确职责、标准化流程、可交付文档，避免“随意建表、烟囱开发”

第一部分：DWS 层 —— 轻度聚合层建模五步法

🎯 定位：按分析主题、维度、指标进行轻度聚合，支撑多维分析、趋势统计
🚫 禁止：存储明细、做复杂业务逻辑、直接供业务查询

🔹 第一步：定义聚合主题 & 指标口径

✅ 输入：

  • 业务需求文档（如“需要按行业统计企业专利数”）
  • DWD 表结构（如 dwd_company_base_full, dwd_patent_full）
  • 指标字典（如“有效专利”的定义）

✅ 输出：

  • 聚合主题清单（如“企业专利统计”、“联系人有效性分析”）
  • 每个主题的指标口径文档

🛠️ 操作步骤：

  1. 与业务方对齐“分析主题”
    • 示例：“我们需要三个聚合主题：
      1. 企业知识产权统计（专利、软著）
      2. 企业联系人有效性分析
      3. 区域行业分布统计”
  2. 明确定义每个指标的口径
    • 示例：“有效专利 = 状态为‘授权’或‘有效’的专利，且申请日期在过去10年内”
  3. 确定聚合维度
    • 示例：“按企业、按行业、按省份、按专利类型”

📝 交付物模板：

# DWS 聚合主题定义

## 主题1：企业知识产权统计

### 指标口径
- total_patent_count：COUNT(patent_id) WHERE status IN ('授权','有效') AND apply_date >= DATE_SUB(NOW(), 3650)
- invention_patent_count：同上 + patent_type = '发明专利'

### 聚合维度
- company_id
- industry_code
- province_code
- patent_type_code

⚠️ 避坑指南：

  • ❌ 不要“模糊口径” → 必须明确定义，避免后续扯皮
  • ❌ 不要“过度聚合” → 只做轻度聚合，复杂逻辑留给 ADS

🔹 第二步：设计 DWS 表结构

✅ 输入：

  • 聚合主题 & 指标口径
  • DWD 表结构

✅ 输出：

  • DWS 表结构（含字段、类型、注释）
  • 分组键（GROUP BY 字段）

🛠️ 操作步骤：

  1. 确定表名规范
    • dws_<主题>_<维度粒度>
    • 示例：dws_company_patent_by_industry（按行业聚合）
  2. 设计字段
    • 分组键（维度字段）
    • 聚合指标（SUM/COUNT/MAX）
    • 时间戳（etl_time）
  3. 选择 Doris 表模型
    • 推荐 AGGREGATE KEY（自动聚合）或 UNIQUE KEY（手动覆盖）

📝 交付物模板：

-- 表结构示例
CREATE TABLE realtime_dws.dws_company_patent_by_industry (
  industry_code VARCHAR(10) COMMENT '行业编码',
  industry_name STRING COMMENT '行业名称',  -- 退化维度
  total_patent_count BIGINT SUM COMMENT '专利总数',
  invention_patent_count BIGINT SUM COMMENT '发明专利数',
  last_update_time DATETIME REPLACE COMMENT '最后更新时间',
  etl_time DATETIME REPLACE COMMENT 'ETL时间'
) ENGINE=OLAP
AGGREGATE KEY(industry_code, industry_name)
DISTRIBUTED BY HASH(industry_code) BUCKETS 10;

⚠️ 避坑指南：

  • ❌ 不要“存明细” → DWS 是聚合层，不是明细层
  • ❌ 不要“维度过多” → 避免笛卡尔积，控制在3~4个维度

🔹 第三步：编写 ETL 逻辑 & 测试用例

✅ 输入：

  • DWS 表结构
  • 指标口径

✅ 输出：

  • ETL SQL 脚本
  • 测试用例（含预期结果）

🛠️ 操作步骤：

  1. 编写聚合 SQL：
-- 示例：按行业聚合专利
INSERT INTO realtime_dws.dws_company_patent_by_industry
SELECT
  dwd.industry_code,
  dwd.industry_name,
  COUNT(p.patent_id) AS total_patent_count,
  SUM(CASE WHEN p.patent_type_code = 'invention' THEN 1 ELSE 0 END) AS invention_patent_count,
  MAX(p.apply_date) AS last_update_time,
  NOW() AS etl_time
FROM realtime_dwd.dwd_company_base_full dwd
JOIN realtime_dwd.dwd_patent_full p ON dwd.company_id = p.company_id
WHERE p.status_code IN ('authorized', 'valid')
  AND p.apply_date >= DATE_SUB(NOW(), INTERVAL 10 YEAR)
GROUP BY dwd.industry_code, dwd.industry_name;

编写测试用例：

# 测试用例

## 用例1：发明专利统计
- 条件：industry_code = 'I61', 且专利类型为 'invention'
- 预期：invention_patent_count = 该行业发明专利数

## 用例2：空值处理
- 条件：industry_code 为空
- 预期：不计入任何分组（或归入'未知'）

⚠️ 避坑指南：

  • ❌ 不要“JOIN 多个大表” → 优先用 DWD 预计算字段
  • ❌ 不要“无测试” → 至少验证总数、空值、边界

🔹 第四步：建立元数据 & 文档

✅ 输入：

  • ETL 逻辑
  • 表结构

✅ 输出：

  • 字段级元数据（血缘、口径、负责人）
  • 表级文档（更新频率、下游依赖）

📝 交付物模板：

# dws_company_patent_by_industry 文档

## 1. 表说明
- 用途：按行业统计企业专利分布
- 更新频率：每小时
- 负责人：数据团队-王五

## 2. 下游依赖
- dwt_company_profile
- ads_bi_patent_analysis

## 3. 指标口径
- total_patent_count：状态为‘授权’或‘有效’的专利，且申请日期在过去10年内

⚠️ 避坑指南：

  • ❌ 不要“无文档” → 新人无法接手
  • ❌ 不要“血缘缺失” → 出问题无法追溯

🔹 第五步：上线 & 监控

✅ 输入：

  • ETL 脚本
  • 测试报告
  • 文档

✅ 输出：

  • 上线的 DWS 表
  • 监控告警规则

🛠️ 操作步骤：

  1. 上线 Checklist：
    • 表结构创建
    • ETL 任务配置
    • 测试数据验证
    • 文档归档
    • 通知下游
  2. 监控指标：
指标监控方式告警阈值
数据量波动SUM(total_patent_count) vs 昨日> ±30%
空值率COUNT_IF(industry_code='') / COUNT(*)> 5%

第二部分：DWT 层 —— 主题画像层建模四步法

🎯 定位：构建企业/联系人画像，只包含稳定、高频、通用的特征
🚫 禁止：存储过程数据、临时指标、业务专属字段

🔹 第一步：定义画像主题 & 特征清单

✅ 输入：

  • 业务需求（如“销售需要企业价值评分”）
  • DWS 表（如 dws_company_summary）
  • DWD 表（如 dwd_company_base_full）

✅ 输出：

  • 画像主题（如“企业画像”、“联系人画像”）
  • 特征清单（每个特征的计算逻辑）

🛠️ 操作步骤：

  1. 与业务方对齐“画像用途”
    • 示例：“企业画像用于：销售优先级排序、客户分层、资源分配”
  2. 列出核心特征
    • 示例：
      • customer_value_score（客户价值分）
      • is_high_potential（是否高潜力）
      • company_scale_level（企业规模等级）
  3. 明确定义每个特征的计算逻辑
    • 示例：customer_value_score = 专利数 * 0.4 + 联系有效性 * 0.6

📝 交付物模板：

# DWT 画像特征定义

## 画像主题：企业画像

### 特征清单
- customer_value_score：专利数 * 0.4 + 联系有效性 * 0.6
- is_high_potential：customer_value_score > 80
- company_scale_level：按注册资本分层（1~5级）

⚠️ 避坑指南：

  • ❌ 不要“特征爆炸” → 只选5~10个核心特征
  • ❌ 不要“临时特征” → 只放稳定、通用的特征

🔹 第二步：设计 DWT 表结构

✅ 输入：

  • 画像特征清单
  • DWD/DWS 表结构

✅ 输出：

  • DWT 表结构（含字段、类型、注释）

🛠️ 操作步骤：

  1. 确定表名规范
    • dwt_<主体>_profile
    • 示例：dwt_company_profile
  2. 设计字段
    • 主体ID（如 company_id）
    • 画像特征（预计算字段）
    • 更新时间

📝 交付物模板：

CREATE TABLE realtime_dwt.dwt_company_profile (
  company_id BIGINT COMMENT '企业ID',
  company_name STRING COMMENT '企业名称',
  industry_name STRING COMMENT '行业名称',
  total_patent_count BIGINT COMMENT '专利总数（来自DWS）',
  valid_contact_rate DECIMAL(5,4) COMMENT '联系有效性（来自DWS）',
  customer_value_score DECIMAL(5,2) COMMENT '客户价值分',
  is_high_potential TINYINT COMMENT '是否高潜力',
  company_scale_level TINYINT COMMENT '企业规模等级',
  profile_update_time DATETIME COMMENT '画像更新时间',
  etl_time DATETIME COMMENT 'ETL时间'
) ENGINE=OLAP
UNIQUE KEY(company_id)
DISTRIBUTED BY HASH(company_id) BUCKETS 10;

⚠️ 避坑指南：

  • ❌ 不要“存过程数据” → 只存最终特征
  • ❌ 不要“JOIN 多表” → 优先用 DWS 预聚合字段

🔹 第三步：编写 ETL 逻辑 & 测试用例

✅ 输入：

  • DWT 表结构
  • 特征计算逻辑

✅ 输出：

  • ETL SQL 脚本
  • 测试用例

🛠️ 操作步骤：

  1. 编写画像 SQL：
INSERT INTO realtime_dwt.dwt_company_profile
SELECT
  dwd.company_id,
  dwd.company_name,
  dwd.industry_name,
  dws.total_patent_count,
  dws.valid_contact_rate,
  (dws.total_patent_count * 0.4 + dws.valid_contact_rate * 100 * 0.6) AS customer_value_score,
  CASE WHEN (dws.total_patent_count * 0.4 + dws.valid_contact_rate * 100 * 0.6) > 80 THEN 1 ELSE 0 END AS is_high_potential,
  CASE 
    WHEN dwd.registered_capital_wan > 1000 THEN 5
    WHEN dwd.registered_capital_wan > 500 THEN 4
    ELSE 3
  END AS company_scale_level,
  NOW() AS profile_update_time,
  NOW() AS etl_time
FROM realtime_dwd.dwd_company_base_full dwd
JOIN realtime_dws.dws_company_summary dws ON dwd.company_id = dws.company_id;

测试用例：

# 测试用例

## 用例1：客户价值分计算
- 输入：total_patent_count=10, valid_contact_rate=0.8
- 预期：customer_value_score = 10*0.4 + 80*0.6 = 52.0

## 用例2：高潜力判断
- 输入：customer_value_score=85
- 预期：is_high_potential=1

⚠️ 避坑指南：

  • ❌ 不要“复杂计算” → 保持 ETL 简单，复杂逻辑放 ADS
  • ❌ 不要“无测试” → 验证特征计算逻辑

🔹 第四步：上线 & 治理

✅ 输入：

  • ETL 脚本
  • 测试报告
  • 文档

✅ 输出：

  • 上线的 DWT 表
  • 治理规范（字段准入、生命周期）

🛠️ 操作步骤：

  1. 上线 Checklist（同 DWS）
  2. 治理规范：
    • 新增字段需评审（是否高频、稳定、通用）
    • 每季度清理低频字段

第三部分：ADS 层 —— 应用出口层建模四步法

🎯 定位：按需定制，统一出口，所有查询/导出必须走 ADS
🚫 禁止：直接查 DWD/DWS/DWT，无文档建表

🔹 第一步：需求分析 & 场景定义

✅ 输入：

  • 业务需求（如“销售需要导出高潜力企业清单”）
  • 用户角色（销售、BI、运营）

✅ 输出：

  • ADS 场景清单（每个场景一张表）
  • 字段需求清单

🛠️ 操作步骤：

  1. 明确“谁在什么场景用”
    • 示例：“销售部：需要导出‘高潜力企业清单’，包含企业名称、联系方式、评分”
  2. 列出所需字段
    • 示例：
      • company_name
      • contact_phone
      • customer_value_score
      • 导出时间

📝 交付物模板：

# ADS 需求定义

## 场景：销售导出高潜力企业

### 使用人
- 销售团队

### 字段需求
- company_name
- contact_phone（主联系方式）
- customer_value_score
- export_time

⚠️ 避坑指南：

  • ❌ 不要“一个表打天下” → 一个场景一张表
  • ❌ 不要“字段堆砌” → 只给需要的字段

🔹 第二步：设计 ADS 表结构

✅ 输入：

  • 场景定义
  • DWD/DWS/DWT 表结构

✅ 输出：

  • ADS 表结构（含字段、类型、注释）

🛠️ 操作步骤：

  1. 确定表名规范
    • ads_<场景>_<主体>_<用途>
    • 示例：ads_sales_company_export
  2. 设计字段
    • 业务友好字段（中文、格式化）
    • 预计算字段（如排名、分组）
    • 导出时间戳

📝 交付物模板：

CREATE TABLE realtime_ads.ads_sales_company_export (
  company_name STRING COMMENT '企业名称',
  contact_phone STRING COMMENT '联系电话',
  customer_value_score DECIMAL(5,2) COMMENT '客户价值分',
  company_grade STRING COMMENT '客户等级（A/B/C）',
  export_time DATETIME COMMENT '导出时间'
) ENGINE=OLAP
DUPLICATE KEY(company_name)
DISTRIBUTED BY HASH(company_name) BUCKETS 10;

⚠️ 避坑指南：

  • ❌ 不要“技术字段” → 如 company_id，除非业务需要
  • ❌ 不要“未格式化” → 如 is_valid=1 → 应转为“有效”

🔹 第三步：编写 ETL 逻辑 & 测试用例

✅ 输入：

  • ADS 表结构
  • 字段需求

✅ 输出：

  • ETL SQL 脚本
  • 测试用例

🛠️ 操作步骤：

  1. 编写 ADS SQL：
INSERT INTO realtime_ads.ads_sales_company_export
SELECT
  dwt.company_name,
  chc.channel_value AS contact_phone,
  dwt.customer_value_score,
  CASE 
    WHEN dwt.customer_value_score > 90 THEN 'A类'
    WHEN dwt.customer_value_score > 70 THEN 'B类'
    ELSE 'C类'
  END AS company_grade,
  NOW() AS export_time
FROM realtime_dwt.dwt_company_profile dwt
JOIN realtime_dwd.dwd_contact_channel_full chc ON dwt.company_id = chc.company_id
WHERE dwt.is_high_potential = 1
  AND chc.is_primary = 1
  AND chc.is_valid = 1;

测试用例：

# 测试用例

## 用例1：客户等级映射
- 输入：customer_value_score=95
- 预期：company_grade='A类'

## 用例2：联系方式过滤
- 条件：is_primary=1 AND is_valid=1
- 预期：只取主有效联系方式

⚠️ 避坑指南：

  • ❌ 不要“直接查 DWD” → 必须通过 DWT/DWS
  • ❌ 不要“无格式化” → 字段要业务友好

🔹 第四步：上线 & 权限控制

✅ 输入：

  • ETL 脚本
  • 测试报告
  • 文档

✅ 输出：

  • 上线的 ADS 表
  • 权限控制策略

🛠️ 操作步骤：

  1. 上线 Checklist（同前）
  2. 权限控制：
    • 只开放 ADS 库给业务用户
    • 按角色授权（如销售只能查 sales_ 开头的表）
  3. 文档要求：
    • 每个 ADS 表必须有《使用说明》

🧭 总结：三层建模标准化流程

层级核心步骤关键交付物
DWS1. 定义主题
2. 设计表结构
3. 编写ETL
4. 元数据
5. 监控		聚合口径文档、表结构、ETL脚本
DWT1. 定义画像
2. 设计表结构
3. 编写ETL
4. 治理		画像特征文档、表结构、治理规范
ADS1. 需求分析
2. 设计表结构
3. 编写ETL
4. 权限		场景需求文档、表结构、权限策略

下一篇
数仓建模体系05
上一篇 数仓建模体系03