Time Travel retention periods in Snowflake - Mini Project: Build & Apply

Time Travel Retention Periods in Snowflake

📖 Scenario: You are managing a Snowflake data warehouse for a retail company. The company wants to keep historical data changes available for recovery and auditing using Snowflake's Time Travel feature.

🎯 Goal: Configure a Snowflake table with a specific Time Travel retention period and verify the configuration.

📋 What You'll Learn

Create a table named sales_data with columns order_id (integer) and amount (number).

Set a Time Travel retention period of 3 days for the sales_data table.

Query the table properties to confirm the retention period is set correctly.

Use valid Snowflake SQL syntax and best practices.

💡 Why This Matters

🌍 Real World

Time Travel retention periods help companies recover data accidentally deleted or changed, ensuring data safety and auditability.

💼 Career

Cloud data engineers and database administrators often configure Time Travel settings to meet business data retention policies.

1

Create the sales_data table

Write a Snowflake SQL statement to create a table called sales_data with two columns: order_id as INTEGER and amount as NUMBER.

Snowflake

-- Your code here to create the sales_data table

Need a hint?

Use CREATE TABLE followed by the table name and column definitions inside parentheses.

2

Set Time Travel retention period to 3 days

Write a Snowflake SQL statement to alter the sales_data table and set its Time Travel retention period to 3 days using DATA_RETENTION_TIME_IN_DAYS.

Snowflake

CREATE TABLE sales_data (
    order_id INTEGER,
    amount NUMBER
);
-- Your code here to set retention period

Need a hint?

Use ALTER TABLE with SET DATA_RETENTION_TIME_IN_DAYS = 3 to configure retention.

3

Query the retention period property

Write a Snowflake SQL query to select the data_retention_time_in_days property from the information_schema.tables for the sales_data table in your current schema.

Snowflake

CREATE TABLE sales_data (
    order_id INTEGER,
    amount NUMBER
);

ALTER TABLE sales_data SET DATA_RETENTION_TIME_IN_DAYS = 3;

-- Your code here to query retention period

Need a hint?

Use information_schema.tables and filter by table_name and table_schema.

4

Complete the Time Travel retention setup

Add a comment line at the end of your SQL script stating -- Time Travel retention period set to 3 days for sales_data table to document your configuration.

Snowflake

CREATE TABLE sales_data (
    order_id INTEGER,
    amount NUMBER
);

ALTER TABLE sales_data SET DATA_RETENTION_TIME_IN_DAYS = 3;

SELECT data_retention_time_in_days
FROM information_schema.tables
WHERE table_name = 'SALES_DATA'
  AND table_schema = CURRENT_SCHEMA();

-- Your comment here

Need a hint?

Add a comment line starting with -- describing the retention period setting.