DEA-C02 Performance Optimization — Timed 20 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?

20 minute time limit — choose an answer to begin.

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.

Question 16

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?

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 17

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 18

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 19

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 20

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.