More Thoughts on Cloning in Snowflake

Recently, I was asked to compare data from a table in Snowflake with the same table’s data from a few hours before. As always, no one had thought about copying the data into another table before they started working with it. They just remembered an approximate time they started working with the table — 10 AM — and the table’s name — Public.WorkingTable. So, let’s see what we can do, shall we?

With this little bit of information, we actually have everything we need to get started. First, we log on to the Public schema of our database and begin typing the following command:

CREATE TABLE WorkingTable_20250107_1000 CLONE WorkingTable AT (TIMESTAMP => '2025-01-07 10:00:00') 

This should create a new table – called WorkingTable_20250107_1000 – with all of the data as of 10 AM this morning.

This query should work. And yes, I do emphasize “should” because there are 3 cases where it doesn’t.

1. If the table does not have time travel turned on. Unless you have backups from exactly 10 AM, you’re not going to get the data. 🙁
2. If time has already passed out of this table’s time travel capability. For example, if the table is set only to have 24 hours of time travel available, once it is 10:01 on 2025-01-08, then this command won’t work.
3. Sometimes, Snowflake sees the datetime string as a string for whatever reason. To get around that, I tend to rewrite the query as follows:

CREATE TABLE WorkingTable_20250107_1000 CLONE WorkingTable AT (TIMESTAMP => '2025-01-07 10:00:00'::TIMESTAMP))

Note that the only difference is that we’re making doubly sure that Snowflake knows that we want to convert the string to a timestamp before creating our new table with all of our cloned data.

From here, we can use one of the fun tricks with SQL that I’ve blogged about before here.

SELECT * FROM WorkingTable
EXCEPT
SELECT * FROM WorkingTable_20250107_1000

It is to be noted that you can use MINUS here instead of EXCEPT. Both of them do the same operation in Snowflake.

And that last query’s result will show the differences between how the table looks now versus what it looked like at 10 AM.

With that, I hope you will enjoy using cloning the next time that it’s appropriate for you in Snowflake. And just because everyone needs a good metal song about cloning, I leave you with Fear Factory’s Cloning Technology: