COF-C03 Performance Optimization, Querying, and Transformation Practice Test 2 — 15 Questions

Question 1

A data engineer runs a complex query against a large fact table joined to several dimension tables. The Query Profile shows a significant amount of time spent in the 'Join' operator, and the optimizer has chosen a hash join. The engineer wants to improve performance by ensuring the smaller dimension tables are broadcast to all nodes. Which Snowflake feature or technique should be used?

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

Accepted Answer

Ensure that the dimension tables are small enough to fit in memory and rely on the optimizer's automatic broadcast decision.. Snowflake's optimizer automatically determines join strategies, including broadcast joins, based on table statistics and sizes. When a dimension table is small enough, the optimizer can broadcast it to all nodes, avoiding the expensive shuffle of the large fact table. Ensuring accurate statistics and keeping dimension tables small enables this optimization.

Answer

Enable the BROADCAST join hint by adding /*+ BROADCAST(dim_table) */ to the query.

Answer

Use a clustering key on the join column of the large fact table.

Answer

Add a search optimization service to the dimension tables.

Question 2

A data analyst runs a query that joins a 2 TB fact table to a small 5 MB dimension table. The Query Profile shows a Broadcast Join, and the query takes 12 minutes. The analyst wants to reduce the execution time by changing how the data is distributed before the join. Which action should the analyst take?

Accepted Answer

Rewrite the query to join on a distribution key that matches the fact table's natural clustering, or explicitly repartition the fact table using a CTE with a hash distribution.. The broadcast join is typically efficient for small dimension tables, but when the fact table is extremely large, broadcasting the small table to every node may cause excessive network traffic and memory pressure. Repartitioning the large table on the join key or aligning with its clustering key allows Snowflake to perform a shuffle join where each node processes a subset of data, reducing data movement and improving parallelism.

Answer

Disable the broadcast join by setting USE_BROADCAST_JOIN = FALSE at the session level.

Answer

Create a materialized view on the fact table that pre-aggregates the join key.

Answer

Add a CLUSTER BY clause on the join key of the small dimension table so it is co-located with the fact table.

Question 3

A data analyst needs to transform semi-structured JSON data stored in a VARIANT column into a relational table with separate columns for each attribute. The JSON objects have a consistent schema. Which Snowflake feature should the analyst use to achieve this transformation efficiently?

Accepted Answer

Use the VARIANT extraction operator (:) with a SELECT statement to create columns.. When JSON objects have a consistent schema, the most efficient way to transform them into relational columns is to use the VARIANT extraction operator (:) along with explicit casts. This approach allows the analyst to select each attribute as a separate column in a single query, avoiding the overhead of exploding and re-aggregating data. It is a straightforward and performant method for this common transformation task.

Answer

Use the PARSE_JSON function to convert the VARIANT into a relational format.

Answer

Use the FLATTEN function to explode the JSON into multiple rows.

Answer

Use the OBJECT_CONSTRUCT function to build a relational table.

Question 4

A data engineer runs a query that joins a 2 TB fact table to a small 10 MB dimension table. The Query Profile shows a CartesianJoin operator and the query returns far more rows than expected. The join condition in the SQL is `ON fact.dim_key = dim.dim_key`. The engineer wants to correct the query so it performs an inner join and avoids the Cartesian product. Which action should the engineer take?

Accepted Answer

Ensure the join condition is not wrapped in a function that prevents predicate pushdown and confirm both sides of the equality are column references.. The CartesianJoin operator indicates that Snowflake could not apply the join predicate, often because the condition is missing or is written in a way that prevents the optimizer from using it, such as wrapping a column in a function. Confirming that the equality is between two column references restores the intended inner join and avoids the Cartesian product.

Answer

Rewrite the query to use a subquery that selects only the needed columns from the dimension table before joining.

Answer

Verify that the join condition references columns with compatible data types and that the dimension table has been analyzed with AUTO_STATS.

Answer

Add a WHERE clause that filters the fact table by a date range before the join.

Question 5

A data engineer runs a complex aggregation against a large fact table in Snowflake. The Query Profile shows the join operator is producing a much larger intermediate result set than expected, and the query is slow. The engineer suspects that the join order chosen by the optimizer is suboptimal. Which action should the engineer take to address this issue?

Accepted Answer

Collect fresh statistics on the joined tables using ALTER TABLE ... CLUSTER BY or rely on automatic statistics, then re-run the query.. The optimizer relies on up-to-date statistics to estimate cardinalities and choose join order. When statistics are stale or missing, it may pick a suboptimal join order, causing large intermediate results. Ensuring statistics are current allows the optimizer to make better decisions. Clustering can also help by providing more accurate pruning information. The other options either force inefficient join types, use unsupported hints, or do not directly influence join order.

Answer

Rewrite the query to use a CROSS JOIN between the fact table and the dimension table.

Answer

Use the JOIN keyword with the ORDERED hint to force the join order as written in the query.

Answer

Add a clustering key on the join column of the larger table and let Snowflake automatically reorder joins.

Question 6

A data engineer is optimizing a complex query that joins five tables and uses multiple aggregations. The Query Profile shows a significant amount of time spent in the Join and Aggregate operators. The engineer wants to leverage Snowflake's adaptive optimization techniques to improve performance without rewriting the query. Which TWO features are examples of adaptive optimizations that Snowflake may apply automatically? (Choose two.)

Accepted Answer

Dynamic pruning of micro-partitions during query execution based on runtime filters.. Snowflake's optimizer applies adaptive techniques such as join reordering and dynamic partition pruning to improve query performance without user intervention. Join reordering uses cardinality estimates to execute more selective joins earlier, reducing intermediate data. Dynamic pruning uses runtime filters to skip micro-partitions that cannot satisfy join or filter conditions, lowering I/O. These optimizations are visible in the Query Profile as reduced rows processed and bytes scanned. Materialized views and warehouse scaling are manual or concurrency-focused and do not qualify as adaptive query optimizations.

Answer

Automatic scaling of the virtual warehouse during query execution to add more compute nodes.

Answer

On-the-fly compression of intermediate results to reduce network transfer between warehouses.

Answer

Automatic creation of materialized views for frequently executed subqueries.

Question 7

A data engineer is writing a query that filters on the column ORDER_DATE, which is a DATE. The engineer writes WHERE ORDER_DATE = '2024-01-15'. The query runs but returns no results, even though the table contains rows for that date. The engineer verifies that the data exists. What is the most likely cause?

Accepted Answer

The string literal is being interpreted as a TIMESTAMP with the session timezone, and the DATE column is compared to the timestamp, causing a mismatch when the timezone offset shifts the date.. The most likely cause is that Snowflake implicitly converts the string literal to a TIMESTAMP using the session timezone, and then compares it to the DATE column. If the session timezone has a positive offset, the timestamp may fall on the previous date, causing the equality check to fail. To avoid this, use TO_DATE on the literal or ensure the session timezone is UTC.

Answer

The ORDER_DATE column is stored as a string, so the comparison is lexicographic.

Answer

The string literal is being implicitly cast to TIMESTAMP, and the time component is set to midnight, but the column is DATE, so the comparison fails due to type mismatch.

Answer

The session timezone is set to a non-UTC timezone, causing the date to shift.

Question 8

A data engineer notices that a query filtering on a VARCHAR column with a high number of distinct values is performing poorly. The Query Profile shows a full table scan despite the filter. The engineer wants to improve performance by reducing the amount of data scanned. Which Snowflake feature should the engineer implement?

Accepted Answer

Search optimization service on the VARCHAR column.. The search optimization service is specifically designed to speed up selective queries on high-cardinality columns by maintaining a persistent index-like structure. It allows Snowflake to prune micro-partitions efficiently, reducing the data scanned for point lookups. This is the recommended approach when a full table scan occurs despite a selective filter on a VARCHAR column with many distinct values.

Answer

Clustering the table on the VARCHAR column.

Answer

Using the RESULT_SCAN function to retrieve cached results.

Answer

Creating a materialized view that filters on the VARCHAR column.

Question 9

A reporting team runs a nightly aggregation over a 4 TB table. The aggregation groups by a DATE column and filters on a REGION column. The table was defined with CLUSTER BY (REGION_ID) two years ago and has never been re-clustered since, though the average depth remains around 4. A data engineer wants to reduce the volume of micro-partitions scanned without rewriting the table. Which action is most appropriate?

Accepted Answer

Run ALTER TABLE ... RECLUSTER to reorganize the existing micro-partitions against the current clustering key.. The table already has a clustering key on REGION_ID, and the nightly query filters on REGION, so the fastest path to fewer scanned micro-partitions is to restore physical clustering against the existing key. RECLUSTER rewrites the affected micro-partitions so that rows with similar clustering key values are grouped together, which lets the optimizer prune more aggressively. Changing the clustering key or adding other objects does not repair the existing layout.

Answer

Create a materialized view that pre-aggregates by DATE and REGION, then query the view.

Answer

Suspend automatic clustering and rebuild the table with CLUSTER BY (DATE, REGION) instead.

Answer

Add a search optimization service to the REGION_ID column so the filter is resolved from the search access path.

Question 10

A data engineer is designing a transformation pipeline that will run daily. The pipeline needs to process incremental data and ensure that only new or modified records are processed. Which TWO Snowflake features should be used to efficiently identify new or modified records? (Choose two.)

Accepted Answer

Streams. Streams and Change Tracking are both designed to capture and provide access to change data in Snowflake. Streams record changes to a table and allow consuming them as a changelog, while Change Tracking enables efficient queries to retrieve only changed rows. Together, they support incremental processing and CDC pipelines, ensuring only new or modified records are processed.

Answer

Time Travel

Answer

Materialized Views

Answer

Tasks

Question 11

A data engineer runs a query that aggregates sales data by region and product category. The query takes 10 minutes on a Medium warehouse. The engineer notices that the query spends most of its time in the 'Aggregate' operator and that the data is evenly distributed. The engineer wants to reduce the execution time without increasing the warehouse size. Which action should the engineer take?

Accepted Answer

Pre-aggregate the data into a materialized view and query the materialized view.. Pre-aggregating the data into a materialized view is an effective way to reduce the time spent in the Aggregate operator without scaling up the warehouse. The materialized view stores the aggregated results, so the query can simply read the pre-computed data. Snowflake automatically maintains the view and can transparently rewrite queries to use it, providing faster performance for repetitive aggregation workloads.

Answer

Increase the warehouse size to a Large to double the compute resources.

Answer

Rewrite the query to use a GROUP BY on a subset of columns and then join the results.

Answer

Use a window function instead of GROUP BY to compute the aggregates.

Question 12

A data engineer is working with a large table that has a clustering key defined on a timestamp column. The engineer notices that queries filtering on a different column, customer_id, are not benefiting from clustering. The engineer wants to improve performance for these queries without changing the existing clustering key. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Add a search optimization service to the table for the customer_id column.. To improve performance on a column that is not the clustering key, the engineer can create a materialized view that filters on customer_id and cluster it on that column, or add the Search Optimization Service on the base table for customer_id. Both approaches avoid changing the existing clustering key and provide efficient access paths for queries filtering on customer_id. Changing the clustering key or adding a secondary key is not feasible, and the CLUSTER BY clause is not used in SELECT.

Answer

Create a secondary clustering key on customer_id in addition to the existing clustering key.

Answer

Use the CLUSTER BY clause in the SELECT statement to dynamically cluster the results by customer_id.

Answer

Change the clustering key to customer_id and remove the timestamp clustering key.

Question 13

A data engineer is working with a large table that has a clustering key on `customer_id`. The engineer runs a query that filters on `customer_id` and also joins to a smaller table. The Query Profile shows that the join is using a broadcast join, and the query is spilling to remote disk. The engineer wants to reduce spilling and improve performance. Which action should the engineer take?

Accepted Answer

Increase the size of the virtual warehouse to provide more memory for the join.. Spilling to remote disk during a join is a sign that the virtual warehouse lacks sufficient memory for the operation. Increasing the warehouse size provides more memory and compute, which can eliminate spilling and improve performance. Other options do not directly address the memory shortfall.

Answer

Disable the broadcast join by setting the parameter ENABLE_BROADCAST_JOIN to FALSE.

Answer

Rewrite the query to use a NOT IN subquery instead of a join.

Answer

Add a clustering key on the join key of the smaller table.

Question 14

A data engineer is building a transformation pipeline that processes a stream of semi-structured events stored in a VARIANT column. The events contain a nested field `details` that may be either a JSON object or a JSON array, depending on the event type. The engineer needs to extract a specific key `status` from `details` regardless of whether `details` is an object or an array of objects, and return the first non-null status found. Which Snowflake function or approach is most appropriate?

Accepted Answer

Use `COALESCE(details:status, details[0]:status)` to attempt extraction from both an object and an array.. When a VARIANT field can be either an object or an array, `COALESCE` provides a concise way to attempt extraction from both structures. `details:status` succeeds for objects, while `details[0]:status` succeeds for arrays. The first non-null result is returned. Other approaches either assume a fixed structure, require additional flattening and aggregation, or introduce unnecessary conditional logic. `COALESCE` directly satisfies the requirement to return the first non-null status.

Answer

Use `GET_PATH(details, 'status')` to directly extract the status value.

Answer

Use `FLATTEN(input => details)` and then filter on `VALUE:status`.

Answer

Use `TYPEOF(details)` to branch the logic and then call `OBJECT_GET` or `ARRAY_GET` accordingly.

Question 15

A query is experiencing performance degradation, and the Query Profile indicates 'Remote Disk Spilling'. Which action is the most direct solution to resolve this specific bottleneck?

Accepted Answer

Scale up the virtual warehouse to a larger size.. Remote disk spilling occurs when the local SSD storage of a virtual warehouse is completely exhausted, forcing Snowflake to write intermediate data to slower remote cloud storage. This usually happens during large sorts, joins, or aggregations. Increasing the warehouse size provides more memory and local storage, ensuring that large intermediate result sets can be processed without hitting the high-latency remote storage layer.

Answer

Enable the Search Optimization Service on the table.

Answer

Implement a clustering key on the table's join columns.

Answer

Create a Materialized View for the underlying query.