Effectivity Satellites填充及乱序变更事件处理与查询方案咨询

关于处理方式正确性的判断 #

该处理逻辑完全符合Data Vault 2.0体系下effectivity satellite（有效期附属表）的仅插入设计规范，没有逻辑错误。
仅插入模式的核心约束是不允许修改历史已写入的任何行，面对乱序到达的事件时，只能通过新增记录的方式修正历史有效期区间，出现同起始时间、不同结束时间的多条冗余记录是该模式下的正常现象，冗余数据由后续查询逻辑统一过滤即可，不需要修改写入逻辑。

指定时间点的雇佣关系查询方法 #

核心逻辑

由于仅插入模式下会存在冗余的有效期记录，查询前需要先对数据做一次清洗，规则为：同一个业务唯一键（本例为person_id + employee_id）的相同起始时间记录中，有效期结束时间最小的那条为真实生效记录，其余长区间的为过期冗余数据，可直接排除。
清洗完成后得到的所有区间无重叠，直接匹配查询时间点即可得到正确状态。

具体查询SQL示例（以通用SQL语法为例）

WITH cleaned_effectivity AS (
    -- 第一步清洗冗余记录，得到真实有效区间
    SELECT 
        person_id,
        employee_id,
        effectivity_start_date,
        MIN(effectivity_end_date) AS real_end_date
    FROM effectivity_sat_table
    GROUP BY person_id, employee_id, effectivity_start_date
)
-- 第二步匹配查询时间点
SELECT * 
FROM cleaned_effectivity
WHERE effectivity_start_date <= @query_time 
  AND real_end_date > @query_time

如果查询返回记录则代表该时间点雇佣关系有效，无返回则代表无效。

示例时间点查询结果

1. 时间点t1.5：清洗后的有效区间[t1, t2)满足匹配条件，返回记录，雇佣状态为有效
2. 时间点t2.5：无任何清洗后的区间满足匹配条件，无返回结果，雇佣状态为无效

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