COF-C02: SnowPro Core Certification

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.

One of the key features of Snowflake's architecture is its unique approach to eliminating resource contention through the use of virtual warehouses. This is achieved by separating storage and compute resources, allowing multiple virtual warehouses to operate independently on the same data without affecting each other. This means that different workloads, such as loading data, running queries, or performing complex analytics, can be processed simultaneously without any performance degradation due to resource contention.

References:

Snowflake Documentation on Virtual Warehouses

SnowPro Core Certification Study Guide

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.

References:

Snowflake Documentation on Pipes

SnowPro Core Certification Study Guide

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

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.

References:

Snowflake Documentation on Clustering Keys & Clustered Tables

Snowflake Documentation on SYSTEM$CLUSTERING_INFORMATION

Stack Overflow discussion on cluster key selection in Snowflake