COF-C03 Practice Test 10 — 10 Questions

Question 1

A Snowflake administrator needs to grant USAGE on a virtual warehouse named ANALYTICS_WH to a role named ANALYST_ROLE. Which SQL command accomplishes this?

Accepted Answer

GRANT USAGE ON WAREHOUSE ANALYTICS_WH TO ROLE ANALYST_ROLE;. To allow a role to use a virtual warehouse, you must grant the USAGE privilege on that warehouse to the role. The correct syntax uses GRANT USAGE ON WAREHOUSE TO ROLE . This enables the role to start the warehouse and execute queries. Other privileges like OPERATE or MODIFY are for administrative control, not query usage. The database and ALTER WAREHOUSE approaches are incorrect object types or commands.

Answer

ALTER WAREHOUSE ANALYTICS_WH SET ROLE ANALYST_ROLE;

Answer

GRANT USAGE ON DATABASE ANALYTICS_WH TO ROLE ANALYST_ROLE;

Answer

GRANT OPERATE ON WAREHOUSE ANALYTICS_WH TO ROLE ANALYST_ROLE;

Question 2

A data engineer is loading a batch of semi-structured JSON files from an external stage into a VARIANT column. They want the COPY INTO command to skip any file that contains malformed JSON without failing the entire load. Which COPY INTO option should they configure?

Accepted Answer

ON_ERROR = 'SKIP_FILE'. The ON_ERROR = 'SKIP_FILE' option tells Snowflake to skip a file entirely when any error occurs during loading, which includes malformed JSON. This allows the COPY INTO operation to continue processing other valid files without failing the entire statement. The other options either abort the load, skip only rows, or are not valid Snowflake syntax.

Answer

ON_ERROR = 'REPLACE'

Answer

ON_ERROR = 'CONTINUE'

Answer

ON_ERROR = 'ABORT_STATEMENT'

Question 3

A data engineer is loading a 5 TB compressed CSV file into a Snowflake table using the COPY INTO command. The file is stored in an external stage pointing to an Amazon S3 bucket. The engineer notices that the load is slower than expected and wants to improve performance. Which of the following actions is most likely to improve the load performance?

Accepted Answer

Split the large CSV file into multiple smaller files and load them in parallel.. Snowflake's COPY INTO loads data in parallel by processing multiple files concurrently. When a single large file is loaded, it is handled by one thread, limiting throughput. Splitting the file into multiple smaller files enables Snowflake to use multiple threads and compute resources, thereby improving load performance. Other options do not directly address the parallelism limitation.

Answer

Use a larger file format that supports compression, such as Parquet.

Answer

Enable the VALIDATION_MODE parameter to speed up data validation.

Answer

Increase the size of the virtual warehouse used for the load.

Question 4

A data architect needs to ensure that a specific set of resource-intensive queries always have dedicated compute resources and do not compete with other workloads. Which Snowflake feature should be used?

Accepted Answer

Separate virtual warehouses. To guarantee dedicated compute for specific queries and avoid competition, the architect should provision a separate virtual warehouse. Snowflake's architecture separates compute from storage, allowing multiple independent warehouses to access the same data. By assigning the resource-intensive queries to their own warehouse, the architect ensures they have exclusive use of that compute cluster, preventing contention with other workloads.

Answer

Resource monitors

Answer

Multi-cluster warehouses

Answer

Query acceleration service

Question 5

A Snowflake provider wants to share a secure view with a consumer. The view is defined on a table in a different database than the one containing the view, and the provider must ensure the consumer cannot access the underlying base table directly. Which action should the provider take?

Accepted Answer

Grant USAGE on the database and schema containing the secure view, and SELECT on the secure view, to the share.. When sharing a secure view that references objects in other databases, the provider must grant USAGE on the database and schema that contain the view, and SELECT on the view, to the share. The base table's database is not shared, and the secure view's owner's rights allow access to the base table without exposing it to the consumer.

Answer

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

Answer

Add the base table to the share and rely on the secure view to restrict access.

Answer

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

Question 6

A user runs a query that was executed by another user 10 minutes ago, and the underlying data has not changed. The query returns results instantly without using a virtual warehouse. Which Snowflake feature explains this behavior?

Accepted Answer

The query result cache stored in the Cloud Services layer.. The query result cache is a Cloud Services feature that persists query results for 24 hours. When an identical query is re-executed and the underlying data remains unchanged, Snowflake serves the cached result directly, bypassing warehouse computation and delivering sub-second response times.

Answer

The metadata cache in the Cloud Services layer.

Answer

The materialized view automatically created for the query.

Answer

The local disk cache on the virtual warehouse.

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 user runs a query that filters on a column with a very high cardinality, such as a timestamp with millisecond precision. The table is extremely large and is not clustered. What is the most likely impact on query performance?

Accepted Answer

The query will scan all micro-partitions because pruning is ineffective for high-cardinality columns.. For a large, unclustered table, filtering on a high-cardinality column like a millisecond timestamp leads to ineffective micro-partition pruning. Snowflake's pruning relies on min/max metadata, and high-cardinality columns have wide, overlapping ranges across micro-partitions. As a result, the query scans most or all micro-partitions, causing poor performance. Clustering or search optimization could help, but neither is present here.

Answer

The query will use the search optimization service to quickly find matching rows.

Answer

The query will benefit from the query result cache because the filter is highly selective.

Answer

Snowflake will automatically create a clustering key on the high-cardinality column to improve pruning.

Question 9

A Snowflake account has a virtual warehouse that is configured with AUTO_SUSPEND = 60 seconds and AUTO_RESUME = TRUE. A user runs a query that takes 5 minutes to complete. After the query finishes, the warehouse remains idle for 2 minutes and then suspends. During the idle period, what charges apply?

Accepted Answer

Charges apply for the entire 2-minute idle period because the warehouse is still running.. Snowflake charges for a virtual warehouse based on the time it is running, regardless of whether it is actively processing queries. During the idle period before auto-suspension, the warehouse continues to consume credits. The AUTO_SUSPEND setting controls how long the warehouse remains running after the last query, but any time it is running, charges apply. Therefore, the entire idle period incurs charges until the warehouse suspends.

Answer

No charges apply during the idle period because the warehouse is not processing queries.

Answer

Charges apply only if the warehouse is resumed during the idle period.

Answer

Charges apply only for the first 60 seconds of idle time, as per the AUTO_SUSPEND setting.

Question 10

A data engineer is loading a 4 GB CSV file from an external stage into a Snowflake table using COPY INTO. The file is compressed with gzip and has a header row. The engineer notices the load is taking longer than expected. Which action is MOST likely to improve performance?

Accepted Answer

Split the file into multiple smaller files and load them in parallel.. Splitting large files into multiple smaller files enables parallel loading, which is a key performance optimization for COPY INTO. Snowflake can distribute the load across multiple threads when multiple files are present, reducing overall load time.

Answer

Enable the PURGE option to remove the file after loading.

Answer

Increase the warehouse size to a larger size.

Answer

Use a larger file format option to skip the header.