维度建模中销售与退款关联多事实表的设计方案咨询

场景背景 #

OLTP交易系统将销售正向交易、退款/取消逆向交易拆分存储在两张独立物理表中：

• sale：销售成交表，存储正向交易记录
• refund：退款/取消表，每笔记录必须关联一条已存在的sale记录作为冲抵对象
  两张表的公共维度字段基本对齐，覆盖交易时间、门店、销售人员、POS终端等属性，基础表结构如下：

CREATE TABLE sale
(
    sale_id uuid,
    transaction_at timestamp with time zone,
    store_id uuid,
    clerk_id uuid,
    clerk_number bigint,
    currency character varying(3),
    pos_id uuid,
    total numeric,
    net_total numeric
);

CREATE TABLE refund
(
    refund_id uuid,
    sale_id uuid, -- 关联待冲抵的原销售记录
    refunded_at timestamp with time zone,
    pos_id uuid,
    clerk_id uuid,
    clerk_number bigint
)

核心分析需求包括三类：

• 按任意公共维度统计净销售额（毛销售额扣减退款额）
• 按任意公共维度统计退款总金额、退款率等逆向交易指标
• 支撑灵活的ad-hoc业务探查需求

已有思路的问题判断 #

你之前判断“两张表对应两个独立业务流程、应设计为独立事实表”的结论完全正确，OLTP侧的范式化设计符合交易系统要求，但接入数仓时不需要为了计算净销售额额外构建独立的net_sale事实表——你提到的两个缺陷确实成立：

• 无退款的销售占总交易量的99%左右，单独建净销售事实表会产生极高的数据冗余
• “净销售”是聚合计算的派生指标，不存在对应的实体业务事件，不符合Kimball维度建模中事实表对应真实业务过程的核心原则

落地建模方案 #

在你初步设计的双事实表方案基础上做两处优化即可，完全覆盖所有分析场景，同时兼顾性能、灵活性和存储成本：

1. 明细层（DWD）双事务事实表设计 #

保留销售、退款两张独立的事务型事实表，不要在明细层做两表JOIN合并：

• 销售事实表：直接从OLTPsale表抽取数据，关联对应维度表获取维度外键，total/net_total等金额度量保持正值存储
• 退款事实表：从OLTPrefund表抽取数据时，关联原sale表冗余必要字段，同时保留两套维度属性：
  • 退款事件自身属性：退款时间、退款操作店员、退款发生门店、退款操作POS机
  • 原销售事件属性：原销售时间、原销售店员、原销售门店、原销售POS机
  • 度量值：直接取原销售记录的total/net_total值，统一记为负值，和销售事实表的金额口径完全对齐

这里不要单独设计refund_amount类的正向度量字段，逆向交易金额记负是数仓处理冲抵类场景的通用做法，后续聚合时直接对金额字段求和即可得到净值，能避免大量跨表扣减的逻辑bug。

2. 汇总层（DWS）统一聚合逻辑 #

不要让报表层直接关联两张明细事实表计算指标，把公共聚合逻辑下沉到数仓汇总层：

• 对于按原销售维度统计的场景（比如原销售店员的净业绩、某批次销售的退款率）：将两张事实表按原销售维度的字段对齐，做UNION ALL合并后按维度预聚合，直接sum金额字段得到毛销售额、退款额、净销售额
• 对于按退款事件维度统计的场景（比如退款操作员的工作量、退款发生时段的退款趋势）：直接基于退款事实表的退款维度字段预聚合对应指标即可

方案优势 #

• 符合建模规范：两张事实表分别对应销售成交、退款办理两个真实业务过程，没有人为创造虚拟业务过程对应的事实表
• 查询性能高：常用指标预聚合后，报表查询直接读取汇总表结果，不需要每次实时关联两张大表做计算
• 分析灵活性强：双维度属性的设计避免了维度错位问题——既不会把上月销售本月产生的退款算到本月销售业绩里，也不会把店员A开单、店员B处理退款的交易算到店员B的销售业绩里，能覆盖所有维度组合的分析需求
• 存储成本低：仅在占总数据量1%左右的退款事实表做字段冗余，不会产生大量无效存储
  不要为了减少查询时的表关联强行合并明细事实，也不要为了单个派生指标构建高冗余的单表，维度建模的核心是明细层对齐业务过程，公共计算逻辑统一在汇总层实现即可。

内容的提问来源于stack exchange，提问作者filpa