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

The minimum Snowflake edition that supports database replication is the Enterprise edition. Database replication allows data to be replicated between different Snowflake accounts or regions, providing high availability and disaster recovery capabilities.

Snowflake Documentation: Database Replication

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.

In Snowflake, network policies define the allowed IP address ranges from which users can connect to Snowflake, enhancing security by restricting access based on network location. The creation and management of network policies require sufficient privileges. Specifically, a user with theSECURITYADMINrole or any role with higher privileges, such asACCOUNTADMIN, can create network policies. Additionally, a custom role can be granted theCREATE NETWORK POLICYprivilege, enabling users assigned to that role to also create network policies. This approach allows for flexible and secure management of network access to Snowflake.

In Snowflake, permissions for accessing database objects, including tables, are not granted directly to users but rather to roles. A role encapsulates a collection of privileges on various Snowflake objects. Users are then granted roles, and through those roles, they inherit the permissions necessary to read a table or perform other actions. This approach adheres to the principle of least privilege, allowing for granular control over database access and simplifying the management of user permissions.

The correct answer is A. SECURITYADMIN .

In Snowflake’s default role hierarchy, the SECURITYADMIN role is responsible for managing security-related objects and grants. This role is granted the global MANAGE GRANTS privilege, which allows it to manage object grants across the account.

Why A is correct:

SECURITYADMIN is the built-in role designed to manage users, roles, and grants. By default, it has the ability to grant and revoke privileges.

Why the other options are incorrect:

B. USERADMIN is used primarily for user and role management, but it does not have the same broad default grant management authority as SECURITYADMIN.

C. SYSADMIN is typically used to create and manage account objects such as warehouses, databases, schemas, and tables, but it is not the default security grant administrator.

D. PUBLIC is automatically granted to every user and should not be used for administrative grant management.

Official Snowflake documentation reference:

Snowflake documentation describes SECURITYADMIN as the role that manages grants globally and inherits the privileges of USERADMIN. It includes the MANAGE GRANTS privilege by default.

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 answers are A. SINGLE and C. MAX_FILE_SIZE .

When unloading data from Snowflake using COPY INTO < location > , Snowflake can create one or more output files. Some options influence how many files are generated.

Why A is correct:

The SINGLE option specifies whether Snowflake generates a single output file or multiple output files. When SINGLE = TRUE, Snowflake unloads data into a single file, subject to command and file size behavior.

Why C is correct:

The MAX_FILE_SIZE option specifies the maximum size of each file generated during unloading. Smaller maximum file sizes can result in more files, while larger maximum file sizes can result in fewer files.

Why the other options are incorrect:

B. RECORD_DELIMITER controls how records are separated inside the output file. It does not control how many files are created.

D. INCLUDE_QUERY_ID controls whether the query ID is included in unloaded file names. It affects naming behavior, not the number of files generated.

E. DETAILED_OUTPUT controls the level of output information returned by the command. It does not control the number of unloaded files.

Official Snowflake documentation reference:

Snowflake documentation for COPY INTO < location > describes SINGLE as controlling whether a single file is generated and MAX_FILE_SIZE as controlling the maximum size of unloaded files.

The correct answer is C. To ensure the same sample is returned each time a query is run .

The REPEATABLE keyword is used with a seed value to make sampling deterministic. This means Snowflake can return the same sample when the query is rerun with the same seed and the underlying data has not changed.

Why C is correct:

SELECT *

FROM table_A

SAMPLE BERNOULLI (10) REPEATABLE (123);

This returns a repeatable sample based on the seed value 123.

Why the other options are incorrect:

A. Returning a fixed number of rows uses SAMPLE ( < n > ROWS), not REPEATABLE.

B. REPEATABLE does not remove duplicates.

D. Block sampling is controlled with BLOCK or SYSTEM, not REPEATABLE.

Official Snowflake documentation reference:

Snowflake documentation explains that REPEATABLE or SEED specifies a seed value to make sample results repeatable.

Session policies allow account administrators to define rules such as session timeout , requiring users to re-authenticate after inactivity . This is different from password or network policies, which manage login credentials and IP filtering.

To handle a large number of complex queries, configuring the warehouse in auto-scale mode by increasing the number of warehouse clusters is recommended. This setup allows Snowflake to dynamically add clusters as demand increases, ensuring better performance and concurrency. Increasing the number of clusters provides scalability for concurrent users and heavy workloads, improving response times without impacting individual query performance.

In Snowflake, if a table is accidentally dropped but still within the data retention period (also known as " Time Travel " ), the simplest and most efficient recovery method is the UNDROP command. This command restores the deleted table to its previous state with minimal operational effort. Since Snowflake retains dropped table data for a specific retention period (up to 90 days for the Enterprise edition), UNDROP can quickly recover the table without the need for complex cloning or data reloading processes, making it ideal for accidental deletions.

The correct answer is B. Hybrid .

A hybrid table is Snowflake’s table type designed for transactional workloads. Hybrid tables support row-level operations and are optimized for low-latency, high-concurrency transactional access patterns.

Why B is correct:

Hybrid tables are intended for transactional and operational workloads, such as applications that require fast point lookups, inserts, updates, and deletes.

Why the other options are incorrect:

A. Event tables store event and log data generated by Snowflake features such as Snowpark logging and tracing.

C. Dynamic tables are used for automated data transformation pipelines. They are not optimized for transactional workloads.

D. Permanent tables are the standard Snowflake table type for persistent analytical storage, but they are not specifically optimized for transactional workloads like hybrid tables.

Official Snowflake documentation reference:

Snowflake documentation describes hybrid tables as Snowflake tables optimized for transactional workloads that require low-latency reads and writes.