DEA-C02 Performance Optimization — 15 Questions

Question 1

A data engineer notices a significant spike in warehouse credit consumption after implementing a complex join on high-cardinality columns. Which optimization strategy will most effectively reduce execution time without increasing warehouse size?

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

Accepted Answer

Implement clustering keys on the join columns.. Clustering tables on the join keys reduces the volume of data scanned by the query engine through partition pruning. By aligning the physical data storage with the query's access patterns, Snowflake avoids scanning irrelevant micro-partitions. This performance optimization is critical for large-scale joins where data shuffling or excessive partition scanning becomes a bottleneck, as it directly reduces the I/O overhead associated with large table scans, leading to lower execution latency.

Answer

Increase the warehouse size to X-Large.

Answer

Use a result cache to store the join result.

Answer

Change the table type from standard to transient.

Question 2

A data engineer is analyzing slow-running queries. Which TWO of the following metrics in the Query Profile are primary indicators of remote disk I/O bottlenecks? (Select TWO)

Accepted Answer

Percentage of bytes scanned from cache.. Remote disk I/O bottlenecks occur when the warehouse must frequently fetch data from cloud storage rather than using the local disk cache or memory. High percentages of 'Remote Disk I/O' and 'Bytes Scanned' signify that the warehouse is not utilizing its local cache effectively or is processing datasets that exceed the cache capacity. Identifying these metrics helps engineers determine if clustering or warehouse sizing is necessary for optimization.

Answer

Total number of partitions.

Answer

Query result cache hits.

Answer

Number of active users.

Question 3

A data engineer identifies that a large table has poor pruning performance. Which THREE actions can improve the efficiency of queries filtering on this table? (Select THREE)

Accepted Answer

Define a clustering key on the commonly filtered columns.. Improving pruning involves organizing data so that the query optimizer can easily skip partitions. By clustering, partitioning by high-cardinality columns, or adjusting the physical sort order, you reduce the number of micro-partitions that need to be scanned. These strategies ensure that only the relevant data blocks are loaded into memory, which is the cornerstone of effective performance optimization in the Snowflake architecture, minimizing both I/O and total compute time.

Answer

Increase the number of warehouse clusters.

Answer

Use a materialized view to pre-aggregate the data.

Question 4

Which TWO factors should be considered when deciding between a Materialized View and a standard table with a clustering key for optimization? (Select TWO)

Accepted Answer

Frequency of underlying base table data modifications.. Materialized views are automated and best for small, frequently accessed, pre-aggregated results. Clustering keys are better for large tables where you need to filter across various ranges dynamically. Choosing the right one depends on the nature of the query patterns and the frequency of data updates. Understanding these trade-offs ensures that the engineer selects the most performant and cost-effective approach for the specific analytical workload they are trying to optimize.

Answer

The total number of rows in the table.

Answer

The number of users accessing the system.

Answer

The cloud provider being used (AWS, Azure, or GCP).

Question 5

Which TWO of the following practices are recommended to minimize query queueing and optimize warehouse utilization?

Accepted Answer

Configure the warehouse as a multi-cluster warehouse.. Efficient warehouse management balances concurrency and resource availability. By using multi-cluster warehouses, Snowflake can automatically spin up additional clusters to handle concurrent demand, preventing queueing. Additionally, setting a lower 'Auto-Suspend' time ensures that idle resources are released quickly, preventing unnecessary costs while maintaining readiness for incoming workloads. These two strategies combined ensure that compute resources scale dynamically with user demand while remaining cost-effective during periods of low activity.

Answer

Set the warehouse auto-suspend to 10 minutes.

Answer

Set the statement_timeout_in_seconds to 0.

Answer

Use the 'Economy' scaling policy for all production warehouses.

Question 6

A developer wants to optimize a query that frequently joins two very large tables, 'Fact_Sales' and 'Dim_Products'. The query profile indicates high network transfer costs. What is the best strategy to reduce this?

Accepted Answer

Clustering both tables on the common join key.. In large-scale joins, network cost is often caused by shuffling data across nodes to align join keys. If one table is significantly smaller than the other, broadcast joins can eliminate this movement. However, if both are large, clustering both tables on the join key allows for a 'collocated join,' where matching data already resides on the same nodes, drastically reducing the need for network data transfer during the join execution.

Answer

Increase the number of clusters in the warehouse.

Answer

Change the warehouse type to 'Snowpark-optimized'.

Answer

Enable search optimization on the join keys.

Question 7

Which feature should be used to prevent a runaway query from consuming excessive warehouse credits?

Accepted Answer

Resource Monitor.. Resource monitors are the primary tool in Snowflake for managing credit consumption. By setting a hard limit on a warehouse, you can ensure that it automatically suspends or notifies administrators once a specific credit threshold is reached. This provides a safety net against runaway queries that might otherwise run for hours, inadvertently consuming large amounts of warehouse compute credits and driving up organizational costs unexpectedly.

Answer

Query Timeout settings.

Answer

Search Optimization Service.

Answer

Clustering Keys.

Question 8

Which THREE factors can negatively impact the 'Clustering Depth' of a table in Snowflake?

Accepted Answer

Frequent DML operations that insert unordered data.. Clustering depth measures how well data is organized for partition pruning. Factors that degrade this include frequent DML operations (inserts, updates, deletes) which cause data to be appended in an unorganized manner, the absence of clustering keys which prevents automatic organization, and large amounts of data being loaded without a logical ordering, which forces Snowflake to scan more partitions than necessary to satisfy a query.

Answer

Use of clustering keys.

Answer

Using smaller warehouse sizes for queries.

Question 9

A data engineer notices that a warehouse is consistently showing 100% load, but queries are not queueing. What is the most likely cause?

Accepted Answer

The warehouse is operating at peak efficiency.. When a warehouse shows 100% load but no queueing, it means the warehouse is fully utilizing its allocated compute capacity without exceeding it. This is generally the desired state for a warehouse, indicating that the resources are being used effectively to handle the workload. It does not necessarily imply a performance problem; it simply confirms that the currently allocated compute power is matching the demand of the incoming query stream.

Answer

The warehouse is too small for the workload.

Answer

The queries are blocked by network latency.

Answer

The auto-suspend time is too high.

Question 10

A data engineer needs to optimize a set of recurring complex queries that involve expensive aggregations and joins on a large dataset that is updated every few minutes. Which TWO factors should be considered when deciding whether to use Materialized Views instead of regular views or caching? (Select TWO)

Accepted Answer

The frequency of data changes in the underlying base tables.. Materialized Views are most effective when the data is frequently accessed but changes relatively slowly, as every update to the base table triggers background maintenance costs. For tables updated every few minutes, the compute costs for keeping the Materialized View synchronized must be weighed against the performance gains during query execution to ensure cost-efficiency.

Answer

The number of users concurrently running the same query.

Answer

Whether the query uses non-deterministic functions like CURRENT_TIMESTAMP().

Answer

The total number of columns in the base table schema.

Question 11

A data engineer is tasked with improving the performance of a Query Acceleration Service (QAS) eligible workload. Which THREE types of queries are most likely to benefit from the Query Acceleration Service? (Select THREE)

Accepted Answer

Queries with large scans and selective filters.. The Query Acceleration Service (QAS) is designed to offload heavy scanning and filtering tasks to a shared pool of compute resources. It is most effective for queries that scan large amounts of data, use selective filters, or involve high-cardinality aggregations. This offloading reduces the burden on the main warehouse, allowing it to focus on complex joins.

Answer

Queries that include a LIMIT clause without an ORDER BY.

Answer

Queries that involve small tables with complex window functions.

Question 12

A data engineer observes that an external table is performing poorly. Which TWO actions can be taken to improve the query performance on external tables? (Select TWO)

Accepted Answer

Partition the external table based on the folder structure in cloud storage.. External tables reside outside Snowflake, so performance is limited by the latency of the cloud storage and the metadata overhead. Manually partitioning the external table and using the auto-refresh feature ensures that Snowflake's metadata stays synchronized with the actual files in storage, allowing for much faster partition pruning during query execution.

Answer

Increase the warehouse size to a 3X-Large.

Answer

Convert the external table to a standard Snowflake table.

Answer

Disable the METADATA_COLUMNS parameter in the table definition.

Question 13

A Snowflake data engineer is investigating a query that runs significantly slower than expected. The query includes a join between a very large fact table and a small dimension table. The Query Profile shows a CartesianJoin operator, and the join condition uses an equality predicate between the two tables. What is the most likely cause of the CartesianJoin?

Accepted Answer

The join condition is missing or incorrectly specified, causing the optimizer to perform a cross join.. A CartesianJoin in the Query Profile indicates that the join is being executed without a proper join condition, resulting in a cross product. This typically happens when the equality predicate is missing, misspelled, or not recognized by the optimizer. Correcting the join condition to a valid equi-join allows the optimizer to use a more efficient hash join, drastically improving performance.

Answer

The warehouse is too small, causing the optimizer to choose a CartesianJoin as a fallback.

Answer

The small dimension table lacks a clustering key, forcing a full scan and Cartesian product.

Answer

The fact table is not clustered on the join key, leading to a CartesianJoin.

Question 14

A data engineer is optimizing a query that performs a large aggregation over a clustered table. The Query Profile shows that the aggregation step is spilling to local disk. The warehouse size is currently Medium. The engineer wants to reduce spilling without increasing warehouse size. Which action is most likely to improve performance?

Accepted Answer

Rewrite the query to use a temporary table to pre-aggregate the data before the final aggregation.. Pre-aggregating data into a temporary table reduces the number of rows that the final aggregation must process, which lowers memory pressure and prevents spilling. This technique allows the query to be executed in stages, each fitting within the available memory of the current warehouse. It is an effective way to optimize without increasing warehouse size.

Answer

Add a clustering key on the group-by columns to improve data pruning and reduce the number of rows processed.

Answer

Enable the USE_CACHED_RESULT parameter to reuse previous aggregation results.

Answer

Increase the warehouse size to Large to provide more memory for the aggregation.

Question 15

A data engineer is designing a table that will be frequently queried with filters on both a high-cardinality column (e.g., ORDER_ID) and a low-cardinality column (e.g., STATUS). The table is very large. Which TWO actions will most effectively improve query performance? (Choose two.)

Accepted Answer

Define a clustering key on the high-cardinality column (ORDER_ID).. Clustering on a high-cardinality column like ORDER_ID enables effective pruning for filters on that column. A composite clustering key on (STATUS, ORDER_ID) further improves pruning when queries filter on both columns, as it orders data within each status by ORDER_ID. Together, these actions provide the best performance for the given query patterns.

Answer

Define a clustering key on the low-cardinality column (STATUS).

Answer

Create a materialized view that pre-filters data by STATUS.

Answer

Enable the SEARCH_OPTIMIZATION parameter on the table.