3. 数据仓库分层设计：ODS、DWD、DWS、ADS 各层职责与典型表结构设计原则

在采购支出数据仓库中，分层设计是保障数据可追溯性、降低ETL开发复杂度、提升查询性能的核心架构手段。每一层都有明确的职责边界和表结构约束，避免数据烟囱式开发。以下按照从源系统到最终应用的顺序，详细阐述各层职责与设计原则。

3.1 分层架构总览

采购支出数据仓库通常采用四层标准架构：ODS（操作数据存储层） → DWD（明细数据层） → DWS（汇总数据层） → ADS（应用数据层）。各层之间通过ETL任务单向流转，数据逐层收敛、聚合与主题化。

3.2 ODS 层：操作数据存储层

核心职责：作为源系统数据的"近镜像"缓冲区，以最少的转换将采购业务系统的原始数据接入数据仓库。ODS 层不进行业务逻辑清洗，仅做必要的格式统一（如字符集转换、时间戳规范化）。

3.2.1 典型表结构设计原则

• 字段级映射：表字段与源系统保持一一对应，字段名建议使用源系统原始字段名加前缀（如 src_）以标识来源。
• 增量标识：必须包含 ods_batch_id（批次号）、ods_load_time（加载时间戳）、ods_operation_flag（I/U/D 操作标识）三个技术字段。
• 分区策略：按日期分区（如 dt 字段），保留最近 30~90 天全量数据，历史数据归档至冷存储。
• 禁止聚合：ODS 层不允许做任何汇总、去重或业务过滤，保留源系统最细粒度记录。

3.2.2 采购支出 ODS 层典型表清单

3.3 DWD 层：明细数据层

核心职责：对 ODS 层数据进行清洗、标准化、维度退化与一致性处理，形成企业级一致的事实明细。DWD 层是数据仓库的"单一事实来源"（Single Source of Truth）。

3.3.1 典型表结构设计原则

• 退化维度：将常用的维度属性（如供应商名称、物料类别、采购组织）直接退化到事实表中，减少关联查询。
• 一致性度量：统一单位（如金额统一为人民币元、数量统一为基本计量单位）、统一货币汇率转换。
• 清洗规则落地：在 ETL 过程中执行空值填充、异常值标记、格式校验，并记录清洗日志到 dwd_data_quality_log 表。
• 缓慢变化维度（SCD）处理：对于供应商名称变更、付款条件调整等，采用 SCD Type 2 方式保留历史版本。

3.3.2 采购支出 DWD 层核心事实表设计

-- DWD 层价格方差事实表 DDL 示例（PostgreSQL）
CREATE TABLE dwd_price_variance_fact (
    variance_id          BIGINT PRIMARY KEY,
    po_number            VARCHAR(20) NOT NULL,
    line_num             INT NOT NULL,
    material_code        VARCHAR(30) NOT NULL,
    material_name        VARCHAR(200),          -- 退化维度
    vendor_code          VARCHAR(20) NOT NULL,
    vendor_name          VARCHAR(200),          -- 退化维度
    expected_unit_price  DECIMAL(18,4),
    actual_unit_price    DECIMAL(18,4),
    variance_amount      DECIMAL(18,4),
    variance_rate        DECIMAL(8,4),
    variance_reason      VARCHAR(100),
    dwd_load_time        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    dwd_batch_id         VARCHAR(50)
) PARTITION BY RANGE (dw_load_date);

3.4 DWS 层：汇总数据层

核心职责：基于 DWD 层明细数据，按分析主题构建轻度汇总宽表，减少重复计算，提升查询性能。DWS 层是数据仓库的"公共汇总层"，面向跨主题分析。

3.4.1 典型表结构设计原则

• 主题宽表：每个分析主题（如供应商分析、物料分析、采购组织分析）构建一张宽表，包含该主题下最常用的维度与度量。
• 预聚合粒度：通常按"天 + 维度组合"进行聚合，如 dt + vendor_code + material_code。
• 衍生指标：在 DWS 层计算同比、环比、累计值、移动平均等衍生指标，避免在报表层重复计算。
• 数据冗余：允许适度冗余，将多个事实表的度量合并到一张宽表中，减少报表 JOIN 操作。

3.4.2 采购支出 DWS 层典型宽表

-- DWS 供应商日汇总宽表 DDL 示例
CREATE TABLE dws_vendor_daily_agg (
    dt                  DATE NOT NULL,
    vendor_code         VARCHAR(20) NOT NULL,
    vendor_name         VARCHAR(200),
    risk_level          VARCHAR(10),
    po_count            INT DEFAULT 0,
    total_order_amt     DECIMAL(18,2) DEFAULT 0,
    total_receive_amt   DECIMAL(18,2) DEFAULT 0,
    total_invoice_amt   DECIMAL(18,2) DEFAULT 0,
    price_variance_count INT DEFAULT 0,
    avg_variance_rate   DECIMAL(8,4),
    dws_load_time       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (dt, vendor_code)
) PARTITION BY RANGE (dt);

3.5 ADS 层：应用数据层

核心职责：直接面向报表工具、BI 仪表板、数据接口和临时查询。ADS 层的数据结构完全按消费端需求定制，不要求遵循星型或雪花型范式。

3.5.1 典型表结构设计原则

• 需求驱动：每个 ADS 表对应一个具体的报表或接口需求，如"采购支出月度趋势报表"、"供应商价格方差排行榜"。
• 宽表扁平化：将 DWS 层数据与维度属性合并为扁平宽表，减少报表 SQL 复杂度。
• 预计算复杂指标：在 ADS 层完成排名、百分位、目标达成率等复杂计算。
• 数据生命周期：ADS 层数据保留周期较短（通常 1~3 年），且支持快速重建。

3.5.2 采购支出 ADS 层典型表

3.6 分层设计原则总结