SnowPro Advanced Query Performance Optimization

1. Which factor most significantly impacts query scan time in Snowflake?

Number of columns selected

Number of micro-partitions scanned

Warehouse cluster count

Result set size

2. What is the optimal strategy for handling complex subqueries in Snowflake to maximize performance?

Convert to CTEs and materialize intermediate results strategically

Use nested subqueries without CTEs

Always flatten subqueries into single queries

Disable query caching for subqueries

3. How do you identify if a query is benefiting from metadata caching in Snowflake?

Check the EXPLAIN output for cache hits

Review QUERY_HISTORY for total_elapsed_time vs scan_bytes

Monitor warehouse credit usage only

Use SHOW QUERIES command

4. Which of the following is a best practice for optimizing INSERT/UPDATE operations in Snowflake?

Use single-row inserts for consistency

Batch operations and minimize micro-partition count changes

Disable clustering during updates

Use UNION ALL for all data modifications

5. What is the primary purpose of query result caching expiration in Snowflake?

To prevent stale data from being returned

To reduce storage usage and ensure data freshness

To improve warehouse performance

To disable clustering keys

6. How does Snowflake's adaptive execution plan improve query performance?

It pre-compiles all queries at warehouse startup

It adjusts join strategies and data distribution based on runtime statistics

It automatically adds clustering keys during execution

It caches all intermediate results

7. Which query optimization technique is most effective for reducing scans on large historical tables?

Adding more indexes

Partition pruning with time-based clustering

Increasing warehouse size

Disabling result caching

8. What does the clustering_ratio metric indicate in Snowflake?

The percentage of data compression

How well data is organized relative to clustering keys

The number of active clusters

The ratio of cold to hot storage

9. How can you minimize spilling in Snowflake queries?

Use smaller data types in SELECT clauses

Increase warehouse size or optimize join order

Disable clustering keys

Use UNION instead of JOIN

10. What is the benefit of using semi-structured data types efficiently in Snowflake?

Reduces query compilation time

Improves storage efficiency and query performance on JSON/Parquet data

Eliminates the need for clustering

Automatically parallelizes all queries

11. Which Snowflake feature allows you to cache query results across multiple sessions for improved performance?

Result Cache

Query Cache

Session Cache

Table Cache

12. What is the recommended approach for optimizing queries with large UNION ALL operations?

Use UNION instead of UNION ALL

Partition source tables and prune early

Increase warehouse size to maximum

Disable result caching

13. How does Snowflake handle skewed data distribution with clustering keys?

It automatically rebalances partitions

It uses clustering depth metrics to optimize micro-partition pruning

It disables clustering for skewed columns

It duplicates data across multiple clusters

14. What is the purpose of the ANALYZE TABLE command in Snowflake?

To collect table statistics for query optimization

To compress table data

To create indexes on columns

To validate data integrity

15. Which join strategy typically performs best for large fact tables in Snowflake?

Nested loop join

Hash join

Merge join

Broadcast join

16. How does Snowflake's metadata caching contribute to query optimization?

It stores all query results in memory

It caches table statistics and micro-partition metadata for faster pruning

It eliminates the need for clustering

It reduces data warehouse size

17. What is the impact of using a selective WHERE clause in Snowflake queries?

Reduces the number of micro-partitions scanned

Increases memory consumption

Requires additional clustering keys

Disables result caching

18. Which system view would you query to analyze query execution statistics and identify bottlenecks?

INFORMATION_SCHEMA.TABLES

SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

SNOWFLAKE.ACCOUNT_USAGE.TABLES

INFORMATION_SCHEMA.COLUMNS

19. In Snowflake, what does the EXPLAIN command provide?

A visual diagram of table relationships

An execution plan showing operations and estimated costs

A list of all queries run in the session

Statistics on data distribution

20. What is the primary benefit of using clustering keys in Snowflake?

Reduces storage costs by 50%

Improves query performance by pruning micro-partitions

Automatically distributes data across nodes

Eliminates the need for indexes

SnowPro Advanced Query Performance Optimization Quiz