Storage Layer

Overview

The Storage Layer is the foundation of Snowflake's architecture. It is where all persistent data resides. For the SnowPro Core (COF-C03) exam, you must understand how data is physically stored in the underlying cloud provider (S3, GCS, Azure Blob), how it is structured (micro-partitions), and how storage costs are calculated.

Key Concepts

Centralized Object Storage

When data is loaded into Snowflake, it is reorganized into Snowflake's internal optimized, compressed, columnar format.

• The data is stored in the cloud object storage provided by the cloud platform where your Snowflake account is hosted (e.g., Amazon S3, Google Cloud Storage, or Microsoft Azure Blob Storage).
• Encrypted: All data at rest is automatically encrypted by Snowflake using AES-256 strong encryption.
• Immutable: The underlying files created by Snowflake are immutable; they cannot be changed once written. Updates create new files.

Columnar Storage

Snowflake stores data in a columnar format. Traditional databases often use row-based storage.

• Columnar storage is highly efficient for analytical queries (OLAP) because if a query only selects three columns out of a 100-column table, Snowflake only reads the data for those three columns from disk.

Micro-Partitions

Snowflake automatically divides all data into continuous, non-overlapping storage units called micro-partitions.

• Size: Each micro-partition contains between 50 MB and 500 MB of *uncompressed* data, which generally compresses down to roughly 16 MB of physical storage.
• Automatic: Users do not manage partitioning. It is handled automatically by Snowflake as data is ingested.
• Metadata: The Cloud Services layer stores metadata for each micro-partition, including the range of values (MIN/MAX) for every column. This allows Snowflake to rapidly skip (prune) irrelevant micro-partitions during a query.

How It Works

Data Compression

Snowflake automatically compresses all data upon ingestion.

• You do not need to define compression algorithms or manage indexes.
• Because data is columnar, compression is highly effective (similar data types are grouped together).

Storage Costs

Storage in Snowflake is billed based on a flat rate per terabyte per month.

• Costs are based on the compressed size of the data.
• Storage usage includes:
• Current data in database tables.
• Historical data maintained for Time Travel and Fail-Safe (which we will cover in later sections).
• Data stored in internal stages (temporary storage for loading/unloading).

SQL Examples

You generally do not interact with the storage layer directly via SQL, but you can monitor storage usage:

-- View storage usage for the account (requires ACCOUNTADMIN role)
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE 
ORDER BY USAGE_DATE DESC LIMIT 10;

-- View table size information
SHOW TABLES;
-- Look at the 'bytes' column in the output, which shows compressed size.

Exam Tips

⚠️ Crucial for COF-C03:

• Remember the size of micro-partitions: 50 MB to 500 MB of uncompressed data, which compresses to roughly 16 MB.
• All data is automatically compressed and encrypted at rest by default. No configuration is required.
• Storage is billed on compressed data size.
• The underlying storage files are immutable.

Key Takeaways

• Data resides in cloud object storage (S3, Azure Blob, GCS).
• Data is stored in an optimized, compressed, columnar format.
• Micro-partitions are automatically managed by Snowflake.
• Cost is calculated based on total compressed storage used, including Time Travel and Fail-Safe data.