Querying historical data (AT, BEFORE) in Snowflake - Commands & Configuration

Introduction

Sometimes you need to see what your data looked like at a specific time or before a certain moment. Snowflake lets you do this easily by querying historical data using time travel features.

When you accidentally deleted or changed data and want to see the original version.

When you want to compare today's data with how it was yesterday or last week.

When you need to audit changes made to your data over time.

When you want to restore data to a previous state after a mistake.

When you want to analyze trends by looking at snapshots of data at different times.

Commands

This command retrieves the data from 'my_table' exactly as it was at 10 AM on June 1, 2024. It uses Snowflake's time travel feature to see the table's state at that moment.

Terminal

SELECT * FROM my_table AT (TIMESTAMP => TO_TIMESTAMP('2024-06-01 10:00:00'));

Expected Output

ID | NAME  | VALUE

1  | apple | 100

2  | orange| 150

3  | banana| 120

This command fetches the data from 'my_table' as it was just before the statement executed at 10 AM on June 1, 2024. It helps to see the data state before a change happened.

Terminal

SELECT * FROM my_table BEFORE (STATEMENT => '2024-06-01 10:00:00');

Expected Output

ID | NAME  | VALUE

1  | apple | 90

2  | orange| 140

3  | banana| 110

This command shows the data from 'my_table' as it was one hour ago (3600 seconds before the current time). It's useful to quickly check recent past data.

Terminal

SELECT * FROM my_table AT (OFFSET => -3600);

Expected Output

ID | NAME  | VALUE

1  | apple | 95

2  | orange| 145

3  | banana| 115

Key Concept

If you remember nothing else from this pattern, remember: Snowflake lets you query your data as it was at or before a specific time using time travel syntax.

Common Mistakes

Using incorrect timestamp format in the AT clause.

Snowflake requires timestamps in a specific format; wrong format causes errors or no results.

Always use TO_TIMESTAMP() or a valid timestamp string like 'YYYY-MM-DD HH24:MI:SS'.

Trying to query historical data beyond Snowflake's retention period.

Snowflake only keeps historical data for a limited time (default 1 day, can be extended). Queries beyond that fail.

Check your account's time travel retention period and query only within that window.

Confusing AT and BEFORE clauses and expecting the same results.

AT shows data exactly at the timestamp; BEFORE shows data just before a statement executed, which can differ.

Use AT for exact time snapshots and BEFORE to see data before a change.

Summary

Use the AT clause to query data as it was at a specific timestamp.

Use the BEFORE clause to see data just before a statement executed.

Use OFFSET to query data relative to the current time.

Ensure timestamps are correctly formatted and within retention limits.