如何为多个 data sources 设计统一的 SCD 维度

2026-10-24支付阿杰

结论

应建立统一的符合维度（conformed dimension）DimLocation，不要分别维护 Ordering Location 和 Execution Location 两套 SCD。

设计时需要处理好三件事：

使用独立的企业级 Location 标识，将两个系统的 business key 映射到同一个 Location。
为每个属性指定权威来源，不能由数据加载的先后顺序决定最终值。
先合并两个数据源的最新状态，再对合并后的完整维度记录执行 SCD Type 2。

如果两个系统都提供 Location Name，需要制定固定的处理规则。可以指定一个权威来源，也可以保留两个源值，或者通过主数据管理流程生成标准名称，但不能按照 ETL 到达顺序相互覆盖。

source_system	source_location_bk	enterprise_location_id
ORDERING	LOC-1008	501
EXECUTION	SITE-778	501

明确属性的所有权

每个属性都应配置权威来源和 SCD 策略。例如：

属性	权威来源	建议策略
Billing information	Ordering System	Type 2 或 Type 1，取决于是否需要历史
Latitude / Longitude	Execution System	通常 Type 2
Ordering Location Name	Ordering System	保留源值
Execution Location Name	Execution System	保留源值
Canonical Location Name	明确的优先级或 MDM	Type 2 或 Type 1

对于重复的 Location Name 属性，通常有三种处理方式。

指定唯一权威来源

例如，规定 Ordering System 是 Location Name 的主数据源：

location_name = ordering_location_name

Execution System 中的名称只用于数据质量检查，不参与覆盖。

这种方式最简单，但前提是 Ordering System 确实拥有名称的业务所有权。

保留两个源值，同时生成标准值

ordering_location_name
execution_location_name
location_name

标准名称可以按照固定优先级生成：

COALESCE(
    NULLIF(TRIM(ordering_location_name), ''),
    NULLIF(TRIM(execution_location_name), ''),
    'Unknown'
)

保留两个源值，更便于排查名称不一致的问题。

拆分语义不同的属性

如果两个名称表达的含义不同，例如一个是账单名称，另一个是现场名称，就不应强行合并：

billing_location_name
execution_site_name

这种情况下，它们不是重复属性，而是两个独立的业务属性。

SCD 的处理流程

不要让 Ordering 和 Execution 的加载任务分别直接修改 dim_location。两个任务可能互相覆盖，也可能在同一个批次中产生多余的维度版本。

可以采用以下流程：

分别将两个系统的变更写入 staging。
通过 location_key_xref 转换为统一的 enterprise_location_id。
更新每个来源的当前状态表。
为受影响的 Location 重新生成完整的 canonical record。
按照属性所有权规则处理重复属性。
将 canonical record 与当前维度版本比较。
如果 Type 2 属性发生变化，关闭旧版本并插入新版本。
如果只有 Type 1 属性发生变化，直接更新当前版本。
记录来源冲突和无法映射的 business key。

可以维护一张来源状态表：

CREATE TABLE location_source_snapshot (
    source_system          VARCHAR(30) NOT NULL,
    enterprise_location_id BIGINT      NOT NULL,
    source_location_bk     VARCHAR(100) NOT NULL,
    location_name          VARCHAR(200),
    billing_address        VARCHAR(500),
    billing_account        VARCHAR(100),
    latitude               DECIMAL(9,6),
    longitude              DECIMAL(9,6),
    source_modified_at     TIMESTAMP,
    PRIMARY KEY (source_system, enterprise_location_id)
);

这样，无论哪个系统发生变化，都可以将它的新状态与另一个系统已经保存的最新状态重新组合，不必等待两个系统同时发送完整数据。

合并两个来源

下面的 SQL 展示了 canonical record 的构造方式。具体函数需要根据数据库方言调整。

SELECT
    x.enterprise_location_id,

    o.location_name AS ordering_location_name,
    e.location_name AS execution_location_name,

    COALESCE(
        NULLIF(TRIM(o.location_name), ''),
        NULLIF(TRIM(e.location_name), ''),
        'Unknown'
    ) AS location_name,

    o.billing_address,
    o.billing_account,
    e.latitude,
    e.longitude

FROM affected_locations x

LEFT JOIN location_source_snapshot o
    ON o.enterprise_location_id = x.enterprise_location_id
   AND o.source_system = 'ORDERING'

LEFT JOIN location_source_snapshot e
    ON e.enterprise_location_id = x.enterprise_location_id
   AND e.source_system = 'EXECUTION';

这里的 COALESCE 表示 Ordering System 对标准名称具有更高优先级。如果实际的权威来源不同，就应明确调整规则，不能依赖哪张表先加载。

执行 SCD Type 2

可以先为需要保留历史的属性计算哈希：

SHA2(
    CONCAT_WS(
        '|',
        COALESCE(location_name, '<NULL>'),
        COALESCE(billing_address, '<NULL>'),
        COALESCE(billing_account, '<NULL>'),
        COALESCE(CAST(latitude AS VARCHAR(30)), '<NULL>'),
        COALESCE(CAST(longitude AS VARCHAR(30)), '<NULL>')
    ),
    256
)

然后在一个事务中关闭旧版本并插入新版本：

BEGIN;

UPDATE dim_location d
SET
    effective_to = :change_time,
    is_current = 'N'
WHERE d.enterprise_location_id = :enterprise_location_id
  AND d.is_current = 'Y'
  AND d.tracked_hash <> :new_tracked_hash;

INSERT INTO dim_location (
    location_sk,
    enterprise_location_id,
    location_name,
    ordering_location_name,
    execution_location_name,
    billing_address,
    billing_account,
    latitude,
    longitude,
    effective_from,
    effective_to,
    is_current,
    tracked_hash
)
SELECT
    :new_location_sk,
    :enterprise_location_id,
    :location_name,
    :ordering_location_name,
    :execution_location_name,
    :billing_address,
    :billing_account,
    :latitude,
    :longitude,
    :change_time,
    TIMESTAMP '9999-12-31 00:00:00',
    'Y',
    :new_tracked_hash
WHERE NOT EXISTS (
    SELECT 1
    FROM dim_location d
    WHERE d.enterprise_location_id = :enterprise_location_id
      AND d.is_current = 'Y'
      AND d.tracked_hash = :new_tracked_hash
);

COMMIT;

实际实现中还应设置唯一约束或使用适当的事务隔离，保证每个 enterprise_location_id 最多只有一个当前版本。

如果采用半开区间，建议统一使用：

effective_from <= event_time
AND event_time < effective_to

这样可以避免版本切换时出现时间重叠。

事实表如何选择维度版本

加载事实时，先将来源 business key 转换为 enterprise_location_id，再根据业务事件时间查找当时有效的维度版本：

SELECT d.location_sk
FROM location_key_xref x
JOIN dim_location d
  ON d.enterprise_location_id = x.enterprise_location_id
WHERE x.source_system = :source_system
  AND x.source_location_bk = :source_location_bk
  AND :event_time >= x.valid_from
  AND :event_time < COALESCE(
        x.valid_to,
        TIMESTAMP '9999-12-31 00:00:00'
      )
  AND :event_time >= d.effective_from
  AND :event_time < d.effective_to;

这里应使用订单发生时间、执行发生时间，或者由事实粒度定义的其他业务时间，而不是 ETL 加载时间。具体使用哪个时间，取决于该 fact 表示的是订单、执行结果，还是两者组合后的业务过程。

需要特别处理的问题

两个来源在同一批次都发生变化

先更新两个来源的 snapshot，再生成一次 canonical record。不要先根据 Ordering 生成一个版本，然后又根据 Execution 生成第二个版本，否则会产生没有业务意义的中间版本。

延迟到达的数据

至少要保留两个时间概念：

source_modified_at：来源系统认为变更发生的时间。
load_timestamp：数据进入仓库的时间。

如果来源能够提供可靠的业务生效时间，可以回补历史区间。否则，只能按照仓库发现变更的时间创建新版本，不能根据加载顺序推断真实的业务生效时间。

尚未建立 ESB 映射

不要猜测两个 Location 是否相同。可以使用固定的 Unknown 维度成员，或者创建待解析成员，并将记录写入异常队列。映射补齐后，再按照既定的迟到维度策略修正事实外键。

一个系统删除 Location

需要先确定删除的具体含义。它可能表示：

Location 在整个企业中已经失效；
仅在该来源系统中不再使用；
来源数据被物理清理，但业务实体仍然存在。

通常只有第一种情况需要关闭统一维度成员。第二种情况更适合维护来源级状态，例如 ordering_active_flag 和 execution_active_flag。

Type 1 与 Type 2 不要混在同一个哈希中

只有需要产生历史版本的属性才应写入 tracked_hash。例如，只用于纠正拼写且不要求追踪历史的属性，应按 Type 1 更新。否则，一次普通的文本修正也会生成新的维度版本。

其他可行设计

如果两个系统中的 Location 并非完全相同的业务概念，可以建立两个维度：

DimBillingLocation
DimExecutionLocation

事实表分别引用两个外键。这种设计适用于账单地点和实际执行地点可能不同，并且拥有不同生命周期或不同粒度的场景。

如果企业已经有 MDM 系统，也可以由 MDM 提供统一的 Location ID、标准名称和属性权威规则，数据仓库只负责保存历史版本。

对于当前场景，只要两个 business key 确实表示同一个业务地点，最符合 Kimball 思路的方案仍是使用统一的符合维度，持久化 business key 映射，明确属性所有权，并在合并后的维度状态上执行一次 SCD 处理。