DEA-C02 Performance Optimization Practice Test 5 — 15 Questions

Question 1

A data engineer runs a nightly transformation that joins a 12 TB fact table to a 300 GB dimension table. The fact table is clustered by DATE_KEY, and the dimension is small enough to fit in memory. Query Profile shows the join operator building a hash table on the 12 TB side and spilling to remote disk. The engineer wants to eliminate the remote spill without increasing warehouse size. Which action should the engineer take?

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

Accepted Answer

Rewrite the join so the 300 GB dimension table is the build side and the 12 TB fact table is the probe side.. Hash join performance depends on which input is used to build the in-memory hash table. Building on the smaller dimension and probing with the large fact table keeps the hash table resident in memory, eliminating remote disk spilling. Clustering, resizing, or materializing the join do not correct the build-side selection, so they leave the root cause of the spill unaddressed.

Answer

Increase the warehouse size by one step so the join operator receives more memory per node.

Answer

Add a clustering key on the 12 TB fact table using the dimension's primary key column.

Answer

Convert the 12 TB fact table to a materialized view joined with the dimension table and refresh it nightly.

Question 2

A data engineer is optimizing a query that aggregates a large sales table by product category and region. The query currently uses a GROUP BY on two high-cardinality columns and produces a large intermediate result set. The query profile shows significant network traffic and remote spilling. The engineer wants to reduce remote spilling without changing the aggregation logic. Which approach is most likely to achieve this?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Remote spilling occurs when an operation's intermediate results exceed available memory and are written to remote storage. Increasing the warehouse size provides more memory per node, which can accommodate larger intermediate results and reduce or eliminate remote spilling. While clustering and query acceleration can improve performance in other ways, they do not directly address the memory pressure of the aggregation. Rewriting the query might help but is not as direct or reliable as scaling up.

Answer

Add a clustering key on the product category column.

Answer

Rewrite the query to use a two-step aggregation with a temporary table.

Answer

Enable the query acceleration service on the warehouse.

Question 3

A data engineer is reviewing a Query Profile for a query that filters a large table on a column with a very high cardinality, such as a UUID. The query applies an equality predicate on that column and returns a single row. The table is not clustered on that column. Which Snowflake feature is designed to accelerate this type of highly selective point lookup?

Accepted Answer

Search optimization service. Search optimization service is designed to accelerate highly selective point lookups and equality predicates, such as a UUID equality filter returning a single row. It maintains a search access path that lets the optimizer locate matching micro-partitions without scanning the entire table. Clustering, query acceleration, and materialized views serve different purposes and do not target point lookups as directly.

Answer

Automatic clustering

Answer

Materialized view with a cluster by clause

Answer

Query acceleration service

Question 4

A data engineer notices that a daily aggregation query scans 4 TB of a 5 TB table even though it only needs the last seven days of data. The table has a DATE column but no clustering key, and the query filters with DATE >= CURRENT_DATE - 7. Query Profile shows almost no partition pruning. What is the most effective change to reduce bytes scanned?

Accepted Answer

Add a clustering key on the DATE column and let Automatic Clustering maintain it.. Partition pruning depends on how well the filter column correlates with micro-partition boundaries. A DATE clustering key groups similar dates together, allowing the optimizer to skip most micro-partitions for a seven-day filter. Automatic Clustering preserves that layout over time. Predicate rewrites, materialized views, and search optimization do not change the physical clustering that governs pruning in this scenario.

Answer

Enable search optimization service on the DATE column to improve range filter performance.

Answer

Create a materialized view that selects only the last seven days and refresh it on a schedule.

Answer

Replace the filter with a BETWEEN clause using explicit literal dates instead of CURRENT_DATE arithmetic.

Question 5

A data engineer notices that a recurring daily query that joins a large fact table with a date dimension is taking longer than expected. The query filters on a date range and joins on a date key. The fact table is clustered by date_key, and the date dimension is small. The query profile shows a Cartesian join warning. Which action should the engineer take to resolve the Cartesian join and improve performance?

Accepted Answer

Ensure the join condition uses the correct columns and is not accidentally omitted.. A Cartesian join warning in the query profile signals that the join is producing a Cartesian product, usually due to a missing or incorrect join condition. The most direct fix is to ensure the join predicate correctly matches the fact table's date_key to the date dimension's key. This eliminates the Cartesian product, reducing the result set and improving performance. Other options like filtering or scaling up do not address the root cause and may lead to incorrect results or inefficiency.

Answer

Add a WHERE clause to filter the date dimension to the same range as the fact table.

Answer

Add a clustering key on the date dimension's date key.

Answer

Increase the warehouse size to handle the larger result set.

Question 6

A data engineer is analyzing a Query Profile for a query that joins three large tables. The profile shows that the optimizer chose a broadcast join for one of the joins, but the broadcasted table is 50 GB. The query is spilling to remote disk. Which action is most likely to improve performance by changing the join strategy?

Accepted Answer

Collect statistics on the join columns and ensure the tables are not stale.. The optimizer chose a broadcast join for a 50 GB table, which is inefficient and causes spilling. This often happens when statistics are stale or missing, leading to underestimation of the table size. Collecting statistics on the join columns provides accurate cardinality estimates, enabling the optimizer to choose a better join strategy, such as a hash join with a smaller build side. This directly addresses the root cause.

Answer

Add a clustering key on the join columns of all three tables.

Answer

Increase the warehouse size to 4X-Large.

Answer

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

Question 7

A data engineer maintains a very large fact table that is loaded nightly with the previous day's orders. Analysts run reports that almost always filter on ORDER_DATE and also aggregate by CUSTOMER_ID. The engineer wants search optimization to accelerate point lookups on ORDER_ID and CUSTOMER_ID without adding clustering maintenance overhead. Which configuration best meets these requirements?

Accepted Answer

Enable the search optimization service on the CUSTOMER_ID and ORDER_ID columns.. Search optimization is designed for selective point lookups and equality predicates on specific columns, and it is maintained automatically as new data lands, which matches the nightly-load pattern and the desire to avoid clustering maintenance. Clustering and materialized views address different access patterns, and unique constraints do not create physical access paths.

Answer

Define a clustering key on (ORDER_DATE, CUSTOMER_ID) and let automatic clustering maintain it.

Answer

Add a unique constraint on ORDER_ID and rely on the optimizer to use it for pruning.

Answer

Create a materialized view that pre-aggregates the table by ORDER_DATE and CUSTOMER_ID.

Question 8

A data engineer is reviewing a query profile for a query that performs a large join. The profile shows a high percentage of time spent in the 'Join' node with significant 'Bytes spilled to local storage'. The engineer wants to reduce local spilling without changing the query. Which action is most appropriate?

Accepted Answer

Increase the warehouse size to provide more memory for the join operation.. Local spilling during a join indicates that the join operation is exceeding the memory available on a warehouse node. Increasing the warehouse size provides more memory per node, which can accommodate larger intermediate results and reduce spilling. Filtering, clustering, or changing join types may have indirect benefits but do not directly resolve the memory pressure. Therefore, scaling up the warehouse is the most appropriate action.

Answer

Reduce the size of the smaller table in the join by filtering it.

Answer

Add a clustering key on the join column of the larger table.

Answer

Change the join type from a hash join to a sort-merge join.

Question 9

A data engineer runs a transformation that loads a 50 GB staged file into a target table using a COPY INTO statement with a single large file. The warehouse is a MEDIUM multi-cluster warehouse. The load takes far longer than expected because one node processes the entire file. Which change most directly improves load throughput?

Accepted Answer

Split the staged file into multiple smaller files of roughly 100-250 MB compressed and load them in the same COPY INTO statement.. COPY INTO distributes work per file, so throughput scales with the number of files rather than the size of the warehouse. Breaking a single large file into many moderately sized compressed files enables parallel processing across nodes, which is the direct remedy for a load dominated by one file.

Answer

Set the ON_ERROR option to CONTINUE and re-run the COPY INTO statement.

Answer

Enable multi-cluster scaling by setting the maximum cluster count to four on the warehouse.

Answer

Increase the warehouse size from MEDIUM to 4X-LARGE before running the same COPY INTO statement.

Question 10

A data engineer runs a long-running aggregation query and inspects the Query Profile. The profile shows a single operator with an output row count roughly 400 times larger than its input row count, and the downstream operator is bottlenecked. Which action should the engineer take to resolve this?

Accepted Answer

Reduce the number of rows produced by the exploding operator by moving the aggregation to a separate earlier stage or by rewriting the join/grouping logic so cardinality grows later in the plan.. The decisive clue is an operator emitting far more rows than it receives, which is the signature of a cardinality explosion rather than a compute or pruning problem. Adding warehouse capacity, toggling result caching, or enabling search optimization all leave that multiplication intact. Only restructuring the plan so the aggregation occurs before or alongside the fan-out keeps downstream operators from processing duplicated rows.

Answer

Enable the Search Optimization Service on the base table so the exploding operator can skip micro-partitions and emit fewer rows.

Answer

Disable the query result cache at the account level so the exploding operator re-executes and produces a smaller result set.

Answer

Increase the size of the virtual warehouse to add more compute nodes so the exploding operator can process more rows per second.

Question 11

A data engineer maintains a table that receives continuous small INSERT statements throughout the day. Query performance on this table has degraded even though total data volume is modest, and the Query Profile shows many very small micro-partitions being scanned. Which action best addresses the underlying cause?

Accepted Answer

Batch the incoming rows and load them less frequently, or use a staging table and periodically merge into the target.. The profile evidence of many tiny micro-partitions points to ingestion pattern rather than query design or compute. Each small insert creates its own micro-partition, so the table becomes a patchwork of fragments that the optimizer must enumerate and scan. Batching or staging-then-merging reduces the number of partitions created, which lowers metadata and scan overhead at the source.

Answer

Schedule periodic reclustering of the table using a clustering key on the most selective filter column.

Answer

Increase the warehouse size so the larger compute pool can scan the many small micro-partitions in parallel.

Answer

Convert the table to a transient table so Snowflake stops maintaining the metadata for the small micro-partitions.

Question 12

A data engineer is optimizing a query that joins a very large fact table to a small dimension table. The Query Profile shows the small table being redistributed across all nodes before the join. Which action is most likely to improve performance?

Accepted Answer

Broadcast the small dimension table so it is replicated to all nodes and the large fact table is not redistributed.. The profile shows the small relation being shuffled, which is the expensive but avoidable side of the join. Broadcasting the small table replicates it to every node so the large fact table stays in place, eliminating the shuffle of the big input. This is the standard remedy when a join's redistribution targets the wrong relation.

Answer

Increase the warehouse size so the redistribution of the small table completes faster across more nodes.

Answer

Rewrite the query to use a correlated subquery instead of a join so the small table is not redistributed.

Answer

Add a clustering key to the small dimension table so its rows are stored contiguously.

Question 13

A data engineer is investigating a query that reads a large table and returns only a few rows after filtering on a high-cardinality column. The Query Profile shows a high percentage of partitions scanned relative to partitions total. Which two actions should the engineer take to improve pruning? (Choose two.)

Accepted Answer

Define a clustering key on the frequently filtered high-cardinality column so related values are co-located in the same micro-partitions.. Effective pruning depends on two things: physical co-location of similar values and predicates the optimizer can translate into partition-value bounds. Clustering the filtered column groups similar values, while a direct comparison to a constant gives the optimizer the bounds it needs. Together they shrink the set of micro-partitions that must be read for a selective filter.

Answer

Increase the virtual warehouse size so more compute nodes are available to evaluate the filter predicate in parallel.

Answer

Convert the table to a view over the same data so the optimizer can push the filter predicate down into the view definition.

Answer

Apply a filter transformation inside the query that wraps the filtered column in a function such as UPPER or CAST before comparing it to a literal.

Question 14

A data engineer notices that a dashboard query returns instantly on repeat executions but takes much longer the first time each morning. No data has changed overnight. Which Snowflake feature is most directly responsible for the fast repeat executions?

Accepted Answer

The query result cache stores the result of the identical query and returns it without re-executing, as long as the underlying data and query text are unchanged.. The near-instant repeat execution with unchanged data is the signature of the query result cache, which persists results and serves identical queries without recomputation. The first run of the day must execute because the result is not yet cached or was invalidated, and any data change would also invalidate it.

Answer

The metadata cache holds micro-partition statistics that let the optimizer skip execution entirely and return a stored row set.

Answer

The local disk cache on the warehouse nodes retains the scanned micro-partitions so the second execution reads them without remote I/O.

Answer

The virtual warehouse remains suspended overnight, so the first execution warms it and later executions reuse the warmed compute.

Question 15

A data engineer notices that a query against a large table returns results quickly when filtering on one column but scans the entire table when filtering on another column. Both columns are used in equality predicates. What is the most likely explanation?

Accepted Answer

The fast column is a clustering key, so the optimizer can prune micro-partitions for that predicate.. Micro-partition pruning depends on the physical ordering of data. A clustering key aligns the data so that equality filters on that column can skip irrelevant micro-partitions, while an unclustered column forces a full scan. This accounts for the difference in the observed query times.

Answer

The fast column has a lower cardinality, so the optimizer can use metadata to skip partitions.

Answer

The fast column is part of the table's primary key, which automatically prunes partitions.

Answer

The fast column is defined with a NOT NULL constraint, which enables partition skipping.