Time Travel retention periods in Snowflake - Time & Space Complexity

Time Complexity: Time Travel retention periods

O(n)

Understanding Time Complexity

We want to understand how the cost of keeping past data versions grows as we keep more history in Snowflake's Time Travel.

Specifically, how does the retention period affect the operations needed to store and access data?

Scenario Under Consideration

Analyze the time complexity of managing Time Travel retention periods in Snowflake.

-- Set retention period for a table
ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 7;

-- Query data as of a past time
SELECT * FROM my_table AT (OFFSET => -3600);

This sequence sets how long past data is kept, queries data from the past.

Identify Repeating Operations

Look at what happens repeatedly as retention period changes.

• Primary operation: Storing historical data versions for each change.
• How many times: Once per data change, repeated over the retention period.

How Execution Grows With Input

As the retention period grows, the amount of stored past data grows roughly in proportion.

Input Size (Retention Days)	Approx. Storage & Access Operations
1	Stores 1 day's worth of changes
7	Stores 7 days' worth of changes
30	Stores 30 days' worth of changes

Pattern observation: More retention days means more stored versions, so storage and retrieval work grows linearly.

Final Time Complexity

Time Complexity: O(n)

This means the work to store and access past data grows in direct proportion to the retention period length.

Common Mistake

[X] Wrong: "Increasing retention period does not affect storage or query cost."

[OK] Correct: Longer retention means more past data versions are kept, so storage and query operations must handle more data.

Interview Connect

Understanding how retention settings affect system work shows you can think about cost and performance trade-offs in cloud data systems.

Self-Check

"What if we changed retention from days to hours? How would the time complexity change?"