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

Question 1

What is the role of the 'Search Optimization Service' in Snowflake?

Accepted Answer

It accelerates point-lookup queries.. The Search Optimization Service is specifically designed to improve the performance of point-lookup queries on large tables. By creating a persistent search index, it allows Snowflake to quickly identify specific rows that match a filter, even in massive datasets. This is essential for applications requiring sub-second response times for single-record lookups, which would otherwise require full table scans that are expensive and slow in traditional analytical environments.

Answer

It speeds up complex join operations.

Answer

It enables automatic data partitioning.

Answer

It optimizes storage space for tables.

Question 2

Which statement best describes the 'Snowflake Data Cloud' architecture's approach to scalability?

Accepted Answer

It supports independent scaling of compute and storage.. Snowflake's architecture features a unique separation of storage and compute. This allows storage to grow independently as data is added and compute resources to be scaled independently as workloads change. Because compute resources (virtual warehouses) can be added, removed, or resized instantly without affecting the data in storage, Snowflake provides near-infinite, elastic scalability that is perfectly suited for modern, high-demand cloud analytical applications.

Answer

It requires manual sharding of data across compute nodes.

Answer

Storage and compute are scaled together as a single unit.

Answer

Scalability is limited by the physical hardware of the server.

Question 3

A data engineer wants to ensure that data loaded into Snowflake is encrypted at rest and in transit. Which Snowflake architecture component handles the encryption and decryption processes automatically without requiring user-managed keys or manual intervention?

Accepted Answer

The Snowflake Cloud Services layer. Snowflake utilizes a multi-layered security architecture where encryption is handled transparently by the cloud service layer. Data is automatically encrypted using AES-256 before being written to persistent storage. This architectural feature is critical because it ensures compliance and data protection without adding operational overhead for the end user, allowing architects to focus on data transformation logic rather than managing complex infrastructure security keys.

Answer

The Virtual Warehouse compute engine

Answer

The Snowflake Stage object configuration

Answer

The Snowflake Database Catalog

Question 4

Which TWO of the following statements accurately describe the functionality of Snowflake's separation of storage and compute architecture?

Accepted Answer

Storage costs are independent of the warehouse size.. Snowflake's architecture decouples storage from compute, allowing each to scale independently. This is foundational to the platform, enabling users to resize compute resources instantly without moving data. Understanding this model is essential because it avoids the classic 'resource contention' issue found in traditional databases where storage and compute are bound, allowing for optimized cost management and highly elastic performance for diverse analytical workloads across the organization.

Answer

Scaling a warehouse requires reloading all data.

Answer

Data is physically tied to specific compute clusters.

Answer

Storage is only accessible during warehouse uptime.

Question 5

Which Snowflake feature provides an 'always-on' mechanism to allow users to instantly query the state of data as it existed at any point in the past within a defined retention period?

Accepted Answer

Time Travel. Time Travel is a core Snowflake feature that maintains historical data states, allowing users to query data as it existed at a specific timestamp or offset. This feature is vital for data recovery, auditing, and comparing current data against historical trends. By leveraging the metadata-heavy architecture of Snowflake, it provides near-instantaneous access to previous data versions without requiring costly manual backups or complex database snapshots.

Answer

Database Snapshots

Answer

Materialized Views

Answer

Data Sharing

Question 6

What is the role of the Cloud Services layer within the Snowflake architecture when a user submits a query?

Accepted Answer

It coordinates the query compilation and optimization.. The Cloud Services layer acts as the 'brain' of Snowflake, handling authentication, metadata management, access control, and query optimization. When a user submits a query, this layer parses, compiles, and optimizes the execution plan before dispatching it to a virtual warehouse. Understanding this layer is essential because it is the common entry point for all operations, ensuring consistent security, policy enforcement, and efficient query planning regardless of the compute power used.

Answer

It executes the physical join and aggregation operations.

Answer

It persists the raw data in micro-partitions.

Answer

It manages the local SSD cache of the warehouse.

Question 7

A data engineer runs a query that joins a large fact table with a small dimension table. The query takes 12 minutes. The engineer notices that the small dimension table is broadcast to all compute nodes, and the fact table is redistributed across nodes. Which Snowflake feature is primarily responsible for this behavior, and what is its main benefit in this scenario?

Accepted Answer

Query optimizer's join strategy selection, which chooses broadcast for small tables to minimize data movement and improve performance.. The Snowflake query optimizer evaluates join inputs and selects the most efficient distribution method. When one side of the join is small, broadcasting it to all nodes eliminates the need to redistribute the larger table, reducing network traffic and improving performance. This optimization is automatic and based on table statistics and heuristics.

Answer

Virtual warehouse scaling, which adds compute resources to handle the join operation more quickly.

Answer

Result caching, which stores the query results for subsequent identical queries, reducing execution time.

Answer

Micro-partitioning, which automatically divides tables into small chunks and enables pruning during scans.

Question 8

A Snowflake administrator configures a storage integration named EXT_S3_INT so that an external stage can read Parquet files from an Amazon S3 bucket. After creating the integration, the administrator runs DESCRIBE INTEGRATION EXT_S3_INT and copies the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID values. What must the administrator do with these two values to allow Snowflake to access the bucket?

Accepted Answer

Attach them to an AWS IAM role trust policy that permits the Snowflake-generated IAM user to assume the role.. Storage integrations let Snowflake assume an AWS IAM role instead of storing AWS credentials. The IAM user ARN is the principal that assumes the role, and the external ID is a condition that prevents the confused-deputy problem. Both must be configured in the role's trust policy in AWS, after which the integration can be referenced by an external stage to read the S3 data.

Answer

Register them with an AWS KMS key policy so Snowflake can decrypt the Parquet files at rest.

Answer

Store them as a secret in Snowflake and reference the secret from the external stage definition.

Answer

Add them to the S3 bucket policy as principal and condition values in the bucket's resource statement.

Question 9

A Snowflake user runs a SELECT statement against a large fact table. The query returns results in under a second, and the query profile shows that no warehouse was started. Which Snowflake feature most likely served the result?

Accepted Answer

The Snowflake Query Result Cache in the Cloud Services layer.. The Query Result Cache is a Cloud Services layer feature that stores the output of completed queries for up to 24 hours. When an identical query is submitted and the micro-partitions referenced by the original query are unchanged, Snowflake returns the cached result directly, bypassing the warehouse entirely. This is why the query completed quickly with no warehouse start.

Answer

The result set persisted by the user's open session.

Answer

The metadata cache maintained by the Cloud Services layer.

Answer

The local disk cache on the virtual warehouse.

Question 10

A data engineer loads a batch of records into a table and then runs a query that filters on a column with a high-cardinality value. The query profile shows partition pruning eliminated most micro-partitions, but the query still scans many rows within the surviving partitions. The table has never been clustered. Which characteristic of Snowflake micro-partitions best explains why pruning was effective at the partition level yet still left many rows to scan?

Accepted Answer

Micro-partitions are immutable and store columnar data, so pruning uses min/max metadata while row-level filtering still requires reading surviving partitions.. Micro-partitions are immutable columnar storage units that Snowflake automatically maintains with per-column metadata, including min/max values. That metadata lets the optimizer prune partitions that cannot satisfy a filter even on unclustered tables. However, pruning is coarse: once a partition survives, its columnar data must be scanned for matching rows, which is why residual row scanning remains and why clustering can further improve pruning.

Answer

Micro-partitions are compressed and encrypted, so the optimizer cannot read metadata until the files are decompressed on the warehouse.

Answer

Micro-partitions are immutable and therefore require a full table scan whenever a filter column is not the clustering key.

Answer

Micro-partitions are hash-distributed across compute nodes, so pruning depends on which node holds the relevant hash bucket.

Question 11

A data engineer is loading streaming events through Snowpipe. The ingest rate spikes unpredictably, causing queued files to wait several minutes before loading, while the target table is queried heavily by BI users. The engineer wants Snowpipe processing to scale independently of the BI virtual warehouse so that neither workload interferes with the other. Which Snowflake feature should the engineer leverage to achieve this?

Accepted Answer

Rely on Snowflake-managed compute that Snowpipe uses serverlessly to load files without consuming user warehouse resources.. Snowpipe performs continuous data ingestion using compute resources managed by Snowflake rather than a user-created virtual warehouse. Because its compute is separate from the warehouse serving BI queries, ingest and query workloads scale independently, eliminating the interference described. This architecture is what allows Snowpipe to load files promptly even when users are actively querying the target tables.

Answer

Enable multi-cluster scaling on the BI virtual warehouse so both workloads share its compute.

Answer

Create a dedicated virtual warehouse and configure Snowpipe to run all its COPY statements on that warehouse.

Answer

Increase the size of the existing BI virtual warehouse so it can absorb the additional Snowpipe load.

Question 12

A data engineer runs a long-running aggregation query on a virtual warehouse. Mid-execution, the warehouse is resized from MEDIUM to LARGE. What happens to the query?

Accepted Answer

The query continues running on the MEDIUM warehouse until completion, and the resize applies to subsequent queries.. When a warehouse is resized while queries are running, Snowflake postpones the change until the warehouse becomes idle. The running query continues on the original size and completes normally. The new size applies only to future queries after the warehouse restarts. This design avoids disrupting active workloads and ensures consistent execution.

Answer

The query is cancelled and must be resubmitted manually after the resize completes.

Answer

The query fails with an error indicating the warehouse size changed during execution.

Answer

The query is paused, the warehouse resizes, and the query automatically resumes on the LARGE warehouse with full progress preserved.

Question 13

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 14

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 15

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