Query history and profiling in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding Query History Table Output

You run the following query in Snowflake to check recent query history:

SELECT query_id, user_name, execution_status, total_elapsed_time FROM table(information_schema.query_history()) WHERE user_name = 'ANALYST' ORDER BY start_time DESC LIMIT 3;

What will the total_elapsed_time column represent in the output?

Snowflake

SELECT query_id, user_name, execution_status, total_elapsed_time FROM table(information_schema.query_history()) WHERE user_name = 'ANALYST' ORDER BY start_time DESC LIMIT 3;

A The time in microseconds spent transferring data to the client.

B The time in seconds the query spent waiting in the queue before execution.

C The CPU time consumed by the query during execution.

D The total time in milliseconds that the query took to execute from start to finish.

💡 Hint

Think about what 'elapsed time' usually means in timing contexts.

The total_elapsed_time column shows the full duration the query took to run, measured in milliseconds, from start to finish including all processing time.

🧠 Conceptual

intermediate

Identifying Query Failures from History

You want to find all queries that failed in the last 24 hours using Snowflake's query history. Which execution_status value should you filter for to get only failed queries?

A 'SUCCESS'

B 'CANCELED'

C 'FAILED'

D 'RUNNING'

💡 Hint

Think about the status that indicates a query did not complete successfully.

The execution_status 'FAILED' indicates queries that ended with an error and did not complete successfully.

❓ Architecture

advanced

Optimizing Query Profiling for Large Workloads

Your Snowflake account runs thousands of queries daily. You want to efficiently analyze query performance trends over the past week without scanning the entire query history table. Which approach is best to reduce query time and cost?

A Use the QUERY_HISTORY function without any filters and rely on Snowflake to optimize automatically.

B Use the QUERY_HISTORY view with a filter on start_time to limit to the last week and select only needed columns.

C Query the QUERY_HISTORY table without filters to get all data and then filter in your application.

D Download the entire query history table and analyze it locally in a spreadsheet.

💡 Hint

Filtering early reduces data scanned and speeds up queries.

Filtering on start_time in the QUERY_HISTORY view limits data scanned, reducing cost and improving query speed. Selecting only needed columns also helps.

❓ security

advanced

Controlling Access to Query History Data

Which Snowflake role should you assign to a user to allow them to view query history but prevent them from modifying any data or running queries themselves?

A MONITOR

B SYSADMIN

C ACCOUNTADMIN

D PUBLIC

💡 Hint

This role is designed for read-only monitoring tasks.

The MONITOR role grants read-only access to account usage and query history without permissions to modify data or run queries.

✅ Best Practice

expert

Interpreting Query Profile for Performance Bottlenecks

You examine the query profile for a slow-running Snowflake query. The profile shows a large amount of time spent in the 'Spilled to Disk' operation. What does this indicate and what is the best next step?

A The query ran out of memory and spilled data to disk, slowing performance; increase warehouse size or optimize query to reduce memory use.

B The query is waiting on network I/O; check network connectivity and retry.

C The query is blocked by locks; kill blocking queries to proceed.

D The query completed successfully with no issues; no action needed.

💡 Hint

Spilling to disk usually means memory limits were exceeded.

Spilled to Disk means Snowflake had to write intermediate data to disk because memory was insufficient, causing slower query execution. Increasing warehouse size or rewriting the query can help.