COF-C03 Practice Test 2 — 25 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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

Question 16

How does Snowflake's architecture provide the ability to run multiple concurrent workloads without resource contention?

Accepted Answer

By deploying separate virtual warehouses for each workload.. Snowflake's multi-cluster warehouse architecture allows for complete isolation of compute resources. Each workload can be assigned to a specific virtual warehouse, ensuring that one workload's resource usage does not impact the performance of another. This decoupling is a cornerstone of the Snowflake AI Data Cloud, enabling organizations to handle diverse workloads—from heavy data engineering to real-time analytics—simultaneously without the risk of 'noisy neighbor' scenarios affecting user experience.

Answer

By using a single large warehouse for all workloads.

Answer

By relying on the underlying cloud provider's hypervisor.

Answer

By implementing software-based resource queues in the storage layer.

Question 17

A data engineer needs to ensure that sensitive PII columns are masked for all users except for a specific group of HR analysts. Which Snowflake feature is the most efficient and scalable solution to implement this requirement?

Accepted Answer

Implement Dynamic Data Masking policies on the sensitive columns.. Dynamic Data Masking (DDM) allows centralized management of data access policies based on the user's role. By attaching a masking policy to a column, Snowflake automatically evaluates the user's role at query runtime. This approach is superior to static masking or manual view management because it centralizes governance, minimizes data duplication, and ensures that security policies remain consistent regardless of how the user accesses the underlying table.

Answer

Create secure views for every user role in the account.

Answer

Use the UNMASK function in every query selecting sensitive data.

Answer

Apply Row-Level Security to hide rows containing sensitive PII.

Question 18

Which administrative role should be used to manage the lifecycle of warehouses and databases, while strictly avoiding the management of users and roles?

Accepted Answer

SYSADMIN. The SYSADMIN role is the standard role for creating and managing data objects such as databases, schemas, tables, and warehouses. It is distinct from the SECURITYADMIN role, which manages user identities and permissions. This separation of duties is a fundamental pillar of Snowflake's security architecture, ensuring that operational and administrative control over data objects is kept separate from the management of user access, thereby mitigating the risk of privilege escalation and internal misuse.

Answer

ACCOUNTADMIN

Answer

USERADMIN

Answer

SECURITYADMIN

Question 19

An administrator needs to restrict access to sensitive PII data. Which TWO of the following are valid approaches to implement governance in Snowflake?

Accepted Answer

Apply a Row Access Policy to the table.. Snowflake provides several layers of defense-in-depth to secure sensitive information. Row Access Policies filter which rows a user can see, while Dynamic Data Masking transforms column data based on user privileges. Using these in combination allows architects to build a highly restrictive environment where users only interact with the exact data subsets and column values they are authorized to access, complying with strict regulatory data privacy standards.

Answer

Use physical data partitioning to store PII in separate tables.

Answer

Assign the ACCOUNTADMIN role to all data stewards.

Answer

Export data to an encrypted S3 bucket for security.

Question 20

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.