Top 8 Batch Processing and Pipeline Optimization Interview Questions

Batch processing remains central to data engineering, even in organizations that also use streaming. Interviews often test how you balance throughput, cost, freshness, reliability, and recovery when workloads process millions or billions of records.

Interview framework: Explain the workload and SLA first, then cover partitioning, parallelism, data skew, idempotency, observability, and recovery. For performance questions, identify the bottleneck before proposing a fix.

1. When would you choose batch processing over streaming?

Batch processing handles a bounded collection of data on a schedule or when a defined amount of data is available. Streaming continuously processes events as they arrive, often with lower latency but more complex state and operational requirements.

Factor	Batch	Streaming
Latency	Minutes to hours, depending on schedule and duration	Often seconds or milliseconds when designed for it
Operational model	Bounded runs, retries, and backfills	Long-running applications, state, offsets, and continuous monitoring
Good fit	Daily finance reports, historical transformations, periodic ML feature builds	Fraud detection, live monitoring, event-driven applications
Trade-off	Data is not available until the next run completes	More complex handling of late events, state, replay, and deployment

Choose batch when hourly or daily freshness is acceptable, workloads benefit from large scans or joins, and simple recovery and cost control matter. Choose streaming when the business action depends on very fresh data.

Senior-level answer: This is not always an either-or decision. A platform may stream operational events for alerting and run batch jobs for reconciliation, historical reporting, and model training.

2. How would you design a daily pipeline processing billions of records?

Start with volume, data shape, processing window, target freshness, and source constraints. Then build a distributed pipeline that can scale horizontally, recover by partition, and prove that the output is complete.

1. Ingest incrementally where possible: Prefer CDC, change timestamps, or source partitions over rescanning all history every day.
2. Partition the workload: Split work by date, key range, tenant, or another balanced unit. Avoid tiny partitions and highly skewed keys.
3. Use distributed compute: Choose an engine such as Spark, Beam/Dataflow, or a warehouse-native transformation based on the workload and operational needs.
4. Optimize data layout: Use columnar formats, appropriate compression, partition pruning, and clustering or sorting when supported and useful.
5. Control shuffles: Project only needed columns, filter early, avoid unnecessary wide transformations, and address skewed joins.
6. Write safely: Use idempotent merges, partition replacement, or staged output with a controlled publish step.
7. Validate and monitor: Track records read/written, throughput, task retries, skew, output counts, data quality, cost, and end-to-end duration.
8. Plan recovery: Checkpoint or track completed partitions so failed work can be retried without reprocessing everything.

discover input partitions | v incremental extraction / raw landing | v filter + validate + normalize | v distributed joins and aggregations | v write staged output by target partition | v reconcile + publish + record completion

Capacity planning: Estimate input bytes, expansion during shuffle, compression ratio, output size, and processing throughput. Benchmark representative data before committing to a cluster size or SLA.

Avoid this mistake: “Add more workers” is not a complete answer. More compute will not fix poor partitioning, data skew, excessive shuffles, tiny files, source bottlenecks, or inefficient SQL.

3. How do you make a batch pipeline safe to rerun?

Make the pipeline idempotent: rerunning the same logical input should produce the same correct target state rather than duplicate or corrupt results.

• Use deterministic input boundaries: Capture the exact date range, source offset, or batch ID for each run.
• Use stable keys: Deduplicate by business key and apply deterministic rules when multiple versions exist.
• Choose safe write semantics: Use MERGE/upsert, replace a complete target partition, or write to staging and publish after validation.
• Track run metadata: Store run ID, input range, code version, row counts, status, and completion time.
• Advance checkpoints safely: Update progress only after target output is durable and validated.
• Handle concurrency: Prevent overlapping runs from writing the same partition unless the design explicitly supports it.

Illustrative SQL pattern

-- Replace one complete daily partition only after validation. BEGIN TRANSACTION; DELETE FROM analytics.daily_orders WHERE order_date = DATE '2026-10-11'; INSERT INTO analytics.daily_orders SELECT * FROM staging.daily_orders WHERE order_date = DATE '2026-10-11'; COMMIT;

This transaction syntax is illustrative; support varies by platform. If atomic multi-statement transactions are unavailable, use a staging table and a platform-supported atomic publish or partition-swap operation.

Key principle: Assume a job may fail after writing data but before reporting success. Design the next run to determine what is already committed and safely complete the work.

4. How would you handle a job that consistently misses its SLA?

First establish where the time is spent. Compare actual run duration with the SLA, inspect stage-level metrics, and separate data-volume growth from infrastructure or code regressions.

1. Measure the pipeline: Break down source extraction, queueing, transformation, shuffle, writes, validation, and downstream dependencies.
2. Identify the bottleneck: Check CPU, memory, disk and network I/O, task retries, skew, worker utilization, and source throttling.
3. Optimize the algorithm: Reduce repeated scans, unnecessary joins, wide transformations, and redundant computation.
4. Improve data layout: Filter by partitions, read only needed columns, compact small files, and tune clustering where applicable.
5. Scale deliberately: Add parallelism or compute only after verifying that the bottleneck can use it.
6. Review scheduling: Remove dependency bottlenecks, avoid resource contention, and start upstream work as early as possible.
7. Protect the SLA: Set alerts before the deadline, define a recovery plan, and communicate the impact if the SLA is at risk.

Example: If a Spark job spends most of its time in a join with one highly skewed customer key, increasing the worker count may have limited benefit. Salting the skewed key, pre-aggregating data, or changing the join strategy may help more.

Interview tip: State a baseline, identify the measured bottleneck, apply one change at a time, and compare runtime, correctness, and cost after the change.

5. What causes the small-file problem in a data lake?

The small-file problem occurs when a dataset contains many tiny files rather than fewer reasonably sized files. Query engines and distributed storage systems may spend disproportionate time listing files, reading metadata, scheduling tasks, and opening files instead of processing data.

Common causes:

• Frequent micro-batches or streaming writes that create a file for each small batch.
• Over-partitioning by high-cardinality columns such as user ID or transaction ID.
• Many parallel writers producing tiny output files.
• Repeated incremental updates without compaction or optimized file layout.
• Overly frequent jobs that produce little data per run.

How to fix it:

• Compact small files into larger files using platform-appropriate tools.
• Choose partition columns with useful pruning value and manageable cardinality.
• Tune the number of output partitions and writers.
• Use table-format optimization features where available and schedule maintenance based on measured need.
• Balance file size with parallelism; a single huge file can also limit performance.

Important: There is no universal ideal file size. The right target depends on the storage format, query engine, workload, compression, and platform recommendations.

6. How would you optimize a slow batch pipeline?

Use metrics and execution plans to identify the expensive stage, then optimize the largest source of wasted work first.

Optimization checklist:

• Read less data: Apply partition pruning, predicate pushdown, and column projection.
• Process less history: Use incremental transformations instead of rebuilding all data when correctness allows.
• Reduce shuffle: Filter and pre-aggregate before joins, select appropriate join strategies, and avoid unnecessary repartitioning.
• Handle skew: Inspect partition-level input sizes and task duration; use skew-aware joins or rebalance keys where appropriate.
• Fix file layout: Compact tiny files, choose appropriate output partition counts, and avoid creating too many partitions.
• Cache selectively: Cache only reused intermediate data when the saved computation outweighs memory and serialization costs.
• Tune resources: Adjust workers, memory, parallelism, and autoscaling based on evidence, not guesswork.
• Check output writes: Look for too many small files, slow commits, and expensive merge or compaction operations.

Baseline -> inspect execution plan and stage metrics -> identify dominant bottleneck -> change one factor -> rerun representative input -> compare correctness, runtime, and cost -> keep or revert the change

Example: A warehouse query scans two years of orders to calculate yesterday's revenue. If the table is partitioned by order date, filtering to the required date range and using incremental aggregates may reduce scan cost dramatically—provided late-arriving updates are still handled correctly.

7. How do you process late-arriving data?

Late-arriving data is data that becomes available after the processing window or event time it belongs to. Examples include delayed source uploads, offline devices, late CDC events, and records arriving after a daily report has already run.

Common strategies:

• Use event time and ingestion time separately: Event time represents when the event occurred; ingestion time records when the platform received it.
• Define a lateness window: Reprocess a bounded recent range, such as the previous few days, if the business can tolerate that policy.
• Use idempotent merges: Update the existing business key rather than appending a second copy.
• Recompute affected partitions: Refresh only the date or entity partitions affected by late records.
• Handle very late data: Route it to a correction workflow or targeted backfill and notify downstream consumers if published metrics change.
• Track completeness: Publish freshness and completeness indicators so consumers understand whether a reporting period is final.

Example: A transaction for October 3 arrives on October 6. The pipeline can merge the transaction into the October 3 partition and recompute affected daily revenue rather than treating it as an October 6 transaction.

Key trade-off: A larger lateness window improves completeness but costs more to recompute. Choose it based on observed arrival patterns and business tolerance, and define how records later than the window are handled.

8. How would you design a backfill without disrupting current production workloads?

A backfill should be isolated, resumable, observable, and safe to run alongside production. Its resource use and write behavior must be controlled so it does not delay critical workloads or overwrite newer data.

1. Scope the work: Define affected date ranges, tables, dependencies, expected volume, and correctness requirements.
2. Chunk the workload: Process by partition, date range, or key range with a manifest tracking completion and errors.
3. Limit resource use: Use a separate queue or cluster, lower priority, concurrency caps, and rate limits for source systems.
4. Use deterministic writes: Replace complete partitions, write to a shadow target, or merge with version-aware rules.
5. Coordinate with live ingestion: Define which data wins when backfill and current jobs touch the same keys or partitions.
6. Validate each chunk: Check counts, distinct keys, aggregates, and business rules before marking it complete.
7. Make it resumable: Retry only failed chunks and record code version, input range, output version, and run status.
8. Publish safely: Use a controlled cutover or partition-level publication after reconciliation when risk warrants it.

for partition in backfill_manifest.pending_partitions(): acquire_limited_capacity() output = recompute(partition) validate(output) write_to_staging(partition, output) reconcile(partition) publish_partition_atomically(partition) mark_complete(partition)

Important: Never assume the historical result is newer than production data just because the backfill runs later. Use source versions, event timestamps with suitable tie-breakers, or an explicit precedence policy to prevent stale backfill output from overwriting newer records.

Quick Revision Table

Topic	Core takeaway
Batch vs. streaming	Choose based on freshness requirements, complexity, and workload economics.
Billions of records	Use incremental reads, balanced partitions, distributed processing, and measurable recovery.
Safe reruns	Use deterministic boundaries and idempotent target writes.
Missed SLA	Measure the bottleneck before changing code or compute.
Small files	Reduce metadata and scheduling overhead through compaction and sensible partitioning.
Slow pipeline	Read less, shuffle less, address skew, and benchmark changes.
Late-arriving data	Separate event time from arrival time and correct affected partitions safely.
Backfills	Chunk, throttle, validate, track progress, and protect live writes.

Scenario-Based Practice Questions

Try answering these scenarios out loud before checking the suggested approach.

Scenario 1: A daily job finishes in 5 hours, but the SLA is 3 hours. What is your first move?

Break down runtime by stage and compare it with a previous healthy run. Inspect input growth, query plans, shuffle volume, task skew, I/O, retries, and queueing. Optimize the dominant bottleneck, benchmark the result, and add early-warning alerts. Do not start by blindly doubling compute.

Scenario 2: A backfill and today's production run write the same date partition.

Define write ownership and precedence, isolate the backfill, and avoid overlapping writes unless the storage system supports safe concurrency. Use a staging or shadow target, version-aware merges, and a controlled partition publish after validation. Ensure older historical output cannot overwrite newer production data.

Scenario 3: Your lake has millions of tiny Parquet files. How would you fix it?

Inspect file-size distribution, partition cardinality, writer parallelism, and batch frequency. Compact files into appropriately sized outputs, reduce over-partitioning, tune output partition counts, and adjust micro-batch behavior. Measure query planning time, scan throughput, and maintenance cost after compaction.

Scenario 4: Late records change last week's reported revenue.

Track event time and ingestion time separately, identify the affected business date, and merge records by stable keys. Recompute the affected partition or aggregate, reconcile against source totals, and communicate whether the period is provisional or finalized. Define a correction policy for records that arrive beyond the normal lateness window.

Chapter Summary

• Choose batch or streaming based on business latency, workload, and operational trade-offs.
• Scale large batch jobs through incremental ingestion, balanced partitioning, and efficient distributed processing.
• Make reruns safe with deterministic input boundaries and idempotent writes.
• Diagnose SLA misses using stage-level metrics before tuning resources.
• Control small files with compaction, appropriate partitioning, and sensible writer parallelism.
• Optimize the largest measured bottleneck, not every stage at once.
• Handle late data with event-time-aware corrections and clear completeness policies.
• Protect production during backfills with isolation, throttling, resumability, and safe publication.

Practice Challenge

Design a daily batch pipeline that processes several billion events into a cloud data lake and warehouse. Explain your ingestion strategy, partitioning, compute engine, file layout, idempotency, SLA monitoring, late-data policy, and how you would backfill three months while production continues.

Suggested Blogger post title: Top 8 Batch Processing Interview Questions for Data Engineers | Performance and Reliability