SnowPro Core: SnowPro Core

A clustering key in Snowflake is used to co-locate similar data within the same micro-partitions to improve query performance, especially for large tables where data is not naturally ordered or has become fragmented due to extensive DML operations. The appropriate use of a clustering key can lead to improved scan efficiency and better column compression, resulting in faster query execution times.

The indicators that it may be appropriate to use a clustering key for a table include:

D) Queries on the table are running slower than expected: This can happen when the data in the table is not well-clustered, leading to inefficient scans during query execution.

E) The clustering depth for the table is large: A large clustering depth indicates that the table's data is spread across many micro-partitions, which can degrade query performance as more data needs to be scanned.

Snowflake Documentation on Clustering Keys & Clustered Tables

Snowflake Documentation on SYSTEM$CLUSTERING_INFORMATION

Stack Overflow discussion on cluster key selection in Snowflake

Unstructured data stored in Snowflake can be retrieved by using SQL functions to generate URLs that point to the data.These URLs can then be used to download the data directly to a client

To allow secure views the ability to reference data in multiple databases, the REFERENCE_USAGE privilege must be granted on each database that contains objects referenced by the secure view2. This privilege is necessary before granting the SELECT privilege on a secure view to a share.

A materialized view is beneficial when the query consumes many compute resources every time it runs (B), and when the results of the query do not change often and are used frequently (E).This is because materialized views store pre-computed data, which can speed up query performance for workloads that are run frequently or are complex

When a virtual warehouse in Snowflake is resized, specifically when it is increased in size, the additional compute resources become immediately available to all running and queued queries. This means that the performance of these queries can improve due to the increased resources.Conversely, when the size of a warehouse is reduced, the compute resources are not removed until they are no longer being used by any current operations1.

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Virtual Warehouses2

To traverse semi-structured data in Snowflake, a user can insert a colon (:) between the VARIANT column name and any first-level element.This path syntax is used to retrieve elements in a VARIANT column4.

Snowsight automatically detects if a target account is in a different region and enables cross-cloud auto-fulfillment when using a paid listing on the Snowflake Marketplace.This feature allows Snowflake to manage the replication of data products to consumer regions as needed, without manual intervention1.

To provide a user with the necessary access to create a materialized view in a schema, the user must be granted a role that has the CREATE MATERIALIZED VIEW privilege on that schema.First, the role is granted to the user, and then the privilege is granted to the role

User-level network policies in Snowflake can be created by roles with the necessary privileges to manage security and account settings. The ACCOUNTADMIN role has the highest level of privileges across the account, including the ability to manage network policies. The SECURITYADMIN role is specifically responsible for managing security objects within Snowflake, which includes the creation and management of network policies.

[COF-C02] SnowPro Core Certification Exam Study Guide

Snowflake Documentation on Network Policies1

Section 1.3 - SnowPro Core Certification Study Guide