COF-C02 Snowflake SnowPro Core Certification Exam Free Practice Exam Questions (2025 Updated)

To ensure that the output will be a single file when unloading data into a stage, you should use the COPY option SINGLE=TRUE. This option specifies that the result of the COPY INTO command should be written to a single file, rather than multiple files.

In the Snowflake Data Exchange, the ability to get or request data from listings is generally controlled by specific roles and privileges:

A. Users with ACCOUNTADMIN role: This role typically has the highest level of access within a Snowflake account, including the ability to manage and access all features and functions. Users with this role can access data listings within the Data Exchange.

D. Users with import share privilege: This specific privilege is necessary for users who need to import shared data from the Data Exchange. This privilege allows them to request and access data listings explicitly shared with them.

Masking policies in Snowflake are created and applied at the column level. These policies are used to obfuscate sensitive data by masking the values in a specific column. Masking policies can enforce data privacy and protection measures by dynamically masking the data based on the role of the user querying the data.

When deciding to use a secure view, several considerations come into play, especially concerning security and performance:

A.No details of the query execution plan will be available in the query profiler: Secure views are designed to prevent the exposure of the underlying data and the view definition to unauthorized users. Because of this, the detailed execution plans for queries against secure views are not available in the query profiler. This is intended to protect sensitive data from being inferred through the execution plan.

C.Secure views do not take advantage of the same internal optimizations as standard views: Secure views, by their nature, limit some of the optimizations that can be applied compared to standard views. This is because they enforce row-level security and mask data, which can introduce additional processing overhead and limit the optimizer's ability to apply certain efficiencies that are available to standard views.

B.Once created, there is no way to determine if a view is secure or notis incorrect because metadata about whether a view is secure can be retrieved from the INFORMATION_SCHEMA views or by using theSHOW VIEWScommand.

D.It is not possible to create secure materialized viewsis incorrect because the limitation is not on the security of the view but on the fact that Snowflake currently does not support materialized views with the same dynamic data masking and row-level security features as secure views.

E.The view definition of a secure view is still visible to users by way of the information schemais incorrect because secure views specifically hide the view definition from users who do not have the privilege to view it, ensuring that sensitive information in the definition is not exposed.

USE SECONDARY ROLES ALL: Activates all granted secondary roles for the current session.

USE SECONDARY ROLES NONE: Deactivates all active secondary roles for the current session.

Incorrect Commands: The options referencing "RESUME", "SUSPEND", and "ADD" are not valid commands in the context of secondary roles.

Snowflake implements Discretionary Access Control (DAC) by using a role-based access control model. In this model, access privileges are not directly assigned to individual objects or users but are encapsulated within roles. These roles are then assigned to users, effectively granting them the access privileges contained within the role. This approach allows for granular control over database access, making it easier to manage permissions in a scalable and flexible manner.

The search optimization service in Snowflake uses persistent data structures known as search access paths to improve the performance of point lookups. These structures enable efficient retrieval of data by reducing the amount of data scanned during queries.

Search Access Paths:

Search access paths are special indexing structures maintained by the search optimization service.

They store metadata about the distribution of data within tables, enabling faster lookups for specific values.

Point Lookups:

Point lookups involve searching for a specific value within a column.

By leveraging search access paths, Snowflake can quickly locate the exact micro-partition containing the value, minimizing the amount of data scanned.

Performance Improvement:

The use of search access paths significantly reduces query execution time for point lookups.

This is especially beneficial for large tables where scanning all micro-partitions would be computationally expensive.

To provide a user with the necessary access to create a materialized view in a schema, the user must be granted a role that has the CREATE MATERIALIZED VIEW privilege on that schema. First, the role is granted to the user, and then the privilege is granted to the role

In Snowflake, a tabular User-Defined Function (UDF) is defined with a return clause that includes the keyword “TABLE.” This indicates that the UDF will return a set of rows, which can be used in the FROM clause of a query. References: Based on my internal knowledge as of 2021.

To allow secure views the ability to reference data in multiple databases, the REFERENCE_USAGE privilege must be granted on each database that contains objects referenced by the secure view2. This privilege is necessary before granting the SELECT privilege on a secure view to a share.

With the create data EXCHANGE listing privilege, a Snowflake user can modify the properties of listings and submit them for approval or publishing on the Data Exchange. This allows them to manage and share data sets with consumers effectively. References: Based on general data exchange practices in cloud services as of 2021.

 Multi-Factor Authentication (MFA) token caching is typically supported for clients that maintain a persistent connection or session with Snowflake, such as the ODBC driver and Python connector, to reduce the need for repeated MFA challenges. References: Based on general security practices in cloud services as of 2021.

The Snowflake feature that allows for small volumes of data to be continuously loaded into Snowflake and incrementally made available for analysis is Snowpipe. Snowpipe is designed for near-real-time data loading, enabling data to be loaded as soon as it’s available in the storage layer3

 The maximum Time Travel retention period for a transient table in Snowflake is 1 day. This is the default and maximumduration for which Snowflake maintains the historical data for transient tables, allowing users to query data as it appeared at any point within the past 24 hours2.

 Snowflake reorganizes data into its internal optimized, compressed, columnar format in the Database Storage layer. This process is part of how Snowflake manages data storage, ensuring efficient data retrieval and query performance

The USERADMIN role in Snowflake has the ability to create and manage users and roles within the Snowflake environment. This role is specifically dedicated to user and role management and creation

Query parsing and compilation in Snowflake occur within the cloud services layer. This layer is responsible for various management tasks, including query compilation and optimization

The cloud services layer of Snowflake architecture handles various aspects including security functions, authentication of user sessions, and access control, ensuring that only authorized users can access the data and services23.

During a query on a virtual warehouse, the columns in the result set of the query are cached. This allows for faster retrieval of data if the same or a similar query is run again, as the system can retrieve the data from the cache rather than reprocessing the entire query. References: [COF-C02] SnowPro Core Certification Exam Study Guide

 When a view is defined on a permanent table, and a temporary table with the same name is created in the same schema, the query from the view will return the data from the permanent table. Temporary tables are session-specific and do not affect the data returned by views defined on permanent tables2.

 The OBJECT_CONSTRUCT function is used in Snowflake to create a JSON object from relational data, which is useful when unloading semi-structured data. The SINGLE = TRUE parameter is used when unloading data to ensure that the data is exported as a single file, which can be up to 5 GB in size. References: [COF-C02] SnowPro Core Certification Exam Study Guide

 Periodic rekeying of micro-partitions is a feature that requires the Enterprise Edition of Snowflake or higher. This feature is part of Snowflake’s comprehensive approach to encryption key management, ensuring data security through regular rekeying1. References: [COF-C02] SnowPro Core Certification Exam Study Guide

When unloading data to an external stage, the maximum file size supported is 5 GB. This limit ensures efficient data transfer and management within Snowflake’s architecture

 A secure view in Snowflake is exposed only to users with the OWNERSHIP privilege. This privilege ensures that only authorized users who own the view, or roles that include ownership, can access the secure view

 The reader account compute will be billed to the provider account. Very Comprehensive Explanation: In Snowflake, when a provider creates a reader account for a consumer who does not have a Snowflake account, the compute resources used by the reader account are billed to the provider’s account. This allows the consumer to query the shared data without incurring any costs. References: [COF-C02] SnowPro Core Certification Exam Study Guide

Clones in Snowflake have the advantage of being created almost instantly and saving space by not duplicating storage. This is due to Snowflake’s zero-copy cloning feature, which allows for the creation of object clones without the additional storage costs typically associated with data duplication23. Clones are independent of the original table and do not stay in sync with it, nor do they inherently have better query performance. However, they do inherit the time travel history from the original table at the time of cloning

 Snowflake’s secure data sharing feature allows sharing of certain database objects with other Snowflake accounts. Amongthe options provided, external tables and secure UDFs can be shared