如何在Redshift中实现符合自定义规则的SCD Type 2批量数据处理

背景需求 #

现有全量批次数据，以creation_time列标识记录的生成先后顺序，需要对数据做处理后落地SCD Type 2逻辑，额外维护活跃时间、非活跃时间两列。

原始数据 #

creation_time单位为毫秒，具体数据如下：

key data    creation_time (单位：毫秒)
k1  abc     2021-09-16 14:17:28.447000
k1  abcd    2021-09-16 18:50:42.676000
k1  abc     2021-09-16 18:50:42.853000
k1  abcd    2021-09-16 18:50:43.141000
k1  abc     2021-09-16 18:50:43.809000
k1  abcd    2021-09-16 18:50:44.288000
k1  abc     2021-09-16 18:50:44.854000

处理规则 #

• 所有传入数据先按creation_time从小到大排序
• 仅当新传入记录的data字段发生变更，且其active_dtmz（精确到秒）为递增状态时，才插入新版本记录，同时将前序同key的有效记录置为非活跃
• 核心要求：
  • active_dtmz仅保留秒级精度
  • 同一key下，data字段未发生变化的后续记录直接忽略

处理后输出样例 #

key data    active_dtmz             inactive_dtmz
k1  abc     2021-09-16 14:17:28     2021-09-16 18:50:42
k1  abcd    2021-09-16 18:50:42     2021-09-16 18:50:43
k1  abc     2021-09-16 18:50:43     2021-09-16 18:50:44
k1  abcd    2021-09-16 18:50:44     3001-01-01 00:00:00

实现步骤参考 #

1. 按key字段对数据做分组，每组内按照creation_time升序排序
2. 对每组内的所有记录，先将creation_time截断到秒级，得到候选active_dtmz
3. 逐行遍历组内记录，对比当前记录和上一条已保留记录的data值：如果data值相同直接跳过；如果data值不同且当前记录的候选active_dtmz大于上一条保留记录的active_dtmz，则将当前记录加入保留列表
4. 对保留列表内的记录，按顺序将前一条记录的inactive_dtmz设置为后一条记录的active_dtmz，最后一条记录的inactive_dtmz默认设置为3001-01-01 00:00:00，代表当前为有效活跃状态

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