GumGum Tech Blog
Published in

GumGum Tech Blog

Time Travel in Snowflake

Background

Purpose

Snowflake Time Travel

Snowflake Micro-partition selection during Time Travel

The Plot

The Issue

Avengers: Endgame (2019)

The Change

The Terminator (1984)
SELECT unique_key_1, unique_key_2, unique_key_3, COUNT(*)
FROM my_table at(timestamp => ‘2021–10–16 13:11:00’::timestamp)
GROUP BY $1, $2, $3
HAVING COUNT(*) > 1

The Root Cause

Back to the Future (1985)
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE END_TIME <= ‘2021–10–16 13:30:00’::timestamp
AND END_TIME > ‘2021–10–16 13:00:00’
AND LOWER(QUERY_TEXT) LIKE ‘%my_table%’;

The Fix

Harry Potter and the Prisoner of Azkaban (2004)
-- Create temp 
CREATE TEMP TABLE revert_my_table
AS
SELECT *
FROM my_table at(timestamp => '2021-10-16 13:11:00'::timestamp)
WHERE DAY = '2021-10-11';
-- Delete data
DELETE FROM my_table
WHERE DAY = '2021-10-11';
-- Insert 10-16 records
INSERT INTO my_table
SELECT *
FROM revert_my_table;

Reflection

Costs Considerations

Summary