如何创建SQL表增量差异快照并重构表状态？

问题1：使用EXCEPT语句能否创建准确的增量变更快照？ #

不能。

原因在于EXCEPT是基于整行数据完全匹配来筛选差异的，只能捕获两种变更：

• 新增的行（t₁存在、t₀不存在的行）
• 被修改的行（修改后整行数据与t₀中对应行完全不同的行）

但你的场景是仅通过is_deleted字段实现软删除（无物理删除），对于被软删除的行（t₀中存在、t₁中仍存在但is_deleted字段从false变为true），这类行在t₀和t₁中都存在，只是单个字段值变化，整行对比时会被判定为“存在于双方”，因此不会被纳入main_diffs，导致增量快照缺失了软删除的变更记录。

另外，若存在同一主键的行被多次修改后又改回原状态的极端情况，EXCEPT默认的去重逻辑（PostgreSQL、SQLite、DuckDB的EXCEPT默认等同于EXCEPT DISTINCT）也会漏掉这类无最终差异的中间变更，但这在每日快照的场景中影响较小。

问题2：通过t₀加增量快照能否重构t₁时刻的表状态？ #

不能。

主要有两个核心问题：

1. 缺失软删除变更：如问题1所述，main_diffs没有包含被软删除的行的状态变化，重构后的main_reconstituted会保留t₀中未标记删除的旧行，与t₁中已标记为删除的实际状态不符。
2. 出现重复行：对于被修改的行，t₀中的旧行仍会留在main_reconstituted中，而main_diffs会插入修改后的新行，导致同一主键的行同时存在旧版和新版数据，与t₁的唯一行状态冲突。

补充说明（针对PostgreSQL、SQLite、DuckDB） #

这三个数据库均支持EXCEPT语法，且整行对比、去重的逻辑一致，因此上述结论在三个数据库中完全适用。若要实现准确的增量捕获与状态重构，需要基于唯一主键关联t₀与t₁，分别筛选新增行、修改/软删除行，例如：

-- 捕获完整增量：新增+修改/软删除
create table main_diffs as
  -- 新增行
  select * from t1 where id not in (select id from t0)
  union all
  -- 修改/软删除行（主键存在但内容不同）
  select t1.* from t1 join t0 on t1.id = t0.id where t1 is distinct from t0;

-- 重构t₁状态
create table main_reconstituted as
  -- 保留t₀中未变更的行
  select t0.* from t0 left join t1 on t0.id = t1.id where t1 is null
  union all
  -- 加入增量中的新增/修改行
  select * from main_diffs;

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