ETL/ELT Pipeline Design: Batch, Streaming, and Hybrid Approaches

1. The Batch Processing Pipeline: The Scheduled Workhorse (Easy)

• Technical Explanation:
  Batch processing is a data processing model where data is collected, stored, and then processed in large, discrete groups or "batches." These jobs are typically executed on a recurring schedule (e.g., hourly, daily). This model is synonymous with the traditional ETL (Extract, Transform, Load) and modern ELT (Extract, Load, Transform) paradigms we discussed previously. It prioritizes high-volume throughput and cost-efficiency over low latency.

  The architecture typically involves:

  • Extract: Pulling data from various sources (databases, logs, APIs).
  • Transform: A powerful compute engine like Apache Spark or a legacy system like Hadoop MapReduce cleans, joins, aggregates, and restructures the entire batch of data.
  • Load: The processed data is loaded into a destination system, often a data warehouse like Snowflake or a data lake for historical analysis.

• Metaphor for a 5-Year-Old:
  Imagine a mail carrier who doesn't deliver letters one by one. Instead, they collect all the mail for your entire neighborhood in a big bag all day long. At the end of the day, they sit down, sort all the letters into neat piles for each house (transform), and then deliver all the mail for each house in one big drop-off (load). It's efficient for the mail carrier, but you have to wait until the end of the day to get your letters.

• Python Code Snippet (Conceptual Batch Job):
  This script simulates a daily batch job. It reads all the log files generated in a day, processes them together to create a summary, and writes a single report.

  import os
  import glob
  
  # Setup: Create some dummy daily log files (our "batch" of data)
  os.makedirs("daily_logs", exist_ok=True)
  with open("daily_logs/2023-10-28_10-00.log", "w") as f: f.write("ERROR: Connection failed\nINFO: User logged in")
  with open("daily_logs/2023-10-28_14-00.log", "w") as f: f.write("INFO: Data processed\nERROR: Disk full")
  with open("daily_logs/2023-10-28_18-00.log", "w") as f: f.write("WARN: High latency detected")
  
  def run_daily_batch_report():
      """Simulates a batch job that runs once a day."""
      print("Starting daily batch job...")
      
      log_files = glob.glob("daily_logs/*.log")
      total_lines = 0
      error_count = 0
      
      # EXTRACT & TRANSFORM: Process the entire batch of files
      for file_path in log_files:
          with open(file_path, "r") as f:
              for line in f:
                  total_lines += 1
                  if "ERROR" in line:
                      error_count += 1
      
      # LOAD: Write the final aggregated report
      report = f"Daily Summary:\nTotal Lines Processed: {total_lines}\nTotal Errors: {error_count}"
      with open("daily_report.txt", "w") as f:
          f.write(report)
      
      print("Daily batch job finished. Report generated.")
      print(report)
  
  run_daily_batch_report()
  
  # Cleanup
  os.remove("daily_report.txt")
  for f in glob.glob("daily_logs/*.log"): os.remove(f)
  os.rmdir("daily_logs")
  

2. The Stream Processing Pipeline: The Real-Time Reactor (Easy)

• Technical Explanation:
  Stream processing is a model where data is processed continuously, event by event, as it is generated. The goal is to minimize latency and enable real-time reactions. The data is considered "unbounded"—a never-ending flow.

  The architecture typically consists of:

  • Ingestion: An event broker like Apache Kafka or AWS Kinesis captures the stream of events from sources (e.g., IoT sensors, user clicks) in a durable and scalable way.
  • Processing: A stream processing engine, such as Apache Flink or Spark Streaming, consumes events from the broker. It applies transformations, runs aggregations over windows of time, or detects patterns on each event as it arrives.
  • Delivery: The processed data is immediately sent to a destination system, like a monitoring dashboard, a notification service, or a real-time application database.

• Metaphor for a 5-Year-Old:
  This is like a text message conversation. As soon as your friend sends you a message (an event), your phone buzzes, and you can read and reply to it instantly. You don't wait for all of your friend's messages to arrive at the end of the day in a big bundle. You react to each one as it comes in.

• Python Code Snippet (Conceptual Stream Processor):
  This script uses a Python generator to simulate a real-time stream of financial transactions. The process_stream function inspects each transaction the moment it "arrives."

  import time
  import random
  
  def real_time_transaction_stream():
      """A generator to simulate a never-ending stream of transactions."""
      transaction_id = 0
      while True:
          yield {
              'id': transaction_id,
              'amount': round(random.uniform(1.0, 2000.0), 2),
              'timestamp': time.time()
          }
          transaction_id += 1
          time.sleep(random.uniform(0.5, 2.0)) # Simulate time between events
  
  def process_stream(stream, fraud_threshold=1000.0):
      """Processes each event from the stream as it arrives."""
      print(f"Starting stream processor. Fraud alert threshold: ${fraud_threshold}")
      for event in stream:
          # Process one event at a time
          print(f"  -> Received transaction {event['id']} for ${event['amount']:.2f}")
          if event['amount'] > fraud_threshold:
              # Real-time reaction
              print(f"!!! ALERT: Potential fraud detected! Transaction {event['id']} of ${event['amount']:.2f} exceeds threshold.")
  
  # In a real system, this would run forever. We'll simulate for a few events.
  try:
      transaction_generator = real_time_transaction_stream()
      # Create a limited version of the generator for this example
      limited_stream = (next(transaction_generator) for _ in range(5))
      process_stream(limited_stream)
  except StopIteration:
      print("Stream simulation ended.")
  

3. Choosing Your Model: Latency, Throughput & Cost Trade-offs (Medium)

Choosing between batch and stream processing is a classic system design trade-off. The decision hinges on business requirements, not just technical superiority.

To get a clear overview of these trade-offs, let's turn to a well-structured guide.

Here is a summary of the key trade-offs you must be able to articulate in an interview:

Real-world Example (Netflix):

• Batch Pipeline: A nightly job that processes all of the viewing data from the previous 24 hours to calculate and update the "Top 10 in your country" list. Accuracy and completeness are key; a 12-hour delay is acceptable.
• Stream Pipeline: When you hit pause on a show, an event is processed in real time to save your exact timestamp. When you resume on another device, that real-time data is used to start playback from the correct spot. Latency is critical for a good user experience.

4. Practical Design: A Batch Pipeline for Analytics (Hard)

Let's ground these concepts in a realistic system design problem. Imagine you're at Meta, designing a system to aggregate ad clicks for advertisers. The primary requirement is to provide a dashboard where advertisers can see how many clicks their ads received yesterday.

This scenario, where data freshness is measured in days or hours, is a perfect fit for a batch pipeline.

• Technical Explanation:
  The architecture proposed in the video follows a classic ELT pattern suitable for batch processing:

  1. Extract & Load: A Click Processor service receives click events and immediately loads them into a write-optimized, scalable database like Cassandra. This raw data store is optimized to handle a massive influx of writes (high velocity).
  2. Transform: A scheduled Cron Job kicks off an Apache Spark job (e.g., every hour or once a day). This job reads a large batch of raw click events from Cassandra.
  3. Aggregate: The Spark job performs a massive-scale MapReduce operation to transform the raw data, aggregating clicks by ad ID and time window (e.g., per minute).
  4. Load (to Analytics DB): The aggregated, structured results are loaded into a read-optimized OLAP Database. This database is designed for the fast analytical queries that will power the advertiser's dashboard.

• Metaphor:
  This is like a restaurant kitchen. The waiters (Click Processors) take raw orders (clicks) and put them on a spike (Cassandra) all evening. It's fast and simple. At the end of the night, the head chef (Spark) takes all the orders from the spike, calculates how many of each dish were sold (aggregation), and writes the final totals on a small whiteboard (OLAP DB) for the manager to review in the morning.

• Python Code Snippet (Conceptual PySpark Batch Aggregation):
  This is how the core logic inside the Spark job might look.

  # This is a conceptual PySpark code snippet. It will not run without a Spark environment.
  from pyspark.sql import SparkSession
  from pyspark.sql.functions import count, window
  
  # Assume 'spark' is a SparkSession entry point
  # Assume 'raw_clicks_df' is a DataFrame read from the raw data source (e.g., Cassandra)
  # with columns: 'ad_id', 'timestamp'
  
  # TRANSFORM: Aggregate clicks into 1-minute windows
  aggregated_df = raw_clicks_df \
      .groupBy(
          window("timestamp", "1 minute"),  # Group events into 1-minute tumbling windows
          "ad_id"                          # And also group by the ad ID
      ) \
      .agg(count("*").alias("total_clicks")) # Count the clicks in each group
  
  # LOAD: Write the aggregated batch results to a read-optimized store
  # In a real system, this would write to an OLAP database or data warehouse.
  aggregated_df.write.mode("overwrite").parquet("analytics_data/")
  
  # The advertiser dashboard would now query these pre-aggregated results.
  

5. Evolving the Design: Streaming and Hybrid Architectures (Harder)

The batch design is great for cost and throughput, but what if the business demands faster insights? "I don't want to wait until tomorrow, I want to see clicks from the last 5 minutes!" This is where the design must evolve.

• Technical Explanation:
  To solve the latency problem, we replace the batch components with a streaming architecture.

  1. From DB to Stream: Instead of writing to Cassandra, the Click Processor now sends events to a Kinesis stream (or Kafka).
  2. From Batch to Stream Processor: The scheduled Spark job is replaced with an always-on Apache Flink job that reads from the stream in real-time. Flink maintains the aggregations in memory, flushing updates to the OLAP database every minute (or even every few seconds).

  However, this introduces a new risk: what if the streaming processor has a bug or goes down, leading to inaccurate counts? This leads to a Hybrid Architecture.

  • Hybrid Model (Speed + Reconciliation): We keep the real-time streaming pipeline for low-latency dashboards. But, we also run the original, robust Spark batch job once a day. This batch job re-processes all the raw events (which are also dumped to a data lake like S3 from Kinesis) and reconciles any discrepancies in the OLAP database. This gives us the best of both worlds: fast, near-real-time data that is eventually corrected to be 100% accurate.

  This practical, hybrid approach is a variation of well-known patterns like the Lambda and Kappa architectures.

Conclusion

You have now learned how to design data pipelines by choosing the appropriate processing model based on clear business trade-offs. This is a core competency for any senior data or backend engineer.

Key Takeaways:

• Batch Processing is the scheduled workhorse, ideal for historical analysis where latency is not a primary concern. It prioritizes throughput and cost-efficiency.
• Stream Processing is the real-time reactor, essential for use cases requiring immediate action and low-latency insights.
• The Choice is a Trade-off between latency, throughput, data scope (bounded vs. unbounded), and cost.
• Hybrid Architectures are common in the real world. They combine the speed of streaming for real-time views with the accuracy and reliability of batch processing for reconciliation and deep analysis, giving you the best of both worlds.

Next Lesson Preview

We've designed pipelines to ingest and process data. But what happens when the structure of that data changes? A source system might add a new field, remove an old one, or change a data type. If not handled properly, this "schema drift" can break your entire pipeline. In our next lesson, we will learn how to Manage schema evolution in data pipelines using strategies like schema-on-read and schema registries.