Can you describe a situation where you analyzed and identified bottlenecks in PySpark jobs, and what steps you took to improve their performance?

In a recent ETL pipeline, the job was taking 45 minutes to process 5 TB of data. I started by inspecting the Spark UI: the stages with the longest duration were shuffle read/write and GC pauses. I found that the shuffle partitions were 2000, each about 1 GB, causing excessive disk I/O. I reduced the number of partitions to 200 using repartition(200) and added spark.sql.shuffle.partitions=200 to the config. Next, I noticed a data skew on a key used in a join; the skewed key caused one executor to process 80% of the data. I applied a salting technique to split the skewed key into multiple buckets, then performed a secondary join to merge the results. I also enabled caching for a frequently reused lookup table, which eliminated a full scan. After these changes, the job runtime dropped to 12 minutes, and GC overhead fell below 5%. The key steps were profiling, partition tuning, skew mitigation, and selective caching.