DEA-C02 Performance Optimization Practice Test 3 — 15 Questions

Question 1

A data engineer notices that a recurring ETL query that joins a large fact table with a date dimension is taking longer than expected. The query profile shows that the join is a 'Hash Join' with a large build side. The fact table is clustered on a different column, and the date dimension is small. The engineer wants to reduce the build side size. Which action is most likely to achieve this?

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

Accepted Answer

Ensure that the join key on the fact table is used as a clustering key.. The large build side in a hash join is often caused by data shuffling when the join key is not aligned with the table's clustering. Clustering the fact table on the join key enables co-located joins, reducing the amount of data that must be built and shuffled. Filtering the dimension or using semi-joins may not apply, and increasing warehouse size only adds resources without reducing the build side. Therefore, clustering on the join key is the most effective action to reduce the build side size.

Answer

Add a filter on the date dimension to reduce the number of rows before the join.

Answer

Rewrite the query to use a semi-join or anti-join if applicable.

Answer

Increase the warehouse size to add more memory for the build side.

Question 2

A data engineer is troubleshooting a query that performs a large aggregation with GROUP BY on a high-cardinality column. The query profile shows that the aggregation step is spilling to remote disk. The warehouse is a 2X-Large and has 16 credits/hour. The engineer wants to eliminate the spilling without increasing the warehouse size. Which action is most likely to achieve this?

Accepted Answer

Increase the warehouse size to 3X-Large.. Spilling to remote disk during aggregation typically occurs when the aggregation state exceeds available memory. Increasing the warehouse size adds more memory and compute resources, allowing the aggregation to be processed in memory and eliminating spilling. Clustering, subqueries, or result caching do not directly reduce the memory footprint of a high-cardinality aggregation. Although the engineer wanted to avoid increasing warehouse size, it is the most effective action in this scenario.

Answer

Add a clustering key on the GROUP BY column.

Answer

Enable the 'USE_CACHED_RESULT' parameter and re-run the query.

Answer

Rewrite the query to use a subquery that pre-aggregates before the main GROUP BY.

Question 3

A user complains that a dashboard query is slow during peak hours. The warehouse is configured with auto-suspend and auto-resume. What is the most likely cause of the latency observed during the initial execution?

Accepted Answer

Warehouse provisioning time.. When a warehouse is in a suspended state, the first query submitted triggers a 'cold start' as Snowflake provisions compute resources. This process involves allocating virtual nodes, which takes a few seconds, leading to latency. This is a common occurrence in environments using auto-suspend to save costs. Understanding this behavior is vital for performance tuning, as engineers often confuse this infrastructure provisioning time with actual query execution performance issues.

Answer

The query result cache is full.

Answer

The warehouse size is too small.

Answer

The metadata cache is invalidated.

Question 4

Which of the following scenarios is most appropriate for using a Materialized View to optimize performance?

Accepted Answer

Frequently run queries involving complex joins and aggregations on static data.. Materialized Views in Snowflake are best suited for queries that are frequently executed, computationally expensive, and rely on stable, non-volatile data. By pre-computing the result set and storing it, Snowflake avoids the overhead of repeated aggregation or complex joins. This is essential for dashboarding or reporting where the same transformation is run repeatedly, saving both compute credits and time while ensuring the underlying data remains consistent with the base table.

Answer

Queries that are run only once a month.

Answer

Queries that access data from an external stage.

Answer

Queries that filter on non-deterministic functions.

Question 5

A user runs a query twice in succession with no data changes. The second query completes in near-zero time. What feature is responsible for this performance?

Accepted Answer

Query Result Cache.. The Query Result Cache stores the output of every query for a rolling 24-hour period. When a subsequent query matches the exact SQL text and the underlying data in the source tables has not changed, Snowflake returns the result from the cache. This mechanism avoids the need to spin up compute resources or scan data, providing an instantaneous response for repeated queries, which is a major efficiency feature for frequently accessed reports.

Answer

Warehouse Local Cache.

Answer

Metadata Cache.

Answer

Search Optimization Service.

Question 6

Which of the following is the most cost-effective way to handle massive concurrent read-only queries?

Accepted Answer

Use a multi-cluster warehouse with economy scaling.. Multi-cluster warehouses are designed specifically to handle high concurrency. By setting the scaling policy to 'Economy', Snowflake adds clusters only when the queue grows, prioritizing cost over immediate startup. This allows the system to scale horizontally to meet demand without requiring manual intervention, effectively balancing user experience with credit consumption. It is the standard solution for environments where dashboard traffic spikes and performance must be maintained without over-provisioning compute resources.

Answer

Increase the warehouse size.

Answer

Enable query pruning.

Answer

Create materialized views for every user.

Question 7

A table experiences performance degradation over time due to frequent DML operations (INSERT/UPDATE/DELETE). What is the most likely cause?

Accepted Answer

Micro-partition fragmentation.. Frequent DML operations lead to 'micro-partition fragmentation'. When rows are updated or deleted, the micro-partitions become sub-optimally filled or 'dirty'. This forces the query engine to scan more partitions than necessary because the data is no longer organized linearly. This fragmentation is a common performance bottleneck in write-heavy environments, and it requires periodic table maintenance or clustering to restore the efficiency of the partition pruning process for subsequent reads.

Answer

The metadata cache is exceeding its capacity.

Answer

The warehouse cache is becoming corrupted.

Answer

The result cache is being constantly invalidated.

Question 8

Which of the following is the best practice for using the query profile to identify performance bottlenecks?

Accepted Answer

Start by identifying the operator with the highest time contribution.. The most effective approach is to focus on the 'heavy' operators, which are the parts of the query plan consuming the most time or resources. By analyzing the Query Profile from the top-down, an engineer can isolate where the processing is stalling. This targeted analysis is crucial for performance tuning, as it prevents wasting time on minor optimizations and ensures that efforts are directed toward the root causes of latency, such as spilling or high I/O.

Answer

Only analyze queries that take longer than one hour.

Answer

Ignore spilling metrics if the query eventually finishes.

Answer

Focus primarily on the number of partitions scanned.

Question 9

A query is slow because it is scanning a massive table. The filters are on columns that are not currently clustered. What is the most immediate step to improve performance?

Accepted Answer

Add a clustering key on the filter columns.. When dealing with massive table scans, the most immediate improvement comes from reducing the volume of data read. If the filter columns aren't clustered, adding a clustering key is the most direct way to inform the engine which partitions can be skipped. This is a foundational performance technique in Snowflake, allowing the database to prune partitions based on the filter criteria, thereby reducing I/O and accelerating query execution without needing to change existing query SQL code.

Answer

Use the SEARCH OPTIMIZATION service.

Answer

Rewrite the query to use an INNER JOIN.

Answer

Scale down the warehouse to a smaller size.

Question 10

A data engineer is tuning a query that joins two large tables. The query is performing a 'Remote Disk Spilling' operation. Which optimization strategy is most effective to resolve this?

Accepted Answer

Scale up the warehouse to a larger size.. Remote disk spilling occurs when the memory allocated to the virtual warehouse is insufficient to hold the intermediate result sets of a join or aggregation. By increasing the size of the warehouse, the memory available per node doubles, allowing larger datasets to be processed entirely in-memory. This significantly reduces latency associated with I/O operations and speeds up complex join operations that frequently cause spilling in standard configurations.

Answer

Increase the multi-cluster warehouse scaling policy to 'Maximized'.

Answer

Enable Result Cache by setting USE_CACHED_RESULT to TRUE.

Answer

Implement a materialized view on the joined columns.

Question 11

Refer to the exhibit. Based on the Query Profile, what is the most likely bottleneck for this query?

Accepted Answer

Inefficient data filtering and large data scans.. In this profile, the Table Scan accounts for the vast majority (85%) of the total query time. This indicates that the query is spending most of its duration reading data from storage rather than performing complex joins or aggregations. The lack of remote disk spilling suggests that memory is sufficient. Therefore, improving the scan performance through clustering or partition pruning is the most logical step to reduce execution time.

Answer

Insufficient warehouse memory.

Answer

Network congestion between nodes.

Answer

Excessive joins causing data shuffling.

Question 12

Which of the following describes the purpose of the 'Result Cache' in Snowflake?

Accepted Answer

It provides instant access to query results without compute costs.. The Result Cache is a managed feature that stores the output of a query for a period (typically 24 hours). If an identical query is submitted later, Snowflake returns the cached results immediately without using any compute resources. This is a critical performance and cost optimization tool, as it eliminates the need to re-process large datasets for common, repeated queries, providing near-zero latency and zero credit consumption.

Answer

It stores data blocks to improve subsequent table scans.

Answer

It allows users to manually clear cache for specific tables.

Answer

It persists query results permanently in the user's stage.

Question 13

A user is experiencing 'Data Spilling' in a query that performs a complex window function over a very large dataset. What is the most likely reason this is occurring and how should it be addressed?

Accepted Answer

The query requires a large sort buffer; scale up the warehouse.. Window functions require sorting data based on the partition and order keys specified in the query. If the dataset is too large for the memory assigned to the warehouse nodes, Snowflake will spill the sorting process to local disk or remote storage. Increasing the warehouse size allocates more memory per node, which can accommodate larger sort buffers, thereby reducing or eliminating the need for disk spilling during the window function operation.

Answer

The query is missing a filter, and scaling out will fix it.

Answer

The table is not clustered, which causes excessive scanning.

Answer

The query needs a dedicated 'Snowpark-optimized' warehouse.

Question 14

When analyzing a Query Profile, which indicator most strongly suggests that 'Partition Pruning' is performing effectively?

Accepted Answer

Low partitions scanned compared to total partitions.. Partition pruning is the process where Snowflake skips scanning micro-partitions that cannot possibly contain the data requested by the query. A low 'Partitions scanned' count relative to the total 'Partitions total' is the most direct indicator that the pruning process is working. This is highly efficient as it avoids I/O operations for data that is not relevant to the query's filters, leading to faster execution times.

Answer

High bytes scanned relative to bytes total.

Answer

High network transfer metrics in the profile.

Answer

The presence of a 'Join' operator in the query profile.

Question 15

Refer to the exhibit. The 'sales' table is very large and not clustered. Which action will provide the most significant performance improvement for this query?

Accepted Answer

Define a clustering key on (region, sale_date).. Since the query filters by 'region' and 'sale_date', performance is bottlenecked by the need to scan large amounts of irrelevant data. By clustering the table on 'region' and 'sale_date', Snowflake will organize the micro-partitions so that they are logically grouped by these columns. This allows the query engine to ignore huge swaths of data, drastically reducing the I/O requirement and accelerating the scan portion of the query.

Answer

Enable the Search Optimization Service on the sales table.

Answer

Increase the warehouse size.

Answer

Materialize the query result in a view.