ARA-C01 Performance Optimization Practice Test 5 — 15 Questions

Question 1

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?

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

Accepted Answer

Create a separate warehouse for the heavy reports and route those queries to it, leaving the dashboard warehouse for interactive queries.. Separate warehouses are the standard way to isolate workloads in Snowflake. By giving heavy reports their own warehouse, the interactive dashboards no longer queue behind them, and each workload can be sized and scaled independently. This avoids over-provisioning a single warehouse and keeps credit usage aligned with each workload's needs.

Answer

Increase the warehouse size to the next larger size so that all queries get more compute resources.

Answer

Enable the Query Acceleration Service on the existing warehouse to offload portions of the heavy queries.

Answer

Set the STATEMENT_TIMEOUT_IN_SECONDS parameter to a low value on the warehouse so long queries are cancelled.

Question 2

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?

Accepted Answer

Adding a clustering key on the timestamp column. Clustering the table on the timestamp column enables partition pruning, so the query only scans micro-partitions that contain the relevant time range. This directly reduces I/O and improves aggregation performance. Search Optimization is for point lookups, larger warehouses add compute but not pruning, and multi-cluster addresses concurrency.

Answer

Increasing the warehouse size

Answer

Using a multi-cluster warehouse

Answer

Enabling the Search Optimization Service on the table

Question 3

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?

Accepted Answer

Rewrite the full outer join as an inner join if the business logic does not require unmatched rows.. Full outer joins prevent the optimizer from pushing dimension filters down to the fact table, forcing a full scan. When unmatched rows are not needed, rewriting as an inner join allows predicate pushdown, so the fact table reads only matching rows. This reduces I/O and speeds up the query without altering the required result set.

Answer

Increase the warehouse size to give the join more memory.

Answer

Materialize the dimension table as a temporary table before the join.

Answer

Add a clustering key on the fact table's join column to improve pruning.

Question 4

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?

Accepted Answer

The small dimension table has grown significantly and is no longer suitable for a broadcast join.. When a small dimension table grows, it may exceed the threshold for a broadcast join, causing the optimizer to switch to a hash join that shuffles the large fact table. This increases data movement and join processing time. The other options would typically manifest in different operators or with additional symptoms like spills.

Answer

The warehouse size was reduced, causing less memory for join operations.

Answer

Statistics on the fact table are stale, leading to a poor join order.

Answer

The fact table has been clustered on a different column, causing poor pruning.

Question 5

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?

Accepted Answer

Increase the size of the virtual warehouse used for the query.. Spilling to local disk during a join indicates that the operation requires more memory than available. Increasing the virtual warehouse size provides more memory and compute resources, allowing the join to process data in memory and reducing spillage. This is a direct and effective first step. Other options may help in specific cases but do not address the immediate memory constraint.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Rewrite the query to use a smaller dimension table.

Answer

Add a clustering key to the fact table on the join column.

Question 6

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?

Accepted Answer

Create a separate warehouse for ETL jobs and use resource monitors to control credit usage.. Workload isolation is best achieved by dedicating separate virtual warehouses to different workload types. ETL jobs and ad-hoc queries have different resource profiles and SLAs; putting them on the same warehouse causes contention. A separate ETL warehouse with its own resource monitor allows the architect to control costs and prevent ETL from affecting ad-hoc performance. Other options either do not isolate workloads or introduce disruptive side effects.

Answer

Configure the warehouse to use multi-cluster mode with a minimum of two clusters.

Answer

Enable the Query Acceleration Service on the warehouse to offload portions of the ETL jobs.

Answer

Set the STATEMENT_TIMEOUT_IN_SECONDS parameter to a low value to terminate long ETL queries.

Question 7

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?

Accepted Answer

Add a clustering key on the column used in the WHERE clause.. Clustering on the column used in the WHERE clause improves pruning by organizing data so that similar values are stored together. This allows Snowflake to skip micro-partitions that do not contain matching values, reducing the number scanned. Increasing warehouse size does not reduce partitions scanned, and search optimization is more suited for selective lookups. Therefore, clustering is the most effective action.

Answer

Use a materialized view that pre-aggregates the data.

Answer

Increase the size of the virtual warehouse.

Answer

Enable the Search Optimization Service on the table.

Question 8

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?

Accepted Answer

A clustering key on the region and date columns.. Clustering on the columns used in the dashboard filters (region and date) enables Snowflake to prune micro-partitions, reducing the data scanned. Snowflake automatically maintains clustering, so ongoing effort is minimal. This directly addresses the performance issue of scanning many micro-partitions and is the recommended approach for large tables with recurring filter patterns.

Answer

A materialized view that pre-aggregates sales by region and date.

Answer

Search Optimization Service on the region column.

Answer

Increasing the warehouse size to scan the table faster.

Question 9

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?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Spilling to local disk during aggregation indicates that the aggregation state exceeds available memory. The most direct remedy is to increase memory per node by scaling up the warehouse. This allows the aggregation to be processed in memory, reducing or eliminating spilling. Other options either do not target memory usage or may worsen it. While scaling up increases cost, it is often the simplest and most effective fix for memory-bound aggregations.

Answer

Add a clustering key on the group-by columns to reduce the number of groups.

Answer

Enable the Query Acceleration Service to offload the aggregation to shared compute.

Answer

Rewrite the query to use a window function instead of a GROUP BY.

Question 10

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?

Accepted Answer

Rewrite the query to include an appropriate join condition between the fact and dimension tables.. A Cartesian product in a join is typically caused by a missing or incorrect join condition. The most effective fix is to rewrite the query to include the proper join predicate, which enables Snowflake to execute an efficient join algorithm. Other measures like adding filters or increasing warehouse size do not address the core problem.

Answer

Enable the Query Acceleration Service to automatically optimize the join.

Answer

Add a WHERE clause to filter the result set after the join.

Answer

Increase the size of the virtual warehouse to handle the larger result set.

Question 11

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

Accepted Answer

Enable multi-cluster warehouse mode and set a minimum and maximum cluster count.. High concurrency with queuing is best addressed by scaling out compute resources. Multi-cluster warehouses automatically add clusters to handle queuing, and setting min/max clusters controls the scale. Separating workloads onto different warehouses distributes load and reduces contention. Scaling up, auto-suspend, and Query Acceleration Service do not directly improve concurrency; they address different problems such as per-query performance, cost control, or specific query offloading.

Answer

Enable the Query Acceleration Service on the warehouse to handle queued queries.

Answer

Increase the warehouse size to add more compute nodes per cluster.

Answer

Set the warehouse to auto-suspend after a short period to free up resources for other warehouses.

Question 12

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?

Accepted Answer

Create a search optimization service on the ACCOUNT_ID column.. Search Optimization Service is the ideal solution for accelerating selective queries on columns that are not part of the clustering key. It creates a persistent search access path that allows Snowflake to quickly locate micro-partitions containing the desired values. This improves ACCOUNT_ID-only queries without altering the existing clustering on TRADE_DATE, thus preserving performance for TRADE_DATE queries.

Answer

Add a secondary clustering key on ACCOUNT_ID.

Answer

Add a materialized view that filters on ACCOUNT_ID and pre-aggregates the data.

Answer

Change the clustering key to (ACCOUNT_ID, TRADE_DATE).

Question 13

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?

Accepted Answer

Scale up the warehouse to a larger size to provide more memory per node.. Spilling to local disk during aggregation indicates that the query's working set exceeds the available memory per node. Scaling up the warehouse increases memory per node, allowing the aggregation to be processed in memory and reducing spilling. Adding clusters or enabling QAS does not address the per-query memory limitation.

Answer

Increase the number of clusters in the multi-cluster warehouse to 8.

Answer

Reduce the size of the warehouse to a smaller size to decrease spilling.

Answer

Enable the Query Acceleration Service to offload the aggregation.

Question 14

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?

Accepted Answer

Extract frequently queried JSON attributes into separate relational columns and cluster on those columns.. Extracting frequently queried JSON attributes into native columns and clustering on them allows Snowflake to prune micro-partitions efficiently, reducing the amount of data scanned. This improves query latency and can reduce storage costs because native columns are more compact than VARIANT. Other options either add overhead or do not address the need for efficient filtering on specific attributes.

Answer

Use a materialized view that pre-aggregates the JSON data by customer_id.

Answer

Enable the Search Optimization Service on the VARIANT column to accelerate point lookups.

Answer

Store the JSON data in an external table and query it directly with Snowflake.

Question 15

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?

Accepted Answer

Add a clustering key on the filtered column.. The query scans many partitions because the table is not clustered, even though the filter is selective. Adding a clustering key on the filtered column reorganizes data so that only relevant micro-partitions are read. This reduces I/O and TableScan time. Other options may provide some benefit but do not directly solve the partition pruning issue.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Create a materialized view on the filtered column.

Answer

Increase the warehouse size to add more compute nodes.