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

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

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

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

Hint

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

Practice

1. What does the Time Travel retention period in Snowflake control?

easy

A. How long you can access previous versions of data

B. How long data is stored in the cloud

C. The speed of query execution

D. The number of users who can access a table

Solution

1. Step 1: Understand Time Travel feature

   Time Travel allows you to see and restore past versions of your data.

2. Step 2: Identify what retention period controls

   The retention period sets how many days you can access these past versions.

3. Final Answer:

   How long you can access previous versions of data -> Option A

4. Quick Check:

   Retention period = access duration for past data [OK]

Hint: Retention period means how long past data is accessible [OK]

Common Mistakes:

• Confusing retention with storage duration
• Thinking it controls query speed
• Assuming it limits user access

2. Which SQL command correctly sets the Time Travel retention period to 7 days for a table named sales_data?

easy

A. ALTER TABLE sales_data SET RETENTION_DAYS = 7;

B. ALTER TABLE sales_data SET DATA_RETENTION_TIME_IN_DAYS = 7;

C. UPDATE TABLE sales_data SET DATA_RETENTION_TIME_IN_DAYS = 7;

D. ALTER TABLE sales_data DATA_RETENTION = 7;

Solution

1. Step 1: Recall correct syntax for setting retention

   The correct command uses ALTER TABLE with SET DATA_RETENTION_TIME_IN_DAYS.

2. Step 2: Match syntax with options

   ALTER TABLE sales_data SET DATA_RETENTION_TIME_IN_DAYS = 7; matches the exact syntax; others use incorrect keywords or commands.

3. Final Answer:

   ALTER TABLE sales_data SET DATA_RETENTION_TIME_IN_DAYS = 7; -> Option B

4. Quick Check:

   Use ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS [OK]

Hint: Use ALTER TABLE ... SET DATA_RETENTION_TIME_IN_DAYS [OK]

Common Mistakes:

• Using UPDATE instead of ALTER TABLE
• Wrong keyword like RETENTION_DAYS
• Missing SET keyword

3. Given a table with a Time Travel retention period of 3 days, what happens if you try to query data from 5 days ago using Time Travel?

medium

A. You get an error because data older than 3 days is not available

B. You get the data from 5 days ago without issues

C. The query runs but returns empty results

D. The retention period automatically extends to 5 days

Solution

1. Step 1: Understand retention period limit

   Retention period limits how far back you can access data versions.

2. Step 2: Apply to 5 days query with 3 days retention

   Since 5 days is beyond 3 days retention, data is unavailable and causes an error.

3. Final Answer:

   You get an error because data older than 3 days is not available -> Option A

4. Quick Check:

   Query beyond retention period = error [OK]

Hint: Cannot access data older than retention days [OK]

Common Mistakes:

• Assuming data is always available
• Thinking query returns empty instead of error
• Believing retention auto-extends

4. You tried to set the Time Travel retention period to 100 days on a table, but Snowflake returned an error. What is the most likely cause?

medium

A. You need to restart the Snowflake service

B. Syntax error in the ALTER TABLE command

C. Table does not support Time Travel

D. Retention period exceeds the maximum allowed limit

Solution

1. Step 1: Know Snowflake retention limits

   Snowflake limits Time Travel retention to a maximum (usually 1 to 90 days depending on edition).

2. Step 2: Check if 100 days exceeds limit

   100 days is beyond typical max, causing an error.

3. Final Answer:

   Retention period exceeds the maximum allowed limit -> Option D

4. Quick Check:

   Retention > max limit causes error [OK]

Hint: Check max retention days allowed before setting [OK]

Common Mistakes:

• Assuming syntax error without checking limits
• Thinking table lacks Time Travel support
• Believing service restart fixes retention errors

5. A company wants to keep Time Travel data for 14 days but minimize storage costs. Which approach best balances these needs?

hard

A. Set DATA_RETENTION_TIME_IN_DAYS to 30 to avoid data loss

B. Set DATA_RETENTION_TIME_IN_DAYS to 1 and rely on backups for older data

C. Set DATA_RETENTION_TIME_IN_DAYS to 14 and regularly purge old data

D. Disable Time Travel and keep manual snapshots

Solution

1. Step 1: Understand retention vs storage cost

   Longer retention means more storage cost; shorter retention saves cost but limits recovery.

2. Step 2: Evaluate options for 14-day retention and cost balance

   Setting retention to 14 days meets recovery needs; purging old data helps control storage.

3. Final Answer:

   Set DATA_RETENTION_TIME_IN_DAYS to 14 and regularly purge old data -> Option C

4. Quick Check:

   Retention matches need + purge controls cost [OK]

Hint: Match retention days to need, purge to save cost [OK]

Common Mistakes:

• Setting retention too low losing data
• Setting retention too high increasing cost
• Disabling Time Travel losing recovery benefits