如何用SQL基于SCD2新维度表更新事实表代理键

在数据仓库中更换了维度表，新维度表使用全新的代理键值，因此需要更新事实表中的所有历史数据，将原代理键替换为新代理键。以下是示例：

旧维度表 #

事实表 #

新维度表 #

为简化问题，假设活跃记录的name字段唯一。最终需要得到目标事实表：

新事实表 #

无SCD2时操作较简单，但需考虑日期范围时，如何用SQL实现该更新？

解决方案 #

核心逻辑是通过旧维度表与新维度表的业务属性+时间范围匹配，建立新旧代理键的映射关系，再用映射关系更新事实表。

1. 构建新旧代理键映射 #

先通过关联新旧维度表，锁定每个旧代理键对应的新代理键：

WITH key_mapping AS (
    SELECT 
        old_dim.id AS old_key,
        new_dim.id AS new_key
    FROM old_dimension old_dim
    JOIN new_dimension new_dim
        ON old_dim.name = new_dim.name
        AND old_dim.value = new_dim.value
        AND old_dim.start_date = new_dim.start_date
        AND old_dim.end_date = new_dim.end_date
)

通过业务属性（name、value）和SCD2的时间区间字段完全匹配，确保维度记录的一一对应。

2. 执行事实表更新 #

利用上面的映射表，替换事实表中的旧代理键：

UPDATE fact_table f
SET name_id = km.new_key
FROM key_mapping km
WHERE f.name_id = km.old_key;

3. 带时间校验的严谨更新（可选） #

如果需要验证事实表的日期确实落在对应维度记录的时间范围内（避免旧维度数据有误），可以加入事实表与旧维度的关联校验：

WITH fact_key_mapping AS (
    SELECT 
        f.name_id AS old_key,
        new_dim.id AS new_key
    FROM fact_table f
    JOIN old_dimension old_dim
        ON f.name_id = old_dim.id
        AND f.date BETWEEN old_dim.start_date AND old_dim.end_date
    JOIN new_dimension new_dim
        ON old_dim.name = new_dim.name
        AND old_dim.value = new_dim.value
        AND old_dim.start_date = new_dim.start_date
        AND old_dim.end_date = new_dim.end_date
)
UPDATE fact_table f
SET name_id = fkm.new_key
FROM fact_key_mapping fkm
WHERE f.name_id = fkm.old_key;

注意事项 #

• 确保日期字段格式统一，避免因格式差异导致时间匹配错误（比如将02.02.2023转换为标准日期类型）。
• 更新前先用SELECT验证映射关系，避免误操作：

SELECT f.date, f.name_id AS old_key, fkm.new_key
FROM fact_table f
JOIN fact_key_mapping fkm ON f.name_id = fkm.old_key;

• 针对大型事实表，建议分批更新，避免长时间锁表影响业务。

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