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

TheTO_JSONfunction in Snowflake is used to convert a relational table or individual rows into JSON format. This function is helpful for exporting data in JSON format.

Using TO_JSON Function:

SELECT TO_JSON(OBJECT_CONSTRUCT(*))

FROM my_table;

Exporting Data:TheTO_JSONfunction converts the table rows into JSON format, which can then be exported to a file.

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. Add more clusters to the warehouse .

Queued queries on a multi-cluster warehouse usually indicate a concurrency problem. Multiple analysts are submitting queries at the same time, and the warehouse does not have enough active cluster capacity to process them concurrently.

Why B is correct:

Adding more clusters to a multi-cluster warehouse increases concurrency capacity. In Auto-scale mode, Snowflake can start additional clusters as needed, up to the configured maximum, to reduce queuing.

Why the other options are incorrect:

A. Scheduling or limiting reports may reduce concurrency, but it adds operational overhead and does not use Snowflake’s built-in multi-cluster scaling capability.

C. Increasing warehouse size can make individual queries faster, but it does not directly improve concurrency as effectively as adding clusters.

D. A Snowpark-optimized warehouse is intended for memory-intensive Snowpark workloads, not for reducing query queues from many concurrent BI/ad hoc users.

Official Snowflake documentation reference:

Snowflake documentation explains that multi-cluster warehouses are designed to handle concurrency by automatically scaling out with additional clusters.

A pre-signed URL provides a way to access objects stored in a stage without requiring authentication to Snowflake. This URL contains all the necessary information for access control, including an expiration time, and it’s signed by the service ' s credentials. This mechanism allows users or applications to access specific files stored in a cloud storage location (stage) directly, bypassing the need to authenticate into Snowflake, ensuring secure and temporary access to the data.

The correct function to find the query ID of the second query executed in the current session isSELECT LAST_QUERY_ID(-2). TheLAST_QUERY_IDfunction returns the query ID for the most recent query executed in the session when called with no arguments. When used with an argument, it can retrieve the ID of previous queries within the same session, where-2would reference the second most recent query executed.

In Snowflake, the use of streams impacts how the data retention period for a table is handled, particularly in scenarios where the stream has not been consumed. The key point to understand is that Snowflake ' s streams are designed to capture data manipulation language (DML) changes such as INSERTS, UPDATES, and DELETES that occur on asource table. Streams maintain a record of these changes until they are consumed by a DML operation or a COPY command that references the stream.

When a stream is created on a table and remains unconsumed, Snowflake extends the data retention period of the table to ensure that the changes captured by the stream are preserved. This extension is specifically up to the point in time represented by the stream ' s offset, which effectively ensures that the data necessary for consuming the stream ' s contents is retained. This mechanism is in place to prevent data loss and ensure the integrity of the stream ' s data, facilitating accurate and reliable data processing and analysis based on the captured DML changes.

This behavior emphasizes the importance of managing streams and their consumption appropriately to balance between data retention needs and storage costs. It ' s also crucial to understand how this temporary extension of the data retention period impacts the overall management of data within Snowflake, including aspects related to data lifecycle, storage cost implications, and the planning of data consumption strategies.

The correct answer is A. View .

In Snowflake data sharing, secure objects are normally required when sharing views or functions because secure objects prevent sensitive underlying query details from being exposed. However, when the SECURE_OBJECTS_ONLY property is set to FALSE, non-secure views can be shared.

Why A is correct:

A view can be shared when SECURE_OBJECTS_ONLY = FALSE. This setting relaxes the requirement that only secure views be included.

Why the other options are incorrect:

B. Stored procedures are not standard objects shared through Snowflake secure data sharing in the same way as tables and views.

C. Storage integration access is not shared as a database object through a Snowflake share.

D. UDF sharing has security restrictions, and the safest exam answer here is a view when SECURE_OBJECTS_ONLY is disabled.

Official Snowflake documentation reference:

Snowflake documentation explains that shares can include database objects such as tables and views. Secure object requirements apply to certain shared objects, and the SECURE_OBJECTS_ONLY property controls whether only secure objects are allowed.

Resource monitors in Snowflake are tools used to track and control the consumption of compute resources, ensuring that usage stays within defined limits. These monitors can be created at the account level, allowing administrators to set overall resource consumption limits for the entire Snowflake account. Additionally, resource monitors can be set at the virtual warehouse level, enabling more granular control over the resources consumed by individual warehouses. This dual-level capability allows organizations to manage their Snowflake usage efficiently, preventing unexpected costs and optimizing performance.

 Search optimization in Snowflake is designed to improve the performance of queries that are selective and involve point lookup operations using equality and IN predicates. It isparticularly beneficial for queries that access columns with a high number of distinct values1.

References = [ COF-C03 ] SnowPro Core Certification Exam Study Guide, Snowflake Documentation

 Snowflake stored procedures can be created to execute with the privileges of the role that owns the procedure (owner’s rights) or with the privileges of the role that calls the procedure (caller’s rights). This allows for flexibility in managing security and access control within Snowflake1.

 By default, the Snowflake role required to create a share is ACCOUNTADMIN (D). This role has the necessary privileges to perform administrative tasks, including creating shares for data sharing purposes

The ResultSet cache is leveraged to quickly return results for repeated queries. Actions that prevent leveraging this cache include stopping the virtual warehouse that the query is running against (B) and executing the RESULTS_SCAN() table function (D). Stopping the warehouse clears the local diskcache, including the ResultSet cache1. The RESULTS_SCAN() function is used to retrieve the result of a previously executed query, which bypasses the need for the ResultSet cache.

Serverless features in Snowflake are billed based on the time they are used, measured in minutes. The cost is calculated by multiplying the duration of the job by an automatic sizing determined by Snowflake, with a minimum billing increment of one minute. This means that even if a serverless feature is used for less than a minute, it will still be billed for the full minute.

Snowflake supports the use of external stages with Amazon Web Services (AWS), Microsoft Azure Cloud, and Google Cloud Platform (GCP). These platforms allow users to stage data externally and integrate with Snowflake for data loading operations

A multi-cluster warehouse should be used in auto-scaling mode when there is a need to handle a large number of concurrent queries. Auto-scaling allows Snowflake to automatically add or remove compute clusters to balance the load, ensuring that performance remains consistent during varying levels of demand

To manage compliance with Personal Identifiable Information (PII) requirements, the Virtual Private Snowflake and Business Critical Editions of Snowflake should be used. These editions provide advanced security features necessary for handling sensitive data

The Enterprise edition of Snowflake allows for a dedicated metadata store, providing additional features designed for large-scale enterprises

 In the Snowflake Enterprise Edition, the default Time Travel retention is set to 1 day, the maximum Time Travel retention can be set up to 90 days, and the Fail-safe retention timeis 7 days3.