Spark SQL, Catalyst & Tuning

DataFrame, SQL, and Catalyst

Spark SQL unifies:

• DataFrame API (Python/Scala)
• SQL strings
• Delta/Iceberg table reads

All compile through Catalyst — rule-based optimizer:

• Predicate pushdown to Parquet
• Column pruning
• Constant folding
• Join reordering

df.explain(mode="cost") / EXPLAIN COST shows the physical plan — senior skill.

Key optimizer features (Spark 3+)

Feature	What it does
AQE	Reoptimize mid-query using runtime stats
Dynamic partition pruning	Skip partitions in join when dimension filters
Bucket join	Skip shuffle if tables bucketed on join key
Z-order (Delta)	Colocate related rows for data skipping

Tuning checklist

1. Filter early — push where before join
2. Partition alignment — write and read on same dt column
3. File compaction — OPTIMIZE / REPARTITION bronze before heavy reads
4. Shuffle partitions — default 200 is often wrong; aim for 2–4× total cores
5. Avoid UDFs — JVM serialization overhead; prefer built-in functions or pandas UDFs only when needed
6. Don't collect — never collect() on large data
7. Broadcast small dims explicitly if optimizer misses it
8. Materialize intermediate results at layer boundaries (bronze/silver)

Spark vs warehouse SQL

Spark	BigQuery/Snowflake
You manage cluster	Managed compute
Great for complex ETL	Great for analyst SQL
Lake files direct	Warehouse storage

Many teams: Spark for ETL, warehouse for serving — dbt bridges the gap (Topic 6).

Interview answer template

"I'd rewrite the UDF as native Spark SQL for Catalyst optimization, enable AQE, set spark.sql.shuffle.partitions based on input size (~128MB per partition), and materialize the silver table so downstream dbt models don't recompute the join."