By learning Snowflake I have done a few steps with the time travel of snowflake to undo an update. Unfortunately it doesn't work for me, even tough I am taking the SQL commands directly from the tutorial:
At the beginning I am starting by changing a column by running the following update statement:
update trips set start_station_name = 'oops';
afther that I am running the following select statement, which is showing me, that the field was successfully updated to 'oops':
select
start_station_name as "station",
count(*) as "rides"
from trips
group by 1
order by 2 desc
limit 20;
Now the magic should happen by the following functionality of snowflake:
Normally we would need to scramble and hope we have a backup lying around.
In Snowflake, we can simply run a command to find the query ID of the last UPDATE command and store it in a variable named $QUERY_ID.
set query_id =
(select query_id from table(information_schema.query_history_by_session (result_limit=>5))
where query_text like 'update%' order by start_time limit 1);
Then the following query:
Use Time Travel to recreate the table with the correct station names:
create or replace table trips as
(select * from trips before (statement => $query_id));
Now, if I would run the select statement from above again, I should get the result with the old values in the column instead of the 'oops'.
Unfortunately, I am just receiving an error here saying, that the statement $query_id was not found.
How could I fix this kind of error?
