基于DuckDB实现SCD Type2的最佳实践及删除行处理咨询

一、分拆Update/Insert操作的最佳实践 #

因为DuckDB不支持MERGE，分拆成更新旧记录、插入新记录是最直接的方案，要注意以下几点：

• 用事务保证原子性：把更新和插入操作放在同一个事务里，避免中途失败导致数据不一致。DuckDB支持标准的BEGIN/COMMIT/ROLLBACK事务语法。
• 精准定位需要更新的旧记录：通过业务唯一键（比如customer_id）关联源表和维度表，只标记那些当前有效且数据发生变化的旧记录为失效，避免无意义的更新。
• 严格维护SCD2核心字段：确保维度表包含valid_from（记录生效时间）、valid_to（记录失效时间）、current_flag（是否为当前有效记录）这三个核心字段，更新时把旧记录的valid_to设为当前时间，current_flag置为FALSE；插入新记录时valid_from设为当前时间，valid_to设为一个极大值（如'9999-12-31'），current_flag置为TRUE。
• 跳过无变化的记录：插入新记录前，先检查源表记录是否和维度表的当前记录完全一致，避免重复插入相同内容。

示例代码：

BEGIN TRANSACTION;

-- 标记有变化的旧记录为失效
UPDATE dim_customer
SET valid_to = CURRENT_TIMESTAMP,
    current_flag = FALSE
FROM src_customer
WHERE dim_customer.customer_id = src_customer.customer_id
  AND dim_customer.current_flag = TRUE
  AND (dim_customer.name != src_customer.name OR dim_customer.email != src_customer.email);

-- 插入新增或更新后的新记录
INSERT INTO dim_customer (customer_id, name, email, valid_from, valid_to, current_flag)
SELECT customer_id, name, email, CURRENT_TIMESTAMP, '9999-12-31'::DATE, TRUE
FROM src_customer
WHERE NOT EXISTS (
    SELECT 1 FROM dim_customer
    WHERE dim_customer.customer_id = src_customer.customer_id
      AND dim_customer.current_flag = TRUE
      AND dim_customer.name = src_customer.name
      AND dim_customer.email = src_customer.email
);

COMMIT;

二、其他可行实现思路 #

如果分拆Update/Insert的方式不符合你的场景，还可以试试以下两种方案：

1. 全量快照替换维度表（适合小数据量场景） #

直接将维度表的历史记录与源表的最新记录合并，重新生成整个维度表。这种方式逻辑简单，不需要分拆操作，但只适合数据量不大的情况，避免大表重建带来的性能开销。

示例代码：

CREATE OR REPLACE TABLE dim_customer AS
-- 保留所有历史失效记录
SELECT customer_id, name, email, valid_from, valid_to, current_flag
FROM dim_customer
WHERE current_flag = FALSE
UNION ALL
-- 加入源表的最新记录（新增+更新）
SELECT 
    s.customer_id,
    s.name,
    s.email,
    CURRENT_TIMESTAMP AS valid_from,
    '9999-12-31'::DATE AS valid_to,
    TRUE AS current_flag
FROM src_customer s
LEFT JOIN dim_customer d 
    ON s.customer_id = d.customer_id 
    AND d.current_flag = TRUE
WHERE d.customer_id IS NULL -- 新增记录
   OR (d.name != s.name OR d.email != s.email); -- 有变化的记录

2. 临时表中转批量操作 #

先通过查询生成需要处理的记录（失效的旧记录、新增的新记录）存入临时表，再基于临时表完成更新和插入。这种方式可以提前过滤数据，减少主表的操作量，适合中等数据量场景。

示例代码：

-- 创建临时表，存储需要更新的业务键
CREATE TEMP TABLE updated_customers AS
SELECT d.customer_id
FROM dim_customer d
JOIN src_customer s ON d.customer_id = s.customer_id
WHERE d.current_flag = TRUE
  AND (d.name != s.name OR d.email != s.email);

-- 创建临时表，存储需要插入的新记录
CREATE TEMP TABLE new_customers AS
SELECT s.customer_id, s.name, s.email
FROM src_customer s
LEFT JOIN dim_customer d ON s.customer_id = d.customer_id AND d.current_flag = TRUE
WHERE d.customer_id IS NULL
UNION ALL
SELECT s.customer_id, s.name, s.email
FROM src_customer s
JOIN updated_customers uc ON s.customer_id = uc.customer_id;

BEGIN TRANSACTION;

-- 更新旧记录
UPDATE dim_customer
SET valid_to = CURRENT_TIMESTAMP, current_flag = FALSE
WHERE customer_id IN (SELECT customer_id FROM updated_customers);

-- 插入新记录
INSERT INTO dim_customer (customer_id, name, email, valid_from, valid_to, current_flag)
SELECT customer_id, name, email, CURRENT_TIMESTAMP, '9999-12-31'::DATE, TRUE
FROM new_customers;

COMMIT;

-- 清理临时表
DROP TABLE updated_customers;
DROP TABLE new_customers;

三、处理删除行的方案 #

SCD Type2要求保留历史轨迹，因此不建议直接硬删除维度表记录，推荐以下两种软删除方案：

1. 标记删除并保留历史 #

给维度表新增is_deleted布尔字段，当源表中某条记录被删除时，找到维度表中对应的当前有效记录，将其valid_to设为当前时间、current_flag置为FALSE、is_deleted置为TRUE，这样既保留了历史关联，又标记了删除状态。

示例代码：

BEGIN TRANSACTION;

-- 标记源表已删除的维度记录为失效+删除
UPDATE dim_customer
SET valid_to = CURRENT_TIMESTAMP,
    current_flag = FALSE,
    is_deleted = TRUE
WHERE current_flag = TRUE
  AND customer_id NOT IN (SELECT customer_id FROM src_customer);

COMMIT;

2. 标记后归档到单独表 #

如果主维度表不需要保留删除记录，可以先标记删除，再将这些记录移到归档表，最后从主表移除。这种方式可以减小主表的体积，同时保留删除历史用于审计。

示例代码：

BEGIN TRANSACTION;

-- 标记删除
UPDATE dim_customer
SET valid_to = CURRENT_TIMESTAMP,
    current_flag = FALSE,
    is_deleted = TRUE
WHERE current_flag = TRUE
  AND customer_id NOT IN (SELECT customer_id FROM src_customer);

-- 归档删除记录
INSERT INTO dim_customer_archive
SELECT * FROM dim_customer WHERE is_deleted = TRUE;

-- 从主表移除归档记录
DELETE FROM dim_customer WHERE is_deleted = TRUE;

COMMIT;

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