Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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 AT and BEFORE clauses.
  • 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

ClauseDescriptionExample
AT (TIMESTAMP => 'timestamp')Query data at exact timestampSELECT * FROM table AT (TIMESTAMP => TO_TIMESTAMP('2024-06-01 12:00:00'));
AT (OFFSET => seconds)Query data seconds agoSELECT * FROM table AT (OFFSET => 3600);
BEFORE (STATEMENT => statement_id)Query data before a statementSELECT * FROM table BEFORE (STATEMENT => '01a2b3c4d5');
UNDROPRestore dropped objectsUNDROP TABLE my_table;
CLONECreate a copy at a past stateCREATE 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.