Performance Optimization

A warehouse is being used for both ETL processes and ad-hoc BI reporting. Users report that BI reports are slow during ETL runs. What is the best optimization?

A user runs the same complex analytical query twice within 5 minutes and notices the second execution is nearly instantaneous. Which Snowflake feature is primarily responsible for this performance improvement?

Which of the following is the best practice for using the query profile to identify performance bottlenecks?

A data engineer is reviewing a query profile for a query that performs a large join. The profile shows a high percentage of time spent in the 'Join' node with significant 'Bytes spilled to local storage'. The engineer wants to reduce local spilling without changing the query. Which action is most appropriate?

Refer to the exhibit. A data engineer is analyzing a Query Profile for a long-running join operation. Based on the provided JSON statistics, what is the most effective action to improve the performance of this specific query?

Which of the following is the most cost-effective way to handle massive concurrent read-only queries?

A user complains that a dashboard query is slow during peak hours. The warehouse is configured with auto-suspend and auto-resume. What is the most likely cause of the latency observed during the initial execution?

A query is slow because it is scanning a massive table. The filters are on columns that are not currently clustered. What is the most immediate step to improve performance?

A data engineer notices that a query performing a large GROUP BY on a high-cardinality column is slow. The Query Profile shows that the aggregation step is spilling to local disk. The engineer wants to reduce local spilling without changing the query logic. Which action is most appropriate?

A data engineer is analyzing a query that filters on a column named 'status' which has only 5 distinct values. The table has 10 billion rows. The query is running slowly, and the query profile shows a full table scan. Which action is most likely to improve performance?

A data engineer is analyzing a Query Profile for a query that joins a large fact table to a small dimension table. The profile shows a significant amount of time spent in the 'Join' operator, and the 'Bytes spilled to remote storage' metric is high. The engineer has already confirmed that the small table is used as the build side. Which optimization should the engineer try next to reduce remote spilling?

A data engineer is analyzing a slow-running query that performs a large aggregation over a table with many columns. The query profile shows that most time is spent in the 'Aggregate' operator, and there is significant data spilling to local disk. Which action is most likely to reduce the spilling and improve performance?

Refer to the exhibit. The 'sales' table is very large and not clustered. Which action will provide the most significant performance improvement for this query?

A data engineer notices that a daily aggregation query scans 4 TB of a 5 TB table even though it only needs the last seven days of data. The table has a DATE column but no clustering key, and the query filters with DATE >= CURRENT_DATE - 7. Query Profile shows almost no partition pruning. What is the most effective change to reduce bytes scanned?

A user is experiencing 'Data Spilling' in a query that performs a complex window function over a very large dataset. What is the most likely reason this is occurring and how should it be addressed?

A data engineer is optimizing a query that joins a very large fact table to a small dimension table. The Query Profile shows the small table being redistributed across all nodes before the join. Which action is most likely to improve performance?

When analyzing a Query Profile, which indicator most strongly suggests that 'Partition Pruning' is performing effectively?

A data engineer wants to optimize a query that performs a point lookup on a value nested deep within a VARIANT column in a 50TB table. Which approach is the most effective for optimizing this lookup?

A data engineer is investigating a query that reads a large table and returns only a few rows after filtering on a high-cardinality column. The Query Profile shows a high percentage of partitions scanned relative to partitions total. Which two actions should the engineer take to improve pruning? (Choose two.)

A table experiences performance degradation over time due to frequent DML operations (INSERT/UPDATE/DELETE). What is the most likely cause?

What is the primary benefit of using a 'Materialized View' over a standard view in Snowflake?

While reviewing a Query Profile, a data engineer notices a 'Join' operator where the number of output rows is significantly larger than the sum of the input rows. Which TWO steps should be taken to resolve this performance issue? (Select TWO)

A data engineer runs a query that joins a 5 TB fact table to a 200 GB dimension table. The query profile shows a broadcast operation for the dimension table and a local spilling node on the fact table. The engineer wants to reduce local spilling without changing the query results. The warehouse is a multi-cluster warehouse with sufficient memory. Which action is most likely to reduce local spilling?

A data engineer is loading 1TB of data from an S3 bucket into Snowflake using the COPY command. The data consists of 10,000 small files (approx. 100KB each). How will this file size affect the loading performance?

Refer to the exhibit. Based on the Query Profile, what is the most likely bottleneck for this query?

Which TWO of the following are considered 'anti-patterns' for performance in Snowflake?

A data engineer maintains a table that receives continuous small INSERT statements throughout the day. Query performance on this table has degraded even though total data volume is modest, and the Query Profile shows many very small micro-partitions being scanned. Which action best addresses the underlying cause?

A data engineer notices that a query against a large table returns results quickly when filtering on one column but scans the entire table when filtering on another column. Both columns are used in equality predicates. What is the most likely explanation?

Which of the following scenarios is most appropriate for using a Materialized View to optimize performance?

A data engineer is monitoring warehouse performance and notices a high 'Warehouse Overload' status. Which TWO metrics from the Query History or Warehouse Load monitoring views should be prioritized to confirm the warehouse is undersized? (Select TWO)

A data engineer notices that a dashboard query returns instantly on repeat executions but takes much longer the first time each morning. No data has changed overnight. Which Snowflake feature is most directly responsible for the fast repeat executions?

A data engineer is optimizing a Snowflake environment for a data warehouse that experiences high concurrency during business hours. The engineer observes that many queries are small and frequent, and the warehouse is often queued. The engineer wants to reduce queueing and improve throughput without increasing cost significantly. Which two actions should the engineer take? (Choose two.)

A data engineer runs a long-running aggregation query and inspects the Query Profile. The profile shows a single operator with an output row count roughly 400 times larger than its input row count, and the downstream operator is bottlenecked. Which action should the engineer take to resolve this?

A company is using a Multi-cluster Warehouse (MCW) with a 'Standard' scaling policy. Users report that during peak morning hours, query queuing occurs briefly before a new cluster starts. What is the effect of changing the scaling policy to 'Economy' in this scenario?

A data engineer runs a query on a Snowflake virtual warehouse that scans a 5 TB table but returns only 100 rows. The Query Profile shows that the TableScan operator processed 5 TB of data even though a highly selective filter on a non-clustered column was applied. The warehouse is sized Medium. Which action will most effectively reduce the amount of data scanned by this query?

A data engineer is investigating a slow query that scans a large table. The Query Profile shows that the table scan is reading a very high number of micro-partitions compared to the total number of partitions in the table. The query filters on a column that is not the clustering key. What is the most likely explanation for the high number of partitions read?

A data engineer notices that a recurring daily query that joins a large fact table with a date dimension is taking longer than expected. The query filters on a date range and joins on a date key. The fact table is clustered by date_key, and the date dimension is small. The query profile shows a Cartesian join warning. Which action should the engineer take to resolve the Cartesian join and improve performance?

A data engineer runs a transformation that loads a 50 GB staged file into a target table using a COPY INTO statement with a single large file. The warehouse is a MEDIUM multi-cluster warehouse. The load takes far longer than expected because one node processes the entire file. Which change most directly improves load throughput?

A data engineer is optimizing a query that aggregates a large sales table by product category and region. The query currently uses a GROUP BY on two high-cardinality columns and produces a large intermediate result set. The query profile shows significant network traffic and remote spilling. The engineer wants to reduce remote spilling without changing the aggregation logic. Which approach is most likely to achieve this?

Which of the following describes the purpose of the 'Result Cache' in Snowflake?

A data engineer is analyzing a Query Profile for a query that joins three large tables. The profile shows that the optimizer chose a broadcast join for one of the joins, but the broadcasted table is 50 GB. The query is spilling to remote disk. Which action is most likely to improve performance by changing the join strategy?

Refer to the exhibit. A data engineer runs this query to investigate clustering costs. The output shows high credit consumption but the 'Clustering Depth' of the table remains high. What is the most likely cause of this behavior?

A data engineer notices that a dashboard query is running slowly. The query filters a large table on a column with high cardinality and returns a small number of rows. The engineer wants to improve performance for this specific query pattern. Which Snowflake feature is most appropriate?

A data engineer is reviewing a Query Profile for a query that filters a large table on a column with a very high cardinality, such as a UUID. The query applies an equality predicate on that column and returns a single row. The table is not clustered on that column. Which Snowflake feature is designed to accelerate this type of highly selective point lookup?

A query is filtering a table based on a 'TRANSACTION_DATE' column using a range (e.g., BETWEEN '2023-01-01' AND '2023-01-31'). The table is 500GB and not explicitly clustered. Why might this query still perform well and show good partition pruning?

A data engineer runs a dashboard query that aggregates sales by region for the current month. The query scans a large fact table but returns only a few rows. The Query Profile shows that most time is spent scanning micro-partitions that do not contain the current month's data. Which feature should the engineer use to improve performance for this recurring query?

A user runs a query twice in succession with no data changes. The second query completes in near-zero time. What feature is responsible for this performance?

When designing a clustering key for a table that is frequently joined with other tables, which strategy generally provides the best performance for the join operation?

A data engineer is tuning a query that joins two large tables. The query is performing a 'Remote Disk Spilling' operation. Which optimization strategy is most effective to resolve this?

A data engineer maintains a very large fact table that is loaded nightly with the previous day's orders. Analysts run reports that almost always filter on ORDER_DATE and also aggregate by CUSTOMER_ID. The engineer wants search optimization to accelerate point lookups on ORDER_ID and CUSTOMER_ID without adding clustering maintenance overhead. Which configuration best meets these requirements?

A data engineer runs a nightly transformation that joins a 12 TB fact table to a 300 GB dimension table. The fact table is clustered by DATE_KEY, and the dimension is small enough to fit in memory. Query Profile shows the join operator building a hash table on the 12 TB side and spilling to remote disk. The engineer wants to eliminate the remote spill without increasing warehouse size. Which action should the engineer take?