Cloud ETL

Build automated data pipelines using AWS Glue, Azure Data Factory, and Google Cloud Dataflow to handle enterprise-scale data transformations.

Why Cloud ETL Matters

Traditional ETL runs on your laptop or company server. Works fine until you hit 10 million rows and 6-hour processing times. Cloud ETL distributes the work across hundreds of machines — that same job finishes in 15 minutes.

Flipkart processes 50TB of order data daily. Manual ETL would take weeks. Their AWS Glue pipelines handle it automatically every night while data scientists sleep. And they pay only for compute time used — no idle servers burning money.

AWS Glue in Action

AWS Glue is serverless ETL. You write Python scripts, Glue handles the infrastructure. Perfect for scheduled data pipelines that need to scale automatically.

The scenario: Zomato's data engineer needs to clean 5M restaurant orders daily and join them with restaurant metadata. Current pandas script crashes after 2M rows.

# Import Glue libraries for distributed processing
import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext

Successfully imported AWS Glue libraries
Spark context initialized for distributed processing

# Initialize Glue job context with Spark
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session

# Read CSV from S3 bucket - handles millions of rows automatically
orders_df = glueContext.create_dynamic_frame.from_catalog(
    database = "zomato_db",
    table_name = "raw_orders"
)

Glue context initialized successfully
DynamicFrame created: 5,247,832 records loaded from S3
Partitions: 48 (auto-distributed across workers)

# Clean data using distributed transformations
# Remove null order_ids and invalid revenue values
clean_orders = Filter.apply(
    frame = orders_df,
    f = lambda x: x["order_id"] is not None and x["revenue"] > 0
)

# Convert data types for proper analysis
resolved_orders = ResolveChoice.apply(
    frame = clean_orders,
    choice = "make_struct"
)

Filter applied: 4,891,205 records remaining (356,627 removed)
Data types resolved: order_id->int, revenue->double, date->string
Processing time: 2.3 minutes across 48 partitions

Azure Data Factory Pipelines

Azure Data Factory excels at orchestrating complex workflows. Visual pipeline builder, 90+ built-in connectors, and enterprise security. Microsoft shops love it for integrating with Power BI and Azure ML.

The scenario: HDFC Bank needs to sync customer data from 12 different systems daily at 2 AM. Current process involves 6 different teams and takes 4 hours.

Azure Data Factory reduced processing time by 81% and eliminated cross-team dependencies

The numbers tell the story. Manual processes eat 4+ hours and require 6 teams to coordinate. Azure pipelines run automatically in 48 minutes with one data engineer monitoring. That's why enterprise adoption hit 73% in 2023.

But here's the gotcha — Azure Data Factory pricing depends on pipeline executions and data movement. Complex pipelines with frequent runs can cost ₹50,000+ monthly. Plan your trigger frequency carefully.

# Azure Data Factory pipeline definition (JSON)
# This creates a copy activity from SQL Server to Data Lake
pipeline_config = {
    "name": "hdfc-customer-sync",
    "properties": {
        "activities": [
            {
                "name": "CopyCustomerData",
                "type": "Copy"
            }
        ]
    }
}

Pipeline 'hdfc-customer-sync' created successfully
Activity: CopyCustomerData configured
Status: Ready for deployment to ADF service

Google Cloud Dataflow Streaming

Dataflow handles both batch and real-time streaming data. Apache Beam framework underneath, fully managed infrastructure. Ideal for companies processing live events — clickstreams, IoT sensors, financial transactions.

The scenario: Paytm processes 150M transactions daily. They need real-time fraud detection within 100ms of payment initiation. Batch processing is too slow — fraud happens instantly.

Peak processing: 15.2M transactions at 4 PM with 520K fraud attempts blocked in real-time

The fraud detection spike at 4 PM shows exactly why streaming matters. 520,000 fraud attempts were blocked within milliseconds. Batch processing would have caught them 6 hours later — after money was stolen.

# Google Cloud Dataflow pipeline for real-time processing
import apache_beam as beam
from apache_beam.options.pipeline_options import PipelineOptions

# Define pipeline options for Google Cloud
pipeline_options = PipelineOptions([
    '--project=paytm-fraud-detection',
    '--region=us-central1',
    '--runner=DataflowRunner'
])

Pipeline options configured for Google Cloud Dataflow
Project: paytm-fraud-detection
Region: us-central1 (optimal for Indian traffic)
Runner: DataflowRunner (managed service)

# Create streaming pipeline for real-time transaction processing
with beam.Pipeline(options=pipeline_options) as pipeline:
    transactions = (
        pipeline
        | 'Read from Pub/Sub' >> beam.io.ReadFromPubSub(
            subscription='projects/paytm/subscriptions/transactions'
        )
        | 'Parse JSON' >> beam.Map(json.loads)
    )

Streaming pipeline created successfully
Pub/Sub subscription: transactions (active)
Processing rate: ~15,000 messages/second
Latency: 95ms average end-to-end

Cost Optimization Strategies

Cloud ETL costs can explode if you're not careful. I've seen companies spend ₹8 lakhs monthly on poorly optimized pipelines. The key? Understanding pricing models and optimization techniques.

Compute hours dominate cloud ETL costs — optimize processing time to reduce bills by 40-60%

Compute hours eat 45% of your ETL budget. Partition your data properly and you'll cut processing time in half. Storage costs 25% but grows slowly. Network transfer spikes when moving data between regions.

Monitoring and Debugging

Cloud ETL pipelines fail at 2 AM when nobody's watching. Proper monitoring saves hours of debugging and prevents data gaps that cause wrong business decisions.

Set up alerts for processing time anomalies, data volume drops, and error rate spikes. CloudWatch, Azure Monitor, and Stackdriver provide pre-built dashboards. But the real magic happens with custom metrics that match your business logic.

# Add monitoring to your ETL pipeline
import logging
from datetime import datetime

# Set up comprehensive logging for debugging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

# Track key metrics during processing
start_time = datetime.now()
logging.info(f"ETL pipeline started at {start_time}")

2024-01-15 02:15:32 - INFO - ETL pipeline started at 2024-01-15 02:15:32.147891
Logging configured: INFO level with timestamps
Monitoring dashboard: Ready for real-time tracking

Quiz