Snowflake Architecture and Features
The three layers
Snowflake separates storage, compute, and cloud services. Data lives in cloud object storage as immutable, compressed, columnar micro-partitions (roughly 50–500 MB uncompressed). Because partitions are immutable, every DML creates new partitions and marks old ones deleted; Time Travel and Fail-safe retain the old versions. The cloud services layer holds metadata, the optimizer, transaction management, and security - the brain, not the muscle. Virtual warehouses are independent MPP compute clusters with local SSD caches that are dropped when the warehouse suspends.
What questions test
- Pruning is metadata-driven: the optimizer uses per-partition min/max and distinct counts, not indexes. There is no user-created index.
- Clustering is not partitioning; a clustering key only improves pruning and is maintained via automatic clustering, which consumes credits.
- Warehouse sizes double in compute; scaling up helps large or skewed queries, scaling out (multi-cluster) helps concurrency.
- Cloud services credits are billed only when daily consumption exceeds 10% of warehouse credits.
Traps: assuming the local disk cache survives suspension; assuming a bigger warehouse always speeds up a small query (result cache and overhead can dominate); confusing a multi-cluster warehouse with multiple warehouses; assuming storage and compute scale together.
Rule of thumb: if a query scans far more partitions than it returns data from, fix pruning/clustering before resizing. If many similar concurrent queries queue, add clusters; if one query spills, resize up. Worked example: a 40-minute query returns 2% of partitions; a clustering key on the filter column cuts scanned partitions 10x, while a resize would cost 2x per hour for maybe 2x speed.