Querying historical data (AT, BEFORE) in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Querying data at a specific point in time

You want to query the state of a table named orders exactly as it was at the timestamp '2024-01-01 12:00:00'. Which SQL query will return the correct historical data snapshot in Snowflake?

A SELECT * FROM orders BEFORE (TIMESTAMP => '2024-01-01 12:00:00');

B SELECT * FROM orders AT (OFFSET => '2024-01-01 12:00:00');

C SELECT * FROM orders AT (TIMESTAMP => '2024-01-01 12:00:00');

D SELECT * FROM orders FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00';

💡 Hint

Use the Snowflake syntax that queries the table state exactly at a given timestamp.

In Snowflake, the AT (TIMESTAMP => ...) clause queries the table as it existed exactly at the specified timestamp. The BEFORE clause queries data before that timestamp, and FOR SYSTEM_TIME AS OF is not valid syntax in Snowflake.

❓ service_behavior

intermediate

Querying data before a specific timestamp

You want to retrieve all rows from the customers table as they existed strictly before '2024-03-15 08:00:00'. Which query will correctly return this data in Snowflake?

A SELECT * FROM customers BEFORE (TIMESTAMP => '2024-03-15 08:00:00');

B SELECT * FROM customers AT (TIMESTAMP => '2024-03-15 08:00:00');

C SELECT * FROM customers WHERE created_at < '2024-03-15 08:00:00';

D SELECT * FROM customers FOR SYSTEM_TIME BEFORE '2024-03-15 08:00:00';

💡 Hint

Use the Snowflake clause that returns data before a given timestamp.

The BEFORE (TIMESTAMP => ...) clause in Snowflake returns the table state strictly before the specified timestamp. Filtering by created_at is not reliable for time travel queries, and FOR SYSTEM_TIME is not valid in Snowflake.

🧠 Conceptual

advanced

Understanding Snowflake Time Travel retention

What is the default maximum time travel retention period for standard Snowflake accounts, during which historical data can be queried using AT or BEFORE clauses?

A 90 days

B 30 days

C 7 days

D 1 day

💡 Hint

Think about how long Snowflake keeps historical data by default for querying.

Snowflake's default Time Travel retention period is 1 day (24 hours) for all accounts, including Standard Edition accounts. For Standard Edition, the maximum retention period is limited to 1 day and cannot be extended. Enterprise Edition (and higher) allows up to 90 days. The 7-day period refers to Fail-safe retention, which is separate and not queryable using AT or BEFORE.

❓ Architecture

advanced

Designing a data recovery strategy using Snowflake Time Travel

You want to design a recovery plan for accidental data deletion in a Snowflake table. Which approach best uses Snowflake's time travel feature to restore data to a point before deletion?

A Use <code>ROLLBACK TRANSACTION</code> to undo the deletion immediately.

B Use <code>SELECT * FROM table AT (TIMESTAMP => ...)</code> to query historical data, then insert it back into the table.

C Use <code>RESTORE TABLE table TO BEFORE DELETE</code> command to revert the table automatically.

D Use <code>SELECT * FROM table WHERE deleted = FALSE</code> to filter out deleted rows.

💡 Hint

Snowflake does not have a direct restore command but allows querying past data states.

Snowflake's time travel allows querying the table as it existed at a previous timestamp using the AT clause. To recover deleted data, you query the historical data and then re-insert it. There is no direct RESTORE TABLE command or ROLLBACK TRANSACTION in Snowflake for this purpose. Filtering by a deleted flag depends on your schema and does not use time travel.

❓ security

expert

Access control for querying historical data in Snowflake

Which Snowflake privilege is required for a user to query a table's historical data using the AT or BEFORE clauses?

A USAGE privilege on the database and schema, plus SELECT privilege on the table

B OWNERSHIP privilege on the table only

C MONITOR privilege on the account

D CREATE STAGE privilege on the schema

💡 Hint

Think about the privileges needed to read data from a table, including historical data.

To query historical data using time travel, a user needs the SELECT privilege on the table and USAGE privilege on the database and schema. OWNERSHIP is not required just to query data. MONITOR and CREATE STAGE privileges are unrelated to querying table data.