🧠 Fundamentals

🎯 What is a Cloud Data Warehouse?

A Cloud Data Warehouse is a fully managed, cloud-based analytics database. It separates storage from compute, scales elastically, and charges on-demand. Leading platforms include Snowflake, Google BigQuery, Amazon Redshift, and Azure Synapse.

Why Cloud Warehouses Matter

Serverless: No infrastructure. Elastic: Scale up/down. Separated: Storage & compute. Pay-per-use: Cost-effective. Managed: No ops. Fast: MPP engines.

📌 Serverless📌 Elastic📌 Separated📌 Managed

# Cloud Data Warehouse — the big picture
# Fully managed analytics database in the cloud.

# CONCEPTUAL OVERVIEW
print("""
=== Cloud Data Warehouse ===

What it is:
  - Managed analytics database
  - Cloud-based
  - Separates storage and compute
  - Elastic scaling
  - Pay-per-use

Key features:
  - No infrastructure
  - Auto-scaling
  - MPP architecture
  - Columnar storage
  - SQL support
  - Concurrency

Leading platforms:
  - Snowflake
  - Google BigQuery
  - Amazon Redshift
  - Azure Synapse
  - Databricks SQL

What it's NOT:
  - Not OLTP
  - Not a data lake
  - Not for unstructured data
  - Not for streaming
""")

# SIMPLE QUERY EXAMPLE
print("=== Simple Query Example ===")
print("""
-- Snowflake
SELECT 
    date,
    COUNT(*) as events,
    COUNT(DISTINCT user_id) as users
FROM analytics.public.events
WHERE date >= '2024-01-01'
GROUP BY date
ORDER BY date;

-- BigQuery
SELECT 
    date,
    COUNT(*) as events
FROM `project.dataset.events`
WHERE date >= '2024-01-01'
GROUP BY date;

-- Redshift
SELECT 
    date,
    COUNT(*) as events
FROM analytics.events
WHERE date >= '2024-01-01'
GROUP BY date;
""")

# KEY PROPERTIES
print("""
=== Key Properties ===

1. MANAGED
   - No infrastructure
   - No patching
   - No tuning
   - Focus on data

2. ELASTIC
   - Scale up/down
   - On-demand
   - Pay per use

3. SEPARATED
   - Storage and compute
   - Independent scaling
   - Cost optimization

4. COLUMNAR
   - Columnar storage
   - Compression
   - Fast analytics

5. MPP
   - Massively parallel
   - Distributed
   - Fast queries

6. CONCURRENCY
   - Many users
   - Isolated workloads
   - No contention
""")

# REAL-WORLD SCALE
print("=== Real-World Deployments ===")
deployments = [
    ('Snowflake', 'Petabytes', 'Many enterprises'),
    ('BigQuery', 'Exabytes', 'Google Cloud'),
    ('Redshift', 'Petabytes', 'AWS'),
    ('Synapse', 'Petabytes', 'Azure'),
    ('Databricks', 'Petabytes', 'Lakehouse'),
]

print(f"{'Platform':<15} {'Scale':<20} {'Cloud':<25}")
print("-" * 60)
for platform, scale, cloud in deployments:
    print(f"{platform:<15} {scale:<20} {cloud:<25}")

❄️ Snowflake

🎯 The Cloud-Native Warehouse

Snowflake is a cloud-native data warehouse built from scratch for the cloud. It separates storage and compute, supports multi-cloud, and is known for its ease of use and performance.

Snowflake Characteristics

Cloud-native: Built for cloud. Separated: Storage & compute. Multi-cloud: AWS, Azure, GCP. Virtual warehouses: Compute clusters. Time travel: Versioned data. Data sharing: Secure.

❄️ Cloud-Native❄️ Separated❄️ Multi-Cloud❄️ Virtual Warehouses

# Snowflake — deep dive
print("=== What is Snowflake? ===")
print("""
Snowflake:
  - Cloud-native warehouse
  - Separates storage and compute
  - Multi-cloud (AWS, Azure, GCP)
  - Virtual warehouses (compute)
  - Time travel
  - Data sharing
  - Zero-copy cloning

Created by:
  - Benoit Dageville, Thierry Cruanes
  - 2012

Architecture:
  - Storage layer (S3, Blob, GCS)
  - Compute layer (virtual warehouses)
  - Cloud services (metadata, optimizer)
""")

print("\n=== Key Features ===")
print("""
1. VIRTUAL WAREHOUSES
   - Compute clusters
   - Independent scaling
   - Per-workload
   - Auto-suspend/resume

2. TIME TRAVEL
   - Query previous versions
   - Up to 90 days
   - Undrop tables

3. ZERO-COPY CLONE
   - Clone tables instantly
   - No data copy
   - Metadata only

4. DATA SHARING
   - Share data securely
   - No data copy
   - Cross-account

5. MULTI-CLOUD
   - AWS
   - Azure
   - GCP
   - Same experience
""")

print("\n=== Example ===")
print("""
-- Create virtual warehouse
CREATE WAREHOUSE analytics_wh
    WITH WAREHOUSE_SIZE = 'MEDIUM'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE;

-- Create database
CREATE DATABASE analytics;

-- Create table
CREATE TABLE analytics.public.events (
    id INT,
    user_id INT,
    event_type STRING,
    timestamp TIMESTAMP
);

-- Query
SELECT * FROM analytics.public.events
WHERE timestamp > '2024-01-01';

-- Time travel
SELECT * FROM analytics.public.events
AT (TIMESTAMP => '2024-01-01 00:00:00'::timestamp);

-- Clone
CREATE TABLE events_clone CLONE analytics.public.events;
""")

print("\n=== Snowflake Pros & Cons ===")
print("""
PROS:
  ✓ Cloud-native
  ✓ Separated storage/compute
  ✓ Multi-cloud
  ✓ Easy to use
  ✓ Time travel
  ✓ Data sharing
  ✓ Zero-copy clone

CONS:
  ✗ Cost (can be high)
  ✗ Vendor lock-in
  ✗ Not open source
  ✗ Limited ML
  ✗ Proprietary
""")

🔵 BigQuery

🎯 The Serverless Warehouse

Google BigQuery is a serverless, highly scalable cloud data warehouse. It's fully managed, requires no infrastructure, and charges per query or per slot.

BigQuery Characteristics

Serverless: No infrastructure. Columnar: Capacitor format. SQL: Standard SQL. ML: BigQuery ML. Streaming: Real-time. Pay-per-query: Cost model.

🔵 Serverless🔵 Columnar🔵 BigQuery ML🔵 Streaming

# BigQuery — deep dive
print("=== What is BigQuery? ===")
print("""
BigQuery:
  - Serverless warehouse
  - Fully managed
  - No infrastructure
  - Columnar storage
  - Standard SQL
  - BigQuery ML
  - Streaming

Created by:
  - Google
  - 2010

Architecture:
  - Dremel (query engine)
  - Colossus (storage)
  - Jupiter (network)
  - Borg (compute)
""")

print("\n=== Key Features ===")
print("""
1. SERVERLESS
   - No infrastructure
   - Auto-scaling
   - Pay per query or slot

2. COLUMNAR STORAGE
   - Capacitor format
   - Compression
   - Fast scans

3. BIGQUERY ML
   - SQL-based ML
   - Train models
   - Predictions

4. STREAMING
   - Real-time inserts
   - Streaming API
   - Dataflow integration

5. FEDERATED QUERIES
   - External tables
   - Cloud Storage
   - Bigtable
   - Spanner
""")

print("\n=== Example ===")
print("""
-- Create dataset
CREATE SCHEMA analytics;

-- Create table
CREATE TABLE analytics.events (
    id INT64,
    user_id INT64,
    event_type STRING,
    timestamp TIMESTAMP
);

-- Query
SELECT 
    DATE(timestamp) as date,
    COUNT(*) as events
FROM analytics.events
WHERE timestamp >= '2024-01-01'
GROUP BY date
ORDER BY date;

-- BigQuery ML
CREATE MODEL analytics.predict_churn
OPTIONS(model_type='logistic_reg') AS
SELECT
    churned AS label,
    age,
    tenure,
    monthly_charges
FROM analytics.customers;
""")

print("\n=== BigQuery Pros & Cons ===")
print("""
PROS:
  ✓ Serverless
  ✓ No infrastructure
  ✓ Auto-scaling
  ✓ BigQuery ML
  ✓ Streaming
  ✓ Federated queries
  ✓ Cost-effective for sporadic

CONS:
  ✗ Cost can spike
  ✗ Vendor lock-in
  ✗ GCP-only
  ✗ Complex pricing
  ✗ Limited control
""")

🔴 Redshift

🎯 The AWS Warehouse

Amazon Redshift is AWS's cloud data warehouse. It's based on PostgreSQL, uses columnar storage and MPP, and offers both provisioned and serverless options.

Redshift Characteristics

PostgreSQL: Based on. Columnar: Storage. MPP: Parallel. Provisioned: Or serverless. Spectrum: Query S3. AWS: Integrated.

🔴 PostgreSQL🔴 Columnar🔴 MPP🔴 AWS

# Redshift — deep dive
print("=== What is Redshift? ===")
print("""
Redshift:
  - AWS warehouse
  - Based on PostgreSQL
  - Columnar storage
  - MPP architecture
  - Provisioned or serverless
  - Redshift Spectrum
  - AWS integration

Created by:
  - Amazon
  - 2012

Architecture:
  - Leader node (query planning)
  - Compute nodes (execution)
  - Columnar storage
  - MPP
""")

print("\n=== Key Features ===")
print("""
1. COLUMNAR STORAGE
   - Compression
   - Zone maps
   - Fast scans

2. MPP
   - Parallel execution
   - Distributed
   - Scale out

3. REDSHIFT SPECTRUM
   - Query S3 directly
   - External tables
   - No loading

4. SERVERLESS
   - No infrastructure
   - Auto-scaling
   - Pay per use

5. AWS INTEGRATION
   - S3
   - Glue
   - Kinesis
   - IAM
""")

print("\n=== Example ===")
print("""
-- Create table
CREATE TABLE events (
    id INT,
    user_id INT,
    event_type VARCHAR(50),
    timestamp TIMESTAMP
)
DISTSTYLE KEY
DISTKEY (user_id)
SORTKEY (timestamp);

-- Query
SELECT 
    DATE(timestamp) as date,
    COUNT(*) as events
FROM events
WHERE timestamp >= '2024-01-01'
GROUP BY date
ORDER BY date;

-- Spectrum
CREATE EXTERNAL TABLE spectrum.events (
    id INT,
    user_id INT,
    event_type VARCHAR(50),
    timestamp TIMESTAMP
)
STORED AS PARQUET
LOCATION 's3://bucket/events/';
""")

print("\n=== Redshift Pros & Cons ===")
print("""
PROS:
  ✓ AWS integration
  ✓ PostgreSQL-compatible
  ✓ Columnar
  ✓ Spectrum (S3)
  ✓ Serverless option
  ✓ Mature

CONS:
  ✗ AWS-only
  ✗ Complex tuning
  ✗ Cost
  ✗ Vendor lock-in
  ✗ Limited ML
""")

🟣 Synapse

🎯 The Azure Warehouse

Azure Synapse Analytics is Microsoft's cloud data warehouse and analytics platform. It combines SQL pools, Spark pools, and data integration in one workspace.

Synapse Characteristics

Unified: SQL + Spark. Dedicated: SQL pools. Serverless: SQL. Spark: Pools. Integration: Pipelines. Azure: Integrated.

🟣 Unified🟣 SQL + Spark🟣 Serverless🟣 Azure

# Synapse — deep dive
print("=== What is Synapse? ===")
print("""
Azure Synapse:
  - Microsoft warehouse
  - Unified analytics
  - SQL + Spark
  - Data integration
  - Serverless SQL
  - Dedicated SQL pools
  - Azure integration

Created by:
  - Microsoft
  - 2019

Architecture:
  - SQL pools (dedicated)
  - Serverless SQL
  - Spark pools
  - Pipelines
  - Studio (UI)
""")

print("\n=== Key Features ===")
print("""
1. DEDICATED SQL POOLS
   - Provisioned
   - MPP
   - Columnar
   - Scale

2. SERVERLESS SQL
   - On-demand
   - Query data lake
   - No infrastructure

3. SPARK POOLS
   - Apache Spark
   - Big data
   - ML

4. PIPELINES
   - Data integration
   - ETL
   - Orchestration

5. AZURE INTEGRATION
   - Data Lake
   - Cosmos DB
   - Power BI
""")

print("\n=== Example ===")
print("""
-- Dedicated SQL pool
CREATE TABLE events (
    id INT,
    user_id INT,
    event_type VARCHAR(50),
    timestamp DATETIME2
)
WITH (
    DISTRIBUTION = HASH(user_id),
    CLUSTERED COLUMNSTORE INDEX
);

-- Query
SELECT 
    CAST(timestamp AS DATE) as date,
    COUNT(*) as events
FROM events
WHERE timestamp >= '2024-01-01'
GROUP BY CAST(timestamp AS DATE);

-- Serverless
SELECT *
FROM OPENROWSET(
    BULK 'https://storage.dfs.core.windows.net/data/events/*.parquet',
    FORMAT = 'PARQUET'
) AS events;
""")

print("\n=== Synapse Pros & Cons ===")
print("""
PROS:
  ✓ Unified (SQL + Spark)
  ✓ Serverless option
  ✓ Azure integration
  ✓ Power BI
  ✓ Data integration

CONS:
  ✗ Azure-only
  ✗ Complex
  ✗ Cost
  ✗ Vendor lock-in
  ✗ Steep learning curve
""")

🏗️ Architecture

🎯 Separation of Storage and Compute

Cloud warehouses separate storage (cheap object storage) from compute (elastic clusters). This enables independent scaling and cost optimization.

Architecture

Storage: S3, Blob, GCS. Compute: Clusters. Metadata: Catalog. Optimizer: Query planning. Separated: Scale independently. Elastic: On-demand.

🏗️ Storage🏗️ Compute🏗️ Metadata🏗️ Separated

# Cloud Warehouse Architecture
print("=== Architecture Diagram ===")
print("""
  ┌─────────────────────────────────────────┐
  │              Client                     │
  │      (BI, SQL, JDBC, ODBC)              │
  └─────────────────┬───────────────────────┘
                    │
                    ▼
  ┌─────────────────────────────────────────┐
  │           Cloud Services                │
  │                                         │
  │  - Metadata                             │
  │  - Optimizer                            │
  │  - Security                             │
  │  - Transactions                         │
  └─────────────────┬───────────────────────┘
                    │
        ┌───────────┼───────────┐
        │           │           │
        ▼           ▼           ▼
  ┌─────────┐ ┌─────────┐ ┌─────────┐
  │Compute 1│ │Compute 2│ │Compute 3│
  │(Cluster)│ │(Cluster)│ │(Cluster)│
  │         │ │         │ │         │
  │- Query  │ │- Query  │ │- Query  │
  │- Cache  │ │- Cache  │ │- Cache  │
  └────┬────┘ └────┬────┘ └────┬────┘
       │           │           │
       └───────────┼───────────┘
                   │
                   ▼
  ┌─────────────────────────────────────────┐
  │              Storage                    │
  │                                         │
  │  S3 │ Blob │ GCS │ ...                  │
  │                                         │
  │  - Columnar                             │
  │  - Compressed                           │
  │  - Cheap                                │
  └─────────────────────────────────────────┘
""")

print("\n=== Components ===")
print("""
1. STORAGE
   - Object storage
   - S3, Azure Blob, GCS
   - Columnar format
   - Compressed
   - Cheap

2. COMPUTE
   - Virtual warehouses
   - Clusters
   - Elastic
   - Per-workload
   - Auto-scaling

3. METADATA
   - Catalog
   - Statistics
   - Schema
   - Transactions

4. OPTIMIZER
   - Query planning
   - Cost-based
   - Rule-based

5. CACHE
   - Result cache
   - Metadata cache
   - Local SSD
""")

print("\n=== Separation Benefits ===")
print("""
1. INDEPENDENT SCALING
   - Storage grows forever
   - Compute scales per workload
   - No coupling

2. COST OPTIMIZATION
   - Pay for what you use
   - Auto-suspend
   - Right-size compute

3. WORKLOAD ISOLATION
   - Separate warehouses
   - No contention
   - Predictable performance

4. MULTI-TENANCY
   - Multiple teams
   - Isolated
   - Shared storage

5. ELASTICITY
   - Scale up for peaks
   - Scale down for valleys
   - On-demand
""")

# IMPLEMENTATION
print("\n=== Simulating Cloud Warehouse ===")

class CloudWarehouse:
    """Simulate cloud warehouse."""
    
    def __init__(self, name):
        self.name = name
        self.storage = {}  # table -> data
        self.warehouses = {}  # warehouse -> {size, state}
        self.query_history = []
    
    def create_warehouse(self, name, size='MEDIUM'):
        """Create virtual warehouse."""
        sizes = {'SMALL': 1, 'MEDIUM': 2, 'LARGE': 4, 'XLARGE': 8}
        self.warehouses[name] = {
            'name': name,
            'size': size,
            'credits': sizes.get(size, 2),
            'state': 'suspended',
        }
        print(f"Warehouse: Created '{name}' ({size})")
    
    def resume_warehouse(self, name):
        """Resume warehouse."""
        if name in self.warehouses:
            self.warehouses[name]['state'] = 'running'
            print(f"Warehouse: Resumed '{name}'")
    
    def suspend_warehouse(self, name):
        """Suspend warehouse."""
        if name in self.warehouses:
            self.warehouses[name]['state'] = 'suspended'
            print(f"Warehouse: Suspended '{name}'")
    
    def load_table(self, table, data):
        """Load data."""
        self.storage[table] = data
        print(f"Storage: Loaded '{table}' ({len(data)} rows)")
    
    def query(self, sql, warehouse='default'):
        """Execute query."""
        print(f"\nQuery: {sql[:60]}...")
        print(f"  Warehouse: {warehouse}")
        
        # Check warehouse
        if warehouse in self.warehouses:
            if self.warehouses[warehouse]['state'] != 'running':
                self.resume_warehouse(warehouse)
        
        # Execute
        result = {'rows': 100, 'time': 1.5}
        self.query_history.append({
            'sql': sql,
            'warehouse': warehouse,
            'result': result,
        })
        
        return result


# Demo
print("=== Cloud Warehouse Demo ===")

warehouse = CloudWarehouse('snowflake-demo')

# Create warehouses
warehouse.create_warehouse('etl_wh', 'LARGE')
warehouse.create_warehouse('bi_wh', 'MEDIUM')

# Load data
warehouse.load_table('events', [{'id': i} for i in range(1000)])

# Query
warehouse.resume_warehouse('bi_wh')
result = warehouse.query("SELECT COUNT(*) FROM events", 'bi_wh')
print(f"  Result: {result}")

# Suspend
warehouse.suspend_warehouse('bi_wh')

# ARCHITECTURE SUMMARY
print("\n=== Architecture Summary ===")
print(f"{'Component':<25} {'Role':<40}")
print("-" * 65)
print(f"{'Storage':<25} {'Object storage':<40}")
print(f"{'Compute':<25} {'Virtual warehouses':<40}")
print(f"{'Metadata':<25} {'Catalog, statistics':<40}")
print(f"{'Optimizer':<25} {'Query planning':<40}")
print(f"{'Cache':<25} {'Result, metadata':<40}")

⚙️ Storage & Compute

🎯 The Key Innovation

The separation of storage and compute is the defining feature of cloud warehouses. Storage is cheap and infinite; compute is elastic and pay-per-use.

Separation

Storage: S3, Blob, GCS. Compute: Clusters. Independent: Scale separately. Cost: Optimize. Isolation: Workloads. Elastic: On-demand.

⚙️ Storage⚙️ Compute⚙️ Independent⚙️ Elastic

# Storage & Compute Separation
print("=== Storage Layer ===")
print("""
Storage:
  - Object storage (S3, Blob, GCS)
  - Columnar format
  - Compressed
  - Cheap
  - Infinite
  - Durable

Cost:
  - ~$0.02/GB/month
  - No compute cost
  - Pay for what you store

Benefits:
  - Infinite scale
  - Cheap
  - Durable
  - Separated
""")

print("\n=== Compute Layer ===")
print("""
Compute:
  - Virtual warehouses
  - Clusters
  - Elastic
  - Per-workload
  - Auto-suspend/resume

Cost:
  - Per second/minute
  - Based on size
  - Pay for what you use

Benefits:
  - Elastic
  - Isolated
  - Right-sized
  - Cost-effective
""")

print("\n=== Separation Benefits ===")
print("""
1. INDEPENDENT SCALING
   - Storage grows forever
   - Compute scales per workload

2. COST OPTIMIZATION
   - Auto-suspend
   - Right-size
   - Pay per use

3. WORKLOAD ISOLATION
   - Separate warehouses
   - No contention

4. ELASTICITY
   - Scale up for peaks
   - Scale down for valleys

5. MULTI-TENANCY
   - Multiple teams
   - Isolated
   - Shared storage
""")

print("\n=== Example ===")
print("""
-- Snowflake
-- Storage: S3
-- Compute: Virtual warehouses

-- Create warehouse
CREATE WAREHOUSE etl_wh
    WITH WAREHOUSE_SIZE = 'LARGE'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE;

-- Create warehouse
CREATE WAREHOUSE bi_wh
    WITH WAREHOUSE_SIZE = 'MEDIUM'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE;

-- Use different warehouses
USE WAREHOUSE etl_wh;
INSERT INTO analytics.events SELECT * FROM staging.events;

USE WAREHOUSE bi_wh;
SELECT * FROM analytics.events WHERE date = CURRENT_DATE;
""")

print("\n=== Cost Comparison ===")
print(f"{'Component':<25} {'Traditional':<25} {'Cloud':<25}")
print("-" * 75)
print(f"{'Storage':<25} {'Local disk':<25} {'Object storage':<25}")
print(f"{'Compute':<25} {'Fixed servers':<25} {'Elastic':<25}")
print(f"{'Scaling':<25} {'Vertical':<25} {'Horizontal':<25}")
print(f"{'Cost':<25} {'Fixed':<25} {'Pay-per-use':<25}")
print(f"{'Isolation':<25} {'Limited':<25} {'Excellent':<25}")

📈 Elasticity

🎯 Scale On Demand

Elasticity lets cloud warehouses scale compute up and down based on workload. You pay only for what you use.

Elasticity

Scale up: Bigger clusters. Scale out: More clusters. Auto-suspend: Save cost. Auto-resume: On demand. Per-second: Billing. Isolated: Workloads.

📈 Scale Up📈 Scale Out📈 Auto-Suspend📈 Per-Second

# Elasticity
print("=== What is Elasticity? ===")
print("""
Elasticity:
  - Scale compute on demand
  - Pay for what you use
  - Auto-suspend/resume
  - Per-second billing

Benefits:
  - Cost-effective
  - Responsive
  - No over-provisioning
  - No under-provisioning
""")

print("\n=== Scaling Options ===")
print("""
1. SCALE UP
   - Bigger warehouse
   - More CPU, memory
   - Faster queries

2. SCALE OUT
   - More warehouses
   - More concurrency
   - Isolated workloads

3. AUTO-SUSPEND
   - Suspend when idle
   - Save cost
   - Configurable

4. AUTO-RESUME
   - Resume on query
   - No manual action

5. MULTI-CLUSTER
   - Multiple clusters
   - Same warehouse
   - High concurrency
""")

print("\n=== Example ===")
print("""
-- Snowflake
-- Auto-suspend after 5 minutes
CREATE WAREHOUSE analytics_wh
    WITH WAREHOUSE_SIZE = 'MEDIUM'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE;

-- Scale up
ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'LARGE';

-- Scale out (multi-cluster)
ALTER WAREHOUSE analytics_wh 
    SET MIN_CLUSTER_COUNT = 2
    MAX_CLUSTER_COUNT = 10;
""")

print("\n=== Cost Implications ===")
print("""
Without elasticity:
  - Over-provision
  - Pay for idle
  - High cost

With elasticity:
  - Auto-suspend
  - Pay per use
  - Low cost

Example:
  - Warehouse: MEDIUM
  - Credits: 2/hour
  - Used: 100 hours/month
  - Cost: 200 credits

  With auto-suspend:
  - Used: 50 hours/month
  - Cost: 100 credits
  - Savings: 50%
""")

print("\n=== Elasticity Comparison ===")
print(f"{'Aspect':<25} {'Traditional':<25} {'Cloud':<25}")
print("-" * 75)
print(f"{'Scaling':<25} {'Manual':<25} {'Automatic':<25}")
print(f"{'Speed':<25} {'Weeks':<25} {'Seconds':<25}")
print(f"{'Cost':<25} {'Fixed':<25} {'Pay-per-use':<25}")
print(f"{'Idle':<25} {'Pay':<25} {'Suspend':<25}")
print(f"{'Peaks':<25} {'Over-provision':<25} {'Scale up':<25}")

🔐 Security

🎯 Protecting Data in the Cloud

Cloud warehouses offer robust security: encryption, access control, auditing, and compliance. Security is shared between the provider and the customer.

Security

Encryption: At rest, in transit. Access: RBAC, IAM. Auditing: Logs. Compliance: SOC2, HIPAA. Network: VPC, private. Masking: PII.

🔐 Encryption🔐 Access🔐 Auditing🔐 Compliance

# Security
print("=== Encryption ===")
print("""
At rest:
  - AES-256
  - Provider-managed
  - Customer-managed keys
  - Always on

In transit:
  - TLS 1.2+
  - HTTPS
  - Encrypted connections

Key management:
  - AWS KMS
  - Azure Key Vault
  - GCP KMS
""")

print("\n=== Access Control ===")
print("""
RBAC:
  - Roles
  - Users
  - Grants

Example:
  CREATE ROLE analyst;
  GRANT USAGE ON DATABASE analytics TO ROLE analyst;
  GRANT SELECT ON TABLE analytics.events TO ROLE analyst;
  GRANT ROLE analyst TO USER alice;

Row-level:
  CREATE ROW ACCESS POLICY ...
  
Column-level:
  CREATE MASKING POLICY ...
""")

print("\n=== Auditing ===")
print("""
What to log:
  - Who accessed
  - What was accessed
  - When
  - From where
  - Result

Tools:
  - Snowflake: ACCESS_HISTORY
  - BigQuery: Audit Logs
  - Redshift: STL_ACCESS_LOG
  - Synapse: Auditing
""")

print("\n=== Compliance ===")
print("""
Certifications:
  - SOC 2
  - ISO 27001
  - HIPAA
  - GDPR
  - PCI DSS
  - FedRAMP

Shared responsibility:
  - Provider: Infrastructure
  - Customer: Data, access
""")

print("\n=== Best Practices ===")
print("""
1. Enable encryption
2. Use RBAC
3. Least privilege
4. Audit access
5. Mask PII
6. Network isolation
7. Rotate keys
8. Monitor
""")

# SECURITY SUMMARY
print("\n=== Security Summary ===")
print(f"{'Area':<25} {'Description':<40}")
print("-" * 65)
print(f"{'Encryption':<25} {'At rest, in transit':<40}")
print(f"{'Access control':<25} {'RBAC, IAM':<40}")
print(f"{'Auditing':<25} {'Logs':<40}")
print(f"{'Compliance':<25} {'SOC2, HIPAA':<40}")
print(f"{'Network':<25} {'VPC, private':<40}")
print(f"{'Masking':<25} {'PII':<40}")

💰 Cost Model

🎯 Pay for What You Use

Cloud warehouses charge for storage and compute separately. Storage is cheap; compute is per-second/minute based on warehouse size.

Cost Model

Storage: Per GB/month. Compute: Per second. Credits: Snowflake. Slots: BigQuery. Nodes: Redshift. Optimize: Auto-suspend.

💰 Storage💰 Compute💰 Credits💰 Optimize

# Cost Model
print("=== Storage Cost ===")
print("""
Storage:
  - Per GB/month
  - Cheap
  - ~$20-25/TB/month
  - No compute cost

Example:
  1 TB data
  $23/month

  10 TB data
  $230/month
""")

print("\n=== Compute Cost ===")
print("""
Compute:
  - Per second/minute
  - Based on size
  - Credits or slots

Snowflake:
  - Credits
  - XS: 1 credit/hour
  - S: 2 credits/hour
  - M: 4 credits/hour
  - L: 8 credits/hour
  - XL: 16 credits/hour

BigQuery:
  - On-demand: $5/TB scanned
  - Flat-rate: slots

Redshift:
  - Per node/hour
  - dc2.large: $0.25/hour
  - ra3.xlplus: $1.086/hour
""")

print("\n=== Cost Example ===")
print("""
Snowflake:
  - Storage: 1 TB = $23/month
  - Compute: MEDIUM warehouse
  - Credits: 4/hour
  - Used: 100 hours/month
  - Cost: 400 credits × $3 = $1,200
  - Total: $1,223/month

With auto-suspend:
  - Used: 50 hours/month
  - Cost: 200 credits × $3 = $600
  - Total: $623/month
  - Savings: 49%
""")

print("\n=== Cost Optimization ===")
print("""
1. AUTO-SUSPEND
   - Suspend when idle
   - Configurable

2. RIGHT-SIZE
   - Use smallest warehouse
   - Scale up only when needed

3. RESULT CACHE
   - Cache query results
   - Free reuse

4. MATERIALIZED VIEWS
   - Pre-compute
   - Reduce compute

5. PARTITIONING
   - Prune data
   - Less scan

6. CLUSTERING
   - Sort data
   - Less scan

7. COMPRESSION
   - Columnar
   - Less storage

8. MONITOR
   - Track usage
   - Alert on spikes
""")

print("\n=== Cost Comparison ===")
print(f"{'Platform':<20} {'Storage':<20} {'Compute':<20}")
print("-" * 60)
print(f"{'Snowflake':<20} {'$23/TB/month':<20} {'Credits':<20}")
print(f"{'BigQuery':<20} {'$20/TB/month':<20} {'$5/TB scanned':<20}")
print(f"{'Redshift':<20} {'$24/TB/month':<20} {'Per node':<20}")
print(f"{'Synapse':<20} {'$23/TB/month':<20} {'DWU':<20}")

⚖️ Full Comparison

🎯 Snowflake vs BigQuery vs Redshift vs Synapse

Complete comparison of the four major cloud warehouses.

Comparison Table

Use this table as a quick reference for choosing the right platform.

⚖️ Reference⚖️ Decision⚖️ Complete

# Full Comparison
print("=== Snowflake vs BigQuery vs Redshift vs Synapse ===")
print()

# Print header
print(f"{'Aspect':<25} {'Snowflake':<18} {'BigQuery':<18} {'Redshift':<18} {'Synapse':<18}")
print("-" * 100)

# Data
rows = [
    ('Cloud', 'Multi', 'GCP', 'AWS', 'Azure'),
    ('Serverless', 'Partial', 'Yes', 'Partial', 'Partial'),
    ('Storage', 'S3/Blob/GCS', 'Colossus', 'S3', 'ADLS'),
    ('Compute', 'Virtual WH', 'Slots', 'Nodes', 'DWU'),
    ('Pricing', 'Credits', 'Per query/slot', 'Per node', 'DWU'),
    ('Time travel', 'Yes', 'Yes', 'No', 'No'),
    ('Data sharing', 'Yes', 'Yes', 'Yes', 'Yes'),
    ('ML', 'Limited', 'BigQuery ML', 'Limited', 'Spark ML'),
    ('Streaming', 'Yes', 'Yes', 'Yes', 'Yes'),
    ('Semi-structured', 'Yes', 'Yes', 'Yes', 'Yes'),
    ('Open source', 'No', 'No', 'No', 'No'),
    ('Lock-in', 'High', 'High', 'High', 'High'),
    ('Maturity', 'High', 'High', 'High', 'Medium'),
    ('Ease of use', 'Easy', 'Easy', 'Medium', 'Medium'),
]

for row in rows:
    print(f"{row[0]:<25} {row[1]:<18} {row[2]:<18} {row[3]:<18} {row[4]:<18}")

print()
print("=== Quick Guide ===")
print("""
Choose SNOWFLAKE when:
  - Multi-cloud
  - Ease of use
  - Data sharing
  - Time travel

Choose BIGQUERY when:
  - GCP
  - Serverless
  - BigQuery ML
  - Sporadic queries

Choose REDSHIFT when:
  - AWS
  - PostgreSQL
  - Spectrum
  - Existing AWS

Choose SYNAPSE when:
  - Azure
  - Unified (SQL + Spark)
  - Power BI
  - Microsoft stack
""")

print("=== Decision Flow ===")
print("""
Start
  │
  ├─ GCP? ──Yes──→ BigQuery
  │          │
  │          No
  │          ↓
  ├─ AWS? ──Yes──→ Redshift
  │          │
  │          No
  │          ↓
  ├─ Azure? ──Yes──→ Synapse
  │            │
  │            No
  │            ↓
  └─ Multi-cloud? ──Yes──→ Snowflake
""")

🆚 vs On-Prem

🎯 Cloud vs On-Premise

Cloud warehouses offer elasticity, managed services, and pay-per-use. On-premise warehouses offer control, predictability, and no vendor lock-in.

Comparison

Cloud: Elastic, managed. On-prem: Control, predictable. Cloud: Pay-per-use. On-prem: Capital expense. Cloud: Fast setup. On-prem: Slow setup.

🆚 Cloud🆚 On-Prem🆚 Cost🆚 Control

# Cloud vs On-Prem
print("=== Comparison ===")
print(f"{'Aspect':<25} {'Cloud':<25} {'On-Prem':<25}")
print("-" * 75)
print(f"{'Setup':<25} {'Minutes':<25} {'Weeks':<25}")
print(f"{'Scaling':<25} {'Elastic':<25} {'Manual':<25}")
print(f"{'Cost':<25} {'Pay-per-use':<25} {'Capital':<25}")
print(f"{'Maintenance':<25} {'Provider':<25} {'You':<25}")
print(f"{'Control':<25} {'Limited':<25} {'Full':<25}")
print(f"{'Security':<25} {'Shared':<25} {'You':<25}")
print(f"{'Lock-in':<25} {'High':<25} {'None':<25}")
print(f"{'Predictability':<25} {'Variable':<25} {'Fixed':<25}")

print("\n=== When to Use Cloud ===")
print("""
USE CLOUD:
  ✓ Variable workloads
  ✓ Fast setup
  ✓ No infrastructure
  ✓ Elastic scaling
  ✓ Pay-per-use
  ✓ Managed

USE ON-PREM:
  ✓ Predictable workloads
  ✓ Data sovereignty
  ✓ Full control
  ✓ No vendor lock-in
  ✓ Existing hardware
  ✓ Compliance
""")

print("\n=== Hybrid ===")
print("""
Hybrid:
  - Cloud + on-prem
  - Data gravity
  - Compliance
  - Bursting

Example:
  - On-prem for sensitive data
  - Cloud for analytics
  - Data sync
""")

print("\n=== TCO Comparison ===")
print("""
Total Cost of Ownership:

Cloud:
  - No hardware
  - No maintenance
  - Pay-per-use
  - Variable

On-Prem:
  - Hardware
  - Maintenance
  - Staff
  - Fixed

Break-even:
  - Depends on utilization
  - High utilization → on-prem
  - Variable → cloud
""")

💼 Use Cases

🎯 Real-World Applications

Cloud warehouses power analytics, BI, and data science across every industry.

Use Cases

Analytics: BI, reporting. Data science: ML. Data sharing: Collaboration. Real-time: Streaming. Data lake: Query S3. Multi-cloud: Global.

💼 Analytics💼 Data Science💼 Sharing💼 Real-Time

# Use Cases
print("=== Industry Use Cases ===")
print(f"{'Industry':<20} {'Use Case':<35} {'Platform':<20}")
print("-" * 75)
print(f"{'E-commerce':<20} {'Product analytics':<35} {'Snowflake':<20}")
print(f"{'Finance':<20} {'Risk analysis':<35} {'Redshift':<20}")
print(f"{'Media':<20} {'Content analytics':<35} {'BigQuery':<20}")
print(f"{'Healthcare':<20} {'Patient analytics':<35} {'Synapse':<20}")
print(f"{'Tech':<20} {'Product analytics':<35} {'Snowflake':<20}")
print(f"{'Retail':<20} {'Inventory analytics':<35} {'BigQuery':<20}")

print("\n=== 1. Business Intelligence ===")
print("""
Use case:
  - Dashboards
  - Reports
  - KPIs

Value:
  - Fast
  - Interactive
  - Scalable

Tools:
  - Tableau
  - Power BI
  - Looker
  - Superset
""")

print("\n=== 2. Data Science ===")
print("""
Use case:
  - ML models
  - Feature engineering
  - Exploration

Value:
  - SQL + Python
  - Scalable
  - Integrated

Tools:
  - Jupyter
  - Python
  - ML frameworks
""")

print("\n=== 3. Data Sharing ===")
print("""
Use case:
  - Share data
  - Collaborate
  - Monetize

Value:
  - Secure
  - No copy
  - Real-time

Example:
  - Snowflake Data Marketplace
  - BigQuery Analytics Hub
""")

print("\n=== 4. Real-Time Analytics ===")
print("""
Use case:
  - Streaming data
  - Real-time insights
  - Alerts

Value:
  - Low latency
  - Integrated
  - Scalable

Tools:
  - Kafka
  - Snowpipe
  - BigQuery Streaming
""")

print("\n=== 5. Data Lake Queries ===")
print("""
Use case:
  - Query S3
  - No loading
  - External tables

Value:
  - No ETL
  - Fresh data
  - Cost-effective

Tools:
  - Redshift Spectrum
  - Snowflake External Tables
  - BigQuery External Tables
""")

# IMPLEMENTATION
print("\n=== Practical Example: E-commerce Analytics ===")

class EcommerceAnalytics:
    """Simulate e-commerce analytics."""
    
    def __init__(self):
        self.data = {
            'users': [
                {'id': 1, 'name': 'Alice', 'city': 'NYC'},
                {'id': 2, 'name': 'Bob', 'city': 'LA'},
                {'id': 3, 'name': 'Charlie', 'city': 'NYC'},
            ],
            'orders': [
                {'order_id': 101, 'user_id': 1, 'amount': 100, 'date': '2024-01-01'},
                {'order_id': 102, 'user_id': 2, 'amount': 200, 'date': '2024-01-01'},
                {'order_id': 103, 'user_id': 1, 'amount': 150, 'date': '2024-01-02'},
                {'order_id': 104, 'user_id': 3, 'amount': 300, 'date': '2024-01-02'},
            ],
        }
    
    def query(self, sql):
        """Execute query (simplified)."""
        print(f"Query: {sql[:60]}...")
        
        if 'GROUP BY city' in sql:
            result = {}
            for order in self.data['orders']:
                user = next(u for u in self.data['users'] if u['id'] == order['user_id'])
                city = user['city']
                result[city] = result.get(city, 0) + order['amount']
            return result
        
        return {'count': len(self.data['orders'])}


# Demo
print("=== E-commerce Analytics Demo ===")

analytics = EcommerceAnalytics()

print("\n1. Revenue by city:")
result = analytics.query("SELECT city, SUM(amount) FROM orders GROUP BY city")
for city, revenue in result.items():
    print(f"  {city}: ${revenue}")

# USE CASE SUMMARY
print("\n=== Use Case Summary ===")
print(f"{'Use Case':<25} {'Description':<40}")
print("-" * 65)
print(f"{'BI':<25} {'Dashboards':<40}")
print(f"{'Data science':<25} {'ML models':<40}")
print(f"{'Sharing':<25} {'Collaboration':<40}")
print(f"{'Real-time':<25} {'Streaming':<40}")
print(f"{'Data lake':<25} {'Query S3':<40}")

🚀 Future

🎯 Where Cloud Warehouses Are Going

The future is lakehouse, AI-driven, open formats, and serverless. Cloud warehouses are converging with data lakes.

Future Directions

Lakehouse: Warehouse + lake. AI: Auto-optimization. Open: Iceberg, Delta. Serverless: No ops. Unified: One platform. Multi-cloud: Global.

🚀 Lakehouse🚀 AI🚀 Open🚀 Serverless

# Future of Cloud Warehouses
print("=== 1. Lakehouse Convergence ===")
print("""
Trend: Warehouse + Lake = Lakehouse

Snowflake:
  - Iceberg tables
  - External tables

BigQuery:
  - BigLake
  - Iceberg

Databricks:
  - Lakehouse
  - Delta Lake

Future:
  - Lakehouse standard
  - Open formats
  - Unified analytics
""")

print("\n=== 2. AI and Automation ===")
print("""
Trend: AI-powered warehouses

Automation:
  - Auto-optimization
  - Auto-scaling
  - Auto-tuning

AI:
  - Natural language queries
  - Automated insights
  - Anomaly detection

Future:
  - Self-driving
  - Self-optimizing
  - AI-native
""")

print("\n=== 3. Open Formats ===")
print("""
Trend: Open wins

Formats:
  - Apache Iceberg
  - Delta Lake
  - Apache Hudi

Benefits:
  - No lock-in
  - Multi-engine
  - Community

Future:
  - Standardization
  - Interoperability
  - Open ecosystem
""")

print("\n=== 4. Serverless ===")
print("""
Trend: Serverless default

Services:
  - BigQuery
  - Snowflake (serverless)
  - Redshift Serverless
  - Synapse Serverless

Benefits:
  - No infrastructure
  - Pay per use
  - Auto-scaling

Future:
  - Serverless default
  - Event-driven
  - Cost-effective
""")

print("\n=== 5. Real-Time ===")
print("""
Trend: Real-time warehouses

Features:
  - Streaming ingestion
  - Real-time queries
  - Materialized views
  - Change streams

Future:
  - Real-time default
  - Streaming-first
  - Unified batch + stream
""")

print("\n=== 6. Multi-Cloud ===")
print("""
Trend: Multi-cloud

Snowflake:
  - AWS
  - Azure
  - GCP

Others:
  - Single cloud
  - But improving

Future:
  - Multi-cloud
  - Hybrid
  - Global
""")

# TIMELINE
print("\n=== Timeline ===")
print("""
Past:
  2010: BigQuery
  2012: Redshift
  2012: Snowflake
  2019: Synapse

Present:
  2024: Lakehouse
  2025: AI-powered

Future:
  2030: Serverless default?
  2035: AI-native?
  2040: New paradigms?
""")

print("\n=== Final Thoughts ===")
print("""
Cloud warehouses:
  - Snowflake
  - BigQuery
  - Redshift
  - Synapse

The future is:
  - Lakehouse
  - AI-driven
  - Open
  - Serverless
  - Multi-cloud

"Data in the cloud, insights everywhere."

Keep exploring. Keep building.
""")