Suppose you're given a dataset with 100 million rows and you need to perform a complex query that involves aggregating data from multiple tables. How would you optimize the query for better performance using Apache Spark's advanced features like GraphX and MLlib?

To optimize a 100‑million‑row aggregation in Spark, start by ensuring data is stored in a columnar format such as Parquet with compression to reduce I/O. Use partitioning on high‑selectivity columns (e.g., date or region) so that only relevant partitions are scanned. Cache or persist intermediate results that are reused, especially after expensive joins or aggregations. For joins, broadcast the smaller table if it fits in memory; otherwise, use shuffle hash or sort‑merge joins with proper partitioning to minimize data movement. Leverage Spark’s Catalyst optimizer by writing queries in DataFrame/Dataset API rather than raw SQL, enabling predicate pushdown and column pruning. Adjust the number of shuffle partitions (spark.sql.shuffle.partitions) to match the cluster size and data volume. If the aggregation can be expressed as a graph problem, use GraphX to perform iterative computations efficiently, taking advantage of its in‑memory graph representation. For machine‑learning tasks, MLlib’s pipeline API can parallelize feature engineering and model training; use the built‑in feature hashing or vector assembler to avoid shuffling large feature sets. Finally, enable Tungsten’s whole‑stage code generation and vectorized execution for CPU‑bound operations, and monitor the job with Spark UI to identify bottlenecks. These steps collectively reduce shuffle, I/O, and CPU overhead, yielding significant performance gains.