Snowflake

Snowflake is a data warehouse built around one separation: storage is cheap and infinite, compute is expensive and billed by the second. Data lives in cloud object storage as compressed columnar micro-partitions; queries run on virtual warehouses - compute clusters you size (XS to 6XL), suspend, and resume independently. Once that sinks in, most of Snowflake’s behaviour - and its billing surprises - follows naturally.

Micro-partitions and pruning

Data is stored in immutable, compressed, columnar micro-partitions of roughly 50-500 MB uncompressed. Snowflake tracks per-partition metadata - min/max of every column - and prunes aggressively at query time: a query on WHERE date = '...' reads only partitions whose date range overlaps. There are no user-managed indexes; the pruning is the index. What you control is clustering: data written roughly sorted on your common filter columns prunes far better. A clustering key exists for when natural ingest order does not match query order.

Virtual warehouses

A virtual warehouse is a named compute cluster - really a T-shirt size. Warehouses:

• Can be created, resized, suspended, and resumed independently and instantly.
• Bill per-second while running (60-second minimum per resume).
• Have independent caches: two warehouses scanning the same table each keep their own local cache.
• Multi-cluster warehouses scale out for concurrency - more simultaneous queries, not faster single queries.

The operational pattern is small warehouses that auto-suspend quickly (60-300 s idle) plus auto-resume on first query. A forgotten always-on XL warehouse is the classic four-figure lesson.

The three caches

Results come faster than expected because three cache layers stack:

1. Result cache: identical query text served free for 24 hours.
2. Local disk cache: warehouse-local SSD cache of scanned data - dies with the warehouse.
3. Metadata cache: partition statistics for pruning.

This is also why benchmark numbers vary wildly between cold and warm runs.

Semi-structured data and time travel

VARIANT columns ingest JSON, Avro, Parquet, XML as-is; queries path into them with : notation and lateral flatten. Schema-on-read without a separate pipeline stage.

Time travel lets you query AT(TIMESTAMP|OFFSET) or BEFORE a statement up to your retention window (1 day standard, up to 90 on Enterprise editions). Zero-copy cloning snapshots a table, schema, or whole database instantly as a metadata operation - clones share underlying partitions and only pay for divergence. Undrop, cloning, and time travel together replace most of what “backup” means elsewhere.

Consistency and transactions

Full ACID transactions; writes commit across micro-partitions atomically. Isolation is effectively snapshot-ish per statement. There is no MVCC tuning to do - it is a warehouse, the concurrency question is “how many warehouses run concurrently”, not lock contention.

Vector and semi-structured capabilities

Snowflake added a native VECTOR type and vector distance functions (alongside the Cortex AI functions for embeddings and LLM calls). It is a real vector workload path inside the warehouse, though not a substitute for a dedicated ANN engine at interactive latencies.

What actually breaks

• Warehouse left running: auto-suspend unset on a dev warehouse; the bill is the log.
• Queries that defeat pruning: filtering on a function of the column (WHERE DATE(ts)=... instead of WHERE ts >= ... AND ts < ...) turns a partition skip into a full scan.
• Underestimating cache effects: timing a query once and trusting it - cold vs warm runs can differ an order of magnitude.
• Clone sprawl: zero-copy clones are free to create and billed on divergence; cloned databases nobody remembers quietly accumulate storage.

Where Snowflake is the wrong choice

OLTP workloads - per-request point writes at high concurrency - are not what a columnar warehouse is for; that is a relational database. If your data volume is modest and the team is small, a PostgreSQL instance with decent indexing is usually cheaper and simpler. And if you are already deep in another vendor’s analytics stack, Snowflake’s multi-cloud story matters less than the migration cost.