Which queries will allow the user to view the historical data that was in the table before this query was executed?

MY_TABLE is a table that has not been updated or modified for several days. On 01 January 2021 at 07:01, a user executed a query to update this table.

The query ID is ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726’. It is now 07:30 on the same day.

Which queries will allow the user to view the historical data that was in the table before this query was executed? (Select THREE).

A. SELECT * FROM my table WITH TIME_TRAVEL (OFFSET => -60*30);

B. SELECT * FROM my_table AT (TIMESTAMP => ‘2021-01-01 07:00:00’ :: timestamp);

C. SELECT * FROM TIME_TRAVEL (‘MY_TABLE’, 2021-01-01 07:00:00);

D. SELECT * FROM my table PRIOR TO STATEMENT ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726’;

E. SELECT * FROM my_table AT (OFFSET => -60*30);

F. SELECT * FROM my_table BEFORE (STATEMENT => ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726’);

View Answer

Answer: BDF

Explanation:

According to the AT | BEFORE documentation, the AT or BEFORE clause is used for Snowflake Time Travel, which allows you to query historical data from a table based on a specific point in the past.

The clause can use one of the following parameters to pinpoint the exact historical data you wish to access:

• TIMESTAMP: Specifies an exact date and time to use for Time Travel.

• OFFSET: Specifies the difference in seconds from the current time to use for Time Travel.

• STATEMENT: Specifies the query ID of a statement to use as the reference point for Time

Travel.

Therefore, the queries that will allow the user to view the historical data that was in the table before the query was executed are:

• B. SELECT * FROM my_table AT (TIMESTAMP => ‘2021-01-01 07:00:00’ :: timestamp); This query uses the TIMESTAMP parameter to specify a point in time that is before the query execution time of 07:01.

• D. SELECT * FROM my table PRIOR TO STATEMENT ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726’; This query uses the PRIOR TO STATEMENT keyword and the STATEMENT parameter to specify a point in time that is immediately preceding the query execution time of 07:01.

• F. SELECT * FROM my_table BEFORE (STATEMENT => ‘8e5d0ca9-005e-44e6-b858-a8f5b37c5726’); This query uses the BEFORE keyword and the STATEMENT parameter to specify a point in time that is immediately preceding the query execution time of 07:01.

The other queries are incorrect because:

• A. SELECT * FROM my table WITH TIME_TRAVEL (OFFSET => -60*30); This query uses the OFFSET parameter to specify a point in time that is 30 minutes before the current time, which is 07:30. This is after the query execution time of 07:01, so it will not show the historical data before the query was executed.

• C. SELECT * FROM TIME_TRAVEL (‘MY_TABLE’, 2021-01-01 07:00:00); This query is not valid syntax for Time Travel. The TIME_TRAVEL function does not exist in Snowflake. The correct syntax is to use the AT or BEFORE clause after the table name in the FROM clause.

• E. SELECT * FROM my_table AT (OFFSET => -60*30); This query uses the AT keyword and the OFFSET parameter to specify a point in time that is 30 minutes before the current time, which is 07:30. This is equal to the query execution time of 07:01, so it will not show the historical data before the query was executed. The AT keyword specifies that the request is inclusive of any changes made by a statement or transaction with timestamp equal to the specified parameter. To exclude the changes made by the query, the BEFORE keyword should be used instead.