Filter on date still scans the whole lake
Symptoms
- Table not partitioned by date
- Or filter wrapped in a UDF
Interactive Spark interview questions on Catalyst optimizer. Practice with the matching lesson. Catalyst rewrites your query four times. Exchange in the physical plan is the shuffle you will pay for.
Question 1 of 8
What is Catalyst?
Answer it out loud, then reveal. Play steps through like the simulators.
Case 1 of 3 · symptoms
Symptoms
Indexed as FAQ. Open any item if you prefer a list to Play.
Showing 8 of 8 questions.
What is Catalyst?
Catalyst optimizer · tap to open the answer
Say this first: Spark SQL's optimizer: analysis → logical optimization → physical planning.
Why: It resolves names, pushes filters, reorders joins, then picks physical operators (broadcast vs SMJ). DataFrame/SQL get Catalyst; raw RDDs do not.
Common mistake: Catalyst as a storage format.
Follow-up they will ask: At which phase do unresolved attributes fail?
How do you read explain('formatted') in an interview?
Catalyst optimizer · tap to open the answer
Say this first: Bottom is the scan. Look for PushedFilters, PartitionFilters, Exchange, and the join type.
Why: If the filter isn't in PushedFilters, you're scanning extra files. Exchange means shuffle. BroadcastHashJoin vs SortMergeJoin is the money line.
Common mistake: Pasting explain() without pointing at an operator.
Follow-up they will ask: What does AdaptiveSparkPlan mean?
Catalyst picked SMJ. Stats were wrong. What's your move?
Catalyst optimizer · tap to open the answer
Say this first: ANALYZE TABLE / fix stats, add a broadcast hint if you know the size, or enable AQE join conversion.
Why: Don't hint forever as a substitute for stats. Hints rot. Measure with actual size after filters — AQE sees runtime sizes.
Senior depth: When you've measured the build side after filters and documented why stats cannot see it (UDF, JDBC, etc.).
Common mistake: Broadcast hint on a table that will grow 20×.
Follow-up they will ask: When is a hint the right senior answer?
You misspell a column name and the job fails in under a second with no tasks launched. Which part of Catalyst caught it?
Catalyst optimizer · tap to open the answer
Say this first: The analyzer, which resolves names and types against the catalog before anything is optimized or executed.
Remember these
Why: Your code first becomes an unresolved logical plan where column references are still strings. The analyzer looks each one up against the catalog's schema, binds data types, and resolves functions, so an unknown name dies here as an AnalysisException. Nothing has been optimized and no executor has been contacted, which is why it costs a second and no cluster time. Only after analysis succeeds does the optimizer apply its rewrite rules, and then the physical planner picks operators such as BroadcastHashJoin or SortMergeJoin. The practical lesson is to let Catalyst see the whole chain — breaking a pipeline into collected intermediate steps buys you the same error much later and much more expensively.
Common mistake: Expecting a typo to surface only when the action runs, because 'Spark is lazy'.
Follow-up they will ask: Which failures can analysis not catch?
Catalyst is described as both rule-based and cost-based. Which one actually does the work?
Catalyst optimizer · tap to open the answer
Say this first: Nearly all of it is rule-based rewriting; cost-based optimization is a narrower layer you have to feed with statistics.
Remember these
Why: Logical optimization is a fixed set of rules applied until the tree stops changing — predicate pushdown, column pruning, constant folding, collapsing projections, null propagation. None of those need statistics, because they are always-true rewrites. Cost-based optimization, gated by spark.sql.cbo.enabled, uses table and column statistics to reorder joins and choose build sides, and it is meaningless without ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS. Without those statistics Spark falls back to size estimates derived from file sizes, which is why a filtered table is so often badly mis-estimated. AQE is the third layer and the one most teams actually rely on, because it works from measured shuffle sizes rather than estimates.
ANALYZE TABLE silver.orders COMPUTE STATISTICS FOR COLUMNS customer_id, order_date;Common mistake: Claiming Catalyst 'picks the cheapest plan by cost', as though a cost model drove every decision.
Follow-up they will ask: Why are file-size-based estimates wrong after a filter?
Your filter appears as a separate Filter node above the FileScan instead of inside PushedFilters. Give me three reasons that happens.
Catalyst optimizer · tap to open the answer
Say this first: The predicate is opaque to the source, the source cannot evaluate that expression, or only part of it was pushed and the residual stays above.
Remember these
Why: First, the predicate may not be expressible at all: a UDF, a non-deterministic function, or an expression over something the source cannot see is unpushable, so Catalyst leaves it above the scan. Second, the data source accepts only a subset of filters — Parquet handles simple comparisons on primitive columns, and an implicit cast introduced by comparing a string column to a date can stop the match. Third, a filter can be partially pushed: Spark pushes what it can and keeps the residual above the scan, so a Filter node does not by itself prove pushdown failed. Read PushedFilters and PartitionFilters together, then check Input Size / Records on the scan, because that number is the only proof the pruning reduced I/O. PartitionFilters eliminate whole directories; PushedFilters only skip row groups inside files using Parquet footer statistics.
df.filter("order_date >= date'2024-01-01'").explain("formatted")
# read PartitionFilters, PushedFilters, and Input Size on the scanSenior depth: Because row-group skipping depends on physical layout. If the filtered column's values are spread evenly across every file, the min/max statistics in each footer overlap the predicate and no row group can be skipped. That is the argument for clustering or Z-ORDER on the predicate column: pushdown gives Spark permission to skip, layout is what makes skipping possible.
Common mistake: Concluding pushdown failed just because a Filter node is still present above the scan.
Follow-up they will ask: Why would a correct PushedFilter still not reduce bytes read?
A nine-table join planned left-deep in exactly the order you wrote it, and the intermediate results are enormous. What do you check?
Catalyst optimizer · tap to open the answer
Say this first: Whether statistics exist at all — without them Catalyst cannot reorder joins and keeps your written order.
Remember these
Why: Join reordering is a cost-based rule gated by spark.sql.cbo.enabled and spark.sql.cbo.joinReorder.enabled, and it only considers up to spark.sql.cbo.joinReorder.dp.threshold relations. All of that is useless without row counts and column statistics, so run ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS on the participating tables and re-plan. Then read explain("cost") and compare the estimated sizeInBytes and rowCount on each join node against reality. If the estimates are still wrong — common when a side is filtered through a UDF or read over JDBC — restructure the query so the most selective join runs first, or materialize the selective intermediate. Dropping a broadcast hint on every small side is the shortcut people reach for, and it works until one of those dimensions grows.
spark.conf.set("spark.sql.cbo.enabled", "true")
spark.conf.set("spark.sql.cbo.joinReorder.enabled", "true")
df.explain("cost")Senior depth: When the selectivity is something statistics can never express — a predicate over a UDF, a semi-join against a set computed at runtime, or a source that reports no stats. Then you encode the knowledge in the query: filter and materialize the selective side first so the optimizer is handed a small relation instead of being asked to guess one. Document why, because the next engineer will see a hand-ordered join and try to tidy it up.
Common mistake: Assuming Spark always reorders joins because 'the optimizer handles it'.
Follow-up they will ask: When is rewriting the query better than fixing the statistics?
The top of your explain output says AdaptiveSparkPlan isFinalPlan=false. What does that tell you about everything underneath it?
Catalyst optimizer · tap to open the answer
Say this first: That you are reading the pre-runtime guess, and the operators below may be replaced once real shuffle statistics arrive.
Remember these
Why: AdaptiveSparkPlan is the wrapper AQE puts at the root of a query. isFinalPlan=false means the query has not executed, so what you see is the physical plan Catalyst produced from estimates. Once a shuffle map stage completes, AQE reads its map output statistics and can coalesce partitions, convert a SortMergeJoin to a BroadcastHashJoin, or split skewed partitions, so the plan that actually ran may differ. To see the real plan, run the query and open the SQL / DataFrame tab, where the node reports isFinalPlan=true along with the changes AQE applied. The design consequence is that an explain() pasted into a design doc is a hypothesis, and a performance review should cite the final plan from the UI instead.
df.collect()
# then open the SQL / DataFrame tab: AdaptiveSparkPlan isFinalPlan=trueSenior depth: Capture it from the Spark event log or a query execution listener rather than from a notebook — the SQL tab is backed by events you can retain and query. For a platform, keep event logs, record the final plan per query, and alert when the join strategy changes; a flip from BroadcastHashJoin to SortMergeJoin is usually the earliest signal that a dimension outgrew its threshold.
Common mistake: Treating a notebook explain() as the plan that ran, then debugging operators AQE already replaced.
Follow-up they will ask: How do you capture the final plan for a job that runs on a schedule?