Module introduction: Catalyst, `.explain()`, and caching

Why this module exists

Modules 1 through 5 built Spark's complete mental model from the outside in: the SparkSession, lazy evaluation, the DataFrame as the main interface, partitioning and shuffle, and finally the criterion for choosing between BroadcastHashJoin and SortMergeJoin. In every one of those lessons, one word kept showing up, never fully explained: AdaptiveSparkPlan. It showed up in module 2's first .explain(), in every plan in module 4, and in module 5's two join plans — always there, never explained. This module opens that box: what the Catalyst optimizer is, why a DataFrame goes through four distinct phases before turning into the real work executors run, what Adaptive Query Execution (the piece behind AdaptiveSparkPlan) actually does, and when caching a result saves real work — and when, instead, it just takes up memory without buying anything in return.

There's a reason this module arrives after M5, not before: reading a Catalyst plan with real understanding already requires knowing what a shuffle is (M4) and what tells a BroadcastHashJoin apart from a SortMergeJoin (M5) — without that vocabulary, a .explain() plan is a wall of text. With it, every line tells a concrete decision the optimizer made, and this module teaches you how to read it, how to read when that decision changes on its own at runtime, and how to decide, with evidence — never a stopwatch — whether saving a result in memory is worth it.

Connection to the module. This module adds no new business operation over Kiosko — there's no new join, no new window function. What it adds is the ability to read, with evidence, why Spark executes what it executes, and a new tool (.cache()/.persist()) for deciding, with a real criterion, when saving an intermediate result is worth it.

Two analogies that are going to accompany this whole module

The first: Catalyst and Adaptive Query Execution are, together, a GPS. Before you leave, the GPS plots the best possible route with the information it has available — the map, the speed limit, the distance. That initial route is exactly what Catalyst produces before a DataFrame executes: an optimized plan, based on estimates. But a real GPS doesn't stick with that route if traffic changes halfway through — it recalculates, using real traffic information that's only known once you're already driving. That's exactly Adaptive Query Execution: a second optimization pass, after the work's first stages have already run and Spark has real — not estimated — statistics on how many rows and how many bytes there truly are.

The second: caching a DataFrame is like leaving an already-cooked meal in the refrigerator, instead of cooking it from scratch every time someone opens the door hungry. If three different people are going to eat the same thing throughout the day, cooking once and storing it saves real time — nobody repeats the work of chopping, seasoning, and cooking. But the refrigerator has limited space, and storing something nobody eats again saves nothing: it just takes up space that could have served something else. That is, precisely, this module's complete criterion: caching helps when someone else is going to eat from that same dish; caching doesn't help — and does cost — when you cook something served only once.

Worked example: this module's map, before touching anything

# catalyst_and_caching_map.py
CATALYST_AND_CACHING_MAP = [
    (2, "The Catalyst optimizer's phases",
        "parsed -> analyzed -> optimized -> physical, read with explain(mode='extended')"),
    (3, "Reading explain() in its different modes",
        "simple, extended, cost, formatted, codegen -- the same query, five different readings"),
    (4, "Adaptive Query Execution",
        "The GPS that recalculates: partition coalescing and join strategy switching, with real evidence"),
    (5, "When caching actually helps",
        "A DataFrame reused across three queries -- isCached(), InMemoryTableScan, fewer bytes read from disk"),
    (6, "persist() and storage levels",
        "MEMORY_ONLY, MEMORY_AND_DISK, DISK_ONLY -- the same data, three different memory costs, measured"),
    (7, "When caching only costs memory",
        "A DataFrame used only once -- the cost is real, the benefit is zero"),
    (8, "Project: Kiosko's optimized query plan",
        "The previous six lessons, integrated into a pipeline cached with criteria, verified end to end"),
]

print("=== Catalyst, explain(), and caching -- before touching anything ===\n")
for number, title, detail in CATALYST_AND_CACHING_MAP:
    print(f"L{number}. {title}")
    print(f"     {detail}\n")

print("This module's goal: read WHY Spark executes what it executes,")
print("and decide with evidence (never a stopwatch) when caching helps.")

What to expect. Running python3 catalyst_and_caching_map.py, the output is exactly this:

=== Catalyst, explain(), and caching -- before touching anything ===

L2. The Catalyst optimizer's phases
     parsed -> analyzed -> optimized -> physical, read with explain(mode='extended')

L3. Reading explain() in its different modes
     simple, extended, cost, formatted, codegen -- the same query, five different readings

L4. Adaptive Query Execution
     The GPS that recalculates: partition coalescing and join strategy switching, with real evidence

L5. When caching actually helps
     A DataFrame reused across three queries -- isCached(), InMemoryTableScan, fewer bytes read from disk

L6. persist() and storage levels
     MEMORY_ONLY, MEMORY_AND_DISK, DISK_ONLY -- the same data, three different memory costs, measured

L7. When caching only costs memory
     A DataFrame used only once -- the cost is real, the benefit is zero

L8. Project: Kiosko's optimized query plan
     The previous six lessons, integrated into a pipeline cached with criteria, verified end to end

This module's goal: read WHY Spark executes what it executes,
and decide with evidence (never a stopwatch) when caching helps.

Notice the structure: lessons 2 and 3 open Catalyst's box — which phases a DataFrame goes through, and the five ways to read them. Lesson 4 explains the mechanism you already saw unnamed in modules 4 and 5: AdaptiveSparkPlan. Lessons 5 through 7 resolve, with measurable evidence, the question of when caching helps and when it doesn't. Lesson 8 integrates everything into a single pipeline over fact_orders_at_scale.

Diagram: where you were, where you're headed

flowchart LR
    subgraph M15["Modules 1-5 (already done)"]
        A["Spark installed, fact_orders rebuilt,\npartitions and shuffle measured,\nbroadcast join vs sort-merge join,\nwindow functions at scale"]
    end

    subgraph M6["This module (6 of 8)"]
        B["L2-L3: Catalyst's phases,\nexplain() in its five modes"]
        C["L4: Adaptive Query Execution --\nthe GPS that recalculates in real time"]
        D["L5-L7: when cache() helps,\npersist() and storage levels,\nwhen caching only costs memory"]
        E["L8: project -- optimized\nquery plan, integrated"]
    end

    subgraph Resto["Modules 7-8"]
        F["Partitioned Parquet, UDFs vs\npandas_udf, final capstone"]
    end

    A --> B --> C --> D --> E --> F

This module's map

Lesson    What it builds
────────  ──────────────────────────────────────────────────────────────
L1        (this one) The map: Catalyst and caching, before touching them
L2        The four phases of the logical/physical plan, read with extended
L3        explain()'s five modes, the same query, five readings
L4        AQE: partition coalescing and join strategy switching
L5        cache() over a DataFrame reused across three queries -- real benefit
L6        persist() with different storage levels -- the same data, three costs
L7        cache() over a DataFrame used only once -- cost with no benefit
L8        Project: Kiosko's optimized query plan, verified

Lessons 2 and 3 answer the same question from two angles: which phases exist (L2), and how to read them precisely (L3). Lesson 4 resolves, with executed evidence, the AdaptiveSparkPlan mystery that's accompanied every plan since module 2. Lessons 5 through 7 aren't three separate topics — they're the same caching criterion applied three times: when it helps, how to control it precisely, and when it doesn't help at all. Lesson 8 introduces nothing new — it integrates the previous six lessons into a single pipeline.

Going deeper: why "reading the plan" is a skill, not an implementation detail

It's tempting to treat .explain() as a technical curiosity — something you check once, to satisfy curiosity, and then forget. This module argues the opposite: reading an execution plan is the only honest way to answer questions like "is this query going to be expensive?" or "is caching this worth it?", without resorting to measuring execution times that — as module 4 already established — aren't reproducible across machines or runs. A .explain() plan is: the same query, over the same data, produces the same structural plan no matter which laptop it runs on. That reproducibility is why this entire guide, since module 2, avoids the stopwatch and trusts the plan instead.

Adaptive Query Execution complicates that story in an interesting way, and that's exactly what this module's lesson 4 explains in depth: the plan Catalyst produces before executing isn't always the plan that ends up running. This doesn't break reproducibility — the final plan is still deterministic for the same input data — but it does mean "reading the plan" now has two distinct moments: before running (the prediction) and after running (the reality, with real statistics). This module teaches you to read both, and not to confuse one with the other.

Common mistakes

Assuming "optimizing" means the same thing in Catalyst as in a traditional relational database. What happens: someone who already knows PostgreSQL's or MySQL's EXPLAIN assumes Catalyst's plan reads exactly the same way — looking for indexes, for example — and gets frustrated not finding that vocabulary. Why it happens: the word "optimizer" and the .explain()/EXPLAIN command are nearly identical between traditional relational engines and Spark, and it's reasonable to expect the rest of the vocabulary to match too. How to spot it: if you search for the word "index" in any plan in this guide, you won't find it — Spark has no concept of an index over flat files like Parquet or CSV; instead, it has partitions, shuffle, broadcast, and — this module's topic — caching. How to fix it: treat Catalyst's vocabulary as its own system, not a translation of traditional SQL. The sibling guide advanced-sql-querying-guide covers a relational engine's EXPLAIN; this guide covers a different optimizer, over a different distributed execution model.

Expecting this module to teach "speeding up" Spark with configuration tricks. What happens: someone comes to this module looking for a list of parameters to copy and paste that make any DataFrame run faster. Why it happens: "optimization" sounds, in everyday language, like a collection of generic tricks applicable to any situation. How to spot it: if by the end of this module all you remember are setting names, unable to explain, with evidence from a real plan, why each one matters in a concrete case, you're missing the central part. How to fix it: every lesson in this module teaches you to read evidence before deciding — the plan, the job count, the cached bytes — never to apply a configuration tweak blindly. This guide's discipline, since module 1, is deciding with data, not with general rules.

Believing caching is always a good idea "just in case." What happens: someone, after seeing .cache()'s real benefit in lesson 5, starts caching every DataFrame in their pipeline, without checking whether it's really reused. Why it happens: once you see the benefit with evidence, it's tempting to generalize "caching is good" without the condition attached to it. How to spot it: if you can't name at least two distinct queries reusing the same cached DataFrame, you're probably paying lesson 7's memory cost without lesson 5's benefit. How to fix it: before writing .cache(), answer a concrete question: exactly how many times is this result going to get used afterward? If the answer is "once," don't cache — this module's lesson 7 shows, with measured evidence, why.

Exercises

Exercise 1 — Recite, from memory, this module's two analogies. Without rereading this lesson, explain in your own words the GPS analogy (for Catalyst and AQE) and the refrigerator analogy (for caching). What does the moment the initial decision stops being valid represent, in each one?

See solution

The GPS represents Catalyst and Adaptive Query Execution: it plots the best possible route with the information available before leaving (the optimized plan, based on estimates), and recalculates it halfway through if real traffic — the real execution statistics — turns out different from what was estimated. The moment the initial decision stops being valid is, precisely, the moment a shuffle stage finishes: that's when Spark has, for the first time, real data instead of estimates, and can decide whether it's worth changing the rest of the plan. The refrigerator represents caching: storing an already-cooked meal saves the work of cooking it again, but only if someone else is going to eat it later; if nobody opens the door for that dish again, storing it only took up space without saving anything. The initial decision (caching) stops making sense at the exact moment it's confirmed nobody else is going to reuse the result.

Exercise 2 — Predict, before reading lesson 4, what had to happen for module 4's groupBy("store_id") shuffle to end up with a single partition instead of 200. You already saw that result in module 4, lesson 7, without this module's full explanation. Based on the GPS analogy, write your own prediction of what new information Spark had to make that decision, and at what moment it had it.

See solution

Reasonable prediction: Spark couldn't know, before running the shuffle, how many bytes groupBy("store_id")'s combined result was going to weigh — that's an estimate, not a fact, before running anything. But once the shuffle finished writing its data, Spark does have the result's real weight, measured in bytes, not estimated. With that real number in hand — far below what would justify 200 separate partitions — Spark could "recalculate the route," just like the GPS with real traffic, and decide to read those 200 potential partitions as if they were one. This module's lesson 4 confirms this prediction with the exact name of the setting controlling that decision.

Exercise 3 — Explain, without code, why this module insists "reading the plan" has two distinct moments with Adaptive Query Execution active. In 2-3 sentences, explain the difference between the plan that exists before running a query and the one that exists after, and why confusing one with the other would be a mistake.

See solution

Before running, Catalyst produces a plan based on estimates — file sizes, configuration defaults, with no real measurement yet — that's the plan you see if you call .explain() on a DataFrame that's never been run. After a real action (.collect(), for example), Adaptive Query Execution may have revised that plan with real statistics, produced by the stages that already ran, and the final plan can differ from the initial one — one partition instead of two hundred, a BroadcastHashJoin instead of a SortMergeJoin. Confusing the two would be a mistake because the "before" plan describes a prediction, while the "after" plan describes what the executors actually ran; reporting the first as if it were the second would give an incorrect picture of the real work Spark did.

Summary and next step

This module opens the two boxes left unexplained in modules 4 and 5: what Catalyst is, with its phases from a query's raw text to the executable physical plan, and what AdaptiveSparkPlan does — the name you've already seen in every .explain() since module 2, undefined until now. It also builds the complete criterion, with measurable evidence and never a stopwatch, for deciding when .cache()/.persist() saves real work and when it only takes up memory without buying anything in return.

Before moving on you should be able to: explain this module's two analogies in your own words (the GPS, the refrigerator); predict why a query's plan can have two distinct versions when Adaptive Query Execution is active; and anticipate what evidence — never time in seconds — you're going to use over the next seven lessons to justify every decision.

Lesson 2 opens the first box: the exact four phases any Spark query goes through before turning into real work, read over the same query you already built in module 5.

Resources

• Apache Spark — SQL Performance Tuning (Catalyst, .explain(mode=...), Adaptive Query Execution active by default since Spark 3.2, and caching settings — the central reference for this entire module). spark.apache.org/docs/latest/sql-performance-tuning.html.
• This guide's DESIGN doc (spark-and-distributed-processing-guide/DISENO.md) — this module's full objective within the eight-module plan.
• advanced-sql-querying-guide's DESIGN doc — the sibling guide covering a traditional relational engine's EXPLAIN, a different topic from the Spark optimizer this module teaches. src/guides/advanced-sql-querying-guide/DISENO.md