📋 What makes a cloud warehouse
All four platforms share three architectural properties that distinguish them from traditional on-premise databases:
| Property | What it means | Why it matters |
|---|---|---|
| Columnar storage | Data stored column-by-column rather than row-by-row | Analytics queries read only the columns they need — dramatically less I/O for SELECT region, SUM(amount) over a 10-billion-row table |
| MPP (Massively Parallel Processing) | Query is split across dozens or hundreds of nodes | A 60-second query on one node takes 1-2 seconds across 60 nodes — linear scale-out for read-heavy workloads |
| Separation of storage and compute | Data lives in object storage (S3/GCS); compute nodes are spun up on demand | You pay for storage continuously but only pay for compute when running queries. Scale each independently. Pause compute overnight. |
🏢 The four platforms at a glance
❄️ Snowflake
Signature feature: Virtual warehouses — dedicated, independently-sized compute clusters. Multiple teams can query the same data with separate clusters, zero contention.
- Auto-suspend / auto-resume — no idle cost
- Zero-copy cloning — instant environment copies
- Time Travel — query any table as-of 90 days ago
- Multi-cloud: AWS, GCP, Azure
📊 BigQuery
Signature feature: Truly serverless — no clusters to manage. You write SQL; Google figures out the compute. Pay per byte scanned (on-demand) or reserve a flat slot rate.
- Partitioned and clustered tables reduce scan cost
- INFORMATION_SCHEMA for usage/cost introspection
- BigQuery ML — train models in SQL
- GCP-native: Pub/Sub, Dataflow, Vertex AI
🔴 Redshift
Signature feature: Deepest AWS integration. RA3 nodes separate storage (S3) from compute. Redshift Spectrum queries S3 directly without loading data.
- COPY command — fastest bulk load from S3
- Redshift ML — CREATE MODEL from SQL
- Concurrency Scaling — burst read capacity
- Best when already deep in AWS ecosystem
⚡ Databricks
Signature feature: Unified analytics platform — SQL, Python, ML, and streaming in one environment. Delta Lake provides ACID on top of cloud object storage.
- Unity Catalog — fine-grained governance across clouds
- Photon engine — vectorised C++ SQL engine
- MLflow native integration for experiment tracking
- Best for teams that do both data engineering and ML
💻 Window function query — one per platform
The same rolling 7-day revenue query, adapted to each platform's SQL dialect and optimisation hints:
Snowflake
BigQuery
Redshift
Databricks (Delta Lake)
⚖️ Full comparison table
| Dimension | ❄️ Snowflake | 📊 BigQuery | 🔴 Redshift | ⚡ Databricks |
|---|---|---|---|---|
| Pricing model | Credits per second of warehouse uptime | Per byte scanned (on-demand) or flat slot reservation | Per node-hour (provisioned) or per RPU-second (Serverless) | DBU (Databricks Unit) per compute type + cloud VM cost |
| Concurrency | Excellent — separate virtual warehouses per team, no queuing | Excellent — serverless, auto-scales slots | Good — Concurrency Scaling adds burst capacity | Good — cluster auto-scaling; SQL Warehouse for BI |
| ML integration | Snowpark (Python in-warehouse), Cortex AI | BigQuery ML — train XGBoost, linear models in SQL | Redshift ML — CREATE MODEL → SageMaker under the hood | Best-in-class — MLflow, Feature Store, Model Serving native |
| Streaming ingest | Snowpipe (S3/SQS trigger) or Kafka connector | BigQuery Storage Write API, Pub/Sub subscriptions | Kinesis Firehose → Redshift via COPY | Structured Streaming (Spark), Delta Streaming, Kafka DLT |
| Open format support | Iceberg external tables (read + write via Iceberg REST) | BigLake — external tables over Parquet/ORC/Avro | Spectrum — external tables over S3 Parquet/ORC | Native Delta Lake; Iceberg + Hudi via readers |
| Best for | Multi-cloud, high-concurrency BI, data sharing between orgs | GCP shops, ad-hoc analytics, teams that hate ops | Deep AWS integration, existing Redshift investments | Unified data + ML platform, lakehouse architecture |
💸 Cost gotchas — what surprises teams
A Snowflake virtual warehouse billed at XL size (~$3.20/credit) left running overnight (8 hours) burns ~25 credits — over $80 for nothing. Always set AUTO_SUSPEND = 60 (seconds). Monitor with ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY.
BigQuery's on-demand pricing charges $6.25/TB scanned. A SELECT * against a 5 TB table with no partition filter costs ~$31 every time it runs. Fix: always add a WHERE date_column BETWEEN ... filter, and partition every large table by date. Use INFORMATION_SCHEMA.JOBS to audit query costs.
RA3 nodes are priced per hour whether your cluster is idle or processing. Teams often provision the cluster for peak load and run at 10% utilisation overnight. Consider migrating to Redshift Serverless (billed per RPU-second) or scheduling cluster pause/resume via AWS Lambda.
All-purpose clusters (for interactive notebooks and ETL) are billed at a higher DBU rate than SQL Warehouses (optimised for BI). Analysts running BI queries on all-purpose clusters can cost 2–3x more than the same query on a SQL Warehouse. Enforce job type → cluster type mapping in Unity Catalog policies.
🧭 When to pick each
| Situation | Recommended platform | Reason |
|---|---|---|
| Already on AWS, want lowest operational overhead | Redshift Serverless | No cluster management; COPY from S3 is the fastest bulk load on AWS |
| GCP-native stack, ad-hoc analysts, hate infrastructure | BigQuery | Zero ops; pay-per-query; direct integration with Looker, Vertex AI |
| Multi-cloud data sharing, high BI concurrency, data marketplace | Snowflake | Virtual warehouses eliminate contention; Snowflake Data Sharing is unmatched |
| Unified data + ML platform, lakehouse, Spark workflows | Databricks | Delta Lake + MLflow + Photon in one platform; best for DS+DE teams |
| Early stage startup, tight budget, local development | DuckDB (local) + BigQuery (prod) | DuckDB is free locally; BigQuery has a generous 1 TB/month free tier |
Choosing a warehouse is partly a technical decision and mostly a strategic one. If your data engineers know Spark, go Databricks. If your infrastructure team runs everything on GCP, BigQuery wins by default. If your customers want to share data with you without copying it, Snowflake Data Sharing is the answer. The SQL differences are minor — the ecosystem integration differences are significant.
🧠 Quiz — 4 questions
Next: The Lakehouse — Iceberg, Delta Lake, Hudi.
🎤 Interview Q&A
SELECT region, SUM(amount) FROM payments GROUP BY region needs only two of twenty columns, but scans every row. In a row store (like Postgres), each row is stored together on disk: reading any column means reading all columns on every page. For 20 columns, you read 20x more data than you need. In a columnar store, all values for region are stored together, and all values for amount are stored together. The query reads only those two columns — 90% less I/O. Columnar storage also compresses exceptionally well because similar values are adjacent: a column of dates, all from the same month, compresses by 10x or more with dictionary or run-length encoding. The combination of less I/O and better compression is why columnar analytics queries run orders of magnitude faster than equivalent row-store queries at scale.
AUTO_SUSPEND = 60 so the warehouse hibernates after 60 seconds of inactivity. (3) Query batching — if 50 queries can be grouped and run together, keep the warehouse active for one burst rather than starting and stopping 50 times (each start pays a minimum 60-second billing increment). (4) Query caching — Snowflake caches query results for 24 hours; repeated identical queries are free. (5) Clustering keys — on large tables, clustering on the filter column reduces micro-partition scanning, making each query cheaper. Monitor per-query credit consumption with ACCOUNT_USAGE.QUERY_HISTORY joining to WAREHOUSE_METERING_HISTORY.
✅ Key takeaways
- All cloud warehouses share three foundations: columnar storage (read only needed columns), MPP (parallel execution across nodes), and separated storage/compute (pay for compute only when querying).
- Snowflake's virtual warehouses give each team isolated compute — ideal for high-concurrency BI and cross-organisation data sharing. Always set
AUTO_SUSPENDto avoid idle credit burn. - BigQuery's serverless model eliminates all cluster management — ideal for GCP teams and ad-hoc analysts. The cost trap: non-partitioned tables scan everything. Always partition large tables by date and always filter on the partition column.
- Redshift is the natural choice for deep AWS integration. Redshift Spectrum lets you query cold S3 data without loading it, at $5/TB scanned — ideal for archive queries and multi-engine data sharing.
- Databricks is the unified platform for teams that combine data engineering (Spark), SQL analytics (Photon), and machine learning (MLflow) — Delta Lake provides the ACID foundation across all workloads.
Navigate the cloud warehouse landscape with confidence
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →