Can you describe a project where you developed scalable ETL pipelines using Databricks and PySpark? What challenges did you face and how did you optimize the performance of your PySpark job?

I built a real‑time ETL pipeline for a financial services client that ingested streaming market data, enriched it with reference tables, and wrote the results to a Delta Lake for downstream analytics. The key challenges were handling high ingestion rates (≈1 million rows per second), ensuring low latency (<5 seconds), and scaling the cluster cost‑effectively.

I addressed these by using Structured Streaming with micro‑batch intervals of 2 seconds, enabling spark.sql.streaming.autoAdjustPartitions to let Spark auto‑tune the number of partitions based on input rate. I also used Delta Lake’s ACID guarantees to write to a single table without merge conflicts. For enrichment, I cached the reference tables in memory and used broadcast joins where appropriate.

Performance tuning involved adjusting spark.executor.memory, spark.executor.cores, and spark.dynamicAllocation.enabled to match the workload. I monitored the cluster via the Databricks Jobs UI and used the spark.sql.adaptive.enabled flag to let Spark merge small shuffle files. The result was a 60% reduction in end‑to‑end latency and a 40% cost savings compared to the baseline.