COF-C03 Practice Test 16 — 15 Questions

Question 1

A company has a table named customer_orders that contains a column storing the customer's full name. A masking policy has been applied to that column. The policy uses CURRENT_ROLE() to compare the executing role against a list of roles allowed to see the raw value. A user with a role that is not in the allowed list runs a query that includes the column in an ORDER BY clause. What does the user see?

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

Accepted Answer

The user sees the masked value in the result set and the sort is performed on the masked value.. A masking policy rewrites every reference to the protected column for unauthorized users, including references in ORDER BY. The user therefore sees the masked value and sorting is based on that masked value. The policy is not bypassed by placing the column in a sort clause.

Answer

The user sees the raw value in the result set but the sort is performed on the masked value.

Answer

The user sees the masked value in the result set, but the sort is performed on the raw value because ORDER BY is evaluated before masking.

Answer

The query fails because masking policies cannot be applied to columns used in ORDER BY.

Question 2

What happens when you modify data in a zero-copy cloned table?

Accepted Answer

Only the modified micro-partitions are stored as new files.. When data is modified in a clone, Snowflake uses a 'copy-on-write' mechanism. Only the changed micro-partitions are created as new, independent files. The cloned table then points to the new partition for the modified data while still pointing to the original micro-partitions for all unmodified data. This ensures that the clone remains isolated from the original table without requiring an initial full copy of the entire dataset.

Answer

The original table is automatically updated.

Answer

The entire dataset is copied to the clone.

Answer

The clone becomes locked and read-only.

Question 3

Which component of the Snowflake architecture is responsible for the permanent storage of data and is independent of compute resources?

Accepted Answer

Database Storage Layer. The Storage Layer is the foundation of Snowflake's multi-cluster shared data architecture. By separating storage from compute, Snowflake allows users to scale compute resources independently based on workload demand without duplicating data. This design ensures durability, massive scalability, and cost-efficiency, as users only pay for the storage consumed and the compute time utilized during active query processing tasks.

Answer

Cloud Services Layer

Answer

Virtual Warehouse

Answer

Query Optimizer

Question 4

Which Snowflake feature allows you to access data from a previous point in time to recover from accidental deletions or updates?

Accepted Answer

Time Travel. Time Travel is a critical feature that allows users to query, clone, or restore data as it existed at any point within a defined retention period. This is essential for auditing, compliance, and disaster recovery. Because of the immutable nature of micro-partitions, Snowflake can simply reference older versions of the data, providing a high-performance, low-cost solution for data recovery without needing traditional backup and restore cycles.

Answer

Fail-safe

Answer

Data Sharing

Answer

Cloning

Question 5

Which object acts as the primary container for sharing data with external Snowflake accounts?

Accepted Answer

Share. A Share is the fundamental object in Snowflake that allows a provider to grant access to specific database objects without transferring the data itself. By using Shares, organizations can securely collaborate by allowing consumers to query the provider's data as if it were local to their own account. This architecture is vital for data governance, as it prevents data duplication and ensures consistent security policies across all shared data assets within the ecosystem.

Answer

Data Warehouse

Answer

Database

Answer

Integration

Question 6

A consumer has created a database from a share provided by a partner. The consumer wants to query the shared data but receives an error indicating insufficient privileges. Which role must the consumer's user be granted to access the shared database?

Accepted Answer

A role with the IMPORTED PRIVILEGES privilege on the shared database. To access a shared database, the consumer must grant the IMPORTED PRIVILEGES privilege on that database to a role and then grant that role to the user. This privilege is specific to shared databases and is required for any role that needs to query the shared objects. Other roles like ACCOUNTADMIN or SYSADMIN do not have this privilege by default.

Answer

The PUBLIC role

Answer

The ACCOUNTADMIN role

Answer

The SYSADMIN role

Question 7

A data engineer notices a large table's clustering depth is very high on the columns used in frequent range filters, and queries are scanning far more micro-partitions than expected. The table receives continuous small inserts throughout the day. Which action best improves pruning while controlling reclustering cost?

Accepted Answer

Batch the small inserts into larger, less frequent loads so fewer micro-partitions are created out of order.. Frequent tiny inserts scatter data across many micro-partitions, inflating clustering depth and undermining min-max pruning. Consolidating those inserts into larger batches yields better-ordered micro-partitions, so range filters prune more effectively and automatic reclustering has less fragmented data to reorganize. Choosing a different key or removing clustering would not solve the root cause of the poor ordering.

Answer

Remove the clustering key and add a search optimization service on the filter columns instead.

Answer

Keep the existing clustering key and rely on automatic reclustering to restore ordering.

Answer

Change the clustering key to a high-cardinality column such as a unique transaction ID.

Question 8

A data architect is designing a multi-cluster warehouse to handle unpredictable peak concurrent user sessions. Which scaling property ensures that Snowflake automatically starts additional warehouse clusters to prevent query queuing while maintaining performance?

Accepted Answer

Multi-cluster warehouse configuration. Multi-cluster warehouses are designed specifically to handle high concurrency. By setting the 'MIN_CLUSTER_COUNT' and 'MAX_CLUSTER_COUNT' properties, Snowflake monitors the queue length and automatically provisions additional clusters to distribute the load. This architectural feature is vital for maintaining consistent performance during bursty workloads without manual intervention, ensuring that end-users do not experience latency when the warehouse reaches its full processing capacity under heavy concurrent query requests.

Answer

Auto-resume

Answer

Scaling Policy

Answer

Warehouse auto-suspend

Question 9

A developer is running a COPY INTO command to load 1,000 CSV files. The requirement is that if even one row in any file fails due to a data type mismatch, the entire load operation must stop and no data should be committed to the table. Which ON_ERROR setting is required?

Accepted Answer

ON_ERROR = ABORT_STATEMENT. The ON_ERROR parameter determines how the COPY command handles malformed data or schema mismatches. The ABORT_STATEMENT setting is the strictest option available, ensuring transactional integrity by rolling back the entire load if a single error occurs. This is critical for datasets where partial loads could lead to data inconsistency or require complex manual cleanup.

Answer

ON_ERROR = CONTINUE

Answer

ON_ERROR = SKIP_FILE

Answer

ON_ERROR = SKIP_FILE_1%

Question 10

A Materialized View is created on a base table that experiences high churn (frequent inserts, updates, and deletes). What is the most likely impact of this configuration?

Accepted Answer

The cost of maintaining the view may exceed the query performance benefits.. Materialized views in Snowflake are maintained by a serverless background process. When the base table changes, the view must be updated to remain consistent. High churn on the base table leads to frequent maintenance tasks, which can result in significant serverless credit consumption, often making the view more expensive than the performance benefit it provides.

Answer

The Materialized View will become stale and return outdated results.

Answer

Virtual warehouse credits will be used for the background maintenance.

Answer

The base table will be locked during the view's maintenance cycles.

Question 11

A data engineer needs to transform a variant column containing an array of objects into a relational format. Which TWO Snowflake features or functions are required to achieve this?

Accepted Answer

The FLATTEN function. To transform semi-structured data like arrays into individual rows, the FLATTEN function is used to explode the array elements. This is typically paired with the LATERAL keyword, which allows the FLATTEN function to reference columns from preceding tables in the FROM clause, effectively joining each array element back to its parent row.

Answer

The UNPIVOT clause

Answer

The PARSE_JSON function

Answer

The STRTOK_TO_ARRAY function

Question 12

A company wants to share data with a partner without replicating the data. Which Snowflake feature is designed for this purpose?

Accepted Answer

Data Sharing. Snowflake Data Sharing allows multiple accounts to access the same underlying data directly from the provider's storage without copying or moving any files. This is a revolutionary aspect of Snowflake's architecture, as it provides a live, secure, and governed data access model. By removing the need for ETL or data movement, organizations can collaborate instantly and ensure that the partner is always viewing the most current, accurate version of the shared data.

Answer

External Tables

Answer

Data Replication

Answer

Database Cloning

Question 13

A consumer account has created a read-only database named SHARED_SALES from a provider's share and now wants to combine the shared tables with its own local table, LOCAL_REGIONS, in a single query. Which statement about this operation is accurate?

Accepted Answer

The consumer can join SHARED_SALES tables with LOCAL_REGIONS because the shared database is mounted as a normal read-only database in the consumer account.. A shared database created from a share is a read-only database in the consumer account, and read-only status does not prevent it from being queried alongside local objects. Consumers routinely join shared tables with their own data, as long as their role holds the needed privileges on both sides of the join.

Answer

The consumer cannot join them because shared databases exist in a separate, isolated storage layer that cannot be referenced alongside local tables.

Answer

The consumer can join them only if the provider grants SELECT on the local table through the share.

Answer

The consumer must first clone the shared tables into its own database before any join with local data is possible.

Question 14

How does Snowflake's architecture provide high availability for its internal metadata and security services?

Accepted Answer

By deploying the Cloud Services layer across multiple availability zones.. Snowflake is designed as a multi-zone service. The Cloud Services layer, which manages metadata, authentication, and security, is deployed across multiple availability zones in the cloud provider's region. If one zone fails, the service automatically fails over to another zone without manual intervention. This architectural design ensures that Snowflake remains resilient against infrastructure outages, maintaining high availability for the control plane even if specific data center components experience issues.

Answer

By storing metadata in the user's private S3 bucket.

Answer

By manually replicating the database to a secondary region.

Answer

By using a single large instance to manage all metadata.

Question 15

What is the primary benefit of Snowflake's 'Multi-Cluster Warehouse' feature when handling highly concurrent workloads?

Accepted Answer

It prevents query queuing by automatically spinning up additional clusters.. Multi-Cluster Warehouses allow Snowflake to automatically scale compute capacity by spinning up additional clusters of identical size when demand increases. This prevents query queuing and ensures consistent performance during peak times. By managing the number of active clusters dynamically, Snowflake provides a seamless user experience for concurrent workloads, enabling organizations to meet service-level agreements without manual intervention or over-provisioning compute resources during quiet periods.

Answer

It allows scaling storage independently of compute.

Answer

It reduces the cost of queries by decreasing the warehouse size.

Answer

It enables data sharing between different Snowflake accounts.