如何在Snowflake中捕获每日删除重建表的Delta记录？

方案1：修改刷新逻辑为TRUNCATE+INSERT，复用Streams实现原生增量捕获 #

原CREATE OR REPLACE TABLE会销毁原表导致Streams失效，改用先TRUNCATE再批量INSERT的方式，可让标准Stream持续捕获所有变更（TRUNCATE标记为全表删除，INSERT新行标记为新增），无需自定义差异计算。

操作步骤： #

1. 批量创建标准Stream（覆盖所有目标表）：

-- 生成批量创建Stream的脚本
SELECT 'CREATE OR REPLACE STREAM stream_' || table_name || ' ON TABLE ' || table_name || ' SHOW_INITIAL_ROWS = TRUE;'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'YOUR_SCHEMA' AND table_type = 'BASE TABLE';

执行生成的脚本，即可为所有表创建对应Stream。

2. 调整数据管道刷新逻辑：
   将CREATE OR REPLACE TABLE t AS SELECT ...替换为：

TRUNCATE TABLE t;
INSERT INTO t SELECT ...; -- 原全量数据查询语句

3. 下游消费增量：
   从Stream直接读取变更类型，Stream自动维护消费位点避免重复读取：

SELECT METADATA$ACTION, METADATA$ISUPDATE, *
FROM stream_t
WHERE METADATA$ACTION IN ('INSERT', 'DELETE');
-- 手动推进Stream位点（可选）：ALTER STREAM stream_t APPEND;

优势： #

• 完全原生支持，无自定义差异逻辑
• 批量脚本快速适配数百张表
• 增量捕获性能高，直接读取变更日志

注意点： #

• 必须使用标准Stream（不可用APPEND_ONLY），才能捕获TRUNCATE的删除操作
• SHOW_INITIAL_ROWS=TRUE确保首次创建Stream时能捕获当前表数据（适配下游初始全量需求）

方案2：保留CREATE OR REPLACE逻辑，用Task+存储过程自动批量计算增量（基于Time Travel） #

若无法修改现有管道的CREATE OR REPLACE逻辑，可利用Snowflake Time Travel特性，通过参数化存储过程批量计算每日新旧表差异，无需手动编写MERGE。

操作步骤： #

1. 创建通用差异计算存储过程：

CREATE OR REPLACE PROCEDURE calculate_table_increment(table_name VARCHAR, schema_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
    old_table_snapshot VARCHAR := schema_name || '.' || table_name || ' AT(OFFSET => 86400)'; -- 取1天前的快照
    increment_table VARCHAR := schema_name || '.increment_' || table_name;
BEGIN
    -- 自动创建增量表（同步原表结构+变更类型字段）
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE ' || increment_table || ' LIKE ' || schema_name || '.' || table_name || ' ADD COLUMN METADATA$ACTION VARCHAR(10)';
    
    -- 插入新增/更新行（新表有但旧快照无的记录）
    EXECUTE IMMEDIATE 'INSERT INTO ' || increment_table || ' SELECT *, ''INSERT'' FROM ' || schema_name || '.' || table_name || ' MINUS SELECT *, ''INSERT'' FROM ' || old_table_snapshot;
    
    -- 插入删除行（旧快照有但新表无的记录）
    EXECUTE IMMEDIATE 'INSERT INTO ' || increment_table || ' SELECT *, ''DELETE'' FROM ' || old_table_snapshot || ' MINUS SELECT *, ''DELETE'' FROM ' || schema_name || '.' || table_name;
    
    RETURN 'Increment calculated for ' || table_name;
END;
$$;

2. 批量创建定时Task：

-- 生成批量创建Task的脚本（根据管道刷新时间调整CRON）
SELECT 'CREATE OR REPLACE TASK task_increment_' || table_name || ' SCHEDULE = ''USING CRON 0 1 * * * UTC'' AS CALL calculate_table_increment(''' || table_name || ''', ''' || table_schema || ''');'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'YOUR_SCHEMA' AND table_type = 'BASE TABLE';

执行生成的脚本，为每张表创建定时执行的Task。

3. 下游消费：
   直接从increment_xxx表读取当日增量，通过METADATA$ACTION识别变更类型。

优势： #

• 无需修改现有数据管道
• 存储过程参数化，批量适配数百张表
• 基于Time Travel，无需额外存储历史表（保留期≥1天即可，默认7天）

注意点： #

• 确保目标表的Time Travel保留期≥1天（可通过ALTER TABLE t SET DATA_RETENTION_TIME_IN_DAYS = 7;调整）
• 表结构变更时，增量表会自动同步结构（因使用LIKE创建）

方案3：轻量克隆历史表，配合物化视图自动维护增量 #

利用Snowflake零拷贝克隆特性，每天刷新前克隆当前表作为历史版本，再用物化视图自动对比新旧表差异，适合需长期保留历史增量的场景。

操作步骤： #

1. 批量创建每日克隆任务：

-- 生成批量克隆脚本（可放入Task定时执行）
SELECT 'CREATE OR REPLACE TABLE ' || table_schema || '.' || table_name || '_hist_' || CURRENT_DATE() || ' CLONE ' || table_schema || '.' || table_name || ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'YOUR_SCHEMA' AND table_type = 'BASE TABLE';

2. 批量创建增量物化视图：

-- 生成批量创建物化视图的脚本
SELECT 'CREATE OR REPLACE MATERIALIZED VIEW ' || table_schema || '.mv_increment_' || table_name || ' AS 
SELECT *, ''INSERT'' AS METADATA$ACTION FROM ' || table_schema || '.' || table_name || ' 
MINUS SELECT *, ''INSERT'' AS METADATA$ACTION FROM ' || table_schema || '.' || table_name || '_hist_' || CURRENT_DATE() || ' 
UNION ALL 
SELECT *, ''DELETE'' AS METADATA$ACTION FROM ' || table_schema || '.' || table_name || '_hist_' || CURRENT_DATE() || ' 
MINUS SELECT *, ''DELETE'' AS METADATA$ACTION FROM ' || table_schema || '.' || table_name || ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'YOUR_SCHEMA' AND table_type = 'BASE TABLE';

3. 下游消费：
   直接从物化视图mv_increment_xxx读取增量，物化视图会自动刷新（可配置刷新频率）。

优势： #

• 克隆为零拷贝，存储成本极低
• 物化视图自动维护增量，无需手动触发计算
• 历史克隆表可长期保留，用于数据回溯

注意点： #

• 需定期清理旧克隆表，避免存储冗余（可通过Task自动清理）
• 物化视图刷新会消耗计算资源，需根据业务调整刷新策略

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