Performance Optimization

Which TWO of the following are common indicators that a query is poorly optimized and requires tuning?

An architect notices a large table is frequently queried using a range filter on a timestamp column. The table is currently clustered by a high-cardinality ID column. What is the most efficient way to improve query performance?

An architect is investigating a performance regression in a query that previously ran quickly. The query involves a join between a large fact table and a small dimension table. The query profile shows a significant amount of time spent in the 'Join' operator, with many rows being processed. Which of the following is the most likely cause of the performance degradation?

An organization has a 500TB table containing IoT sensor data. Users frequently run point-lookup queries filtering by a specific Sensor_ID, which has very high cardinality. The table is currently clustered by Event_Timestamp. Performance for these Sensor_ID lookups is poor. Which architectural change would provide the most cost-effective performance improvement for these specific queries?

Under which TWO scenarios should an architect choose to increase the warehouse size (Vertical Scaling) rather than increasing the maximum number of clusters (Horizontal Scaling)?

An architect needs to optimize point-lookup queries on a VARIANT column that stores JSON data. Which TWO methods are most effective for improving performance for these types of queries?

An architect is trying to optimize a query that scans many small files. What is the most effective approach to improve performance?

A data architect is designing a table that will store 10TB of semi-structured JSON data in a VARIANT column. Queries frequently filter on specific JSON attributes using dot notation, such as data:customer_id::string. The architect wants to minimize query latency and storage costs. Which approach should the architect take?

Which system function should an architect use to evaluate the clustering health of a table and determine if the current clustering key is effectively organizing the data into distinct micro-partitions?

A user runs a query that takes 10 minutes to execute. They immediately run the exact same query again, but it still takes 10 minutes. Which of the following is a likely reason why the Result Cache was not utilized?

An architect is considering the Query Acceleration Service (QAS) for a specific workload. Which type of query is most likely to benefit from QAS?

An architect is tuning a query that joins a large fact table to a dimension table. The query profile shows that the join is a full outer join and that the fact table is scanned entirely, even though the query filters on a column from the dimension table. The dimension table is small. The architect wants to reduce the amount of data scanned without changing the result set. Which action is most likely to improve performance?

Which property of External Tables should an architect configure to ensure that Snowflake does not scan all files in the underlying S3/Azure/GCS bucket for every query?

An architect is optimizing a query that joins a large fact table and a small dimension table. The query is slow. What should be the first step to improve performance?

An architect is analyzing a Snowflake query that performs poorly. The Query Profile shows a high percentage of time spent in the 'TableScan' operator with a large number of partitions scanned, despite a filter on a column with high selectivity. The table is not clustered. Which action is most likely to improve performance?

A Snowflake architect is tasked with improving the performance of a dashboard that runs several queries against a large fact table. The queries filter on different columns and are run concurrently by many users. The architect notices that the warehouse is often queued due to high concurrency. Which configuration change is most appropriate to reduce queuing and improve concurrency?

Which Snowflake feature should be used to improve performance for point lookups on tables with billions of rows?

A data engineer is building a dashboard that queries a large sales table with filters on region and date. The table is not clustered, and queries scan many micro-partitions. The engineer wants to improve query performance with minimal ongoing maintenance. Which Snowflake feature should the engineer use?

An architect is optimizing a Snowflake environment where a critical query joins a large fact table with a small dimension table. The Query Profile shows that the build side of the hash join is spilling to local disk. Which action is most likely to eliminate the spilling and improve performance?

A Snowflake architect is investigating a query that performs poorly due to a large hash join. The query joins a 5TB fact table with a 10GB dimension table. The fact table is not clustered. The query filters the fact table on a date range that covers 10% of the data. Which optimization is most likely to improve performance with minimal cost?

While reviewing a Query Profile, an architect notices that a 'Join' operator is consuming 90% of the total execution time, and one specific node in the warehouse is processing significantly more rows than others. What is the most likely cause and the best resolution?

A Snowflake architect notices that a recurring ETL job that loads data into a table and then immediately runs a complex aggregation query is taking longer than expected. The table is not clustered, and the query filters on a timestamp column. Which action would most directly improve the performance of the aggregation query?

A financial services company uses Snowflake to analyze trade data. They have a large table TRANSACTIONS with a clustering key on TRADE_DATE. Queries that filter on TRADE_DATE and ACCOUNT_ID are performing well, but queries that filter only on ACCOUNT_ID are slow. The architect wants to improve performance for ACCOUNT_ID-only queries without degrading the performance of TRADE_DATE queries. Which solution is most appropriate?

Which action should an architect take to optimize a query that is experiencing significant 'Remote Disk Spilling' during a join operation on large datasets?

When would using a Search Optimization Service be inappropriate for a table?

A financial services company runs a Snowflake workload where several long-running analytical queries compete with a high volume of short interactive dashboard queries on the same multi-cluster warehouse. Users report that dashboards sometimes wait behind heavy reports. The architect wants a cost-effective configuration that isolates the heavy reports from the interactive queries without duplicating compute unnecessarily. What should the architect do?

A Snowflake architect is investigating a slow-running query that joins a large fact table with a small dimension table. The query profile shows that the join operation is using a Cartesian product, resulting in a massive number of rows processed. The architect checks the query and notices that the join condition is missing. Which action should the architect take to resolve the performance issue?

A retailer has a 50TB table containing transaction logs. Users frequently query specific transaction_id values (highly selective point lookups) but also run daily reports aggregated by transaction_date. The table is currently clustered by transaction_date. What is the most cost-effective way to improve point lookup performance without degrading report performance?

When a query is slow, which Snowflake feature provides the most granular details about the time spent in every operator (e.g., Join, Filter, Aggregate)?

An architect is designing a dashboard that runs a query joining a 5 TB fact table to a small 2 GB dimension table. The fact table is not clustered, and the dimension table is updated hourly. The query filters on a high-cardinality column in the fact table and joins on a low-cardinality key. The architect wants to minimize query latency without increasing warehouse size. Which approach is most effective?

A Snowflake architect is reviewing a query that uses a window function partitioned by customer_id and ordered by transaction_date. The query processes a 1TB table and runs slowly. The architect notices that the table is not clustered. Which action should the architect take to improve performance?

A Snowflake architect is analyzing a query that performs a large aggregation over a fact table with billions of rows. The query profile shows that the aggregation step is spilling to local disk, and the warehouse is a 2XL multi-cluster warehouse with 4 clusters. The architect wants to reduce the spilling and improve performance. Which action is most likely to achieve this?

A Snowflake architect is optimizing a slow query that performs a large aggregation over a fact table. The query profile shows that the Aggregation operator is taking a long time. Which TWO actions are most likely to improve the performance of this aggregation? (Choose two.)

A data architect notices that a recurring batch job that loads data into a Snowflake table and then runs a series of transformation queries is taking longer than expected. The transformation queries involve multiple joins and aggregations. The architect wants to ensure that the warehouse is adequately sized for the workload without over-provisioning. Which Snowflake feature should the architect use to analyze the performance of individual queries and identify bottlenecks?

Refer to the exhibit. What is the most likely performance issue here?

A SnowPro Advanced Architect is designing a new fact table that will be loaded incrementally each day with 500 million rows. The table will be queried primarily by a small set of high-concurrency BI dashboards filtering on order_date and product_category. The architect wants to minimize micro-partition scanning for these queries. Which table design approach should the architect choose?

A Snowflake architect is analyzing a slow-running query that joins two large tables and aggregates results. The query profile shows a significant amount of time spent in the 'Join' operator and a large number of rows spilled to local storage. The join condition uses a non-equality predicate (e.g., range join). Which optimization technique is most likely to improve performance?

A Snowflake architect is investigating a query that performs poorly due to a Cartesian join between two large tables. The query is intended to join on a specific key, but the join condition is missing in the SQL. After correcting the query to include the join condition, the architect wants to ensure optimal performance. Which of the following is the most effective next step?

When analyzing a query profile, you observe a high 'Remote Disk Spilling' metric. What is the most likely cause, and how can it be resolved?

An architect is designing a table to support analytical queries. Which data type choice would most likely improve performance for filtering operations?

An architect is investigating query performance issues where queries are spilling to local disk. Which TWO actions would most effectively mitigate this issue?

A Snowflake architect is reviewing a query that scans a large table and applies a highly selective filter on a column that is not the clustering key. The query profile shows a TableScan operator with a high percentage of partitions scanned but few rows returned. Which feature should the architect recommend to improve performance for this type of query?

A data architect is tuning a Snowflake virtual warehouse that runs a mix of short ad-hoc queries and long-running analytical queries. The warehouse is sized as Medium, and the architect observes that short queries are frequently queued behind long queries. The architect wants to reduce queuing for short queries without increasing the warehouse size. Which configuration change should the architect make?

Which THREE factors should be considered when evaluating the cost-benefit of enabling the Search Optimization Service on a large table? (Choose three.)

An architect is optimizing a Snowflake environment where several large tables are frequently queried with filters on a timestamp column that is not the natural sort order of the data. The architect wants to improve query performance by reducing the amount of data scanned. Which action should the architect take?

What is the primary benefit of using Materialized Views in Snowflake for performance optimization?

A Snowflake architect is analyzing a query that performs a large join between a fact table and a dimension table. The query profile shows that the join operation is spilling to local disk. The architect wants to reduce the spillage and improve performance. Which action should the architect take first?

Which TWO of the following statements about the Query Acceleration Service are correct? (Choose two.)

A data architect is analyzing a slow-performing query that joins a large fact table with a small dimension table. The Query Profile shows that the join is executed as a broadcast join, and the dimension table is small enough to fit in memory. However, the query still takes a long time because the fact table is not pruned effectively. The fact table is clustered by date, and the query filters on a specific date range. Which action should the architect take to improve pruning and overall performance?

A data architect is designing a performance monitoring strategy for a Snowflake account. They need to identify queries that are consuming the most resources over time to prioritize optimization efforts. Which Snowflake feature should they use to analyze historical query performance and resource consumption?

A data architect is analyzing a query that performs poorly due to excessive data shuffling during a large join operation. The query joins two large tables on a non-clustered key. Which Snowflake feature can help reduce data shuffling by co-locating related data?

An architect is investigating why a query that joins a 10 TB fact table to a small dimension table runs slowly. The query profile shows a Broadcast Join operator and a high percentage of time spent in the Join node. The fact table is not clustered on the join key. Which action is most likely to improve performance?

A Snowflake architect is reviewing a query that runs slowly. The query profile shows that the most time is spent in the TableScan operator, and the operator details indicate that a large number of micro-partitions were scanned. The architect wants to reduce the number of micro-partitions scanned. Which action is most likely to achieve this?

A data engineer is experiencing slow query performance on a large table. The query filters by a high-cardinality column that is not the clustering key. Which optimization technique should the engineer prioritize to improve performance?

Which approach is most effective for optimizing queries that frequently filter by multiple columns simultaneously?

Which Snowflake feature helps minimize query latency by avoiding re-computation for identical queries?

A SnowPro Advanced Architect is analyzing a query that performs a large aggregation over a fact table. The query profile shows that the Aggregation operator is spilling to local disk. The architect wants to reduce spilling and improve performance. Which action is most likely to help?

Which Snowflake feature should be used to monitor and identify slow-running queries across the entire account for performance tuning?

A Snowflake architect is reviewing a query that performs a large aggregation over a fact table. The Query Profile shows that the aggregation is spilling to local disk. The warehouse is a Medium size. The architect wants to reduce or eliminate spilling to improve performance. Which action is most likely to achieve this?

A SnowPro Advanced Architect is configuring a virtual warehouse for a workload that runs a mix of short ad-hoc queries and long-running ETL jobs. The architect wants to prevent long-running ETL jobs from monopolizing the warehouse and degrading ad-hoc query performance. Which approach is most appropriate?

A Snowflake architect is optimizing a virtual warehouse that serves a mix of short ad-hoc queries and long-running ETL jobs. Users report that ad-hoc queries sometimes wait in the queue for several minutes during ETL execution. The architect wants to reduce queuing without increasing costs unnecessarily. Which two actions should the architect take? (Choose two.)

A SnowPro Advanced Architect is tuning a virtual warehouse that experiences high concurrency during peak hours. The architect observes that queries are queuing, and the warehouse is not fully utilizing its resources. Which TWO actions should the architect take to improve concurrency? (Choose two.)

An architect needs to optimize a dashboard that frequently queries a multi-terabyte table. The queries involve complex aggregations on several columns and a join to a small dimension table. The dashboard allows users to filter by any combination of five different dimensions. Which optimization strategy is most appropriate?