ARA-C01 Performance Optimization — All Questions With Answers

Question 1hardmulti select

Read the full Performance Optimization explanation →

An architect is reviewing query profile statistics. Which TWO indicators are signs that a table is poorly clustered for a specific query? (Choose two.)

Question 2mediummultiple choice

Read the full Performance Optimization explanation →

Refer to the exhibit. What is the most likely cause of this performance bottleneck?

Exhibit

Query Profile Snippet:
- Join: 85% of execution time
- Data Volume: 500GB
- Memory: 16GB (Warehouse Size: X-Small)

Question 3mediummultiple choice

Read the full Performance Optimization explanation →

Which approach is most effective for optimizing performance when dealing with queries that exhibit high 'Data Spilling' due to massive aggregations?

Question 4mediummultiple choice

Read the full Performance Optimization explanation →

Which action allows an architect to see how long a specific query spent in the compilation phase versus the execution phase?

Question 5mediummultiple choice

Read the full Performance Optimization explanation →

An architect is evaluating clustering keys for a table that is filtered by both 'Country' and 'OrderDate'. Which clustering key is likely to be most effective?

Question 6mediummultiple choice

Read the full Performance Optimization explanation →

A data architect notices that a multi-cluster warehouse is consistently starting new clusters even when the total query load is low. The users are primarily running short, sub-second dashboard queries. Which scaling policy adjustment should the architect implement to minimize credit consumption while maintaining acceptable performance?

Question 7hardmulti select

Read the full Performance Optimization explanation →

A large fact table is experiencing poor query pruning. The architect is evaluating whether to implement a clustering key. Which TWO factors are most critical in determining the effectiveness of a potential clustering key for improving query performance?

Question 8mediummulti select

Read the full Performance Optimization explanation →

An architect is optimizing a data ingestion pipeline using Snowpipe. Which THREE actions will help ensure optimal performance and minimize latency for data becoming available in the target table?

Question 9hardmultiple choice

Read the full Performance Optimization explanation →

An enterprise data warehouse running on Snowflake experiences recurring query latency spikes during peak reporting hours. Multiple concurrent dashboard queries scan the same large fact table containing over two billion rows, resulting in intense warehouse queueing and resource contention. The data is naturally ordered by insertion timestamp. What is the most architecturally sound Snowflake optimization to alleviate this concurrency bottleneck while maintaining sub-second ingestion rates?

Question 10hardmulti select

Read the full Performance Optimization explanation →

Which THREE factors should be considered when choosing a clustering key for a large Snowflake table to optimize performance?

Question 11mediummultiple choice

Read the full Performance Optimization explanation →

Which action should be taken to improve the performance of a query that joins two tables on a non-indexed column that is not part of a clustering key?

Question 12hardmultiple choice

Read the full Performance Optimization explanation →

A financial services firm runs a Snowflake warehouse where an ETL pipeline loads a 5 TB fact table every night. Analysts report that dashboard queries filtering on a high-cardinality column named ACCOUNT_ID are slow, even though the table is clustered on a different low-cardinality column. The architect needs to improve pruning for these queries without degrading load performance. Which action should the architect take?

Question 13mediummultiple choice

Read the full Performance Optimization explanation →

A data engineering team runs a daily ETL job that loads 500 GB of new data into a large fact table using a single INSERT INTO ... SELECT statement. The table is clustered by ORDER_DATE, and the load process sorts the incoming data by ORDER_DATE before insertion. However, the team observes that the clustering depth of the table degrades after each load, and automatic clustering does not fully restore it before the next query workload begins. The architect wants to minimize reclustering overhead while maintaining good pruning for queries that filter on ORDER_DATE. Which approach is most effective?

Question 14hardmultiple choice

Read the full Performance Optimization explanation →

A SnowPro Advanced Architect is investigating a query that runs slowly on a large table. The query profile shows that the Join operator spends most of its time on the build side, and the table is joined on a column that is not the clustering key. The architect wants to reduce the build-side time without changing the query result. Which action is most likely to help?

Question 15hardmulti select

Read the full Performance Optimization explanation →

A Snowflake architect is evaluating the performance of a large table that is frequently queried with filters on multiple columns. The architect wants to identify which factors can cause poor pruning and lead to full table scans. (Choose two.)

Question 16mediummulti select

Read the full Performance Optimization explanation →

A Snowflake architect is investigating why a recurring ETL query that loads data into a large table is running slower than expected. The query performs a MERGE from a staging table. The architect wants to identify which TWO factors are most likely contributing to the performance degradation. (Choose two.)

Question 17mediummulti select

Read the full Performance Optimization explanation →

An architect is optimizing a Snowflake environment for a workload that includes many concurrent queries with varying complexity. The goal is to minimize queuing and ensure efficient resource utilization. Which two actions should the architect take to achieve this? (Choose two.)

Question 18hardmulti select

Read the full Performance Optimization explanation →

An architect is designing a real-time dashboard that queries a large fact table with frequent filters on both REGION and SALE_DATE. The table is currently clustered by SALE_DATE, but queries filtering on REGION alone are slow. The architect decides to change the clustering key to (REGION, SALE_DATE). Which two statements are true regarding the impact of this change? (Choose two.)

Question 19hardmultiple choice

Read the full Performance Optimization explanation →

An architect is analyzing a query that joins a large fact table with a small dimension table. The query profile shows a broadcast join, but the architect suspects that a different join strategy would be more efficient because the dimension table is actually not small enough to broadcast efficiently. Which Snowflake feature should the architect investigate to influence the join strategy?

Question 20mediummultiple choice

Read the full Performance Optimization explanation →

A data engineering team runs a nightly ELT pipeline that loads a 10 TB table into Snowflake using a single large warehouse. After the load, they run a complex aggregation query that joins this table with a smaller dimension table. The query is slow, and the Query Profile shows that most time is spent in the join operator because the large table is scanned in full. The team wants to reduce the scan time for this recurring query. Which Snowflake feature should they implement?

Question 21mediummultiple choice

Read the full Performance Optimization explanation →

A Snowflake virtual warehouse serving a BI dashboard experiences high queueing during peak hours. The warehouse is a multi-cluster warehouse with a minimum of 2 and maximum of 10 clusters, scaling policy set to Standard. The queries are short and highly concurrent, and the architect observes that additional clusters start only after significant queueing. Which action should the architect take to reduce latency for these short queries?

Question 22mediummultiple choice

Read the full Performance Optimization explanation →

An architect maintains a Snowflake table with a clustering key on the DATE column. Users frequently run queries filtering on DATE ranges and also on a high-cardinality CUSTOMER_ID column. The architect notices that clustering depth is increasing over time. Which action will most effectively reduce clustering depth and maintain query performance?

Question 23mediummulti select

Read the full Performance Optimization explanation →

A Snowflake architect is optimizing a large fact table that is frequently joined with dimension tables. The fact table is several terabytes and is filtered by date and region. The architect wants to improve join performance. Which two actions should the architect take? (Choose two.)

Question 24hardmulti select

Read the full Performance Optimization explanation →

A Snowflake architect is tuning a complex query that aggregates a 5 TB sales table. The query profile shows a high percentage of time in the Aggregation operator and significant data spillage to local disk. The warehouse is a 2X-Large multi-cluster warehouse with maximum clusters set to 1. Which TWO actions are most likely to reduce spillage and improve aggregation performance? (Choose two.)

Question 25hardmultiple choice

Read the full Performance Optimization explanation →

A data architect is tuning a complex query that joins a large fact table with several dimension tables. The query profile shows a significant amount of time spent in the Join operator, specifically on a hash join. The fact table is clustered on a date column, and the join key is a surrogate key with high cardinality. Which optimization technique is most likely to improve join performance?

Question 26hardmulti select

Read the full Performance Optimization explanation →

A Snowflake architect is designing a performance optimization strategy for a data warehouse that experiences high concurrency during business hours. The architect observes that many queries are queued, and some queries are spilling to remote storage. The architect wants to reduce queuing and improve overall throughput without changing the queries. Which two actions should the architect take? (Choose two.)

Question 27easymultiple choice

Read the full Performance Optimization explanation →

A Snowflake architect is analyzing a query that runs slowly due to a large amount of data being scanned from a table. The table is not clustered, and queries frequently filter on a low-cardinality column. Which of the following is the most appropriate initial step to improve query performance?

Question 28mediummultiple choice

Read the full Performance Optimization explanation →

A architect is troubleshooting a slow-running query on a large fact table. The Query Profile shows that over 90% of the total execution time is spent in a TableScan operator, with a large number of partitions scanned and few partitions pruned. The table is not clustered. The query filters on a high-cardinality column named transaction_id. Which Snowflake feature should the architect enable to improve performance for this specific query pattern?

Question 29mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 30mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 31easymultiple choice

Read the full Performance Optimization explanation →

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

Question 32hardmulti select

Read the full Performance Optimization explanation →

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

Question 33mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 34hardmultiple choice

Read the full Performance Optimization explanation →

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

Exhibit

CLI Output: 
SELECT count(*) FROM sales WHERE date >= '2023-01-01' AND date <= '2023-01-31';
Profile:
- Total Execution Time: 45s
- Partitions Scanned: 1000
- Partitions Total: 1000

Question 35mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 36hardmulti select

Read the full Performance Optimization explanation →

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

Question 37mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 38mediummultiple choice

Read the full Performance Optimization explanation →

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)?

Question 39mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 40hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 41easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 42mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 43mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 44hardmulti select

Read the full Performance Optimization explanation →

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)?

Question 45mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 46easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 47mediummulti select

Read the full Performance Optimization explanation →

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?

Question 48mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 49hardmulti select

Read the full Performance Optimization explanation →

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

Question 50easymultiple choice

Read the full Performance Optimization explanation →

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

Question 51mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 52mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 53mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 54hardmultiple choice

Read the full Performance Optimization explanation →

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

Question 55hardmulti select

Read the full Performance Optimization explanation →

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

Question 56mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 57mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 58mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 59hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 60mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 61mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 62mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 63hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 64hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 65mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 66mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 67easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 68easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 69hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 70easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 71hardmulti select

Read the full Performance Optimization explanation →

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.)

Question 72mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 73hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 74hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 75hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 76mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 77easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 78mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 79hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 80mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 81easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 82mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 83hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 84easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 85mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 86mediummulti select

Read the full Performance Optimization explanation →

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.)

Question 87hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 88hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 89mediummulti select

Read the full Performance Optimization explanation →

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.)

Question 90easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 91mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 1hardmulti select

Read the full Performance Optimization explanation →

An architect is reviewing query profile statistics. Which TWO indicators are signs that a table is poorly clustered for a specific query? (Choose two.)

Question 2mediummultiple choice

Read the full Performance Optimization explanation →

Refer to the exhibit. What is the most likely cause of this performance bottleneck?

Exhibit

Query Profile Snippet:
- Join: 85% of execution time
- Data Volume: 500GB
- Memory: 16GB (Warehouse Size: X-Small)

Question 3mediummultiple choice

Read the full Performance Optimization explanation →

Which approach is most effective for optimizing performance when dealing with queries that exhibit high 'Data Spilling' due to massive aggregations?

Question 4mediummultiple choice

Read the full Performance Optimization explanation →

Which action allows an architect to see how long a specific query spent in the compilation phase versus the execution phase?

Question 5mediummultiple choice

Read the full Performance Optimization explanation →

An architect is evaluating clustering keys for a table that is filtered by both 'Country' and 'OrderDate'. Which clustering key is likely to be most effective?

Question 6mediummultiple choice

Read the full Performance Optimization explanation →

A data architect notices that a multi-cluster warehouse is consistently starting new clusters even when the total query load is low. The users are primarily running short, sub-second dashboard queries. Which scaling policy adjustment should the architect implement to minimize credit consumption while maintaining acceptable performance?

Question 7hardmulti select

Read the full Performance Optimization explanation →

A large fact table is experiencing poor query pruning. The architect is evaluating whether to implement a clustering key. Which TWO factors are most critical in determining the effectiveness of a potential clustering key for improving query performance?

Question 8mediummulti select

Read the full Performance Optimization explanation →

An architect is optimizing a data ingestion pipeline using Snowpipe. Which THREE actions will help ensure optimal performance and minimize latency for data becoming available in the target table?

Question 9hardmultiple choice

Read the full Performance Optimization explanation →

An enterprise data warehouse running on Snowflake experiences recurring query latency spikes during peak reporting hours. Multiple concurrent dashboard queries scan the same large fact table containing over two billion rows, resulting in intense warehouse queueing and resource contention. The data is naturally ordered by insertion timestamp. What is the most architecturally sound Snowflake optimization to alleviate this concurrency bottleneck while maintaining sub-second ingestion rates?

Question 10hardmulti select

Read the full Performance Optimization explanation →

Which THREE factors should be considered when choosing a clustering key for a large Snowflake table to optimize performance?

Question 11mediummultiple choice

Read the full Performance Optimization explanation →

Which action should be taken to improve the performance of a query that joins two tables on a non-indexed column that is not part of a clustering key?

Question 12hardmultiple choice

Read the full Performance Optimization explanation →

A financial services firm runs a Snowflake warehouse where an ETL pipeline loads a 5 TB fact table every night. Analysts report that dashboard queries filtering on a high-cardinality column named ACCOUNT_ID are slow, even though the table is clustered on a different low-cardinality column. The architect needs to improve pruning for these queries without degrading load performance. Which action should the architect take?

Question 13mediummultiple choice

Read the full Performance Optimization explanation →

A data engineering team runs a daily ETL job that loads 500 GB of new data into a large fact table using a single INSERT INTO ... SELECT statement. The table is clustered by ORDER_DATE, and the load process sorts the incoming data by ORDER_DATE before insertion. However, the team observes that the clustering depth of the table degrades after each load, and automatic clustering does not fully restore it before the next query workload begins. The architect wants to minimize reclustering overhead while maintaining good pruning for queries that filter on ORDER_DATE. Which approach is most effective?

Question 14hardmultiple choice

Read the full Performance Optimization explanation →

A SnowPro Advanced Architect is investigating a query that runs slowly on a large table. The query profile shows that the Join operator spends most of its time on the build side, and the table is joined on a column that is not the clustering key. The architect wants to reduce the build-side time without changing the query result. Which action is most likely to help?

Question 15hardmulti select

Read the full Performance Optimization explanation →

A Snowflake architect is evaluating the performance of a large table that is frequently queried with filters on multiple columns. The architect wants to identify which factors can cause poor pruning and lead to full table scans. (Choose two.)

Question 16mediummulti select

Read the full Performance Optimization explanation →

A Snowflake architect is investigating why a recurring ETL query that loads data into a large table is running slower than expected. The query performs a MERGE from a staging table. The architect wants to identify which TWO factors are most likely contributing to the performance degradation. (Choose two.)

Question 17mediummulti select

Read the full Performance Optimization explanation →

An architect is optimizing a Snowflake environment for a workload that includes many concurrent queries with varying complexity. The goal is to minimize queuing and ensure efficient resource utilization. Which two actions should the architect take to achieve this? (Choose two.)

Question 18hardmulti select

Read the full Performance Optimization explanation →

An architect is designing a real-time dashboard that queries a large fact table with frequent filters on both REGION and SALE_DATE. The table is currently clustered by SALE_DATE, but queries filtering on REGION alone are slow. The architect decides to change the clustering key to (REGION, SALE_DATE). Which two statements are true regarding the impact of this change? (Choose two.)

Question 19hardmultiple choice

Read the full Performance Optimization explanation →

An architect is analyzing a query that joins a large fact table with a small dimension table. The query profile shows a broadcast join, but the architect suspects that a different join strategy would be more efficient because the dimension table is actually not small enough to broadcast efficiently. Which Snowflake feature should the architect investigate to influence the join strategy?

Question 20mediummultiple choice

Read the full Performance Optimization explanation →

A data engineering team runs a nightly ELT pipeline that loads a 10 TB table into Snowflake using a single large warehouse. After the load, they run a complex aggregation query that joins this table with a smaller dimension table. The query is slow, and the Query Profile shows that most time is spent in the join operator because the large table is scanned in full. The team wants to reduce the scan time for this recurring query. Which Snowflake feature should they implement?

Question 21mediummultiple choice

Read the full Performance Optimization explanation →

A Snowflake virtual warehouse serving a BI dashboard experiences high queueing during peak hours. The warehouse is a multi-cluster warehouse with a minimum of 2 and maximum of 10 clusters, scaling policy set to Standard. The queries are short and highly concurrent, and the architect observes that additional clusters start only after significant queueing. Which action should the architect take to reduce latency for these short queries?

Question 22mediummultiple choice

Read the full Performance Optimization explanation →

An architect maintains a Snowflake table with a clustering key on the DATE column. Users frequently run queries filtering on DATE ranges and also on a high-cardinality CUSTOMER_ID column. The architect notices that clustering depth is increasing over time. Which action will most effectively reduce clustering depth and maintain query performance?

Question 23mediummulti select

Read the full Performance Optimization explanation →

A Snowflake architect is optimizing a large fact table that is frequently joined with dimension tables. The fact table is several terabytes and is filtered by date and region. The architect wants to improve join performance. Which two actions should the architect take? (Choose two.)

Question 24hardmulti select

Read the full Performance Optimization explanation →

A Snowflake architect is tuning a complex query that aggregates a 5 TB sales table. The query profile shows a high percentage of time in the Aggregation operator and significant data spillage to local disk. The warehouse is a 2X-Large multi-cluster warehouse with maximum clusters set to 1. Which TWO actions are most likely to reduce spillage and improve aggregation performance? (Choose two.)

Question 25hardmultiple choice

Read the full Performance Optimization explanation →

A data architect is tuning a complex query that joins a large fact table with several dimension tables. The query profile shows a significant amount of time spent in the Join operator, specifically on a hash join. The fact table is clustered on a date column, and the join key is a surrogate key with high cardinality. Which optimization technique is most likely to improve join performance?

Question 26hardmulti select

Read the full Performance Optimization explanation →

A Snowflake architect is designing a performance optimization strategy for a data warehouse that experiences high concurrency during business hours. The architect observes that many queries are queued, and some queries are spilling to remote storage. The architect wants to reduce queuing and improve overall throughput without changing the queries. Which two actions should the architect take? (Choose two.)

Question 27easymultiple choice

Read the full Performance Optimization explanation →

A Snowflake architect is analyzing a query that runs slowly due to a large amount of data being scanned from a table. The table is not clustered, and queries frequently filter on a low-cardinality column. Which of the following is the most appropriate initial step to improve query performance?

Question 28mediummultiple choice

Read the full Performance Optimization explanation →

A architect is troubleshooting a slow-running query on a large fact table. The Query Profile shows that over 90% of the total execution time is spent in a TableScan operator, with a large number of partitions scanned and few partitions pruned. The table is not clustered. The query filters on a high-cardinality column named transaction_id. Which Snowflake feature should the architect enable to improve performance for this specific query pattern?

Question 29mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 30mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 31easymultiple choice

Read the full Performance Optimization explanation →

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

Question 32hardmulti select

Read the full Performance Optimization explanation →

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

Question 33mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 34hardmultiple choice

Read the full Performance Optimization explanation →

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

Exhibit

CLI Output: 
SELECT count(*) FROM sales WHERE date >= '2023-01-01' AND date <= '2023-01-31';
Profile:
- Total Execution Time: 45s
- Partitions Scanned: 1000
- Partitions Total: 1000

Question 35mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 36hardmulti select

Read the full Performance Optimization explanation →

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

Question 37mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 38mediummultiple choice

Read the full Performance Optimization explanation →

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)?

Question 39mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 40hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 41easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 42mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 43mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 44hardmulti select

Read the full Performance Optimization explanation →

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)?

Question 45mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 46easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 47mediummulti select

Read the full Performance Optimization explanation →

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?

Question 48mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 49hardmulti select

Read the full Performance Optimization explanation →

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

Question 50easymultiple choice

Read the full Performance Optimization explanation →

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

Question 51mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 52mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 53mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 54hardmultiple choice

Read the full Performance Optimization explanation →

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

Question 55hardmulti select

Read the full Performance Optimization explanation →

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

Question 56mediummultiple choice

Read the full Performance Optimization explanation →

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

Question 57mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 58mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 59hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 60mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 61mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 62mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 63hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 64hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 65mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 66mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 67easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 68easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 69hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 70easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 71hardmulti select

Read the full Performance Optimization explanation →

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.)

Question 72mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 73hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 74hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 75hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 76mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 77easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 78mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 79hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 80mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 81easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 82mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 83hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 84easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 85mediummultiple choice

Read the full Performance Optimization explanation →

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?

Question 86mediummulti select

Read the full Performance Optimization explanation →

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.)

Question 87hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 88hardmultiple choice

Read the full Performance Optimization explanation →

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?

Question 89mediummulti select

Read the full Performance Optimization explanation →

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.)

Question 90easymultiple choice

Read the full Performance Optimization explanation →

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?

Question 91mediummultiple choice

Read the full Performance Optimization explanation →

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?