How Snowflake stores and organizes data under the hood: micro-partitions and clustering

Snowflake's storage layer holds all table and file data in compressed, columnar micro-partitions on cloud object storage (S3, Azure Blob, or GCS), completely decoupled from compute. Snowflake automatically manages partitioning, compression, and metadata, eliminating manual tuning tasks common in traditional databases. Storage costs are billed separately from compute based on average monthly compressed data volume.

1 · Learn the must-know

• 1Micro-partitions are immutable, contiguous storage units (50-500MB uncompressed) that Snowflake automatically creates, compresses, and encrypts; users cannot manually define partitioning.
• 2Snowflake stores rich metadata (min/max values, distinct counts, null counts) for each micro-partition, enabling pruning to skip irrelevant partitions during query execution.
• 3Time Travel allows querying or restoring historical data (via AS OF, UNDROP) for a retention period of 1 day by default (Standard edition) up to 90 days (Enterprise+ edition, configurable via DATA_RETENTION_TIME_IN_DAYS).
• 4Fail-safe provides an additional 7-day non-configurable recovery period after Time Travel expires, accessible only by Snowflake support for disaster recovery, not by users.
• 5Storage costs accrue for Time Travel and Fail-safe retained data in addition to active data, so longer retention periods and frequent DML increase storage billing.
• 6Snowflake supports zero-copy cloning (CLONE) which creates new objects referencing existing micro-partitions without duplicating storage, incurring additional costs only as underlying data diverges.

2 · Check your understanding