Chapter 2: Batch Processing Patterns - Data Engineering Course: Complete Interview Preparation | PlayBook Library

SLIDE 21: Section Intro, Batch Processing Patterns

Section 3: Batch Processing Patterns

Batch processing is the workhorse of data engineering. Despite the industry’s enthusiasm for streaming, the vast majority of production data pipelines are batch. Netflix processes over 1 trillion events per day, and the bulk of that processing happens in scheduled batch jobs, not streaming pipelines. Spotify computes personalized playlists for 600 million users using nightly batch jobs. LinkedIn rebuilds its People You May Know graph in batch. Batch processing is not the “old way.” It is the default pattern for any workload where throughput matters more than latency.

This section teaches you how batch processing works under the hood (MapReduce, Spark, DAG execution), how to optimize it (partitioning, shuffle minimization, file format selection), and how to avoid the most common performance and correctness pitfalls.

Recognition Signal:

When the data can be processed in scheduled intervals, when throughput matters more than latency, or when the interviewer mentions daily, hourly, or periodic processing, you are in batch processing territory. Prompts like “Process yesterday’s log data,” “Generate a daily report,” “Train the recommendation model on last week’s data,” or “Recompute all user features” are all batch processing problems.

What We’ll Cover:

When batch is the right choice (and when it is not)
The MapReduce mental model and why it still matters
Apache Spark: architecture, execution model, and optimization
Partitioning strategies in both processing and storage
The shuffle problem and how to minimize its cost
The small file problem and compaction strategies
File format selection for batch workloads
Cost optimization patterns for batch infrastructure

Connection to the Pipeline Mental Model:

Batch processing lives primarily in the Transform stage of the five-stage pipeline (Ingest → Store → Transform → Serve → Monitor). It reads data from the Store layer, applies transformations, and writes results to either the same storage layer (a higher-quality tier) or to the Serve layer (warehouse, feature store). In Section 2, we covered how data gets into the pipeline. This section covers what happens to that data once it arrives.

SLIDE 22: When Batch is the Right Choice

Batch processing is the right default when there is no explicit requirement for sub-minute latency. The engineering instinct to “make it real-time” adds cost, complexity, and operational burden. In interviews, demonstrating that you can identify when batch is sufficient (and resist unnecessary streaming) is a senior-level signal.

Batch is the right choice when:

The consumer tolerates latency of minutes, hours, or days. Daily business reports, weekly summaries, and monthly reconciliations do not need real-time processing.
The workload benefits from processing data in bulk. Aggregations, joins across large tables, sorting, and ML model training are all more efficient in batch because the engine can optimize across the entire dataset.
Cost efficiency matters. Batch clusters can use spot/preemptible instances (60-90% cheaper), can be shut down between runs, and can process during off-peak hours.
The data arrives in batches naturally. Daily file drops from vendors, nightly database exports, and periodic API pulls are inherently batch sources.
Correctness requires processing complete datasets. Computing accurate percentiles, rankings, or deduplication across an entire day’s data requires seeing all of it before producing output.

Batch is NOT the right choice when:

The business requires sub-second or sub-minute response to events (fraud detection, real-time bidding, live dashboards with second-level freshness).
Individual events need to trigger immediate downstream actions (order confirmation emails, inventory decrements, alerting on anomalies).
The data volume per event is small but the event rate is high and continuous, and each event should be processed independently.

These streaming use cases are covered in Section 4. Most production systems use both batch and streaming for different parts of the same platform, a pattern covered in Section 5.

Scale Context:

Netflix: Nightly batch jobs in Spark process over 1.5 petabytes of data to compute content recommendations, A/B test results, and content performance metrics.
Uber: Daily batch Spark jobs compute driver payments, trip analytics, and marketplace health metrics from the previous day’s ride data.
Airbnb: Batch pipelines compute search ranking features, pricing suggestions, and host performance metrics on a daily cadence.

Interview Tip:

When the interviewer presents a problem, resist the instinct to propose streaming immediately. Ask: “What is the latency requirement for this data? Does the consumer need it within seconds, minutes, or is a daily refresh acceptable?” If the answer is anything longer than a few minutes, batch is likely the right starting point. You can always add a streaming layer for the components that genuinely need low latency.

SLIDE 23: The MapReduce Mental Model

MapReduce is the foundational model for all distributed batch processing. You will probably never write raw MapReduce code in a modern data engineering role. But understanding MapReduce is critical because every distributed processing framework (Spark, Hive, Presto, Flink in batch mode) uses the same three-phase model internally. When Spark “shuffles” data, it is doing the MapReduce shuffle. When a SQL engine “groups by” a key, it is doing Map, Shuffle, Reduce.

The Three Phases:

Phase 1: Map

Process each input record independently. No coordination between workers needed. Each worker applies the same function to its local chunk of data.

Structure & Concept Flow

Input records: Map function: Output (key, value) pairs:

"GET /home 200" → extract(URL, count) → ("/home", 1)

"GET /about 200" → extract(URL, count) → ("/about", 1)

"GET /home 404" → extract(URL, count) → ("/home", 1)

"GET /about 200" → extract(URL, count) → ("/about", 1)

"GET /home 200" → extract(URL, count) → ("/home", 1)

Phase 2: Shuffle

Group all values by key. This is the expensive phase because data must move across the network. Records with the same key must end up on the same worker for the reduce phase.

CODE

Before shuffle (distributed across workers):
  Worker A: ("/home", 1), ("/about", 1)
  Worker B: ("/home", 1), ("/about", 1), ("/home", 1)

After shuffle (grouped by key):
  Worker A: ("/home", [1, 1, 1])
  Worker B: ("/about", [1, 1])

Phase 3: Reduce

Aggregate the grouped values into a final result. Each worker processes all values for its assigned keys.

Structure & Concept Flow

("/home", [1, 1, 1]) → reduce(sum) → ("/home", 3)

("/about", [1, 1]) → reduce(sum) → ("/about", 2)

Why This Matters for Interviews:

When an interviewer asks “How would you count page views per URL from 1TB of logs?”, they are asking you to describe a MapReduce-style computation, even if the answer uses Spark SQL. The mental model helps you reason about parallelism (how many mappers?), data movement (how much shuffle?), and bottlenecks (is the reduce step skewed?).

Understanding that shuffle is the most expensive phase is what leads to every major Spark optimization technique covered later in this section: broadcast joins to avoid shuffle, pre-partitioning to minimize shuffle, and map-side aggregation to reduce shuffle volume.

SLIDE 24: MapReduce Applied to a Real Problem

Problem: Count page views per URL from 1TB of web server access logs.

Setup:

CODE

Input:    1 TB of access log files in S3 (Parquet format, ~10 billion rows)
Cluster:  100 workers, each with 4 cores and 16 GB RAM
Goal:     Produce a table of (URL, view_count), sorted by view_count descending

Phase 1: Map (Parallel, No Network I/O)

Each of the 100 workers processes its local 10 GB chunk. The map function extracts the URL from each log line and emits (URL, 1).

CODE

Worker 1 processes 10 GB:  emits ~100M (URL, 1) pairs
Worker 2 processes 10 GB:  emits ~100M (URL, 1) pairs
...
Worker 100 processes 10 GB: emits ~100M (URL, 1) pairs

Total map output: ~10 billion (URL, 1) pairs
Time: ~2 minutes (disk I/O bound, fully parallel)

Optimization: Map-Side Combine (Pre-Aggregation)

Before sending data to the shuffle, each worker locally combines its output. If worker 1 saw “/home” 50,000 times, instead of sending 50,000 individual (URL, 1) pairs, it sends a single (“/home”, 50000) pair.

CODE

Without combiner:  10 billion pairs sent across network (hundreds of GB)
With combiner:     ~1 million unique URLs × 100 workers = ~100M pairs (a few GB)

Network I/O reduction: ~99%

Phase 2: Shuffle (Network-Intensive)

All pairs with the same URL must end up on the same worker. The framework hash-partitions by URL.

Structure & Concept Flow

Hash("/home") % 100 = Worker 37 → All ("/home", count) pairs go to Worker 37

Hash("/about") % 100 = Worker 12 → All ("/about", count) pairs go to Worker 12

Shuffle volume (with combiner): ~2-5 GB across the network

Shuffle volume (without combiner): ~200-500 GB across the network

Time (with combiner): ~30 seconds

Phase 3: Reduce (Parallel, No Network I/O)

Each worker sums the counts for its assigned URLs.

Structure & Concept Flow

Worker 37 receives: ("/home", [50000, 48000, 52000, ...]) → ("/home", 4890000)

Worker 12 receives: ("/about", [12000, 11500, 13000, ...]) → ("/about", 1180000)

Time: ~30 seconds (CPU bound, fully parallel)

Total Execution:

CODE

Map phase:     ~2 minutes  (parallel, I/O bound)
Shuffle phase: ~30 seconds (network bound, reduced by combiner)
Reduce phase:  ~30 seconds (parallel, CPU bound)
Total:         ~3 minutes for 1 TB on 100 workers

The Same Computation in Spark SQL:

CODE

SELECT url, COUNT(*) as view_count
FROM access_logs
WHERE date = '2024-01-15'
GROUP BY url
ORDER BY view_count DESC

Spark translates this SQL into the same Map → Shuffle → Reduce execution plan internally. Understanding MapReduce means you understand what Spark is doing under the hood, which is essential for optimization.

SLIDE 25: Apache Spark: Architecture and Execution Model

Apache Spark is the dominant batch processing framework in production data engineering. Understanding its architecture is not about memorizing configuration parameters. It is about understanding the execution model well enough to reason about performance, identify bottlenecks, and explain optimization decisions in interviews.

Architecture: Driver + Executors

The Driver is the coordinator. It parses your code, builds the execution plan (DAG of stages), and distributes work to executors. It does not process data itself.

The Executors are the workers. Each executor runs on a separate machine (or container), has its own memory and CPU cores, and processes a subset of the data. An executor processes one partition at a time per core.

The Cluster Manager (YARN, Kubernetes, or Spark Standalone) allocates resources and monitors executor health.

API Evolution: RDDs → DataFrames → SQL

Spark’s API evolved from low-level to high-level, with each layer adding optimizations:

RDDs (Resilient Distributed Datasets): The original API. Low-level, explicit control over partitioning and operations. No automatic optimization. You rarely use RDDs directly today unless you need fine-grained control.

DataFrames: Structured API with named columns and types. Spark’s Catalyst optimizer automatically optimizes the execution plan (predicate pushdown, join reordering, projection pruning). This is the standard API for data engineering.

Spark SQL: SQL interface on top of DataFrames. Same Catalyst optimization. Preferred for analysts and for readability.

CODE

# All three produce the same optimized execution plan:

# RDD (low-level, no automatic optimization)
rdd.map(lambda row: (row.category, row.revenue)) \
   .reduceByKey(lambda a, b: a + b)

# DataFrame (automatic optimization via Catalyst)
df.groupBy("category").agg(sum("revenue").alias("total_revenue"))

# SQL (same optimization, more readable)
spark.sql("SELECT category, SUM(revenue) as total_revenue FROM orders GROUP BY category")

Lazy Evaluation and DAG Execution

Spark does not execute transformations immediately. It builds a DAG (Directed Acyclic Graph) of all transformations and only executes when an action (write, collect, count, show) is called. This allows the optimizer to see the entire computation before executing any of it.

CODE

# These lines build the DAG but execute NOTHING:
df = spark.read.parquet("s3://lake/orders/")       # Read (transformation)
filtered = df.filter(col("date") == "2024-01-15")  # Filter (transformation)
grouped = filtered.groupBy("category")              # Group (transformation)
result = grouped.agg(sum("revenue"))                 # Aggregate (transformation)

# This line triggers execution of the entire DAG:
result.write.parquet("s3://lake/output/")            # Write (ACTION)

When the action triggers, Catalyst optimizes the DAG. For example, it pushes the date filter down to the Parquet reader so that only the relevant partition is read from S3, rather than reading all data and filtering afterward. This optimization happens automatically because Spark saw the entire plan before executing.

Stages and Tasks

Spark breaks the DAG into stages at shuffle boundaries. Within a stage, all operations can be pipelined (executed together without data movement). Between stages, data must be shuffled across the network.

Structure & Concept Flow

Stage 1 (no shuffle): Read Parquet → Filter → Project columns

↓ shuffle (groupBy triggers data redistribution)

Stage 2 (no shuffle): Aggregate → Write output

Each stage is divided into tasks, one per partition. If the input has 200 partitions, Stage 1 has 200 tasks that run in parallel across executors.

Interview Tip:

When discussing Spark in an interview, focus on the execution model (lazy evaluation, DAG optimization, stage boundaries at shuffles) rather than API syntax. The interviewer cares whether you understand why a broadcast join eliminates a shuffle stage, not whether you remember the exact syntax for F.broadcast().

SLIDE 26: Spark Partitioning Strategies

Partitioning is the mechanism that controls parallelism in Spark. The number of partitions determines how many tasks run in parallel, which directly impacts both performance and resource utilization. Getting partitioning wrong is one of the most common causes of slow Spark jobs.

The Core Principle:

One partition = one task = one unit of parallel work. If you have 200 partitions and 100 executor cores, Spark runs 100 tasks in parallel, then the next 100. If you have 10 partitions and 100 cores, 90 cores sit idle.

Partition Sizing Guidelines:

CODE

Target partition size: 128 MB - 256 MB (compressed)

Too small (< 50 MB per partition):
  - Scheduling overhead dominates. Spark spends more time managing tasks than processing data.
  - Driver overwhelmed tracking thousands of small tasks.
  - Example: 100 GB split into 10,000 partitions = 10 MB each. Excessive overhead.

Too large (> 1 GB per partition):
  - Individual tasks take too long. A single slow partition delays the entire stage.
  - Higher risk of out-of-memory errors within a single task.
  - Example: 100 GB split into 10 partitions = 10 GB each. Each task needs 10+ GB of memory.

Right-sized:
  - 100 GB / 200 MB target = 500 partitions. Each task processes ~200 MB.
  - On a cluster with 100 cores: 5 waves of 100 parallel tasks.

Hash Partitioning

Distribute data across partitions based on the hash of a key. Produces roughly even distribution for high-cardinality keys.

CODE

df.repartition(200, "user_id")

Use hash partitioning when you need even distribution and your subsequent operations (joins, aggregations) use the same key. If two DataFrames are hash-partitioned by the same key with the same number of partitions, joining them requires no shuffle.

Range Partitioning

Distribute data across partitions based on sorted key ranges. Partition 1 gets keys A-M, partition 2 gets keys N-Z.

CODE

df.repartitionByRange(200, "timestamp")

Use range partitioning when the output needs to be sorted, or when queries will filter on key ranges. Range partitioning can cause skew if the key distribution is uneven (e.g., most users are in one country).

Repartition vs Coalesce

CODE

# Repartition: full shuffle, creates exactly N partitions. Use to increase partitions.
df.repartition(500)

# Coalesce: no shuffle, merges existing partitions. Use to decrease partitions.
df.coalesce(100)

Repartition triggers a full shuffle (expensive) but produces evenly sized partitions. Coalesce avoids shuffle by merging adjacent partitions, but can produce unevenly sized partitions. The rule: use coalesce to reduce partition count (e.g., before writing output files), use repartition to increase partition count or to redistribute by a key.

Common Mistake:

Writing output files directly from a high-partition-count DataFrame produces thousands of small files (the small file problem, covered in SLIDE 29). Always coalesce before writing:

CODE

result.coalesce(10).write.parquet("s3://lake/output/")

This produces 10 output files instead of 500, avoiding downstream small file issues.

SLIDE 27: The Shuffle Problem in Distributed Processing

Shuffle is the most expensive operation in distributed batch processing. It is the phase where data is redistributed across the network based on a key, and it is the primary bottleneck in most Spark jobs. Understanding shuffle is the difference between a 10-minute job and a 2-hour job on the same data.

What Shuffle Is:

When an operation requires all records with the same key to be on the same worker (groupBy, join, distinct, repartition), Spark must physically move data across the network. Each worker sends its local records to the worker responsible for each key, determined by hash partitioning.

CODE

Before shuffle (data distributed by input split):
  Worker A has: user_1 events, user_3 events, user_5 events
  Worker B has: user_1 events, user_2 events, user_4 events
  Worker C has: user_2 events, user_3 events, user_5 events

After shuffle (data grouped by user_id):
  Worker A has: ALL user_1 events, ALL user_2 events
  Worker B has: ALL user_3 events, ALL user_4 events
  Worker C has: ALL user_5 events

Why Shuffle Is Expensive:

Every record involved in a shuffle goes through five steps: serialize on the source worker, write to local disk (spill), transfer across the network, write to local disk on the destination worker, deserialize. For a 500 GB shuffle, this means 500 GB of serialization, 500 GB of network transfer, and 500 GB of deserialization. On a 10 Gbps network, 500 GB takes approximately 7 minutes of pure network transfer, plus the serialization and disk I/O overhead.

Operations That Trigger Shuffle:

Structure & Concept Flow

groupBy / groupByKey → Group all records by key on the same worker

join (both sides large) → Records with matching keys must co-locate

distinct / dropDuplicates → Must compare across all partitions

repartition → Explicitly redistributes all data

orderBy / sort → Global sort requires exchanging data across workers

Operations That Do NOT Trigger Shuffle:

Structure & Concept Flow

filter / where → Each partition filters independently

map / select / withColumn → Each partition transforms independently

coalesce (decrease only) → Merges adjacent partitions without redistribution

broadcast join → Small table sent to all workers, no shuffle of large table

Four Strategies to Minimize Shuffle:

Strategy 1: Broadcast Join. When joining a large table with a small table (< 200-500 MB), broadcast the small table to every worker. Each worker joins its local partition of the large table with its local copy of the small table. No shuffle of the large table.

Strategy 2: Pre-Partitioning. If two DataFrames are already partitioned by the join key with the same number of partitions, joining them requires no shuffle. This is common when one DataFrame was written partitioned by the join key.

Strategy 3: Map-Side Aggregation. Before a groupBy shuffle, perform a local pre-aggregation (combiner) on each worker. This reduces the volume of data that crosses the network, as seen in the MapReduce example on SLIDE 24.

Strategy 4: Avoid Unnecessary Operations. Review whether a distinct or orderBy is actually needed. Each triggers a full shuffle. A distinct on a billion-row DataFrame is extremely expensive. If deduplication can be done earlier (at ingestion) or through an idempotent write (upsert), it avoids the shuffle entirely.

SLIDE 27B: Shuffle: Worked Example with Performance Impact

Problem: Join a 500 GB orders table with a 200 MB products dimension table to compute revenue by product category.

Approach 1: Default Join (Shuffle Both Sides)

CODE

orders = spark.read.parquet("s3://lake/orders/")       # 500 GB, 2 billion rows
products = spark.read.parquet("s3://lake/products/")    # 200 MB, 500K rows

result = orders.join(products, "product_id") \
    .groupBy("category") \
    .agg(sum("amount").alias("revenue"))

What Spark does with a default join:

Structure & Concept Flow

Stage 1: Read orders (500 GB), hash-partition by product_id → shuffle 500 GB

Stage 2: Read products (200 MB), hash-partition by product_id → shuffle 200 MB

Stage 3: Join co-located partitions, then groupBy category → shuffle again for aggregation

Total shuffle volume: ~500 GB + 200 MB + aggregation shuffle

Network time (10 Gbps): ~7 minutes for the join shuffle alone

Disk I/O: 500 GB written to shuffle files, 500 GB read back

Total runtime: ~45 minutes

Approach 2: Broadcast Join (Eliminate the Large Shuffle)

CODE

from pyspark.sql.functions import broadcast

orders = spark.read.parquet("s3://lake/orders/")       # 500 GB
products = spark.read.parquet("s3://lake/products/")    # 200 MB

result = orders.join(broadcast(products), "product_id") \
    .groupBy("category") \
    .agg(sum("amount").alias("revenue"))

What Spark does with a broadcast join:

Structure & Concept Flow

Stage 1: Read products (200 MB), broadcast to all workers (each gets a full copy)

Stage 2: Read orders (500 GB), each worker joins its local partition with its

local copy of products. No shuffle of the 500 GB orders table.

Stage 3: groupBy category → shuffle only the aggregation output (a few MB)

Total shuffle volume: 200 MB broadcast + a few MB aggregation shuffle

Network time: ~2 seconds for broadcast

Total runtime: ~8 minutes

Performance Comparison:

Metric	Default Join	Broadcast Join
Shuffle volume	~500 GB	~200 MB
Network transfer time	~7 minutes	~2 seconds
Total runtime	~45 minutes	~8 minutes
Speedup	baseline	~5.6x faster

The Rule:

If one side of a join is small enough to fit in executor memory (typically < 200-500 MB), always use a broadcast join. In most data engineering workloads, fact-to-dimension joins follow this pattern: the fact table is hundreds of GB or TB, and the dimension table is a few hundred MB at most. Broadcast joins should be your default for this pattern.

Interview Tip:

When walking through a batch pipeline that involves joins, explicitly state: “The product dimension table is 200 MB, so I’d use a broadcast join to avoid shuffling the 500 GB orders table. This eliminates the most expensive stage of the job.” This shows you understand where the performance bottleneck is and how to eliminate it.

SLIDE 28: Data Partitioning in Storage

Partitioning in Spark (previous slides) controls how data is distributed across workers during processing. Partitioning in storage controls how data is organized on disk (or in object storage). Both are critical, but they solve different problems.

Storage partitioning organizes data files into a directory structure based on one or more column values. When a query filters on the partition column, the query engine skips entire directories of data that do not match the filter. This is called partition pruning, and it is one of the most impactful optimizations in batch processing.

Partition by Date: The Most Common Pattern

CODE

s3://data-lake/orders/
  year=2024/
    month=01/
      day=14/
        part-00000.parquet  (150 MB)
        part-00001.parquet  (150 MB)
      day=15/
        part-00000.parquet  (148 MB)
        part-00001.parquet  (152 MB)
    month=02/
      ...

A query for January 15th reads only the day=15 directory (300 MB), not the entire dataset (potentially hundreds of TB).

CODE

SELECT * FROM orders WHERE date = '2024-01-15'
-- Reads: 300 MB (one day's partition)
-- Without partitioning: reads entire table (could be 100+ TB)

Choosing the Right Partition Key:

The partition key should be the column most frequently used in WHERE clauses. For most data engineering workloads, this is date.

Additional partition columns (region, category, source) make sense only when queries frequently filter on those columns AND each partition contains enough data (> 100 MB) to avoid the small file problem.

Structure & Concept Flow

Good: PARTITION BY (date) → 365 partitions/year, ~5 GB/day

Good: PARTITION BY (date, region) → if each region has > 100 MB/day

Bad: PARTITION BY (date, user_id) → millions of partitions, each with KB of data

Bad: PARTITION BY (date, hour, region, source) → over-partitioned, small files everywhere

Partition Pruning in Action:

CODE

Query: "Total revenue for January 2024"

With date partitioning:
  Spark reads: 31 partitions (January only) = ~155 GB
  Time: ~5 minutes

Without date partitioning:
  Spark reads: entire table = ~1.8 TB (full year)
  Time: ~50 minutes

Speedup from partition pruning: ~10x

Key Insight:

Partitioning by date is almost always the first partitioning decision. If you are not sure what to partition by, start with date. It aligns with how data arrives (daily increments), how batch jobs process data (one day at a time), and how most analytical queries filter (date ranges). You can always add secondary partition columns later if query patterns demand it.

SLIDE 29: The Small File Problem

The small file problem is one of the most common performance issues in production data lakes, and one of the most frequently tested concepts in data engineering interviews. It occurs when a storage directory contains thousands of small files instead of a fewer number of appropriately sized files.

What It Looks Like:

CODE

Problem state:
s3://data-lake/events/date=2024-01-15/
  part-00000.parquet   (2 MB)
  part-00001.parquet   (1.5 MB)
  part-00002.parquet   (3 MB)
  ...
  part-04999.parquet   (2.2 MB)
  Total: 5,000 files, average 2 MB each, 10 GB total

Healthy state:
s3://data-lake/events/date=2024-01-15/
  part-00000.parquet   (200 MB)
  part-00001.parquet   (200 MB)
  ...
  part-00049.parquet   (200 MB)
  Total: 50 files, average 200 MB each, 10 GB total

Both contain the same 10 GB of data. The first is 5,000 files; the second is 50. The performance difference is dramatic.

Why Small Files Are a Problem:

Each file requires a metadata operation (LIST, HEAD) before it can be read. Object storage like S3 charges per request and has latency per request (~5-10ms per LIST call). Reading 5,000 files means 5,000 metadata calls before any data is read, adding 25-50 seconds of pure overhead. Spark also creates one task per file (by default for small files), so the driver must schedule and track 5,000 tasks instead of 50, adding scheduling overhead.

CODE

Reading 5,000 small files:
  Metadata overhead:  5,000 × 10ms = 50 seconds
  Task scheduling:    5,000 tasks, driver overhead significant
  Actual read time:   ~2 minutes (network bound)
  Total:              ~3+ minutes

Reading 50 appropriately sized files:
  Metadata overhead:  50 × 10ms = 0.5 seconds
  Task scheduling:    50 tasks, negligible overhead
  Actual read time:   ~2 minutes (network bound, same data volume)
  Total:              ~2 minutes

Why It Happens:

Streaming ingestion writes frequently. A Kafka consumer writing to S3 every 30 seconds creates 2,880 files per day per partition. Over a week, that is 20,000+ files for a single partition.
Over-partitioning. Partitioning by too many columns (date + hour + region + source) creates thousands of directories, each with tiny files.
Spark jobs with too many output partitions. If a Spark job has 1,000 partitions and writes directly without coalescing, it produces 1,000 files.

Solutions:

Compaction jobs: A periodic batch job that reads small files, merges them into larger files, and deletes the originals. This is the most common approach.

CODE

spark.read.parquet("s3://lake/events/date=2024-01-15/") \
    .coalesce(50) \
    .write.mode("overwrite") \
    .parquet("s3://lake/events/date=2024-01-15/")

File size targets at write time: Configure the writer to produce files of a target size (128-256 MB). Spark’s maxRecordsPerFile option controls this.

Table formats with built-in compaction: Delta Lake, Apache Iceberg, and Apache Hudi all include automatic or manual compaction commands (e.g., OPTIMIZE in Delta Lake) that merge small files without manual Spark jobs.

CODE

-- Delta Lake compaction
OPTIMIZE delta.`s3://lake/events/` WHERE date = '2024-01-15'

Target File Sizes:

CODE

Minimum:     128 MB per file (below this, overhead becomes significant)
Ideal:       200-500 MB per file (good balance of parallelism and efficiency)
Maximum:     1 GB per file (beyond this, individual file reads take too long)

SLIDE 30: File Formats for Batch Processing

Choosing the right file format has a significant impact on query performance, storage cost, and schema evolution. This is a frequent interview topic because it tests whether you understand the difference between row-based and columnar storage and can match the format to the workload.

Row-Based vs Columnar:

In a row-based format, each record is stored contiguously: all columns for row 1, then all columns for row 2. This is efficient when you read entire rows (transactional access).

In a columnar format, each column is stored contiguously: all values for column A, then all values for column B. This is efficient when you read specific columns from wide tables (analytical access).

CODE

Table with 4 columns: user_id, name, email, revenue

Row-based storage (Avro, CSV):
  [user_1, "Alice", "a@x.com", 500] [user_2, "Bob", "b@x.com", 300] ...

Columnar storage (Parquet, ORC):
  [user_1, user_2, ...]  [Alice, Bob, ...]  [a@x.com, b@x.com, ...]  [500, 300, ...]

A query SELECT SUM(revenue) FROM users reads only the revenue column in Parquet (25% of the data) but reads all four columns in Avro (100% of the data).

Format Comparison:

Dimension	Parquet	Avro	ORC	JSON	CSV
Storage model	Columnar	Row-based	Columnar	Row-based	Row-based
Compression ratio	Excellent (5-10x)	Good (3-5x)	Excellent (5-10x)	Poor (1-2x)	Poor (1-2x)
Analytical query speed	Fast (column pruning)	Slow (reads all columns)	Fast (column pruning)	Very slow	Very slow
Write speed	Moderate (must organize by column)	Fast (sequential write)	Moderate	Fast	Fast
Schema evolution	Good (add/remove columns)	Excellent (Avro schema registry)	Good	Flexible (schemaless)	None
Splittable (parallel reads)	Yes	Yes (with container format)	Yes	Only if line-delimited	Only if line-delimited
Type system	Rich (nested types, decimals)	Rich (unions, enums)	Rich	Implicit (strings)	None
Best for	Analytics queries, data lake storage, warehouse loading	Data interchange, Kafka messages, streaming ingestion	Hive/Presto workloads	API responses, config files	Legacy interchange, small datasets

Decision Guide:

For data at rest in a lake or warehouse (analytics workloads), use Parquet. This is the default choice for the vast majority of data engineering pipelines. Columnar storage plus excellent compression means faster queries and lower storage costs.

For data in motion (Kafka messages, streaming interchange between services), use Avro. Row-based format is natural for individual records, and the schema registry provides strong schema evolution with forward/backward compatibility checks.

For Hive-native environments, ORC is competitive with Parquet and often performs slightly better in Hive and Presto. In practice, both Parquet and ORC are excellent columnar formats, and the choice often comes down to ecosystem compatibility.

Never use JSON or CSV for large-scale analytical storage. They lack compression, schema enforcement, and columnar access. Use them only at the ingestion boundary (receiving data from APIs or legacy systems) and convert to Parquet as soon as the data lands in the pipeline.

Interview Tip:

A common interview question is “Why Parquet over CSV for your data lake?” The answer touches three points: columnar storage means analytical queries read only the columns they need (10x faster on wide tables), built-in compression reduces storage cost by 5-10x, and schema metadata is embedded in the file so the reader knows column types without external schema definitions.

SLIDE 31: Batch Processing Frameworks Comparison

Multiple frameworks exist for batch processing, each with different performance characteristics, deployment models, and sweet spots. In interviews, the goal is not to name the “best” framework but to explain which framework fits which workload and why.

Dimension	Apache Spark	Hive (on Tez/Spark)	Presto / Trino	Flink (Batch Mode)	Warehouse SQL (Snowflake, BigQuery)
Execution model	In-memory DAG	MapReduce-style (Tez improves this)	Pipelined, in-memory	Streaming-first, batch as special case	Managed, serverless
Latency (ad-hoc queries)	Seconds to minutes	Minutes to hours	Seconds to minutes	Seconds to minutes	Seconds to minutes
Throughput (large jobs)	Excellent	Good	Good (not designed for very large ETL)	Excellent	Excellent (auto-scales)
Programming model	Python, Scala, SQL, Java	SQL (HiveQL)	SQL (ANSI-compliant)	Java, Scala, Python, SQL	SQL
State/ML support	MLlib, GraphX, structured streaming	No	No	Stateful processing, ML (FlinkML)	Limited (UDFs, ML integration varies)
Deployment complexity	Medium (cluster management)	Medium (Hadoop ecosystem)	Medium (coordinator + workers)	Medium to high	None (fully managed)
Cost model	Cluster-based (pay for provisioned resources)	Cluster-based	Cluster-based	Cluster-based	Pay-per-query or provisioned
Best for	General-purpose ETL, ML pipelines, complex transformations	Legacy Hadoop workloads, very large batch jobs	Interactive ad-hoc queries on data lake	Unified batch + streaming	SQL-centric analytics, teams without infrastructure engineers

When to Choose Each:

Spark is the default for general-purpose batch ETL. If your pipeline involves complex transformations, joins across large tables, ML feature engineering, or writing custom processing logic in Python/Scala, Spark is the standard choice.

Presto/Trino is best for interactive, ad-hoc SQL queries against data lake files. It excels at queries that analysts run interactively (sub-minute response times) but is not designed for multi-hour ETL jobs that transform and write hundreds of GB.

Warehouse SQL (Snowflake, BigQuery) is the right choice when the data is already in the warehouse, the transformations are expressible in SQL, and the team wants zero infrastructure management. dbt (covered in Section 10) sits on top of warehouse SQL to orchestrate transformation DAGs.

Flink in batch mode makes sense when the team already uses Flink for streaming and wants a unified framework. It is not the default choice for batch-only workloads.

SLIDE 32: Cost Optimization in Batch Processing

Batch processing infrastructure can be one of the largest line items in a data platform’s budget. At scale, a poorly optimized Spark cluster costs tens of thousands of dollars per month. In interviews, discussing cost optimization signals that you think about systems holistically, not just functionally.

Strategy 1: Spot and Preemptible Instances

Cloud providers sell excess compute capacity at 60-90% discount. Spot instances can be reclaimed with short notice, but batch workloads are ideal for spot because they are fault-tolerant (Spark retries failed tasks) and not latency-sensitive.

CODE

On-demand pricing (AWS, m5.2xlarge):   $0.384/hour
Spot pricing (same instance):           $0.08-$0.15/hour
Savings:                                 60-80%

Example: 100-node Spark cluster running 4 hours/day
  On-demand: 100 × $0.384 × 4 = $153.60/day = $4,608/month
  Spot:      100 × $0.12 × 4  = $48.00/day  = $1,440/month
  Savings: $3,168/month

Best practice: run the driver and a small number of core workers on on-demand instances (to guarantee the job can complete), and run the majority of workers on spot instances.

Strategy 2: Right-Sizing Clusters

Over-provisioned clusters waste money. Under-provisioned clusters waste time (and therefore also waste money through longer job duration).

CODE

Sizing approach for a daily job processing 500 GB:
  Target partition size: 200 MB
  Number of partitions: 500 GB / 200 MB = 2,500 partitions
  Each executor: 4 cores (processes 4 partitions simultaneously)
  Executors needed: 2,500 / 4 = 625 partitions per wave
  With 50 executors: 625 / 50 = ~13 waves, job takes ~15-20 minutes
  With 200 executors: 625 / 200 = ~3 waves, job takes ~5 minutes

  50 executors: slower but cheaper ($20/run)
  200 executors: faster but more expensive ($40/run, even though duration is shorter)

The right size depends on the SLA. If the job must complete by 5 AM and has a 4-hour window, there is no reason to pay for a 200-node cluster that finishes in 5 minutes.

Strategy 3: Schedule Off-Peak

Run batch jobs during off-peak hours (nights, weekends) when spot prices are lower and competition for resources is reduced.

Strategy 4: Data Lifecycle Management

Not all data needs to live in hot storage forever. Implement lifecycle policies that automatically transition data to cheaper tiers as it ages.

Structure & Concept Flow

Data age 0-30 days: S3 Standard ($0.023/GB/month) → active queries

Data age 30-90 days: S3 Infrequent Access ($0.0125/GB/month) → occasional access

Data age 90+ days: S3 Glacier ($0.004/GB/month) → archive, rare access

For a data lake growing at 5 TB/month:

Without lifecycle: 60 TB × $0.023 = $1,380/month after 1 year

With lifecycle: ~$600/month (older data in cheaper tiers)

Strategy 5: Incremental Processing over Full Recompute

Processing only changed data (covered in Section 2) is the single largest cost optimization. A daily pipeline that incrementally processes 5 GB of new data instead of reprocessing 500 GB of total data reduces compute cost by ~99%.

SLIDE 33: Worked Example: Daily Revenue Aggregation from 500GB of Transactions

This worked example walks through a complete batch processing pipeline, applying the patterns from this section. It demonstrates the kind of end-to-end reasoning that interviewers expect in a batch processing question.

Requirements:

E-commerce platform with 50 million transactions per day
Each transaction: ~10 KB (order_id, product_id, customer_id, amount, tax, discount, timestamp, shipping_address, payment_method)
Daily volume: 50M × 10 KB = 500 GB
Output: daily revenue aggregated by product category, region, and payment method for business dashboards
SLA: aggregated data available in the warehouse by 6 AM daily
Product dimension table: 2 million products, 500 MB total

Step 1: Ingestion (covered in Section 2)

Incremental extraction from the transactional database via CDC. Raw transaction events land in the data lake Bronze layer as Parquet files, partitioned by date.

CODE

s3://data-lake/bronze/transactions/date=2024-01-15/
  part-00000.parquet (250 MB)
  part-00001.parquet (250 MB)
  Total: ~500 GB across ~2,000 files of 250 MB each

Step 2: Processing (Spark Job)

CODE

from pyspark.sql.functions import broadcast, sum, count, col

# Read the day's transactions (partition pruning: reads only date=2024-01-15)
transactions = spark.read.parquet("s3://data-lake/bronze/transactions/") \
    .filter(col("date") == execution_date)

# Read the product dimension table (500 MB, fits in memory for broadcast)
products = spark.read.parquet("s3://data-lake/dimensions/products/")

# Join transactions with products using broadcast join (no shuffle of 500 GB)
enriched = transactions.join(broadcast(products), "product_id")

# Aggregate revenue by category, region, and payment method
daily_revenue = enriched.groupBy("date", "category", "region", "payment_method") \
    .agg(
        sum("amount").alias("gross_revenue"),
        sum("discount").alias("total_discounts"),
        sum("tax").alias("total_tax"),
        (sum("amount") - sum("discount")).alias("net_revenue"),
        count("order_id").alias("order_count")
    )

# Coalesce to produce appropriately sized output files (not thousands of small files)
daily_revenue.coalesce(4) \
    .write.mode("overwrite") \
    .partitionBy("date") \
    .parquet("s3://data-lake/gold/daily_revenue/")

Step 3: Performance Analysis

Structure & Concept Flow

Input: 500 GB (2,000 Parquet files, ~250 MB each)

Cluster: 50 executors × 4 cores = 200 concurrent tasks

Partition count: 2,000 input partitions → 10 waves of 200 tasks

Stage 1 (read + filter + broadcast join):

Broadcast: 500 MB product table sent to all 50 executors (~2 seconds)

Read + join: each task processes ~250 MB, joins with local product copy

Time: ~5 minutes (I/O bound, reading 500 GB from S3)

Shuffle: ZERO (broadcast join eliminates the 500 GB shuffle)

Stage 2 (groupBy aggregation):

Shuffle volume: aggregation output is small (~10 MB, only unique combinations

of category × region × payment_method × date)

Time: ~1 minute

Output: 4 Parquet files, ~2-5 MB total (aggregated data is much smaller than input)

Total runtime: ~6-8 minutes

Cost (50 spot instances × $0.12/hour × 0.15 hours): ~$0.90 per daily run

Monthly cost: ~$27

Step 4: Load to Warehouse

The Gold layer Parquet files are loaded into Snowflake for dashboard queries:

CODE

COPY INTO analytics.daily_revenue
FROM @s3_stage/gold/daily_revenue/date=2024-01-15/
FILE_FORMAT = (TYPE = 'PARQUET')
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Step 5: Orchestration (Airflow)

Structure & Concept Flow

DAG: daily_revenue_pipeline

Schedule: 2:00 AM daily

Tasks:

wait_for_data (sensor) → checks Bronze partition exists for execution_date

validate_input → row count check, null rate check on key columns

run_spark_job → the Spark job above

validate_output → verify output file count and row count

load_to_warehouse → COPY INTO Snowflake

notify_success → Slack notification to analytics team

Key Design Decisions Summarized:

The product join uses broadcast (500 MB dimension table avoids 500 GB shuffle). Parquet provides columnar compression (500 GB raw compresses to ~100 GB on disk). Date partitioning enables partition pruning for downstream queries. Coalesce before write prevents small file proliferation. Partition overwrite makes the job idempotent. Spot instances reduce compute cost by ~70%.

SLIDE 34: Wrong vs Right: Batch Processing Anti-Patterns

Anti-Pattern	What Goes Wrong	Correct Approach
No storage partitioning	Every query scans the entire table. A query for one day’s data reads years of history. At 1 TB/day, a year is 365 TB scanned for a single-day query.	Partition by date at minimum. Most analytical queries filter by date, and partition pruning eliminates 99%+ of unnecessary reads.
Small files everywhere	Thousands of tiny files (< 10 MB each) from streaming ingestion or over-partitioned writes. Metadata overhead and scheduling overhead dominate read performance.	Compact files to 200-500 MB targets. Use coalesce before writing Spark output. Use Delta Lake OPTIMIZE or Iceberg compaction.
Full table scans for incremental data	Reprocessing the entire history when only today’s data is new. A daily pipeline that reads 500 TB to process 1 TB of new data.	Read only the new partition. Parameterize by execution_date. Read from `date=2024-01-15` only, not the entire table.
Default joins on large tables	Joining two large tables without considering broadcast. Spark shuffles both sides across the network, creating a bottleneck that dwarfs all other stages.	Use broadcast join when one side is < 500 MB. Most fact-to-dimension joins qualify. Explicitly call `broadcast()` rather than relying on Spark’s auto-broadcast threshold.
No idempotency in write operations	Using INSERT/append mode, so retries create duplicate rows. Revenue appears 2x or 3x the actual value.	Use partition overwrite or upsert. Every write operation must be safe to rerun.
Writing output with too many partitions	Spark job with 2,000 partitions writes 2,000 files. Downstream readers inherit the small file problem.	Coalesce to an appropriate number of output files before writing. Target 200-500 MB per file.
Ignoring data skew	One partition has 100x more data than others (e.g., most orders from one country). One task takes 2 hours while 999 tasks finish in 2 minutes. The entire job waits for the skewed task.	Identify skewed keys. Apply salting (add a random suffix to the key, aggregate in two passes) or repartition to distribute the skewed key across multiple tasks.
Over-provisioning clusters	Running a 200-node cluster 24/7 for jobs that need 50 nodes for 2 hours. Monthly cost: $58,000 instead of$ 1,200.	Right-size clusters to the workload. Use auto-scaling. Terminate clusters after jobs complete. Use spot instances for batch workers.

SLIDE 35: Interview Practice and Common Mistakes

Common Interview Mistakes in Batch Processing:

Mistake	Why It’s Wrong	Stronger Approach
“Spark can handle it” without sizing	No reasoning about partition count, cluster size, or expected runtime. The interviewer cannot evaluate whether the solution is feasible.	Size the problem first: “500 GB input with 200 MB target partitions gives 2,500 partitions. On a 50-executor cluster with 4 cores each, that is about 13 waves, completing in roughly 15 minutes.”
No mention of shuffle	Proposing joins and aggregations without discussing the most expensive operation.	Identify which operations trigger shuffle and how to minimize it: “The product dimension is 500 MB, so I’ll broadcast it to avoid shuffling the 500 GB fact table.”
“We’ll use Parquet because it’s fast”	No explanation of why Parquet is fast for this workload.	“Parquet is columnar, so our aggregation query reads only the 3 columns it needs (amount, category, region) instead of all 15 columns. Combined with Snappy compression, we read roughly 20% of the raw data volume.”
Designing a pipeline that cannot be backfilled	No parameterization by date. The pipeline processes “whatever is new” rather than a specific date range.	“The pipeline accepts execution_date as a parameter and processes exactly one day’s partition. Backfilling last month means rerunning the DAG for 30 dates, each independently.”
No cost discussion	Proposing a cluster without mentioning spot instances, right-sizing, or data lifecycle.	“Batch workers run on spot instances for 70% savings. The cluster scales to 50 nodes during the 2-hour processing window and terminates after. Raw data older than 90 days transitions to Glacier.”

Practice Questions:

“You have 2 TB of daily clickstream data. Design a batch pipeline that produces hourly session metrics (session count, avg session duration, pages per session) for a dashboard. Walk me through the Spark job.” (Key patterns: partitioning by date/hour, session windowing logic, broadcast join for user dimension, coalesce output)
“Your daily Spark job processes 1 TB and takes 3 hours. The SLA is 2 hours. How do you optimize it?” (Key patterns: identify shuffle as bottleneck, broadcast joins, increase parallelism, check for skew, right-size partitions)
“Explain how you would handle a situation where your data lake has accumulated millions of small files over 6 months of streaming ingestion.” (Key patterns: compaction job, Delta Lake OPTIMIZE, file size targets, prevention via coalesce)

SLIDE 36: Section Cheat Sheet

Concepts Covered:

Concept	Key Takeaway
When to use batch	Default when latency tolerance is minutes or longer. Cheaper, simpler, and more efficient for large-volume scheduled workloads.
MapReduce model	Map (parallel, no coordination) → Shuffle (redistribute by key, expensive) → Reduce (aggregate). Every distributed framework uses this model internally.
Spark architecture	Driver coordinates, executors process. Lazy evaluation builds a DAG, actions trigger execution. Catalyst optimizer handles predicate pushdown, join reordering, and projection pruning automatically.
Spark partitioning	Target 128-256 MB per partition. Too few = poor parallelism. Too many = scheduling overhead. Use coalesce to reduce, repartition to increase or redistribute.
Shuffle minimization	Broadcast joins for small tables (< 500 MB), pre-partitioning by join key, map-side aggregation, avoiding unnecessary distinct/orderBy.
Storage partitioning	Partition by date (most common). Enables partition pruning for 10-100x query speedup. Avoid over-partitioning (too many columns = small files).
Small file problem	Files < 128 MB cause metadata overhead and scheduling overhead. Fix with compaction, coalesce before write, or table format compaction (Delta OPTIMIZE).
File formats	Parquet for analytics (columnar, compressed, fast reads). Avro for streaming/interchange (row-based, strong schema evolution).
Cost optimization	Spot instances (60-90% savings), right-sized clusters, off-peak scheduling, data lifecycle policies, incremental processing.

Common Interview Mistakes:

Mistake	Stronger Approach
No problem sizing	Quantify input volume, partition count, cluster size, and expected runtime before proposing a solution.
Ignoring shuffle	Identify shuffle-triggering operations and state how you minimize them (broadcast, pre-partition, combine).
“Parquet because it’s best”	Explain why: columnar access, compression ratio, column pruning for analytical queries.
No backfill capability	Parameterize by execution_date, use partition overwrite, preserve raw data.
No cost awareness	Mention spot instances, cluster right-sizing, and data lifecycle as standard practice.

Key Numbers to Remember:

Structure & Concept Flow

Partition Sizing:

Target partition size: 128-256 MB

Small file threshold: < 128 MB (causes overhead)

Broadcast join threshold: < 200-500 MB (fits in executor memory)

File Format Compression (approximate):

Raw JSON/CSV: 1x (baseline)

Parquet (Snappy): 5-8x compression

Parquet (GZIP): 8-12x compression (slower write)

Avro (Snappy): 3-5x compression

Spark Cluster Sizing:

50 executors × 4 cores: 200 concurrent tasks

100 GB input / 200 MB target: 500 partitions → 3 waves on 200 cores

500 GB input / 200 MB target: 2,500 partitions → 13 waves on 200 cores

Cost Benchmarks:

Spot vs on-demand savings: 60-90%

S3 Standard: $0.023/GB/month

S3 Glacier: $0.004/GB/month

Spark cluster (50 spot nodes): ~$6/hour

Daily 500 GB pipeline: ~$1-2/run on spot instances

How This Connects to Other Sections:

This Section’s Concept	Connects To
MapReduce and Spark execution model	Section 4 (Stream Processing) covers the streaming counterpart: Flink and Kafka Streams, which process one record at a time rather than in bulk.
File formats (Parquet, Avro)	Section 7 (Storage Layer) goes deeper into storage decisions: warehouse vs lake vs lakehouse, and how file formats fit into the medallion architecture.
Partition pruning and storage organization	Section 7 (Storage Layer) covers partitioning and bucketing in the context of the full storage layer, including Delta Lake, Iceberg, and Hudi.
Cost optimization (spot, lifecycle)	Section 12 (Pattern Selection) consolidates cost optimization principles across batch, streaming, and storage.
Broadcast joins and shuffle	Section 5 (Batch vs Streaming) uses shuffle cost as one factor in the batch vs streaming decision framework.
Worked example (daily revenue)	Section 11 (Full Case Studies) integrates batch processing into two complete architectures, showing how the Spark job fits into the end-to-end pipeline with orchestration, quality checks, and monitoring.
Backfill and idempotency	Section 10 (Orchestration) covers how Airflow manages backfill operations: parameterized DAGs, date-range reruns, and partial DAG retries.