Querying historical data (AT, BEFORE) in Snowflake - Step-by-Step Execution

Process Flow - Querying historical data (AT, BEFORE)

Start Query

↓

Specify Time Travel Point

↓

Use AT or BEFORE clause

↓

Snowflake fetches data snapshot

↓

Return historical data

↓

End Query

The query starts by specifying a time travel point using AT or BEFORE, then Snowflake fetches the data snapshot from that time and returns it.

Execution Sample

Snowflake

SELECT * FROM my_table AT (TIMESTAMP => '2024-06-01 10:00:00');

This query fetches the state of 'my_table' exactly at the specified timestamp.

Process Table

Step	Action	Clause Used	Timestamp	Result
1	Start query execution	N/A	N/A	Query begins
2	Identify time travel clause	AT	2024-06-01 10:00:00	Clause recognized
3	Fetch data snapshot	AT	2024-06-01 10:00:00	Data as of exact timestamp fetched
4	Return data	AT	2024-06-01 10:00:00	Historical data returned to user
5	End query	N/A	N/A	Query completes successfully

💡 Query ends after returning the historical data snapshot at the specified timestamp.

Status Tracker

Variable	Start	After Step 2	After Step 3	Final
time_travel_clause	None	AT	AT	AT
timestamp_value	None	2024-06-01 10:00:00	2024-06-01 10:00:00	2024-06-01 10:00:00
data_snapshot	None	None	Data at 2024-06-01 10:00:00	Data at 2024-06-01 10:00:00

Key Moments - 2 Insights

Why does the query use AT instead of BEFORE to get data?

Using AT fetches data exactly at the specified timestamp, while BEFORE fetches data just before that time. See execution_table step 3 where AT clause fetches exact snapshot.

What happens if the timestamp is not valid or too old?

Snowflake returns an error or empty result because data older than the retention period is not available. This is implied in step 3 when fetching data snapshot.

Visual Quiz - 3 Questions

Look at the execution_table, what clause is recognized at step 2?

A AFTER

B BEFORE

C AT

D NONE

💡 Hint

Check the 'Clause Used' column in execution_table row for step 2.

At which step does Snowflake fetch the data snapshot?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Action' column in execution_table to find when data is fetched.

If the query used BEFORE instead of AT, how would the 'Result' in step 3 change?

A Data just before the timestamp fetched

B Data as of exact timestamp fetched

C Data after the timestamp fetched

D No data fetched

💡 Hint

Compare the meaning of AT vs BEFORE clauses in the concept_flow and execution_table.

Concept Snapshot

Snowflake time travel lets you query past data states.
Use AT (TIMESTAMP => 'time') to get data exactly at that time.
Use BEFORE (TIMESTAMP => 'time') to get data just before that time.
Snowflake returns the snapshot of data as it was then.
Useful for recovering or auditing historical data.

Full Transcript

This visual execution shows how Snowflake queries historical data using the AT or BEFORE clauses. The query starts, identifies the time travel clause and timestamp, then fetches the data snapshot from that exact or just before time. Finally, it returns the historical data to the user and ends. Variables like the clause and timestamp are set early and used to fetch the snapshot. Key points include the difference between AT and BEFORE, and that data must be within retention period. The quiz tests understanding of these steps and clause effects.