Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Use AT Clause for Time Travel in Snowflake

In Snowflake, use the AT clause with a timestamp or statement ID to query data as it existed at a specific point in time. This enables time travel by allowing you to see or restore previous versions of tables or data.
📐

Syntax

The AT clause is used in a SELECT statement to specify a point in time for time travel queries. You can use a timestamp or a statement ID to define this point.

  • table_name AT (TIMESTAMP => 'timestamp'): Query the table as it was at the given timestamp.
  • table_name AT (STATEMENT => statement_id): Query the table as it was after the specified statement executed.
sql
SELECT * FROM my_table AT (TIMESTAMP => TO_TIMESTAMP('2024-04-01 10:00:00'));
💻

Example

This example shows how to query a table as it existed at a specific timestamp using the AT clause. It demonstrates retrieving historical data for time travel.

sql
CREATE OR REPLACE TABLE sales (id INT, amount INT);

INSERT INTO sales VALUES (1, 100), (2, 200);

-- Wait or note the current time

INSERT INTO sales VALUES (3, 300);

-- Query the table as it was before the last insert
SELECT * FROM sales AT (TIMESTAMP => TO_TIMESTAMP('2024-04-01 10:00:00'));
Output
ID | AMOUNT ---|-------- 1 | 100 2 | 200
⚠️

Common Pitfalls

Common mistakes when using the AT clause include:

  • Using an incorrect timestamp format or a timestamp outside the data retention period.
  • Not having time travel enabled or the retention period set too short.
  • Confusing AT with BEFORE clause, which queries data before a statement.

Always ensure your timestamp is within the allowed time travel window (default 1 day, configurable up to 90 days).

sql
/* Wrong: timestamp format error */
SELECT * FROM sales AT (TIMESTAMP => '2024-04-01');

/* Right: use TO_TIMESTAMP for correct format */
SELECT * FROM sales AT (TIMESTAMP => TO_TIMESTAMP('2024-04-01 10:00:00'));
📊

Quick Reference

ClauseDescriptionExample
AT (TIMESTAMP => timestamp)Query data as it was at a specific timestampSELECT * FROM table AT (TIMESTAMP => TO_TIMESTAMP('2024-04-01 10:00:00'));
AT (STATEMENT => statement_id)Query data as it was after a specific statementSELECT * FROM table AT (STATEMENT => '01a2b3c4-5678-90de-f123-4567890abcde');
BEFORE (STATEMENT => statement_id)Query data before a specific statement executedSELECT * FROM table BEFORE (STATEMENT => '01a2b3c4-5678-90de-f123-4567890abcde');
✅

Key Takeaways

Use the AT clause with a timestamp or statement ID to query historical data in Snowflake.
Ensure the timestamp is within the time travel retention period to avoid errors.
Use TO_TIMESTAMP to format timestamps correctly in the AT clause.
Time travel must be enabled and retention period configured to use the AT clause effectively.
AT clause queries the state of data exactly at the specified time or statement execution.