COF-C03 - Snowflake AI Data Cloud Features and Architecture (31% of the exam) - Section 1.6
Explain micro-partitions, data clustering, and how Snowflake stores and prunes table data.
Explain that Snowflake stores table data in immutable, compressed, columnar micro-partitions that carry metadata about the values they hold. Describe how the natural clustering of data and partition pruning let Snowflake skip micro-partitions that cannot match a query, and what clustering depth indicates about a large table.
lock_openFree sampleSnowflake AI Data Cloud Features and Architecturemedium
An engineer runs SYSTEM$CLUSTERING_INFORMATION on a fact table and sees an average clustering depth close to 1 for the clustering column. They must explain to the team what that low depth indicates about how the data is organised on that column. Which interpretation is correct?
AFew micro-partitions overlap on that column's value ranges, so the data is well clustered and queries on it prune effectively.check_circle Correct
BThe clustering key was rejected, so Snowflake silently stopped maintaining any physical ordering and reverted the table to fully unordered load order.
COnly one micro-partition exists for the whole table, so clustering depth cannot grow above 1 regardless of how much data is loaded.
DAlmost every micro-partition overlaps on that column's value ranges, so the table is badly clustered and most partitions must be scanned.
Interpret an average clustering depth near 1 as minimal micro-partition overlap on the clustering column, meaning the table is well clustered for pruning. Clustering depth measures the average number of micro-partitions whose value ranges overlap at any point along the clustering column. A depth close to 1 means almost no overlap, so for a given predicate Snowflake can isolate a small set of partitions and skip the rest, which is exactly the pruning behaviour clustering is meant to deliver.
Why A is correct: A clustering depth approaching 1 means very few micro-partitions share overlapping value ranges for that column, which is the ideal state for pruning and confirms strong clustering.
Why B is wrong: Snowflake does not silently reject a clustering key, and a depth near 1 reflects good organisation rather than abandoned maintenance, so this misreads the metric.
Why C is wrong: Depth near 1 measures overlap across many partitions, not a single-partition table, and large tables hold many micro-partitions, so this confuses depth with partition count.
Why D is wrong: Heavy overlap and poor pruning correspond to a high clustering depth, not a depth near 1, so this inverts the meaning of the metric the engineer is reading.
Exam traps in Snowflake AI Data Cloud Features and Architecture
Answers that look right on this material and are not. Each one is a distractor from a different question in the COF-C03 bank for this domain.
Snowflake opens the existing micro-partition in place and edits the affected row's bytes directly, since micro-partitions are mutable files that support row-level overwrite
errorWhy it is wrong: This is tempting because relational engines often edit rows in place, but Snowflake micro-partitions are immutable, so it never rewrites bytes inside an existing partition.
A secondary B-tree index built automatically on order_date that points the optimiser at the exact micro-partitions containing matching rows for the filter
errorWhy it is wrong: This is tempting for those used to traditional databases, but Snowflake does not build user B-tree indexes; pruning relies on per-partition metadata instead.
Snowflake silently created a default clustering key on the load timestamp column the first time the table was queried, which now drives the observed pruning behaviour
errorWhy it is wrong: This is tempting but false, because Snowflake never auto-defines a clustering key; the good pruning comes from the natural order of the loaded data.