ARA-C01 Performance Optimization Practice Test 2 — 15 Questions

Question 1

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

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

The target table has many micro-partitions due to frequent small incremental loads.. Many small micro-partitions from frequent incremental loads increase metadata and scanning overhead, slowing MERGE operations. Non-deterministic functions in the join condition can also block pruning and other optimizations, forcing broader scans. Together, these factors commonly degrade MERGE performance on large tables, and addressing them—by consolidating loads and using deterministic join keys—can restore efficiency.

Answer

The warehouse used for the MERGE is set to auto-suspend after 60 seconds of inactivity.

Answer

The target table is clustered on a column that is not used in the MERGE join or filter.

Answer

The staging table is loaded with a COPY INTO statement that uses a file format with compression.

Question 2

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

Accepted Answer

Configure separate warehouses for different query types (e.g., ETL, reporting, ad-hoc).. Separating workloads into dedicated warehouses prevents resource contention and allows independent scaling. Using a Standard scaling policy on the reporting warehouse ensures that additional clusters start immediately when queries queue, minimizing wait times. Together, these actions optimize concurrency and resource utilization.

Answer

Set the warehouse to auto-suspend after a short period to save costs.

Answer

Implement a multi-cluster warehouse with a scaling policy set to Economy.

Answer

Enable the Query Acceleration Service on warehouses handling large scan queries.

Question 3

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

Accepted Answer

The change will require reclustering the entire table, which may incur significant credit consumption and time.. Changing the clustering key to (REGION, SALE_DATE) improves pruning for queries filtering on both columns and for those filtering on REGION alone, because REGION becomes the leading column. However, it degrades performance for queries filtering only on SALE_DATE. The change also triggers a full reclustering of the table, which consumes credits and time. These trade-offs must be carefully evaluated.

Answer

Queries filtering on REGION alone will not benefit from the new clustering key because REGION is not selective enough.

Answer

Queries filtering on SALE_DATE alone will benefit from clustering because SALE_DATE is the second column in the clustering key.

Answer

Automatic clustering will be disabled by default after changing the clustering key, requiring manual reclustering.

Question 4

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?

Accepted Answer

The query profile's join operator details and the optimizer's statistics.. The query profile exposes join operator statistics, such as row counts and bytes processed, which the optimizer uses to select a join strategy. If the dimension table is larger than expected, the profile will show that, and the architect can address it by updating statistics, clustering, or rewriting the query. There is no direct join strategy hint in Snowflake, so the profile is the right tool to investigate.

Answer

The JOIN_STRATEGY hint in the query.

Answer

The warehouse's MAX_CONCURRENCY_LEVEL parameter.

Answer

The USE_CACHED_RESULT session parameter.

Question 5

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?

Accepted Answer

Define a clustering key on the columns used in the join and filter predicates.. The query performs a full scan of a large table, causing high I/O and long join times. Clustering the table on the join and filter columns enables partition pruning, so Snowflake reads only relevant micro-partitions. This reduces the amount of data scanned and speeds up the join. Other options either target different use cases or do not address the underlying scan inefficiency.

Answer

Create a materialized view that pre-aggregates the join result.

Answer

Enable the Search Optimization Service on the large table.

Answer

Increase the size of the warehouse used for the query.

Question 6

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?

Accepted Answer

Set the warehouse to start clusters immediately when queries are queued by using the Standard scaling policy with a lower queueing threshold.. For short, highly concurrent queries, the Standard scaling policy starts additional clusters more quickly than Economy, reducing queueing latency. Economy is designed for longer-running queries and delays cluster startup to avoid over-provisioning. The other options either delay startup or do not address the concurrency bottleneck.

Answer

Switch to a larger warehouse size to increase concurrency per cluster.

Answer

Increase the maximum cluster count and enable the scaling policy to start clusters more quickly.

Answer

Change the scaling policy to Economy to start clusters more aggressively.

Question 7

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?

Accepted Answer

Add CUSTOMER_ID as a second clustering column to the existing clustering key.. The increasing clustering depth indicates that the current clustering key is not optimally aligned with query patterns that also filter on CUSTOMER_ID. Extending the clustering key to include CUSTOMER_ID enables Snowflake to maintain co-locality for both columns, reducing depth and preserving pruning efficiency. Automatic clustering will then reorganize micro-partitions accordingly.

Answer

Manually recluster the table by executing ALTER TABLE ... RECLUSTER.

Answer

Suspend the automatic clustering service to prevent further depth increase.

Answer

Convert the table to a transient table to reduce clustering overhead.

Question 8

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

Accepted Answer

Ensure that the join keys are of the same data type in both tables.. Matching data types on join keys avoids implicit casts that can hinder pruning and increase compute. Clustering the fact table on frequently filtered columns like date and region enables effective micro-partition pruning, reducing the data scanned before the join. Together, these actions improve join performance by minimizing data movement and processing.

Answer

Cluster the fact table on the join key columns.

Answer

Use a larger warehouse size to increase memory for hash joins.

Answer

Convert the fact table to a materialized view to pre-join with dimensions.

Question 9

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

Accepted Answer

Increase the warehouse size to 4X-Large.. Spillage during aggregation indicates insufficient memory for the group-by operation. Increasing warehouse size provides more memory and compute per node, directly reducing spillage. Clustering on the GROUP BY columns reduces the volume of data that must be shuffled and aggregated, also lowering memory pressure. The other options either target concurrency (multi-cluster), offload scans (QAS), or are too general (filtering) to reliably resolve the specific spillage issue.

Answer

Rewrite the query to use a smaller result set by filtering earlier.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Set the multi-cluster warehouse maximum clusters to 4.

Question 10

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?

Accepted Answer

Add a clustering key on the join key of the fact table.. The hash join is spending significant time because the join key is not co-located. Clustering the fact table on the join key aligns micro-partitions so that matching keys are stored together, reducing data shuffling and hash table size. This directly addresses the join bottleneck, whereas other options either target different bottlenecks or are less effective for this specific issue.

Answer

Use a materialized view that pre-joins the fact and dimension tables.

Answer

Increase the warehouse size to add more compute resources.

Answer

Enable the Search Optimization Service on the fact table.

Question 11

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

Accepted Answer

Configure the warehouse as a multi-cluster warehouse with a minimum and maximum cluster count greater than one.. The primary issue is queuing due to high concurrency, which is best addressed by scaling out with a multi-cluster warehouse. Spilling can be mitigated by isolating those queries to a larger, dedicated warehouse. These actions reduce contention and improve throughput without modifying queries.

Answer

Set the warehouse to auto-suspend after a short period to save credits.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Increase the size of the warehouse to add more memory per node.

Question 12

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?

Accepted Answer

Ensure that the low-cardinality column is used in the query's WHERE clause with effective pruning.. The most appropriate initial step is to ensure that the low-cardinality column is used effectively in the WHERE clause, as this can enable pruning if the data is ordered or if statistics allow. Clustering on low-cardinality columns is often not beneficial, and scaling up or using materialized views are not first-line fixes. Effective filtering can reduce data scanned without additional cost.

Answer

Create a materialized view that includes the low-cardinality column as a filter.

Answer

Use a larger warehouse to increase compute power.

Answer

Define a clustering key on the low-cardinality column.

Question 13

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?

Accepted Answer

Search Optimization Service. Search Optimization Service is specifically built to accelerate selective point lookups on high-cardinality columns such as transaction_id. By creating a persistent search access path, it allows Snowflake to quickly find matching rows without scanning all micro-partitions. Clustering, materialized views, and Query Acceleration Service each address different performance patterns and would not provide the same targeted improvement for this query.

Answer

Clustering key on transaction_id

Answer

Query Acceleration Service

Answer

Materialized view on transaction_id

Question 14

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?

Accepted Answer

Define a clustering key on the timestamp column.. Clustering by a timestamp column significantly improves range query performance by physically organizing data according to the filter criteria. Snowflake's automatic clustering service then maintains this order as DML operations occur. This reduces micro-partition scanning during query execution, minimizing I/O overhead. Proper clustering is essential for large datasets where full table scans lead to excessive resource consumption and longer wait times for end users.

Answer

Add a search optimization service index on the timestamp column.

Answer

Increase the warehouse size to handle the large table scan.

Answer

Convert the table to a temporary table to reduce metadata overhead.

Question 15

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

Accepted Answer

Increase the warehouse size.. Remote disk spilling occurs when the active memory of a warehouse node is insufficient to hold the working set of data for an operation like a join or aggregation. By increasing the warehouse size, you provide more memory per node, allowing the query to complete in-memory. This prevents the high-latency I/O operations associated with spilling to remote cloud storage, thereby drastically reducing query execution time.

Answer

Enable multi-cluster warehouse auto-scaling.

Answer

Change the table join order in the SQL statement.

Answer

Remove the query from the warehouse and use a serverless task.