Why Time Travel enables data recovery in Snowflake - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does Time Travel help recover deleted data in Snowflake?

Snowflake's Time Travel feature allows you to access historical data. Which statement best explains how Time Travel enables data recovery?

A It creates a snapshot of the database only when a manual backup is triggered.

B It automatically backs up data to an external storage every hour.

C It keeps a copy of all data changes for a set period, allowing queries on past data states.

D It stores deleted data permanently in a separate archive that must be restored manually.

💡 Hint

Think about how you can query data as it was before a change.

Time Travel keeps track of all data changes for a configurable retention period. This allows users to query or restore data as it existed at any point within that period, enabling easy recovery from accidental deletes or updates.

❓ service_behavior

intermediate

What happens when you query a table using Time Travel in Snowflake?

You run a query on a Snowflake table using the AT clause to specify a past timestamp. What is the expected behavior?

A The query returns the table data as it existed exactly at the specified timestamp.

B The query returns the current data but adds a timestamp column showing the past time.

C The query fails because Time Travel only works for deleted tables, not queries.

D The query returns an empty result if the timestamp is older than 24 hours.

💡 Hint

Consider how Time Travel lets you see data from the past.

Using the AT clause with a timestamp lets you query the table as it was at that exact time, showing historical data within the retention period.

❓ Architecture

advanced

How does Snowflake's Time Travel architecture support data recovery without extra storage costs?

Snowflake uses a unique architecture for Time Travel. Which architectural feature allows Time Travel to enable data recovery efficiently?

A It stores only the changes (deltas) between data versions, not full copies of data.

B It duplicates the entire database every hour to a separate storage location.

C It requires users to manually save snapshots to enable recovery.

D It compresses all historical data into a single archive file updated daily.

💡 Hint

Think about how storing only differences can save space.

Snowflake stores only the changes between data versions (deltas) rather than full copies. This approach reduces storage needs while allowing reconstruction of past data states for recovery.

❓ security

advanced

What security consideration is important when using Time Travel for data recovery?

When recovering data using Time Travel, which security practice is essential to protect sensitive historical data?

A Disable Time Travel to prevent unauthorized access to old data versions.

B Allow all users to access Time Travel data for transparency.

C Encrypt only current data, as historical data is automatically secure.

D Ensure access controls and permissions apply equally to historical data accessed via Time Travel.

💡 Hint

Think about who can see past data versions.

Access controls must apply to historical data accessed through Time Travel to prevent unauthorized viewing or recovery of sensitive information.

✅ Best Practice

expert

What is the best practice for setting Time Travel retention period to balance recovery and cost?

Snowflake allows configuring the Time Travel retention period. Which option best balances data recovery needs and storage cost?

A Always set the retention period to the maximum allowed to keep all historical data indefinitely.

B Set the retention period to the minimum required by your recovery policy, avoiding unnecessary storage costs.

C Disable Time Travel and rely only on manual backups to reduce costs.

D Set retention to zero and enable continuous data replication instead.

💡 Hint

Consider cost vs. recovery needs.

Setting the retention period to the minimum needed ensures you can recover data within your policy while controlling storage costs. Longer retention increases costs without always adding value.

Practice

1. What is the main benefit of Snowflake's Time Travel feature?

easy

A. It automatically backs up data to an external system.

B. It speeds up query performance by caching results.

C. It encrypts data to enhance security.

D. It allows you to recover data from a previous point in time.

Solution

1. Step 1: Understand Time Travel purpose

   Time Travel lets you access data as it was at a past time, enabling recovery.

2. Step 2: Compare options

   Only recovering past data matches Time Travel's main benefit; others describe different features.

3. Final Answer:

   It allows you to recover data from a previous point in time. -> Option D

4. Quick Check:

   Time Travel = Data recovery [OK]

Hint: Time Travel means going back to old data states [OK]

Common Mistakes:

• Confusing Time Travel with backup or encryption
• Thinking it improves query speed
• Assuming it stores data outside Snowflake

2. Which SQL syntax correctly queries data from 2 hours ago using Time Travel?

easy

A. SELECT * FROM table BEFORE (TIMESTAMP => CURRENT_TIMESTAMP - INTERVAL '2' HOUR);

B. SELECT * FROM table AT (TIMESTAMP => DATEADD(hour, -2, CURRENT_TIMESTAMP));

C. SELECT * FROM table AT (OFFSET => -7200);

D. SELECT * FROM table FOR TIME TRAVEL 2 HOURS AGO;

Solution

1. Step 1: Recall correct Time Travel syntax

   Snowflake uses AT (TIMESTAMP => ...) to query past data.

2. Step 2: Analyze options

   SELECT * FROM table AT (TIMESTAMP => DATEADD(hour, -2, CURRENT_TIMESTAMP)); uses DATEADD to subtract 2 hours from current time correctly; others use invalid or unsupported syntax.

3. Final Answer:

   SELECT * FROM table AT (TIMESTAMP => DATEADD(hour, -2, CURRENT_TIMESTAMP)); -> Option B

4. Quick Check:

   Use AT (TIMESTAMP => ...) with DATEADD for past data [OK]

Hint: Use AT (TIMESTAMP => DATEADD(...)) to query past data [OK]

Common Mistakes:

• Using unsupported keywords like BEFORE or FOR TIME TRAVEL
• Incorrect interval syntax
• Using OFFSET with negative seconds (not valid)

3. Given the table users had a row deleted 1 hour ago, what will this query return?
SELECT * FROM users AT (TIMESTAMP => DATEADD(hour, -1, CURRENT_TIMESTAMP));

medium

A. The table data including the deleted row as it was 1 hour ago.

B. An error because Time Travel cannot query deleted rows.

C. Only the deleted row from 1 hour ago.

D. The table data as it is now, without the deleted row.

Solution

1. Step 1: Understand Time Travel query effect

   Querying with AT (TIMESTAMP => ...) shows data as it was at that time, including deleted rows.

2. Step 2: Interpret the query

   The query asks for data 1 hour ago, so it returns all rows including the deleted one.

3. Final Answer:

   The table data including the deleted row as it was 1 hour ago. -> Option A

4. Quick Check:

   Time Travel shows past data including deleted rows [OK]

Hint: Time Travel shows full past data, not just current state [OK]

Common Mistakes:

• Assuming deleted rows are inaccessible
• Expecting only deleted rows returned
• Thinking it causes query errors

4. You tried to restore a dropped table using Time Travel but got an error. What is a likely cause?

medium

A. The Time Travel retention period has expired for that table.

B. You used the wrong SQL syntax for Time Travel.

C. The table was never created in the first place.

D. Time Travel does not support restoring dropped tables.

Solution

1. Step 1: Recall Time Travel retention limits

   Time Travel keeps data only for a set retention period (default 1 day).

2. Step 2: Analyze error cause

   If retention expired, data cannot be recovered, causing errors when restoring dropped tables.

3. Final Answer:

   The Time Travel retention period has expired for that table. -> Option A

4. Quick Check:

   Expired retention blocks recovery [OK]

Hint: Check retention period before restoring dropped tables [OK]

Common Mistakes:

• Assuming Time Travel keeps data forever
• Blaming syntax errors without checking retention
• Thinking dropped tables can't be restored at all

5. You accidentally updated many rows in a table 3 hours ago and want to recover the original data. Your Time Travel retention is 24 hours. Which approach correctly recovers the data?

hard

A. Run INSERT INTO table SELECT * FROM table AT (TIMESTAMP => DATEADD(hour, -3, CURRENT_TIMESTAMP)); to overwrite current data.

B. Run DELETE FROM table WHERE update_time < DATEADD(hour, -3, CURRENT_TIMESTAMP); to undo changes.

C. Use CREATE TABLE new_table AS SELECT * FROM table AT (TIMESTAMP => DATEADD(hour, -3, CURRENT_TIMESTAMP)); and replace the original table after verification.

D. Restore the entire database to the state 3 hours ago using Time Travel.

Solution

1. Step 1: Understand safe recovery method

   Directly overwriting current data risks data loss; safer to create a new table snapshot first.

2. Step 2: Verify and replace

   Creating a new table from past data lets you verify before replacing the original table.

3. Step 3: Analyze other options

   Run INSERT INTO table SELECT * FROM table AT (TIMESTAMP => DATEADD(hour, -3, CURRENT_TIMESTAMP)); to overwrite current data risks duplicate data; C does not revert updates; D is not supported at database level.

4. Final Answer:

   Use CREATE TABLE new_table AS SELECT * FROM table AT (TIMESTAMP => DATEADD(hour, -3, CURRENT_TIMESTAMP)); and replace the original table after verification. -> Option C

5. Quick Check:

   Recover by snapshot then replace [OK]

Hint: Recover past data into new table before replacing [OK]

Common Mistakes:

• Overwriting current data without backup
• Trying unsupported database-wide restore
• Using DELETE to undo updates