Snowflake中替代Oracle ROWID实现更新识别的方案问询

Oracle中依赖ROWID跟踪行生命周期的逻辑无法直接迁移到Snowflake，核心原因是：Snowflake的ROWID是虚拟临时列，基于数据的物理存储位置，更新操作可能会改变行的存储位置导致ROWID变化，且它不具备持久化特性，无法作为行的唯一持久标识。针对无主键表的更新识别，推荐以下两种可行方案：

方案1：引入持久化唯一行标识（最可靠） #

给源表和目标表添加一个不变的唯一ID列，用来跟踪每一行的生命周期，即使行内容更新，这个ID也保持不变。

步骤1：创建持久化唯一ID #

• 给目标表添加IDENTITY自增列（或使用自定义序列）：

CREATE OR REPLACE TABLE EMPLOYEE_TGT (
    EMP_UNIQUE_ID NUMBER(19,0) IDENTITY(1,1) PRIMARY KEY, -- 自动生成唯一自增ID，永久不变
    CLMN_ID VARCHAR(50),
    CLMN_NUM VARCHAR(50),
    CLMN_EMP_NAME VARCHAR(100),
    CLMN_START_DATE DATE
);

• 源表加载时同步生成唯一ID：
  如果源数据本身没有唯一标识，在加载到Snowflake的EMPLOYEE_SRC时，用序列生成ID：

-- 先创建序列
CREATE OR REPLACE SEQUENCE EMP_SRC_SEQ START = 1 INCREMENT = 1;

-- 加载数据时生成ID
COPY INTO EMPLOYEE_SRC (EMP_UNIQUE_ID, CLMN_ID, CLMN_NUM, CLMN_EMP_NAME, CLMN_START_DATE)
FROM (
    SELECT EMP_SRC_SEQ.NEXTVAL, t.$1, t.$2, t.$3, t.$4
    FROM @EMPLOYEE_STAGE/employee_data.csv t -- 替换为你的外部阶段路径
);

步骤2：修改事务识别逻辑 #

用持久化的EMP_UNIQUE_ID替代原来的ROWID进行关联：

SELECT 
    a.EMP_UNIQUE_ID AS TGT_ROW_ID,
    a.CLMN_ID, a.CLMN_NUM, a.CLMN_EMP_NAME,
    b.EMP_UNIQUE_ID AS SRC_ROW_ID,
    b.CLMN_ID AS SRC_CLMN_ID, b.CLMN_NUM AS SRC_CLMN_NUM, b.CLMN_EMP_NAME AS SRC_CLMN_EMP_NAME,
    -- 识别事务类型
    IFF(a.EMP_UNIQUE_ID IS NULL, 'I',
        IFF(b.EMP_UNIQUE_ID IS NULL, 'D',
            IFF(
                a.CLMN_ID = b.CLMN_ID 
                AND a.CLMN_NUM = b.CLMN_NUM 
                AND a.CLMN_EMP_NAME = b.CLMN_EMP_NAME, 
                'IGNORE', 
                'U'
            )
        )
    ) AS TRANSACTION_FLAG
FROM EMPLOYEE_TGT a
FULL OUTER JOIN EMPLOYEE_SRC b ON a.EMP_UNIQUE_ID = b.EMP_UNIQUE_ID;

方案2：使用业务唯一键组合（仅适用于存在业务唯一标识的场景） #

如果源表中存在一组列的组合可以唯一标识行（即使没有主键约束），比如CLMN_ID + CLMN_NUM可以唯一区分员工，那么直接用这个组合作为关联键：

SELECT 
    a.CLMN_ID || '|' || a.CLMN_NUM AS TGT_KEY,
    a.CLMN_ID, a.CLMN_NUM, a.CLMN_EMP_NAME,
    b.CLMN_ID || '|' || b.CLMN_NUM AS SRC_KEY,
    b.CLMN_ID AS SRC_CLMN_ID, b.CLMN_NUM AS SRC_CLMN_NUM, b.CLMN_EMP_NAME AS SRC_CLMN_EMP_NAME,
    IFF(a.CLMN_ID IS NULL, 'I',
        IFF(b.CLMN_ID IS NULL, 'D',
            IFF(
                a.CLMN_EMP_NAME = b.CLMN_EMP_NAME 
                AND a.CLMN_START_DATE = b.CLMN_START_DATE, 
                'IGNORE', 
                'U'
            )
        )
    ) AS TRANSACTION_FLAG
FROM EMPLOYEE_TGT a
FULL OUTER JOIN EMPLOYEE_SRC b 
    ON a.CLMN_ID = b.CLMN_ID 
    AND a.CLMN_NUM = b.CLMN_NUM;

注意事项 #

• 哈希方法不适用：哈希值基于行内容生成，更新后内容变化会导致哈希值改变，无法关联原行，因此不能用来识别更新。
• 方案1是通用解决方案：无论是否存在业务唯一键，引入持久化唯一ID都能可靠跟踪行的生命周期，是Snowflake中替代Oracle ROWID逻辑的标准做法。

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