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

Question 1

A data engineer is tuning a query that filters on a VARCHAR column `status` with values such as 'ACTIVE', 'INACTIVE', and 'PENDING'. The table is very large and the query currently performs a full table scan. The engineer wants to reduce the amount of data scanned by using a search optimization service. Which action should the engineer take?

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

Accepted Answer

Enable the search optimization service on the table and specify the status column in the search optimization configuration.. The search optimization service is designed to accelerate selective point lookups and substring searches on large tables. Enabling it on the table and adding the status column to its configuration allows Snowflake to use an optimized access path that avoids a full table scan, directly addressing the performance issue.

Answer

Create a secondary index on the status column using the CREATE INDEX command.

Answer

Add a clustering key on the status column and expect the query to use clustering for pruning.

Answer

Create a materialized view on the status column and query the materialized view instead.

Question 2

A data engineer is working with a table that contains a VARIANT column storing arrays of JSON objects. The engineer needs to produce a report that lists each object's attributes in separate rows. Which Snowflake function should the engineer use to transform the array into multiple rows?

Accepted Answer

FLATTEN. The FLATTEN function is specifically designed to explode semi-structured arrays and objects into multiple rows. When applied to a VARIANT column containing an array of objects, it returns one row per object, allowing the engineer to access each object's attributes. This is the correct approach for transforming nested data into a relational format for reporting. Other functions either create semi-structured data or aggregate rows, which are not appropriate here.

Answer

OBJECT_CONSTRUCT

Answer

PARSE_JSON

Answer

ARRAY_AGG

Question 3

A data engineer is optimizing a query that filters on a high-cardinality column in a very large table. The query currently performs a full table scan. The engineer decides to add a clustering key on that column. After clustering, the query performance improves significantly for some queries but remains poor for others that filter on a different low-cardinality column. What is the most likely reason for the inconsistent performance?

Accepted Answer

Clustering keys only improve performance for queries that filter on the clustering key column; other filters benefit only if they are correlated with the clustering key.. Clustering improves pruning only for predicates on the clustering key or on columns that are correlated with it. When queries filter on a different, uncorrelated column, the micro-partitions cannot be pruned based on that column, so performance remains poor. The engineer should consider whether the low-cardinality column is correlated with the clustering key or consider a different clustering strategy, such as clustering on an expression that combines both columns if that aligns with query patterns.

Answer

The clustering key must be defined on all columns used in WHERE clauses to achieve any performance improvement.

Answer

The automatic clustering service has not yet reclustered the table, so the clustering key is not effective for any query.

Answer

Clustering keys only benefit queries that use the CLUSTER BY clause in the SELECT statement.

Question 4

A data analyst runs a query that filters on a DATE column and returns a small number of rows from a large table. The Query Profile shows a TableScan operator with a high percentage of partitions scanned. The analyst wants to reduce the number of partitions scanned without changing the query result. What should the analyst do?

Accepted Answer

Add a cluster key on the DATE column to improve partition pruning.. Partition pruning relies on the natural ordering of data within micro-partitions. When a table is not clustered on the filter column, the optimizer may scan many partitions because matching rows are spread across them. Adding a cluster key on the DATE column reorganizes the data so that each micro-partition contains a narrow range of dates, enabling effective pruning. This reduces I/O and improves performance without altering the query semantics. Other options either add resources or change the query pattern but do not directly reduce partitions scanned.

Answer

Create a materialized view that pre-aggregates the data by DATE.

Answer

Use the RESULT_SCAN function to retrieve cached results from a previous similar query.

Answer

Increase the warehouse size to enable more parallel scans of the table.

Question 5

A data engineer is investigating a slow-running query. The Query Profile shows a high percentage of time spent in the TableScan operator and a large number of partitions scanned. The engineer wants to reduce the number of partitions scanned by using pruning. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Apply filters early in the query and avoid wrapping filter columns in functions.. Partition pruning is driven by filters that the optimizer can use against micro-partition metadata. Filtering on clustering key columns and avoiding functions on filter columns allow Snowflake to skip irrelevant micro-partitions, directly reducing the number of partitions scanned.

Answer

Increase the size of the virtual warehouse to reduce the number of partitions scanned.

Answer

Use the SEARCH function in the WHERE clause for all string filters.

Answer

Convert the table to a transient table to enable automatic pruning.

Question 6

A data engineer notices that a query performing a large aggregation is spilling to remote disk. The warehouse is a 2XL multi-cluster warehouse with maximum clusters set to 4. The engineer wants to reduce spilling and improve performance without increasing the warehouse size. Which action should the engineer take?

Accepted Answer

Rewrite the query to use a smaller aggregation or break it into stages.. Remote disk spilling occurs when the aggregation operation cannot fit its intermediate results in memory. The most direct way to reduce spilling without increasing warehouse size is to reduce the memory demand of the query itself. Rewriting the query to perform smaller aggregations or breaking it into stages can lower the peak memory usage, allowing the operation to complete within the available memory. This addresses the underlying issue rather than trying to work around it with additional resources.

Answer

Increase the maximum cluster count to 8.

Answer

Enable the query acceleration service for the warehouse.

Answer

Add a clustering key on the group by columns.

Question 7

A data engineer runs a query that joins a large fact table to a small dimension table. The Query Profile shows a Cartesian join with massive intermediate row counts. The join condition in the SQL is `ON fact.dim_id = dim.id`. The dimension table has a primary key on `id` and the fact table has a foreign key referencing it, but neither constraint is enforced. Which action will most reliably eliminate the Cartesian join and produce the expected result?

Accepted Answer

Ensure the join predicate is actually included in the query and that the dimension table's `id` column is not wrapped in a function or cast that prevents hash-join matching.. A Cartesian join in the Query Profile almost always indicates the optimizer could not identify an equi-join predicate. Common causes include a missing or mistyped join condition, or a function or cast applied to the join column on one side, which prevents hash-join matching. Confirming the predicate exists and that both columns are directly comparable restores the intended hash join. Clustering, natural joins, and warehouse resizing do not fix join semantics.

Answer

Increase the size of the virtual warehouse to provide more memory for the join operation.

Answer

Rewrite the join to use `NATURAL JOIN` so Snowflake automatically infers the join key from matching column names.

Answer

Add a `CLUSTER BY` clause on the fact table's `dim_id` column to improve join locality.

Question 8

A data engineer is building a transformation pipeline that processes semi-structured JSON data. The pipeline needs to extract values from nested objects and arrays and output a relational table. The engineer wants to minimize manual coding and ensure the transformation is maintainable. Which Snowflake feature should the engineer use?

Accepted Answer

The FLATTEN function with LATERAL joins to explode arrays and extract nested fields.. FLATTEN is designed to transform semi-structured data by expanding arrays and objects into rows. When combined with LATERAL, it can be applied to each row of a base table, producing a relational output that includes the exploded elements. This approach handles nested structures and is more maintainable than manual string parsing or repeated path expressions. PARSE_JSON only parses text into VARIANT, while OBJECT_CONSTRUCT and TRY_CAST serve different purposes. FLATTEN is the correct feature for relational transformation of JSON.

Answer

The PARSE_JSON function to convert the JSON into a relational table automatically.

Answer

The OBJECT_CONSTRUCT function to build a relational schema from the JSON keys.

Answer

The TRY_CAST function to coerce the entire JSON column into a relational table.

Question 9

A data analyst runs a query that joins a large fact table with a small dimension table. The query is slow, and the Query Profile shows a lot of data movement across the warehouse. Which Snowflake feature is designed to improve performance by automatically broadcasting small tables to all nodes in the warehouse?

Accepted Answer

Broadcast Join. The optimizer may choose a broadcast join when one side of the join is small, sending that table to all nodes to avoid shuffling the large table. This reduces data movement and speeds up the join. The Query Profile would indicate a Broadcast operation. Other features like Result Caching, Automatic Clustering, and Search Optimization Service address different performance aspects and do not directly control join data movement.

Answer

Result Caching

Answer

Search Optimization Service

Answer

Automatic Clustering

Question 10

A data engineer needs to transform semi-structured data stored in a VARIANT column. The engineer wants to extract a scalar value from a JSON object and use it in a relational query. Which Snowflake feature should the engineer use?

Accepted Answer

Use the colon operator (:) to access the value by key, for example variant_column:key_name.. The colon operator is the standard way to access a scalar value within a VARIANT column in Snowflake. It allows direct key-based access without the need for additional parsing or row expansion, making it ideal for extracting a single value for relational queries.

Answer

Use the TO_VARCHAR function to cast the entire VARIANT to a string and then use a JSON parser.

Answer

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

Answer

Use the PARSE_JSON function to convert the VARIANT to a string and then use string functions.

Question 11

A data engineer needs to transform a JSON column stored in a VARIANT type into a relational table. The JSON contains a top-level array of objects, each with keys `id`, `name`, and `tags`, where `tags` is itself an array of strings. The engineer wants each object to become a row, with the `tags` array flattened into a separate column containing one tag per row. Which combination of Snowflake functions will produce one row per tag while preserving `id` and `name`?

Accepted Answer

`LATERAL FLATTEN(input => json_col:tags)` combined with `json_col:id::INT` and `json_col:name::STRING` in the SELECT list.. To explode a nested array within a VARIANT column, `LATERAL FLATTEN` is the correct tool. It takes an array as input and returns one row per element, while the lateral join keeps the original row's other columns accessible. Referencing `json_col:id` and `json_col:name` in the SELECT list preserves those attributes. Other functions either treat the array as a string, operate on object keys, or flatten the wrong level of the JSON structure, so they do not produce the required one-row-per-tag output.

Answer

`ARRAY_TO_STRING(json_col:tags, ',')` followed by `SPLIT_TO_TABLE` on the resulting string.

Answer

`OBJECT_KEYS(json_col)` to extract the array elements, then `GET` to access each tag.

Answer

`PARSE_JSON` on the VARIANT column followed by `FLATTEN` on the entire JSON object.

Question 12

A data engineer runs a query that performs a large aggregation over a table with billions of rows. The Query Profile shows that the Aggregate operator is spilling to local disk. The engineer wants to eliminate the spilling and improve performance. Which action is most likely to achieve this?

Accepted Answer

Increase the warehouse size to provide more memory for the aggregation.. Spilling to local disk indicates that the aggregation operator's memory footprint exceeds the available memory on the warehouse. Increasing the warehouse size provides more memory per node, allowing the aggregation to complete in memory. This is the most direct solution because it addresses the resource constraint. Clustering, query rewriting, and result caching do not increase memory and therefore do not resolve the spilling condition.

Answer

Enable the USE_CACHED_RESULT parameter to reuse previous aggregation results.

Answer

Add a cluster key on the group-by columns to reduce the number of groups.

Answer

Rewrite the query to use a window function instead of a GROUP BY aggregation.

Question 13

A data engineer is tuning a query that joins a 900 million row fact table to a 2 million row dimension table. The dimension table is fully contained in the fact table's join-key range, and the join key is not the clustering key of either table. The engineer wants to eliminate the shuffle of the fact table across warehouse nodes. Which approach best achieves this?

Accepted Answer

Broadcast the 2 million row dimension table so each node joins its local fact table partitions.. The goal is to avoid moving the large relation. Broadcasting the small dimension table replicates only 2 million rows to each node, while every node keeps its local slice of the fact table and performs the join in place. This is the classic broadcast join pattern and is what the optimizer chooses automatically when one side is small. Clustering, warehouse resizing, and broadcasting the large table all fail to remove the fact table shuffle.

Answer

Force the optimizer to broadcast the fact table to every node so each node joins locally.

Answer

Increase the warehouse size so the extra nodes provide enough memory to hold both tables without spilling.

Answer

Add a clustering key on the fact table's join column so the optimizer can use partition pruning during the join.

Question 14

A data analyst runs a dashboard query that aggregates sales by region for the current month. The query has run successfully many times today, but the analyst notices it is returning results in under a second even though the underlying table is very large. The analyst has not changed the query or the data. Which Snowflake feature is most likely responsible for the fast response?

Accepted Answer

Result caching has stored the query result, and the query is being served from the persisted result cache.. Result caching in Snowflake stores the complete output of a query for 24 hours. When the same query is re-run and the underlying data has not changed, Snowflake returns the cached result directly without using a warehouse. This yields sub-second response times even for large aggregations. Clustering, warehouse size, and local disk cache all affect query execution speed but do not store final results, so they cannot explain the instant response.

Answer

The query is using the local disk cache on the virtual warehouse.

Answer

The table is clustered on the region column, allowing partition pruning.

Answer

The virtual warehouse is configured with a large multi-cluster size.

Question 15

A data engineer is writing a transformation that reads a VARIANT column containing nested arrays of objects and needs to produce one output row per array element. Which TWO Snowflake features or functions should be used to accomplish this? (Choose two.)

Accepted Answer

Use the VALUE column returned by FLATTEN to access each element's contents.. FLATTEN is the built-in table function that turns a VARIANT array into rows, and when applied laterally it is evaluated per input row. The VALUE column of the FLATTEN output holds each array element, which for an array of objects is itself a VARIANT object that can be dereferenced with the colon path syntax. Together they transform nested arrays into a relational shape without manual iteration.

Answer

Use OBJECT_CONSTRUCT to iterate over the array and emit one row per element.

Answer

Use PARSE_JSON to convert the array into a relational table with one column per object attribute.

Answer

Use GET_PATH to extract the array and then join it to a sequence generator to produce rows.