105 lines
6.4 KiB
Markdown
105 lines
6.4 KiB
Markdown
# CHPA 01/02 重构迁移清单
|
||
|
||
## 1. 范围
|
||
|
||
本批覆盖工作区 `CHPA/` 下全部 6 个 `01` 开头脚本和 11 个 `02` 开头脚本。原文件保持只读，新代码写入 `RE/sql/chpa/01_dwd` 和 `RE/sql/chpa/02_dws`。
|
||
|
||
重构遵循：
|
||
|
||
```text
|
||
DWD -> DWS -> DM
|
||
```
|
||
|
||
本批不修改 `03` 开头脚本。所有 `03` 消费者的旧表引用必须在下一批统一迁移，旧表在此之前保留为验证基线。
|
||
|
||
## 2. 旧文件到新文件映射
|
||
|
||
| 旧文件 | 新文件 | 新目标或处理方式 |
|
||
| --- | --- | --- |
|
||
| `01 dwd_update.sql` | `01_dwd/01_standardize_gnd_codes.sql` | DWD 配置编码标准化 |
|
||
| `01 dwd_update.sql` | `01_dwd/02_refresh_ims_market_config.sql` | 保留 `dwd.dwd_gnd_ims_tblmarket` |
|
||
| `01 dwd_update.sql` | `01_dwd/03_refresh_retail_market_config.sql` | 保留 `dwd.dwd_gnd_ext_retail_tblmarket` |
|
||
| `01 dwd_update.sql` | `01_dwd/04_refresh_dtp_market_config.sql` | 保留 `dwd.dwd_gnd_dtp_tblmarket` |
|
||
| `01 dwd_update.sql` | `01_dwd/05_normalize_gnd_time_windows.sql` | DWD 时间窗口标准化，保留旧默认值差异 |
|
||
| `01 dwd_ims_td_manufacturer_corp.sql` | `01_dwd/06_fix_ims_manufacturer_type.sql` | DWD `ManufacturerType_ID=0 -> 2` 修复 |
|
||
| `01_FB_BLOB_TO_DWD.sql` | `01_dwd/07_ingest_pharbers_province_fact.sql` | 保留 `dwd.dwd_gnd_pharbers_prov_fact` |
|
||
| `01 dwd_ims_atc_hierarchy.sql` | `02_dws/01_dws_ext_td_ims_atc_hierarchy.sql` | `dws.dws_ext_td_ims_atc_hierarchy` |
|
||
| `01 dwd_ims_nfc_hierarchy.sql` | `02_dws/02_dws_ext_td_ims_nfc_hierarchy.sql` | `dws.dws_ext_td_ims_nfc_hierarchy` |
|
||
| `01 dwd_ims_td_manufacturer_corp.sql` | `02_dws/03_dws_ext_td_ims_manufacturer_corporation.sql` | `dws.dws_ext_td_ims_manufacturer_corporation` |
|
||
| `01 dwd_ims_td_pack_property.sql` | `02_dws/04_dws_ext_td_ims_pack_property.sql` | `dws.dws_ext_td_ims_pack_property` |
|
||
| `02 DWS_IMS_TD_GEO.sql` | `02_dws/05_dws_ext_td_ims_geo.sql` | `dws.dws_ext_td_ims_geo` |
|
||
| `02 dws_ims_td_corp_cn.sql` | `02_dws/06_dws_ext_td_ims_corporation_cn.sql` | `dws.dws_ext_td_ims_corporation_cn` |
|
||
| `02 dws_ims_td_manu_cn.sql` | `02_dws/07_dws_ext_td_ims_manufacturer_cn.sql` | `dws.dws_ext_td_ims_manufacturer_cn` |
|
||
| `02 tmp_ims_td_prod_tmp.sql` | `02_dws/08_dws_ext_td_ims_product_multi_manufacturer.sql` | 将临时名单升级为声明式 DWS 标记维表 |
|
||
| `02 dws_ims_td_prod_cn.sql` | `02_dws/09_dws_ext_td_ims_product_cn.sql` | `dws.dws_ext_td_ims_product_cn`，声明式生成 `RANK_TYPE` |
|
||
| `02 dws_ims_td_atc_cn.sql` | `02_dws/10_dws_ext_td_ims_atc_cn.sql` | `dws.dws_ext_td_ims_atc_cn` |
|
||
| `02 dws_ims_td_nfc_cn.sql` | `02_dws/11_dws_ext_td_ims_nfc_cn.sql` | `dws.dws_ext_td_ims_nfc_cn` |
|
||
| `02 dws_ims_td_market_ta.sql` | `02_dws/12_dws_ext_td_ims_market_ta.sql` | `dws.dws_ext_td_ims_market_ta` |
|
||
| `02 tmp_ims_tf_fact_sales.sql` | `02_dws/13_dws_ext_td_ims_pack_ym.sql` | `dws.dws_ext_td_ims_pack_ym` |
|
||
| `02 tmp_ims_tf_fact_sales.sql` | `02_dws/14_dws_ext_tf_ims_chpa_sales.sql` | `dws.dws_ext_tf_ims_chpa_sales` |
|
||
| `02 dws_ims_td_date.sql` | `02_dws/15_dws_ext_td_ims_date.sql` | `dws.dws_ext_td_ims_date` |
|
||
| `02 dws_ims_td_market.sql` | `02_dws/16_dws_ext_td_ims_market.sql` | `dws.dws_ext_td_ims_market` |
|
||
|
||
## 3. 执行顺序
|
||
|
||
### DWD
|
||
|
||
1. 标准化 GND 配置编码。
|
||
2. 刷新 IMS、Retail、DTP 市场配置快照。
|
||
3. 按旧业务口径补齐各配置表时间窗口。
|
||
4. 修复 manufacturer 类型。
|
||
5. Pharbers 省级事实入湖任务独立运行；无匹配文件时必须失败，不能复用旧 staging 数据。
|
||
|
||
### DWS
|
||
|
||
1. ATC/NFC hierarchy。
|
||
2. Manufacturer-corporation mapping。
|
||
3. Pack property。
|
||
4. Geo、corporation/manufacturer 中文维度。
|
||
5. 多厂家产品标记、产品中文维度。
|
||
6. ATC/NFC 中文维度、market-TA mapping。
|
||
7. Pack 月快照、CHPA sales 事实、日期维度。
|
||
8. Market 维度。
|
||
|
||
## 4. 明确保留的兼容行为
|
||
|
||
- ATC/NFC 父级编码截取长度保持不变。
|
||
- Pack、product、molecule 编码补零规则保持不变。
|
||
- A5Z/A5ZD corporation/manufacturer 覆盖规则保持不变。
|
||
- GND 时间默认值中 `190001/209901` 与 `200001/299912` 的历史差异暂不统一。
|
||
- CHPA sales 全国数据从 `202201` 开始，省级数据不增加该下限。
|
||
- Market 扩展市场 ratio 的旧脚本净结果保持为 `1`；缺失 `ELSE` 的疑点只记录，不在本批修复。
|
||
- 日期维度保留 YYYYMM 值 `max(YM)-900`（约九年）的旧算法，尽管旧注释称“最近五年”。
|
||
|
||
## 5. 本批修复的工程问题
|
||
|
||
- DWD 派生宽表迁移到 DWS，并使用 `dws_ext_td_` / `dws_ext_tf_` 命名。
|
||
- 持久化写入使用显式目标字段和最终投影。
|
||
- 一次性建表语句以注释形式放在独立 Databricks cell。
|
||
- 产品排序从“临时表 + UPDATE”改为声明式构建。
|
||
- CHPA sales 从共享 `tmp` 表提升为正式 DWS 事实表。
|
||
- DWS sales 不再读取 DM geography，改为读取本批 DWS geo。
|
||
- Market 的共享临时表、MERGE DELETE 和 UPDATE 改为 notebook 内 CTE、anti join、rank 和投影。
|
||
- Pharbers 入湖在无文件时失败，避免旧 staging 被再次覆盖到 DWD。
|
||
|
||
## 6. 上线前契约确认
|
||
|
||
- Legacy manufacturer build 使用 `T1.*` 且仓库无完整 DDL。新表明确收窄为当前消费者需要的 9 列，注释 CTAS 从源表达式推导字段类型；新增消费者字段前必须先 `DESCRIBE` 源表并协调扩展契约。
|
||
- Market-TA 明确收窄为 `MARKET, TA` 两列，并用零行 CTAS 推导类型；上线前用 `DESCRIBE` 确认旧 source/target 没有仍需保留的业务列。
|
||
- `dws_ext_td_ims_pack_ym` 首次部署必须 `DEEP CLONE` 旧表，不能只复制 schema，否则五年窗口外的历史映射会丢失。
|
||
- CHPA sales 的省份映射从 DM geography 改为 DWS geo 后，必须验证 fact `PROVINCE_C` 到 geo `PROVINCE_C` 的匹配率和 geo 省份唯一性。
|
||
- Legacy DM 消费者读取 `CORP_DES_CN` / `MANU_DES_CN`，而 02 构建契约保留 `CORP_DES_C` / `MANU_DES_C`；03 迁移时必须统一。
|
||
- Market 的扩展 ratio、扩展行 `BU`/`BrandType` 位置互换、KC NULL 去重和无总排序 tie-breaker 均为兼容保留项，业务修复需单独变更。
|
||
|
||
## 7. 下一批必须迁移的 03 消费者
|
||
|
||
本批新表上线后，以下类型的旧引用仍存在于 `03` 脚本中：
|
||
|
||
- `dws.dws_ims_td_geo`、`dws.dws_ims_td_market`；
|
||
- `dws.dws_ims_td_*_cn`；
|
||
- `dwd.dwd_ims_td_pack_property`；
|
||
- `tmp.tmp_ims_tf_fact_sales`；
|
||
- `dws.dws_ims_td_pack_ym`。
|
||
|
||
在 `03` 消费者全部迁移并完成新旧双向差集验证之前，不删除旧表。
|