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.