COF-C03 Hard Questions — 25 Difficult Questions

Question 1

Refer to the exhibit. A user with the role 'ANALYST_ROLE' cannot see tables inside the 'sales_db.public' schema despite having 'USAGE' on the database. What is the most likely reason for this access issue?

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

Accepted Answer

The user lacks the 'USAGE' privilege on the schema.. In Snowflake, the USAGE privilege on a database is insufficient to view objects within schemas; the user must also be granted USAGE on the schema and SELECT on the specific tables or views. This multi-layered privilege requirement is a core component of Snowflake's security model, preventing accidental data exposure by requiring explicit grants at every level of the object hierarchy, from the database down to the individual object.

Answer

The user needs the 'OWNERSHIP' privilege on the schema.

Answer

The database is not set as the default database for the user.

Answer

The user is missing the 'MANAGE GRANTS' privilege.

Question 2

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 3

Refer to the exhibit. A user executes a COPY INTO command and then queries the COPY_HISTORY. Based on the output shown, what most likely happened during the load and what is the current state of the data in the SALES_DATA table?

Accepted Answer

Exactly 500 rows were successfully loaded into the table, and the 2 errors were ignored.. The COPY_HISTORY result showing 'PARTIALLY_LOADED' with specific row errors indicates that the ON_ERROR parameter was likely set to 'CONTINUE'. In this mode, Snowflake skips rows that contain errors but proceeds to load all valid records into the table. This results in a successful transaction for the 500 valid rows, while the 2 erroneous rows are logged but not ingested.

Answer

The load failed completely, and no rows were added to the table because of the 2 errors.

Answer

The 2 errors represent duplicate files that were already loaded in a previous session.

Answer

The entire file was skipped because the number of errors exceeded the default threshold.

Question 4

Which approach is most effective for optimizing an aggregation query that performs a 'GROUP BY' on a high-cardinality column?

Accepted Answer

Use a materialized view to pre-aggregate the data.. High-cardinality columns can cause memory bottlenecks during aggregation because the distinct values cannot fit into the memory of a single node, leading to disk spilling. By using techniques like pre-aggregation or creating a materialized view that groups the data by the high-cardinality column, you reduce the workload. These strategies move the compute-heavy grouping operation to a more efficient time or structure, thereby preventing memory exhaustion and significantly improving the performance of the aggregation query.

Answer

Increase the warehouse size to add more nodes.

Answer

Reduce the number of columns in the SELECT clause.

Answer

Change the table type to transient.

Question 5

Refer to the exhibit. If a user with the 'ANALYST' role queries a table protected by this policy, what will they see?

Accepted Answer

The string '***@***.com'.. The MASKING POLICY uses the CURRENT_ROLE() function to determine visibility. Since the 'ANALYST' role is not included in the 'IN' clause of the CASE statement, the query will evaluate to the ELSE condition. Consequently, the sensitive email data will be replaced by the literal string '***@***.com'. This demonstrates how attribute-based access control works in Snowflake, ensuring that sensitive data exposure is restricted based on the user's active role.

Answer

The actual email addresses.

Answer

An error message indicating insufficient privileges.

Answer

NULL values.

Question 6

A provider is preparing to share data with a consumer via a direct share. The provider wants to ensure that the consumer can access the data but cannot see the underlying table structure or any other objects in the database. Which two actions should the provider take? (Choose two.)

Accepted Answer

Ensure the share contains only the secure view and no other objects.. To share data while hiding the underlying structure, the provider should create a secure view and add it to the share, and ensure the share contains only that view. Secure views prevent consumers from seeing the base table definitions, and limiting the share to the view ensures no other objects are exposed. Other options involve direct table access or ineffective grants that do not meet the security requirements.

Answer

Grant USAGE on the database to the consumer's role.

Answer

Create a database role and grant it to the consumer.

Answer

Add the table directly to the share and grant SELECT on the table to the consumer.

Question 7

A developer is using a Stream on a table to capture changes. If the developer executes a DML statement that consumes the data from the Stream within a transaction, what happens to the Stream's offset after the transaction commits?

Accepted Answer

The offset is advanced only if the transaction commits successfully.. Snowflake Streams use an offset to track the point in time from which they are reading changes. When a Stream is used as a source in a DML operation (like INSERT INTO... SELECT FROM stream), the offset is advanced only when the transaction successfully commits, ensuring 'exactly-once' processing of the change data.

Answer

The offset is advanced immediately when the SELECT is executed.

Answer

The offset must be manually updated using the ALTER STREAM command.

Answer

The Stream is dropped and must be recreated for the next batch.

Question 8

Refer to the exhibit. Given the state described in the JSON, what happens when a user executes the query?

Accepted Answer

The query returns results from the cache.. Even though the warehouse is suspended, the query will execute successfully because the Cloud Services layer can fulfill the request from the result cache. This highlights the efficiency of the Snowflake architecture, where the Cloud Services layer functions independently of the compute layer. If the query result exists in the cache and the underlying table data has not changed, Snowflake retrieves the result without starting the warehouse, saving costs.

Answer

The warehouse automatically resumes.

Answer

The query fails due to lack of compute.

Answer

The query waits for the warehouse to start.

Question 9

When considering the implementation of a clustering key on a large table, which TWO scenarios indicate that clustering will provide the most significant performance benefit?

Accepted Answer

Queries against the table typically filter on a specific dimension column.. Clustering is most effective for large tables (typically multi-terabyte) where query performance has degraded over time due to poor data grouping. It benefits queries that use selective filters on specific columns, as it allows the optimizer to skip a high percentage of micro-partitions that do not contain relevant data.

Answer

The table is frequently updated with small DML operations.

Answer

The table size is less than 50 GB and fits in the local cache.

Answer

The table is used exclusively for full table scans and exports.

Question 10

Which component is responsible for orchestrating the lifecycle of a virtual warehouse, including start and stop operations?

Accepted Answer

The Cloud Services layer. The Cloud Services layer orchestrates the life cycle of virtual warehouses. When a query is received, the Cloud Services layer determines if the warehouse is running; if not, it triggers the start process. This is part of the 'serverless' experience Snowflake provides, allowing users to interact with the platform without manually managing compute resources. This automation is vital for maintaining high availability while optimizing cost through automated suspension and resumption.

Answer

The Storage layer

Answer

The Compute layer

Answer

The Object Storage API

Question 11

A Snowflake user needs to query data stored in an external cloud storage location (e.g., Amazon S3) without loading it into Snowflake tables. They want to minimize data movement and cost. Which Snowflake feature should they use?

Accepted Answer

External Tables. External tables enable querying data in external cloud storage directly, without loading it into Snowflake. This reduces data movement and cost because the data stays in its original location. Internal stages, materialized views, and Snowpipe all involve loading or storing data within Snowflake, which does not meet the requirement.

Answer

Snowpipe

Answer

Materialized Views

Answer

Internal Stages

Question 12

Refer to the exhibit. Why were zero credits consumed for the query execution?

Accepted Answer

The query was satisfied by the Query Result Cache.. The exhibit indicates that the query utilized the Query Result Cache, as evidenced by 'cached_result': 'TRUE'. When Snowflake identifies an identical query whose results are still in the cache and the underlying data has not changed, it returns the stored result set. This bypasses the virtual warehouse compute layer entirely, resulting in zero credit consumption, which is a highly efficient way to serve repeat dashboard queries.

Answer

The warehouse WH_XS is in a suspended state.

Answer

The data was already present in the warehouse local disk cache.

Answer

The query failed during the planning phase.

Question 13

A data engineer needs to transform semi-structured data stored in a VARIANT column that contains an array of JSON objects into a relational table with one row per object. The engineer wants to use a Snowflake function that can expand the array into multiple rows. Which function should be used?

Accepted Answer

FLATTEN. FLATTEN is the correct function to expand an array of objects into multiple rows. It is typically used in the FROM clause with LATERAL, allowing each element of the array to become a separate row while preserving access to the original row's columns. The other functions either create VARIANT values or aggregate rows, not expand arrays.

Answer

PARSE_JSON

Answer

OBJECT_CONSTRUCT

Answer

ARRAY_AGG

Question 14

A data steward needs to ensure that a column containing email addresses is masked for all users except those with the role 'COMPLIANCE_OFFICER'. The masking should show a fixed string '****' for unauthorized users. Which Snowflake feature should be used?

Accepted Answer

Masking policy. A masking policy in Snowflake allows column-level security by dynamically masking data based on the user's role or other conditions. By applying a masking policy to the email column that returns '****' for all roles except 'COMPLIANCE_OFFICER', the data steward ensures that only authorized users see the actual email addresses. This is the correct feature for column-level masking.

Answer

Object tagging

Answer

Secure view

Answer

Row access policy

Question 15

Refer to the exhibit. What is the primary advantage of using the VARIANT data type in this scenario?

Accepted Answer

It enables seamless querying of semi-structured data using SQL.. The VARIANT data type in Snowflake allows for the storage of semi-structured data like JSON, Avro, or Parquet in a native format. By using VARIANT, Snowflake parses the data upon ingestion and stores it in an optimized internal representation. This enables users to query semi-structured data using standard SQL, providing a bridge between the flexibility of NoSQL and the reliability and performance of relational databases.

Answer

It forces the data into a rigid relational schema.

Answer

It converts all data to string format for storage.

Answer

It increases storage costs by requiring a fixed-width format.

Question 16

A provider wants to share a database with a consumer but must prevent the consumer from seeing the database's table and schema names in its own account. The provider also wants the consumer's queries to be isolated from the provider's own warehouse usage. Which approach satisfies both requirements?

Accepted Answer

Create a secure view that projects the data and grant only the view to the share, then let the consumer use its own warehouse.. A secure view exposes only the projected result, so the consumer never sees the underlying table or schema names. Because the consumer creates a database from the share and queries it with its own warehouse, the provider's warehouse is untouched. Together, the secure view and consumer-owned warehouse meet both the naming-concealment and usage-isolation requirements.

Answer

Use a database replication task to copy the data into the consumer's account.

Answer

Create a reader account for the consumer and grant the consumer's role access to the provider's warehouse.

Answer

Grant SELECT on each table directly to the share and let the consumer create a database from the share.

Question 17

Refer to the exhibit. What is the impact of changing the MAX_CONCURRENCY_LEVEL parameter on this warehouse?

Accepted Answer

It allows each cluster to run more queries simultaneously.. The MAX_CONCURRENCY_LEVEL parameter controls the number of concurrent queries a single cluster can handle before queuing begins. By increasing this value, you allow more queries to run in parallel on the same compute resources. However, this also divides the CPU and memory among more queries, which can lead to individual query performance degradation if the workload is compute-intensive, making it a critical tuning parameter for balancing throughput and performance.

Answer

It increases the number of clusters in the warehouse.

Answer

It scales the warehouse size (e.g., X-SMALL to SMALL).

Answer

It disables the auto-scaling capabilities of the warehouse.

Question 18

A data engineer is designing a strategy for a table that receives millions of new rows daily and is frequently queried using a filter on 'Region' and 'OrderDate'. How does Snowflake’s natural clustering architecture affect the maintenance of this table?

Accepted Answer

Snowflake will use metadata to prune micro-partitions that do not match the filter criteria.. Snowflake's micro-partitioning automatically organizes data as it is ingested, creating a 'natural' clustering based on the order of arrival. If data is naturally ordered by date, queries filtering on date will be highly efficient due to metadata-based pruning. However, as data distribution changes over time, Snowflake's background Automatic Clustering service can maintain optimal performance without manual intervention.

Answer

The engineer must manually rebuild the table daily to re-sort the data for pruning.

Answer

The engineer must create a clustered index on the 'Region' column to ensure performance.

Answer

The 'Region' column must be part of a primary key to enable partition pruning.

Question 19

Which TWO of the following statements accurately describe the characteristics of Snowflake's micro-partitions?

Accepted Answer

Micro-partitions are immutable.. Micro-partitions are the fundamental unit of storage in Snowflake. Understanding their structure is vital for performance tuning, as they are immutable and automatically clustered. Because they are immutable, any update or delete operation results in the creation of new micro-partitions, which is handled seamlessly by the cloud service layer. This architecture removes the need for manual index maintenance and vacuuming common in legacy databases, ensuring consistent performance for analytic workloads.

Answer

Micro-partitions are manually created by users using clustering keys.

Answer

Micro-partitions range in size from 500 MB to 1 GB before compression.

Answer

Users must run an ANALYZE command to update partition metadata.

Question 20

Which TWO of the following are benefits of Snowflake's decoupled storage and compute architecture?

Accepted Answer

Ability to scale compute without resizing storage.. Decoupling storage and compute is the foundation of Snowflake's AI Data Cloud. It enables users to scale compute resources up or down to match workload intensity without affecting storage costs, and vice versa. This independence provides unmatched agility, as organizations can handle massive data volumes while only paying for the compute power actually required to process those volumes, eliminating the need for rigid, hardware-constrained capacity planning.

Answer

Reduced storage costs due to on-demand compute.

Answer

Automatic data partitioning based on hardware capacity.

Answer

Compute resources must be equal to storage size.