SnowPro Core Certification (Micro-partitions)

What is the typical size of Micro partitions before compression?

50MB-500MB

The Snowflake metadata repository does not contains which of the following?

Aggregated data from the table

How can clustering keys help query performance?

Clustering keys improve query performance by reducing the amount of data scanned through effective pruning.

How does the INSERT statement affect micro-partitions in a table?

It adds new micro-partitions to the same table without locking any existing micro-partitions

Why is clustering and re-clustering important in Snowflake?

It improves query performance by reducing the amount of data scanned

Is micro-partitioning performed manually or automatically on all Snowflake tables?

Micro-partitioning is automatically performed on all Snowflake tables.

Which of the following statements correctly describe Snowflake micro-partitions?

Micro-partitions are the physical data files that comprise Snowflake's logical tables.

Does Snowflake prune micro-partitions based on a predicate with a subquery?

No, Snowflake does not prune micro-partitions based on a predicate with a subquery, even if the subquery results in a constant.

(True / False) Clustering keys for a table can only contain a single column

No, clustering keys can contain multiple columns.

Can micro partitioning be disabled for tables if needed?

No, micro partitioning can not be disabled and is automatically managed by Snowflake. You can only control the clustering key which changes the micro partitioning approach but does not disable it.

Are micro-paritions mutable?

No, micro-partitions are immutable

What technique does Snowflake use to limit the number of micro-partitions scanned by each query?

Pruning

What are some general indicators that a clustering key is required on a table?

Query performance has degraded over time and/or data size is multi terabytes

What SQL statement returns cluster key information for a table?

SHOW TABLES LIKE <table> or SHOW CLUSTERING KEYS IN TABLE <table_name>;

Which kind of queries WONT benefit from clustering.

Select all rows in the table

What does Snowflake store about rows in a micro-partition?

Snowflake stores the number of distinct values, the range of values for each of the columns in the micro partition, and additional properties for optimization and efficient processing.

What does the clustering depth for a populated table measure?

The average depth (1 or greater) of the overlapping micro-partitions for specified columns in a table.

What metric can be used to identify high-churn dimension tables in Snowflake?

The ratio of FAILSAFE_BYTES divided by ACTIVE_BYTES in the TABLE_STORAGE_METRICS view.

What happens to the impacted micro-partitions when multiple updates are made to a table in Snowflake?

They are re-created and transition through the CDP storage life-cycle.

Which of the following is a benefit of Snowflake micro-partitions?

They enable horizontal and vertical query pruning.

How many micro-partitions may a typical Snowflake table have?

Thousands to millions of micro-partitions.

(True / False) At the instant the clone is created, all micro-partitions in both tables are fully shared.

True

(True / False) There is a cost associated with maintaining the partitions associated with the clustering keys.

True

Does Snowflake automatically determine the most efficient algorithm to compress columns in a micro partitions?

Yes it is true. Columns are also compressed individually within micro-partitions. Snowflake automatically determines the most efficient compression algorithm for the columns in each micro-partition.

Does Snowflake maintain micro-partitions using the order of the data in which it is inserted?

Yes, Snowflake automatically partitions tables based on the order of data when it's loaded or inserted

Can micro-partitions overlap in their range of values?

Yes, micro-partitions can overlap in their range of values