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

Question 1

A Snowflake analyst runs a query that aggregates sales by region over the last 30 days. The query takes 40 seconds on an X-Small warehouse. The analyst then reruns the exact same query 10 minutes later without any data changes. It completes in 1 second. Which Snowflake feature explains this behavior?

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

Accepted Answer

Result Cache. The dramatic speedup on an identical query with unchanged data is the signature of Result Cache. Snowflake stores the result set in the Cloud Services layer, so a repeat query is served without engaging the virtual warehouse. Local disk and metadata caches accelerate data scanning and pruning but still require query execution, and auto-suspend does not cache results.

Answer

Warehouse Auto-Suspend

Answer

Metadata Cache

Answer

Local Disk Cache

Question 2

A data engineer runs a query that joins a large fact table with a small dimension table. The Query Profile shows a Join operation with an exploding number of rows and significant spilling to remote disk. The engineer notices the join condition uses a function on the join key of the large table. Which action is most likely to improve performance?

Accepted Answer

Rewrite the join to avoid applying a function to the join key of the large table.. The function on the join key prevents the optimizer from using an efficient join strategy and can cause a row explosion. Rewriting the join to compare raw columns allows hash join and pruning, directly fixing the root cause. Larger warehouses, clustering on the raw column, or caching do not address the expression in the join predicate.

Answer

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

Answer

Increase the size of the virtual warehouse to a larger multi-cluster warehouse.

Answer

Enable the USE_CACHED_RESULT parameter for the session.

Question 3

A data engineer needs to transform semi-structured data stored in a VARIANT column that contains an array of JSON objects into a relational table with one row per object. The engineer wants to use a Snowflake function that can expand the array into multiple rows. Which function should be used?

Accepted Answer

FLATTEN. FLATTEN is the correct function to expand an array of objects into multiple rows. It is typically used in the FROM clause with LATERAL, allowing each element of the array to become a separate row while preserving access to the original row's columns. The other functions either create VARIANT values or aggregate rows, not expand arrays.

Answer

PARSE_JSON

Answer

OBJECT_CONSTRUCT

Answer

ARRAY_AGG

Question 4

A data engineer needs to transform semi-structured JSON data stored in a VARIANT column into a relational table. The JSON contains nested arrays and objects. Which Snowflake feature should be used to flatten the arrays into separate rows while preserving the parent-child relationship?

Accepted Answer

LATERAL FLATTEN. LATERAL FLATTEN is designed to explode nested arrays and objects into rows while maintaining the association with the source row. It is the correct tool for transforming semi-structured data into a relational format. The other functions either construct objects, aggregate into arrays, or parse strings, none of which expand arrays into multiple rows.

Answer

PARSE_JSON

Answer

ARRAY_AGG

Answer

OBJECT_CONSTRUCT

Question 5

A data engineer is analyzing a slow query and notices that the Query Profile shows a high percentage of time spent in 'TableScan' with many partitions scanned but few rows returned. Which action is most likely to improve performance?

Accepted Answer

Add a clustering key on the columns used in the filter predicates.. The Query Profile indicates excessive partitions scanned, which is a sign of poor partition pruning. Clustering the table on the filter columns can co-locate similar values, allowing the optimizer to skip more partitions. Increasing warehouse size or clusters does not reduce the amount of data read, and enabling Search Optimization on all columns is not a focused fix.

Answer

Enable the Search Optimization Service on all columns.

Answer

Use a larger warehouse with more clusters.

Answer

Increase the size of the virtual warehouse.

Question 6

A user runs a query that filters on a column with a high cardinality and the table is not clustered. The query scans a large number of micro-partitions. Which action would most directly reduce the number of micro-partitions scanned?

Accepted Answer

Add a clustering key on the filtered column.. Clustering keys physically sort data so that similar values are co-located in micro-partitions. This enables the optimizer to prune partitions based on filter predicates, directly reducing the number of micro-partitions scanned. Larger warehouses or caching do not change the volume of data scanned for the initial query.

Answer

Use a larger warehouse with multi-cluster scaling.

Answer

Increase the size of the virtual warehouse.

Answer

Enable result caching for the session.

Question 7

A data engineer is optimizing a complex query that joins multiple large tables and includes several aggregations. The Query Profile shows a high number of rows spilled to local disk and remote disk. Which TWO actions are most likely to reduce spilling and improve performance? (Choose two.)

Accepted Answer

Rewrite the query to filter and aggregate data earlier in the pipeline.. Spilling indicates that intermediate results exceed available memory. Increasing warehouse size provides more memory, and rewriting the query to filter and aggregate earlier reduces the volume of intermediate data. Clustering, result caching, and temporary tables do not directly address the memory pressure causing the spilling.

Answer

Add a clustering key on the join columns of the largest table.

Answer

Convert the query to use a temporary table to materialize intermediate results.

Answer

Enable the USE_CACHED_RESULT parameter to reuse previous results.

Question 8

A data engineer has a large table SALES_RAW with a VARIANT column PAYLOAD that stores semi-structured JSON. The engineer needs to flatten an array of product objects inside PAYLOAD into separate rows, keeping all other columns intact. Which Snowflake construct should be used in the SELECT statement to achieve this?

Accepted Answer

LATERAL FLATTEN(input => PAYLOAD:products). The LATERAL FLATTEN table function is the standard Snowflake mechanism for expanding semi-structured arrays into rows while keeping the parent row's other columns. It accepts a VARIANT input and returns one row per element, making it ideal for converting nested JSON into a relational result set without losing context.

Answer

PARSE_JSON(PAYLOAD):products

Answer

ARRAY_AGG(PAYLOAD:products)

Answer

OBJECT_CONSTRUCT('products', PAYLOAD:products)

Question 9

A developer needs to flatten a VARIANT column named payload that contains a nested JSON array of order line items into individual rows, preserving the parent order attributes alongside each line item. Which Snowflake construct accomplishes this in a single SELECT statement?

Accepted Answer

A LATERAL FLATTEN of the payload:line_items array joined back to the parent row.. LATERAL FLATTEN is Snowflake's dedicated mechanism for exploding semi-structured arrays and objects into relational rows. Used laterally, it correlates each element with its parent row, so order attributes remain available alongside each line item. This yields a fully relational result set from nested JSON in one statement without manual recursion or aggregation.

Answer

A PARSE_JSON call applied to the payload column in the SELECT list.

Answer

A recursive common table expression that walks the JSON hierarchy level by level.

Answer

A GROUP BY on the VARIANT column with an ARRAY_AGG of the line items.

Question 10

A data engineer is optimizing a transformation pipeline and wants to reduce compute cost and improve performance for queries that repeatedly scan the same large table with different filters. Which TWO Snowflake features or techniques directly support this goal? (Choose two.)

Accepted Answer

Create a materialized view that pre-aggregates the filtered results.. Clustering and materialized views both attack the cost of repeated scans. Clustering reduces the micro-partitions read by improving pruning on filtered columns, while materialized views precompute and maintain aggregated results so recurring queries avoid touching the base table. Together they cut compute for the described pattern, whereas timeout settings, cache disabling, and transient storage do not improve scan efficiency.

Answer

Disable the result cache at the account level to force fresh execution.

Answer

Convert the table to a transient table to avoid fail-safe storage charges.

Answer

Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter for the session.

Question 11

A finance analyst runs a monthly report that aggregates 18 months of sales data. The report executes 40 times per day, and each run currently takes 4 minutes on a medium warehouse. The underlying tables are loaded once nightly. Which approach most effectively reduces compute cost for this workload?

Accepted Answer

Enable the result cache by ensuring the query text and session context are identical across runs.. The tables are static during the business day, so the 40 daily executions read identical data. Ensuring identical query text and session context lets Snowflake serve subsequent runs from the result cache without provisioning compute, eliminating nearly all of the recurring cost. Resizing, auto-suspend tuning, and multi-cluster scaling change resource behavior but do not remove the redundant scans.

Answer

Set the warehouse to auto-suspend after 60 seconds to avoid idle credits.

Answer

Create a separate virtual warehouse for the analyst and enable multi-cluster scaling.

Answer

Resize the warehouse to 4X-Large so each run finishes faster.

Question 12

A data engineer needs to transform a table by unpivoting columns Q1, Q2, Q3, Q4 into rows with a quarter label and sales amount. Which Snowflake SQL construct is designed for this task?

Accepted Answer

UNPIVOT. The UNPIVOT operator in Snowflake is the correct tool for converting columns into rows. It takes a list of columns and produces a result set with one row per column per original row, including a label column and a value column. This is exactly what is needed to transform Q1, Q2, Q3, Q4 into quarter labels and sales amounts.

Answer

LATERAL FLATTEN

Answer

PIVOT

Answer

ARRAY_AGG

Question 13

A data engineer notices a large table's clustering depth is very high on the columns used in frequent range filters, and queries are scanning far more micro-partitions than expected. The table receives continuous small inserts throughout the day. Which action best improves pruning while controlling reclustering cost?

Accepted Answer

Batch the small inserts into larger, less frequent loads so fewer micro-partitions are created out of order.. Frequent tiny inserts scatter data across many micro-partitions, inflating clustering depth and undermining min-max pruning. Consolidating those inserts into larger batches yields better-ordered micro-partitions, so range filters prune more effectively and automatic reclustering has less fragmented data to reorganize. Choosing a different key or removing clustering would not solve the root cause of the poor ordering.

Answer

Remove the clustering key and add a search optimization service on the filter columns instead.

Answer

Keep the existing clustering key and rely on automatic reclustering to restore ordering.

Answer

Change the clustering key to a high-cardinality column such as a unique transaction ID.

Question 14

A data engineer needs to transform semi-structured JSON data stored in a VARIANT column into a relational table with separate columns for each attribute. The JSON contains nested objects and arrays. Which Snowflake feature should the engineer use to flatten the arrays and extract the nested attributes in a single SQL statement?

Accepted Answer

LATERAL FLATTEN with the INPUT => column and PATH => 'nested_array' arguments, combined with the VALUE and THIS keywords.. LATERAL FLATTEN is the primary Snowflake construct for expanding arrays within a VARIANT column. It produces one row per element in the array, and by using the VALUE or THIS keyword, the engineer can access the element's attributes. Combining this with a SELECT that references the original table's columns and the flattened output allows a single SQL statement to transform nested JSON into a relational result set.

Answer

Create a materialized view over the VARIANT column and query it with standard SQL.

Answer

Use the TRY_CAST function to convert the VARIANT to a string and then use string functions to parse the JSON.

Answer

Use the PARSE_JSON function to convert the VARIANT into a relational table automatically.

Question 15

A data engineer is optimizing a complex query that joins multiple large tables and applies several aggregations. The Query Profile shows significant time spent in the Join and Aggregate operators, and the warehouse is sized appropriately. Which TWO actions should the engineer take to improve performance? (Choose two.)

Accepted Answer

Collect statistics on the join keys and filter columns.. The query is experiencing bottlenecks in join and aggregation operations despite adequate warehouse size. Collecting statistics on join keys and filter columns gives the optimizer the information it needs to generate a more efficient plan, such as choosing the optimal join order and aggregation method. Additionally, ensuring that join keys have the same data type avoids implicit casting, which can hinder performance by preventing the use of efficient join algorithms. Together, these actions address the root causes of the performance issue.

Answer

Use a larger virtual warehouse to increase compute resources.

Answer

Add search optimization service to the tables involved in the join.

Answer

Rewrite the query to use temporary tables for intermediate results.