Query profiling and the query plan in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding Query Profile Output

After running a complex SELECT query in Snowflake, you open the query profile. Which of the following best describes what the "Execution Time" metric represents?

A The total time Snowflake spent parsing the SQL statement before execution.

B The time taken by Snowflake to execute the query including all steps like scanning, joining, and aggregating data.

C The time Snowflake spent transferring the query results back to the client.

D The time Snowflake spent compiling the query plan but not running the query.

💡 Hint

Think about what "execution" means in the context of running a query.

The "Execution Time" in the query profile shows how long Snowflake took to actually run the query steps such as scanning tables, joining data, and computing results. Parsing and compiling happen before execution, and result transfer happens after execution.

🧠 Conceptual

intermediate

Identifying Query Plan Components

In Snowflake's query plan, which component is responsible for determining the order and method of joining tables?

A Result Cache

B Query Parser

C Query Optimizer

D Warehouse Manager

💡 Hint

Consider which part decides how to best run the query.

The Query Optimizer analyzes the SQL and decides the best way to join tables, which indexes to use, and the order of operations to minimize resource use and time.

❓ Architecture

advanced

Analyzing Query Profile for Performance Bottlenecks

You notice a Snowflake query is running slower than expected. The query profile shows a large percentage of time spent in "Spilling to Disk". What does this indicate and what is the best immediate action?

A The query is using more memory than available; increase warehouse size or optimize query to reduce memory use.

B The query is waiting for network transfer; increase warehouse size to speed up network.

C The query is blocked by locks; kill other queries to release locks.

D The query is waiting on result caching; clear cache to force fresh execution.

💡 Hint

Spilling to disk usually means memory is insufficient.

Spilling to disk happens when the query needs more memory than allocated, causing Snowflake to write intermediate data to disk, which slows performance. Increasing warehouse size or rewriting the query to use less memory helps.

❓ security

advanced

Query Profile and Data Access Auditing

Which part of Snowflake's query profile can help you verify which tables and columns were accessed during a query for auditing purposes?

A Result Cache details showing cached results.

B Query Text section showing the SQL statement.

C Warehouse Usage statistics showing compute time.

D Query Profile's <strong>"Objects Accessed"</strong> section listing tables and columns used.

💡 Hint

Look for where Snowflake shows what data was touched.

The "Objects Accessed" section in the query profile lists all tables and columns the query read or modified, which is useful for auditing data access.

✅ Best Practice

expert

Optimizing Query Plans for Large Joins

You have a query joining two large tables in Snowflake. The query profile shows a "Broadcast Join" was chosen by the optimizer, but the query runs slowly and uses excessive resources. What is the best next step to improve performance?

A Force a <strong>Shuffle Join</strong> by rewriting the query or using query hints to avoid broadcasting large tables.

B Increase the warehouse size to handle the broadcast join better without changes.

C Disable result caching to force fresh data processing.

D Split the query into multiple smaller queries to avoid joins.

💡 Hint

Broadcast joins send one table to all nodes; think about when this is efficient.

Broadcast joins work well when one table is small. For large tables, broadcasting causes heavy network and memory use. Forcing a shuffle join redistributes data more evenly and can improve performance.

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