DE Cloud Data Warehouses
Cloud data warehouses bring together the analytical power of traditional data warehouses and the scalability of cloud infrastructure. They eliminate the hardware procurement, capacity planning, and database administration that made on-premise warehouses expensive and slow to manage. Modern cloud warehouses serve petabytes of data to thousands of concurrent analysts using only a web browser and SQL.
What Makes Cloud Warehouses Different
Traditional on-premise warehouses required purchasing dedicated servers, provisioning exact storage capacity, and managing database software upgrades. Cloud warehouses replace all of this with managed services: the provider handles the underlying infrastructure, and engineers pay only for what they use. More importantly, cloud warehouses separate storage from compute — a design that fundamentally changes how analytical systems scale.
Separation of Storage and Compute
In a traditional warehouse, storage and compute live on the same servers. Scaling up requires buying more machines even if only more storage — not more compute — is needed. Cloud warehouses separate these layers. Data sits in cheap cloud object storage (S3, GCS, ADLS). Compute clusters connect to that storage on demand. Engineers scale storage and compute independently.
Traditional Warehouse:
[Server 1: CPU + RAM + Disk] \
[Server 2: CPU + RAM + Disk] > Storage and compute tightly coupled
[Server 3: CPU + RAM + Disk] /
Cloud Warehouse:
[Object Storage: S3/GCS/ADLS] <-- Store once; unlimited scale; cheap
|
|
[Compute Cluster A] -- runs marketing queries
[Compute Cluster B] -- runs finance queries
[Compute Cluster C] -- runs data pipeline loads
Multiple compute clusters read the same data storage simultaneously without interfering with each other. A heavy reporting job no longer slows down data loading jobs.
The Three Leading Cloud Data Warehouses
Snowflake
Snowflake runs on AWS, GCP, and Azure simultaneously, making it cloud-agnostic. It pioneered the separation of storage and compute model and remains the most popular independent cloud warehouse. Snowflake charges separately for storage (per TB per month) and compute (per credit, proportional to warehouse size and runtime). Engineers spin up virtual warehouses (compute clusters) of different sizes for different workloads and shut them down when idle.
Snowflake Pricing Model: Storage: ~$23 per TB per month (compressed) Compute: Credits per hour, varies by warehouse size X-Small (1 credit/hr): development and small queries Medium (4 credits/hr): standard analytics workloads X-Large (16 credits/hr): heavy transformations, large joins
Google BigQuery
BigQuery is Google's fully serverless data warehouse. Engineers write SQL queries and submit them — BigQuery dynamically allocates the compute needed, runs the query, and the compute disappears afterward. There are no clusters to configure, no virtual warehouses to size. Pricing is per query based on data scanned (approximately $5 per TB scanned) or a flat-rate monthly commitment. BigQuery's serverless model makes it ideal for variable, unpredictable workloads.
Amazon Redshift
Redshift was the first major cloud data warehouse, launched by AWS in 2013. It uses a traditional cluster-based model where engineers provision a fixed cluster size. Redshift Serverless (launched 2022) offers on-demand compute similar to BigQuery. Redshift integrates tightly with the broader AWS ecosystem — S3, Glue, Lambda, and SageMaker — making it the natural choice for AWS-centric organizations.
Feature Comparison
Feature | Snowflake | BigQuery | Redshift -------------------|--------------------|--------------------|------------------ Pricing model | Storage + compute | Per query scanned | Cluster or serverless Multi-cloud | AWS, GCP, Azure | GCP only | AWS only Scaling | Resize warehouse | Fully automatic | Resize cluster Concurrency | Multiple warehouses| Automatic | Concurrency scaling Best for | Mixed workloads | Unpredictable qrys | AWS-native stacks Native format | Iceberg support | BigLake Iceberg | Redshift Spectrum
Loading Data into a Cloud Warehouse
Data engineers load data into cloud warehouses using bulk copy commands that read directly from cloud object storage — far faster than row-by-row inserts.
Snowflake COPY command: COPY INTO orders FROM @my_s3_stage/orders/2024/05/ FILE_FORMAT = (TYPE = PARQUET); BigQuery load from GCS: bq load \ --source_format=PARQUET \ my_dataset.orders \ gs://my-bucket/orders/2024/05/*.parquet Redshift COPY command: COPY orders FROM 's3://my-bucket/orders/2024/05/' IAM_ROLE 'arn:aws:iam::123:role/RedshiftRole' FORMAT AS PARQUET;
Warehouse-Native Transformations
Cloud warehouses run ELT transformations using their own compute engines. After raw data loads, dbt models run SQL transformations inside the warehouse — joining, cleaning, aggregating — and write results to clean tables. The warehouse handles all parallelism automatically. This pattern eliminates the need for external transformation servers like Spark for SQL-based workloads.
Performance Best Practices
Cluster Keys and Partition Pruning
In Snowflake, clustering keys on frequently filtered columns (like date) improve query performance by letting the engine skip micro-partitions that do not contain matching data. In BigQuery, partitioning the table by date column and clustering on frequently filtered columns achieves similar results.
Query Result Caching
Cloud warehouses cache query results. If the same query runs again within a cache window and the underlying data has not changed, the warehouse returns the cached result instantly at no compute cost. Data engineers take advantage of this by designing dashboards to reuse cached query results wherever possible.
Summary
Cloud data warehouses — Snowflake, BigQuery, and Redshift — deliver the power of traditional data warehousing without the burden of hardware management. Separation of storage and compute enables independent scaling of each layer. Each warehouse offers distinct pricing models, cloud affiliations, and feature sets suited to different organizational needs. Data engineers use bulk loading from object storage, SQL-based ELT transformations, and warehouse-native performance features to build fast, cost-efficient analytical systems.