DEA-C02 Performance Optimization Practice Test 4 — 15 Questions

Question 1

What is the primary benefit of using a 'Materialized View' over a standard view in Snowflake?

Accepted Answer

It reduces compute costs by pre-computing query results.. Materialized views store the pre-computed results of a query, which avoids the overhead of re-calculating the results during each execution. This is extremely beneficial for queries that are complex, resource-intensive, and executed frequently. By contrast, a standard view computes its output every time it is called, consuming compute resources and potentially causing latency for end-users, whereas materialized views provide near-instant access to the computed dataset.

Answer

It supports real-time data streaming updates.

Answer

It automatically updates based on all underlying table changes.

Answer

It is the only way to join two tables in Snowflake.

Question 2

Which TWO of the following are considered 'anti-patterns' for performance in Snowflake?

Accepted Answer

Using one large warehouse for all user workloads.. Using a single massive warehouse for all workloads leads to poor resource isolation and contention, as simple queries compete with heavy ones. Additionally, using SELECT * in production code is an anti-pattern because it retrieves unnecessary columns, forcing the system to read more data blocks than required. Both practices degrade performance and increase costs by wasting I/O and compute resources on operations that could be avoided.

Answer

Specifying column names instead of SELECT *.

Answer

Utilizing multi-cluster warehouses for concurrency.

Answer

Implementing materialized views for performance.

Question 3

A warehouse is being used for both ETL processes and ad-hoc BI reporting. Users report that BI reports are slow during ETL runs. What is the best optimization?

Accepted Answer

Separate workloads into two distinct warehouses.. The most effective way to address contention between different workload types is to isolate them into separate virtual warehouses. ETL processes are typically compute-intensive, while BI reporting requires low latency. By separating these into two warehouses, the ETL process cannot starve the BI reports of resources, and you can size each warehouse appropriately for its specific task, ensuring both workloads run efficiently without competing for the same compute resources.

Answer

Increase the size of the existing warehouse.

Answer

Use the 'Economy' scaling policy for the warehouse.

Answer

Enable search optimization for the BI reporting tables.

Question 4

Refer to the exhibit. A data engineer is analyzing a Query Profile for a long-running join operation. Based on the provided JSON statistics, what is the most effective action to improve the performance of this specific query?

Accepted Answer

Scale up the virtual warehouse to a larger size.. The exhibit shows significant spillage to both local and remote disk, with 1.2TB reaching remote storage. Remote spillage is a critical performance bottleneck because it involves network latency to S3/Azure Blob. Moving to a larger warehouse provides more memory and local storage per node, allowing the join to stay in-memory or on faster local SSDs.

Answer

Enable the Search Optimization Service on the join columns.

Answer

Apply a clustering key to the tables involved in the join.

Answer

Rewrite the query to use a Common Table Expression (CTE).

Question 5

A company is using a Multi-cluster Warehouse (MCW) with a 'Standard' scaling policy. Users report that during peak morning hours, query queuing occurs briefly before a new cluster starts. What is the effect of changing the scaling policy to 'Economy' in this scenario?

Accepted Answer

Queuing will increase because the warehouse waits longer to start a new cluster.. The Economy scaling policy prioritizes credit savings over immediate performance by waiting until there is enough consistent load to keep a new cluster busy for six minutes. In this scenario, switching to Economy would likely increase queuing times because the system becomes more conservative about spinning up additional compute resources compared to the Standard policy.

Answer

Queuing will decrease because the Economy policy optimizes cluster distribution.

Answer

Performance will remain identical but the total credit consumption will decrease.

Answer

The warehouse will automatically scale up to a larger T-shirt size.

Question 6

When designing a clustering key for a table that is frequently joined with other tables, which strategy generally provides the best performance for the join operation?

Accepted Answer

Cluster on the column used as the join key.. Clustering a table on the columns used in join predicates (the join keys) ensures that Snowflake can use join pruning and potentially more efficient join algorithms. When data is co-located within micro-partitions based on the join key, the engine can skip entire partitions that do not have matching keys in the other table, reducing I/O.

Answer

Cluster on the most frequently used filter column in the WHERE clause.

Answer

Cluster on the column with the highest cardinality in the table.

Answer

Cluster on a column that is never updated or changed.

Question 7

Refer to the exhibit. A data engineer runs this query to investigate clustering costs. The output shows high credit consumption but the 'Clustering Depth' of the table remains high. What is the most likely cause of this behavior?

Accepted Answer

The table is being continuously updated with data that overlaps existing ranges.. High credit consumption combined with high clustering depth usually indicates that the table is being frequently updated or appended with data that is significantly 'out of order' relative to the clustering key. This causes the Automatic Clustering service to continuously work to re-sort the data, but the constant influx of unsorted data prevents the depth from improving.

Answer

The clustering key is defined on a column with a 'Date' data type.

Answer

The warehouse used for clustering is too small for the table size.

Answer

The Search Optimization Service is conflicting with the clustering service.

Question 8

A user runs the same complex analytical query twice within 5 minutes and notices the second execution is nearly instantaneous. Which Snowflake feature is primarily responsible for this performance improvement?

Accepted Answer

Result Cache. Snowflake's Result Cache stores the results of queries for 24 hours. If a user executes the exact same query and the underlying data in the table has not changed, Snowflake will return the results directly from the cache, bypassing the compute warehouse entirely and providing sub-second response times.

Answer

Metadata Caching

Answer

Warehouse Local Disk Caching

Answer

Query Acceleration Service

Question 9

While reviewing a Query Profile, a data engineer notices a 'Join' operator where the number of output rows is significantly larger than the sum of the input rows. Which TWO steps should be taken to resolve this performance issue? (Select TWO)

Accepted Answer

Verify if the join keys have a many-to-many relationship.. An output row count significantly larger than input row counts indicates an 'exploding join' or Cartesian product, usually caused by many-to-many relationships or missing join conditions. Validating the join predicates and ensuring the join keys are unique or properly filtered can stop the exponential growth of the intermediate result set.

Answer

Increase the size of the warehouse to handle the extra rows.

Answer

Enable the Search Optimization Service on the join columns.

Answer

Replace the JOIN with a UNION ALL operation.

Question 10

A data engineer is loading 1TB of data from an S3 bucket into Snowflake using the COPY command. The data consists of 10,000 small files (approx. 100KB each). How will this file size affect the loading performance?

Accepted Answer

Loading will be slow due to the high overhead of processing many small files.. Snowflake performance is optimized when file sizes are between 100MB and 250MB compressed. Having 10,000 tiny files creates excessive overhead because each file requires a separate metadata operation and a separate request to the cloud storage. This prevents the warehouse from effectively parallelizing the load and saturating its compute nodes.

Answer

Performance will be optimal because many files allow for maximum parallelism.

Answer

The warehouse will automatically group the files into larger chunks before loading.

Answer

Snowflake will only use a single thread to maintain data integrity for small files.

Question 11

A query is filtering a table based on a 'TRANSACTION_DATE' column using a range (e.g., BETWEEN '2023-01-01' AND '2023-01-31'). The table is 500GB and not explicitly clustered. Why might this query still perform well and show good partition pruning?

Accepted Answer

The data was inserted in chronological order, creating natural clustering.. Snowflake micro-partitions are immutable and created in the order data is inserted. If the data is naturally loaded in chronological order, the 'TRANSACTION_DATE' values will be naturally clustered within the micro-partitions. This 'natural clustering' allows the metadata-driven pruning to skip partitions that fall outside the date range, even without an explicit clustering key.

Answer

The Result Cache is automatically storing the range for all users.

Answer

Snowflake automatically clusters all Date columns by default.

Answer

The query is small enough to fit entirely in the warehouse's metadata cache.

Question 12

A data engineer wants to optimize a query that performs a point lookup on a value nested deep within a VARIANT column in a 50TB table. Which approach is the most effective for optimizing this lookup?

Accepted Answer

Enable Search Optimization on the VARIANT column's specific paths.. The Search Optimization Service in Snowflake supports semi-structured data, including fields within VARIANT, OBJECT, and ARRAY types. By enabling search optimization on these specific paths, Snowflake creates a specialized index that allows for efficient point lookups of nested values, significantly reducing the amount of data scanned from the 50TB table.

Answer

Flatten the VARIANT column into a Materialized View.

Answer

Cluster the table based on the extracted value from the VARIANT column.

Answer

Use a standard VIEW to pre-parse the VARIANT data for all users.

Question 13

A data engineer is monitoring warehouse performance and notices a high 'Warehouse Overload' status. Which TWO metrics from the Query History or Warehouse Load monitoring views should be prioritized to confirm the warehouse is undersized? (Select TWO)

Accepted Answer

Queued Overload Time. Warehouse overloading is characterized by queries waiting in a queue because all available compute threads are occupied. High queuing times (Queued Overload Time) and significant disk spillage (Remote Disk Spillage) are the primary indicators that the current warehouse size cannot handle the concurrency or the memory requirements of the workload.

Answer

Total Number of Queries Executed

Answer

Percentage of Metadata Scans

Answer

Data Scanned from Cache

Question 14

A data engineer runs a query on a Snowflake virtual warehouse that scans a 5 TB table but returns only 100 rows. The Query Profile shows that the TableScan operator processed 5 TB of data even though a highly selective filter on a non-clustered column was applied. The warehouse is sized Medium. Which action will most effectively reduce the amount of data scanned by this query?

Accepted Answer

Add a clustering key on the filtered column and enable automatic clustering.. The TableScan reads 5 TB because the filter column is not clustered, so micro-partition pruning cannot eliminate irrelevant data. Adding a clustering key on the filtered column, with automatic clustering enabled, reorganizes micro-partitions so the optimizer can skip most of them. This reduces bytes scanned and directly targets the bottleneck shown in the Query Profile, rather than adding compute or duplicating data.

Answer

Create a materialized view that selects all columns from the table.

Answer

Increase the warehouse size from Medium to 2X-Large.

Answer

Add a search optimization service to the table.

Question 15

A data engineer runs a query that joins a 5 TB fact table to a 200 GB dimension table. The query profile shows a broadcast operation for the dimension table and a local spilling node on the fact table. The engineer wants to reduce local spilling without changing the query results. The warehouse is a multi-cluster warehouse with sufficient memory. Which action is most likely to reduce local spilling?

Accepted Answer

Increase the warehouse size to provide more memory per node.. Local spilling indicates that an operation is exceeding the memory available on a warehouse node. Increasing the warehouse size provides more memory per node, which can prevent or reduce spilling. While other actions like filtering or clustering might reduce data volume, they do not directly address the memory constraint of the operation. Disabling broadcast could change the join strategy but may introduce other bottlenecks. Therefore, scaling up the warehouse is the most direct solution.

Answer

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

Answer

Reduce the size of the fact table by filtering out rows before the join.

Answer

Disable broadcast for the dimension table to force a shuffle join.