同步SOURCE到TARGET时，如何避免Snowflake序列无意义执行减少ID间隙？

场景与问题 #

需要将SOURCE表的新数据同步到TARGET表，ID分配逻辑如下：

• 更新数据时，复用TARGET表中已有的id
• 新增数据时，通过Snowflake Sequence生成新id

当前使用的代码片段：

select case when ep.id is not null then ep.id else sequence.nextval
...
from SOURCE p left join TARGET ep  on p.otherKey = ep.otherKey;

问题在于：不管是更新还是新增操作，序列都会被执行。由于更新操作远多于新增记录，导致ID间隙过大，需要保留同一序列但避免不必要的调用。

解决方案 #

方案1：拆分更新与插入操作（推荐）

把同步逻辑拆成两个独立步骤，仅在插入新增数据时调用序列：

1. 更新已有数据：直接匹配otherKey更新TARGET的字段，完全不涉及序列
2. 插入新增数据：仅对SOURCE中不存在于TARGET的记录生成新ID

示例代码：

-- 第一步：更新已有数据
UPDATE TARGET ep
SET col1 = p.col1, col2 = p.col2 -- 替换为实际需要更新的字段列表
FROM SOURCE p
WHERE p.otherKey = ep.otherKey;

-- 第二步：插入新增数据
INSERT INTO TARGET (id, otherKey, col1, col2)
SELECT sequence.nextval, p.otherKey, p.col1, p.col2
FROM SOURCE p
LEFT JOIN TARGET ep ON p.otherKey = ep.otherKey
WHERE ep.id IS NULL;

这种方式彻底避免了更新场景下的序列调用，从根源解决ID间隙问题，同时逻辑清晰、性能更优。

方案2：单语句过滤新增记录再调用序列

如果必须用单条语句实现，可以先筛选出需要新增的记录，再针对性调用序列：

SELECT 
    COALESCE(ep.id, sequence.nextval)
    , p.otherKey
    , p.col1
    , p.col2 -- 替换为实际字段
FROM SOURCE p
LEFT JOIN TARGET ep ON p.otherKey = ep.otherKey
WHERE ep.id IS NULL
UNION ALL
SELECT 
    ep.id
    , p.otherKey
    , p.col1
    , p.col2
FROM SOURCE p
INNER JOIN TARGET ep ON p.otherKey = ep.otherKey;

通过UNION ALL拆分新增和更新的记录集，仅在新增分支调用序列，更新分支直接复用已有ID。

内容的提问来源于stack exchange，提问作者bogdan.rusu