Free Snowflake COF-C03 Practice Exam with Questions & Answers | Set: 5

In Snowflake streams, theMETADATASACTIONcolumn indicates the type of data manipulation operation that has occurred. The possible values includeINSERTandDELETE.

INSERT:Indicates that a new row has been inserted into the table.

DELETE:Indicates that a row has been deleted from the table.

The GET command is used to download (i.e., unload) files from a Snowflake internal stage to the local file system . COPY INTO writes to stages, not directly to local disk.

The correct answer is D. USAGE on the parent database and schemas and SELECT on the relevant tables or views .

To apply a masking policy to a table or view column, a role needs the ability to reference the masking policy and the target object. The APPLY privilege on the masking policy allows the role to apply that policy. However, the role must also have access to the object hierarchy and the relevant table or view.

Why D is correct:

The user already has APPLY on the masking policy. In addition, the role needs:

USAGE ON DATABASE < database_name >

USAGE ON SCHEMA < schema_name >

SELECT ON TABLE OR VIEW < object_name >

This allows the user to access the parent database and schema and apply the masking policy to relevant columns in the table or view.

Why the other options are incorrect:

A. SELECT alone is not enough because the user also needs USAGE on the parent database and schema.

B. USAGE on the parent database and schemas is not enough because the user also needs privileges on the relevant table or view.

C. This omits USAGE on the parent database, which is required to resolve the object path.

Official Snowflake documentation reference:

Snowflake documentation explains that to set or unset a masking policy on a column, a role needs the APPLY privilege on the masking policy, plus sufficient privileges on the target object and its containing database and schema.

Snowflake recommends automating key pair rotation to improve security and comply with industry best practices. SSH key pairs are used for key-pair authentication with clients like SnowSQL or Snowpipe.

Snowflake allows users to write custom application logic in JavaScript for two types of objects: Stored Procedures and User-Defined Functions (UDFs).

Stored Procedures:Snowflake stored procedures can be written in JavaScript to encapsulate complex business logic and procedural operations.

CREATE OR REPLACE PROCEDURE my_procedure()

RETURNS STRING

LANGUAGE JAVASCRIPT

EXECUTE AS CALLER

AS

$$

// JavaScript logic here

$$;

User-Defined Functions (UDFs):Snowflake UDFs can be written in JavaScript to perform custom calculations or operations on data.

CREATE OR REPLACE FUNCTION my_function(x FLOAT)

RETURNS FLOAT

LANGUAGE JAVASCRIPT

AS

$$

return x * 2;

$$;

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 B. The role will lose access to objects that are not granted to the updated role .

Snowflake access control is based on privileges granted to roles. Users receive access to objects through the active role and any inherited roles in the role hierarchy. If privileges are revoked from a role, that role no longer has those privileges unless they are still inherited through another granted role.

Why B is correct:

When privileges are removed from a role, users using that role lose the ability to perform actions that depended on the revoked privileges. Access is controlled by the current set of grants assigned to the role.

Why the other options are incorrect:

A. A role does not retain revoked privileges until a new session starts. Revoked privileges affect access according to Snowflake access control rules.

C. The user does not need to reissue USE ROLE just because privileges were revoked.

D. Snowflake does not terminate active sessions simply because a privilege is removed from a role.

Official Snowflake documentation reference:

Snowflake documentation explains that privileges are granted to roles, roles are granted to users or other roles, and revoking privileges removes the ability to perform the associated actions unless access exists through another role path.

Parquet is a columnar storage file format that is optimized for efficiency in both storage and processing. It supports compression and encoding schemes that significantly reduce the storage space needed and speed up data retrieval operations, making it ideal for handling large volumes of data. Unlike JSON or TSV, which are row-oriented and typically uncompressed, Parquet is designed specifically for use with big data frameworks, offering advantages in terms of performance and cost when storing and querying semi-structured data.

Snowflake ' s access control framework utilizes a combination of Discretionary Access Control (DAC) and Role-based Access Control (RBAC). DAC in Snowflake allows the object owner to grant access privileges to other roles. RBAC involves assigning roles to users and then granting privileges to those roles. Through roles, Snowflake manages which users have access to specific objects and what actions they can perform, which is central to security and governance in the Snowflake environment.

A key consideration for Data Clean Rooms is account and region compatibility. Participating accounts generally need to be in supported regions for the clean room configuration and collaboration model.

Important note: Data Clean Rooms have evolved quickly, and regional/cross-cloud support can change. I cannot live-verify the newest Snowflake restrictions here, but this is the best SnowPro-style answer from the provided options.

The correct answer is B. DROP PROCEDURE SALES_REPORT(INT, INT); .

In Snowflake, stored procedures can be overloaded. This means multiple procedures can have the same name but different argument signatures. Because of this, when dropping a stored procedure, the procedure name and argument data types are used to identify the exact procedure to remove.

Why B is correct:

The procedure was created with two arguments:

MONTH INT, YEAR INT

When dropping the procedure, Snowflake uses the argument data types, not the argument names. Therefore, the correct statement is:

DROP PROCEDURE SALES_REPORT(INT, INT);

Why the other options are incorrect:

A. The argument names are not included in the drop signature. Snowflake expects the argument data types.

C. This includes only argument names without data types, which is not the correct procedure signature syntax.

D. This does not identify the procedure signature and is not sufficient when dropping a stored procedure.

Official Snowflake documentation reference:

Snowflake documentation for DROP PROCEDURE explains that the procedure name and argument data types are required to identify the stored procedure to drop.

The search optimization service in Snowflake enhances query performance by maintaining a persistent data structure. This structure indexes the values of table columns across micro-partitions, allowing Snowflake to quickly identify which micro-partitions contain relevant data for a query. By efficiently narrowing down the search space, this service reduces the amount of data scanned during query execution, leading to faster response times and more efficient use of resources.

The correct answers are B. CURRENT_ROLE() and E. CURRENT_USER() .

Corrected typos:

The original options included typing errors:

C. CURRENT_ RCT.FTY^O should be CURRENT_REGION()

D. CURRENT _SESSION {) should be CURRENT_SESSION()

When a secure view is shared with another Snowflake account, Snowflake protects consumer identity and role details in certain contexts. As a result, functions that expose user or role identity can return NULL.

Why B is correct:

CURRENT_ROLE() can return NULL in a secure view shared with another account because the provider account should not receive or depend on the consumer’s active role identity.

Why E is correct:

CURRENT_USER() can return NULL in a secure view shared with another account because the provider should not be able to see the consumer’s user identity through the shared secure view.

Why the other options are incorrect:

A. CURRENT_ACCOUNT_NAME() returns account-related context and is not one of the commonly tested secure data sharing functions that returns NULL in this scenario.

C. CURRENT_REGION() returns region context and is not the best answer here.

D. CURRENT_SESSION() returns session information and is not the commonly tested pair for secure shared views.

Official Snowflake documentation reference:

Snowflake documentation explains that in secure views shared with consumer accounts, certain context functions such as CURRENT_USER and CURRENT_ROLE return NULL to protect consumer information.