Understanding Snowflake Storage Optimization

Official answer

For standard Snowflake tables, loaded data is reorganized into compressed columnar micro-partitions. Snowflake manages their physical layout and keeps metadata that helps skip irrelevant partitions and columns during a query.

Micro-partitions are created automatically from the data's loading/insertion order; a user-defined clustering key is not required. Do not assume that Snowflake automatically selects the ideal explicit key for every workload. Optional clustering maintenance can improve a demonstrated access pattern, but it has compute and retained-storage costs.

Columnar storage can reduce reads when a query selects a small subset of columns, and compression can reduce the stored footprint. Actual benefit depends on data distribution, types and query predicates. Heavy overlap in a filtered column's ranges can reduce pruning even when the table is compressed well.

Use appropriate types and retention policies, and measure active data alongside Time Travel, Fail-safe and clone-related storage where applicable. Dropping a column or deleting rows does not necessarily reclaim all historical storage immediately.

Compare storage bytes/cost, scanned partitions/bytes, runtime and maintenance credits on representative workloads. Daily active users does not measure compression effectiveness. Preserve data meaning while optimizing: shortening identifiers, dropping required history or changing numeric precision merely to save space can break correctness.

Micro-partitions and clustering, clustering keys, historical storage costs