COF-C03 Practice Test 5 — 10 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

When a virtual warehouse spills data to local disk, what does this indicate about the query and resource allocation?

Accepted Answer

The memory capacity of the warehouse nodes is insufficient.. Spilling to local disk occurs when the data required for an operation, such as a large join or sort, exceeds the memory capacity of the warehouse nodes. This significantly degrades performance because disk I/O is much slower than memory access. Recognizing this behavior is vital for performance tuning, as it signals that the current warehouse size is insufficient for the volume of data being processed, necessitating a larger warehouse or a more efficient query design.

Answer

The warehouse is too large.

Answer

The data is not properly clustered.

Answer

The result cache is full.

Question 7

A data engineer needs to transform semi-structured data stored in a VARIANT column that contains an array of JSON objects into a relational table with one row per object. The engineer wants to use a Snowflake function that can expand the array into multiple rows. Which function should be used?

Accepted Answer

FLATTEN. FLATTEN is the correct function to expand an array of objects into multiple rows. It is typically used in the FROM clause with LATERAL, allowing each element of the array to become a separate row while preserving access to the original row's columns. The other functions either create VARIANT values or aggregate rows, not expand arrays.

Answer

PARSE_JSON

Answer

OBJECT_CONSTRUCT

Answer

ARRAY_AGG

Question 8

A consumer has mounted a shared database named 'SALES_DATA_SHARED'. They need to add a new column to one of the tables in this database to store local annotations. Which statement best describes the outcome if they attempt this?

Accepted Answer

The operation will fail because shared databases are read-only for consumers.. Shared databases in Snowflake are strictly read-only for the consumer. The consumer cannot perform any Data Definition Language (DDL) operations like ALTER TABLE, nor any Data Manipulation Language (DML) operations like INSERT or UPDATE on the shared objects. This architecture ensures the integrity of the provider's data and maintains a single version of truth across all consumers.

Answer

The operation will succeed, but the changes will only be visible to the consumer's account.

Answer

The operation will succeed only if the provider has granted the 'MODIFY' privilege to the share.

Answer

The operation will fail unless the consumer is using the 'ACCOUNTADMIN' role.

Question 9

Which of the following describes the purpose of 'Object Tagging' in Snowflake?

Accepted Answer

To logically group objects for discovery and compliance reporting.. Object tagging allows administrators to assign metadata to objects (like tables, columns, or warehouses) to facilitate discovery, tracking, and governance. Tags are useful for identifying sensitive data, tracking costs, or enforcing compliance. This is a crucial feature for large organizations that need to report on data usage, lifecycle, and sensitivity across thousands of objects, making it easier to manage and audit data at scale.

Answer

To encrypt sensitive data at the column level.

Answer

To restrict user access based on their network location.

Answer

To automatically optimize query performance.

Question 10

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.