Snowflake数据恢复咨询：误执行CREATE OR REPLACE及修改瞬态表后找回数据

一、CREATE OR REPLACE操作后的数据恢复 #

CREATE OR REPLACE TABLE 本质是先删除原表再创建新表，原表会进入Snowflake的时间旅行保留周期，可通过以下步骤恢复：

1. 确认原表的可恢复性
   执行以下命令查看表的历史版本，确认原表仍在时间旅行保留期内：

   SHOW TABLES HISTORY LIKE 'your_table_name';
   

   结果中会包含被替换前的表记录，以及对应的时间戳/版本ID。

2. 恢复原表（UNDROP）
   如果原表未超出保留期，直接执行UNDROP命令恢复：

   UNDROP TABLE your_table_name;
   

   恢复后的表会自动添加时间戳后缀（如your_table_name_20240520123456），可通过ALTER TABLE重命名：

   ALTER TABLE your_table_name_20240520123456 RENAME TO your_table_name_old;
   

3. 时间旅行查询恢复（若UNDROP不可用）
   若原表已过UNDROP保留期，可通过时间旅行直接查询被替换前的数据，再重建表：

   -- 按时间戳查询（替换为原表被替换前的时间）
   CREATE OR REPLACE TABLE your_table_name_restored AS
   SELECT * FROM your_table_name AT(TIMESTAMP => '2024-05-18 10:00:00'::TIMESTAMP);
   
   -- 或按时间偏移量（单位秒，如1天前即86400秒）
   CREATE OR REPLACE TABLE your_table_name_restored AS
   SELECT * FROM your_table_name AT(OFFSET => -86400);
   

二、瞬态表（Transient Table）两天前的数据恢复 #

瞬态表的默认数据保留期为1天，但创建时可指定最长90天的保留期，恢复方案分两种情况：

情况1：瞬态表保留期≥2天 #

1. 确认保留期
   执行命令查看表的保留期设置：

   DESCRIBE TABLE your_transient_table;
   

   查看DATA_RETENTION_TIME_IN_DAYS字段，若值≥2则可通过时间旅行恢复。

2. 查询并恢复数据
   直接查询两天前的数据并重建表：

   -- 按时间偏移量（两天=172800秒）
   CREATE OR REPLACE TABLE your_transient_table_restored AS
   SELECT * FROM your_transient_table AT(OFFSET => -172800);
   
   -- 或按具体时间戳
   CREATE OR REPLACE TABLE your_transient_table_restored AS
   SELECT * FROM your_transient_table AT(TIMESTAMP => DATEADD(DAY, -2, CURRENT_TIMESTAMP()));
   

   若只是需要恢复被修改的部分数据，可对比当前表和历史数据，选择性插入/更新。

情况2：瞬态表保留期<2天 #

此时时间旅行无法恢复数据，可尝试以下备选方案：

• 检查是否有克隆表（CLONE）：若之前对该瞬态表执行过CREATE TABLE ... CLONE操作，可从克隆表中恢复数据。
• 检查外部备份：若定期将表数据导出到外部存储（如S3、Azure Blob），可从备份文件重新导入。
• 企业版及以上用户：联系Snowflake支持团队，尝试通过Fail-safe恢复（Fail-safe是时间旅行过期后的7天灾难恢复机制，仅支持官方协助）。

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