文章
数仓建模工具包
📎 1. 《DWD 七步法 Checklist.xlsx》
用途：指导新人从 ODS 到 DWD 表的完整建模流程
适用对象：数据开发、数仓工程师
使用方式：每建一张 DWD 表，填写一份 Checklist
| 步骤 | 任务 | 是否完成 | 负责人 | 交付物链接/说明 |
|---|---|---|---|---|
| 1. 划分业务域 & 明确目标 | 与业务方对齐分析主题（如“高企资质评估”） | ☐ | 《业务域定义.md》 | |
| 定义 DWD 表用途（如“支撑资质评分、区域分布”） | ☐ | |||
| 2. 字段筛选 | 使用四象限法筛选字段（高频/高质量保留） | ☐ | 《字段筛选四象限模板.xlsx》 | |
| 业务方确认保留/删除清单 | ☐ | |||
| 3. 制定清洗 & 标准化规则 | 逐字段制定清洗规则（空值、异常、单位） | ☐ | 《清洗规则表.xlsx》 | |
| 建立码值映射表（province、industry 等） | ☐ | dim_province, dim_industry | ||
| 4. 设计维度退化 & 表结构 | 识别高频维度（如 province_name） | ☐ | ||
| 编写 Doris 建表语句 | ☐ | 《Doris 建表语句模板.sql》 | ||
| 5. 编写 ETL & 测试用例 | 编写 ETL SQL（含 Array 处理） | ☐ | ||
| 编写测试用例（空值、边界、异常） | ☐ | |||
| 6. 建立元数据 & 文档 | 填写字段血缘、口径、负责人 | ☐ | 元数据平台录入 | |
| 编写表级文档（用途、更新频率、下游） | ☐ | 《dwd_xxx_full文档.md》 | ||
| 7. 上线 & 监控 | 表结构创建 | ☐ | ||
| DolphinScheduler 任务配置 | ☐ | 任务名：load_dwd_xxx | ||
| 配置监控告警（数据量、空值率、时效性） | ☐ |
📎 2. 《字段筛选四象限模板.xlsx》
用途：从 ODS 90+ 字段中科学筛选 DWD 保留字段
原则：高频 + 高质量 优先保留
Sheet1: 字段筛选清单
| 字段名（ODS） | 业务含义 | 使用频率（高/低） | 数据质量（高/低） | 处理方式 | 保留字段名（DWD） | 说明 |
|---|---|---|---|---|---|---|
| company_name | 企业名称 | 高 | 高 | ✅ 保留 | company_name | 直接保留 |
| credit_code | 统一信用代码 | 高 | 高 | ✅ 保留 | unified_social_credit_code | 标准化命名 |
| province_raw | 注册省份（原始） | 高 | 中 | ⚠ 清洗+退化 | province_code, province_name | 映射国标+退化名称 |
| phone | 联系电话 | 中 | 低 | ⚠ 清洗后保留 | contact_phone | 标准化为11位 |
| temp_flag | 临时标记 | 低 | 低 | ❌ 删除 | - | 无业务价值 |
| operator_id | 操作人工号 | 低 | 高 | ❌ 删除 | - | 不用于分析 |
| 法人身份证号 | 敏感信息 | 高 | 高 | ❌ 删除或脱敏 | - | 隐私合规 |
Sheet2: 四象限说明
| 高频使用 | 低频使用 | |
|---|---|---|
| 高质量 | ✅ 保留（核心字段） | ⚠ 保留（备用） |
| 低质量 | ⚠ 清洗后保留 | ❌ 删除 |
📎 3. 《Doris 建表语句模板.sql》
适用场景：无历史分区、当前最新全量、Doris 引擎
命名规范：realtime_dwd.dwd_<业务域>_<实体>_full
-- 企业基础表
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',
etl_time DATETIME COMMENT 'ETL时间'
) ENGINE=OLAP
UNIQUE KEY(company_id)
DISTRIBUTED BY HASH(company_id) BUCKETS 10;
-- 联系方式表（含 Array）
CREATE TABLE realtime_dwd.dwd_contact_channel_full (
channel_id BIGINT COMMENT '联系方式ID',
contact_id BIGINT COMMENT '联系人ID',
company_id BIGINT COMMENT '企业ID',
company_name STRING COMMENT '企业名称（退化）',
contact_name STRING COMMENT '联系人名称（退化）',
channel_type VARCHAR(20) COMMENT '方式类型：phone/email/wechat',
channel_value STRING COMMENT '具体值',
source_codes ARRAY<VARCHAR(10)> COMMENT '来源ID数组，如["1","3"]',
tag_codes ARRAY<VARCHAR(20)> COMMENT '标签ID数组，如["valid","not_company"]',
is_valid TINYINT COMMENT '是否有效（预计算）',
is_primary TINYINT COMMENT '是否主联系方式',
etl_time DATETIME COMMENT 'ETL时间'
) ENGINE=OLAP
UNIQUE KEY(channel_id)
DISTRIBUTED BY HASH(company_id) BUCKETS 10;
-- 索引建议（可选）
CREATE INDEX idx_tag_codes ON realtime_dwd.dwd_contact_channel_full(tag_codes) USING INVERTED;📎 4. 《ETL 任务合并清单.md》
标：将 200+ 任务 → 50 以内，串行 20 分钟完成
原则：一个任务处理多个相关表，避免重复 JOIN
# ETL 任务合并清单（高企咨询场景）
## 1. 企业相关
- **任务名**：`load_dwd_company`
- **处理表**：
- `dwd_company_base_full`
- **说明**：单表，简单清洗
## 2. 联系人 + 联系方式
- **任务名**：`load_dwd_contact_and_channel`
- **处理表**：
- `dwd_contact_base_full`
- `dwd_contact_channel_full`
- **说明**：
- 先写联系人表（退化企业维度）
- 再写联系方式表（JOIN 刚生成的联系人表）
- 避免重复 JOIN ODS 企业表
## 3. 知识产权（专利 + 软著）
- **任务名**：`load_dwd_ipr`
- **处理表**：
- `dwd_patent_full`
- `dwd_copyright_full`
- **说明**：结构类似，合并处理
## 4. 聚合层
- **任务名**：`agg_dws_company_summary`
- **处理表**：
- `dws_company_summary`
- **说明**：基于 DWD 预计算字段，无 JOIN
## 5. 画像层
- **任务名**：`build_dwt_company_profile`
- **处理表**：
- `dwt_company_profile`
- **说明**：JOIN DWD + DWS 主键，无 GROUP BY
## 6. ADS 层（按需）
- **任务名**：`ads_sales_company_list`
- **处理表**：
- `ads_sales_company_list`
- **说明**：JOIN DWD + DWS + DWT，拍平 Array，格式化字段📎 5. 《ADS 表开发规范.md》
核心原则：ADS 是唯一出口，一个需求 = 一张表
# ADS 表开发规范
## 1. 命名规范
- 库名：`realtime_ads`
- 表名：`ads_<业务场景>_<主体>_<用途>`
- 示例：
- `ads_sales_company_list` # 销售企业清单
- `ads_unclaimed_company_export` # 待领取客户导出
- `ads_contact_channel_detail` # 联系方式明细
## 2. 字段设计原则
- ✅ **业务友好**：字段名用中文语义（如 `company_grade = 'A类客户'`）
- ✅ **拍平 Array**：`source_codes ARRAY` → `source_names_str STRING`
- ✅ **格式化布尔值**：`is_valid=1` → `is_valid_desc='有效'`
- ✅ **宽表设计**：JOIN DWD + DWS + DWT，一次出结果
- ❌ **禁止技术字段**：如 `company_id`（除非业务需要）
- ❌ **禁止未处理 Array**：下游不会写 `LATERAL VIEW`
## 3. ETL 开发要求
- 必须从 DWD/DWS/DWT 取数，禁止直连 ODS
- 必须在 ADS 层完成最终计算（如客户等级、导出时间）
- 必须添加 `export_time DATETIME` 字段
## 4. 示例
```sql
CREATE TABLE realtime_ads.ads_sales_company_list (
company_name STRING COMMENT '企业名称',
industry_name STRING COMMENT '行业名称',
total_patent_count BIGINT COMMENT '专利总数',
company_grade STRING COMMENT '客户等级（A/B/C类）',
export_time DATETIME COMMENT '导出时间'
);
INSERT INTO realtime_ads.ads_sales_company_list
SELECT
dwd.company_name,
dwd.industry_name,
dws.total_patent_count,
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_dwd.dwd_company_base_full dwd
JOIN realtime_dws.dws_company_summary dws ON dwd.company_id = dws.company_id
JOIN realtime_dwt.dwt_company_profile dwt ON dwd.company_id = dwt.company_id;5. 上线流程
- 创建 ADS 表
- 配置 DolphinScheduler 任务
- 编写《使用说明》：字段含义、适用场景、负责人
- 通知业务方
- 开放 realtime_ads 库权限（按角色）