Study guide

2.3 Scenario Practice for Snowflake AI Data Cloud Features and Architecture

SnowPro Core Study Guide Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: SnowPro Core Orientation and Exam Strategy

1.1 Current SnowPro Core Exam Facts 1.2 Eligibility, Registration, and Scheduling 1.3 Blueprint Domains and Weighting 1.4 Editions, Question Style, and Score Report Thinking 1.5 Study Calendar and Hands-On Practice Plan

2 Chapter 2: Snowflake AI Data Cloud Features and Architecture

2.1 Snowflake AI Data Cloud Features and Architecture Overview 2.2 Core Workflows and Decision Points 2.3 Scenario Practice for Snowflake AI Data Cloud Features and Architecture 2.4 Common Traps in Snowflake AI Data Cloud Features and Architecture 2.5 Practice Drills and Readiness Markers

3 Chapter 3: Account Management and Data Governance

3.1 Account Management and Data Governance Overview 3.2 Core Workflows and Decision Points 3.3 Scenario Practice for Account Management and Data Governance 3.4 Common Traps in Account Management and Data Governance 3.5 Practice Drills and Readiness Markers

4 Chapter 4: Data Loading, Unloading, and Connectivity

4.1 Data Loading, Unloading, and Connectivity Overview 4.2 Core Workflows and Decision Points 4.3 Snowpipe, External Tables, and Storage Integrations 4.4 Bulk Unloading and Semi-Structured Data 4.5 Connectors, Drivers, and Snowsight Loading

5 Chapter 5: Performance Optimization, Querying, and Transformation

5.1 Performance Optimization, Querying, and Transformation Overview 5.2 Core Workflows and Decision Points 5.3 Micro-Partitions, Pruning, Clustering, and the Query Profile 5.4 Search Optimization, Materialized Views, and Query Best Practices 5.5 Transformations: ELT, Streams & Tasks, and Semi-Structured Data

6 Chapter 6: Data Collaboration

6.1 Secure Data Sharing Architecture 6.2 Creating Shares, Granting Objects, and Reader Accounts 6.3 Snowflake Marketplace, Listings, and Data Exchange 6.4 Secure Views, Cross-Region Sharing, and Governance 6.5 Choosing the Right Sharing Method and Readiness Drills

7 Chapter 7: Final Review and Test Day

7.1 Timed Practice Strategy 7.2 Last-Week Review Map 7.3 Exam-Day Checklist 7.4 After the Exam and Next Steps

2.3 Scenario Practice for Snowflake AI Data Cloud Features and Architecture

Key Takeaways Explain This Topic AI

Snowflake automatically divides every table into micro-partitions of roughly 50–500 MB of uncompressed data, stored compressed and columnar.

Query pruning uses per-micro-partition metadata (min/max, distinct counts) so the optimizer skips partitions that cannot match a filter.

Clustering keys are optional and meant for very large tables; they let Automatic Clustering co-locate similar values to improve pruning.

Snowflake has three caches: the metadata cache, the warehouse local-disk cache, and the 24-hour result cache in cloud services.

The result cache returns identical query results without a warehouse, provided the underlying data and query are unchanged and roles permit it.

Last updated: October 2026

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.

  • Concept What it means
  • Clustering key Column(s) Snowflake orders the table by for better pruning
  • Automatic Clustering Serverless background reclustering (consumes credits)
  • Clustering depth Overlap measure — lower is better

The Three Caches

Snowflake uses three distinct caches, and distinguishing them is part of objective 4.3:

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 .

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.

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 .

  • Cache Location Cleared when
  • Metadata Cloud services Object metadata changes
  • Local disk Running warehouse Warehouse suspends
  • Result Cloud services 24h elapse or data changes