If a Spark query on Parquet is slow, what optimisation steps would you take?

Diagnosing and tuning slow Parquet queries in Spark follows a layered approach:

1. Check partitioning — ensure the table is partitioned on high-cardinality filter columns (e.g., date, region). Without partitions, Spark scans all files.

   df.write.partitionBy("date", "region").parquet("s3://path/")
   

2. Verify column pruning — use SELECT col1, col2 rather than SELECT * so only required column chunks are read.
3. Align filters with statistics — filter on columns that have good min/max spread so row group skipping is effective. Sort data by the filter column before writing (Z-ordering or file-level sorting).
4. Right-size row groups — too-small row groups → many footer reads; too-large → coarse skipping. Target 128 MB–512 MB per row group.
5. Check compression codec — Snappy (fast decompression) suits CPU-bound queries; ZSTD suits I/O-bound scenarios.
6. Enable Adaptive Query Execution (AQE) — spark.sql.adaptive.enabled=true reoptimises joins and coalesces small partitions at runtime.
7. Inspect the Spark UI — look at the scan stage: bytes read vs. bytes skipped tells you how effective pushdown is.