COF-C03 Performance Optimization, Querying, and Transformation — 25 Questions

Question 1

Refer to the exhibit. Which SQL snippet correctly extracts the 'total' value of the second order in the JSON object stored in a column named 'src'?

Accepted Answer

src:orders[1]:total. To access nested data in a VARIANT column, Snowflake uses colon notation for object keys and bracket notation for array indices. Because Snowflake arrays are zero-indexed, the second element in the 'orders' array is accessed using [1]. The final value is then retrieved by specifying the '.total' key.

Answer

src:orders[2]:total

Answer

src:orders.1.total

Answer

GET_PATH(src, 'orders/1/total')

Question 2

Which TWO of the following statements are true regarding Snowflake's caching mechanisms? (Choose two)

Accepted Answer

The local disk cache persists across warehouse sessions.. Snowflake utilizes a multi-layered caching architecture to optimize performance and minimize costs. The Result Cache stores the output of previous queries, allowing for near-instant retrieval if the query is re-run without changes. The Local Disk Cache caches data retrieved from remote storage during query execution, which significantly speeds up subsequent queries that require access to the same data, provided the warehouse remains active. Understanding these layers is fundamental to mastering performance tuning.

Answer

The result cache is automatically cleared when the warehouse suspends.

Answer

Data in the local disk cache is shared across different warehouses.

Answer

Result caching only works if the table data has changed.

Question 3

How can you optimize a query that frequently joins two very large tables that are updated infrequently?

Accepted Answer

Implement clustering keys on both tables.. For large, infrequently updated tables, creating a materialized view or using a permanent table that is well-clustered is a best practice. Because the data changes rarely, the cost of maintaining the clustered structure or materialized view is low compared to the performance gains achieved during query time. This reduces the need for the engine to perform expensive full-table scans and large-scale join operations, which is the key to optimizing performance for static large-scale datasets.

Answer

Use a temporary table for the join.

Answer

Move the data to an external stage.

Answer

Always use a cross join.

Question 4

Which THREE actions can help reduce the compute cost of a query? (Choose three)

Accepted Answer

Use the result cache by running the same query repeatedly.. Reducing compute cost involves two primary strategies: optimizing the query to run faster and utilizing the most efficient resources. Reducing the volume of data scanned through pruning, caching query results, and ensuring the virtual warehouse size is aligned with the workload complexity are all effective ways to lower costs. By spending less time in 'running' status, the virtual warehouse consumes fewer credits, directly impacting the overall financial efficiency of the Snowflake environment.

Answer

Increase the virtual warehouse size significantly.

Answer

Use larger files for bulk data loads.

Question 5

What is the main function of the Cloud Services layer in Snowflake regarding query performance?

Accepted Answer

It caches the result sets of queries.. The Cloud Services layer is the 'brain' of Snowflake. It handles query parsing, optimization, and security. Its primary performance function is to generate the most efficient execution plan for a given SQL query. By analyzing metadata, the Cloud Services layer decides the best way to access data, ensuring that the Virtual Warehouse layer only performs the necessary operations, thus optimizing overall query performance.

Answer

It executes the actual heavy data processing.

Answer

It stores the raw table data files.

Answer

It performs automatic indexing of columns.

Question 6

A data engineer notices that a daily aggregation query is performing poorly even though the virtual warehouse is sized correctly. The query filters on a 'transaction_date' column, but the data is loaded into the table in a random order based on 'customer_id'. Which Snowflake feature would most likely resolve this performance issue by optimizing the physical layout of the data?

Accepted Answer

Clustering Keys. Effective pruning is vital for Snowflake performance as it reduces the volume of data scanned by the virtual warehouse. By leveraging metadata stored in micro-partitions, Snowflake determines which blocks are relevant to the query filters. This process minimizes I/O overhead and significantly lowers credit consumption, especially on multi-terabyte tables where scanning every row would be prohibitively expensive and slow for users.

Answer

Search Optimization Service

Answer

Query Acceleration Service

Answer

Materialized Views

Question 7

A large multi-cluster warehouse is configured with the Auto-scale policy. Which TWO factors will cause the warehouse to start a new cluster to handle incoming queries?

Accepted Answer

The number of queries in the queue exceeds a specific threshold.. Multi-cluster warehouses provide horizontal scaling to handle high concurrency. The decision to start a new cluster is based on the demand for compute resources and the current load on the existing clusters. Understanding these triggers helps administrators manage costs while ensuring that users do not experience significant queuing during peak usage.

Answer

A single query requires more memory than is available in one cluster.

Answer

The data being queried is not present in the local SSD cache of the first cluster.

Answer

The user executing the query has a higher priority level than other users.

Question 8

Which THREE conditions must be met for a query to successfully use the Result Cache?

Accepted Answer

The underlying data in the tables must not have changed.. The Result Cache is a powerful feature that returns query results instantly without using any virtual warehouse credits. However, it is only utilized if the query and the underlying data remain identical. Understanding these requirements is key for developers to write efficient code that maximizes cache hits and reduces costs.

Answer

The query must be executed by the same user who originally ran it.

Answer

The query must be executed using the same virtual warehouse size.

Question 9

A developer needs to load data from an S3 bucket and perform a transformation (e.g., column renaming and data type casting) during the load. Which TWO methods can be used to accomplish this?

Accepted Answer

Using a SELECT statement within a COPY INTO command.. Snowflake allows for basic transformations during the data loading process, which can reduce the need for a separate staging table. This 'Load-Time Transformation' is efficient because it happens as data is being written to micro-partitions, saving both time and compute credits compared to a multi-step process.

Answer

Creating a CSV file format with the 'TRANSFORM = TRUE' option.

Answer

Using a Snowflake Task to run an UPDATE statement after the load.

Answer

Defining the transformation logic inside the External Stage definition.

Question 10

A data engineer notices that queries against a massive partitioned table are slow despite clustering. The table is clustered by a high-cardinality column. Which strategy should be applied to improve performance?

Accepted Answer

Change the clustering key to a column with lower cardinality that is frequently used in WHERE clauses.. High-cardinality columns are poor candidates for clustering keys because they create too many micro-partitions with little data overlap, preventing effective pruning. Switching to a lower-cardinality column or a column frequently used in filter predicates significantly reduces the number of partitions scanned by the query engine. Proper clustering is essential for minimizing I/O and reducing costs in Snowflake, as it directly impacts the efficiency of the partition pruning mechanism during query execution.

Answer

Increase the warehouse size to utilize more compute resources.

Answer

Rebuild the table using the CLUSTER BY clause on the existing high-cardinality column.

Answer

Enable search optimization for the high-cardinality column.

Question 11

Which TWO of the following statements are true regarding the use of Result Caching in Snowflake?

Accepted Answer

The result cache is automatically invalidated if the underlying table data changes.. Result caching is a key performance feature that stores query results for 24 hours. When a subsequent query matches exactly, Snowflake retrieves the result without utilizing compute resources. Understanding the cache lifecycle is vital for cost management and ensuring users receive current data. It is important to know that changes to the underlying data or the expiration of the cache timer will force the system to execute the query against the warehouse again.

Answer

Result caching is available for all query types, including DDL and DML operations.

Answer

Queries using non-deterministic functions like CURRENT_TIMESTAMP() will always use the result cache.

Answer

Result cache requires a running virtual warehouse to retrieve data.

Question 12

An organization has a dashboard that runs every minute to display the latest data. Which Snowflake feature is most appropriate to optimize performance while minimizing costs?

Accepted Answer

Implement a Materialized View to pre-compute the dashboard results.. For dashboards requiring frequent, near-real-time updates, utilizing Materialized Views or Result Caching is critical. Materialized views automatically maintain pre-computed results, making them ideal for high-frequency dashboard queries where the underlying data does not change in every single millisecond. This reduces compute load by avoiding redundant calculations. Choosing the right optimization strategy ensures that the dashboard remains responsive while preventing excessive credit consumption from constant re-calculation of the same analytical logic.

Answer

Use a larger warehouse to ensure the query finishes in under a second.

Answer

Create a task that drops and recreates the table every minute.

Answer

Use an external table with auto-refresh enabled.

Question 13

A data engineer runs a query that joins a large fact table to a small dimension table. The query profile shows a Broadcast Join, and the engineer wants to force a different join strategy that avoids replicating the large table across all nodes. Which session parameter should be set to change the join strategy?

Accepted Answer

JOIN_POLICY = 'NONE'. JOIN_POLICY controls whether the optimizer may use a broadcast join. Setting it to 'NONE' prevents broadcast joins, which is useful when the build side is large or when network overhead from broadcasting is undesirable. The other options are either invalid parameter names or invalid values that Snowflake does not recognize.

Answer

DISABLE_JOIN_BROADCAST = TRUE

Answer

ENABLE_BROADCAST_JOIN = FALSE

Answer

JOIN_POLICY = 'HASH'

Question 14

A data engineer runs a query that joins a 2 TB fact table to a small 5 MB dimension table. The Query Profile shows a Cartesian join with a row count far larger than expected, and the join condition was accidentally omitted in the SQL. Which join type should the engineer use to ensure the small dimension table is broadcast to every node instead of shuffling both large tables across the network?

Accepted Answer

Rewrite the query with an explicit equality join predicate between the fact and dimension tables.. A Cartesian join in the Query Profile is the signature of a missing or non-equality join predicate. Supplying the proper equality condition between the fact and dimension tables lets the optimizer select an appropriate join method and typically broadcast the tiny dimension, avoiding a full shuffle of both relations. Correcting the predicate also restores the intended result set.

Answer

Add a CLUSTER BY clause on the fact table's join key to force a broadcast join.

Answer

Use a broadcast join by relying on the optimizer's automatic selection for a small table.

Answer

Increase the virtual warehouse size to a larger multi-cluster warehouse to handle the Cartesian join.

Question 15

A data engineer runs a query that joins a large fact table to a small dimension table. The Query Profile shows a Broadcast Join, and the query takes much longer than expected. The engineer wants to force a more efficient join strategy by giving the optimizer better cardinality information. Which action should the engineer take?

Accepted Answer

Collect statistics on the join key of the large fact table.. The optimizer relies on statistics to estimate cardinality and choose join strategies. When statistics are missing or stale, it may default to a broadcast join even when a hash join would be more efficient. Collecting statistics on the join key of the large table gives the optimizer the data it needs to make a better decision, directly addressing the performance issue.

Answer

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

Answer

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

Answer

Disable the Broadcast Join by setting the session parameter ENABLE_BROADCAST_JOIN to FALSE.

Question 16

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?

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 17

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 18

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 19

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 20

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.