Query parsing and compilation in Snowflake occur within the cloud services layer. This layer is responsible for various management tasks, including query compilation and optimization

Query pruning in Snowflake refers to the optimization technique where the system reduces the amount of data scanned by a query based on the query conditions. This typically involves skipping unnecessary data partitions that do not contribute to the query result. The effectiveness of this technique can be observed through:

Option A: Partitions scanned. This statistic indicates how many data partitions were actually scanned as a result of query pruning, showing the optimization in action.

Option C: Bytes scanned. This measures the volume of data physically read during query execution, and a reduction in this number indicates effective query pruning, as fewer bytes are read when unnecessary partitions are skipped.

Options B, D, and E do not directly relate to observing the effects of query pruning. " Partitions total " shows the total available, not the impact of pruning, while " Bytes read from result " and " Bytes written " relate to output rather than the efficiency of data scanning.

 Snowflake supports semi-structured data types, which include OBJECT and VARIANT. These data types are capable of storing JSON-like data structures, allowing for flexibility in datarepresentation. OBJECT can directly contain VARIANT, and thus indirectly contain any other data type, including itself1.

 Snowflake data providers can share data residing in different databases through secure views. Secure views allow for the referencing of objects such as schemas, tables, and other views contained in one or more databases, as long as those databases belong to the same account. This enables providers to share data securely and efficiently with consumers. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The correct answer is D. When the system estimates that there is enough query load to keep the new cluster busy for at least 6 minutes .

In Snowflake, a multi-cluster warehouse can use either a Standard or Economy scaling policy. The Economy scaling policy is more conservative. It starts additional clusters only when Snowflake estimates that the extra cluster will have enough work to justify the additional compute cost.

Why D is correct:

With the Economy scaling policy, Snowflake starts another cluster only if the system estimates there is enough query load to keep the new cluster busy for approximately 6 minutes. This helps reduce credit consumption by avoiding unnecessary short-lived cluster starts.

Why the other options are incorrect:

A. The condition is not based on all active clusters running queries for more than 6 minutes.

B. A single queued query for more than 6 minutes is not the exact Economy scaling rule.

C. The rule is not specifically based on 3 or more queued queries.

D. This matches Snowflake’s documented behavior for the Economy scaling policy.

Official Snowflake documentation reference:

Snowflake documentation explains that the Economy scaling policy conserves credits by starting additional clusters only when Snowflake estimates enough query load exists to keep the cluster busy for at least 6 minutes.

Resource monitors in Snowflake can be configured at the account and virtual warehouse levels. They are used to track credit usage and control costs associated with running virtual warehouses. When certain thresholds are reached, resource monitors can trigger actions such as sending alerts or suspending warehouses to prevent excessive credit consumption. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The worksheet and database explorer feature in Snowsight allows users to tag frequently accessed worksheets for ease of access. This functionality helps users organize and quickly navigate to the worksheets they use most often, enhancing productivity and streamlining the data exploration and analysis process within Snowsight, Snowflake ' s web-based query and visualization interface.

 Snowflake’s hierarchical key model includes several levels of keys, where Account master keys and File keys are part of this hierarchy. Account master keys are used to encrypt all the data within an account, while File keys are used to encrypt individual files within the database2.

The Virtual Private Snowflake (VPS) edition offers the highest level of security for organizations with the strictest security requirements. This edition provides a dedicated andisolated instance of Snowflake, including enhanced security features and compliance certifications to meet the needs of highly regulated industries or any organization requiring the utmost in data protection and privacy.

Pruning in Snowflake refers to the process of filtering or disregarding micro-partitions that are not needed to satisfy the conditions of a query. This optimization technique helps reduce the amount ofdata scanned, thereby improving query performance

To execute a series of SQL statements using a task, a user would use a stored procedure that contains multiple SQL statements and invoke this stored procedure from the task. References: Snowflake Documentation2.

 Running EXPLAIN and SHOW commands, as well as running a custom query, consume virtual warehouse credits in the Snowflake environment. These activities require computational resources, and therefore, credits are used to account for the usage of these resources. References: [COF-C03] SnowPro Core Certification Exam Study Guide

The Snowflake Query Profile provides a graphic representation of the main components of the query processing. This visual aid helps users understand the execution details and performance characteristics of their queries4.

 Snowflake recommends creating separate warehouses for each workload to resolve performance issues caused by shared virtual warehouses. This ensures that the resources are not being overutilized by one user’s activities, thereby affecting the performance of another user’s activities4.

 The stream type that can be used for tracking the records in external tables is ‘External’. This type of stream is specifically designed to track changes in external tables

In Snowflake, a tabular User-Defined Function (UDF) is defined with a return clause that includes the keyword “TABLE.” This indicates that the UDF will return a set of rows, which can be used in the FROM clause of a query. References: Based on my internal knowledge as of 2021.

 Snowpark is designed to bring the data programmability to Snowflake, enabling developers to write code in familiar languages like Scala, Java, and Python. It allows for the execution of these codes directly within Snowflake’s virtual warehouses, eliminating the need for a separate cluster. Additionally, Snowpark’s compatibility with Spark allows users to leverage their existing Spark code with minimal changes1.

The Snowflake Account_Usage.Metering_History view is used to gather the hourly credit usage for an account. This view provides details on the credits consumed by various services within Snowflake for the last 365 days1.

 SnowCD (Snowflake Connectivity Diagnostic Tool) is the best tool provided by Snowflake for troubleshooting network connectivity issues. It helps diagnose and resolve issues related to connecting to Snowflake services

https://docs.snowflake.com/en/user-guide/snowcd.html#:~:text=SnowCD%20(i.e.%20Snowflake%20Connectivity%20Diagnostic,their%20network%20connection%20to%20Snowflake.

 When deciding on a clustering key for a table, Snowflake recommends using the columns that are most actively used in the select filters. This is because clustering by these columns can improve the performance of queries that filter on these values, leading to more efficient scans and better overall query performance2. References: [COF-C03] SnowPro Core Certification Exam Study Guide