What optimization techniques have you implemented? Can you explain the architecture of Spark, why we need Spark, and why we can't do it in SQL?

Spark is a distributed data processing engine that excels at large‑scale, iterative workloads. Its architecture consists of a driver program that builds a Directed Acyclic Graph (DAG) of stages, a cluster manager (YARN, Mesos, or Kubernetes) that allocates resources, and executors that run tasks on worker nodes. The DAG scheduler optimizes the execution plan, while the task scheduler assigns tasks to executors. Spark’s in‑memory storage (RDDs, DataFrames, Datasets) allows fast data reuse across stages, and the Tungsten engine optimizes memory usage and code generation.

Optimization techniques I’ve used include:

1. Partitioning & Bucketing – Align data partitions with join keys to reduce shuffle.
2. Broadcast joins – For small lookup tables, broadcasting avoids shuffling large datasets.
3. Cache & Persist – Persist intermediate results in memory or SSD to avoid recomputation.
4. Predicate push‑down – Filter data early in the pipeline to reduce I/O.
5. Tungsten & Whole‑Stage Code Generation – Enable efficient CPU usage.
6. Adaptive Query Execution (AQE) – Dynamically adjust join strategies based on runtime statistics.

Why Spark? Traditional SQL databases are single‑node or limited‑scale and excel at transactional workloads, but they lack the distributed, in‑memory, fault‑tolerant capabilities needed for big data analytics, iterative machine learning, and real‑time streaming. SQL engines can be extended with distributed query engines (e.g., Presto, Hive), but Spark provides a unified API for batch, streaming, and ML workloads, making it a versatile choice for modern data pipelines.