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.

Practice

1. What does the AT clause do when querying historical data in Snowflake?

easy

A. Shows data exactly at a specified point in time

B. Shows data after a specified point in time

C. Shows data before a specified point in time

D. Shows the latest data ignoring time

Solution

1. Step 1: Understand the purpose of AT clause

   The AT clause is used to query data as it existed exactly at a specific timestamp.

2. Step 2: Compare with other time clauses

   Unlike BEFORE, which shows data just before a time, AT shows the exact snapshot at that time.

3. Final Answer:

   Shows data exactly at a specified point in time -> Option A

4. Quick Check:

   AT = exact time snapshot [OK]

Hint: Remember: AT means exact time snapshot, not before or after [OK]

Common Mistakes:

• Confusing AT with BEFORE clause
• Thinking AT shows data after the time
• Assuming AT ignores time and shows latest data

2. Which of the following is the correct syntax to query a table named sales as it was exactly at timestamp '2024-01-01 10:00:00' using Snowflake's time travel feature?

easy

A. SELECT * FROM sales AT '2024-01-01 10:00:00';

B. SELECT * FROM sales AT (TIMESTAMP => '2024-01-01 10:00:00'; )

C. SELECT * FROM sales AT (TIMESTAMP => '2024-01-01 10:00:00')

D. SELECT * FROM sales BEFORE (TIMESTAMP => '2024-01-01 10:00:00');

Solution

1. Step 1: Identify correct syntax for AT clause

   Snowflake requires the AT clause with parentheses and the keyword TIMESTAMP => for time travel queries.

2. Step 2: Check syntax details

   SELECT * FROM sales AT (TIMESTAMP => '2024-01-01 10:00:00') correctly uses AT (TIMESTAMP => '2024-01-01 10:00:00') without a trailing semicolon inside the query string.

3. Final Answer:

   SELECT * FROM sales AT (TIMESTAMP => '2024-01-01 10:00:00') -> Option C

4. Quick Check:

   Correct AT syntax = SELECT * FROM sales AT (TIMESTAMP => '2024-01-01 10:00:00') [OK]

Hint: Use AT (TIMESTAMP => 'timestamp') with parentheses for time travel [OK]

Common Mistakes:

• Using BEFORE instead of AT for exact time
• Omitting parentheses around TIMESTAMP clause
• Adding semicolon inside query string causing syntax error

3. Given the table orders with changes over time, what will the following query return?

SELECT COUNT(*) FROM orders BEFORE (TIMESTAMP => '2024-03-01 12:00:00');

medium

A. Count of rows just before '2024-03-01 12:00:00'

B. Count of rows after '2024-03-01 12:00:00'

C. Count of rows exactly at '2024-03-01 12:00:00'

D. Count of all current rows ignoring time

Solution

1. Step 1: Understand BEFORE clause behavior

   The BEFORE clause returns data as it existed just before the specified timestamp.

2. Step 2: Apply to COUNT query

   The query counts rows in orders table as they were immediately before '2024-03-01 12:00:00'.

3. Final Answer:

   Count of rows just before '2024-03-01 12:00:00' -> Option A

4. Quick Check:

   BEFORE = data just before time [OK]

Hint: BEFORE means data snapshot just before the timestamp [OK]

Common Mistakes:

• Confusing BEFORE with AT clause
• Assuming BEFORE returns data after the timestamp
• Thinking BEFORE returns current data

4. You wrote this query but got a syntax error:

SELECT * FROM customers AT TIMESTAMP => '2024-05-01 09:00:00';

What is the likely cause?

medium

A. Table name should be in quotes

B. Missing parentheses around the TIMESTAMP clause

C. Timestamp format is incorrect

D. Using BEFORE instead of AT

Solution

1. Step 1: Check syntax for AT clause

   The AT clause requires parentheses around the TIMESTAMP => '...' part.

2. Step 2: Identify missing parentheses

   The query misses parentheses, causing a syntax error.

3. Final Answer:

   Missing parentheses around the TIMESTAMP clause -> Option B

4. Quick Check:

   AT clause needs parentheses [OK]

Hint: Always wrap TIMESTAMP clause in parentheses after AT [OK]

Common Mistakes:

• Omitting parentheses around TIMESTAMP
• Confusing syntax with BEFORE clause
• Incorrect timestamp format causing error

5. You want to compare the number of rows in the transactions table exactly at '2024-04-15 08:00:00' and just before that time. Which pair of queries correctly achieves this?

hard

A. SELECT COUNT(*) FROM transactions BEFORE (TIMESTAMP => '2024-04-15 08:00:00');SELECT COUNT(*) FROM transactions AT TIMESTAMP => '2024-04-15 08:00:00');

B. SELECT COUNT(*) FROM transactions BEFORE (TIMESTAMP => '2024-04-15 08:00:00');SELECT COUNT(*) FROM transactions AFTER (TIMESTAMP => '2024-04-15 08:00:00');

C. SELECT COUNT(*) FROM transactions AT (TIMESTAMP => '2024-04-15 08:00:00');SELECT COUNT(*) FROM transactions AT (TIMESTAMP => '2024-04-15 07:59:59');

D. SELECT COUNT(*) FROM transactions AT (TIMESTAMP => '2024-04-15 08:00:00');SELECT COUNT(*) FROM transactions BEFORE (TIMESTAMP => '2024-04-15 08:00:00');

Solution

1. Step 1: Identify query for exact time

   Use AT (TIMESTAMP => '2024-04-15 08:00:00') to get data exactly at that timestamp.

2. Step 2: Identify query for just before time

   Use BEFORE (TIMESTAMP => '2024-04-15 08:00:00') to get data just before that timestamp.

3. Step 3: Verify option matches both queries

   SELECT COUNT(*) FROM transactions AT (TIMESTAMP => '2024-04-15 08:00:00');
   SELECT COUNT(*) FROM transactions BEFORE (TIMESTAMP => '2024-04-15 08:00:00'); correctly uses AT and BEFORE clauses as required.

4. Final Answer:

   SELECT COUNT(*) FROM transactions AT (TIMESTAMP => '2024-04-15 08:00:00');
   SELECT COUNT(*) FROM transactions BEFORE (TIMESTAMP => '2024-04-15 08:00:00'); -> Option D

5. Quick Check:

   AT = exact, BEFORE = just before [OK]

Hint: Use AT for exact time, BEFORE for just before time [OK]

Common Mistakes:

• Swapping AT and BEFORE clauses
• Using AFTER which is invalid in Snowflake time travel
• Using two AT queries with different timestamps