Free Snowflake COF-C02 Practice Exam with Questions & Answers | Set: 8

Resource monitors are used to track and control credit usage . They can trigger notifications or suspend compute resources when thresholds are exceeded.

The correct answer is B. To track DML changes that have been made to tables .

A stream is a Snowflake object used for change data capture. It records changes made to a source table so those changes can be consumed later by SQL logic, usually as part of an incremental processing pipeline.

Why B is correct:

Streams track DML changes such as:

INSERT

UPDATE

DELETE

They also expose metadata columns such as:

METADATA$ACTION

METADATA$ISUPDATE

METADATA$ROW_ID

These columns help identify the type of change and the affected row.

Why the other options are incorrect:

A. Tasks execute scheduled SQL and can be used to run data pipelines. Streams track the changes consumed by those pipelines.

C. File formats define how staged files are interpreted during loading or unloading. Streams are unrelated to file format definitions.

D. Caching is handled by Snowflake result cache and warehouse cache mechanisms, not by streams.

Official Snowflake documentation reference:

Snowflake documentation describes streams as objects that record DML changes made to tables, including inserts, updates, and deletes.

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 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.

In Snowflake, the parameter strip_outer_array = true can be set in the COPY INTO < table > command to remove the outer array structure from a semi-structured file. This parameter is useful for JSON files or similar data formats where an array might wrap the data, allowing Snowflake to directly load the inner elements as table rows.

The correct answer is A. Use the OBJECT_CONSTRUCT function with the COPY INTO < location > command .

When unloading relational data to JSON, each relational row must be transformed into a single semi-structured object. OBJECT_CONSTRUCT creates a JSON-style object from the row’s columns, and COPY INTO < location > unloads the result to a stage.

Why A is correct:

Snowflake requires unloaded JSON data to be represented as a single VARIANT, OBJECT, or ARRAY value per row. OBJECT_CONSTRUCT converts relational columns into an object suitable for JSON unload.

Example:

COPY INTO @my_stage/output/

FROM (

SELECT OBJECT_CONSTRUCT(*)

FROM customers

)

FILE_FORMAT = (TYPE = JSON);

Why the other options are incorrect:

B. Setting the file format to JSON alone is not sufficient for relational rows with multiple columns. The rows must be converted into a single semi-structured column.

C. PUT uploads local files to an internal stage. It does not unload table data.

D. GET downloads files from an internal stage to a local machine. It does not unload table data from Snowflake.

Official Snowflake documentation reference:

Snowflake documentation explains that to unload relational data to JSON, the query should return a single column containing semi-structured data, commonly produced with OBJECT_CONSTRUCT.

The role that can by default create resource monitors in Snowflake is theACCOUNTADMINrole. Resource monitors are a crucial feature in Snowflake that allows administrators to track and control the consumption of compute resources, ensuring that usage stays within specified limits. The creation and management of resource monitors involve defining thresholds for credits usage, setting up notifications, and specifying actions to be taken when certain thresholds are exceeded.

Given the significant impact that resource monitors can have on the operational aspects and billing of a Snowflake account, the capability to create and manage them is restricted to theACCOUNTADMINrole. This role has the broadest set of privileges in Snowflake, including the ability to manage all aspects of the account, such as users, roles, warehouses, databases, and resource monitors, among others.

Temporary and transient tables in Snowflake share several characteristics, notably, neither table type has a Fail-safe period. Fail-safe is a feature that provides additional data protection beyond the Time Travel period. However, this feature does not apply to temporary or transient tables. Additionally, for both types of tables, the data retention period effectively ends when the tables are dropped. This means that once these tables are deleted, their data is not recoverable, distinguishing them from permanent tables, which benefit from Snowflake ' s Time Travel and Fail-safe features.

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.

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.

Snowflake Documentation: COPY INTO < location >

When using the SAMPLE clause in a query, if the seed is not set, Snowflake will use a different random seed for each execution of the query. This results in different rows being sampled each time, leading to different results. Setting a seed ensures that the same rows are sampled each time the query is run.

Snowflake Documentation: Sampling

The correct answers are C. Data is not transferred between accounts and D. Consumer account will incur compute costs .

Important correction:

The provided answer A, B is not correct.

Secure Data Sharing allows data providers to share selected database objects with consumer accounts without copying or transferring the underlying data. Consumers query the shared data using their own virtual warehouses, so the consumer generally pays for the compute used to query the share.

Why C is correct:

With Secure Data Sharing, the provider’s data remains in the provider account. Snowflake shares metadata and access, not a physical copy of the data.

Why D is correct:

Consumers use their own compute resources to query shared data. Therefore, the consumer account incurs compute costs when querying the share.

Why the other options are incorrect:

A. Regular, non-secure views generally cannot be shared in the standard secure sharing model. Views shared with consumers should be secure views, unless specific account-level settings allow non-secure objects. The general SnowPro Core concept is that secure views are used to share view logic safely.

B. Sensitive data is not automatically masked simply because data is shared. Masking policies or secure views must be designed and applied intentionally.

E. Reader accounts can query shared data but cannot edit the provider’s source data.

Official Snowflake documentation reference:

Snowflake documentation explains that Secure Data Sharing does not copy or move data between accounts. Consumers use their own warehouses to query shared data, while providers retain control of the shared objects.

When creating a CSV file format in Snowflake and the requirement is to wrap columns by single quotes or double quotes, theFIELD_OPTIONALLY_ENCLOSED_BYparameter should be used in the file format specification. This parameter allows you to define a character (either a single quote or a double quote) that can optionally enclose each field in the CSV file, providing flexibility in handling fields that contain special characters or delimiters as part of their data.

TheACCESS_HISTORYview in Snowflake is a powerful tool for enhancing data governance, especially concerning monitoring and auditing data access patterns for both read and write operations. The key ways in whichACCESS_HISTORYenhances overall data governance are:

B. Provides a unified picture of what data was accessed and when it was accessed:This view logs details about query executions, including the objects (tables, views) accessed and the timestamps of these accesses. It ' s instrumental in auditing and compliance scenarios, where understanding the access patterns to sensitive data is critical.

E. Determines whether a given row in a table can be accessed by the user by filtering the data based on a given policy:While this option is a bit of a misinterpretation of whatACCESS_HISTORYdirectly offers, it indirectly supports data governance by providing the information necessary to analyze access patterns. This analysis can then inform policy decisions, such as implementing Row-Level Security (RLS) to restrict access to specific rows based on user roles or attributes.

ACCESS_HISTORYdoes not automatically apply data masking or tag columns with personal information. However, the insights derived from analyzingACCESS_HISTORYcan be used to identify sensitive data and inform the application of masking policies or other security measures.

Resource monitor schedules cannot be reset via update. The only way to restore the default schedule is to drop and recreate the monitor.

To extract the URL of a directory table on an external stage for further transformation in Snowflake, the GET_ABSOLUTE_PATH function can be used. This function returns the full path of a file or directory within a specified stage, enabling users to dynamically construct URLs for accessing or processing data stored in external stages.