How to Use Time Travel in Snowflake: Syntax and Examples
Use Snowflake's
AT or BEFORE clause with TIMESTAMP or OFFSET to query data as it existed in the past. Time Travel lets you access historical data up to the retention period for tables, schemas, or databases.Syntax
Snowflake's Time Travel uses the AT or BEFORE clause to specify a point in time or offset for querying historical data.
AT (TIMESTAMP => 'timestamp_value'): Query data exactly at the given timestamp.BEFORE (STATEMENT => statement_id): Query data before a specific statement executed.AT (OFFSET => offset_in_seconds): Query data as it was a number of seconds ago.
You can use these clauses with SELECT, CLONE, or UNDROP commands.
sql
SELECT * FROM my_table AT (TIMESTAMP => TO_TIMESTAMP('2024-06-01 12:00:00'));
Example
This example shows how to query a table as it existed 1 hour ago using Time Travel.
sql
SELECT * FROM orders AT (OFFSET => 3600);
Output
[
{"order_id": 101, "status": "shipped", "amount": 250},
{"order_id": 102, "status": "pending", "amount": 100}
]
Common Pitfalls
Common mistakes when using Time Travel include:
- Using timestamps outside the retention period (default 1 day, can be extended).
- Confusing
ATandBEFOREclauses. - Trying to Time Travel on transient or temporary tables which do not support it.
- Not having sufficient privileges to access historical data.
sql
/* Wrong: timestamp too old or table type unsupported */ SELECT * FROM temp_table AT (TIMESTAMP => TO_TIMESTAMP('2023-01-01 00:00:00')); /* Right: use supported table and valid timestamp */ SELECT * FROM orders AT (TIMESTAMP => CURRENT_TIMESTAMP() - INTERVAL '1 DAY');
Quick Reference
| Clause | Description | Example |
|---|---|---|
| AT (TIMESTAMP => 'timestamp') | Query data at exact timestamp | SELECT * FROM table AT (TIMESTAMP => TO_TIMESTAMP('2024-06-01 12:00:00')); |
| AT (OFFSET => seconds) | Query data seconds ago | SELECT * FROM table AT (OFFSET => 3600); |
| BEFORE (STATEMENT => statement_id) | Query data before a statement | SELECT * FROM table BEFORE (STATEMENT => '01a2b3c4d5'); |
| UNDROP | Restore dropped objects | UNDROP TABLE my_table; |
| CLONE | Create a copy at a past state | CREATE TABLE clone_table CLONE original_table AT (OFFSET => 3600); |
Key Takeaways
Use the AT or BEFORE clause with a timestamp or offset to query past data in Snowflake.
Time Travel works within the retention period, usually 1 day by default.
Temporary and transient tables do not support Time Travel.
You can restore dropped tables or clone data as it existed in the past using Time Travel.
Ensure you have the right permissions to access historical data.
