📋 What makes a cloud warehouse

All four platforms share three architectural properties that distinguish them from traditional on-premise databases:

PropertyWhat it meansWhy it matters
Columnar storageData stored column-by-column rather than row-by-rowAnalytics 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 nodesA 60-second query on one node takes 1-2 seconds across 60 nodes — linear scale-out for read-heavy workloads
Separation of storage and computeData lives in object storage (S3/GCS); compute nodes are spun up on demandYou 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

snowflake_window.sql Lesson 31 · rolling 7-day revenue with clustering key

BigQuery

bigquery_window.sql Lesson 31 · partitioned table, cost-efficient scan
BigQuery query information
Query complete (1.2s elapsed, 48.3 MB processed) Billed: 48.3 MB · Estimated cost: $0.000241 · Slot-time: 0.4s

Redshift

redshift_window.sql Lesson 31 · Redshift Spectrum + DISTKEY hint

Databricks (Delta Lake)

databricks_window.sql Lesson 31 · Delta table with Z-ORDER optimisation

⚖️ 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

❄️ Snowflake: warehouse left running

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: on-demand queries on non-partitioned tables

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.

🔴 Redshift: over-provisioned node type

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.

⚡ Databricks: all-purpose clusters vs SQL Warehouses

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

SituationRecommended platformReason
Already on AWS, want lowest operational overheadRedshift ServerlessNo cluster management; COPY from S3 is the fastest bulk load on AWS
GCP-native stack, ad-hoc analysts, hate infrastructureBigQueryZero ops; pay-per-query; direct integration with Looker, Vertex AI
Multi-cloud data sharing, high BI concurrency, data marketplaceSnowflakeVirtual warehouses eliminate contention; Snowflake Data Sharing is unmatched
Unified data + ML platform, lakehouse, Spark workflowsDatabricksDelta Lake + MLflow + Photon in one platform; best for DS+DE teams
Early stage startup, tight budget, local developmentDuckDB (local) + BigQuery (prod)DuckDB is free locally; BigQuery has a generous 1 TB/month free tier
The real decision is ecosystem lock-in

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

Question 1 of 4
What is the key architectural advantage of separating storage from compute in a cloud warehouse?
Question 2 of 4
A BigQuery table has 5 TB of data. A query with no partition filter runs every hour. What is the most cost-effective fix?
Question 3 of 4
Which Snowflake feature allows you to create an instant copy of a database or table without duplicating the underlying data?
Question 4 of 4
A data team wants to run Spark ETL jobs AND train ML models AND serve SQL to BI dashboards — all in one platform. Which warehouse is best suited?
0
/ 4
Cloud warehouses decoded.

Next: The Lakehouse — Iceberg, Delta Lake, Hudi.

🎤 Interview Q&A

Easy Why do cloud warehouses use columnar storage instead of row storage?
+
Analytics queries have a characteristic shape: they read a few columns across many rows — 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.
Medium How would you reduce Snowflake credit consumption for a pipeline that runs 50 small queries per day?
+
The main levers are: (1) Warehouse sizing — a XS warehouse (1 credit/hour) is sufficient for small queries; an XL warehouse (16 credits/hour) running 50 small queries wastes credits. (2) Auto-suspend — set 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.
Hard How does Redshift Spectrum differ from a native Redshift table, and when would you use each?
+
A native Redshift table stores data on RA3 managed storage (backed by S3 but managed by Redshift with local caching on NVMe SSDs). Queries against native tables benefit from local disk cache, distribution keys, sort keys, and zone maps — they are fast for repeatedly-queried hot data. Redshift Spectrum queries data that lives in S3 directly, without loading it into Redshift. Spectrum is appropriate for: (1) Cold historical data — years of raw event logs that are queried once a month don't belong in managed storage; Spectrum queries them in-place and you pay only for S3 storage + the bytes scanned by Spectrum ($5/TB). (2) Landing zone queries — querying data that has just landed in S3 before it's fully transformed and loaded. (3) Multi-engine data sharing — the same Parquet files in S3 can be read by Spectrum, Athena, Spark, and Glue simultaneously; no data duplication. The trade-off: Spectrum queries are slower than native table queries for hot data because there is no local caching and no sort/distribution optimisations. Use native tables for your regularly-queried mart tables, and Spectrum for cold/archive data that doesn't justify the storage and ETL cost of loading into managed storage.

✅ 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_SUSPEND to 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.
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

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 →