Querying historical data (AT, BEFORE) in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Querying data at a specific point in time

You want to query the state of a table named orders exactly as it was at the timestamp '2024-01-01 12:00:00'. Which SQL query will return the correct historical data snapshot in Snowflake?

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

B SELECT * FROM orders AT (OFFSET => '2024-01-01 12:00:00');

C SELECT * FROM orders AT (TIMESTAMP => '2024-01-01 12:00:00');

D SELECT * FROM orders FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00';

💡 Hint

Use the Snowflake syntax that queries the table state exactly at a given timestamp.

In Snowflake, the AT (TIMESTAMP => ...) clause queries the table as it existed exactly at the specified timestamp. The BEFORE clause queries data before that timestamp, and FOR SYSTEM_TIME AS OF is not valid syntax in Snowflake.

❓ service_behavior

intermediate

Querying data before a specific timestamp

You want to retrieve all rows from the customers table as they existed strictly before '2024-03-15 08:00:00'. Which query will correctly return this data in Snowflake?

A SELECT * FROM customers BEFORE (TIMESTAMP => '2024-03-15 08:00:00');

B SELECT * FROM customers AT (TIMESTAMP => '2024-03-15 08:00:00');

C SELECT * FROM customers WHERE created_at < '2024-03-15 08:00:00';

D SELECT * FROM customers FOR SYSTEM_TIME BEFORE '2024-03-15 08:00:00';

💡 Hint

Use the Snowflake clause that returns data before a given timestamp.

The BEFORE (TIMESTAMP => ...) clause in Snowflake returns the table state strictly before the specified timestamp. Filtering by created_at is not reliable for time travel queries, and FOR SYSTEM_TIME is not valid in Snowflake.

🧠 Conceptual

advanced

Understanding Snowflake Time Travel retention

What is the default maximum time travel retention period for standard Snowflake accounts, during which historical data can be queried using AT or BEFORE clauses?

A 90 days

B 30 days

C 7 days

D 1 day

💡 Hint

Think about how long Snowflake keeps historical data by default for querying.

Snowflake's default Time Travel retention period is 1 day (24 hours) for all accounts, including Standard Edition accounts. For Standard Edition, the maximum retention period is limited to 1 day and cannot be extended. Enterprise Edition (and higher) allows up to 90 days. The 7-day period refers to Fail-safe retention, which is separate and not queryable using AT or BEFORE.

❓ Architecture

advanced

Designing a data recovery strategy using Snowflake Time Travel

You want to design a recovery plan for accidental data deletion in a Snowflake table. Which approach best uses Snowflake's time travel feature to restore data to a point before deletion?

A Use <code>ROLLBACK TRANSACTION</code> to undo the deletion immediately.

B Use <code>SELECT * FROM table AT (TIMESTAMP => ...)</code> to query historical data, then insert it back into the table.

C Use <code>RESTORE TABLE table TO BEFORE DELETE</code> command to revert the table automatically.

D Use <code>SELECT * FROM table WHERE deleted = FALSE</code> to filter out deleted rows.

💡 Hint

Snowflake does not have a direct restore command but allows querying past data states.

Snowflake's time travel allows querying the table as it existed at a previous timestamp using the AT clause. To recover deleted data, you query the historical data and then re-insert it. There is no direct RESTORE TABLE command or ROLLBACK TRANSACTION in Snowflake for this purpose. Filtering by a deleted flag depends on your schema and does not use time travel.

❓ security

expert

Access control for querying historical data in Snowflake

Which Snowflake privilege is required for a user to query a table's historical data using the AT or BEFORE clauses?

A USAGE privilege on the database and schema, plus SELECT privilege on the table

B OWNERSHIP privilege on the table only

C MONITOR privilege on the account

D CREATE STAGE privilege on the schema

💡 Hint

Think about the privileges needed to read data from a table, including historical data.

To query historical data using time travel, a user needs the SELECT privilege on the table and USAGE privilege on the database and schema. OWNERSHIP is not required just to query data. MONITOR and CREATE STAGE privileges are unrelated to querying table data.

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