Every Spark engineer eventually hits the same wall: the query works, but you don't know why it's slow, or why it suddenly got fast after a one-line config change. The gap between "I can write Spark SQL" and "I can reason about Spark's execution" is almost entirely a gap in understanding four subsystems — the Catalyst optimizer, Adaptive Query Execution, the shuffle mechanism, and Spark's memory model — plus, on Databricks specifically, how Photon layers on top of all of it. This post walks through each one, not as an API reference, but as a mental model you can use the next time a query profile looks wrong and you need to know where to look.
From SQL to Execution: The Catalyst Optimizer
When you submit a query — SQL, DataFrame API, or a CLUSTER BY DDL statement — none of it runs directly. It first becomes a tree of logical operations, and Catalyst's job is to turn that tree into something the cluster can actually execute efficiently. This happens in four phases.
Analysis resolves names. Every column reference, table reference, and function call in your query is unresolved at first — Catalyst doesn't know that f.customer_level refers to a real column until it looks up the schema of fact_profitability_actuals in the catalog. This is also where type coercion happens: if you compare a string column to an integer literal, analysis is where Catalyst decides how to cast one to match the other. A query that fails with AnalysisException failed here, before a single byte of data was touched.
Logical optimization is where the famous rule-based transformations live: predicate pushdown, constant folding, column pruning, null propagation. This is the phase that decides whether your WHERE fiscper >= ... filter gets pushed all the way down to the file scan (so files get skipped) or gets applied late (so everything gets read first). It's also where Catalyst can fold CONCAT(CAST(YEAR(CURRENT_DATE())...)) into a literal at plan time, which is why constant date-range expressions in a WHERE clause don't need to be manually pre-computed — Catalyst does it for you, once, before the physical plan is even generated.
Physical planning is where logical operations get mapped onto actual Spark execution strategies. A logical Join node becomes one of several physical join implementations — broadcast hash join, sort-merge join, shuffle hash join — and Catalyst picks based on cost estimates: table sizes, whether statistics are available, and configured thresholds like spark.sql.autoBroadcastJoinThreshold. This is the single most consequential decision Catalyst makes for a large query, and it's also the one most likely to go wrong when statistics are stale or missing, which is why ANALYZE TABLE and Delta's file-level stats matter more than most people realize.
Code generation is the last step: Spark doesn't interpret the physical plan row by row. It generates actual JVM bytecode (whole-stage code generation) for chains of operators, fusing them into a single tight loop rather than paying virtual-call overhead per row per operator. This is why a filter-then-project-then-aggregate chain in Spark can be competitive with hand-written Scala — the interpretation overhead is compiled away.
The practical upshot: when a query is slow, EXPLAIN FORMATTED isn't just diagnostic theater. It's the only way to see whether logical optimization pushed your filters where you think it did, whether physical planning chose the join strategy you expect, and — critically on Databricks — whether Photon accepted or rejected each operator, since Photon operators are annotated distinctly in the plan.
Aayush Sharma
