Snowflake Time Travel

12/4/20231 min read

Snowflake's "Time Travel" is a feature that allows users to access historical data and track changes over time within their data warehouse. Here's a detailed explanation of how Time Travel works in Snowflake:

  1. Basics of Time Travel:

    • Snowflake offers "Time Travel" and "Fail-Safe."

    • "Time Travel" permits users to query historical data.

    • "Fail-Safe" acts as a safety net, preserving data for a set period.

  2. Time Travel Syntax:

    • Users leverage the AS OF clause in SQL queries.

    • Example: SELECT * FROM table_name AS OF TIMESTAMP '2023-01-01 00:00:00';

    • Retrieves the table's state as of January 1, 2023, at midnight.

  3. Retention Periods:

    • Time Travel adheres to retention periods.

    • Default: 24 hours for "Time Travel" and seven days for "Fail-Safe."

    • Users can extend retention as needed.

  4. Practical Example:

    • Consider a table, sales_data, recording daily transactions.

    • Query: SELECT * FROM sales_data AS OF TIMESTAMP '2023-01-01 00:00:00';

    • Retrieves data as of January 1, 2023, facilitating historical analysis.

  5. Cloning with Time Travel:

    • Users clone a table at a specific point.

    • Example: CREATE TABLE cloned_sales_data CLONE sales_data AS OF TIMESTAMP '2023-01-01 00:00:00';

    • Generates cloned_sales_data with data from sales_data on January 1, 2023.

  6. Managing Retention:

    • Users adjust Time Travel retention.

    • Example: ALTER TABLE sales_data SET MIN_RETENTION_TIME = INTERVAL '3' DAY;

    • Alters minimum retention for Time Travel on sales_data to three days.

  7. Monitoring Time Travel:

    • Snowflake offers metadata views for monitoring.

    • Example: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sales_data';

    • Inspects RETENTION_TIME_IN_DAYS to view configured retention.