Time Travel - Part I

1. Time Travel - Part I

We’re about to learn about time travel in Snowflake, and I’ll just give it to you straight: Time travel is a superpower. I’ve had data science jobs before where some data would get messed up, and then we’d have to recreate our tables from raw data, and it would take forever, and at the end we wouldn’t always be able to get back to the pre-corrupted state. There would be these weird, persistent differences we couldn’t account for and we would never figure it out. If we’d been using Snowflake, we could have avoided those headaches because, as you’ll see, Snowflake time travel lets you go back in time and recover the exact data in your table, schema, or database at a point in the recent past. It’s kind of hard to fathom. To understand time travel, we have to understand data retention in Snowflake. If we run the command: SHOW TABLES; And scroll to the right, we’ll see a column called “retention_time.” For a normal, permanent table, the default retention time is 1 day. If you’re on the “enterprise edition” of Snowflake, you could raise this all the way up to 90 days using an ALTER TABLE command: ALTER TABLE FROSTBYTE_TASTY_BYTES.RAW_POS.TEST_MENU SET DATA_RETENTION_TIME_IN_DAYS \= 90; If we then run SHOW TABLES again, we’ll see that the retention time has gone to 90 days: SHOW TABLES; You can see that it now says 90 days here. (Let’s clean this up and bring that back down to 1 day.) ALTER TABLE FROSTBYTE_TASTY_BYTES.RAW_POS.TEST_MENU SET DATA_RETENTION_TIME_IN_DAYS \= 1; Having a retention time of 1 day doesn’t mean your data will go away after a day – these are permanent tables, so they’ll persist indefinitely. What this means is that Snowflake keeps track of all changes to your data that happened in the last day, and with time travel, lets you go back to what your table looked like at any point in that period. So if you set a data retention time of 90 days, you can query your table as of the table’s state at any moment in the last 90 days, though you should be careful because if there’s high data churn, this could mean you’re paying for a lot of storage. It may surprise you to know that we’ve already used Snowflake’s time travel functionality several times in this course! When we learned to undrop databases, undrop schemas, and undrop tables, all of that was time travel, and was only possible because of Snowflake’s data retention feature. Also, we learned about seeing table metadata using SHOW TABLES – it turns out, this lists dropped tables if you’re still within the retention period. Soon we’ll learn how to use Time Travel in another way (in addition to undrop) – by querying past data from Snowflake tables. There are [three](https://quickstarts.snowflake.com/guide/getting_started_with_time_travel/index.html?index=..%2F..index#0) ways to do this: One, querying a table as of a particular timestamp Two, querying a table as of a certain number of seconds in the past Three, querying a table before a certain past query was run In the next video, we’ll learn how to do each of these in practice.

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.