Pass the Snowflake SnowPro Core Certification COF-C03 Questions and answers with Dumpstech

The correct answer is B. Only one refresh will run at a time .

Failover groups can be refreshed on a schedule to replicate supported account objects and database objects to target accounts. Snowflake does not run overlapping refresh operations for the same failover group.

Why B is correct:

If a scheduled refresh is still running, Snowflake does not start another concurrent refresh for the same failover group. Refresh operations are managed so that only one refresh runs at a time.

Why the other options are incorrect:

A. Multiple refreshes for the same failover group do not run concurrently.

C. Refreshes still require the appropriate privileges and configured replication/failover group permissions.

D. A delayed or long-running refresh does not mean the next refresh is automatically canceled in the way stated. The key behavior is that refreshes do not overlap.

Official Snowflake documentation reference:

Snowflake documentation for replication and failover groups explains that scheduled refreshes are managed by Snowflake and that refresh operations do not run concurrently for the same group.

The correct answer is A. Multi-Factor Authentication, or MFA .

Multi-Factor Authentication reduces the risk of relying only on a username and password. Even if a password is compromised, MFA requires an additional verification factor before the user can access Snowflake.

Why A is correct:

MFA strengthens password-based authentication by requiring an extra factor, such as a push approval, passcode, or other verification method. Snowflake’s native MFA is powered by Duo Security.

Why the other options are incorrect:

B. Standard authentication usually refers to username and password authentication, which is exactly the risk the question wants to reduce.

C. Snowflake OAuth is an authorization/authentication integration method, but MFA is the direct feature designed to reduce password-only risk.

D. External OAuth can centralize authentication through an external identity provider, but the best Snowflake security feature for reducing password authentication risk is MFA.

Official Snowflake documentation reference:

Snowflake documentation describes MFA as an additional layer of security for user login and explains that Snowflake MFA is powered by Duo Security.

In Snowflake, the ACCOUNTADMIN role is required to create resource monitors. Resource monitors are used to manage and monitor the consumption of compute resources. The ACCOUNTADMIN role has the necessary privileges to create, configure, and manage resource monitors across the account.

Snowflake Documentation: Resource Monitors

The correct answer is D. By identifying where column-level values are located in each micro-partition .

The Search Optimization Service improves query performance for highly selective queries. It works by creating and maintaining a persistent data structure called a search access path .

The search access path records information about where specific values are located within the table’s micro-partitions. This allows Snowflake to find qualifying rows more efficiently without scanning unnecessary micro-partitions.

Why D is correct:

The search access path helps Snowflake identify which micro-partitions contain column values that match a query predicate. This improves pruning and can speed up selective point lookup queries.

Why the other options are incorrect:

A. Search optimization does not create a real-time index for every database. It is enabled on selected tables and columns.

B. A catalog of tables is not the purpose of the search access path.

C. Query history tracks executed queries. Search optimization does not work by listing previous queries.

Official Snowflake documentation reference:

Snowflake documentation describes the search access path as a persistent data structure maintained by the Search Optimization Service. It helps Snowflake determine which micro-partitions contain values needed by selective queries.

The Activity area of Snowsight, Snowflake ' s web interface, allows users to perform several important tasks related to query management and performance analysis. Among the options provided, the correct ones are:

B. Explore each step of an executed query:Snowsight provides detailed insights into query execution, including the ability to explore the execution plan of a query. This helps users understand how a query was processed, identify performance bottlenecks, and optimize query performance.

C. Monitor queries executed by users in an account:The Activity area enables users to monitor the queries that have been executed by users within the Snowflake account. This includes viewing the history of queries, their execution times, resources consumed, and other relevant metrics.

These features are crucial for effective query performance tuning and ensuring efficient use of Snowflake ' s resources.

In Snowflake, the term " privilege " refers to a defined level of access to an object. Privileges are specific actions that roles can perform on securable objects in Snowflake, such as tables, views, warehouses, databases, and schemas. These privileges are granted to roles and can be further granted to users through their roles, forming the basis of Snowflake’s access control framework.

When attempting to load large files, such as a 125 GB file, into Snowflake using SnowSQL, the process might encounter limitations related to the maximum execution time for queries or data loading operations. If the loading process exceeds this time limit (typically around 24 hours), it could be aborted without committing any part of the file to the database. This behavior is designed to prevent indefinite resource consumption and to maintain system stability, emphasizing the need for optimizing data load operations, possibly through file segmentation or parallel loading strategies.

The correct answer is D. To unload a single file from a table into a stage .

The COPY INTO < location > command is used to unload data from Snowflake to a stage. When the SINGLE = TRUE option is included, Snowflake attempts to unload the query or table result into a single output file instead of multiple files.

Why D is correct:

SINGLE = TRUE is specifically an unload option for COPY INTO < location > . It is useful when a downstream process expects one output file.

Example:

COPY INTO @my_stage/export/customers.csv

FROM customers

FILE_FORMAT = (TYPE = CSV)

SINGLE = TRUE;

Why the other options are incorrect:

A. Preventing duplicate loads is handled by Snowflake load metadata and the FORCE option, not SINGLE = TRUE.

B. Loading an individual file is done with COPY INTO < table > and a file path or the FILES option, not COPY INTO < location > SINGLE = TRUE.

C. “Parse the results into single stages” is not a Snowflake unload concept.

Official Snowflake documentation reference:

Snowflake documentation for COPY INTO < location > describes SINGLE as the option that specifies whether to generate a single file or multiple files when unloading data.

The correct answer is C. Queries will continue running, using the current warehouse size .

When a virtual warehouse is resized while it is running, the resize does not affect queries that are already in progress. Running queries continue using the warehouse resources that were available when they started. The new warehouse size applies to queries that start after the resize operation.

Why C is correct:

Snowflake does not cancel or restart existing queries when a warehouse is resized. In-progress queries continue executing with the current warehouse size.

Why the other options are incorrect:

A. Resizing a warehouse does not automatically cancel in-progress queries.

B. In-progress queries do not switch to the new warehouse size.

D. Queries are not paused and reprocessed after resizing.

Official Snowflake documentation reference:

Snowflake documentation explains that resizing a running warehouse affects only new queries and queued queries. Currently running queries continue to use the warehouse size that was in effect when they started.

In Snowflake, a role is an access control entity that can be created as part of a hierarchy within an account. Roles are used to grant and manage privileges in a structured and scalable manner.

Understanding Roles:

Roles are logical entities that group privileges together.

They are used to control access to securable objects like tables, views, warehouses, and more.

Role Hierarchy:

Roles can be organized into a hierarchy, allowing for the inheritance of privileges.

A role higher in the hierarchy (parent role) can grant its privileges to a lower role (child role), simplifying privilege management.

Creating Roles:

Roles can be created using the CREATE ROLE command.

You can define parent-child relationships by granting one role to another.

Example Usage:

CREATE ROLE role1;

CREATE ROLE role2;

GRANT ROLE role1 TO role2;

In this example,role2inherits the privileges ofrole1.

Benefits:

Simplifies privilege management: Hierarchies allow for efficient privilege assignment and inheritance.

Enhances security: Roles provide a clear structure for managing access control, ensuring that privileges are granted appropriately.

Snowflake Documentation: Access Control in Snowflake

Snowflake Documentation: Creating and Managing Roles

When unloading data using the COPY INTO < location > command, Snowflake offers options to compress files. The default behavior is governed by the COMPRESSION parameter. Setting COMPRESSION = AUTO allows Snowflake to automatically use its default compression algorithm, which is GZIP for most formats like CSV.

The correct answer is A. View.

In Snowflake, a SQL User-Defined Function can reference certain database objects in a SQL expression or query expression. A view can be referenced by a SQL UDF, subject to normal privilege and ownership rules.

Why A is correct:

A UDF can include SQL logic that references a view. This allows reusable logic to be built on top of existing query definitions.

Example concept:

CREATE FUNCTION my_function()

RETURNS NUMBER

AS

$$

SELECT COUNT(*) FROM my_view

$$;

Why the other options are less appropriate:

B. A stream is a change tracking object and is not the standard object referenced from a UDF for reusable query logic.

C. An external table is used to query data stored in external cloud storage. It is not the best answer for this SnowPro-style question.

D. A materialized view is a precomputed view-like object, but the standard documented answer for an object referenced from a UDF is a view.

Official Snowflake documentation reference:

Snowflake documentation for SQL UDFs explains that SQL UDF definitions can contain SQL expressions and query expressions. These query expressions can reference database objects such as views, depending on privileges and valid SQL usage.

The correct answer is C. A new cluster will be started.

A multi-cluster warehouse in Auto-scale mode can automatically start additional clusters when query concurrency increases. This allows Snowflake to handle more concurrent users and queries without forcing all queries to wait on a single cluster.

Why C is correct:

When the current cluster does not have enough capacity to handle the number of concurrent queries, Snowflake can start another cluster, up to the configured maximum cluster count. This helps reduce queuing and improves concurrency.

Why the other options are incorrect:

A. Queued queries are not dropped simply because concurrency exceeds the current cluster’s capacity.

B. The warehouse does not shut down because of high concurrency.

D. Queries are not automatically redirected to another warehouse. Multi-cluster scaling occurs within the same virtual warehouse.

Official Snowflake documentation reference:

Snowflake documentation explains that multi-cluster warehouses can automatically start and stop additional clusters to manage query concurrency. In Auto-scale mode, Snowflake starts additional clusters as needed, up to the maximum number configured for the warehouse.

In Snowflake, aShareis the object that contains all the information required to share a database with other Snowflake accounts. Shares are used to securely share data stored in Snowflake tables and views, enabling data providers to grant data consumers access to their datasets without duplicating data. When a database is shared, it can include one or more schemas, and each schema can contain tables, views, or both.