How to Use BEFORE Clause for Time Travel in Snowflake
In Snowflake, use the
BEFORE clause with TIMESTAMP or STATEMENT to query data as it existed before a specific time or statement. This allows you to travel back in time to see previous versions of your data easily.Syntax
The BEFORE clause is used with the AT keyword in Snowflake's time travel feature to specify a point in time before which you want to see the data.
It can be used as:
BEFORE (TIMESTAMP => 'timestamp_value')- to query data before a specific timestamp.BEFORE (STATEMENT => statement_id)- to query data before a specific statement executed.
This clause is placed inside the AT clause like AT BEFORE (TIMESTAMP => '...').
sql
SELECT * FROM my_table AT BEFORE (TIMESTAMP => '2024-06-01 12:00:00');
Example
This example shows how to query a table's data as it existed before a specific timestamp using the BEFORE clause.
sql
CREATE OR REPLACE TABLE sales (id INT, amount INT); INSERT INTO sales VALUES (1, 100), (2, 200); -- Wait or perform some changes UPDATE sales SET amount = 150 WHERE id = 1; -- Query data before the update SELECT * FROM sales AT BEFORE (TIMESTAMP => CURRENT_TIMESTAMP() - INTERVAL '1 minute');
Output
ID | AMOUNT
---|--------
1 | 100
2 | 200
Common Pitfalls
- Using
BEFOREwithout specifyingTIMESTAMPorSTATEMENTcauses errors. - Querying a time before the table existed returns empty results.
- Time travel retention period limits how far back you can query.
- Mixing
ATandBEFOREincorrectly, likeAT BEFORE 'timestamp'without parentheses, is invalid syntax.
sql
/* Incorrect usage - missing parentheses and keyword */ SELECT * FROM sales AT BEFORE '2024-06-01 12:00:00'; /* Correct usage */ SELECT * FROM sales AT BEFORE (TIMESTAMP => '2024-06-01 12:00:00');
Quick Reference
| Clause | Description | Example |
|---|---|---|
| BEFORE (TIMESTAMP => 'timestamp') | Query data before a specific timestamp | SELECT * FROM table AT BEFORE (TIMESTAMP => '2024-06-01 12:00:00'); |
| BEFORE (STATEMENT => statement_id) | Query data before a specific statement executed | SELECT * FROM table AT BEFORE (STATEMENT => '01a2b3c4d5e6f7'); |
| AT | Specifies time travel query point | SELECT * FROM table AT BEFORE (TIMESTAMP => 'timestamp'); |
Key Takeaways
Use the BEFORE clause inside AT to query data as it existed before a specific time or statement.
Always specify BEFORE with TIMESTAMP or STATEMENT keyword and parentheses for valid syntax.
Time travel queries are limited by the data retention period configured in Snowflake.
Incorrect syntax or querying before table creation returns errors or empty results.
BEFORE clause helps safely view historical data without affecting current data.
