COF-C03 Practice Test 13 — 15 Questions

Question 1

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 2

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 3

A provider has shared a database with a consumer. The consumer reports that they can see the shared database but cannot query any tables because they get an error about insufficient privileges. The provider used the following command to create the share: CREATE SHARE MY_SHARE; then added a database and a table to the share. The provider also granted USAGE on the database and SELECT on the table to a role named SHARE_ROLE, and then granted SHARE_ROLE to MY_SHARE. What is the most likely cause of the consumer's issue?

Accepted Answer

The provider must also grant USAGE on the schema containing the table to SHARE_ROLE.. To share a table, the provider must grant USAGE on the database, USAGE on the schema, and SELECT on the table to the share. The scenario omits the schema usage grant, which is required for the consumer to access objects within that schema. Without it, queries will fail with insufficient privileges. Adding the schema usage grant resolves the issue.

Answer

The consumer must create a database from the share before they can query the tables.

Answer

The share was not granted to the consumer account using ALTER SHARE ... ADD ACCOUNT.

Answer

The provider must use a secure view instead of a table to share data.

Question 4

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.

Question 5

A provider is preparing to share a secure view that references a table in another database. The provider must ensure the consumer can query the view but cannot access the base table. Which two actions must the provider take? (Choose two.)

Accepted Answer

Grant SELECT on the secure view to the share.. To share a secure view that references a table in another database, the provider must grant USAGE on the database and schema containing the view, and SELECT on the view, to the share. These two grants allow the consumer to query the view. The base table remains private because the view executes with the owner's rights, and no privileges on the base table are granted.

Answer

Grant SELECT on the base table to the share.

Answer

Create a new role that has access to the base table and grant that role to the share.

Answer

Grant USAGE on the database and schema containing the base table to the share.

Question 6

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?

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 7

A data provider at ACME Corp has created a share named REGIONAL_SALES_SHARE and added a secure view on the SALES table. The provider now wants to grant a specific consumer account, named CONSUMER_ACCT, access to this share. Which command should the provider use to accomplish this?

Accepted Answer

ALTER SHARE REGIONAL_SALES_SHARE ADD ACCOUNT = CONSUMER_ACCT;. To share data with a specific consumer account, the provider must add that account to the share using ALTER SHARE ... ADD ACCOUNT. This grants the consumer account the ability to create a database from the share. The other commands either have incorrect syntax or serve different purposes, such as granting privileges within an account rather than authorizing an external account.

Answer

GRANT IMPORTED PRIVILEGES ON DATABASE REGIONAL_SALES_SHARE TO ACCOUNT CONSUMER_ACCT;

Answer

GRANT USAGE ON SHARE REGIONAL_SALES_SHARE TO ACCOUNT CONSUMER_ACCT;

Answer

CREATE SHARE REGIONAL_SALES_SHARE GRANT TO ACCOUNT CONSUMER_ACCT;

Question 8

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 9

A data engineer is troubleshooting a COPY INTO command that loads JSON files from a named external stage and is seeing unexpected NULL values in several VARIANT columns. Which TWO actions should the engineer take to diagnose how the JSON is being parsed? (Choose two.)

Accepted Answer

Run COPY INTO with VALIDATION_MODE = 'RETURN_ERRORS' to surface parsing problems before committing data.. Diagnosing unexpected NULLs in VARIANT columns requires visibility into how the load parsed the JSON. VALIDATION_MODE = 'RETURN_ERRORS' validates files and reports parsing errors without loading, while COPY_HISTORY provides per-statement load status and error details. Together they reveal whether the file format definition matches the actual JSON structure. Aborting the load, changing the format type, or stripping the outer array do not expose the parsing problem.

Answer

Enable STRIP_OUTER_ARRAY = TRUE on the file format to flatten the JSON into columns.

Answer

Set ON_ERROR = 'ABORT_STATEMENT' and rerun the load to force Snowflake to reveal the malformed records.

Answer

Change the file format TYPE to CSV so Snowflake parses each line as a flat record and exposes the raw text.

Question 10

A provider wants to share a database with a consumer account. After creating the share and granting the necessary privileges, which command must the provider run to make the share available to the consumer account?

Accepted Answer

ALTER SHARE share_name ADD ACCOUNT = consumer_account_locator;. The provider makes a share available to a consumer by adding the consumer's account to the share with ALTER SHARE ... ADD ACCOUNT. This is the standard account-to-account sharing step. Other commands either invent syntax, reverse the direction, or misunderstand how shares are granted. Once the account is added, the consumer can create a database from the share.

Answer

ALTER ACCOUNT consumer_account ADD SHARE share_name;

Answer

CREATE SHARE share_name WITH ACCOUNT = consumer_account;

Answer

GRANT USAGE ON SHARE share_name TO ACCOUNT consumer_account;

Question 11

A provider account named PROVIDER_ACCT has created a share named PARTNER_SHARE and granted SELECT on a secure view to it. The provider now needs to make this share available to a specific consumer account named CONSUMER_ACCT. Which single command should the provider execute to accomplish this?

Accepted Answer

ALTER SHARE PARTNER_SHARE ADD ACCOUNT = CONSUMER_ACCT;. Sharing to a specific consumer requires the provider to attach the consumer's account to the share. The ALTER SHARE ... ADD ACCOUNT statement performs this binding and is executed in the provider account. Once added, the consumer sees the share as an inbound share and can create a database from it, gaining read-only access to the granted objects.

Answer

GRANT USAGE ON SHARE PARTNER_SHARE TO ACCOUNT CONSUMER_ACCT;

Answer

CREATE SHARE PARTNER_SHARE FOR ACCOUNT = CONSUMER_ACCT;

Answer

ALTER ACCOUNT CONSUMER_ACCT ADD SHARE PARTNER_SHARE;

Question 12

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 13

A data provider at a healthcare analytics company wants to share live patient-readmission metrics with a partner hospital. The partner must query the data with low latency and the provider must retain full ownership and control of the underlying tables. The provider creates a share and grants USAGE on a secure view to the share. Which action must the provider perform next so the partner account can mount and query the shared data?

Accepted Answer

Add the partner's account to the share using ALTER SHARE ... ADD ACCOUNTS.. For a direct share, the provider grants object privileges to the share, then associates the consumer account with the share. The consumer then creates a database from the share to query it. Adding the account is mandatory because privileges alone do not cross account boundaries; the share must be explicitly bound to the consumer's account before it can be mounted.

Answer

Create a database from the share in the provider account and grant USAGE to the partner.

Answer

Publish the share as a listing on the Snowflake Marketplace.

Answer

Grant the CONSUME privilege on the share to the partner's account.

Question 14

A data engineer observes that a transformation query run on a MEDIUM warehouse spends most of its time in the 'Remote Disk Spilling' phase of the Query Profile. The query joins two large tables and performs a large sort. Memory usage shows the warehouse consistently near its limit. The engineer wants the most direct fix that addresses the root cause. Which action should be taken?

Accepted Answer

Increase the size of the virtual warehouse so more memory is available per node and spilling is avoided.. Remote disk spilling indicates that the join and sort working set exceeded warehouse memory and intermediate data was written to remote storage. The most direct remedy is to give the operation more memory by scaling up the virtual warehouse, which increases the memory available per node. Query rewriting, query acceleration, and clustering all fail to address the memory shortfall that the Query Profile is reporting.

Answer

Enable the query acceleration service on the warehouse to offload the sort to shared compute.

Answer

Rewrite the query to use a smaller data type for the join keys so less memory is consumed per row.

Answer

Add a clustering key to the larger table so fewer micro-partitions are scanned during the join.

Question 15

A data engineer is analyzing a slow query that uses a window function with a PARTITION BY clause on a high-cardinality column. The Query Profile shows that the window function is causing significant data shuffling. The engineer wants to reduce the shuffling. Which approach is most likely to improve performance?

Accepted Answer

Pre-aggregate the data in a subquery or CTE to reduce the number of rows before applying the window function.. The shuffling is caused by the need to co-locate rows with the same partition key. If the dataset is reduced before the window function, the shuffle volume decreases. Pre-aggregating in a subquery or CTE is an effective way to reduce the number of rows that must be redistributed. This is especially beneficial when the window function does not need row-level detail. The other options either do not affect shuffling or introduce additional overhead.

Answer

Add an ORDER BY clause inside the window function to sort the data before partitioning.

Answer

Replace the window function with a self-join on the partitioning column.

Answer

Use a QUALIFY clause to filter the results after the window function is computed.