Snowflake (Cloud Data Warehouse)

To place Snowflake, first separate two kinds of database workload. OLTP (Online Transaction Processing) is the transactional database behind an app: many small, fast reads and writes — insert an order, update a balance, fetch one user. Postgres and MySQL are OLTP systems, tuned for correctness and low-latency single-row operations. OLAP (Online Analytical Processing) is the opposite: a few enormous queries that scan millions or billions of rows to answer analytical questions — "total revenue by region by month over three years." A data warehouse is an OLAP system built for exactly this.

A cloud data warehouse is a warehouse delivered as a managed service on cloud infrastructure — no servers to provision, storage and compute that scale on demand, pay-as-you-go billing. Snowflake is the leading example (BigQuery and Redshift are peers). You point it at your data, run SQL, and it handles the machinery underneath.

The insight that made Snowflake distinct is the separation of storage and compute. In a traditional warehouse, the machines that hold the data are the same machines that query it, so to get more query power you must also buy more storage, and to store more data you must also pay for more compute — the two are welded together. Snowflake splits them into independent layers that scale separately and are billed separately.

The practical payoff: your data sits in one shared, cheap storage pool, and you spin up as much or as little query compute as you need, whenever you need it, without moving or copying the data. Ten analysts can each run heavy queries on their own compute cluster against the same data without slowing each other down.

Snowflake's architecture is three decoupled layers stacked on top of each other. Naming each one shows exactly where data lives, where queries run, and where the coordination happens.

• Storage layer — all data lives here, once, in cloud object storage (S3-class). Snowflake stores it in a compressed columnar format, split into micro-partitions — small chunks (tens of megabytes) that each carry metadata about the range of values they contain. This is the cheap, shared foundation every query reads from.
• Compute layer — made of virtual warehouses, which are independent clusters of compute. Each virtual warehouse runs queries against the shared storage. You can have many at once, sized independently, and one warehouse's load never touches another's — this is how Snowflake isolates the ETL job from the analyst's dashboard.
• Cloud services layer — the coordinator. It holds metadata (what tables and micro-partitions exist), the query optimizer (which decides how to run a query efficiently), authentication and security, and transaction management. It is the brain that ties storage and compute together.

Why store data by column instead of by row? An analytical query typically touches a few columns across many rows — "average price of all orders." A row-oriented store must read every full row (including columns you don't need); a columnar store keeps each column together, so the query reads only the price column and skips the rest. Columns of similar values also compress far better, shrinking what must be read from disk.

Pruning builds on this. Because each micro-partition records the min and max values it holds, when your query filters WHERE order_date = '2026-01-05', Snowflake checks each partition's metadata and skips every partition whose date range can't contain that value. A query that would naively scan a terabyte can end up reading a few gigabytes. Columnar layout plus pruning is what lets a warehouse answer huge-scan queries in seconds.

Because compute is decoupled, a virtual warehouse can be resized or spun up and down on demand. Need a heavy report to finish faster? Scale the warehouse up for that query, then back down. Snowflake bills compute per second while a warehouse is running and can auto-suspend it after idle time, so you pay for compute only while queries actually run. Storage is billed separately, by volume. This elasticity is the direct commercial benefit of separating the two layers.

Three features fall naturally out of the architecture:

• Zero-copy cloning — you can clone a whole table or database instantly without duplicating the underlying data. The clone just points at the same micro-partitions; new storage is used only when the clone diverges. Perfect for spinning up a full copy of production for testing.
• Time travel — Snowflake retains old versions of data for a configurable window (up to days), so you can query a table as it was at a past timestamp, or recover from an accidental delete. SELECT ... AT (TIMESTAMP => ...).
• Data sharing — you can grant another Snowflake account live, read-only access to your data without copying or moving it; they query it on their own compute. Sharing data becomes a permission, not an export.

CREATE TABLE orders_sandbox CLONE orders;           -- instant, no data copied
SELECT * FROM orders AT (OFFSET => -3600);           -- table as it was an hour ago

Snowflake and an OLTP database are not competitors — they do different jobs, and a healthy system uses both. Keep the app's live data in an OLTP database and copy it into the warehouse for analysis.

Rule of thumb: never point user-facing app traffic at a warehouse, and never run big analytical scans against your transactional database — move the data over (via CDC or a batch pipeline) and let each system do what it is built for.

• OLTP databases run the app with fast single-row transactions; OLAP data warehouses answer huge analytical scans — Snowflake is a managed cloud OLAP warehouse.
• Snowflake's defining idea is separating storage and compute so each scales and is billed independently.
• Three layers: shared columnar storage in micro-partitions, independent virtual warehouses for compute, and a cloud services layer for metadata, optimization, and security.
• Columnar storage reads only the needed columns and compresses well; pruning skips micro-partitions whose value ranges can't match the query, turning terabyte scans into gigabyte reads.
• Compute is elastic and billed per second with auto-suspend, so you pay only while queries run.
• Zero-copy cloning, time travel, and data sharing fall out of the architecture — instant copies, querying past states, and granting live access without moving data.
• Use a warehouse for analytics and a transactional DB for live app traffic; move data between them rather than overloading either.

• [ ] I can define OLTP and OLAP and give an example database for each.
• [ ] I can explain what a cloud data warehouse is and name Snowflake's peers.
• [ ] I can state why separating storage and compute is Snowflake's key idea.
• [ ] I can name the three layers and describe each one's responsibility.
• [ ] I can explain columnar storage and how micro-partition pruning speeds queries.
• [ ] I can explain per-second billing and auto-suspend.
• [ ] I can describe zero-copy cloning, time travel, and data sharing.
• [ ] I can decide when to use a warehouse versus a transactional database.