Querying historical data (AT, BEFORE) in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the AT clause do in Snowflake time travel queries?

The AT clause lets you query the exact state of a table at a specific point in time in the past.

intermediate

How does the BEFORE clause differ from AT in Snowflake?

BEFORE returns the state of the data just before a specified time or query ID, while AT returns the state exactly at that time.

beginner

What is the maximum time travel retention period in Snowflake by default?

By default, Snowflake retains historical data for 1 day (24 hours) for time travel queries.

intermediate

Write a simple Snowflake query to get the state of a table named orders exactly 2 hours ago.

SELECT * FROM orders AT (TIMESTAMP => DATEADD(hour, -2, CURRENT_TIMESTAMP()));

beginner

Why is querying historical data useful in cloud data warehouses like Snowflake?

It helps recover lost or changed data, audit changes, and analyze past states without restoring backups.

What does the AT clause in Snowflake time travel do?

A Deletes data at a specified time

B Shows data before a specified past time

C Shows current data only

D Shows data exactly at a specified past time

AT returns the exact state of data at a given past timestamp.

Which clause returns data just before a specified time in Snowflake?

A AT

B SINCE

C BEFORE

D AFTER

BEFORE returns the state of data immediately before the specified time.

What is the default time travel retention period in Snowflake?

A 12 hours

B 24 hours

C 1 hour

D 7 days

Snowflake retains historical data for 24 hours by default.

Which of these is a valid Snowflake query to get data 30 minutes ago?

A SELECT * FROM table AT (TIMESTAMP => DATEADD(minute, -30, CURRENT_TIMESTAMP()));

B SELECT * FROM table BEFORE (TIMESTAMP => DATEADD(minute, -30, CURRENT_TIMESTAMP()));

C SELECT * FROM table SINCE (TIMESTAMP => DATEADD(minute, -30, CURRENT_TIMESTAMP()));

D SELECT * FROM table AFTER (TIMESTAMP => DATEADD(minute, -30, CURRENT_TIMESTAMP()));

AT with DATEADD gets the exact state at 30 minutes ago.

Why use Snowflake time travel to query historical data?

A To recover or audit past data states

B To delete old data

C To speed up current queries

D To create new tables

Time travel helps recover or audit data as it was in the past.

Explain how you would use the AT and BEFORE clauses in Snowflake to query historical data.

Think about recovering data or auditing changes.

Describe a real-life scenario where querying historical data with Snowflake time travel would be helpful.

Imagine you lost some important data or want to check what changed.