COF-C03 Snowflake AI Data Cloud Features and Architecture — Timed 40 Questions

Question 1

Refer to the exhibit. The PROD_WH is experiencing significant performance degradation during peak hours. Analysts report queries are taking longer than usual, yet the warehouse is only using a single cluster. Which action should be taken to improve query performance?

40 minute time limit — choose an answer to begin.

Accepted Answer

Change the scaling policy to STANDARD. The exhibit shows a multi-cluster warehouse configured with a maximum of 5 clusters but utilizing the 'ECONOMY' scaling policy. In Snowflake, the 'ECONOMY' policy favors waiting for existing clusters to handle the load before spinning up new ones to save costs. Switching to 'STANDARD' policy will prioritize adding clusters sooner, which helps reduce query queuing and improves overall performance during high-concurrency periods while effectively leveraging the available compute resources.

Answer

Increase the MAX_CLUSTER_COUNT to 10

Answer

Increase the warehouse size from X-SMALL to SMALL

Answer

Decrease the auto_suspend value to 60

Question 2

An organization requires strict data isolation between departments while sharing the same underlying data. What is the most efficient way to achieve this using Snowflake?

Accepted Answer

Assign different warehouses to different roles.. Snowflake's architecture allows for separate virtual warehouses to be mapped to the same database. By granting different roles to different departments and assigning each to their own virtual warehouse, you achieve complete compute isolation. This ensures that one department's heavy queries cannot impact the performance of another's, while both access the exact same underlying storage, eliminating the need for data duplication or complex ETL synchronization processes.

Answer

Create separate databases for each department.

Answer

Use a single warehouse for all departments.

Answer

Use virtual private Snowflake instances.

Question 3

Which TWO of the following are true regarding the Snowflake Local Disk Cache?

Accepted Answer

It is managed automatically by Snowflake.. The Local Disk Cache is an essential feature that speeds up repeated query execution. It uses the SSD storage attached to the compute nodes in a virtual warehouse. When a query is run, the data retrieved from the object storage is cached locally. If the same warehouse performs the same query again, the data can be read from this local cache, dramatically reducing latency compared to reading from the cloud storage again.

Answer

It is persistent across warehouse restarts.

Answer

It is shared across different virtual warehouses.

Answer

It is used to cache query result sets.

Question 4

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 5

Which TWO features are part of Snowflake's Cortex AI capabilities that allow users to perform natural language processing directly within SQL?

Accepted Answer

SNOWFLAKE.CORTEX.SUMMARIZE. Snowflake Cortex is a fully managed service that provides building blocks for AI and machine learning. It integrates Large Language Models (LLMs) directly into the Snowflake platform, allowing users to call specialized functions within their SQL queries. This architecture removes the need to move data to external AI platforms, maintaining security and reducing the complexity of AI-driven workflows.

Answer

SNOWFLAKE.CORTEX.FORECAST

Answer

SNOWFLAKE.CORTEX.VECTOR_SEARCH

Answer

SNOWFLAKE.CORTEX.TRAIN_MODEL

Question 6

A developer needs to store intermediate transformation data that does not require long-term retention or Fail-safe protection but should persist across multiple sessions. Which TWO table types would meet these requirements while minimizing storage costs?

Accepted Answer

Transient Tables. Transient and Temporary tables are designed to reduce storage costs by eliminating the Fail-safe period, which is a non-configurable 7-day period for Permanent tables. Transient tables are better for data that needs to be accessed by multiple users or sessions, while Temporary tables are best for data that is only needed within a single user's current session.

Answer

Permanent Tables

Answer

External Tables

Answer

Secure Views

Question 7

In the context of the Snowflake AI Data Cloud, what is the primary architectural difference between a 'Standard' Virtual Warehouse and a 'Snowpark Optimized' Virtual Warehouse?

Accepted Answer

Snowpark Optimized warehouses provide 16x more memory per node than Standard warehouses.. Snowpark Optimized warehouses are designed for memory-intensive workloads, such as training machine learning models or performing complex data transformations using Python or Scala. While Standard warehouses provide a balance of CPU and memory, Snowpark Optimized warehouses offer significantly more memory per credit, reducing the risk of 'out-of-memory' errors for large-scale data science tasks.

Answer

Snowpark Optimized warehouses have faster SSDs for local caching of data.

Answer

Only Snowpark Optimized warehouses can execute Python and Scala code.

Answer

Snowpark Optimized warehouses are the only ones capable of multi-cluster scaling.

Question 8

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 9

Refer to the exhibit. A workload is experiencing intermittent queuing. The current scaling policy is set to ECONOMY. What should the administrator consider regarding the scaling policy to prioritize performance over credit savings?

Accepted Answer

Change the SCALING_POLICY to STANDARD.. The ECONOMY scaling policy is designed to minimize costs by waiting until the queue is sufficiently large before spinning up a new cluster. By switching to the STANDARD policy, Snowflake prioritizes performance by starting new clusters immediately when a query is queued. This change is crucial for time-sensitive production workloads where avoiding even minor query delays is more important than the potential for slightly higher compute costs.

Answer

Increase the MIN_CLUSTER_COUNT to 5.

Answer

Resize the warehouse to 2X-LARGE.

Answer

Set the auto-suspend time to 0.

Question 10

Which THREE features are provided by the Snowflake Cloud Services layer?

Accepted Answer

Query result caching. The Cloud Services layer is the intelligent control plane of the Snowflake architecture. It is responsible for critical management and coordination tasks. Understanding these functions is essential for grasping how Snowflake provides a unified, secure, and performant environment. These features enable developers and administrators to manage data access, ensure transactional consistency, and optimize query execution without having to interact with the underlying physical infrastructure, which remains abstracted and managed by Snowflake.

Answer

Data compression and encryption

Answer

Large-scale data storage persistence

Question 11

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.

Question 12

Which TWO of the following statements correctly describe the behavior of Snowflake's Cloud Services layer?

Accepted Answer

It manages authentication and security access control.. The Cloud Services layer acts as the 'brain' of the Snowflake platform, handling high-level tasks like authentication, query parsing, and access control. By offloading these management tasks from the compute layer, Snowflake ensures that the compute resources are exclusively dedicated to processing data. Understanding this layer is critical for grasping how Snowflake achieves concurrency and global management across different cloud regions.

Answer

It performs physical data transformation and heavy join operations.

Answer

It stores the permanent, encrypted copies of user tables.

Answer

It handles query execution scaling automatically.

Question 13

Which THREE of the following are true regarding Snowflake's Micro-partitions?

Accepted Answer

Micro-partitions are static and immutable after they are created.. Micro-partitions are the core unit of storage in Snowflake. Unlike traditional block-level storage, micro-partitions are immutable, compressed, and store data in a columnar fashion. This design is crucial for Snowflake's performance, as it allows for extremely efficient data retrieval, pruning, and cloning operations. Understanding these properties is essential for grasping why Snowflake does not require traditional manual indexing or frequent vacuuming processes like older database technologies.

Answer

Users must manually define the size of micro-partitions.

Answer

Data within each micro-partition is stored in row-oriented format.

Question 14

Which THREE of the following statements about Snowflake Virtual Warehouses are true?

Accepted Answer

Warehouses can be resized while running without interrupting queries.. Virtual Warehouses are the compute engines of Snowflake. They are essentially independent clusters that provide resources to process SQL commands. Understanding how they work, how they are resized, and how they behave during multi-cluster scaling is fundamental for any SnowPro exam candidate. They are designed to be spun up and down on demand, allowing for maximum flexibility and granular cost management in an analytical workload environment.

Answer

Warehouses store the data they process on their local SSDs permanently.

Answer

Suspending a warehouse immediately deletes all cached data.

Answer

Only one warehouse can be active in a Snowflake account at a time.

Question 15

A data engineer runs a query that joins a large fact table with a small dimension table. The query performs a broadcast join, sending the entire small table to every node processing the large table. Which Snowflake architectural feature enables this efficient broadcast without manual data movement?

Accepted Answer

The query optimizer decides to broadcast the small table, and the warehouse's nodes retrieve the necessary micro-partitions from remote storage.. The broadcast join is an execution strategy chosen by the query optimizer when one side of the join is small enough to be sent to all nodes processing the larger side. Because Snowflake separates storage from compute, each node can independently read the required micro-partitions from remote storage, enabling this without manual intervention.

Answer

The Cloud Services layer automatically replicates the small table to all compute nodes.

Answer

The virtual warehouse's compute nodes share a distributed file system that allows direct access to any micro-partition.

Answer

The small table is automatically cached in the warehouse's local memory, and all nodes read from that cache.

Question 16

A security administrator needs to allow an external analytics team to read a specific set of columns in a table while preventing them from seeing customer email addresses and phone numbers. The team uses their own Snowflake account and must query the data directly without copying it. The administrator wants the masking to be enforced based on the querying user's role, even if the data is accessed through a view. Which Snowflake capability should the administrator implement?

Accepted Answer

Create a masking policy and attach it to the sensitive columns, then share the table through a Snowflake share with the external account.. Column-level masking policies in Snowflake evaluate the current role and return either the original value or a masked value, enabling role-based protection of sensitive fields. Because the policy is attached to the column itself, it remains in effect even when the table is shared to another Snowflake account, allowing direct queries while preventing unauthorized roles from seeing email addresses and phone numbers.

Answer

Use object tagging on the sensitive columns and grant the external role the TAG_READ privilege.

Answer

Create a secure view that excludes the sensitive columns and grant the external role access to the view.

Answer

Apply a row access policy to the table so the external team only sees rows that do not contain sensitive data.

Question 17

A data steward must ensure that analysts querying a table in the SALES database see masked values for a phone number column unless they belong to a specific role. The steward also needs the masking rule to apply consistently across all tables that use the same column name. Which Snowflake feature should be used?

Accepted Answer

A masking policy assigned to the column and applied consistently by column name.. Masking policies are schema-level objects that transform column values based on the querying role and can be attached directly to a column or via a tag. Assigning the same policy to columns with the same name across tables enforces consistent masking. Row access policies, secure views, and bare tags do not provide the same role-based column transformation across multiple tables.

Answer

A secure view that selects a constant placeholder value in place of the phone number column.

Answer

A row access policy attached to each table with a mapping table of authorized roles.

Answer

An object tag applied to the phone number column with a value of 'PII'.

Question 18

A Snowflake administrator needs to ensure that a critical data pipeline continues to run even if the primary virtual warehouse fails. The pipeline runs a series of SQL statements that load data from an external stage into a permanent table and then perform transformations. The administrator wants to configure automatic failover for the warehouse so that if the primary warehouse becomes unavailable, a secondary warehouse in a different region takes over. Which Snowflake feature should the administrator use?

Accepted Answer

Virtual warehouse replication with failover. Virtual warehouse replication with failover is designed to replicate a warehouse to a secondary region and automatically redirect queries if the primary fails. This ensures that compute resources remain available even during a regional outage, which is critical for continuous pipeline execution. Other options address scaling, data replication, or intra-region redundancy, but none provide automatic cross-region failover for the warehouse itself.

Answer

Multi-cluster warehouse with auto-scaling enabled

Answer

Database replication with failover enabled

Answer

Snowflake's built-in high availability within a single region

Question 19

A Snowflake user executes a query that joins a large fact table with a small dimension table. The query is performing poorly. The user notices that the dimension table is not being broadcast and the join is taking a long time. Which of the following actions is most likely to improve the query performance?

Accepted Answer

Use a larger virtual warehouse to provide more compute resources for the join.. When a join is performing poorly, one common cause is insufficient compute resources, leading to spilling to local or remote storage. Increasing the warehouse size provides more memory and compute power, which can allow the join to complete more efficiently. While other factors like data skew or join strategy can also affect performance, scaling up the warehouse is a straightforward action that can yield immediate improvements.

Answer

Add a clustering key to the large fact table on the join column.

Answer

Enable the USE_CACHED_RESULT parameter to force the use of cached results.

Answer

Ensure that statistics are up to date by running ANALYZE TABLE on both tables.

Question 20

A Snowflake user is querying a large table and notices that the query is slow. The user wants to improve performance by leveraging clustering. Which two actions can help improve the clustering and query performance? (Choose two.)

Accepted Answer

Enable automatic clustering on the table.. Defining a clustering key on frequently filtered columns and enabling automatic clustering are the two actions that improve clustering and query performance. The clustering key co-locates related data, reducing scan size, while automatic clustering maintains this organization as data changes. Manual sorting is temporary, increasing warehouse size does not address clustering, and RECLUSTER is not a valid command.

Answer

Increase the warehouse size to a larger size.

Answer

Manually sort the table by the desired columns using ORDER BY in a CREATE TABLE AS SELECT statement.

Answer

Use the RECLUSTER command to manually recluster the table.