Neo-ZQYY/.kiro/specs/dwd-business-panorama/design.md

# 设计文档：DWD 业务全景梳理

## 概述

本设计文档描述如何系统梳理飞球 ETL 的 DWD 层全部业务数据，产出 5 份全景分析文档。这是一个纯文档梳理任务，不涉及代码改动，核心挑战在于：如何在"数据本源优先"的强制准则下，高效、准确地完成 7 个业务域、约 30 张表（含扩展表）的字段语义验证和跨表关联分析。

### 设计目标

1. 定义可重复执行的梳理方法论，确保每张表的分析过程一致
2. 设计每份文档的结构模板，确保产出物格式统一
3. 规划数据验证的技术方案，确保结论可追溯
4. 设计文档间的引用和关联机制，避免信息重复

### 范围

- 输入：`test_etl_feiqiu` 数据库 DWD schema 的全部表和数据
- 参考：现有 BD 手册（`apps/etl/connectors/feiqiu/docs/database/DWD/`）、已有分析报告（`docs/reports/`）
- 产出：5 份全景文档，输出到 `docs/reports/`

### DWD 表全景

根据现有 BD 手册，DWD 层包含以下表：

| 类型 | 表数量 | 表名 |
|------|--------|------|
| 维度表（主表） | 9 | `dim_site`, `dim_table`, `dim_assistant`, `dim_member`, `dim_member_card_account`, `dim_tenant_goods`, `dim_store_goods`, `dim_goods_category`, `dim_groupbuy_package` |
| 维度表（扩展表） | 8 | 上述除 `dim_goods_category` 外均有 `_ex` 扩展表 |
| 事实表（主表） | 13 | `dwd_settlement_head`, `dwd_payment`, `dwd_table_fee_log`, `dwd_table_fee_adjust`, `dwd_assistant_service_log`, `dwd_member_balance_change`, `dwd_recharge_order`, `dwd_refund`, `dwd_groupbuy_redemption`, `dwd_platform_coupon_redemption`, `dwd_store_goods_sale`, `dwd_goods_stock_summary`, `dwd_goods_stock_movement` |
| 事实表（扩展表） | 11 | 除 `dwd_payment`, `dwd_goods_stock_summary`, `dwd_goods_stock_movement` 外均有 `_ex` 扩展表 |

共计约 43 张表。


## 架构

### 整体梳理流程

梳理工作分为两个阶段：基础层（表结构与字段语义）和全景层（跨表关联分析）。基础层产出为全景层的输入。

```mermaid
flowchart TD
    subgraph Phase1["阶段一：基础层梳理"]
        A[枚举 DWD 全部表] --> B[按业务域分组]
        B --> C[逐表执行智能聚焦分析]
        C --> D[字段分类筛选]
        D --> E[业务关键字段倒推验证]
        E --> F[产出：DWD 表结构与字段语义总览]
    end

    subgraph Phase2["阶段二：全景层梳理"]
        F --> G[业务全景：消费产生机制]
        F --> H[账务全景：结算与支付]
        F --> I[财务全景：收入与对账]
        F --> J[维度表与主数据全景]
    end

    subgraph Validation["贯穿：数据验证"]
        K[information_schema 查表结构]
        L[SQL 查询验证值域分布]
        M[交叉查询验证关联关系]
        N[对账公式全量验证]
    end

    C -.-> K
    E -.-> L
    E -.-> M
    H -.-> N
```

### 信息流向

```mermaid
flowchart LR
    subgraph 输入源
        DB[(test_etl_feiqiu<br/>DWD schema)]
        BD[现有 BD 手册<br/>宏观参考]
        RPT[已有分析报告<br/>待验证假设]
    end

    subgraph 梳理过程
        DB -->|information_schema| META[表结构元数据]
        DB -->|SQL 查询| DATA[实际数据验证]
        BD -->|宏观层可参考| REF[参考起点]
        RPT -->|字段级需验证| HYP[待验证假设]
    end

    subgraph 产出
        META --> DOC1[文档1: 表结构总览]
        DATA --> DOC1
        REF --> DOC1
        HYP --> DOC1
        DOC1 --> DOC2[文档2: 业务全景]
        DOC1 --> DOC3[文档3: 账务全景]
        DOC1 --> DOC4[文档4: 财务全景]
        DOC1 --> DOC5[文档5: 维度表全景]
    end
```


## 组件与接口

### 组件一：单表智能聚焦分析器

对每张 DWD 表执行标准化的分析流程，产出该表的字段语义报告。

#### 分析流程（每张表）

```
步骤 1: 表结构获取
  → 查询 information_schema.columns 获取列名、类型、nullable
  → 禁止参考 db/ 目录下的 DDL .sql 文件

步骤 2: 字段分类筛选
  → 查询全表空字段（SELECT column WHERE ALL NULL），标记为"空字段-跳过"
  → 识别 ETL 管理字段（_etl_loaded_at, _etl_batch_id 等），简要标注
  → 识别含义透明字段（id, site_id, created_at 等），仅列出
  → 剩余为"业务关键字段"，进入深度验证

步骤 3: 业务关键字段倒推验证
  → 从含义明确的字段出发（如 id, site_id, total_amount）
  → 通过 JOIN / 聚合对比 / 值域交叉推断不确定字段
  → 金额字段：MIN/MAX/AVG/中位数/NULL占比 + 交叉验证
  → 枚举字段：DISTINCT 值 + 频次分布
  → 关联 ID：JOIN 验证关联完整性

步骤 4: 偏差检测
  → 对比现有 BD 手册的字段描述
  → 标注一致/偏差/错误
```

#### 输出格式（每张表）

```markdown
### {table_name}

**业务职责**：一句话描述
**数据状态**：{行数} 行，时间范围 {min_date} ~ {max_date}
**主键**：{pk_fields}
**关联表**：{related_tables with join fields}

#### 业务关键字段

| 字段名 | 类型 | 验证状态 | 语义说明 | 值域/分布 |
|--------|------|----------|----------|-----------|
| ... | ... | ✅/⚠️/❌ | ... | ... |

#### 空字段（附录）
{列出全 NULL 的字段名}

#### 偏差记录
{与现有文档不一致的地方}
```

### 组件二：全景文档生成器

基于单表分析结果，按业务视角组织跨表关联分析。

#### 全景文档通用模板

```markdown
# {全景文档标题}

> 数据来源：test_etl_feiqiu (DWD schema)
> 验证日期：{date}
> 数据时间范围：{min_date} ~ {max_date}

## 目录
{自动生成}

## 正文
{按业务逻辑组织的分析内容}
{每个关键结论标注验证状态：✅ 已验证 / ⚠️ 部分验证 / ❌ 未验证}

## 附录
### 验证 SQL
{关键验证查询}
### 数据样例
{来自测试库的真实数据}
```

### 组件三：数据验证引擎

贯穿整个梳理过程的验证机制。

#### 验证类型

| 验证类型 | 方法 | 适用场景 |
|----------|------|----------|
| 值域验证 | MIN/MAX/AVG/MEDIAN/NULL% | 金额字段、数值字段 |
| 枚举验证 | DISTINCT + COUNT | 状态字段、类型字段 |
| 关联验证 | LEFT JOIN + NULL 检查 | 外键关联完整性 |
| 等式验证 | SUM 对比 | 对账公式（F1~F6 等） |
| 交叉验证 | 多表 JOIN + 聚合对比 | 跨表金额一致性 |
| 边界验证 | WHERE = 0 / < 0 / IS NULL | 异常值业务含义 |

#### 验证结果标注规范

- ✅ 已验证：附验证 SQL 摘要或结果统计
- ⚠️ 部分验证：附已知例外数量和分类
- ❌ 未验证：附原因（数据不足/无法关联/逻辑不明）
- ⚠️ 警告：经多次交叉验证仍无法对齐的数据关系


## 数据模型

### DWD 表按业务域分组

本次梳理将 DWD 层全部表按 7 个业务域组织，每个域内的表构成一个分析单元。

```mermaid
erDiagram
    %% 结算域
    dwd_settlement_head ||--o{ dwd_payment : "order_settle_id"
    dwd_settlement_head ||--o{ dwd_table_fee_log : "order_settle_id"
    dwd_settlement_head ||--o{ dwd_store_goods_sale : "order_settle_id"
    dwd_settlement_head ||--o{ dwd_assistant_service_log : "order_settle_id"
    dwd_settlement_head ||--o{ dwd_platform_coupon_redemption : "order_settle_id"
    dwd_settlement_head ||--o{ dwd_refund : "order_settle_id"

    %% 台桌域
    dim_table ||--o{ dwd_table_fee_log : "table_id"
    dwd_table_fee_log ||--o{ dwd_table_fee_adjust : "table_fee_log_id"

    %% 助教域（作废判断已内聚到 dwd_assistant_service_log_ex.is_trash）
    dim_assistant ||--o{ dwd_assistant_service_log : "assistant_id"

    %% 会员域
    dim_member ||--o{ dim_member_card_account : "member_id"
    dim_member ||--o{ dwd_member_balance_change : "member_id"
    dim_member_card_account ||--o{ dwd_recharge_order : "tenant_member_card_id"

    %% 团购域
    dim_groupbuy_package ||--o{ dwd_groupbuy_redemption : "groupbuy_package_id"

    %% 商品域
    dim_goods_category ||--o{ dim_tenant_goods : "category_id"
    dim_tenant_goods ||--o{ dim_store_goods : "tenant_goods_id"
    dim_store_goods ||--o{ dwd_store_goods_sale : "site_goods_id"
    dim_store_goods ||--o{ dwd_goods_stock_summary : "site_goods_id"
    dim_store_goods ||--o{ dwd_goods_stock_movement : "site_goods_id"

    %% 门店维度
    dim_site ||--o{ dwd_settlement_head : "site_id"
```

### 业务域与表映射

| 业务域 | 事实表 | 维度表 | 核心关联 |
|--------|--------|--------|----------|
| 结算 | `dwd_settlement_head`(+ex), `dwd_payment`, `dwd_refund`(+ex) | — | 结算单是所有消费的汇总入口 |
| 台桌 | `dwd_table_fee_log`(+ex), `dwd_table_fee_adjust`(+ex) | `dim_table`(+ex) | 台费计费流水 → 台费调整 |
| 助教 | `dwd_assistant_service_log`(+ex) | `dim_assistant`(+ex) | 助教服务流水（作废通过 `_ex.is_trash` 判断） |
| 会员 | `dwd_member_balance_change`(+ex), `dwd_recharge_order`(+ex) | `dim_member`(+ex), `dim_member_card_account`(+ex) | 充值 → 余额变动 |
| 团购 | `dwd_groupbuy_redemption`(+ex), `dwd_platform_coupon_redemption`(+ex) | `dim_groupbuy_package`(+ex) | 团购核销 → 平台券核销 |
| 商品 | `dwd_store_goods_sale`(+ex) | `dim_tenant_goods`(+ex), `dim_store_goods`(+ex), `dim_goods_category` | 商品销售流水 |
| 库存 | `dwd_goods_stock_summary`, `dwd_goods_stock_movement` | （复用商品域维度表） | 库存汇总 + 变动流水 |

### 5 份文档的数据依赖关系

```mermaid
flowchart TD
    DOC1["文档1: DWD 表结构与字段语义总览<br/>覆盖全部 43 张表"]

    DOC1 --> DOC2["文档2: 业务全景<br/>消费产生机制"]
    DOC1 --> DOC3["文档3: 账务全景<br/>结算与支付流水"]
    DOC1 --> DOC4["文档4: 财务全景<br/>收入确认与对账"]
    DOC1 --> DOC5["文档5: 维度表与主数据全景<br/>全部维度表"]

    DOC2 -->|消费构成| DOC3
    DOC2 -->|消费金额| DOC4
    DOC3 -->|支付渠道| DOC4
    DOC5 -->|维度关联| DOC2
    DOC5 -->|维度关联| DOC3
```

### 文档产出路径与文件名

| 序号 | 文档 | 文件名 | 路径 |
|------|------|--------|------|
| 1 | DWD 表结构与字段语义总览 | `dwd-table-structure-overview.md` | `docs/reports/` |
| 2 | 业务全景：消费产生机制 | `dwd-business-panorama.md` | `docs/reports/` |
| 3 | 账务全景：结算与支付流水 | `dwd-accounting-panorama.md` | `docs/reports/` |
| 4 | 财务全景：收入确认与对账 | `dwd-financial-panorama.md` | `docs/reports/` |
| 5 | 维度表与主数据全景 | `dwd-dimension-panorama.md` | `docs/reports/` |

### 文档间引用规范

- 文档间使用相对路径引用：`[表结构总览](./dwd-table-structure-overview.md#table_name)`
- 引用已有分析报告：`[消费金额口径分析](./consume-money-caliber-deep-analysis.md#章节名)`
- 引用 BD 手册：`[BD 手册](../../apps/etl/connectors/feiqiu/docs/database/DWD/main/BD_manual_xxx.md)`
- 引用结论时标注验证状态和来源文档


## 正确性属性

*属性（Property）是一种在系统所有合法执行中都应成立的特征或行为——本质上是对系统应做什么的形式化陈述。属性是人类可读规格与机器可验证正确性保证之间的桥梁。*

### Property 1: DWD 表覆盖完整性

*对于* DWD schema（`test_etl_feiqiu.dwd`）中 `information_schema.tables` 返回的任意表，产出的文档集合中必须包含对该表的描述段落（表名出现在某份文档的标题或表格中）。

**Validates: Requirements 1.1, 5.1**

### Property 2: 主键标注准确性

*对于* DWD schema 中的任意表，文档中记录的主键字段集合必须与 `information_schema.table_constraints` + `key_column_usage` 查询返回的实际主键约束一致。

**Validates: Requirements 1.5**

### Property 3: 业务环节数据佐证

*对于* 业务全景文档（文档2）中描述的任意业务环节段落，该段落必须包含至少一个来自测试库的数据样例（以代码块或表格形式呈现的查询结果）。

**Validates: Requirements 2.6**

### Property 4: 对账公式验证一致性

*对于* 账务全景文档（文档3）中列出的任意对账公式，文档中标注的成立率和例外数量必须与在 `test_etl_feiqiu` 全量数据上执行该公式验证 SQL 的实际结果一致。

**Validates: Requirements 3.5, 6.2**

### Property 5: 文档元数据完整性

*对于* 产出的任意全景文档，文档开头必须包含：(a) 数据来源标注（`test_etl_feiqiu`）、(b) 验证日期、(c) 数据时间范围（最早和最晚记录的时间）。

**Validates: Requirements 6.4**

### Property 6: 文档输出路径正确性

*对于* 本次梳理产出的任意文档文件，其路径必须位于 `docs/reports/` 目录下。

**Validates: Requirements 7.1**

### Property 7: 文档模板一致性

*对于* 产出的任意全景文档，其结构必须包含以下模板元素：标题、数据来源与验证日期块、目录、正文、附录（含验证 SQL 或数据样例）。

**Validates: Requirements 7.3**

### Property 8: 内部链接格式

*对于* 产出文档中的任意内部链接（指向本项目其他 markdown 文件的链接），链接必须使用相对路径格式（以 `./` 或 `../` 开头），且目标文件实际存在。

**Validates: Requirements 7.5**


## 错误处理

本任务是纯文档梳理，不涉及运行时代码。"错误"主要指梳理过程中遇到的数据异常和验证失败。

### 数据异常处理策略

| 异常场景 | 处理方式 | 文档标注 |
|----------|----------|----------|
| 表无数据（0 行） | 跳过字段语义验证，仅记录表结构 | `❌ 未验证：表无数据` |
| 数据量不足（<10 行） | 执行有限验证，标注样本量不足 | `⚠️ 部分验证：仅 N 行数据` |
| 字段全 NULL | 标记为空字段，不展开分析 | 附录中列出字段名 |
| 对账公式不成立 | 分析例外案例，量化影响范围 | `⚠️ 成立率 X%，例外 N 笔` |
| 交叉验证矛盾 | 记录矛盾细节，标注为不确定 | `⚠️ 警告：无法对齐` |
| 现有文档与数据不一致 | 以数据为准，记录偏差 | `偏差记录` 段落 |

### 不确定性升级机制

当遇到以下情况时，必须在文档中以 `⚠️ 警告` 醒目标记：

1. 经过 ≥3 次不同角度的交叉验证仍无法确认的字段含义
2. 对账公式成立率 < 95% 且无法归因的例外
3. 金额字段的计算关系无法通过任何已知公式解释
4. 枚举值在现有文档中未记录且无法通过数据推断含义

警告内容须包含：已尝试的验证方法、无法确认的具体原因、建议的后续验证方向。

## 测试策略

### 测试方法说明

本任务的产出物是 markdown 文档而非代码，因此传统的单元测试和属性测试需要适配为"文档正确性验证"。

### 属性测试（Property-Based Testing）

使用 Python + `hypothesis` 库，针对设计文档中定义的正确性属性编写验证脚本。

**测试库**：`hypothesis`（项目已有，见 `tests/` 目录）
**最低迭代次数**：100 次（对于涉及随机采样的属性）
**测试位置**：`tests/` 目录（Monorepo 级属性测试）

#### 属性测试实现方案

| Property | 测试方法 | 实现思路 |
|----------|----------|----------|
| P1: 表覆盖完整性 | 查询 information_schema → 解析文档 → 比对 | 从数据库获取全部 DWD 表名，解析 5 份文档提取提及的表名，验证覆盖率 = 100% |
| P2: 主键标注准确性 | 查询 PK 约束 → 解析文档 → 比对 | 对于随机采样的 N 张表，比对文档中的主键与数据库实际主键 |
| P3: 业务环节数据佐证 | 解析文档段落 → 检查数据样例 | 解析业务全景文档的每个业务环节段落，验证包含代码块或数据表格 |
| P4: 对账公式验证一致性 | 提取公式 → 执行 SQL → 比对成立率 | 对于文档中的每个对账公式，重新执行验证 SQL，比对成立率 |
| P5: 文档元数据完整性 | 解析文档头部 → 检查必要字段 | 对于每份文档，检查开头是否包含数据来源、验证日期、时间范围 |
| P6: 文档路径正确性 | 列出产出文件 → 检查路径 | 验证所有产出文件位于 `docs/reports/` |
| P7: 文档模板一致性 | 解析文档结构 → 检查模板元素 | 对于每份文档，检查是否包含标题、元数据块、目录、正文、附录 |
| P8: 内部链接格式 | 正则提取链接 → 检查格式和目标 | 提取所有 markdown 链接，验证使用相对路径且目标文件存在 |

#### 属性测试标签格式

每个属性测试必须包含注释标签：

```python
# Feature: dwd-business-panorama, Property 1: DWD 表覆盖完整性
# Feature: dwd-business-panorama, Property 2: 主键标注准确性
# ...
```

### 单元测试（示例测试）

针对 prework 中标记为 `yes - example` 的验收标准：

| 验收标准 | 测试内容 |
|----------|----------|
| 1.6 SCD2 字段标注 | 检查 `dim_member` 文档中是否标注了 `scd2_start_time`, `scd2_end_time`, `scd2_is_current`, `scd2_version` |
| 2.2 消费类目覆盖 | 检查业务全景文档中是否包含"台费"、"商品消费"、"助教服务"、"灯控电费"四个关键词 |
| 2.5 团购三层价格 | 检查文档中是否提及 `sale_price`、`pl_coupon_sale_amount`、`coupon_amount` |
| 2.7 Mermaid 流程图 | 检查业务全景文档中是否包含 ` ```mermaid` 代码块 |
| 3.7 consume_money 三种口径 | 检查文档中是否包含口径 A、B、C 的描述 |
| 4.5 对账矩阵 | 检查财务全景文档中是否包含矩阵格式的表格 |
| 7.2 5 份文档产出 | 检查 `docs/reports/` 下是否存在 5 个指定文件名 |
| 7.4 Mermaid 图表 | 检查每份文档中是否包含至少一个 Mermaid 代码块 |

### 边界条件测试

| 验收标准 | 边界场景 |
|----------|----------|
| 1.7 表无数据 | 验证无数据表在文档中有"数据不足"标注 |

### 测试执行

```bash
# 属性测试（Monorepo 级）
cd C:\NeoZQYY && pytest tests/test_dwd_panorama_properties.py -v

# 单元测试
cd C:\NeoZQYY && pytest tests/test_dwd_panorama_examples.py -v
```