Time Travel is a powerful data protection feature in Snowflake that enables users to access historical data at any point within a defined retention period. This capability allows organizations to query, clone, and restore data as it existed at specific moments in the past, providing essential prote…Time Travel is a powerful data protection feature in Snowflake that enables users to access historical data at any point within a defined retention period. This capability allows organizations to query, clone, and restore data as it existed at specific moments in the past, providing essential protection against accidental or malicious data modifications.
The Time Travel retention period can be configured from 0 to 90 days, depending on the Snowflake edition. Standard Edition supports up to 1 day of Time Travel, while Enterprise Edition and higher support up to 90 days. This retention period can be set at the account, database, schema, or table level using the DATA_RETENTION_TIME_IN_DAYS parameter.
Time Travel supports several key operations. The AT clause allows querying data as it existed at a specific timestamp, while the BEFORE clause retrieves data from just before a specified point. Users can reference data using timestamps, statement IDs, or time offsets. For example, SELECT * FROM table AT(TIMESTAMP => '2024-01-15 10:00:00') retrieves the table state at that exact moment.
The UNDROP command leverages Time Travel to restore dropped tables, schemas, or databases within the retention period. This provides a safety net against accidental deletions. Additionally, Time Travel enables zero-copy cloning of historical data states, allowing users to create clones of objects as they existed at previous points.
Time Travel consumes storage for maintaining historical data versions. Once the retention period expires, data transitions to Fail-safe, a seven-day period during which Snowflake can recover data for disaster recovery purposes. Unlike Time Travel, Fail-safe is accessible only by Snowflake support.
Understanding Time Travel is crucial for data governance, compliance requirements, and maintaining data integrity. It provides audit capabilities, supports data recovery scenarios, and enables analytical queries on historical data states, making it an essential feature for enterprise data management in Snowflake.
Time Travel in Snowflake: Complete Guide for SnowPro Core Certification
What is Time Travel?
Time Travel is a powerful Snowflake feature that allows you to access historical data at any point within a defined retention period. This means you can query, clone, or restore data that has been changed or deleted, providing a safety net against accidental data modifications.
Why is Time Travel Important?
Time Travel is critical for several reasons: • Data Recovery: Recover accidentally deleted or modified data • Historical Analysis: Query data as it existed at a specific point in time • Auditing: Track changes and understand data evolution • Development: Create clones of data from specific points for testing • Compliance: Meet regulatory requirements for data retention
How Time Travel Works
Snowflake automatically maintains historical data for a configurable retention period. The key parameter is DATA_RETENTION_TIME_IN_DAYS, which can be set at account, database, schema, or table level.
Retention Periods by Edition: • Standard Edition: 0 to 1 day • Enterprise Edition and higher: 0 to 90 days • Default setting: 1 day for all editions
Accessing Historical Data:
1. AT clause: Query data at a specific timestamp or statement SELECT * FROM table AT(TIMESTAMP => '2024-01-15 10:00:00'); SELECT * FROM table AT(OFFSET => -3600); SELECT * FROM table AT(STATEMENT => 'query-id');
2. BEFORE clause: Query data before a specific point SELECT * FROM table BEFORE(STATEMENT => 'query-id');
Restoring Data with UNDROP
You can restore dropped objects using: • UNDROP TABLE table_name; • UNDROP SCHEMA schema_name; • UNDROP DATABASE database_name;
Time Travel data consumes storage and incurs costs. Longer retention periods mean higher storage costs. After the Time Travel period expires, data moves to Fail-safe for an additional 7 days (non-configurable, Enterprise and higher).
Key Limitations
• Temporary and transient tables have a maximum retention of 1 day • Temporary and transient tables have no Fail-safe period • Time Travel cannot recover data beyond the retention period • Continuous data changes increase Time Travel storage usage
Exam Tips: Answering Questions on Time Travel Feature
Focus Areas for the Exam:
1. Know the retention limits: Remember Standard = 1 day max, Enterprise+ = 90 days max
2. Understand the syntax: Be familiar with AT, BEFORE, OFFSET, TIMESTAMP, and STATEMENT options
3. UNDROP functionality: Know that UNDROP works for tables, schemas, and databases
4. Transient vs Permanent tables: Questions often test understanding that transient tables have limited Time Travel (0-1 day) and no Fail-safe
5. Storage implications: Understand that Time Travel data contributes to storage costs
6. Cloning with Time Travel: Know that you can clone objects from historical points within the retention window
7. Hierarchy of settings: DATA_RETENTION_TIME_IN_DAYS can be set at account, database, schema, or table level
Common Exam Scenarios: • Identifying the correct SQL syntax for querying historical data • Choosing appropriate table types based on Time Travel requirements • Understanding cost implications of retention settings • Recognizing when Time Travel vs Fail-safe applies
Remember: Time Travel is for user-initiated recovery, while Fail-safe is for Snowflake Support-assisted recovery only.