Why Time Travel enables data recovery in Snowflake - See It in Action

Why Time Travel enables data recovery

📖 Scenario: You are a data engineer working with Snowflake, a cloud data platform. Your team accidentally deleted some important data from a table. Luckily, Snowflake has a feature called Time Travel that lets you recover data from the past.In this project, you will create a simple table, insert data, configure Time Travel settings, and then use Time Travel to recover deleted data.

🎯 Goal: Build a Snowflake setup that demonstrates how Time Travel can recover deleted data from a table.

📋 What You'll Learn

Create a table named employees with columns id (integer) and name (string).

Insert three rows of employee data into the employees table.

Set the Time Travel retention period to 1 day for the employees table.

Use Time Travel to recover the employees table data after deleting one row.

💡 Why This Matters

🌍 Real World

Time Travel helps data engineers recover accidentally deleted or changed data without needing backups.

💼 Career

Understanding Time Travel is essential for managing data safety and recovery in cloud data platforms like Snowflake.

1

Create the employees table

Write a SQL statement to create a table called employees with two columns: id as INTEGER and name as STRING.

Snowflake

-- Your code here to create the employees table

Need a hint?

Use CREATE TABLE employees (id INTEGER, name STRING); to create the table.

2

Insert employee data

Insert three rows into the employees table with these exact values: (1, 'Alice'), (2, 'Bob'), and (3, 'Charlie').

Snowflake

CREATE TABLE employees (id INTEGER, name STRING);
-- Your code here to insert three rows

Need a hint?

Use INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

3

Set Time Travel retention period

Alter the employees table to set the Time Travel retention period to 1 day using DATA_RETENTION_TIME_IN_DAYS = 1.

Snowflake

CREATE TABLE employees (id INTEGER, name STRING);
INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
-- Your code here to set Time Travel retention

Need a hint?

Use ALTER TABLE employees SET DATA_RETENTION_TIME_IN_DAYS = 1; to configure Time Travel.

4

Recover deleted data using Time Travel

Delete the row where id = 2 from employees. Then write a query to select all rows from employees as it was before the deletion using Time Travel with AT (OFFSET => -1).

Snowflake

CREATE TABLE employees (id INTEGER, name STRING);
INSERT INTO employees (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
ALTER TABLE employees SET DATA_RETENTION_TIME_IN_DAYS = 1;
-- Your code here to delete and recover data using Time Travel

Need a hint?

Use DELETE FROM employees WHERE id = 2; to remove the row, then SELECT * FROM employees AT (OFFSET => -1); to see the data before deletion.