Snowflake

Snowflake is a cloud data warehouse that became the benchmark for the category by making one architectural choice central: separating storage from compute. Your data lives once in cloud storage; independent, elastically-sized compute clusters (virtual warehouses) query it. You can run a tiny warehouse for a dashboard and a huge one for a nightly batch, scale each up or down in seconds, spin up isolated warehouses per team so they don't contend — and pay for storage and compute separately.

That decoupling, delivered as a fully-managed SQL service across AWS, Azure, and GCP, is why Snowflake led the shift from on-premise data warehouses to the cloud and anchors the modern data stack for a huge number of companies. It sits within the broader data warehousing landscape as the pure-play warehouse benchmark — and the reference point every competitor is measured against.

TL;DR

Architecture: The Key Idea

Traditional warehouses coupled storage and compute — scaling one meant scaling both, and workloads fought over shared resources. Snowflake splits them into three layers:

The consequences are what people pay for:

Quick Example

Snowflake is SQL — the distinctive parts are the compute control and its unique commands:

Standout Features

Beyond the architecture, a few capabilities are genuinely differentiating:

Zero-Copy Cloning

CREATE TABLE ... CLONE makes an instant copy of a table, schema, or entire database of any size, using no additional storage — the clone shares the original's underlying data blocks, and only diverging changes consume new space (copy-on-write). This transforms workflows: spin up a full-size dev/test environment with production data in seconds and at near-zero cost, instead of copying terabytes. It's a headline reason teams love Snowflake.

Time Travel and Fail-Safe

Snowflake retains historical versions of data (configurable, up to 90 days on higher tiers), so you can query data as it existed in the past or undrop an accidentally-deleted table. The bad UPDATE that mangled a table becomes recoverable with a SELECT ... BEFORE and a re-insert — a safety net traditional warehouses lack.

Secure Data Sharing

You can grant another Snowflake account live, read-only access to your data without copying it — they query your data in place, always current, with no ETL or file transfer. This underpins the Snowflake Marketplace and data-collaboration use cases, and is a fundamentally different model from emailing extracts around.

Semi-Structured Data and More

Native handling of JSON/Parquet/Avro via the VARIANT type (query nested data with SQL directly), plus a growing platform: Snowpark (run Python/Java/Scala transformations in-warehouse), Streamlit apps, and Cortex AI functions. Snowflake has been expanding from "warehouse" toward "data platform."

Snowflake vs Alternatives

The primary comparison is Snowflake vs BigQuery: BigQuery is serverless (no warehouses to size — you submit SQL and Google handles compute, billing per TB scanned or via slots), which is simpler but gives less control; Snowflake's explicit virtual warehouses give granular control over compute cost and performance, plus multi-cloud portability. Both are excellent; the choice often comes down to cloud alignment (all-in on GCP → BigQuery), how much compute control you want, and pricing shape. Redshift makes most sense deep in the AWS ecosystem; Databricks when Spark/ML share the platform (the "lakehouse" convergence).

Common Mistakes

Leaving Warehouses Running

Compute bills accrue per-second while a warehouse runs, even idle. A warehouse without AUTO_SUSPEND, or oversized "just in case," burns money continuously — the classic Snowflake surprise bill. Set aggressive auto-suspend, right-size warehouses to the workload, and monitor spend. See Cloud Costs.

Treating It Like an OLTP Database

Snowflake is OLAP — built for large analytical scans, not high-frequency single-row reads/writes. Using it as an application backend (per-request point lookups, transactional updates) is slow and expensive. Keep transactional workloads on PostgreSQL/MySQL; use Snowflake for analytics.

SELECT * on Wide Tables

Snowflake's columnar storage and per-scan cost mean reading all columns of a wide table when you need three wastes compute and money. Select only needed columns — the same warehouse discipline that makes columnar engines fast.

No Cost Governance

Self-serve SQL access plus per-second billing plus easy warehouse creation equals runaway costs without controls. Use resource monitors (spend limits/alerts), separate warehouses per team for cost attribution, and review expensive queries. Cost management is an ongoing operational discipline, not a one-time setup.

Ignoring Clustering on Huge Tables

Very large tables benefit from clustering keys (organizing data by common filter columns) so queries prune unneeded data — analogous to indexing intent. Not needed for most tables, but the wrong-or-missing clustering on a multi-terabyte table is a real performance/cost problem.

FAQ

What makes Snowflake different from older data warehouses?

The separation of storage and compute, delivered as a fully-managed cloud service. Older warehouses coupled the two (scaling meant scaling both; workloads contended), required significant tuning (indexes, distribution keys, vacuuming), and ran on fixed infrastructure. Snowflake made compute elastic and independent, eliminated most tuning, and runs across clouds — which is why it drove the cloud-warehouse shift.

Snowflake or BigQuery?

Both are top-tier. BigQuery is serverless (no compute to manage, GCP-only) — simpler, less control, ideal if you're on GCP. Snowflake gives explicit compute control via virtual warehouses and is cloud-neutral (AWS/Azure/GCP) — better when you want to tune cost/performance per workload or avoid cloud lock-in. Decide by cloud alignment, desired control, and pricing model; both handle serious scale well.

Is Snowflake expensive?

It can be, because per-second compute billing rewards discipline and punishes neglect — an idle oversized warehouse or unbounded self-serve access produces surprise bills. Used well (auto-suspend, right-sized warehouses, resource monitors, columnar-aware queries), it's cost-effective. The cost is highly controllable but not automatically controlled — governance is on you. See Cloud Costs.

Can Snowflake handle semi-structured and unstructured data?

Semi-structured, natively — the VARIANT type stores JSON/Parquet/Avro and you query nested fields with SQL directly, no rigid pre-defined schema. It also handles unstructured files (via stages and functions) and, increasingly, ML/AI workloads via Snowpark and Cortex. It's evolving from a pure warehouse into a broader data platform.

What's a virtual warehouse?

A named, independently-sized compute cluster (XS to 6XL) that executes your queries. You can run many simultaneously against the same data — one per team or workload — so they don't contend, and start/stop/resize each in seconds, paying only while running. It's the unit of compute (and compute cost) in Snowflake, and the mechanism behind its workload isolation and elasticity.

Related Topics

References