如何用Snowflake Time Travel获取SCD Type 1表月度历史数据？

一、使用Snowflake Time Travel实现 #

SCD Type 1表的核心特点是更新时直接覆盖旧数据，本身不保留历史版本，要获取月度历史数据需借助Snowflake的Time Travel功能，核心逻辑是获取目标月份特定时间点的表快照。

关键前提 #

首先确认目标表的Time Travel保留期足够覆盖查询范围，执行以下命令查看：

DESCRIBE TABLE your_scd1_table;

检查结果中的RETENTION_TIME字段，需确保数值大于目标月份到查询时间的间隔天数（比如8月查7月数据，至少需要保留31天以上）。

具体查询示例 #

1. 获取7月31日结束时的表状态
   如果在8月1日查询，可通过时间偏移量指定：

   SELECT * FROM your_scd1_table AT(OFFSET => -1);
   

   也可以直接指定7月底的时间戳，避免偏移量计算误差：

   SELECT * FROM your_scd1_table AT(TIMESTAMP => '2024-07-31 23:59:59'::TIMESTAMP);
   

2. 获取7月1日初始状态的表数据
   直接指定7月1日的起始时间戳：

   SELECT * FROM your_scd1_table AT(TIMESTAMP => '2024-07-01 00:00:00'::TIMESTAMP);
   

注意事项 #

Time Travel仅能返回表在单个时间点的快照，无法直接获取7月期间所有的增量变化记录（因为SCD Type 1本身覆盖旧值）。如果需要追踪7月内的多次更新，需结合QUERY_HISTORY找到表的更新时间点，再分别获取对应时间点的快照。

二、不使用Time Travel的替代方案 #

如果Time Travel的保留期无法满足长期查询需求，或需要更灵活的历史数据追踪，可采用以下方案：

方案1：定期生成月度快照表 #

通过Snowflake任务（Task）自动在每月初创建上月的表快照，后续直接查询快照表即可：

-- 创建每月执行的任务，以2024年7月快照为例
CREATE TASK create_monthly_snapshot_task
  WAREHOUSE = your_warehouse
  SCHEDULE = 'USING CRON 0 0 1 * * UTC' -- 每月1日UTC时间0点执行
AS
  CREATE TABLE your_scd1_table_snapshot_202407 AS SELECT * FROM your_scd1_table;

执行后，可通过your_scd1_table_snapshot_202407直接获取7月的表数据。

方案2：基于变更数据捕获（CDC）追踪历史 #

利用Snowflake的流（Stream）捕获SCD Type 1表的所有变更操作，同步到历史记录表中，实现全量历史回溯：

1. 创建流捕获表变更：

   CREATE STREAM your_scd1_table_stream ON TABLE your_scd1_table;
   

2. 创建任务定期将流中记录同步到历史表：

   CREATE TASK sync_change_history_task
     WAREHOUSE = your_warehouse
     SCHEDULE = 'USING CRON 0 * * * * UTC' -- 每小时执行一次
   AS
     INSERT INTO your_scd1_history_table
     SELECT CURRENT_TIMESTAMP AS change_time, * FROM your_scd1_table_stream;
   

后续查询your_scd1_history_table，可筛选7月的变更记录，进而还原出任意时间点的表状态。

方案3：扩展表结构记录更新时间（局限性方案） #

在SCD Type 1表中新增last_updated_date字段，每次更新时记录当前时间：

ALTER TABLE your_scd1_table ADD COLUMN last_updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

查询7月数据时，可筛选last_updated_date <= '2024-07-31'，但此方案仅能获取到截至7月31日的最新数据，无法还原7月内的中间状态（因为旧值已被覆盖），仅适合无需追踪中间变化的场景。

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