Snowflake使用lateral flatten合并数组时出现重复主键如何解决？

本次主键重复的核心逻辑明确：nextval('序列名') 会在SELECT执行阶段逐行触发取值，你当前的流程是先给staging层包含数组的单行数据生成主键，再执行LATERAL FLATTEN打平数组，原本的1行变为N行后，同一个主键会被复制到N行上，插入维度表时自然触发唯一性冲突。

• 方案1：调整主键生成时机，放到LATERAL FLATTEN执行之后
  无需修改整体架构，仅需把主键生成逻辑从打平数组前置为打平后执行即可，示例逻辑如下：

  SELECT 
    nextval('你的维度表主键序列') AS dim_id,
    f.value AS 数组打平后的业务字段,
    -- 其他需要导入维度表的字段
  FROM staging表 s,
  LATERAL FLATTEN(input => s.待打平的数组字段) f
  

  该方案下打平后的每一行都会单独触发nextval取唯一值，不会出现重复主键。

• 方案2：维度表侧自生成主键
  如果你不想在ETL流程中处理序列调用，也可以将维度表的主键列设置为自增列（IDENTITY属性），merge时不需要给主键列传值，Snowflake会自动给每一条新插入的行生成唯一主键。
  维度表建表参考逻辑：

  CREATE OR REPLACE TABLE 维度表 (
    dim_id NUMBER IDENTITY START = 1 INCREMENT = 1 PRIMARY KEY,
    业务字段1 数据类型,
    业务字段2 数据类型
    -- 其他字段定义
  );
  

  该方案下merge语句只需要处理业务字段的匹配逻辑即可，完全规避主键重复问题。

• 如果维度表主键没有和其他下游系统做强绑定，优先选方案2，后续不管你打平多少层数组、新增什么数据源导入维度表，都不需要再关心主键生成逻辑，降低出错概率。
• 如果你的ETL链路中有其他逻辑需要提前拿到维度主键做关联，就选方案1，仅调整序列调用的位置就能快速解决问题。

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