Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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 BEFORE without specifying TIMESTAMP or STATEMENT causes errors.
  • Querying a time before the table existed returns empty results.
  • Time travel retention period limits how far back you can query.
  • Mixing AT and BEFORE incorrectly, like AT 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

ClauseDescriptionExample
BEFORE (TIMESTAMP => 'timestamp')Query data before a specific timestampSELECT * FROM table AT BEFORE (TIMESTAMP => '2024-06-01 12:00:00');
BEFORE (STATEMENT => statement_id)Query data before a specific statement executedSELECT * FROM table AT BEFORE (STATEMENT => '01a2b3c4d5e6f7');
ATSpecifies time travel query pointSELECT * 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.