如何通过Merge Query实现Type 2维度表记录员工历史数据？

1. 先确认维度表结构 #

首先创建符合Type 2要求的员工维度表，新增有效周期和活跃标识字段：

CREATE TABLE employee_dim (
    id INT,
    name VARCHAR(50),
    salary DECIMAL(10,2),
    status VARCHAR(20),
    start_date DATE,
    end_date DATE,
    active_flag CHAR(1) DEFAULT 'Y',
    PRIMARY KEY (id, start_date) -- 复合主键，区分同一员工的不同历史版本
);

2. 分两步实现历史版本更新 #

Type 2的核心是标记旧版本为失效，插入新版本，分两步执行可规避不同数据库Merge语法的差异问题：

第一步：标记变化的旧记录为失效

对比源表与维度表的活跃记录，将有字段变更的旧版本标记为非活跃，设置有效截止日期为变更前一日：

UPDATE employee_dim
SET 
    end_date = CURRENT_DATE() - INTERVAL '1 DAY', -- 替换为对应数据库的日期函数，如SQL Server用DATEADD(day, -1, GETDATE())
    active_flag = 'N'
WHERE id IN (
    SELECT target.id
    FROM employee_dim target
    JOIN employee_source source 
        ON target.id = source.id 
        AND target.active_flag = 'Y'
    WHERE 
        -- 定义需要触发版本变更的字段，根据业务调整
        target.name <> source.name 
        OR target.salary <> source.salary 
        OR target.status <> source.status
);

第二步：插入新的活跃记录

插入源表中所有无对应活跃版本的记录（包括变更后的新版本、新增员工）：

INSERT INTO employee_dim (id, name, salary, status, start_date, end_date, active_flag)
SELECT
    source.id,
    source.name,
    source.salary,
    source.status,
    CURRENT_DATE() AS start_date, -- 变更当日作为新版本的生效日期
    '9999-12-31' AS end_date, -- 用最远日期标识当前活跃版本
    'Y' AS active_flag
FROM employee_source source
LEFT JOIN employee_dim target 
    ON source.id = target.id 
    AND target.active_flag = 'Y'
WHERE target.id IS NULL; -- 筛选出无活跃版本的记录

3. 执行效果示例 #

假设初始维度表活跃记录：

次日源表数据（John涨薪、Joe状态变更）：

执行后维度表会包含：

注意事项 #

• 日期函数：根据使用的数据库调整日期计算逻辑，比如MySQL用DATE_SUB(CURDATE(), INTERVAL 1 DAY)，BigQuery用DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)。
• 变更字段：仅将业务上需要追踪历史的字段加入对比条件，避免不必要的版本生成。
• 员工离职处理：如果源表移除了某个员工，需要额外逻辑将其最后一条活跃记录标记为失效，可在UPDATE步骤中加入对应的判断。

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