Snowflake Architecture Explained, vs Databricks and BigQuery

Snowflake is one of the most widely used cloud data warehouses, and its architecture explains most of its strengths and most of its cost surprises. This article walks through the three layers, the features that follow from them, and how Snowflake compares with the two platforms it is most often weighed against: Databricks and Google BigQuery.

The core idea: storage and compute are separate

In a traditional warehouse appliance, storage and processing live on the same machines. If you need more compute for month-end reporting, you buy more of both, and a heavy data load slows down everyone's dashboards. Snowflake separates them. All data sits once in cloud object storage (on AWS, Azure or Google Cloud), and any number of independent compute clusters can read it at the same time. That single decision is what makes the rest of the design work.

Layer 1: database storage

When data is loaded, Snowflake reorganises it into its own compressed, columnar format and splits each table into micro-partitions of roughly 50 to 500 MB of uncompressed data. For every micro-partition it keeps metadata such as the minimum and maximum value of each column. At query time this metadata lets Snowflake skip partitions that cannot contain matching rows, which is called pruning. You never manage files, indexes or vacuuming yourself; the trade-off is that the storage format is Snowflake's own (though Snowflake can also work with Apache Iceberg tables in your own storage).

Layer 2: query processing with virtual warehouses

Compute comes from virtual warehouses: clusters you create in T-shirt sizes (X-Small, Small, Medium and up), each size roughly doubling the compute and the credits per hour. Warehouses can suspend automatically when idle and resume on the next query, and billing is per second of running time (with a one-minute minimum each time one starts). A multi-cluster warehouse adds clusters automatically when many users query at once, which helps BI concurrency.

Because warehouses are independent, the standard pattern is one per workload: loading, transformation and BI never slow each other down, and each has its own cost line.

Layer 3: cloud services

A shared services layer handles everything that is not scanning data: authentication, access control, the metadata catalog, query parsing and optimisation, and transaction management. It also serves the result cache, so re-running an identical query on unchanged data can return instantly without using a warehouse.

Features that come from this design

• Time Travel: query or restore data as it was at an earlier point, within a retention window that depends on edition and settings.
• Zero-copy cloning: create a full copy of a table, schema or database instantly; storage is only used for data that changes afterwards. Ideal for test environments.
• Secure data sharing: give another account live, read-only access to your data without copying it.
• Workload isolation: separate warehouses for separate jobs, as shown above.

-- A separate, small warehouse for each workload; it stops billing when idle
CREATE WAREHOUSE transform_wh
  WITH WAREHOUSE_SIZE = 'XSMALL'
       AUTO_SUSPEND = 60          -- seconds of inactivity before suspending
       AUTO_RESUME = TRUE
       INITIALLY_SUSPENDED = TRUE;

-- Time Travel: query a table as it was one hour ago
SELECT * FROM analytics.orders AT(OFFSET => -3600);

-- Zero-copy clone: a full copy for testing, no data duplicated until it changes
CREATE TABLE analytics.orders_dev CLONE analytics.orders;

-- Recover a table dropped by mistake (within the Time Travel window)
UNDROP TABLE analytics.orders;

Snowflake vs Databricks vs BigQuery

All three separate storage from compute and can serve as the storage layer in a modern data engineering architecture. The differences are in philosophy and in who they suit best.

	Snowflake	Databricks	BigQuery
Origin and focus	SQL data warehouse, fully managed	Spark-based lakehouse for data engineering and ML	Serverless warehouse on Google Cloud
Storage	Snowflake-managed columnar format (Iceberg tables also supported)	Open Delta Lake tables in your own object storage	Google-managed columnar storage (BigLake for open formats)
Compute you manage	Virtual warehouses you size and schedule	Clusters and SQL warehouses (serverless options available)	None to size; capacity measured in slots
Pricing model	Credits per second of warehouse time, plus storage	Compute usage units (DBUs) plus your cloud costs	Per data scanned (on-demand) or reserved capacity, plus storage
Strongest at	SQL analytics, concurrency, ease of administration, data sharing	Large-scale Python/Spark processing, ML, streaming, open formats	Zero-administration analytics, very large scans, Google ecosystem
Watch out for	Warehouses left running, oversizing	Cluster configuration and cost governance	Unpartitioned queries scanning whole tables

How to choose

• Mostly SQL, BI and analysts: Snowflake or BigQuery. Pick BigQuery if you are already on Google Cloud and want no infrastructure at all.
• Heavy data engineering in Python, machine learning, streaming, or a strong preference for open file formats: Databricks.
• Mixed: this is common. Open table formats such as Apache Iceberg increasingly let one copy of data be read by more than one engine.

Whatever you pick, run a proof of concept on your own data and queries and measure cost per workload. Vendor benchmarks rarely match real usage.

Snowflake cost tips

1. Set AUTO_SUSPEND to 60 seconds on every warehouse unless there is a reason not to.
2. Start at X-Small and size up only when a workload is measurably too slow; doubling size doubles credits per hour.
3. Use separate warehouses per workload so each cost is visible, and set resource monitors with credit limits.
4. Make transformations incremental so nightly jobs process only new data.
5. Add clustering keys only to very large tables that are filtered on the same column, and check pruning in the query profile first.

Summary

Snowflake's three layers (shared columnar storage, independent virtual warehouses, and a cloud services layer) explain its workload isolation, instant cloning and Time Travel, as well as why idle warehouses are the main cost risk. Databricks leans towards open formats and Spark-based engineering and ML; BigQuery removes compute management entirely. Next in this series: loading data into the warehouse with ETL or ELT and scheduling it with Apache Airflow.