Snowflake Time Travel

Snowflake and Time Travel.

Snowflake time travel. Snowflake lets you try out their services for free with a trial account. It serves as a powerful tool for performing the following tasks. The biggest difference however is SQL Server stores all the versions indefinitely while Snowflake only maintains the different versions of your table for a specific period in time.

First things first lets get your Snowflake account and user permissions primed to use Time Travel features. Time travel can be defined on objects like Databases Schemas Tables. Data that has been changed or deleted at any point.

These features are included standard for all accounts ie. Here is the list of operations allowed with time travel. Create or replace table vs insert overwrite into table.

Using a timestamp stamp and explicitly casting to TIMESTAMP results in an unintended but correct error as the timestamp is casting to TIMESTAMP effectively TIMESTAMP_NTZ due to TIMESTAMP_TYPE_MAPPING setting and the table was created much later in the UTC clock. For example if you have a Employee table and if you delete the table accidentally you can use time travel and go back 5 minutes and retrieve the data back. However we can enable for longer a data retention period of up to 90 days for enterprise and higher editions.

Snowflake Time Travel. Snowflake stores those immutable data blocks used for Time Travel based on the parameter DATA_RETENTION_TIME_IN_DAYS. The standard retention period is one day 24 hours and is automatically enabled for all Snowflake accounts.

Snowflake storage layer uses immutable storage as a technique for storing data. So the full query to access the time travel data would look as below. The standard mechanism for accessing historical data in Snowflake is to use whats called Time Travel.