Snowflake SnowPro Core Practice Test - Questions Answers, Page 4

Materialized views in Snowflake are a feature that allows for the pre-computation and storage of query results for faster query performance. This feature is available starting from the Enterprise Edition of Snowflake. It is not available in the Standard Edition, and while it is also available in higher editions like Business Critical and Virtual Private Snowflake, the Enterprise Edition is the minimum requirement.

Snowflake Documentation on CREATE MATERIALIZED VIEW1.

Snowflake Documentation on Working with Materialized Views

https://docs.snowflake.com/en/sql-reference/sql/create-materialized-view.html#:~:text=Materialized%20views%20require%20Enterprise%20Edition,upgrading%2C%20please%20contact%20Snowflake%20Support.

When aCLUSTER BYclause is added to a Snowflake table, it specifies one or more columns to organize the data within the table's micro-partitions. This clustering aims to colocate data with similar values in the same or adjacent micro-partitions. By doing so, it enhances the efficiency of query pruning, where the Snowflake query optimizer can skip over irrelevant micro-partitions that do not contain the data relevant to the query, thereby improving performance.

Snowflake Documentation on Clustering Keys & Clustered Tables1.

Community discussions on how source data's ordering affects a table with a cluster key

Column-level security is a feature that allows fine-grained control over access to specific columns within a table. This is particularly useful for managing sensitive data and ensuring that only authorized users can view or manipulate certain pieces of information. According to my last update, this feature was available in the Enterprise Edition or higher editions of Snowflake.

https://docs.snowflake.com/en/user-guide/intro-editions.html

Snowflake supports several methods for authenticating users, includingfederated authentication,key-pair authentication, andOAuth. Federated authentication allows users to authenticate using their organization's identity provider. Key-pair authentication uses a public-private key pair for secure login, and OAuth is an open standard for access delegation commonly used for token-based authentication.Reference:Authentication policies | Snowflake Documentation,Authenticating to the server | Snowflake Documentation,External API authentication and secrets | Snowflake Documentation.

The parameterSTATEMENT_QUEUED_TIMEOUT_IN_SECONDSsets the limit for a query to wait in the queue in order to get its chance of running on the warehouse. The query will quit after reaching this limit. By default, the value of this parameter is 0 which mean the queries will wait indefinitely in the waiting queue

https://community.snowflake.com/s/article/Warehouse-Concurrency-and-Statement-Timeout-Parameters#:~:text=The%20parameter%20STATEMENT_QUEUED_TIMEOUT_IN_SECONDS%20sets%20the,indefinitely%20in%20the%20waiting%20queue.

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

In Snowflake, aPipeis the object designed to enable the continuous, near-real-time loading of data from files as soon as they are available in a cloud storage location. Pipes use Snowflake'sCOPYcommand to load data and can be associated with aStageobject to monitor for new files. When new data files appear in the stage, the pipe automatically loads the data into the target table.

Snowflake Documentation on Pipes

SnowPro Core Certification Study Guide

https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro.html

In Snowflake, to create a materialized view, the user must have the necessary privileges on the schema where the view will be created. These privileges are granted through roles, not directly to individual users. Therefore, the correct process is to grant the role to the user and then grant the privilege to create the materialized view to the role itself.

The statementGRANT ROLE MYROLE TO USER USER1;grants the specified role to the user, allowing them to assume that role and exercise its privileges. The subsequent statementCREATE MATERIALIZED VIEW ON SCHEMA MYDB.MYSCHEMA TO MYROLE;grants the privilege to create a materialized view within the specified schema to the roleMYROLE. Any user who has been grantedMYROLEcan then create materialized views inMYDB.MYSCHEMA.

Snowflake Documentation on Roles

Snowflake Documentation on Materialized Views

https://docs.snowflake.com/en/user-guide/intro-summary-loading.html#:~:text=For%20delimited%20files%20(CSV%2C%20TSV,encoding%20to%20use%20for%20loading.

For delimited files (CSV, TSV, etc.), the default character set is UTF-8. To use any other characters sets, you must explicitly specify the encoding to use for loading. For the list of supported character sets, seeSupported Character Sets for Delimited Files(in this topic).