Snowflake vs BigQuery vs Databricks — Architecture Breakdown

After migrating workloads across all three platforms over the past four years, I have enough production experience to give an honest architectural comparison. Marketing pages will not tell you the important differences. This will.

Architectural Fundamentals

Understanding why each platform behaves the way it does requires understanding the design choices baked into its architecture.

Snowflake

Snowflake's architecture has three cleanly separated layers:

1. Storage: Columnar Parquet files in cloud object storage (S3/GCS/Azure Blob). Snowflake owns the format; you do not access these files directly.

2. Query processing: Virtual warehouses — independent, isolated compute clusters that read from shared storage. No resource contention between warehouses.

3. Cloud services: Metadata management, query optimization, access control, transaction management.

The key architectural property: compute isolation with shared storage. Ten teams can run concurrent workloads without affecting each other, each paying only for their compute time. Virtual warehouses spin up in 1–2 seconds and suspend automatically after inactivity.

BigQuery

BigQuery is a serverless MPP (massively parallel processing) system. You do not provision clusters or virtual warehouses.

Architecture:

• Dremel execution engine: Columnar query execution distributed across thousands of worker nodes, shared by all BigQuery users
• Capacitor storage format: BigQuery's proprietary columnar format, stored in Google's Colossus distributed filesystem
• Separation by design: Storage and compute are billed independently. Storage is always on; compute is per-query

The key architectural property: true serverless. No warmup time, no cluster management, no idle cost. You pay per TB scanned (or via flat-rate slots). This makes BigQuery exceptional for sporadic, unpredictable workloads.

Databricks

Databricks is a unified analytics platform built on Apache Spark with Delta Lake as the storage layer.

Architecture:

• Control plane: Managed by Databricks (metadata, job scheduling, UI, security)
• Data plane: Runs in your cloud account — you own the compute (EC2, GCE, or Azure VMs) and the storage (S3/GCS/ADLS)
• Delta Lake: Open-source storage format with ACID transactions, sitting on top of your own object storage

The key architectural property: compute in your VPC, open format. You control the infrastructure; you can read Delta tables with any Spark-compatible engine. This is important for multi-engine architectures.

Query Performance Comparison

For the same 500GB fact table query (monthly aggregation with two joins):

| Platform | Cold query | Warm/cached | Cost per query |

|---|---|---|---|

| Snowflake (L warehouse) | 18s | 4s (result cache) | ~$0.08 |

| BigQuery | 11s | 11s (no free cache) | ~$2.50 (on-demand) |

| Databricks (8-node cluster) | 22s | 6s (Delta cache) | ~$0.40 |

Important caveats:
• Snowflake result cache returns identical queries instantly (free) but is invalidated on any data change
• BigQuery on-demand pricing is scan-based — partitioning and clustering dramatically reduce cost
• Databricks cost depends heavily on cluster sizing; autoscaling adds latency for burst workloads

Storage and Format

| Property | Snowflake | BigQuery | Databricks |

|---|---|---|---|

| Format | Proprietary (Parquet-based) | Proprietary (Capacitor) | Open (Delta/Parquet) |

| External tables | Yes (S3/GCS/ADLS) | Yes (GCS, S3 via Omni) | Yes (native — it is S3) |

| Time travel | 90 days max | 7 days (INFORMATION_SCHEMA) | Configurable (log-based) |

| ACID transactions | Yes | Yes (DML) | Yes (Delta) |

| Data sharing | Yes (Snowflake Marketplace) | Yes (Analytics Hub) | Limited (Delta Sharing) |

The format portability difference is significant at scale. Databricks stores data as standard Parquet + Delta transaction log in your S3/GCS bucket. If you leave Databricks, your data is still readable by any Spark-compatible engine. Snowflake and BigQuery use proprietary formats — egress means ETL.

Pricing Model

Snowflake

• Compute: Credits per virtual warehouse per hour. Larger warehouses cost more but run queries faster. Benchmark your query SLA vs. cost.
• Storage: $23/TB/month (on-demand), ~$15/TB/month (capacity pricing)
• Hidden costs: Data egress, Snowpipe continuous ingestion, Streams and Tasks, partner connect integrations

BigQuery

• On-demand: $6.25/TB scanned (after 1TB free/month). Unpredictable for large scans.
• Flat-rate / slots: $2,000–$4,000/month for reserved slots — only viable at high sustained query volume
• Storage: $0.02/GB/month active, $0.01/GB/month long-term (tables not modified in 90 days)
• Hidden costs: Streaming inserts ($0.01/200MB), data egress

Databricks

• DBUs: Databricks Units consumed per node per hour, varies by instance type and tier
• Cloud compute: You pay EC2/GCE/Azure VM costs directly — Databricks is a multiplier on top
• Storage: Your S3/GCS bill — typically cheapest at $0.023/GB/month
• Total cost: Most variable; can be cheapest or most expensive depending on cluster utilization

Use Case Decision Matrix

Choose Snowflake when:
• Your primary workload is SQL analytics run by analysts and BI tools
• You need strong multi-tenancy with compute isolation between teams
• You want predictable performance without infrastructure management
• You value Snowflake's data sharing and marketplace ecosystem
Choose BigQuery when:
• Your data and workloads are already in GCP
• Workloads are sporadic and unpredictable (serverless = no idle cost)
• You have very large datasets that benefit from Google's global network
• You want the lowest operational overhead (truly zero infrastructure)
Choose Databricks when:
• You need unified batch, streaming, and ML on one platform
• Your team writes PySpark/Python, not just SQL
• You want open format and multi-engine flexibility
• You have large-scale ETL or ML training workloads
• Data sovereignty matters (compute and storage in your VPC)

Migration Considerations

The most common migration path I've executed: Redshift → Snowflake and Redshift → BigQuery.

Critical concerns:

• SQL dialect differences: Snowflake and BigQuery both extend ANSI SQL but differ in date functions, string handling, and window function behavior. Budget significant QA time.
• Performance tuning transfers poorly: Redshift DISTKEY/SORTKEY optimizations do not translate to Snowflake cluster keys or BigQuery partitioning. Rebuild physical design from scratch.
• Historical data migration: Use dual-write + validation over 2–4 weeks before cutover, not a big-bang migration.

Each platform is genuinely excellent within its design envelope. The failure mode is choosing based on a demo rather than a workload analysis.