COF-C03 Snowflake AI Data Cloud Features and Architecture Practice Test 3 — 15 Questions

Question 1

A data engineer runs a long-running query on a virtual warehouse. After the query completes, they immediately run the same query again. The second execution returns in under a second and consumes no warehouse credits. Which Snowflake feature explains this behavior?

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

Accepted Answer

Query Result Cache. The Query Result Cache automatically caches the results of every query for 24 hours. When an identical query is re-executed and the underlying data has not changed, Snowflake returns the cached result without using the virtual warehouse, resulting in sub-second response and zero credit consumption. This is a built-in feature that does not require any user configuration.

Answer

Local Disk Cache

Answer

Metadata Cache

Answer

Materialized Views

Question 2

A Snowflake administrator is configuring a virtual warehouse to handle a workload with unpredictable concurrency spikes. They want to ensure that the warehouse can automatically add compute resources to handle the load and remove them when no longer needed. Which two features should they configure? (Choose two.)

Accepted Answer

Multi-cluster warehouse. To handle unpredictable concurrency, a multi-cluster warehouse must be enabled, and an auto-scaling policy must be set. The multi-cluster warehouse allows Snowflake to add clusters as needed, and the auto-scaling policy defines the conditions for adding or removing clusters. Auto-suspend, auto-resume, and QAS do not provide dynamic scaling for concurrency.

Answer

Auto-resume

Answer

Auto-suspend

Answer

Query Acceleration Service

Question 3

A Snowflake architect is designing a solution to share live data with a partner organization. The partner uses their own Snowflake account in a different region. The architect wants to minimize data duplication and ensure the partner always sees the latest data. Which Snowflake feature should the architect use?

Accepted Answer

Data sharing using a share object, which allows the partner to access data directly from the provider's account without copying.. Snowflake data sharing allows a provider to share data with a consumer without copying, enabling real-time access to the latest data. The consumer reads directly from the provider's storage, so there is no duplication. This is the most efficient and up-to-date method for sharing live data with a partner, even across regions.

Answer

Snowpipe to continuously load data into the partner's account as it changes.

Answer

External tables that point to files in a cloud storage bucket that both parties can access.

Answer

Database replication to the partner's region, which creates a copy of the data that is refreshed periodically.

Question 4

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 5

Which TWO of the following statements accurately describe the function of the Snowflake Cloud Services layer?

Accepted Answer

It handles query parsing, compilation, and optimization.. The Cloud Services layer acts as the brain of Snowflake. It handles metadata management, access control, query optimization, and infrastructure coordination. By separating these management tasks from the compute layer, Snowflake ensures that global state is maintained independently of specific compute clusters. Understanding this layer is fundamental to grasping how Snowflake achieves high availability, security, and performance without requiring users to manage underlying hardware or software configurations directly.

Answer

It stores the actual table data in micro-partitions.

Answer

It provides compute resources to execute DML statements.

Answer

It performs the final data shuffling for JOIN operations.

Question 6

What is the primary benefit of the Snowflake architecture's decoupling of storage and compute?

Accepted Answer

It enables independent scaling of storage and compute resources.. The separation of storage and compute allows organizations to scale these resources independently based on demand. You can store petabytes of data without paying for compute, and you can spin up massive compute clusters to run complex analytics without needing to replicate or move your data. This architectural flexibility is a core differentiator, enabling cost-effective data warehousing and high-performance processing without the rigid constraints of traditional on-premises or monolithic cloud databases.

Answer

It allows data to be stored in multiple cloud regions simultaneously.

Answer

It automatically encrypts all data at rest and in transit.

Answer

It eliminates the need for virtual warehouses.

Question 7

When a query is submitted to Snowflake, which component is responsible for retrieving the metadata required to build the query execution plan?

Accepted Answer

The Cloud Services Layer. The Cloud Services layer maintains the metadata store, which contains information about table structures, file locations (in object storage), and statistics. When a user runs a query, the Cloud Services layer queries this metadata to optimize the execution path. This centralized management ensures that the compute nodes only receive instructions on which files to process, significantly reducing overhead and improving query performance by pruning unnecessary data before it is ever accessed.

Answer

The Virtual Warehouse

Answer

The Database Storage Layer

Answer

The Query Results Cache

Question 8

Which THREE of the following are benefits of Snowflake's micro-partitioning architecture?

Accepted Answer

Enables efficient data pruning during query execution.. Micro-partitions are the core unit of storage in Snowflake. Because they are immutable and contain metadata (like min/max values), Snowflake can perform efficient pruning, skipping irrelevant data during query execution. This architecture allows for automatic clustering, high concurrency without locking, and efficient DML operations. Understanding these benefits is crucial for optimizing Snowflake performance, as it explains why traditional indexing strategies are largely unnecessary and why Snowflake handles large-scale analytical workloads so efficiently.

Answer

Supports traditional B-tree indexing for faster lookups.

Answer

Requires manual vacuuming to reclaim disk space.

Question 9

Which feature allows Snowflake to automatically utilize results from previous queries without consuming compute resources?

Accepted Answer

Query Result Cache. The Query Result Cache stores the results of queries for 24 hours. If an identical query is executed and the underlying data has not changed, Snowflake returns the result from the cache immediately. This avoids the need to spin up or use warehouse compute resources, providing near-instant responses while significantly reducing costs. It is a critical component for optimizing dashboard performance and reducing redundant processing of static data in analytical environments.

Answer

Warehouse Caching

Answer

Metadata Cache

Answer

Micro-partition Pruning

Question 10

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 11

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 12

Which component in the Snowflake architecture is responsible for managing the data storage lifecycle, including compaction and file management?

Accepted Answer

The Cloud Services layer. Snowflake is a fully managed service, which means it automatically handles storage optimization tasks. The Cloud Services layer keeps track of all micro-partitions and performs background maintenance, such as coalescing small files into larger, more efficient ones. This 'zero-management' philosophy allows users to focus on querying their data rather than worrying about physical file layout, vacuuming, or manual performance tuning of the underlying storage environment.

Answer

The Virtual Warehouse

Answer

The Object Storage provider

Answer

The Data Loader

Question 13

Which Snowflake feature allows for the creation of a 'zero-copy' clone of a database or table?

Accepted Answer

Zero-copy Cloning. Cloning in Snowflake creates a metadata-only reference to the existing data. Because it does not copy the actual underlying micro-partitions, it is instantaneous and incurs no additional storage costs until the data in the clone is modified. This is a powerful feature for development, testing, and production environments, allowing users to create fully isolated, writable copies of production data in seconds without duplicating large datasets.

Answer

Data Replication

Answer

Table Partitioning

Answer

Time Travel

Question 14

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 15

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.