The STRIP_NULL_VALUE function in Snowflake is used to convert a JSON null value into aSQL NULL value1.

The TABLE_STORAGE_METRICS metadata table stores the storage utilization information, including for tables that have been dropped but are still incurring storage costs2.

The Query Profile statistics that indicate intermediate results do not fit in memory are the bytes spilled to local storage and bytes spilled to remote storage2.

 For optimizing the performance of queries that run on a very large table, it is recommended to choose one high cardinality column as the clustering key. This helps to co-locate similar rows in thesame micro-partitions, improving scan efficiency in queries by skipping data that does not match filtering predicates4.

The SECURITYADMIN role in Snowflake can manage any object grant globally, including modifying and revoking grants. This role has the necessary privileges to oversee and control access to all securable objects within the Snowflake environment4.

Snowflake stores metadata for rows in micro-partitions, including the range of values for each column and the number of distinct values1.

The Snowflake data types that can be used to build nested hierarchical data are OBJECT and VARIANT. These data types support the storage and querying of semi-structured data, allowing for the creation of complex, nested data structures

The LIST and PUT commands are specific to SnowSQL and cannot be executed in the web interface or other SQL clients. LIST is used to display the contents of a stage, and PUT is used to upload files to a stage. References: [COF-C02] SnowPro Core Certification Exam Study Guide

The purpose of a Query Profile is to provide a detailed analysis of a particular query’s execution plan, including the mechanics, behavior, and performance. It helps in identifying potential performance bottlenecks and areas for optimization

When federated authentication is activated in Snowflake, users authenticate via an external identity provider (IdP) rather than using Snowflake-managed credentials. Therefore, a user with a password defined by Snowflake will be unable to enter a password and must use their IdP credentials to log in.

QUSTION NO:579

What value provides information about disk usage for operations where intermediate results do not fit in memory in a Query Profile?

A. IO

B. Network

C. Pruning

D. Spilling

Answer: D

In Snowflake, when a query execution requires more memory than what is available, Snowflake handles these situations by spilling the intermediate results to disk. This process is known as " spilling. " The Query Profile in Snowflake includes a metric that helps users identify when and how much data spilling occurs during the execution of a query. This information is crucial for optimizing queries as excessive spilling can significantly slow down query performance. The value that provides this information about disk usage due to intermediate results not fitting in memory is appropriately labeled as " Spilling " in the Query Profile.

Snowflake’s Time Travel feature is used for the maintenance of historical data, allowing users to access andrestore data that has been changed or deleted within a defined period4.

 The factors that impact storage costs in Snowflake include the account type (Capacity or On Demand) and the cloud region used by the account. These factors determine the rate at which storage is billed, with different regions potentially having different rates3.

The BUILD_STAGE_FILE_URL function generates a Snowflake-hosted file URL to a staged file using the stage name and relative file path as inputs2.

 A Scoped URL permits temporary access to a staged file without the need to grant privileges to the stage or to issue access tokens. It provides a secure way to share access to filesstored in Snowflake

To reduce data spilling in Snowflake, using a larger virtual warehouse is effective because it provides more memory and local disk space, whichcan accommodate larger data operations and minimize the need to spill data to disk or remote storage1. References: [COF-C02] SnowPro Core Certification Exam Study Guide

The Snowflake command to unload the result of a query to a single file is COPY INTO < internal stage > with SINGLE = TRUE followed by a GET command to download the file. This command unloads the query result into a single file in the specified internal stage

Processing data in smaller batches reduces resource pressure during execution.

Increasing warehouse size provides more memory and CPU, which helps reduce memory spillage , where intermediate data is written to disk.

When a virtual warehouse in Snowflake is suspended, the cache is dropped and is no longer available upon restart. This means that all cached data, including results and temporary data, are cleared from memory. The purpose of this behavior is to conserve resources while the warehouse is not active. Upon restarting the warehouse, it will need to reload any data required for queries from storage, which may result in a slower initial performance until the cache is repopulated. This is a critical consideration for managing performance and cost in Snowflake.

To change a Standard virtual warehouse to a Snowpark-optimized virtual warehouse, the warehouse must be in a suspended state. This ensures that no operations are disrupted during the modification process.

Suspend the Warehouse:Ensure that the warehouse is suspended.

ALTER WAREHOUSE my_warehouse SUSPEND;

Alter the Warehouse:Modify the warehouse to be Snowpark-optimized.

ALTER WAREHOUSE my_warehouse SET WAREHOUSE_TYPE = ' SNOWPARK-OPTIMIZED ' ;

Resume the Warehouse:Resume the warehouse to make it operational.

ALTER WAREHOUSE my_warehouse RESUME;

In Snowflake, the authorization to executeCREATE < object > statements, such as creating tables, views, databases, etc., is determined by the role currently set as the user ' s primary role. The primary role of a user or session specifies the set of privileges (including creation privileges) that the user has. While users can have multiple roles, only the primary role is used to determine what objects the user can create unless explicitly specified in the session.