2.3 Scenario Practice for Snowflake AI Data Cloud Features and Architecture

Micro-Partitions

Snowflake stores all table data in micro-partitions — contiguous units of storage created automatically as data is loaded. Key facts the exam tests:

• Each micro-partition contains between 50 MB and 500 MB of uncompressed data (smaller once compressed).
• Data is stored in columnar form within each micro-partition, so a query reads only the columns it needs.
• Snowflake automatically compresses each column using the most efficient scheme it determines.
• Micro-partitions are immutable: an UPDATE/DELETE rewrites affected partitions rather than editing in place (this is what powers Time Travel).
• Partitioning is fully automatic — you do not define or manage partitions as you would in a traditional warehouse.

For each micro-partition, the cloud services layer stores metadata: the range (min and max value) of each column, the number of distinct values, and other properties. This metadata is the engine behind performance.

Query Pruning

Pruning is how Snowflake avoids scanning data it does not need. When a query has a filter (e.g., WHERE order_date = '2026-01-15'), the optimizer checks each micro-partition's min/max metadata and skips any partition whose range cannot contain a matching value. Only the surviving partitions are read by the warehouse. Effective pruning is the single biggest driver of query performance, and it happens with no configuration on well-ordered data.

Clustering and Clustering Keys

As data naturally loads, micro-partitions are typically well-ordered on the columns you load by. Over time, with many DML operations, the data in a very large table can become less ordered, so a filter column's values spread across many micro-partitions and pruning degrades.

A clustering key is one or more columns (or expressions) you designate so Snowflake co-locates rows with similar values in the same micro-partitions. Automatic Clustering is a serverless service that incrementally reorganizes the table in the background to maintain that order — it consumes credits but requires no manual warehouse.

Guidance the exam expects:

• Clustering keys are optional and intended for large tables (multi-terabyte) where query performance has degraded due to poor pruning.
• Choose clustering columns that are frequently used in filters or joins, with the right cardinality — not too low, not unique per row.
• Clustering depth measures the average overlap of micro-partitions for the clustering key; a lower clustering depth means better clustering and better pruning. SYSTEM$CLUSTERING_INFORMATION reports it.
• Do not add a clustering key to small tables or to tables that already prune well — it just adds cost.

The Three Caches

Snowflake uses three distinct caches, and distinguishing them is a frequent exam item:

1. Metadata cache — Lives in the cloud services layer. Holds object metadata and statistics. Lets simple queries (row counts, min/max, table DDL) resolve without a running warehouse.
2. Warehouse (local disk) cache — Lives on a running virtual warehouse's local SSD. Holds raw table data fetched from remote storage so repeat reads are faster. Dropped when the warehouse suspends.
3. Result cache — Lives in the cloud services layer and holds the results of executed queries for 24 hours (the timer resets each time the cached result is reused, up to a maximum of 31 days). A repeated query returns instantly from this cache without using any warehouse credits.

The result cache is reused only when all conditions hold: the new query text is syntactically identical (after normalization), the underlying data has not changed, the same role/privileges apply, the query does not use non-deterministic functions like CURRENT_TIMESTAMP, and the result cache has not been disabled. If any condition fails, the query runs again on a warehouse.

Putting It Together in a Scenario

If an analyst reruns the exact dashboard query minutes later and it returns instantly with no credit use, that is the result cache. If a different query reads the same table quickly because the warehouse is warm, that is the local disk cache. If COUNT(*) returns with no warehouse, that is the metadata cache. Mapping the behavior to the correct cache is exactly how the exam frames these questions.

Immutability, DML, and Time Travel's Foundation

Because micro-partitions are immutable, Snowflake never edits data in place. An UPDATE or DELETE writes new micro-partitions for the changed rows and marks the old ones as no longer part of the active table — but the old partitions are retained for the Time Travel window. This copy-on-write design is why Time Travel and zero-copy cloning are possible: the historical versions already exist on disk.

Zero-copy cloning uses the same idea. CREATE TABLE new_t CLONE old_t creates a new table that shares the existing micro-partitions with the source — no data is duplicated and the clone is nearly instant. Storage is only consumed later, as the clone or the source diverges and new partitions are written. You can clone tables, schemas, and whole databases this way, making instant dev/test environments cheap.

When Pruning Fails

Pruning depends on the filter column's values being concentrated in few micro-partitions. It degrades when:

• The query filters on a column unrelated to the natural load order.
• A large table has churned heavily, scattering values across many partitions.
• A function wraps the column (e.g., WHERE TO_DATE(ts) = ...) in a way the optimizer cannot use against min/max metadata.

The remedies, in order of cost: rewrite the predicate to use metadata; load or order data by the filter column; and only for very large tables, add a clustering key.