COF-C03 Practice Test 18 — 15 Questions

Question 1

When loading data into Snowflake using the COPY INTO command, what is the impact of using the 'STRIP_OUTER_ARRAY = TRUE' file format option for JSON files?

Accepted Answer

It allows Snowflake to load each element of a top-level array as a separate row.. Snowflake provides various transformation options during the ingestion process to simplify the data structure. The STRIP_OUTER_ARRAY option is particularly useful for JSON files where the entire content is wrapped in a single array. Removing this array allows Snowflake to treat each element as a separate record for ingestion.

Answer

It removes all square brackets from within the JSON data structure.

Answer

It converts semi-structured JSON arrays into a comma-separated string.

Answer

It improves performance by compressing the JSON file during the load.

Question 2

What is the primary function of the Snowflake 'Result Cache'?

Accepted Answer

It allows queries to return results without using compute resources.. The Result Cache stores the output of every query for 24 hours. When a subsequent query matches a previous one exactly (including the data being accessed), Snowflake returns the cached results immediately without using any compute resources. This provides massive performance benefits and cost savings for repeated queries, which are common in dashboards and reporting applications where the underlying data might not change frequently during the day.

Answer

It stores raw data files for faster retrieval.

Answer

It automatically scales compute resources.

Answer

It maintains the metadata of the database objects.

Question 3

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 4

What is the primary purpose of a 'Tag' in Snowflake from a data governance perspective?

Accepted Answer

To identify and track sensitive data for compliance.. Tags in Snowflake are used to label and categorize data objects for governance, reporting, and cost allocation. By applying a tag to a table or column, an administrator can track sensitive data across the entire account. This allows for automated reporting on data lineage and compliance, enabling teams to quickly identify where PII or other critical information resides for regulatory auditing and risk management purposes.

Answer

To increase query performance for joins.

Answer

To define the access level of a user.

Answer

To compress data for storage savings.

Question 5

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 6

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 7

Which of the following describes the correct order of precedence for role inheritance in Snowflake?

Accepted Answer

Parent roles inherit all privileges granted to their child roles.. Snowflake utilizes a hierarchical RBAC model where privileges are additive. When a role is granted to another role, the parent role inherits all privileges assigned to the child role. This design allows for the creation of functional roles that map to business units or job tasks, ensuring that permissions are managed efficiently and transparently throughout the entire organizational structure of the account.

Answer

Object-level privileges override role-level inheritance.

Answer

Privileges are inherited only from the ACCOUNTADMIN role.

Answer

Child roles always inherit the privileges of the parent role.

Question 8

Which Snowflake architecture feature enables seamless data sharing between two different Snowflake accounts without the need to copy or move data?

Accepted Answer

Secure Data Sharing. Secure Data Sharing relies on Snowflake's unique architecture where data is stored in a centralized location while being accessed by multiple compute environments. By granting permissions to an object, a provider can expose data to a consumer account. Because no data movement is required, the shared data is always up-to-date and reflects the current state of the provider's database, providing a real-time collaboration experience without the overhead of traditional ETL.

Answer

Database Replication

Answer

Zero-Copy Cloning

Answer

Virtual Private Snowflake

Question 9

Refer to the exhibit. Based on the configuration provided for the ANALYTICS_WH, how will Snowflake manage clusters when multiple users start submitting queries at the same time?

Accepted Answer

Snowflake will start a new cluster only if it estimates there is enough work for 6 minutes.. The Economy scaling policy prioritizes cost savings by waiting to start a new cluster until there is enough work to keep it busy for at least six minutes. This is different from the Standard policy, which starts clusters immediately to minimize queuing. This configuration is ideal for workloads where immediate response times are less critical than budget control.

Answer

A new cluster will start immediately as soon as a single query is queued.

Answer

The warehouse will immediately scale up to 4 clusters to handle the incoming load.

Answer

The warehouse will automatically increase its size from X-Small to Large.

Question 10

An administrator wants to ensure that a specific role can only access Snowflake from the corporate office IP range. Which tool should they use?

Accepted Answer

Network Policies.. Network Policies are the mechanism to restrict access by IP address. By creating a policy and applying it to a specific user or the entire account, administrators can ensure that connections only succeed from authorized locations. This is a foundational governance practice to protect against unauthorized access from external or insecure network locations, effectively creating a perimeter around the data platform.

Answer

Row Access Policies.

Answer

Granting specific network privileges.

Answer

Setting a Session Policy.

Question 11

A provider has a share containing a secure view that references a table in the same database. The provider wants the share to stop being visible to a specific consumer account but keep the share and its grants intact for other consumers. Which action accomplishes this?

Accepted Answer

Run ALTER SHARE ... REMOVE ACCOUNTS = <consumer_account> so only that account loses access while the share and its grants remain unchanged.. A share's consumer list is managed with ALTER SHARE. Using REMOVE ACCOUNTS for one account revokes that account's access while leaving the share definition and all object grants untouched, so remaining consumers continue working without interruption.

Answer

Run ALTER SHARE ... ADD ACCOUNTS = <consumer_account> with a REVOKE keyword to flip the account's access off.

Answer

Run REVOKE USAGE ON DATABASE FROM SHARE to detach the consumer account.

Answer

Run DROP SHARE <share_name> and recreate it immediately, re-adding every consumer except the one to be excluded.

Question 12

Snowflake's optimizer uses various techniques to improve query performance dynamically. Which TWO of the following are examples of Adaptive Query Optimization?

Accepted Answer

Dynamic Pruning. Adaptive Query Optimization refers to the engine's ability to adjust its execution plan based on real-time data characteristics observed during query processing. This includes techniques like dynamic pruning and join filtering, which allow the engine to bypass irrelevant data even when static metadata is insufficient.

Answer

Static partition pruning based on metadata

Answer

Manual Clustering Key assignment

Answer

Automatic Micro-partitioning

Question 13

Which THREE factors influence the performance of a Snowflake query? (Choose three)

Accepted Answer

The clustering of data in micro-partitions.. Performance in Snowflake is multidimensional, influenced by both user-driven configurations and the automated optimization processes inherent in the architecture. Key factors include the degree of data clustering, which dictates how much data must be scanned; the warehouse size, which provides the raw compute power for processing; and the efficiency of the SQL code, such as avoiding unnecessary operations. Understanding these allows administrators to balance cost and performance effectively in their Snowflake environment.

Answer

The amount of data in the result cache.

Answer

The number of users currently logged into the system.

Question 14

A data architect needs to ensure that data in Snowflake is encrypted at rest and in transit. Which feature provides the highest level of security by allowing the customer to manage their own encryption keys?

Accepted Answer

Tri-Secret Secure. Tri-Secret Secure combines Snowflake-managed keys with customer-managed keys stored in an external Key Management Service (KMS). This architecture provides an additional layer of protection, ensuring that even if Snowflake's internal infrastructure were compromised, the data remains encrypted without the customer-provided key. It is essential for highly regulated industries requiring absolute control over their data lifecycle and encryption keys.

Answer

Standard Snowflake encryption

Answer

Always Encrypted

Answer

Data Masking

Question 15

Which feature should a provider use if they need to allow a consumer to access data based on the consumer's specific identity or organizational attributes?

Accepted Answer

Row Access Policies. Row-level security, implemented via Row Access Policies, is the correct tool for filtering data dynamically based on user context. In a sharing context, the policy evaluates the current user's attributes at query time. This allows a single shared table to serve different consumers with customized views of the data, ensuring that each consumer only sees the records they are authorized to access without creating separate tables for every partner.

Answer

Standard Views

Answer

Data Masking Policies

Answer

Account-level Replication