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.

Practice

1. What does the EXPLAIN command in Snowflake do?

easy

A. Shows the steps Snowflake will take to run a query

B. Runs the query and returns the results

C. Deletes the query from history

D. Optimizes the query automatically

Solution

1. Step 1: Understand the purpose of EXPLAIN

   The EXPLAIN command shows the query plan, which details how Snowflake will execute the query step-by-step without running it.

2. Step 2: Differentiate EXPLAIN from other commands

   Unlike running the query or deleting history, EXPLAIN only provides the plan, not results or changes.

3. Final Answer:

   Shows the steps Snowflake will take to run a query -> Option A

4. Quick Check:

   EXPLAIN = Shows query plan [OK]

Hint: EXPLAIN previews query steps without running it [OK]

Common Mistakes:

• Thinking EXPLAIN runs the query
• Confusing EXPLAIN with query deletion
• Assuming EXPLAIN optimizes automatically

2. Which of the following is the correct syntax to get the query plan for a SELECT statement in Snowflake?

easy

A. SHOW PLAN FOR SELECT * FROM my_table;

B. EXPLAIN SELECT * FROM my_table;

C. DESCRIBE QUERY SELECT * FROM my_table;

D. PLAN QUERY SELECT * FROM my_table;

Solution

1. Step 1: Recall Snowflake syntax for query plans

   Snowflake uses the EXPLAIN keyword before a query to show its plan.

2. Step 2: Verify syntax correctness

   Only EXPLAIN SELECT ... is valid; other options are not recognized commands.

3. Final Answer:

   EXPLAIN SELECT * FROM my_table; -> Option B

4. Quick Check:

   EXPLAIN + query = correct syntax [OK]

Hint: Use EXPLAIN before your query to see the plan [OK]

Common Mistakes:

• Using SHOW PLAN which is not valid in Snowflake
• Trying DESCRIBE QUERY which is incorrect
• Using PLAN QUERY which is not a command

3. Given the query plan shows a step with a large number of rows scanned and a high cost, what does this indicate?

medium

A. Snowflake will skip this step automatically

B. The query is efficient and fast

C. This step may slow down the query and use more resources

D. The query will return no results

Solution

1. Step 1: Interpret rows scanned and cost in query plan

   A large number of rows scanned and high cost means this step requires more work and resources.

2. Step 2: Understand impact on query performance

   Such steps usually slow down the query and increase resource usage, not speed it up or skip it.

3. Final Answer:

   This step may slow down the query and use more resources -> Option C

4. Quick Check:

   High rows scanned = slower step [OK]

Hint: High rows scanned means slower query step [OK]

Common Mistakes:

• Assuming high cost means faster query
• Thinking Snowflake skips costly steps
• Confusing cost with query result count

4. You ran EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01'; but the plan shows a full table scan. What is a likely reason?

medium

A. There is no index or clustering on order_date

B. The query syntax is incorrect

C. Snowflake does not support filtering on dates

D. The table is empty

Solution

1. Step 1: Analyze why full table scan occurs

   Full table scans happen when no index or clustering helps filter data efficiently.

2. Step 2: Check other options for validity

   The query syntax is correct, Snowflake supports date filters, and an empty table would not cause a full scan.

3. Final Answer:

   There is no index or clustering on order_date -> Option A

4. Quick Check:

   No index = full scan [OK]

Hint: No index or clustering causes full scans [OK]

Common Mistakes:

• Thinking syntax error causes full scan
• Believing Snowflake can't filter dates
• Assuming empty table causes full scan

5. You want to optimize a slow query that joins two large tables. The query plan shows a costly step performing a large shuffle of data between nodes. What is the best approach to reduce this cost?

hard

A. Increase the warehouse size without changing the query

B. Add more columns to the SELECT clause

C. Run the query without a WHERE clause

D. Use clustering keys to co-locate join keys and reduce data shuffle

Solution

1. Step 1: Understand data shuffle in join operations

   Data shuffle happens when data must move between nodes to match join keys, which is costly.

2. Step 2: Use clustering keys to optimize joins

   Clustering keys organize data so join keys are stored together, reducing shuffle and improving speed.

3. Step 3: Evaluate other options

   Adding columns or removing filters increases data processed; increasing warehouse size helps but does not reduce shuffle.

4. Final Answer:

   Use clustering keys to co-locate join keys and reduce data shuffle -> Option D

5. Quick Check:

   Clustering keys reduce shuffle cost [OK]

Hint: Clustering keys reduce costly data shuffle in joins [OK]

Common Mistakes:

• Adding columns increases data, not reduces cost
• Removing WHERE clause slows query
• Relying only on warehouse size ignores shuffle