COF-C03 Practice Exam 2 — 20 Questions

Question 1

Which TWO of the following tasks are performed by the Snowflake Cloud Services layer?

Accepted Answer

Managing user authentication and access control for the account.. The Cloud Services layer acts as the 'brain' of Snowflake, managing the overall state of the system and coordinating activities. It handles tasks that do not require heavy computational power, such as security, metadata management, and query optimization. By centralizing these functions, Snowflake ensures consistent governance and performance across all virtual warehouses and storage components.

Answer

Executing the actual DML operations and data processing tasks.

Answer

Storing the raw data files in micro-partitions within cloud storage.

Answer

Providing the physical hardware resources for the cloud environment.

Question 2

A company is using a Multi-cluster Warehouse with the 'Auto-scale' mode enabled. What is the primary performance benefit of this configuration for a BI dashboard used by 500 concurrent users?

Accepted Answer

It prevents query queuing by providing more clusters to handle concurrent requests.. Multi-cluster warehouses are designed to handle high concurrency by automatically starting additional warehouse clusters as query queuing is detected. This ensures that as more users connect and run queries, the system can scale horizontally to maintain consistent performance and minimize wait times for all users.

Answer

It reduces the execution time of a single, complex long-running query.

Answer

It increases the memory available for large-scale data joins.

Answer

It automatically optimizes the clustering keys of the tables being queried.

Question 3

A data engineer has set up a Snowpipe that continuously loads JSON files from an external stage into a table. The stage references an Amazon S3 bucket with a notification integration. After several days, the engineer notices that new files are not being ingested, even though they exist in the S3 bucket. The Snowpipe is in a RUNNING state and the notification integration is active. What is the MOST likely cause of the missing data?

Accepted Answer

The S3 bucket notification is not configured to send events for the specific prefix or suffix of the new files.. Snowpipe ingests files based on event notifications from the external stage's cloud storage. When new files are not loaded, the most likely cause is that the event notification is not configured to capture those files, such as missing prefix or suffix filters. The pipe's running state and notification integration being active do not guarantee that all files trigger events. Checking the S3 bucket's event configuration is essential.

Answer

The files are compressed with gzip, which Snowpipe cannot automatically decompress.

Answer

The virtual warehouse used by Snowpipe is suspended, preventing the pipe from executing.

Answer

The Snowpipe's metadata has expired because the pipe was paused for more than 14 days.

Question 4

An organization wants to restrict access to Snowflake based on the source IP address of the client application. Which object should the administrator configure to enforce this network-level security?

Accepted Answer

Network Policy. Network policies are the primary mechanism for restricting access to Snowflake based on IP addresses. By defining allowed and blocked IP ranges, administrators can protect the account from unauthorized access attempts originating from outside the corporate network. This is a foundational governance task that ensures only trusted traffic can reach the Snowflake service, effectively mitigating risks associated with stolen credentials or external malicious activity.

Answer

Authentication Policy

Answer

Access Control Policy

Answer

Session Policy

Question 5

A data steward needs to review the history of changes made to a table, including which columns were added or dropped and when, for an audit that covers the past 60 days. The table is in a database that has a data retention period of 90 days. Which Snowflake feature should the steward use to retrieve this information?

Accepted Answer

The object change history accessible through the ACCOUNT_USAGE schema, such as the COLUMNS view with its deleted and change tracking columns.. Object change history in the ACCOUNT_USAGE schema records metadata changes such as column additions and drops, with timestamps and deleted markers. This is the correct source for auditing schema evolution. Time Travel and query or access history views serve different purposes and do not provide a structured column change log.

Answer

The ACCESS_HISTORY view in the ACCOUNT_USAGE schema.

Answer

The ACCOUNT_USAGE.QUERY_HISTORY view filtered by the table name.

Answer

Time Travel by querying the table with AT (TIMESTAMP => ...) for each day in the audit period.

Question 6

What is the primary function of the 'SECURITYADMIN' role in Snowflake's RBAC model?

Accepted Answer

Managing access control, including users and roles.. The SECURITYADMIN role is dedicated to the management of users, roles, and grants. By separating administrative duties into distinct roles like SECURITYADMIN (for access) and SYSADMIN (for objects), Snowflake enables a clear separation of concerns. This is a critical governance practice that prevents a single individual from having both the ability to create objects and the ability to assign permissions to those objects, thereby reducing the risk of unauthorized access.

Answer

Creating and managing virtual warehouses.

Answer

Granting ownership of data objects to users.

Answer

Monitoring account-level credit consumption.

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

How does Snowflake's architecture handle the storage and querying of semi-structured data like JSON to optimize performance?

Accepted Answer

Data is compressed and stored in a columnar format based on common paths.. Snowflake optimizes semi-structured data by automatically shredding it into an internal columnar format when stored in a VARIANT column. This allows the query engine to only read the specific paths or keys required by a query, similar to how it handles standard relational columns, leading to significantly better performance than traditional blob storage.

Answer

JSON is stored as a raw BLOB and parsed at execution time.

Answer

Individual keys are automatically extracted into a hidden relational schema.

Answer

Users must manually define a schema before JSON data can be queried efficiently.

Question 10

A data engineer needs to verify the structure and content of several CSV files staged in an internal Snowflake stage without actually loading the data into the production table or incurring significant compute costs. Which parameter should be used with the COPY INTO command to achieve this specific goal?

Accepted Answer

VALIDATION_MODE = RETURN_ERRORS. The VALIDATION_MODE parameter is specifically designed to parse staged files and return errors or data samples without performing an actual load. This prevents unnecessary data ingestion while allowing engineers to identify formatting issues or schema mismatches early in the development lifecycle. It is a cost-effective method to ensure data quality before executing a full production data pipeline.

Answer

ON_ERROR = ABORT_STATEMENT

Answer

PURGE = FALSE

Answer

FORCE = TRUE

Question 11

Refer to the exhibit. Based on the JSON configuration for this Snowflake listing, who will be able to discover and access this data?

Accepted Answer

Only the specific accounts 'ORG_A.ACCOUNT_1' and 'ORG_B.ACCOUNT_2'.. The exhibit defines a Private Listing. Private listings are not visible in the public Snowflake Marketplace. Instead, they are only discoverable by the specific accounts listed in the 'target_accounts' array. This allows providers to use the Marketplace interface and management tools to share data with specific partners securely, without making the offering public to the entire Snowflake community.

Answer

Any Snowflake customer in the same region as the provider.

Answer

Only the users within the provider's own Snowflake account.

Answer

All accounts belonging to ORG_A and ORG_B, regardless of the individual account names.

Question 12

What is the primary role of the 'ORGANIZATIONADMIN' role in Snowflake?

Accepted Answer

To perform account-level management across the organization.. The ORGANIZATIONADMIN (ORGADMIN) role is responsible for managing tasks at the account-level across the entire organization. This includes creating and managing multiple accounts, monitoring organizational usage, and configuring global replication. This role is highly privileged and exists above the ACCOUNTADMIN level, allowing for centralized control over the enterprise's Snowflake footprint while maintaining segregation between different business units or environments within the same organization.

Answer

To manage data access within a single account.

Answer

To query all data across the entire organization.

Answer

To assign roles to users within a single database.

Question 13

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 14

Which component of the Snowflake architecture is responsible for managing data integrity, query optimization, and transaction management?

Accepted Answer

Cloud Services Layer. The Cloud Services layer acts as the 'brain' of Snowflake. It handles all authentication, metadata management, access control, and query optimization. By centralizing these tasks, the architecture keeps the compute layer decoupled and stateless. This is fundamental to Snowflake's design, as it allows users to perform DDL and DML operations, maintain security, and optimize query plans without requiring management of the underlying hardware or software infrastructure.

Answer

Compute Layer

Answer

Storage Layer

Answer

Database Layer

Question 15

A provider wants to share data with a consumer using a Direct Share. Which two statements accurately describe the consumer's experience after the share is mounted? (Choose two.)

Accepted Answer

The consumer can view the share's objects without any additional storage cost for the shared data.. With Direct Sharing, the consumer mounts a read-only database and queries it using their own compute. Storage remains with the provider, so the consumer incurs no storage cost for the shared data. The consumer cannot modify shared objects or add objects inside the shared database, which distinguishes sharing from copying data.

Answer

The consumer can modify the shared data directly because they own the mounted database.

Answer

The consumer must copy the shared data into their own tables before querying it.

Answer

The consumer can create their own tables in the same schema as the shared objects.

Question 16

A company wants to enforce that all data in a specific schema is protected by a data classification tag before it can be queried by analysts. The security team has created a tag named DATA_CLASS and a masking policy associated with that tag. Analysts report they can still see raw values in some columns. What is the most likely cause?

Accepted Answer

The masking policy was created but not associated with the DATA_CLASS tag.. Tag-based masking requires the masking policy to be attached to the tag. Creating both objects without linking them leaves tagged columns unmasked. The fix is to associate the policy with the tag so that every column carrying the tag is automatically protected.

Answer

The masking policy uses CURRENT_ROLE() and the analysts are using a role that is not listed in the policy's allowed roles.

Answer

The analysts have been granted the APPLY MASKING POLICY privilege on the tag.

Answer

The DATA_CLASS tag was created in a different database than the schema being protected.

Question 17

A developer is building a Change Data Capture (CDC) pipeline using Snowflake. Which TWO features are required to ensure that only new or modified data is processed and that the processing logic runs automatically whenever data arrives?

Accepted Answer

Streams. Modern data pipelines in Snowflake rely on the synergy between Streams and Tasks to achieve efficient CDC. Streams provide the ability to track changes at the row level without manual versioning, while Tasks provide the scheduling and execution framework. Together, they allow for automated, incremental processing that reduces latency and optimizes resource consumption.

Answer

External Tables

Answer

Stored Procedures

Answer

Dynamic Tables

Question 18

For optimal parallel loading performance using a Snowflake virtual warehouse, what is the generally recommended compressed file size range for data files in a stage?

Accepted Answer

10MB to 100MB. Snowflake's architecture is optimized for parallel processing, where each execution thread in a warehouse can process a separate file. To maximize this parallelism and avoid overhead, it is recommended to aim for file sizes between 10MB and 100MB when compressed. This ensures that the workload is distributed evenly across all available compute nodes without overwhelming the system with metadata management.

Answer

1KB to 100KB

Answer

1GB to 5GB

Answer

Exactly 256MB to match HDFS blocks

Question 19

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 20

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