Apache Spark WTF??? — 🪖 SQL Goes Hardcore 🧠

Have you ever stood in awe before a monstrous SQL query — a 200-line beast stitched together with CTEe, joins, subqueries, and mysterious window functions — and wondered how on earth Spark plans to march this thing into battle? Do you whisper “please explain” to Spark, only to be met with a barrage of cryptic plans, hash joins, and whole-stage codegen jargon?

Well, soldier, buckle up your seatbelt and grab your helmet — because this isn’t your average “SELECT * FROM tutorial.” We’re about to drop into the trenches of Spark SQL, where performance myths die, optimizers strategize, and you’ll finally learn how to command your queries like a battle-hardened data general.

---

👶 Operation Catalyst: Rookie Orientation

Alright, recruits — the syntax drills are over. You’ve already survived the parsing and analysis camp in my previous article, where Spark turned your innocent SQL text into something vaguely resembling logic. Now we enter the real battlefield: Catalyst’s planning and optimization engine — where queries are weaponized for execution.

Catalyst doesn’t just “run” your SQL. It strategizes. It’s the field general standing over a massive data map, deciding which troops (joins, filters, projections) go first, which to merge, which to eliminate, and which to replace with something smarter. The logical plan you lovingly crafted? Catalyst tears it apart, rewires it, and transforms it through a series of battle-hardened stages.

Think of each stage as a different level of tactical intelligence.

• The logical optimizations are like your drill sergeants yelling, “Why are you filtering after a join? Drop and give me 20 predicate pushdowns!”
• The physical planner steps in like a logistics officer, choosing whether to send your troops in a SortMergeJoin, a BroadcastHashJoin, or maybe a quick ShuffleHashJoin ambush — all depending on the terrain (your data sizes and configurations).
• And then there’s Whole-Stage CodeGen, the special forces unit that takes the final plan and compiles it into tight JVM bytecode — no overhead, no mercy.

But wait, there’s a new commander roaming the field: AQE — Adaptive Query Execution. AQE is like battlefield intelligence that updates mid-fight. It observes how your query performs, gathers metrics, and changes the battle plan on the fly. But beware: in some situations, AQE can overcorrect, misread the terrain, and lead your troops straight into a performance ambush.

---

💣 Mission Plan: Catalyst Optimizations

Every army needs discipline, and Catalyst is no exception. Its secret weapon isn’t brute force — it’s rules. Catalyst Optimizer follows a rule-based optimization system where each rule acts like a tiny field manual: one focused tactic to make your query plan smarter, faster, and more efficient.

But here’s the catch — Catalyst doesn’t just run a few rules and call it a day. It applies them in waves, iteratively rewriting and refining your logical plan until there’s nothing left to optimize. Think of it as a squad of data sergeants yelling orders at your query:

“Fold that constant!”
 “Push that filter forward!”
 “Stop scanning that entire table, soldier!”

Let’s go through some of its key maneuvers — with examples from the front lines.

💣 Constant Folding — “Don’t Waste Ammo”

Catalyst hates redundancy. If it sees something like:

SELECT fuel * (1 + 0.08) AS war_price FROM tanks;

It won’t waste CPU cycles calculating 1 + 0.08 for every row like a rookie. Instead, it precomputes it during optimization and replaces it with:

SELECT fuel * 1.08 AS war_price FROM tanks;

💥 Pitfall 1 — Non-Deterministic Functions Aren’t Folded. Spark’s Catalyst optimizer refuses to fold any expression that’s non-deterministic — meaning it might return a different result each time it’s evaluated. For example:

SELECT current_timestamp(), rand(), uuid();

Even though current_timestamp() might look constant within a single query, Catalyst won’t fold it. Why? Because doing so would “freeze” its value at plan time instead of execution time.
✅ Fix: Want it constant? Compute it yourself. Catalyst won’t babysit your randomness or sense of time.

💥 Pitfall 2 — Over-Aggressive Folding Can Change Semantics. Catalyst assumes your constants are safe to precompute — but not every expression is purely arithmetic. Example:

SELECT 1 / 0 AS mission_failure;

Instead of waiting until runtime to crash, Catalyst may try to fold it at optimization time and throw a planning error before you even run the query. Another case:

SELECT cast('not-a-number' AS int);

Catalyst might fail during folding and abort the plan — which can be very confusing if your expression is inside a larger conditional, like:

SELECT CASE WHEN x > 0 THEN cast('not-a-number' AS int) ELSE 0 END;

Spark might still attempt to fold the cast('not-a-number' AS int) part even though it’s never actually used, resulting in an early failure.
✅ Fix: Don’t hand live grenades to Catalyst — guard dangerous expressions with CASE WHEN or try_cast. That way Spark evaluates them only when needed, not during planning.

💥 Pitfall 3 — Constants Aren’t Always As Constant As You Think. If your constants depend on session configuration, environment variables, or Spark SQL functions like current_date(), their folded value might differ between executions or Spark sessions. So when you say:

WHERE event_date > current_date() - INTERVAL 7 DAYS

Catalyst may fold that date into a fixed literal at plan time, meaning your “last 7 days” filter won’t actually move forward over time if you cache or reuse the plan.
✅ Fix: Rebuild or refresh the plan for time-dependent queries, or use runtime parameters instead of static literals.

💣 Predicate Pushdown — “Engage the Enemy Early”

Imagine you have this:

SELECT * FROM supplies WHERE front = 'EUROPE';

Without optimization, Spark might march through the entire dataset and then apply the filter — a complete waste of ammo. Catalyst yells, “Negative, soldier!” and pushes the WHERE condition down to the data source — be it Parquet, Delta Lake, or even a remote database — so only the relevant rows are read. That means less data on the wire, fewer I/O skirmishes, and a much faster victory.

💥 Pitfall 1 — Complex Expressions Block Pushdown. If your filter involves a function Spark can’t translate into the source’s native predicate language, pushdown won’t happen. Example:

SELECT * FROM supplies WHERE upper(front) = 'EUROPE';

The upper() function prevents Spark from pushing the filter to Parquet because Parquet doesn’t know how to evaluate that expression. Instead, Spark will scan all rows and apply the filter afterward — costly and sneaky.
✅ Fix: Precompute normalized columns (e.g., front_upper) or rewrite filters to use literals Spark can push.

💥 Pitfall 2 — Data Type Mismatch. Even when the expression looks simple, subtle type mismatches can ruin pushdown. Example:

SELECT * FROM supplies WHERE id = 100;

If id is stored as a string in the Parquet schema, Spark has to cast it before comparing — and that cast disables pushdown.
✅ Fix: Ensure schemas match your query’s data types.

💥 Pitfall 3 — Nested Columns or Structs. When dealing with nested structures:

SELECT * FROM supplies WHERE payload.details.country = 'SP';

Spark’s Parquet/ORC readers might not push filters deep into nested fields. Catalyst doesn’t always generate a compatible filter predicate for complex types — so you end up reading entire payloads anyway.
✅ Fix: Flatten or extract relevant fields into top-level columns when you care about pushdown.

💥 Pitfall 4 — Combining Filters in Non-Pushdown-Friendly Ways. Catalyst is smart, but when you start chaining filters with unsupported operators (like UDFs or mixed AND/OR logic), it may choose to apply them all in Spark’s execution engine instead of partially pushing what it could. Example:

SELECT * FROM supplies WHERE front = 'EUROPE' OR my_custom_udf(country) = 'EUROPE';

Even though the first predicate is pushdown-compatible, the OR + UDF combo makes the whole condition non-pushable.
✅ Fix: Separate filters into multiple stages (df.filter(...).filter(...)) so Spark can push what’s possible before applying the rest.

💥 Pitfall 5 — Source-Specific or Version-Specific Gaps. Different connectors have different levels of predicate pushdown maturity. For instance:

• JDBC pushdown may depend on the dialect and connector implementation (e.g., MySQL vs. Postgres behave differently).
• Delta Lake pushdown might be limited when your filter targets non-partitioned columns or involves Delta’s transaction metadata.
• In Snowflake, pushdown fights the battle where the data lives. But since connector 3.0.0, only basic filters make the trip — joins and fancy logic stay behind. Keep it simple, flip on sfPushDown="on", and call in Snowpark for heavy artillery (it’s not Spark, despite the name).

✅ Fix: Always verify with .explain(true) or the Spark UI’s SQL tab — look for “PushedFilters” in the plan. If you don’t see it, your filters stayed behind.

💣 Projection Pruning — “Travel Light, Move Fast”

You ask for a few columns:

SELECT unit_id, op_funds FROM missions;

But your table has 200 columns. Catalyst says: “We’re not hauling the whole warehouse into the battlefield.” It rewrites the plan to only read the necessary columns — projection pruning — dramatically reducing I/O and memory pressure.

💥 Pitfall 1 — Nested or Complex Columns Sneak Past the Gate. Catalyst can only prune top-level columns cleanly. If your table stores structs, arrays, or maps (think payload.details.country), Spark often ends up reading the entire nested structure even if you just need one small field.
✅ Fix: Flatten your data model or extract the needed fields into columns (e.g., using withColumn("country", col("payload.details.country"))).

💣 Pitfall 2 — Expression Fog of War. When you wrap columns inside expressions or UDFs, Catalyst loses visibility of what’s actually needed.

SELECT concat(first_name, ' ', last_name) AS full_name FROM agents;

Catalyst may end up reading both first_name and last_name entirely — or worse, everything — if the logic isn’t transparent.
✅ Fix: Keep expressions simple or break them into clear select() steps so Catalyst can track the base columns.

💥 Pitfall 3 — Caching Too Early. If you cache a DataFrame before pruning, Spark stores all columns in memory — even the ones you won’t use later. Once it’s cached, projection pruning is out of the picture.
✅ Fix: Always prune first, then cache. Cache after the last select() that trims unnecessary columns.

💥 Pitfall 4 — Disobedient Data Sources. Not every data source obeys pruning orders. Some JDBC, JSON, or CSV readers fetch entire rows before Spark applies the projection.
✅ Fix: Check your explain() plan for ReadSchema or PushedProjects. Use Parquet, ORC, or Delta for proper pruning.

💥 Pitfall 5 — Lazy Pruning Betrayal. Spark’s lazy evaluation means pruning happens only when the plan executes. If you inspect your DataFrame too early (like printing the schema or count), the optimization may not yet apply.
✅ Fix: Don’t panic — pruning happens at execution time. Just ensure your transformations are column-limited before actions like collect() or write().

💥 Pitfall 6 — View Materialization Trap. If your query uses temp views or SQL views, Spark may expand them before pruning, pulling in more columns than needed.
✅ Fix: Project explicitly in your main query — don’t rely on view definitions to be pruned downstream.

💥 Pitfall 7 — Catalog Confusion. Sometimes schema inference (especially with JSON or CSV) makes Spark overestimate what’s needed — reading extra columns “just in case.”
✅ Fix: Define explicit schemas whenever possible; don’t let Spark guess your payload.

💣 Join Reordering — “Flank the Enemy”

When Catalyst spots multiple joins, it doesn’t just execute them in the order you wrote. For example:

SELECT * FROM HQ A  
JOIN division B ON A.id = B.id  
JOIN squad C ON B.ref = C.ref;

Catalyst might decide to join B and C first — because that smaller join produces fewer rows, which then makes the A join faster.

This is join reordering, a tactical move where Catalyst estimates cardinalities (row counts) and rearranges joins for the least cost.

💥 Pitfall 1 — Stale or Missing Statistics = Blind Strategy
 Catalyst’s join reordering depends on table statistics. If those stats are missing or outdated, Spark guesses wrong — sometimes spectacularly. What should’ve been a tiny join becomes a full-blown shuffle war.
✅ Fix: Keep your stats fresh with ANALYZE TABLE <table> COMPUTE STATISTICS or automatic stats collection. No intel = bad tactics.

💥 Pitfall 2 — Broadcast Joins Can Derail Reordering
 Once you force a broadcast join (via hint or size threshold), Catalyst stops reordering around it — it’s locked in place. A wrong broadcast = a trapped squad.
✅ Fix: Use broadcast hints only when you know the data fits in memory. Otherwise, let Catalyst decide based on stats.

💥 Pitfall 3— Cross Joins: The Nuclear Option. If Catalyst can’t detect valid join conditions (missing or ambiguous keys), it might trigger a full cross join — combining every row with every other. Boom.
✅ Fix: Always double-check your ON clauses and verify the plan (explain(true)). A missing join condition can multiply your dataset faster than you can kill the job.

💥 Pitfall 4 — Too Many Joins, Not Enough Intel. With long join chains (5+ tables), Catalyst’s cost model can get noisy. Even with stats, it might misjudge intermediate sizes and pick a suboptimal plan.
✅ Fix: Break complex joins into stages or materialize critical intermediates with cache() or temp views. Don’t expect Catalyst to plan your whole war in one go.

💣 Pitfall 5— Hint Conflicts: Command Chaos. Conflicting hints (BROADCAST, SHUFFLE_MERGE, MERGE) on different joins can confuse Catalyst — it might ignore all of them or follow only one, unpredictably.
✅ Fix: Use hints sparingly and consistently. One clear command beats three conflicting orders.

💣 Filter Combining — “Don’t Repeat Yourself”

If you’ve got:

WHERE ammo > 100 AND ammo > 200

Catalyst merges them into a single efficient condition:

WHERE ammo > 200

Simple, right? But multiply that across thousands of filters and expressions, and you start seeing how Catalyst squeezes out micro-optimizations like a perfectionist sergeant inspecting uniforms.

💥 Pitfall 1 — Filters on Different Columns Stay Fragmented. Catalyst only combines filters on the same attribute. If your logic mixes columns (ammo > 200 AND fuel > 50), it won’t merge or reorder them — even if one filter makes the other irrelevant.
✅ Fix: Simplify related filters before Spark sees them, or use DataFrame transformations that clearly express dependencies. Don’t expect Catalyst to read your tactical intentions.

💥 Pitfall 2 — Non-Deterministic Filters Are Off-Limits. Catalyst refuses to merge conditions that contain non-deterministic functions (rand(), uuid(), current_timestamp()). Each evaluation could differ — and Spark won’t risk changing semantics.
✅ Fix: Isolate or precompute random or time-based values before applying filters. Keep your WHERE clause pure and predictable.

💥 Pitfall 3 — Filter Order Still Matters (a Bit). While Catalyst tries to push selective filters first, it can’t always reorder them across projections or complex expressions. That means an expensive filter might still run before a cheap one.
✅ Fix: Apply the most selective (and cheapest) filters as early as possible in your DataFrame chain — Catalyst helps, but it’s not clairvoyant.

💥 Pitfall 4 — Filters Hidden in UDFs Are Invisible. If your filtering logic lives inside a UDF (WHERE is_restricted(ammo)), Catalyst can’t analyze or combine it. It just treats it like a black box and executes everything.
✅ Fix: Rewrite logic with built-in Spark SQL functions. Catalyst only optimizes what it can see.

💥 Pitfall 5 — Redundant Filters Can Still Slip Through. When filters are written in different ways (ammo > 100 vs. NOT(ammo <= 100)), Catalyst might fail to recognize them as duplicates and evaluate both.
✅ Fix: Keep conditions consistent and simple — avoid logical inversions that confuse the optimizer.

💥 Pitfall 6 — Post-Join Filters May Miss Pushdown Opportunities. Catalyst combines filters after joins if possible, but once data’s shuffled, it’s too late to prune early.
✅ Fix: Always filter before joins when logically possible — prune the battlefield before sending reinforcements.

💣 Subquery Collapse — “Unify the Frontlines”

Nested queries? Catalyst hates them.

SELECT * FROM (  
  SELECT * FROM missions WHERE zone = 'EUROPE'  
) WHERE firepower > 1000;

Catalyst merges these into one plan, removing unnecessary layers. That’s subquery collapsing, a cleanup operation that keeps your plan lean and easy to optimize further.

💥 Pitfall 1 — Name Collisions After Collapse. When inner and outer queries have overlapping column names, collapsing can rename or shadow them unexpectedly — suddenly, your region column points to a different alias than before.
✅ Fix: Always alias your fields explicitly (SELECT sales.region AS sales_region) before collapsing happens. Never trust unnamed columns in nested queries.

💥 Pitfall 2 — Losing Query Boundaries Changes Semantics. Catalyst sometimes pushes filters or limits from the outer query into the inner one during collapse, changing the number of rows or the filtering order.
✅ Fix: If query boundaries matter (e.g., for top-N per group logic), use LIMIT, DISTINCT, or explicit temp views to enforce separation. Make your intent crystal clear.

💥 Pitfall 3 — Non-Deterministic Expressions Get Duplicated. If both subqueries use non-deterministic functions (rand(), current_timestamp()), collapsing can cause them to be evaluated once instead of twice — or vice versa — changing your results.
✅ Fix: Precompute random or time-based expressions before nesting. Catalyst optimizes structure, not your sense of probability.

💥 Pitfall 4 — CTEs and Views Aren’t Always Collapsed. WITH clauses (CTEs) or SQL views can block collapsing if Spark thinks they’re reused or logically distinct. That means extra layers, extra scans, and slower plans.
✅ Fix: Don’t assume CTEs are optimized away — reuse DataFrames programmatically when possible, or materialize explicitly only when needed.

💥 Pitfall 5 — Analyzer Rewrites May Break Expectations. Sometimes, the subquery collapse triggers additional analyzer passes — like pushing projections or filters — that reorder expressions in ways you didn’t expect.
✅ Fix: Always check the final logical plan (df.explain(True)) to ensure the rewrite matches your mental model. Catalyst doesn’t salute your query syntax — only the plan.

💥 Pitfall 6 — SQL Semantics vs. Spark Semantics. Spark SQL doesn’t always follow traditional ANSI SQL rules. A subquery that’s legal and isolated in pure SQL may get collapsed or flattened by Catalyst, breaking expected scoping behavior.
✅ Fix: When porting SQL logic from other engines, test each nested level separately. Spark plays by Catalyst’s rules, not your database’s.

💣 UDFs — “The Enemy Within”

Every unit has that one rebel who ignores the chain of command. In Spark SQL, that’s your User-Defined Function (UDF). Catalyst treats them like a sealed crate marked “classified” — it can’t see inside, can’t optimize around it, and definitely can’t control what’s happening inside that box. Once you deploy a UDF, you’re on your own, soldier.

💥 Pitfall 1 — Optimization Blackout. Catalyst loses visibility when a UDF shows up. No predicate pushdown, no constant folding, no filter reordering — your query plan becomes a dead zone. For example:

SELECT my_udf(value) FROM big_table WHERE value > 100;

Spark must read everything because it can’t tell whether the UDF changes how value behaves.
✅ Fix: Avoid wrapping filters or projections inside UDFs — let Catalyst do the heavy lifting.

💥 Pitfall 2 — Serialization Crossfire. Python or R UDFs force data to leave the JVM, cross process boundaries, and come back. Each trip means heavy serialization, deserialization, and no vectorization.
✅ Fix: Use Pandas UDFs (@pandas_udf) or Scala/Java UDFs to keep data in batches and reduce I/O overhead.

💥 Pitfall 3 — Side-Effects: The Silent Saboteur. Open a file? Log a message? Call an API? Spark will do it for every row. What should’ve been a single handshake or read of a config file turns into an artillery strike of side effects — and yes, I’ve witnessed the carnage.
✅ Fix: Don’t do one-time work inside the UDF. Initialize resources outside and keep the UDF pure.

💥 Pitfall 4 — Non-Deterministic Chaos. Using randomness, timestamps, or external mutable state inside UDFs makes Catalyst freeze. It can’t assume repeatability, so it disables caching and may re-evaluate the function multiple times.

✅ Fix: Keep UDFs pure — no random, no clock, no global state. If unavoidable, mark deterministic functions explicitly (Scala/Java only).

💥 Pitfall 5 — “I Could’ve Used a Built-in” Syndrome. Many UDFs just reinvent what Spark already provides: string parsing, math ops, array transformations. When you use UDFs instead of built-ins, you forfeit Catalyst’s full optimization power.
✅ Fix: Check Spark’s built-in functions (org.apache.spark.sql.functions) or higher-order functions (transform, filter, aggregate) before rolling your own.

💥 Pitfall 6 — Blind to the Battle Plan. Once a UDF is in your query, your explain plan becomes a mess of PythonUDF or ScalaUDF nodes. Catalyst can’t reorder or simplify the operations around it. 
✅ Fix: Always inspect your plan with df.explain(true). If you see PythonUDF high in the plan, Catalyst has already surrendered — time to refactor.

💥 Pitfall 7 — UDFs and Complex Conditions Hide Join Keys. Catalyst can’t analyze join predicates that use UDFs or messy expressions like A.key = substr(B.compound_key, 1, 10). It treats them as opaque, blocking reordering and other join optimizations.
✅ Fix: Keep join conditions simple and explicit. Preprocess keys before joining so Catalyst can see what you mean.

💥Pitfall 8 — Marching Without Arrow Support. When you use Pandas UDFs but forget to enable Apache Arrow, Spark falls back to slow, row-by-row data transfer between the JVM and Python. That’s like moving terabytes on foot instead of airlifting them — painfully slow and utterly demoralizing.
✅ Fix: Enable Arrow with:

spark.conf.set("spark.sql.execution.arrow.pyspark.enabled", "true")

Arrow allows Spark to exchange columnar data efficiently, keeping your Pandas UDFs vectorized and blazing fast.

💣 The AQE Wildcard — “Dynamic Reinforcements”

And then comes AQE (Adaptive Query Execution) — the modern battlefield AI. While Catalyst does its planning before execution, AQE adapts during execution.

For instance, it might notice that one side of your join is smaller than expected and switch to a broadcast join mid-fight. Victory! 🏆
But… if AQE misjudges the terrain (due to skew, caching, or incorrect metrics), it might redeploy the wrong troops, triggering massive shuffles or even OOM errors. Defeat! 💀

💥 Pitfall 1 — Misleading Initial Stats = Bad Intel, Bad Strategy. AQE relies on runtime statistics, but the first few stages still use Catalyst’s estimated stats. If your initial partitions are skewed or outdated, AQE’s early decisions are built on sand.
✅ Fix: Keep table stats fresh and repartition wisely. Don’t expect AQE to fix bad intelligence — it’s reactive, not psychic.

💥 Pitfall 2 — Skewed Data Still Wins the War (for the wrong side). AQE can detect skew and split large partitions into smaller ones — if it spots the skew early enough. But with highly uneven distributions, it can underestimate severity and still shuffle massive chunks of data.
✅ Fix: Preemptively mitigate skew using techniques like salting or bucketing. AQE helps, but it’s not a miracle worker.

💥 Pitfall 3 — Cached Data Disables AQE’s Recon. Once you cache a DataFrame, Spark skips adaptive re-optimization because it treats cached data as static. That means no dynamic join switching, no skew handling — just the old plan.
✅ Fix: Cache only after AQE’s job is done, or disable caching when adaptive strategies matter.

💥 Pitfall 4 — Broadcast Joins Gone Wild. AQE sometimes decides mid-flight to switch a shuffle join into a broadcast join — great when it works, catastrophic when the “small” side isn’t actually small. Instant OOM.
✅ Fix: Cap your broadcast size with spark.sql.autoBroadcastJoinThreshold. AQE’s optimism can fill your cluster’s memory faster than you can say KillExecutors.

💥 Pitfall 5 — Changing Join Strategies = Changing Semantics. In complex pipelines, AQE’s strategy swaps (like from sort-merge to broadcast) can subtly change execution order or output partitioning, which breaks downstream assumptions.
✅ Fix: When partitioning or sort order matters, explicitly control it with repartition() or sortWithinPartitions(). Don’t let AQE improvise your logistics.

💥 Pitfall 6 — Logging Lies (or Half-Truths). explain() only shows the initial logical plan, not the adaptive adjustments AQE makes at runtime. You might think Spark ran one plan — it actually ran another.
✅ Fix: Always inspect the final adaptive plan in Spark UI’s SQL tab or logs (AdaptiveSparkPlanExec). Trust, but verify.

💥 Pitfall 7 — AQE + Dynamic Partition Pruning = Chaos Under Fire
 When AQE and DPP (Dynamic Partition Pruning) work together, timing issues can make Spark miss pruning opportunities or re-trigger expensive shuffles.
✅ Fix: Tune DPP configs (spark.sql.optimizer.dynamicPartitionPruning) and test under load — this duo is powerful but unpredictable.

💥 Pitfall 8 — Overhead on Tiny Jobs. For small queries, AQE’s re-optimization phases can take more time than they save. You end up with micro-battles planned like full invasions.
✅ Fix: Disable AQE for lightweight jobs. Use it only when joins, skews, or large shuffles justify the extra command center.

💥 Pitfall 9 — AQE Can Be a Booby Trap in Large Plans. When your query plan grows massive — dozens of joins, nested subqueries, endless shuffles — AQE can turn from savior to saboteur. As I reported in SPARK-50992 and dissected in my article “I Love It When a Plan Comes Together (Part III)”, adaptive execution sometimes collapses under its own complexity: excessive stage re-planning, heap bloat, and OutOfMemoryErrors that strike long after the query starts.
✅ Fix: If your plan’s a battlefield of joins and Spark blows up before firing a shot, pull AQE out of the equation. Test with it off. Sometimes, survival > sophistication. KISS (Keep It Simple, Spark) the chaos goodbye.

---

🎖️ The Data Engineer’s Code of Honor

You’ve marched through Catalyst’s trenches, dodged AQE ambushes, and survived the optimizer’s chaos. Now it’s time to graduate — but before you’re officially deployed, memorize the Data Engineer’s Code of Honor. These aren’t mere tips; they’re survival rules for life in the Spark SQL battlefield.

1. 🪖 Don’t Give Me Push-ups, Give Me Push-downs. Always push filters and projections to the source. The closer the fight to the data, the fewer casualties on your cluster.
2. ⚙️ Know Your Enemy: The Execution Plan. Never assume Spark did what you think it did. explain(true) is your radar — check it before every mission.
3. 💣 Flatten Before You Fight. Nested schemas and structs are dead weight. Pull out only what you need before Catalyst drags the whole armory into memory.
4. 🔪 Prune Like Your Life Was at Stake. Read only the columns you truly need. Every extra field is another byte in the body bag.
5. 🧠 Trust Catalyst, But Verify AQE. Adaptive Query Execution is powerful but unpredictable. It can win you wars — or start one in your shuffle stage.
6. ⚔️ Join Smart or Die Shuffling. The wrong join can sink your mission. Know your strategies, and hint before Spark surprises you.
7. 🧱 Filter Early, Filter Often. Filters are your first line of defense. Apply them as close to the data source as possible — before joins, before aggregations. A delayed filter is a wasted CPU cycle.
8. 🔥 Cache Strategically, Not Emotionally. Caching too early or too wide turns your cluster into a sitting duck. Cache only what you’ll reuse — and release it when you’re done.
9. 🪶 UDFs Are Friendly Fire. Catalyst can’t see through your UDFs, so it can’t optimize around them. Use built-in functions whenever possible, or accept the collateral damage.
10. 🧩 Update Your Stats, Win Your Battles. Catalyst’s join order and cost estimates depend on fresh table stats. Stale stats mean bad plans — and bad plans mean lost battles.
11. 📦 Keep Your Data Sources in Line. Not all connectors obey orders. JDBC, CSV, and some third-party sources may ignore pushdown or pruning commands. Always inspect the plan to confirm compliance.
12. 🚀 Measure, Don’t Guess. Spark’s performance myths spread faster than log spam. Benchmark, profile, and validate — the truth is in the metrics.
13. 🎯 Leave No Query Behind. Readability matters. A well-structured, documented query outlives optimizations — and helps the next engineer survive the next battle.

---

📋 Mission Accomplished: The Final Debrief

Spark doesn’t follow rank — it follows rules. Master them, and Catalyst becomes your most loyal soldier. Break them, and you’ll be running laps around your cluster while your executors beg for mercy.

And before we stand down, let’s honor the fallen — those who thought they could outsmart the optimizer with endless walls of SQL. May their queries rest in peace, forever shuffling in the great cluster beyond. 🕯️

Dismissed. 🫡

---

“Now tell me you what you want … HARDCORE!
(So what you want?) HARD…CORE!!!
(So what you want?) HARD…CORE!!!
(So what you want?) HARD…CORE!!!
(So what you want? So what you want?) HARD…CORE!!!”
(from “Hardcore” by Tim Dog)

---