在事实表中使用EFFECTIVE_DT/EXPIRATION_DT的维度键同步难题求助

需构建存储员工授予股份数量（granted_share_qty）的事实表，关联SPS Grant_dim、SPS Plan Dim、SPS Client Dim、SPS Customer Dim等Type 2维度表，要求：

• 事实表包含各维度的DimKeys（代理键）和DurableKeys（持久键）
• 支持「截至指定日期」查询，获取对应时点的granted_share_qty及维度属性值
• 因数据量超1亿且变更少，放弃每日快照方案，采用带EFFECTIVE_DT和EXPIRATION_DT的时间区间快照模式
• 核心挑战：维度表支持日内多次近实时版本更新时，需同步更新事实表的DimKeys（即使事实表无业务数据变更），且禁止采用「移除DimKeys、用DurableKeys+日期直接关联维度表」的方案

解决思路 #

1. Snowflake流+任务实现近实时维度变更捕获与事实表同步 #

• 为每个Type 2维度表创建流（Stream），捕获维度版本的新增/更新事件
• 针对每个维度流创建定时任务（Task），频率设置为适配日内近实时变更的粒度（如5分钟一次，可根据业务调整）
• 任务执行逻辑：
  1. 从流中提取变更的维度记录，获取DurableKey、新DimKey、EFFECTIVE_DT、EXPIRATION_DT
  2. 关联事实表中对应DurableKey且当前EXPIRATION_DT为最大值（即生效中）的记录
  3. 将匹配到的事实表记录的EXPIRATION_DT更新为维度新版本EFFECTIVE_DT减1秒（保证时间区间无重叠）
  4. 插入新的事实表记录：复制原记录的业务字段（如granted_share_qty），替换为新的DimKey，设置EFFECTIVE_DT为维度新版本生效日期，EXPIRATION_DT设为9999-12-31
• 依赖Snowflake事务保证更新+插入操作的原子性，避免数据不一致

2. 事实表维度键的独立版本化存储，降低同步开销 #

• 为事实表的每个维度键单独维护时间区间字段，比如新增GRANT_DIM_KEY、GRANT_EFF_DT、GRANT_EXP_DT，PLAN_DIM_KEY、PLAN_EFF_DT、PLAN_EXP_DT等（替代全局的EFFECTIVE_DT/EXPIRATION_DT）
• 当某一维度发生版本变更时，仅需更新对应维度的键和时间区间字段，无需复制整行事实记录，减少存储和写入成本
• 批量同步逻辑：
  1. 日内定时拉取所有维度表的版本变更记录，按DurableKey和生效时间分组
  2. 用Merge语句关联事实表与维度表的DurableKey，匹配事实表中对应维度键生效区间覆盖维度新版本生效时间的记录
  3. 拆分原事实记录的时间区间：保留[原EFF, 新EFF-1]的旧版本记录，插入[新EFF, 原EXP]的新版本记录并替换为新DimKey

3. 预计算维度键映射表，集中处理变更逻辑 #

• 创建中间映射表DIM_KEY_VERSION_MAP，结构包含：DIM_TYPE（如GRANT/PLAN/CLIENT/CUSTOMER）、DURABLE_KEY、DIM_KEY、EFFECTIVE_DT、EXPIRATION_DT
• 用流+任务实时更新该映射表，确保其始终同步所有维度的最新版本信息
• 事实表同步时直接关联该映射表：
  1. 提取映射表中最近变更的记录，按DIM_TYPE和DURABLE_KEY分组
  2. 关联事实表中对应DurableKey且维度键生效区间与映射表新区间重叠的记录
  3. 执行区间拆分和DimKey更新操作，保证事实表维度键版本与映射表一致
• 该方式将维度变更捕获逻辑集中，避免为每个维度单独编写任务，降低维护复杂度

4. 查询层兼容逻辑，降低同步延迟影响 #

• 若事实表DimKey同步存在短暂延迟，可通过以下查询逻辑临时兼容（核心仍需保证事实表同步，此为过渡方案）：

SELECT 
    f.granted_share_qty,
    gd.*, pd.*, cd.*, custd.*
FROM FACT_EMPLOYEE_GRANT f
JOIN SPS_GRANT_DIM gd 
    ON f.GRANT_DURABLE_KEY = gd.DURABLE_KEY 
    AND @specified_date BETWEEN gd.EFFECTIVE_DT AND gd.EXPIRATION_DT
    AND (f.GRANT_DIM_KEY = gd.DIM_KEY OR f.EXPIRATION_DT >= @specified_date)
-- 其他维度表关联逻辑与上述一致
WHERE @specified_date BETWEEN f.EFFECTIVE_DT AND f.EXPIRATION_DT

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