DEA-C02 Performance Optimization Practice Test 2 — 15 Questions

Question 1

A data engineer is analyzing a Query Profile for a query that joins two large tables. The profile shows a significant amount of time spent in the 'Remote Disk Spilling' step. The warehouse is a 2XL. The engineer wants to reduce remote spilling without increasing warehouse size. Which optimization is most likely to be effective?

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

Accepted Answer

Repartition the data by using a different join key that distributes data more evenly.. Remote spilling in a join typically indicates data skew, where certain join keys are overrepresented, causing some nodes to handle disproportionate data and spill to remote disk. Repartitioning the data by using a more evenly distributed join key or applying salting techniques can balance the workload across nodes, reducing memory pressure and eliminating remote spilling. This optimization targets the underlying cause without requiring a larger warehouse.

Answer

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

Answer

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

Answer

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

Question 2

A data engineer is optimizing a query that selects a few columns from a very wide table (100+ columns). The query applies a filter on a non-clustered column and returns a small number of rows. The engineer notices that the query reads a large amount of data despite the filter. Which action will most improve performance?

Accepted Answer

Create a materialized view that includes only the selected columns and the filter column.. A materialized view that includes only the selected columns and the filter column reduces the amount of data scanned because it stores a narrower dataset. The base table is very wide, so reading all columns is inefficient. The materialized view pre-computes and stores only the necessary columns, allowing the query to scan less data. It can also be clustered on the filter column for further pruning, making it the most effective optimization.

Answer

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

Answer

Use the SELECT * EXCLUDE syntax to omit unneeded columns.

Answer

Enable the USE_CACHED_RESULT parameter to reuse previous query results.

Question 3

A data engineer notices that a recurring batch query joining a 2 TB fact table with a 200 GB dimension table consistently spills to local disk. The Query Profile shows a HashJoin operator with a large number of bytes spilled. The warehouse is an X-Large. The join key on the dimension table is not the clustering key. Which change is most likely to eliminate the local spilling while keeping credit usage reasonable?

Accepted Answer

Add a clustering key on the dimension table's join column.. The HashJoin spills because the build side, likely the 200 GB dimension table, does not fit in the warehouse memory. Clustering the dimension table on the join column reduces the build-side data volume by enabling micro-partition pruning when the query filters on that column, and can improve join performance. This targets the memory pressure without scaling up the warehouse, keeping credit usage reasonable.

Answer

Disable the query acceleration service for the warehouse.

Answer

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

Answer

Change the warehouse to a Snowpark-optimized warehouse.

Question 4

A data engineer is optimizing a Snowflake environment where multiple users run concurrent queries on the same large table. The table is frequently filtered on a high-cardinality column, and queries often scan large portions of the table. The engineer wants to reduce the amount of data scanned and improve overall concurrency. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Add a clustering key on the high-cardinality column.. To reduce data scanned, adding a clustering key on the high-cardinality column allows micro-partition pruning, which is highly effective for filters on that column. To improve concurrency, using a multi-cluster warehouse enables multiple clusters to handle simultaneous queries, reducing queueing. Materialized views and search optimization have specific use cases but do not address broad filtering on high-cardinality columns or concurrency as directly. Increasing warehouse size alone does not improve concurrency as effectively as multi-cluster.

Answer

Increase the warehouse size to improve concurrency.

Answer

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

Answer

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

Question 5

A data engineering team runs nightly ELT where transformations execute on a dedicated warehouse while dashboards query a separate reporting warehouse. Analysts complain that morning dashboards are slow, and the team wants to reduce query latency without increasing the size of the reporting warehouse. Which TWO actions should the team take? (Choose two.)

Accepted Answer

Add a clustering key to the large fact tables on the columns most used in dashboard filters.. Dashboard latency on a fixed-size warehouse is reduced by lowering the amount of data and computation each query performs. Persisting precomputed aggregates removes repeated heavy computation, while clustering the large fact tables enables partition pruning during scans. Together they cut work at query time without resizing compute.

Answer

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

Answer

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

Answer

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

Question 6

A data engineer runs a query against a large table that is clustered by ORDER_DATE. The query filters with WHERE ORDER_DATE = CURRENT_DATE() and also joins to a small dimension table. The Query Profile shows that the join build side is a small table, yet the query still spills to local disk during the join. Which action is most likely to reduce the spilling without changing the query semantics?

Accepted Answer

Add a larger warehouse size so the join operator receives more memory per node.. Local disk spilling during a join means an operator exceeded its memory allocation. Increasing the warehouse size provides more memory per node, which raises the threshold at which operators spill and lets the join complete in memory. Because the build side is small, the pressure likely comes from probe-side processing or concurrent operators rather than from the build side itself.

Answer

Change the clustering key from ORDER_DATE to the join key column to improve pruning.

Answer

Enable the USE_CACHED_RESULT parameter for the session so the join build side is cached in memory.

Answer

Set the JOIN_LIMIT parameter to reduce the number of rows materialized during the join.

Question 7

A data engineer manages a Snowflake warehouse that runs a mix of short interactive dashboard queries and long-running ETL transformations. Users report that dashboard queries are sometimes queued behind ETL jobs, increasing latency. The ETL jobs are not time-critical. Which approach best reduces dashboard query latency while maintaining overall throughput?

Accepted Answer

Create a dedicated warehouse for dashboard queries and another for ETL, and set the ETL warehouse to auto-suspend after a short idle period.. Workload isolation using separate warehouses is the most effective way to prevent interactive queries from being queued behind long-running ETL jobs. A dedicated dashboard warehouse ensures that its queries have dedicated compute resources, while the ETL warehouse can be sized and auto-suspended independently. This approach maintains throughput for both workloads and reduces latency for dashboards without affecting ETL performance.

Answer

Create separate warehouses for ETL and dashboards, and configure the dashboard warehouse with a higher priority using the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter.

Answer

Enable the MULTI_CLUSTER_WAREHOUSE feature on the existing warehouse and set MIN_CLUSTER_COUNT=1 and MAX_CLUSTER_COUNT=3.

Answer

Increase the size of the existing warehouse to a larger t-shirt size to provide more compute resources for all queries.

Question 8

A data engineer is tuning a query that joins a 10-billion-row fact table to a small 5,000-row dimension table. The Query Profile shows a broadcast join with high network transfer and long execution time. The dimension table has a reliable primary key and is updated only once per day. Which approach best reduces the join cost?

Accepted Answer

Add a clustering key on the fact table's join column and filter columns to improve partition pruning before the join.. When a broadcast join is dominated by the probe side, the effective remedy is to shrink the probe side before the join. Clustering the fact table on join and filter columns enables pruning so fewer micro-partitions are read and fewer rows are broadcast-joined, cutting network transfer and runtime.

Answer

Rewrite the query so the small dimension is the build side and rely on the optimizer to choose a more efficient join distribution.

Answer

Convert the dimension table into a materialized view and join against the view instead.

Answer

Increase the warehouse size to add more nodes so the broadcast join finishes faster.

Question 9

A data engineer runs a recurring ELT job that loads a 2 TB table daily using a COPY INTO statement from an external stage. The target table is clustered by LOAD_DATE, and each day's load covers exactly one partition. Query performance after each load is initially good, but over several months the table accumulates many small micro-partitions. Which action should the engineer take to improve long-term pruning and scan efficiency for this table?

Accepted Answer

Periodically execute a full-table rewrite using CREATE OR REPLACE TABLE ... AS SELECT with an ORDER BY on the clustering key, or rely on automatic re-clustering plus a periodic large batch load.. Daily single-partition loads create many small micro-partitions, which increases metadata overhead and reduces scan efficiency even when the clustering key is correct. A periodic full rewrite with ORDER BY on the clustering key, combined with the engine's automatic re-clustering, restores a compact micro-partition layout. Batching loads into fewer, larger transactions also reduces fragmentation, improving pruning for queries that filter on LOAD_DATE.

Answer

Schedule a periodic ALTER TABLE ... CLUSTER BY statement to re-cluster the table.

Answer

Convert the table to a transient table to disable micro-partition maintenance overhead.

Answer

Run a periodic ALTER TABLE ... RECLUSTER command to consolidate small micro-partitions.

Question 10

A data engineer notices that a query joining two large tables is performing a remote disk spill. The Query Profile shows that the build side of the join is much larger than the probe side. The engineer wants to reduce remote spilling without increasing warehouse size. Which action is most appropriate?

Accepted Answer

Ensure the smaller table is used as the build side by rewriting the join order or using a subquery to filter the larger table first.. Remote spilling happens when the build side of a join is too large to fit in a node's memory and must be repartitioned over the network. Making the smaller table the build side keeps the hash table in memory. Filtering the larger table before the join further reduces the probe-side data, lowering both memory and network pressure, which eliminates remote spilling without scaling the warehouse.

Answer

Add a clustering key on the join column of the larger table to improve join pruning and reduce data movement.

Answer

Rewrite the query to use a UNION ALL instead of a join to avoid the build side entirely.

Answer

Disable the join spill-to-disk feature by setting the ENABLE_JOIN_SPILL parameter to FALSE.

Question 11

A data engineer is optimizing a query that joins a large fact table 'sales' with a small dimension table 'products'. The query filters on 'products.category' and aggregates sales amounts. The engineer notices that the join is a broadcast join in the query profile. Which action would most likely improve performance by enabling a more efficient join strategy?

Accepted Answer

Ensure the 'products' table is small enough to be broadcast, and if not, consider filtering it before the join to reduce its size.. Filtering the smaller dimension table before the join reduces the amount of data that needs to be broadcast or shuffled, which can make the broadcast join more efficient or enable a more optimal join strategy. This is a common optimization when a dimension table is larger than expected or when the filter is highly selective. Reducing data early in the query plan minimizes compute and I/O.

Answer

Add a clustering key on the 'sales' table on the join key 'product_id'.

Answer

Add a search optimization service to the 'sales' table to speed up point lookups on 'product_id'.

Answer

Rewrite the query to use a LEFT JOIN instead of an INNER JOIN to reduce the amount of data processed.

Question 12

A data engineer is investigating a slow query that joins a 5 billion row fact table to a 200 million row dimension table. The Query Profile shows a single join node with a very high percentage of time in 'Bytes spilled to remote storage' and a high cardinality on the join key. The fact table is clustered by a different column than the join key. Which optimization is most likely to reduce remote spilling for this join?

Accepted Answer

Add a clustering key on the fact table's join key column to align micro-partitions with the join.. Remote spilling in a large join often results from data being repartitioned across nodes because the join key does not align with the table's clustering. By clustering the fact table on the join key, micro-partition ranges align with the join predicate, allowing the optimizer to prune partitions and co-locate matching rows more effectively. This reduces the volume of data shuffled and spilled during the join, improving performance without merely adding compute.

Answer

Disable the join by rewriting the query to use a correlated subquery instead.

Answer

Increase the warehouse size to add more compute nodes and memory.

Answer

Set the JOIN_MEMORY_LIMIT parameter to allow more memory for the join operator.

Question 13

A data engineer is designing a clustering strategy for a very large fact table that is frequently filtered by REGION and queried with aggregations. The table is also joined to a date dimension on DATE_KEY. Which two actions will most likely improve pruning and overall query performance? (Choose two.)

Accepted Answer

Cluster the fact table by REGION because it is a common filter column.. Clustering by REGION improves pruning for the most common filter. A composite key including REGION and DATE_KEY can further enhance pruning for queries that filter on both columns. Both actions directly affect micro-partition layout, allowing the optimizer to skip irrelevant data. The other options either do not improve pruning or introduce unnecessary complexity without addressing the core filtering pattern.

Answer

Disable automatic clustering and rely solely on the natural insertion order.

Answer

Create a materialized view that pre-aggregates by REGION and DATE_KEY.

Answer

Cluster the fact table by DATE_KEY to align with the date dimension join.

Question 14

A data engineer is optimizing a Snowflake environment where several long-running queries frequently spill to remote storage. The engineer has already confirmed that the queries cannot be rewritten. Which TWO actions should the engineer take to reduce remote spilling and improve performance? (Choose two.)

Accepted Answer

Increase the size of the warehouse used for these queries to provide more memory per node.. Remote spilling occurs when operators exceed memory and local disk capacity. Increasing warehouse size provides more memory per node and more nodes, allowing joins and aggregations to keep their working sets in memory. Clustering on frequently joined or filtered columns reduces the volume of data that must be repartitioned and processed, lowering memory pressure. Together, these actions address both the resource and data-distribution causes of remote spilling.

Answer

Reduce the warehouse size to decrease the number of nodes competing for memory.

Answer

Disable the query result cache to free memory for spilling operations.

Answer

Set the ENABLE_REMOTE_SPILLING parameter to FALSE to force local spilling only.

Question 15

A data engineer runs a query on a Snowflake virtual warehouse that joins a 2 TB fact table to a 500 GB dimension table. The warehouse size is Large (8 credits/hour) and the query runs for 45 minutes. The query profile shows that the join build side is 500 GB and the probe side is 2 TB. The engineer notices that the query spends 60% of its time in the 'Join' operator and 30% in 'TableScan'. Which action is most likely to reduce the overall query execution time?

Accepted Answer

Add a clustering key on the join column of the 2 TB fact table.. The join operator dominates the query time, and the large fact table is the primary driver of shuffle and build-side costs. Clustering the fact table on the join column enables Snowflake to co-locate matching rows, reducing data movement and improving join efficiency. Increasing warehouse size or using materialized views does not address the fundamental data distribution issue, and result caching is irrelevant for a first-run query. Therefore, clustering the fact table is the most effective optimization.

Answer

Use a larger warehouse and enable the 'USE_CACHED_RESULT' parameter.

Answer

Increase the warehouse size to 2X-Large.

Answer

Convert the dimension table to a materialized view.