Querying historical data (AT, BEFORE) in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to query data as it was at a specific time.

Snowflake

SELECT * FROM sales_data AT(TIMESTAMP => [1]);

Drag options to blanks, or click blank then click option'

A'2023-01-01 10:00:00'

B'2023-01-01 11:00:00'

C'2023-01-01 12:00:00'

D'2023-01-01 09:00:00'

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put the timestamp in quotes.

Using an invalid timestamp format.

The AT(TIMESTAMP =>) clause lets you query the table as it was at a specific timestamp. You need to provide a valid timestamp string.

2fill in blank

medium

Complete the code to query data as it was before a certain time.

Snowflake

SELECT * FROM sales_data BEFORE(TIMESTAMP => [1]);

Drag options to blanks, or click blank then click option'

A'2023-01-15 00:00:00'

B'2023-02-01 00:00:00'

C'2023-03-01 00:00:00'

D'2023-01-01 00:00:00'

Attempts:

3 left

💡 Hint

Common Mistakes

Using AT instead of BEFORE.

Not quoting the timestamp.

The BEFORE(TIMESTAMP =>) clause returns data as it was before the given timestamp.

3fill in blank

hard

Fix the error in the query to correctly use the time travel feature.

Snowflake

SELECT * FROM sales_data [1](TIMESTAMP => '2023-01-10 08:00:00');

Drag options to blanks, or click blank then click option'

AAS OF

BBEFORE

CAT

DSINCE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'AS OF' which is not valid syntax.

Using 'SINCE' which is not supported.

The correct syntax to query data at a specific time is AT(TIMESTAMP =>) followed by the timestamp.

4fill in blank

hard

Fill both blanks to query data before a certain timestamp and order by date.

Snowflake

SELECT * FROM sales_data [1](TIMESTAMP => [2]) ORDER BY sale_date;

Drag options to blanks, or click blank then click option'

ABEFORE

B'2023-01-20 00:00:00'

C'2023-02-01 00:00:00'

DAS OF

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the keyword and timestamp positions.

Using 'AT' instead of 'BEFORE'.

Use BEFORE(TIMESTAMP =>) followed by the timestamp to get data before that time.

5fill in blank

hard

Fill all three blanks to query data at a specific time, filter by region, and order by amount.

Snowflake

SELECT * FROM sales_data [1](TIMESTAMP => [2]) WHERE region = [3] ORDER BY amount DESC;

Drag options to blanks, or click blank then click option'

AAT

B'2023-01-15 12:00:00'

C'North'

D'2023-01-10 09:00:00'

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting the region string properly.

Using 'BEFORE' instead of 'AT'.

Use AT(TIMESTAMP =>) with the timestamp, then filter by region with quotes around the string.