Query profiling and the query plan in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to view the query profile for a specific query in Snowflake.

Snowflake

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE QUERY_ID = '[1]';

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

Ayour_query_id_here

BLAST_QUERY_ID()

CCURRENT_QUERY_ID()

DQUERY_TEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function instead of the actual query ID string.

Not putting the query ID in quotes.

You need to replace the blank with the actual query ID as a string to filter the query history.

2fill in blank

medium

Complete the code to retrieve the query plan for a given query ID in Snowflake.

Snowflake

SELECT * FROM TABLE(INFORMATION_SCHEMA.GET_QUERY_OPERATOR_STATS('[1]'));

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

AQUERY_ID

BCURRENT_QUERY_ID()

CLAST_QUERY_ID()

Dyour_query_id_here

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function instead of the actual query ID string.

Not quoting the query ID.

The query ID must be provided as a string to the GET_QUERY_OPERATOR_STATS table function to get the query plan.

3fill in blank

hard

Fix the error in the code to correctly get the query profile for the last executed query.

Snowflake

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE QUERY_ID = [1];

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

ACURRENT_QUERY_ID()

B'LAST_QUERY_ID()'

CLAST_QUERY_ID()

D'your_query_id_here'

Attempts:

3 left

💡 Hint

Common Mistakes

Putting LAST_QUERY_ID() in quotes, making it a string literal.

Using a placeholder string instead of the function.

LAST_QUERY_ID() is a function that returns the last query ID without quotes.

4fill in blank

hard

Fill both blanks to create a query that shows the query history filtered by user and ordered by start time descending.

Snowflake

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE USER_NAME = '[1]' ORDER BY [2] DESC;

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

AMY_USER

BSTART_TIME

CQUERY_TEXT

DEXECUTION_STATUS

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name that does not exist for ordering.

Not quoting the user name string.

Replace {{BLANK_1}} with the user name string and {{BLANK_2}} with the column START_TIME to order by start time.

5fill in blank

hard

Fill all three blanks to create a query that retrieves query history for a specific warehouse, filters by execution status, and orders by total elapsed time descending.

Snowflake

SELECT QUERY_ID, EXECUTION_STATUS, TOTAL_ELAPSED_TIME FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE WAREHOUSE_NAME = '[1]' AND EXECUTION_STATUS = '[2]' ORDER BY [3] DESC;

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

AMY_WAREHOUSE

BSUCCESS

CTOTAL_ELAPSED_TIME

DFAILED

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect execution status values.

Not quoting string values.

Ordering by a non-existent column.

Fill the warehouse name string, filter for successful executions, and order by total elapsed time descending.