ARA-C01 Performance Optimization Practice Test 6 — 15 Questions

Question 1

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?

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

Accepted Answer

Add a clustering key on the fact table's high-cardinality filter column.. The query's main cost is scanning the large fact table. Clustering on the high-cardinality filter column enables partition pruning, reducing I/O. The small dimension table does not warrant special handling. Search Optimization is for point lookups, materializing adds overhead, and resizing does not address the root cause of excessive data scanning.

Answer

Enable the Search Optimization Service on the fact table.

Answer

Materialize the join as a new table and refresh it hourly.

Answer

Increase the warehouse size to add more compute nodes.

Question 2

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?

Accepted Answer

Enable multi-cluster warehouse with a minimum and maximum cluster count greater than 1.. High concurrency causing queuing is best resolved by scaling out with a multi-cluster warehouse. By configuring minimum and maximum clusters greater than 1, Snowflake can automatically add clusters when queries are queued, allowing more concurrent queries to run. Scaling up or enabling other features does not increase concurrency capacity.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Increase the warehouse size to a larger T-shirt size.

Answer

Set the warehouse to auto-suspend after 60 seconds of inactivity.

Question 3

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?

Accepted Answer

Add a clustering key on the fact table's join column.. Clustering the large fact table on the join column aligns micro-partitions with the join key, allowing Snowflake to prune partitions and reduce the volume of data read and redistributed during the join. This directly addresses the high time in the Join node. Other options either target different workloads (Search Optimization), add cost without fixing I/O (larger warehouse), or rely on unsupported hints (forcing join type).

Answer

Force the optimizer to use a hash join instead of a broadcast join.

Answer

Increase the size of the virtual warehouse used to run the query.

Answer

Enable the Search Optimization Service on the fact table.

Question 4

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?

Accepted Answer

Rewrite the query to use an equality join by adding a derived join key.. The query profile indicates a costly range join with spilling. Converting to an equality join via a derived key allows Snowflake to use a hash join, which is more scalable and reduces memory pressure. Other options either do not change the join algorithm or are less targeted for this specific bottleneck.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

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

Answer

Increase the warehouse size to add more memory per node.

Question 5

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?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Spilling to local disk in a hash join indicates that the build side does not fit in the available memory. Increasing the warehouse size provides more memory per node, allowing the build side to be held in memory. This eliminates spilling and improves join performance. Other options do not address the memory constraint.

Answer

Rewrite the query to use a nested loop join instead of a hash join.

Answer

Enable the Query Acceleration Service on the warehouse.

Answer

Add a clustering key on the join column of the large fact table.

Question 6

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?

Accepted Answer

Query Profile in the Snowflake web interface.. Query Profile is the dedicated tool for examining query execution plans and operator-level metrics. It helps identify bottlenecks like expensive joins or aggregations. Other options provide either high-level metadata or capacity information, but not the granular execution details needed for tuning individual queries.

Answer

Warehouse load monitoring in the Snowflake web interface.

Answer

Snowflake's automatic clustering recommendations.

Answer

ACCOUNT_USAGE.QUERY_HISTORY view.

Question 7

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?

Accepted Answer

Define a clustering key on the timestamp column.. Clustering on the timestamp column sorts data into micro-partitions by that column, enabling partition pruning for range filters. This reduces I/O for many queries. Materialized views are query-specific, Search Optimization is for point lookups, and resizing does not reduce data scanned.

Answer

Enable the Search Optimization Service on the timestamp column.

Answer

Increase the size of the virtual warehouse used for these queries.

Answer

Create a materialized view that filters on the timestamp column.

Question 8

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?

Accepted Answer

Add a clustering key on the date column of the fact table.. Clustering the fact table on the date column enables micro-partition pruning for the date range filter, reducing the data scanned by the join. This directly addresses the performance bottleneck without the overhead of larger warehouses or materialized views. It is the most cost-effective optimization for this scenario.

Answer

Increase the warehouse size to provide more memory for the hash join.

Answer

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

Answer

Enable the Search Optimization Service on the fact table's date column.

Question 9

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?

Accepted Answer

Search Optimization Service. Search Optimization Service is purpose-built for queries that apply highly selective filters on columns that are not the clustering key. It creates a search access path that enables efficient micro-partition pruning, reducing the number of partitions scanned. While clustering can also improve pruning, it is more suitable for range filters and columns used in multiple queries. QAS and materialized views target different workloads.

Answer

Query Acceleration Service

Answer

Clustering key on the filtered column

Answer

Materialized view on the filtered column

Question 10

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?

Accepted Answer

Cluster the table on customer_id and transaction_date.. Clustering on the columns used in the window function's PARTITION BY and ORDER BY clauses co-locates related data, reducing shuffling and sorting during query execution. This directly improves the performance of window functions on large tables. Other options either do not address data organization or are not applicable.

Answer

Increase the warehouse size to provide more compute for the window function.

Answer

Use the Search Optimization Service on the customer_id column.

Answer

Create a materialized view that pre-computes the window function.

Question 11

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

Accepted Answer

Use a materialized view that pre-aggregates the data.. Clustering on the GROUP BY columns improves data locality, reducing shuffle during aggregation, while a materialized view that pre-aggregates can avoid computing the aggregation altogether for matching queries. Both directly target the aggregation bottleneck. Scaling up or enabling Search Optimization Service do not address the core issue of data organization for aggregation.

Answer

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

Answer

Increase the size of the virtual warehouse to add more compute resources.

Answer

Enable the Search Optimization Service on the fact table.

Question 12

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?

Accepted Answer

Verify that the join key columns in both tables have the same data type and are not implicitly cast.. After correcting the missing join condition, the most effective next step is to ensure that the join keys have matching data types and are not subject to implicit casting. Implicit casts can prevent hash joins and cause full scans, severely impacting performance. Addressing this ensures the optimizer can choose an efficient join method before considering other optimizations like clustering.

Answer

Add a clustering key on the join key in both tables.

Answer

Increase the warehouse size to handle the larger intermediate result set.

Answer

Rewrite the query to use a CROSS JOIN with a WHERE clause.

Question 13

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?

Accepted Answer

Ensure that the date filter is applied as a predicate pushdown and that the clustering key is active.. The fact table is already clustered by date, and the query filters on a date range, so pruning should be effective if the predicate is pushed down and the clustering key is active. The architect should verify that the date filter is applied at the scan level and that the clustering key is not stale. This directly addresses the excessive data scanning shown in the Query Profile, whereas other options do not target the pruning problem.

Answer

Convert the broadcast join to a hash join by increasing the warehouse size.

Answer

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

Answer

Add a clustering key on the join key of the fact table.

Question 14

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

Accepted Answer

Enable multi-cluster warehouse with a minimum of 2 clusters.. To reduce queuing for ad-hoc queries during ETL execution, the architect should either enable multi-cluster warehouse to add capacity dynamically or separate the workloads onto different warehouses. Multi-cluster warehouse with a minimum of 2 clusters ensures additional compute is available for queued queries. Separating ETL and ad-hoc workloads eliminates contention entirely. Both actions directly address the root cause of queuing without unnecessarily increasing costs, as they can be scaled independently.

Answer

Increase the size of the existing warehouse to X-Large.

Answer

Set the warehouse to auto-suspend after 60 seconds of inactivity.

Answer

Set the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to a low value.

Question 15

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?

Accepted Answer

Increase the warehouse size to Large.. Spilling to local disk occurs when an operation requires more memory than the warehouse can provide. Increasing the warehouse size adds more memory per node, allowing the aggregation to complete in-memory and eliminating spilling. While other options might offer some benefits, they do not directly provide additional memory to the operation. Scaling up the warehouse is the most direct and effective solution for memory-intensive operations that are spilling.

Answer

Enable the Query Acceleration Service.

Answer

Add a clustering key on the group-by columns.

Answer

Create a materialized view for the aggregation.