COF-C03 Performance Optimization, Querying, and Transformation — Timed 15 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.