Modern Data Warehousing β Snowflake, BigQuery & Redshift Architecture
In this tutorial, you'll learn about Modern Data Warehousing. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.
Modern Data Warehousing moves beyond traditional on-premise systems to cloud-native platforms like Snowflake, BigQuery, and Redshift β offeringεΌΉζ§ scalability, separation of compute and storage, and pay-per-query pricing for analytics at petabyte scale.
What You'll Learn
By the end of this tutorial, you'll understand the architectural differences between Snowflake, BigQuery, and Redshift, their pricing models, performance optimization techniques, data loading strategies, and how to choose the right warehouse for your use case.
Why It Matters
Warehouse costs can spiral out of control. A query that scans 1TB costs 10x more in one platform than another. Choosing the wrong warehouse architecture leads to performance bottlenecks, unexpected bills, and engineering time wasted on workarounds. DodaTech migrated its analytics stack from a legacy Redshift cluster to Snowflake, cutting query times by 60% and storage costs by 40%.
Real-World Use
Snowflake serves 7,000+ customers including Instacart and Adobe. BigQuery powers Google's internal analytics and serves the Wikimedia Foundation for Wikipedia data analysis. Redshift runs Amazon's massive internal analytics and powers thousands of AWS customers.
Cloud Data Warehouse Architecture
flowchart TB
subgraph "Ingestion Layer"
A[Kafka] --> B[Streaming]
C[Fivetran] --> D[Batch]
end
subgraph "Storage Layer"
E[(Cloud Storage)]
F[(Columnar Format)]
end
subgraph "Compute Layer"
G[Virtual Warehouse]
H[Query Engine]
end
subgraph "Consumption"
I[BI Tools]
J[ML Pipelines]
K[Data Science]
end
B --> E
D --> E
E --> F
F --> G
G --> H
H --> I
H --> J
H --> K
style E fill:#f90,color:#fff
style G fill:#f90,color:#fff
Prerequisites: Understanding of SQL and basic Data Warehousing concepts. Familiarity with Cloud Computing fundamentals. Experience with ETL pipelines helps.
Architecture Comparison
Snowflake
Snowflake separates storage and compute completely. Data is stored in compressed, columnar format in cloud blob storage. Compute is handled by virtual warehouses β independent clusters that can be scaled up, down, or suspended independently.
-- Snowflake: Create a virtual warehouse and table
CREATE WAREHOUSE analytics_wh
WITH WAREHOUSE_SIZE = 'MEDIUM'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;
CREATE TABLE orders (
order_id INTEGER,
customer_id VARCHAR(50),
order_date DATE,
amount DECIMAL(12,2),
status VARCHAR(20)
) CLUSTER BY (order_date);
-- Load data from stage
COPY INTO orders
FROM @my_stage/orders/
FILE_FORMAT = (TYPE = PARQUET)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
-- Query with automatic clustering
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY 1
ORDER BY 1;
Expected output:
MONTH | ORDER_COUNT | TOTAL_REVENUE
-----------+-------------+--------------
2026-01-01 | 45200 | 12500000.00
2026-02-01 | 38900 | 11200000.00
2026-03-01 | 51000 | 14200000.00
2026-04-01 | 47300 | 13100000.00
BigQuery
BigQuery is Serverless β no clusters to manage. It automatically allocates compute resources and scales to petabytes. Storage and compute are billed separately, with flat-rate and on-demand pricing options.
-- BigQuery: Partitioned and clustered table
CREATE TABLE `dodatech.analytics.orders`
PARTITION BY DATE(order_date)
CLUSTER BY customer_id
AS
SELECT * FROM `dodatech.raw.orders`;
-- Query with partition pruning
SELECT
customer_id,
COUNT(*) AS orders,
SUM(amount) AS total_spent
FROM `dodatech.analytics.orders`
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;
-- Use WITH clause for multi-step analysis
WITH monthly_revenue AS (
SELECT
DATE_TRUNC(order_date, MONTH) AS month,
SUM(amount) AS revenue
FROM `dodatech.analytics.orders`
GROUP BY 1
)
SELECT
month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change
FROM monthly_revenue
ORDER BY month;
Expected output:
Row | customer_id | orders | total_spent
----+-------------+--------+------------
1 | C001234 | 42 | 125000.00
2 | C005678 | 38 | 112000.00
3 | C009012 | 35 | 98000.00
Redshift
Redshift uses a cluster-based architecture with leader and compute nodes. Columnar storage, sort keys, and distribution keys optimize query performance. RA3 nodes support managed storage with local caching.
-- Redshift: Create table with sort and dist keys
CREATE TABLE orders (
order_id INTEGER ENCODE AZ64,
customer_id VARCHAR(50) ENCODE LZO,
order_date DATE ENCODE DELTA,
amount DECIMAL(12,2) ENCODE AZ64,
status VARCHAR(20) ENCODE LZO
)
SORTKEY (order_date)
DISTKEY (customer_id);
-- Load from S3
COPY orders
FROM 's3://dodatech-data/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopy'
FORMAT AS PARQUET;
-- Analyze vacuum and performance
SELECT
TRUNC(order_date, 'MM') AS month,
COUNT(*) AS orders,
SUM(amount) AS revenue,
AVG(amount) AS avg_order_value
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY 1
ORDER BY 1;
-- Check table health
SELECT
"table",
size AS mb,
tbl_rows AS rows,
skew_rows AS skew
FROM SVV_TABLE_INFO
WHERE "table" = 'orders';
Expected output:
MONTH | ORDERS | REVENUE | AVG_ORDER_VALUE
-----------+--------+-------------+----------------
2026-01-01 | 45200 | 12500000.00 | 276.55
2026-02-01 | 38900 | 11200000.00 | 287.92
2026-03-01 | 51000 | 14200000.00 | 278.43
Performance Optimization
Key techniques across all platforms
# warehouse_benchmark.py
# Compare query performance across warehouse configurations
import time
import random
class WarehouseBenchmark:
def __init__(self, name):
self.name = name
self.results = []
def run_query(self, query_name, data_size_gb, scan_efficiency):
"""Simulate query execution with data size and efficiency."""
# scan_efficiency: 0.0 (full scan) to 1.0 (perfect pruning)
scanned_gb = data_size_gb * (1 - scan_efficiency)
compute_time = scanned_gb * random.uniform(0.5, 2.0)
self.results.append({
"query": query_name,
"data_size_gb": data_size_gb,
"efficiency": scan_efficiency,
"scanned_gb": round(scanned_gb, 2),
"time_seconds": round(compute_time, 2),
})
return self.results[-1]
def summary(self):
print(f"\n=== Warehouse: {self.name} ===")
print(f"{'Query':<25} {'Data (GB)':<12} {'Efficiency':<12} {'Scanned (GB)':<15} {'Time (s)'}")
print("-" * 75)
for r in self.results:
print(f"{r['query']:<25} {r['data_size_gb']:<12} {r['efficiency']:<12} {r['scanned_gb']:<15} {r['time_seconds']}")
total_time = sum(r["time_seconds"] for r in self.results)
print(f"\nTotal query time: {total_time:.2f}s")
# Without partitioning β full scan
no_partition = WarehouseBenchmark("No Partitioning")
no_partition.run_query("monthly_revenue", 500, 0.0)
no_partition.run_query("customer_ltv", 500, 0.0)
no_partition.summary()
# With partition pruning β 80% efficiency
partitioned = WarehouseBenchmark("With Partitioning")
partitioned.run_query("monthly_revenue", 500, 0.90)
partitioned.run_query("customer_ltv", 500, 0.75)
partitioned.summary()
Expected output:
=== Warehouse: No Partitioning ===
Query Data (GB) Efficiency Scanned (GB) Time (s)
---------------------------------------------------------------------------
monthly_revenue 500 0.0 500.0 712.34
customer_ltv 500 0.0 500.0 845.21
Total query time: 1557.55s
=== Warehouse: With Partitioning ===
Query Data (GB) Efficiency Scanned (GB) Time (s)
---------------------------------------------------------------------------
monthly_revenue 500 0.9 50.0 72.45
customer_ltv 500 0.75 125.0 198.67
Total query time: 271.12s
Data Loading Strategies
# data_loader.py
# Compare batch and incremental loading strategies
class WarehouseLoader:
def __init__(self, warehouse_type):
self.type = warehouse_type
self.loads = []
def full_refresh(self, table_name, rows):
"""Full table reload."""
elapsed = rows * 0.0001 # Simulated: 1s per 10K rows
self.loads.append({
"table": table_name,
"method": "full_refresh",
"rows": rows,
"elapsed_seconds": round(elapsed, 2),
"cost_credits": round(rows * 0.00005, 2),
})
return self.loads[-1]
def incremental_load(self, table_name, new_rows):
"""Incremental append only."""
elapsed = new_rows * 0.00001
self.loads.append({
"table": table_name,
"method": "incremental",
"rows": new_rows,
"elapsed_seconds": round(elapsed, 2),
"cost_credits": round(new_rows * 0.00001, 2),
})
return self.loads[-1]
def summary(self):
print(f"\n=== Data Load: {self.type} ===")
print(f"{'Table':<20} {'Method':<16} {'Rows':<12} {'Time (s)':<12} {'Cost ($)'}")
print("-" * 65)
for l in self.loads:
print(f"{l['table']:<20} {l['method']:<16} {l['rows']:<12} {l['elapsed_seconds']:<12} {l['cost_credits']}")
total_cost = sum(l["cost_credits"] for l in self.loads)
total_time = sum(l["elapsed_seconds"] for l in self.loads)
print(f"\nTotal: {total_time}s, ${total_cost:.2f}")
loader = WarehouseLoader("Snowflake")
loader.full_refresh("orders_hist", 50000000)
loader.incremental_load("orders_daily", 500000)
loader.incremental_load("orders_daily", 500000)
loader.incremental_load("orders_daily", 500000)
loader.summary()
Expected output:
=== Data Load: Snowflake ===
Table Method Rows Time (s) Cost ($)
-----------------------------------------------------------------
orders_hist full_refresh 50000000 5000.0 2500.00
orders_daily incremental 500000 5.0 5.00
orders_daily incremental 500000 5.0 5.00
orders_daily incremental 500000 5.0 5.00
Total: 5015.0s, $2515.00
Common Data Warehousing Mistakes
1. No Partitioning or Clustering
Without partition pruning, every query scans the entire table. A 1TB table costs $5/TB in BigQuery on-demand β one full scan is $5,000. Always partition by date and cluster by frequent filter columns.
2. Ignoring Concurrency Scaling
Snowflake and Redshift handle multiple queries with separate compute resources. Without concurrency scaling, a single heavy query blocks all other queries, causing dashboard timeouts.
3. Wrong Distribution Key in Redshift
Choosing the wrong DISTKEY causes data shuffling between nodes. Use DISTKEY on frequently joined columns. Avoid DISTKEY ALL for large tables β it replicates data to every node.
4. Over-Provisioning Compute
Snowflake virtual warehouses and Redshift clusters billed by the hour. A MEDIUM warehouse costs 2x a SMALL. Start small and use AUTO_SUSPEND to stop billing when idle.
5. Not Compressing Before Loading
Columnar stores compress well. Loading uncompressed CSV wastes storage and slows COPY operations. Use Parquet or ORC with snappy compression for 4-10x compression ratios.
Practice Questions
1. What is the key architectural difference between Snowflake and BigQuery? Snowflake uses virtual warehouses (separate compute clusters that can be started/stopped independently). BigQuery is Serverless β compute is automatically allocated per query with no cluster management. Both separate storage and compute costs.
2. How does Redshift's distribution style affect query performance? Redshift distributes data across compute nodes. AUTO distribution lets Redshift choose. KEY distribution co-locates rows with same key on the same node for faster joins. EVEN distributes rows evenly but requires data shuffling for joins. ALL replicates small tables to all nodes.
3. What is partition pruning and why is it important? Partition pruning eliminates irrelevant data files before query execution. When a WHERE clause filters on the partition column, the engine skips entire partitions. A daily-partitioned table with 365 partitions only reads 1/365th of data for a single-day query.
Frequently Asked Questions
{{< faq question="Which data warehouse is cheapest for small to medium workloads?">}} For workloads under 1TB, BigQuery's on-demand pricing ($5/TB scanned) is often cheapest because there's no fixed compute cost. Snowflake's AUTO_SUSPEND feature can also minimize costs for intermittent usage. Redshift has a minimum cluster cost (~$0.25/hr for dc2.large) that makes it more expensive for light usage. Always benchmark with your actual query patterns. {{< /faq >}}
{{< faq question="Can I use multiple warehouses for different workloads?">}} Yes, many organizations use BigQuery for ad-hoc analytics (Serverless, fast) and Snowflake for production reporting (consistent performance, concurrency). Some use Redshift for very large datasets where its sort key architecture optimizes known query patterns. A multi-warehouse strategy requires consistent data Replication and governance across platforms. {{< /faq >}}
Mini Project: Warehouse Cost Calculator
# cost_calculator.py
# Estimate monthly warehouse costs based on usage
class WarehouseCostEstimator:
def __init__(self, platform):
self.platform = platform
self.components = []
def add_compute(self, name, hours_per_month, unit_cost):
monthly = hours_per_month * unit_cost
self.components.append({"type": "compute", "name": name, "monthly": monthly})
return monthly
def add_storage(self, name, tb, cost_per_tb):
monthly = tb * cost_per_tb
self.components.append({"type": "storage", "name": name, "monthly": monthly})
return monthly
def add_queries(self, name, tb_scanned_per_month, cost_per_tb):
monthly = tb_scanned_per_month * cost_per_tb
self.components.append({"type": "query", "name": name, "monthly": monthly})
return monthly
def estimate(self):
total = sum(c["monthly"] for c in self.components)
print(f"\n=== Cost Estimate: {self.platform} ===")
print(f"{'Type':<12} {'Component':<25} {'Monthly Cost ($)'}")
print("-" * 50)
for c in self.components:
print(f"{c['type']:<12} {c['name']:<25} {c['monthly']:<,.2f}")
print(f"\nTotal estimated monthly cost: ${total:,.2f}")
return total
snowflake = WarehouseCostEstimator("Snowflake")
snowflake.add_compute("Medium WH (8hrs/day)", 240, 2.00)
snowflake.add_compute("Large WH (batch, 4hrs/day)", 120, 4.00)
snowflake.add_storage("Compressed data", 50, 40.00)
snowflake.add_storage("Fail-safe (7 days)", 50, 0.00)
snowflake.estimate()
bigquery = WarehouseCostEstimator("BigQuery")
bigquery.add_queries("On-demand analytics", 200, 5.00)
bigquery.add_storage("Active data", 50, 20.00)
bigquery.add_storage("Long-term (90+ days)", 50, 10.00)
bigquery.estimate()
Expected output:
=== Cost Estimate: Snowflake ===
Type Component Monthly Cost ($)
--------------------------------------------------
compute Medium WH (8hrs/day) 480.00
compute Large WH (batch, 4hrs/day) 480.00
storage Compressed data 2,000.00
storage Fail-safe (7 days) 0.00
Total estimated monthly cost: $2,960.00
=== Cost Estimate: BigQuery ===
Type Component Monthly Cost ($)
--------------------------------------------------
query On-demand analytics 1,000.00
storage Active data 1,000.00
storage Long-term (90+ days) 500.00
Total estimated monthly cost: $2,500.00
Related Concepts
What's Next
You now understand how Snowflake, BigQuery, and Redshift compare across architecture, pricing, and performance. Next, explore ETL vs ELT patterns to choose the right transformation strategy, and learn how Apache Spark complements modern warehouses for large-scale processing.
- Practice daily β Sign up for a free Snowflake or BigQuery trial and load 100K rows
- Build a project β Create partitioned tables, run benchmarks, and estimate the monthly cost
- Explore related topics β Check out zero-copy cloning, data sharing, and warehouse monitoring tools
Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro.
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro