Bird
Raised Fist0
Snowflakecloud~5 mins

Querying historical data (AT, BEFORE) in Snowflake - Commands & Configuration

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction
Sometimes you need to see what your data looked like at a specific time or before a certain moment. Snowflake lets you do this easily by querying historical data using time travel features.
When you accidentally deleted or changed data and want to see the original version.
When you want to compare today's data with how it was yesterday or last week.
When you need to audit changes made to your data over time.
When you want to restore data to a previous state after a mistake.
When you want to analyze trends by looking at snapshots of data at different times.
Commands
This command retrieves the data from 'my_table' exactly as it was at 10 AM on June 1, 2024. It uses Snowflake's time travel feature to see the table's state at that moment.
Terminal
SELECT * FROM my_table AT (TIMESTAMP => TO_TIMESTAMP('2024-06-01 10:00:00'));
Expected OutputExpected
ID | NAME | VALUE 1 | apple | 100 2 | orange| 150 3 | banana| 120
This command fetches the data from 'my_table' as it was just before the statement executed at 10 AM on June 1, 2024. It helps to see the data state before a change happened.
Terminal
SELECT * FROM my_table BEFORE (STATEMENT => '2024-06-01 10:00:00');
Expected OutputExpected
ID | NAME | VALUE 1 | apple | 90 2 | orange| 140 3 | banana| 110
This command shows the data from 'my_table' as it was one hour ago (3600 seconds before the current time). It's useful to quickly check recent past data.
Terminal
SELECT * FROM my_table AT (OFFSET => -3600);
Expected OutputExpected
ID | NAME | VALUE 1 | apple | 95 2 | orange| 145 3 | banana| 115
Key Concept

If you remember nothing else from this pattern, remember: Snowflake lets you query your data as it was at or before a specific time using time travel syntax.

Common Mistakes
Using incorrect timestamp format in the AT clause.
Snowflake requires timestamps in a specific format; wrong format causes errors or no results.
Always use TO_TIMESTAMP() or a valid timestamp string like 'YYYY-MM-DD HH24:MI:SS'.
Trying to query historical data beyond Snowflake's retention period.
Snowflake only keeps historical data for a limited time (default 1 day, can be extended). Queries beyond that fail.
Check your account's time travel retention period and query only within that window.
Confusing AT and BEFORE clauses and expecting the same results.
AT shows data exactly at the timestamp; BEFORE shows data just before a statement executed, which can differ.
Use AT for exact time snapshots and BEFORE to see data before a change.
Summary
Use the AT clause to query data as it was at a specific timestamp.
Use the BEFORE clause to see data just before a statement executed.
Use OFFSET to query data relative to the current time.
Ensure timestamps are correctly formatted and within retention limits.

Practice

(1/5)
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
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard