Performance Optimization practice questions

Increase the warehouse size to X-Large.

Why it fails: Scaling up provides more compute resources but does not inherently address inefficient data access patterns. While it might reduce runtime, it doubles credit consumption per hour, failing the requirement to maintain efficiency. You should optimize the data layout before increasing compute resources to ensure cost-effective performance.

Use a result cache to store the join result.

Why it fails: The result cache is automatically populated by Snowflake when a query is executed. While it helps performance for identical queries, it does not optimize the underlying join execution or manage high-cardinality data access patterns for dynamic or non-repeating queries, making it ineffective for improving the performance of complex analytical workloads.

Change the table type from standard to transient.

Why it fails: Transient tables are intended for temporary data with shorter retention periods for Fail-safe. They have no impact on query performance or join execution efficiency compared to standard tables. Modifying the table type does not change the physical data organization or reduce the I/O required for join operations.

Total number of partitions.

Why it fails: The total number of partitions in a table is a static metadata property. While having too many tiny partitions can impact metadata overhead, the total count itself is not a direct indicator of a remote disk I/O bottleneck during the execution phase of a specific query profile.

Query result cache hits.

Why it fails: Query result cache hits are unrelated to remote disk I/O bottlenecks. A result cache hit means the query finished instantly because the result was already computed. This metric is a positive sign of performance, not an indicator of an underlying I/O bottleneck that requires optimization.

Number of active users.

Why it fails: The number of active users is a concurrency metric. While high concurrency can lead to resource contention, it is not a direct indicator of remote disk I/O performance within a single query's profile. You must look at execution-specific metrics to diagnose physical storage and compute bottlenecks.

Increase the number of warehouse clusters.

Why it fails: Multi-cluster warehouses address concurrency by scaling out compute resources. They do not improve the pruning efficiency of a single query. Regardless of how many clusters are running, the query will still scan the same number of micro-partitions unless the data itself is organized for better partition pruning.

Use a materialized view to pre-aggregate the data.

Why it fails: While a materialized view can improve performance for aggregation queries, it does not improve the pruning performance of the underlying base table. The base table's physical layout remains unchanged, so queries hitting the base table directly will still suffer from the same pruning inefficiencies as before.

The total number of rows in the table.

Why it fails: Both materialized views and clustering keys can handle large tables. The decision is not based on total row count, but rather on the query patterns. Whether you have millions or billions of rows, the choice depends on whether you need pre-computed aggregates or efficient range-based filtering.

The number of users accessing the system.

Why it fails: The number of users is not a primary criterion for choosing between materialized views and clustering keys. Concurrency is better handled by multi-cluster warehouses. Both optimization techniques help individual queries run faster, which indirectly benefits all users, but the choice is based on query structure, not user count.

The cloud provider being used (AWS, Azure, or GCP).

Why it fails: Snowflake's performance optimization features, including materialized views and clustering keys, are entirely cloud-agnostic. They function identically regardless of the underlying cloud provider. This portability is a core feature of the Snowflake platform, ensuring that optimization strategies remain consistent across different infrastructure environments.

Set the warehouse auto-suspend to 10 minutes.

Why it fails: A 10-minute auto-suspend is generally too long for most workloads, leading to unnecessary costs. Shortening this to 60 seconds or less is preferred for most scenarios to stop billing as soon as the compute is idle, ensuring resources are not wasted when no queries are active.

Set the statement_timeout_in_seconds to 0.

Why it fails: Setting the timeout to zero allows queries to run indefinitely. This is poor practice for resource management, as it prevents the system from automatically terminating runaway or inefficient queries, which could lock up valuable compute resources and cause queueing for other users within the same warehouse.

Use the 'Economy' scaling policy for all production warehouses.

Why it fails: The Economy policy prioritizes cost savings by keeping existing clusters busy before spinning up new ones. While cost-effective, it increases the likelihood of query queueing during peak loads. For production environments, the 'Standard' policy is usually preferred to balance performance and cost more effectively under load.

Increase the number of clusters in the warehouse.

Why it fails: Scaling out the warehouse adds more compute nodes, but it does not change the physical distribution of data. In fact, adding nodes might increase the complexity of the data shuffle if the data is not clustered effectively, potentially worsening the network transfer overhead for the query.

Change the warehouse type to 'Snowpark-optimized'.

Why it fails: Snowpark-optimized warehouses provide more memory per core for memory-intensive tasks, but they do not solve network-related issues caused by data shuffling during joins. Network costs are a result of data distribution and key alignment, which requires structural changes like clustering, not just compute-specific resources.

Enable search optimization on the join keys.

Why it fails: Search optimization is designed to speed up point lookups and highly selective filters by creating an index of the data. It is not designed to optimize join performance between large datasets, as joins require scanning and comparing full sets of data rather than isolated, indexed values.

Query Timeout settings.

Why it fails: While statement timeouts can stop individual long-running queries, they do not provide a comprehensive mechanism for tracking and limiting overall warehouse credit usage over time. They are a granular control, whereas resource monitors offer a broader administrative approach to cost management and consumption budgeting.

Search Optimization Service.

Why it fails: The Search Optimization Service is for improving performance of point-lookup queries, not for managing costs. It actually adds a maintenance cost for the background service that keeps the indices updated, meaning it would be counterproductive if the goal were to reduce total credit consumption.

Clustering Keys.

Why it fails: Clustering keys are used for performance optimization by organizing data to improve partition pruning. They have no relationship to cost management or limiting warehouse credit consumption. Implementing clustering keys involves an automated background process that consumes credits, which is the opposite of the stated goal.

Use of clustering keys.

Why it fails: Clustering keys are the mechanism used to improve and maintain clustering. Defining a clustering key is the primary way to reduce clustering depth, not increase it. It instructs the system on how to physically organize the micro-partitions for optimal query performance.

Using smaller warehouse sizes for queries.

Why it fails: Warehouse size affects the compute resources available for query execution, not the physical organization of the data on disk. While a small warehouse might take longer to perform a scan, it does not change the clustering depth of the table itself.

The warehouse is too small for the workload.

Why it fails: If the warehouse were too small, the queries would likely spill to disk or queue up waiting for resources. Since there is no queueing, the warehouse is successfully processing the incoming stream, even if it is doing so at maximum capacity. This is an efficient state, not a failure.

The queries are blocked by network latency.

Why it fails: Network latency would show up in the Query Profile as a high 'Network' wait time, not as a high 'Load' metric on the warehouse. Load is a measure of compute unit utilization, not external network factors that delay query completion.

The auto-suspend time is too high.

Why it fails: Auto-suspend time affects cost by determining how long a warehouse stays active after queries stop. It does not affect the load metric while queries are actively running. A high auto-suspend would lead to higher costs, but it would not influence the warehouse load during peak usage hours.

The number of users concurrently running the same query.

Why it fails: While concurrent users benefit from faster results, Snowflake's Result Cache already handles repeated queries from multiple users efficiently if the underlying data hasn't changed. Materialized Views are specifically designed to pre-compute data to speed up the first execution and subsequent variations of a query, rather than just handling concurrency.

Whether the query uses non-deterministic functions like CURRENT_TIMESTAMP().

Why it fails: Non-deterministic functions cannot be used in Materialized Views because the output must be predictable and static until the next data refresh. While this is a limitation, it is a syntax restriction rather than a performance optimization factor that a data engineer must evaluate when comparing against other caching strategies.

The total number of columns in the base table schema.

Why it fails: The number of columns in a base table does not inherently dictate the performance of a Materialized View. Performance is driven by the volume of data processed and the complexity of the view's definition. Standard micro-partitioning handles wide tables efficiently, so schema width is not a primary optimization consideration.

Queries that include a LIMIT clause without an ORDER BY.

Why it fails: Queries with a LIMIT clause but no ORDER BY are typically satisfied very quickly by the first few micro-partitions the warehouse encounters. Since these queries do not require scanning a large volume of data to find the result set, they do not provide the heavy lifting profile that QAS targets.

Queries that involve small tables with complex window functions.

Why it fails: Complex window functions on small tables are usually compute-intensive but not data-intensive. QAS is optimized for offloading data-heavy operations like scans and filters. Window functions require sophisticated inter-node communication that is better handled by the consistent, dedicated resources of the primary virtual warehouse rather than the acceleration service.

Increase the warehouse size to a 3X-Large.

Why it fails: While a larger warehouse provides more compute, it cannot solve the fundamental bottleneck of scanning many files in cloud storage. The overhead of listing and reading external files often outweighs the raw compute power, meaning that metadata-level optimizations are far more effective for improving external table performance than simply scaling up.

Convert the external table to a standard Snowflake table.

Why it fails: While converting to a standard table would improve performance, it changes the architecture and storage model. The question asks how to improve the performance *of the external table* itself. Migration is a valid alternative, but it does not address the optimization of the existing external table structure.

Disable the METADATA_COLUMNS parameter in the table definition.

Why it fails: Metadata columns like FILENAME and FILE_ROW_NUMBER provide useful information and do not significantly impact the performance of the query engine. Disabling them would not address the core performance issues related to data scanning, file formats, or partition pruning that typically slow down external table queries.

The warehouse is too small, causing the optimizer to choose a CartesianJoin as a fallback.

Why it fails: Warehouse size affects execution speed and spilling, but it does not change the join algorithm from hash join to CartesianJoin. The optimizer selects join operators based on query structure and statistics, not compute resources. A smaller warehouse might cause spilling or longer runtimes, but a CartesianJoin is a logical plan issue, not a resource-driven fallback.

The small dimension table lacks a clustering key, forcing a full scan and Cartesian product.

Why it fails: Clustering keys improve pruning for large tables, but a missing clustering key on a small dimension would not cause a CartesianJoin. The optimizer can still perform an efficient hash join if the join condition is valid. The CartesianJoin specifically indicates the absence of a usable join predicate, not a lack of clustering. Clustering is irrelevant for this small table.

The fact table is not clustered on the join key, leading to a CartesianJoin.

Why it fails: Clustering can improve join performance by enabling better pruning, but it does not cause a CartesianJoin. Even without clustering, an equi-join will typically use a hash join. A CartesianJoin arises when the join condition is missing or invalid, not from clustering choices. Clustering is a physical optimization, not a determinant of join type.

Add a clustering key on the group-by columns to improve data pruning and reduce the number of rows processed.

Why it fails: Clustering on group-by columns can improve pruning for filtered queries, but it does not directly reduce the memory footprint of an aggregation. The aggregation still needs to process all rows that pass the filter. While pruning can reduce input size, it may not eliminate spilling if the aggregation itself requires more memory than available. Clustering is more effective for selective filters, not for reducing aggregation memory.

Enable the USE_CACHED_RESULT parameter to reuse previous aggregation results.

Why it fails: USE_CACHED_RESULT can speed up repeated identical queries by reusing results, but it does not help the first execution of a query that is spilling. Caching only applies when the exact same query has been run before and the underlying data has not changed. In this scenario, the query is likely new or data has changed, so caching would not reduce spilling during execution.

Increase the warehouse size to Large to provide more memory for the aggregation.

Why it fails: Increasing warehouse size would indeed provide more memory and likely reduce spilling, but the engineer explicitly wants to avoid increasing warehouse size. Therefore, this action does not meet the requirement. While it is a valid optimization, it is not the correct choice given the constraint. The goal is to optimize within the current warehouse size.

Define a clustering key on the low-cardinality column (STATUS).

Why it fails: Clustering on a low-cardinality column like STATUS can group data by status values, but because there are few distinct values, each micro-partition may still contain many rows per status. This limits pruning effectiveness for queries that also filter on high-cardinality columns. It is generally better to cluster on high-cardinality columns that are frequently used in filters, as they provide more selective pruning.

Create a materialized view that pre-filters data by STATUS.

Why it fails: A materialized view pre-filters data by STATUS, but if queries also filter on ORDER_ID, the materialized view may not help unless it also includes ORDER_ID. Materialized views are best for repeated queries with the same filter patterns. Here, the query pattern includes both columns, so a materialized view on STATUS alone would not provide optimal pruning for ORDER_ID filters.

Enable the SEARCH_OPTIMIZATION parameter on the table.

Why it fails: SEARCH_OPTIMIZATION is a table parameter that enables additional metadata for point lookups, but it is primarily for equality searches on columns. It does not replace clustering for range scans or multi-column filters. While it can help with high-cardinality point lookups, it is not as broadly effective as a well-chosen clustering key for the described query patterns.

Rewrite the query to filter each table before the join to reduce the number of rows processed.

Why it fails: Filtering before the join can reduce the volume of data, which may help, but it is not always possible or sufficient. If the join is inherently large due to data skew or many-to-many relationships, filtering may not eliminate spilling. Remote spilling is often caused by uneven data distribution, not just total data volume. Therefore, this action may not be the most effective for the described issue.

Add a clustering key on the join columns of both tables.

Why it fails: Clustering on join columns can improve pruning and reduce the amount of data read, but it does not directly address the memory pressure during the join operation. Remote spilling occurs when the join operation exceeds memory and writes intermediate results to remote storage. While clustering may reduce input size, it may not eliminate spilling if the join itself is memory-intensive. Other techniques like reducing data skew are more directly effective.

Use a larger warehouse size to increase memory for the join.

Why it fails: Increasing warehouse size would provide more memory and likely reduce spilling, but the engineer explicitly wants to avoid increasing warehouse size. Therefore, this action does not meet the requirement. While it is a valid optimization, it is not the correct choice given the constraint. The goal is to optimize within the current warehouse size.

Add a clustering key on the filter column of the base table.

Why it fails: Clustering on the filter column can improve pruning by skipping micro-partitions that do not match the filter. However, if the table is very wide, the query still reads all columns from the remaining micro-partitions. Clustering does not reduce the number of columns read. Therefore, while it may help, it does not address the primary inefficiency of scanning unnecessary columns.

Use the SELECT * EXCLUDE syntax to omit unneeded columns.

Why it fails: SELECT * EXCLUDE is a syntax to exclude columns, but it still requires the query to read all columns from the table before excluding them. It does not reduce the amount of data scanned; it only affects the output. To reduce scanned data, you must avoid reading the columns altogether, such as by using a materialized view or a narrower table.

Enable the USE_CACHED_RESULT parameter to reuse previous query results.

Why it fails: USE_CACHED_RESULT can speed up repeated identical queries by reusing results, but it does not help the first execution or if data has changed. In this scenario, the query is likely new or data is changing, so caching would not reduce the data scanned. The issue is the wide table scan, not repeated execution.

Disable the query acceleration service for the warehouse.

Why it fails: The query acceleration service offloads portions of a query to shared compute resources and is designed to improve performance for scans and certain predicates. It does not cause local spilling, and disabling it would remove a potential performance benefit. Spilling is a memory pressure symptom within the warehouse, not an effect of query acceleration, so this action would not resolve the spill.

Rewrite the query to use a LEFT JOIN instead of an INNER JOIN.

Why it fails: Changing the join type does not reduce the build-side size or the amount of data processed in a HashJoin. A LEFT JOIN may actually process the same or more rows depending on the data, and it does not change the fundamental memory requirement for the hash table. The spilling is caused by the build side not fitting in memory, which is independent of join semantics.

Change the warehouse to a Snowpark-optimized warehouse.

Why it fails: Snowpark-optimized warehouses provide more memory per node for memory-intensive workloads such as Python UDFs and Snowpark transformations. They are not the standard remedy for a SQL HashJoin spill, and they cost more credits per second. While they may reduce spilling by adding memory, they do not address the root cause of a large build side, and the credit cost may be unjustified for a batch SQL join.

Increase the warehouse size to improve concurrency.

Why it fails: Increasing warehouse size provides more compute resources per node, which can improve performance for individual queries, but it does not reduce the amount of data scanned. Concurrency is better addressed by using multi-cluster warehouses, which allow multiple clusters to handle concurrent queries. Scaling up a single warehouse does not inherently improve concurrency if the bottleneck is data scanning; it may help but is not as targeted as reducing scanned data through clustering.

Create a materialized view that pre-aggregates the data by the high-cardinality column.

Why it fails: A materialized view pre-aggregates data and can improve performance for specific queries, but it does not reduce the amount of data scanned for queries that filter on the high-cardinality column without using the pre-aggregated results. Materialized views are beneficial for repetitive aggregations, but they add storage and maintenance overhead. For ad-hoc filtering on a high-cardinality column, a clustering key is more effective because it organizes the data for pruning, directly reducing scanned bytes.

Enable the search optimization service on the high-cardinality column.

Why it fails: The search optimization service is designed to improve performance of point lookups and selective queries, but it is not intended for high-cardinality columns where queries scan large portions of the table. It maintains a search access path for equality and IN predicates, which can help with highly selective queries, but it does not reduce scanning for range filters or broad queries. For high-cardinality columns with frequent filtering, clustering is more appropriate.

Shorten the nightly transformation window by running the transformation warehouse at a larger size.

Why it fails: Increasing the transformation warehouse size may finish the ELT job sooner, but it does not change how much work the reporting warehouse performs when dashboards run. The dashboards query a separate warehouse, so their latency is governed by their own scan volume and query shapes. This action is therefore unrelated to the reported slowness.

Configure the reporting warehouse to auto-suspend after a short idle period so it does not stay running overnight.

Why it fails: Auto-suspend reduces credit consumption during idle periods, which is a cost optimization rather than a latency improvement. In fact, aggressive suspension can cause the first morning queries to pay a cold-start penalty while the warehouse resumes. It does not make the analytic queries themselves run faster, so it does not address the stated goal of reducing dashboard latency.

Move the transformation workload onto the reporting warehouse to consolidate compute.

Why it fails: Consolidating the transformation workload onto the reporting warehouse would create resource contention between heavy ELT jobs and latency-sensitive dashboards. Snowflake isolates compute per warehouse precisely so that workloads do not interfere. Merging them would increase variability and likely worsen dashboard latency, directly opposing the team's objective of faster morning queries.