Databricks & Snowflake · Chapter 6 of 10
Snowflake in practice
Virtual warehouses, micro-partitions, clustering and caching — and the three ways people waste money.
Snowflake's design decision is that storage, compute and services are fully separate. Understanding what that means practically is most of what you need.
The three layers
- Storage. Your data, held as compressed micro-partitions in object storage, managed entirely by Snowflake. You pay per terabyte per month and it is the cheap part.
- Compute. Virtual warehouses — independent clusters you start, size and stop. Several can read the same data at once without contending, which is the property that made the architecture attractive.
- Cloud services. The optimiser, metadata, security and transaction management. Largely invisible until a metadata-heavy workload makes it a line item.
Micro-partitions and clustering, which decide your performance
Snowflake automatically divides a table into micro-partitions of roughly 50–500MB uncompressed, and records the range of values each holds. A query filtering on a column then skips every micro-partition whose range cannot contain a match. That pruning is where the speed comes from, and it works well only when rows with similar values sit together.
Data loaded in date order clusters naturally by date, so date filters prune brilliantly. Filter the same table by customer and rows for one customer are scattered across every micro-partition, so pruning does nothing and you scan the table. That is what a clustering key fixes — and it is not free: Snowflake reclusters continuously in the background and you pay for it, so only define one on a large table with a genuinely selective, frequently-filtered column.
Warehouse sizing, and the thing people get backwards
| Situation | What to change | Why |
|---|---|---|
| One query is slow | Size up | More compute per cluster; roughly linear speed and cost, so the total cost of the query is often similar |
| Many users queuing | Multi-cluster | Adds clusters for concurrency. Sizing up does not help queuing |
| Spilling to disk | Size up | The query needs more memory than the warehouse has — visible in the query profile |
| Warehouse idle between queries | Lower auto-suspend | You pay per second while it runs. 60 seconds is usually right; the default is often too generous |
Caching, and why your benchmark is wrong
- Result cache — an identical query against unchanged data returns in milliseconds and consumes no compute, for 24 hours. This is why re-running a query to 'confirm' an optimisation tells you nothing.
- Local disk cache on the warehouse — warm after first use, gone when the warehouse suspends. So aggressive auto-suspend saves credits and costs you cache warmth; on a busy dashboard warehouse the trade is worth thinking about.
- Metadata —
COUNT(*)and min/max often answer from metadata without scanning at all.
The features worth knowing by name
- Zero-copy cloning — clone a database instantly with no storage cost until divergence. The correct way to make a dev environment, and people still copy data instead.
- Time Travel — query or restore a table as of a point in the retention window. Retention beyond the default costs storage.
- Streams and Tasks — change tracking plus scheduling, which is how incremental pipelines are built natively.
- Snowpark — Python, Java and Scala executing inside Snowflake, which is how it answers the ‘we need more than SQL’ objection.
- Secure data sharing — share live data with another account without copying it. Genuinely differentiated and it is why data-sharing use cases go here.