Data Storage & Clustering

Snowflake’s storage layer is fundamentally different from traditional databases. Understanding how micro-partitions work, how data is physically organised on disk, and how clustering influences query performance is essential knowledge for the COF-C02 exam.

1. Micro-Partitions: The Foundation of Snowflake Storage

Every table in Snowflake is divided into micro-partitions — the fundamental unit of Snowflake’s physical storage. Unlike traditional databases that require you to manually define partitions, Snowflake creates and sizes micro-partitions automatically with no intervention required.

Micro-Partition Architecture

Each Snowflake table is divided into contiguous micro-partitions. Data within each micro-partition is stored in columnar format, compressed independently, and catalogued with rich metadata in the Cloud Services layer. Queries skip entire micro-partitions when the predicate values fall outside the stored min/max range for a column.

Key Characteristics of Micro-Partitions

2. Micro-Partition Metadata

Snowflake automatically tracks rich metadata for every micro-partition, stored at the column level in the Cloud Services layer. This metadata is the engine that powers query pruning.

Per-Column Metadata in Every Micro-Partition

For each column in each micro-partition, Snowflake records four key statistics: minimum value, maximum value, number of distinct values (NDV), and null count. This metadata is stored in the Cloud Services layer, not inside the micro-partition data files themselves, so querying it costs no compute.

The Four Metadata Fields (Per Column, Per Micro-Partition)

• Min value — the smallest value present in that column within this micro-partition
• Max value — the largest value present in that column within this micro-partition
• NDV (Number of Distinct Values) — the cardinality of the column within this micro-partition
• Null count — how many null values exist in the column within this micro-partition

3. Query Pruning (Partition Elimination)

Query pruning is Snowflake’s mechanism for skipping micro-partitions that cannot possibly contain rows matching a query’s WHERE clause. This dramatically reduces I/O and accelerates analytical queries.

How Pruning Works

When you run a query with a filter predicate, Snowflake’s Cloud Services layer:

1. Reads the metadata (min/max) for the filtered column across all micro-partitions
2. Compares the predicate value to each micro-partition’s min/max range
3. Marks any micro-partition where the predicate value falls outside the min/max range as pruned
4. Sends only the surviving micro-partitions to the virtual warehouse for scanning

1-- Assume the ORDERS table has 10,000 micro-partitions
2-- and ORDER_DATE is stored in insertion order (chronological)
3SELECT
4  order_id,
5  customer_id,
6  total_amount
7FROM orders
8WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
9
10-- Snowflake reads ORDER_DATE min/max for all 10,000 micro-partitions
11-- Micro-partitions where max(order_date) < '2024-01-01'  → PRUNED
12-- Micro-partitions where min(order_date) > '2024-03-31'  → PRUNED
13-- Only micro-partitions that OVERLAP the range are scanned
14-- Check pruning efficiency in Query Profile → "Partitions scanned" vs "Partitions total"

4. Natural Clustering

Before you define any explicit clustering key, Snowflake naturally orders data within micro-partitions based on insertion order. This is called natural clustering.

Natural Clustering via Insertion Order

When rows are inserted chronologically (e.g., daily ETL loads appending new records), each new micro-partition naturally contains data from a specific time period. A query filtering on a recent date range will automatically prune older micro-partitions without any clustering key defined.

When Natural Clustering is Sufficient

• Time-series tables loaded chronologically (e.g., event logs, transaction history)
• Append-only tables where new data always represents newer time periods
• Small-to-medium tables where full scans are fast regardless

5. Clustering Keys

A clustering key is one or more columns (or expressions) that you designate as the primary sort dimension for a table. When a clustering key is defined, Snowflake will re-sort data into new micro-partitions optimised for pruning on those columns.

Defining a Clustering Key

1-- Option 1: Define clustering key at table creation
2CREATE TABLE sales (
3  sale_id       NUMBER,
4  sale_date     DATE,
5  region        VARCHAR(50),
6  product_id    NUMBER,
7  amount        NUMBER(12,2)
8)
9CLUSTER BY (sale_date, region);
10
11-- Option 2: Add clustering key to an existing table
12ALTER TABLE sales CLUSTER BY (sale_date, region);
13
14-- Option 3: Use an expression as a clustering key
15-- Useful when you want to cluster on a derived value
16ALTER TABLE events CLUSTER BY (DATE_TRUNC('month', event_timestamp));
17
18-- Remove a clustering key entirely
19ALTER TABLE sales DROP CLUSTERING KEY;
20
21-- Suspend Automatic Clustering (keeps key definition, pauses background re-clustering)
22ALTER TABLE sales SUSPEND RECLUSTER;
23
24-- Resume Automatic Clustering
25ALTER TABLE sales RESUME RECLUSTER;

When to Use Clustering Keys

6. Measuring Cluster Quality: Cluster Depth

Snowflake provides the SYSTEM$CLUSTERING_INFORMATION function to evaluate how well-clustered a table is on a given set of columns.

1-- Check clustering information for a table on a specific column set
2SELECT SYSTEM$CLUSTERING_INFORMATION('sales', '(sale_date, region)');
3
4-- Example output (JSON):
5-- {
6--   "cluster_by_keys" : "LINEAR(SALE_DATE, REGION)",
7--   "total_partition_count" : 1250,
8--   "total_constant_partition_count" : 42,
9--   "average_overlaps" : 3.14,
10--   "average_depth" : 2.87,             ← Lower is better. 1.0 = perfectly clustered
11--   "partition_depth_histogram" : {
12--     "00000" : 42,
13--     "00001" : 418,
14--     "00002" : 561,
15--     "00003" : 229
16--   }
17-- }
18
19-- Key metrics to interpret:
20-- average_depth: ideally < 3. Higher means poor clustering (more overlap between micro-partitions)
21-- average_overlaps: how many other micro-partitions share overlapping key ranges
22-- total_constant_partition_count: micro-partitions with only one distinct key value (perfectly prunable)
23
24-- Check the AUTOMATIC_CLUSTERING_HISTORY view to see re-clustering activity
25SELECT *
26FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY
27WHERE TABLE_NAME = 'SALES'
28ORDER BY START_TIME DESC
29LIMIT 10;

7. Automatic Clustering

Automatic Clustering is a Snowflake-managed serverless service that continuously monitors a clustered table and triggers background re-clustering when the cluster depth degrades beyond a threshold.

SELECT SYSTEM$CLUSTERING_INFORMATION('my_large_table', '(event_date, region)');

ALTER TABLE my_large_table CLUSTER BY (event_date, region);

SELECT
  table_name,
  start_time,
  end_time,
  credits_used,
  bytes_reclustered,
  partitions_reclustered
FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY
WHERE table_name = 'MY_LARGE_TABLE'
ORDER BY start_time DESC;

SELECT SYSTEM$CLUSTERING_INFORMATION('my_large_table', '(event_date, region)');

ALTER TABLE my_large_table SUSPEND RECLUSTER;

8. Table Types and Storage Costs

Snowflake has three table types with different storage cost profiles. Understanding the differences is critical for both the exam and real-world cost optimisation.

1-- Transient table: persists across sessions, no fail-safe
2CREATE TRANSIENT TABLE staging.order_staging (
3  order_id   NUMBER,
4  raw_json   VARIANT,
5  loaded_at  TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
6)
7DATA_RETENTION_TIME_IN_DAYS = 1;  -- max for transient
8
9-- Temporary table: session-scoped, dropped automatically when session ends
10CREATE TEMPORARY TABLE session_work.calc_results (
11  customer_id   NUMBER,
12  lifetime_value NUMBER(12,2)
13);
14
15-- Convert a permanent table to transient (must recreate)
16-- There is no ALTER TABLE ... SET TYPE = TRANSIENT in Snowflake
17-- You must CREATE TRANSIENT TABLE ... AS SELECT * FROM original_table
18
19-- Check current table type
20SHOW TABLES LIKE 'ORDER_STAGING' IN SCHEMA staging;
21-- Look at the "kind" column: TABLE (permanent), TRANSIENT, TEMPORARY

9. Storage Metrics: TABLE_STORAGE_METRICS

Snowflake provides the INFORMATION_SCHEMA.TABLE_STORAGE_METRICS view (and the SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS view for historical data) to inspect exactly how much storage each table is consuming across its three storage layers.

1-- Per-table storage breakdown (current state)
2SELECT
3  table_schema,
4  table_name,
5  table_type,
6  -- Active data currently in the table
7  ROUND(active_bytes / POWER(1024, 3), 2)        AS active_gb,
8  -- Data retained for Time Travel (deleted/updated rows still accessible via AT/BEFORE)
9  ROUND(time_travel_bytes / POWER(1024, 3), 2)   AS time_travel_gb,
10  -- Data retained in fail-safe (7 days for permanent, 0 for transient/temporary)
11  ROUND(failsafe_bytes / POWER(1024, 3), 2)      AS failsafe_gb,
12  -- Total billed storage
13  ROUND((active_bytes + time_travel_bytes + failsafe_bytes) / POWER(1024, 3), 2) AS total_billed_gb
14FROM information_schema.table_storage_metrics
15WHERE active_bytes > 0
16ORDER BY total_billed_gb DESC;
17
18-- Find tables with the most fail-safe storage (potential cost saving opportunity)
19SELECT
20  table_name,
21  ROUND(failsafe_bytes / POWER(1024, 3), 2) AS failsafe_gb
22FROM information_schema.table_storage_metrics
23WHERE failsafe_bytes > 1073741824  -- > 1 GB in fail-safe
24ORDER BY failsafe_bytes DESC;
25
26-- Identify staging tables that should probably be TRANSIENT
27SELECT
28  table_name,
29  table_type,
30  ROUND(failsafe_bytes / POWER(1024, 3), 2) AS failsafe_gb
31FROM information_schema.table_storage_metrics
32WHERE table_name ILIKE '%staging%'
33AND table_type = 'BASE TABLE'  -- permanent, not transient
34ORDER BY failsafe_bytes DESC;

10. Columnar vs Row-Based Storage

Snowflake stores data in a columnar format. This is one of the most important architectural decisions underpinning its analytical performance. Understanding why columnar beats row-based storage for OLAP workloads is tested in the COF-C02 exam.

Columnar vs Row-Based Storage Layout

In row-based storage (left), all columns for a single row are stored contiguously. To read a single column across millions of rows, the database must scan every byte of every row. In columnar storage (right), all values for a single column are stored contiguously. Reading one column across millions of rows requires only a tiny fraction of the total I/O, and values within a column compress far more efficiently because they share the same data type and often similar values.

Key Terms

Flashcard Review

Practice Quizzes

Cheat Sheet

Summary

Snowflake’s storage architecture is built on immutable, columnar micro-partitions that are automatically sized and created without any DBA intervention. The rich per-column metadata stored in the Cloud Services layer powers query pruning — the ability to skip entire micro-partitions without reading them — which is fundamental to Snowflake’s query performance.

Natural clustering works well for chronologically loaded time-series data, but large tables with varied query patterns benefit from explicitly defined clustering keys. Automatic Clustering maintains cluster quality continuously as a serverless background service, charged separately from virtual warehouse credits.

For the COF-C02 exam, internalise:

• Micro-partition size: 50–500 MB compressed
• Metadata tracked: min, max, NDV, null count per column per micro-partition
• Cluster depth: lower is better, check with SYSTEM$CLUSTERING_INFORMATION
• Clustering cost: serverless credits (not warehouse credits)
• Table types: permanent (fail-safe 7d) vs transient (no fail-safe) vs temporary (session-scoped, no fail-safe)
• Storage billing: active + time_travel + failsafe bytes, visible in INFORMATION_SCHEMA.TABLE_STORAGE_METRICS