Reviewed by Aditya Kumar · Last reviewed 2026-08-08
I optimized a nightly Spark ETL job that consistently exceeded its 2 hour SLA, running for over 6 hours. By profiling the execution and identifying key bottlenecks, I reduced its runtime to 1.5 hours,…
Pro-Move: 'Spark UI showed 1 partition at 4hr—salted the key, added pruning; 6hr to 90min; documented in runbook.' Red Flag: We added more resources—optimization, not scaling.
This medium-level System Design/Architecture question appears frequently in data engineering interviews at companies like Swiggy. While less common, it tests deeper understanding that distinguishes strong candidates. Mastering the underlying concepts (join, partition) will help you answer variations of this question confidently.
Break this problem into components. Identify the core trade-offs involved, then walk the interviewer through your reasoning step by step. Demonstrate awareness of edge cases and production considerations - this is what separates good answers from great ones. The expert answer includes a code example that demonstrates the implementation pattern.
I optimized a nightly Spark ETL job that consistently exceeded its 2-hour SLA, running for over 6 hours. By profiling the execution and identifying key bottlenecks, I reduced its runtime to 1.5 hours, achieving a 4x improvement.
The primary issues identified through Spark UI and query plan analysis were inefficient full table scans and severe data skew during joins.
event_date, was undergoing a full scan daily because the query lacked a WHERE clause on the partition key. This forced Spark to read terabytes of unnecessary historical data. By adding WHERE event_date = current_date, Spark (and similar systems like Snowflake with micro-partitions) could leverage partition pruning, significantly reducing I/O by only scanning relevant data. -- Optimized query leveraging partition pruning
SELECT ft.id, ft.value, dt.name
FROM fact_table ft
JOIN dim_table dt ON ft.dim_id = dt.id
WHERE ft.event_date = current_date;
BROADCAST join, the dimension table was copied to all Spark executors, allowing for local, in-memory joins without data movement across the network. This is highly efficient for smaller lookup tables.user_id where a few users generated disproportionately more data) led to a few tasks becoming bottlenecks. To address this, I implemented salting. This involved adding a random suffix (e.g., _0 to _N) to the skewed key in both the fact and dimension tables, effectively breaking up the hot partition into multiple, smaller, more manageable partitions. This distributed the workload across more executors, mitigating the hotspot.While effective, these techniques have trade-offs. Broadcast joins consume executor memory, so they are only suitable for small tables. Salting increases the data volume and complicates the join condition, requiring careful implementation. The choice of optimization depends heavily on data characteristics, cluster resources, and the specific query patterns.
In the interview, also mention the importance of establishing baselines, continuous monitoring, and an iterative approach to optimization.
Pro-Move: 'Spark UI showed 1 partition at 4hr—salted the key, added pruning; 6hr to 90min; documented in runbook.' Red Flag: We added more resources—optimization, not scaling.
Some links below are affiliate links. If you buy through them we may earn a small commission at no extra cost to you — it helps keep DataEngPrep free.
According to DataEngPrep.tech, this is one of the most frequently asked System Design/Architecture interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.