Apache Spark WTF??? 🤔Hey, Didn’t I Run This Query Yesterday? 🦫

“Today’s topic: self recursion”
(adapted from “Self Destrukt” by Chill Bump)

---

⏰ Day 1: The First Morning

Before we throw Apache Spark into full sci-fi recursion, we need to meet the innocent version first: the traditional Common Table Expression, or CTE.

What is a CTE and Why Do We Use Them?

A CTE is a temporary, named result set inside a single SQL statement. Instead of burying a giant subquery inside FROM, you define it cleanly at the top with WITH. No magic, no loops, no existential crises.

Without CTEs, complex SQL becomes subquery lasagna. CTEs flatten the logic into a human-readable, top-to-bottom pipeline.

⚠️ Performance Note: Traditional CTEs are mostly syntactic sugar. Spark’s Catalyst Optimizer decides whether to inline, push down filters, or reuse expressions. They don’t guarantee performance magic, but they make queries easier to debug.

The Old Problem: Unknown Depth

Standard CTEs break down when data is secretly a tree or graph. Polite data is flat; rude data comes in hierarchies:

• Org Charts: CEO ➔ VP ➔ Director ➔ Manager ➔ Engineer
• File Directories: /root ➔ /finance ➔ /reports ➔ /2026 ➔ /final_v2
• Bill of Materials: Bicycle ➔ Wheel ➔ Rim ➔ Spoke

All hierarchical data asks the same question:How deep does this go?

In old Spark SQL, you had to guess the depth and chain repetitive LEFT JOINs. At 3 levels, fine. At 30 or 300, SQL stops being analytics and becomes archaeology.

Life Before Recursive CTEs: The PySpark Loop of Sadness

To traverse unknown-depth trees, engineers often had to abandon pure SQL for PySpark loops.

You created a “frontier” DataFrame, joined it back to the base table inside a Python while loop, unioned the results, and repeated.

Because Spark is lazy, each pass can extend the lineage chain. To avoid JVM pain, you had to track state, sprinkle .cache() or .checkpoint(), manage storage, and trigger eager actions just to know when to stop.

Enter Recursive CTEs: SQL Gets a Loop

A Recursive CTE can reference its own output, letting SQL express the loop directly.

You add RECURSIVE and structure the query with two pieces joined by UNION ALL:

WITH RECURSIVE counter_cte(n) AS (  
  SELECT 1                          -- 1. The Anchor (Where do we start?)  
  UNION ALL  
  SELECT n + 1 FROM counter_cte   
               WHERE n < 5          -- 2. The Recursion (How do we repeat?)  
)  
SELECT * FROM counter_cte;

In Groundhog Day terms:

• The Anchor Query (Morning 0): Spark evaluates the seed rows: the CEO, /root, or 1.
• The Recursive Step (Morning 1, 2, 3…): Spark feeds yesterday’s rows back into the query to find the next layer.
• The Termination Condition: The loop exits when an iteration yields zero new rows, or when your WHERE clause cuts it off.

Verifying the Blueprint: Open Source vs. Databricks

Is this proprietary enterprise magic? No.

• Databricks Availability: Public Preview in Databricks SQL 2025.20 and Databricks Runtime 17.0.
• Apache Spark Availability: Open-source in Apache Spark 4.1.0+ under SPARK-24497.
• Safety Guardrails: Engine defaults can fail the query if it exceeds limits such as max recursion depth or accumulated row count.

🧪 The Morning Lab: Paying the Micro-Data Tax

Our first notebook, day_01__the_first_morning.ipynb, tests a flat CTE, a recursive scalar counter, an unknown-depth folder walk, and a depth profiler.

The goal is simple: understand anchor, recursive step, frontier, termination, and accumulated output before the basement machinery appears.

---

👔 Day 2: Same Query, New Rows

Today, we give the groundhog a real job: walking an organizational hierarchy.

The moment a stakeholder asks for everyone who reports to a VP, directly or indirectly, your peace evaporates. Somewhere on disk sits a table with id and parent_id, and Spark is about to wake up at 06:00, run the same query pattern again, and mutter: Didn’t I do this yesterday?

Anatomy of the Org Loop

A recursive hierarchy query has two parts welded together by UNION ALL:

• The Anchor Query (Morning 0): Fetch the starting node, such as the CEO or a department head. This is where the loop begins.
• The Recursive Step (Mornings 1 to N): Reference the CTE itself, take “yesterday’s” discovered rows, and join them back to the source table to find the next generation.

💡 The Golden Rule: Recursive CTEs don’t walk memory pointers. They repeatedly join the base table against yesterday’s active frontier rows.

The Crucial Safeguards: Depth and Paths

Production hierarchies are dirty. One cyclical reference can send Spark into an infinite breakfast loop.

Add two guardrails directly into the recursive step:

• The Depth Escalator: Start with 0 AS depth, then increment by +1 each iteration. This tells you how deep the tree actually runs.
• The Path Tracker: Start with an array containing the initial ID, append each child ID, and block repeats with: WHERE NOT array_contains(path, child_id).

If Alice reports to Bob, Bob reports to Carol, and Carol secretly reports to Alice, you have a corporate ouroboros. The path filter kills the loop before HR becomes a graph database.

The Join Direction Trap

Your join keys decide the direction of travel.

• To Walk Down the Tree (Descendants): Join the base table’s manager_id to the frontier’s employee_id. Find everyone whose manager was discovered yesterday.
• To Walk Up the Tree (Ancestors): Join the base table’s employee_id to the frontier’s manager_id. Find the manager of the person discovered yesterday.

Mix this up and you are no longer finding ancestors or descendants. Join manager_id to manager_id, and Spark starts matching coworkers to coworkers, reinventing LinkedIn inside your production pipeline.

🧪 The Traversal Lab: Defusing the HR Trap

Our second notebook, day_02__same_query_new_rows.ipynb, tests a top-down descendant walk, a middle-management subtree, and a toxic cycle: A ➔ B ➔ C ➔ A.

The goal is to prove the practical rule: always carry depth, always track path, and never trust an org chart after 06:00.

---

🚪 Day 3: The Basement Door Opens

This is where recursive CTEs stop being convenient SQL syntax and become a physical execution story.

The Internal Cast of Characters

When you execute a recursive query, Catalyst lowers your declarative logic into three key internal pieces:

• UnionLoop: The logical blueprint. It combines the anchor query, the recursive step, and the rules for accumulating rows.
• UnionLoopRef: The internal placeholder for yesterday’s frontier. When the recursive block references the CTE name, it does not read the fully accumulated result; it reads the rows produced by the previous iteration.
• UnionLoopExec: The physical loop operator added under SPARK-50892. It runs the anchor, swaps UnionLoopRef for the active frontier on each tick, and drives the recursive execution.

Breaking the DAG: The Cyclic Roundabout

Spark’s execution model is built around DAGs: Directed Acyclic Graphs. The important word is acyclic. Data normally flows one way, from sources to sinks.

Recursion bends that rule. UnionLoopRef acts like a feedback lane, routing the previous iteration’s output back into the recursive branch. Spark cannot know the total number of iterations upfront, because the answer depends on the data.

So the plan is no longer just a static highway. It becomes a controlled roundabout: run one morning, inspect the frontier, decide whether another morning is needed.

The Loop Accountant: Why Spark Must Stop Being Lazy

Normally, Spark loves laziness. It chains transformations and waits for an action like .collect() or .save() before doing real work.

Recursion ruins the nap. At the end of each iteration, Spark must answer one question: Did this morning produce new rows?

If yes, the loop continues. If no, the loop stops. That means the engine must materialize enough of each recursive step to check progress. Clean SQL on top; eager accounting underneath.

Tiny Town Optimizations: LocalRelation & OneRowRelation

For tiny recursive queries, like counting from 1 to 5, Spark may avoid distributed work entirely. The plan can collapse into local operators:

== Physical Plan ==  
UnionLoop (1)  
   :- LocalRelation (2)  
   +- Project (5)  
      +- Filter (4)  
         +- UnionLoopRef (3)

LocalRelation: If the anchor is a hardcoded value, Spark can embed the row directly in the plan. No table scan, no cluster shuffle, no dramatic warehouse invoice.

If you use a tableless SELECT 1, you may instead see a Project over OneRowRelation, Spark’s invisible “one fake row so SQL has something to stand on” trick.

The Ghost of Shuffles Past: Notice what’s missing: no Join, no Exchange, no distributed freight train.

That speed is not representative of real hierarchy traversal. Replace the toy counter with a live table, and the physical joins, partitioning decisions, and shuffle risks come back from the basement.

A Quick AQE Detour: The Groundhog With A Flashlight

One misconception: AQE does not mean the recursive plan grows into a new monster every morning.

Because Spark uses loop-aware physical nodes like UnionLoopExec, AQE can still adapt joins, coalesce shuffle partitions, and react to runtime sizes inside the work being executed. The anchor may be tiny, the middle frontier may explode, and the final frontier may shrink again.

💡 The Correct Mental Model: AQE helps optimize the work inside each morning. It doesn’t remove the calendar loop. Inspect plans, shuffles, spills, and partition counts carefully, but do not blame AQE by default. In this story, the groundhog is holding the flashlight.

🧪 The Basement Lab: Dissecting the Catalyst Blueprint

Our third notebook, day_03__the_basement_door_opens.ipynb, uses EXPLAIN FORMATTED and a token scanner to compare recursive plans.

It contrasts toy numeric recursion, which can use local plan shortcuts, with real table-backed recursion, where Join and Exchange operators can appear and the distributed engine wakes up.

The goal: learn to recognize when recursion is still Tiny Town, and when Spark has opened the basement door.

---

Spark Recursive CTE Internls — Groundhog Day Edition

---

🕯️ Day 4: A Candle in the Basement

When a production pipeline stalls, your instinct is to open the Spark UI, find the active stage, isolate the bad iteration, and fix it. With recursive CTEs, that gets harder.

The SQL is clean, but the loop is engine-owned. The internal state machine is mostly hidden, so your beautiful recursive query can become a black box wearing a very elegant hat.

The Loop Accountant: executeAndCacheAndCount

Day 3 explained why Spark must check progress after each recursive step.
Day 4 is about the bill for that check.

For non-local cases, UnionLoopExec materializes the recursive result through an internal routine shaped like this:

case _ =>  
  val materializedDF = df.repartition()  
  val count = materializedDF.queryExecution.toRdd.count()

This creates two costs.

• The Eager Interrogation: Spark is normally lazy, but recursion needs a stop signal. The driver cannot simply trust a logical plan; it must force work and count the current frontier. Deep hierarchies therefore create sequential progress checks, one morning at a time.
• The Internal Repartition Penalty: Before counting, Spark repartitions the materialized recursive result. That can mean serialization, redistribution, and coordination overhead on every non-local iteration.

Clean SQL upstairs. Accounting department in the basement.

The Lineage Illusion & The Final Union

A naive iterative plan would grow into a monster. To avoid that, UnionLoopExec cuts the active frontier lineage by wrapping the evaluated RDD into a fresh logical leaf:

val logicalRDD = LogicalRDD.fromDataset(  
  prevDF.queryExecution.toRdd,  
  prevDF,  
  prevDF.isStreaming  
).newInstance()

That keeps each active morning manageable.

But the debt does not disappear. Spark still stores each iteration in unionChildren, and when the loop ends, it builds the final accumulated result: Union(unionChildren.toSeq). Then it consolidates partitions with: df.coalesce(numPartitions).

So the plan doesn’t explode during the loop, but the final union still has to assemble every morning’s evidence.

The groundhog kept a diary. Now Spark has to publish the whole thing.

Driving with Foggy Glasses

A manual PySpark loop gives you cockpit control. You can label each iteration:

# Programmatic cockpit control over the driver context  
for i in range(max_layers):  
    spark.sparkContext.setJobDescription(f"Hierarchy Iteration {i}")  
    next_frontier.count()

With native WITH RECURSIVE, you cannot inject a callback between iterations. The loop lives inside UnionLoopExec, so Spark UI visibility becomes less granular.

You can hear the alarm clocks. Spark does not write the date on each one.

When the query finishes, the operator exposes high-level metrics:

override lazy val metrics = Map(  
  "numOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of output rows"),  
  "numIterations" -> SQLMetrics.createMetric(sparkContext, "number of recursive iterations"),  
  "numAnchorOutputRows" -> SQLMetrics.createMetric(sparkContext, "number of anchor output rows"))

Useful? Yes.
Live debugging microscope? No.

💡 The Takeaway: The recursive CTE groundhog has a Fitbit, not a GoPro. Spark can tell you how many steps it took; it does not show you live video of Morning 37 catching fire.

Deconstructing the Theory: Why Manual Loops Crush Native SQL

In our Day 4 lab, a synthetic 488,281-row hierarchy finished in 10.15 seconds with a manual loop and 137.71 seconds with native SQL.

That’s not a universal law. It’s a warning label.

The manual loop won because it had three advantages:

• Active Memory Housekeeping vs. Passive Accumulation: The manual loop can call frontier.unpersist() as soon as a layer is done. Native recursion keeps iteration outputs around so it can build the final union.
• Bypassing the Macro-Union Planning Tax: Native recursion assembles all layers at the end. The manual loop can accumulate state incrementally with .unionByName() and avoid one large delayed assembly step.
• Granular Driver Control: Manual loops can label jobs, tune joins per iteration, checkpoint, unpersist, and print telemetry. Native SQL is cleaner, but the steering wheel is behind glass.

Survival Strategies: Forcing the Visibility

Since the engine room does not give full live telemetry, add your own guardrails.

• The Post-Run Depth Profiler: Always carry depth. After the query, group by depth to see where the tree expanded.
• Bounded Depth Windows: Do not test production data with 500 possible iterations on the first run. Start with WHERE depth < 3, then 5, then wider.
• The PySpark Retro-Loop: If live observability, custom checkpoints, and per-iteration labels matter more than SQL elegance, use a manual loop.

What Spark Should Build Next

UnionLoopExec already tracks iteration-level state internally. Spark should expose more of it.

Something like:

SET spark.sql.recursiveCte.logIterations = true;  
  
-- Expected output:  
-- Recursive CTE loopId=7 iteration=12 rows=183920 duration=14.2s

That would turn recursive CTEs from black-box elegance into production-grade machinery.

🧪 The Observability Lab: Cockpit vs. Cruise Control

Our fourth notebook, day_04__a_candle_in_the_basement.ipynb, runs a synthetic 488,281-record hierarchy across 9 layers.

It compares native recursion against an instrumented manual PySpark loop, focusing on observability, memory cleanup with frontier.unpersist(), incremental .unionByName(), and Spark UI labeling with setJobDescription().

The lesson: native SQL is the clean cruise-control version. Manual loops are uglier, but sometimes you need the cockpit.

---

🧠 Day 5: The Recomputation Trap

Every morning at 06:00, Phil remembers yesterday. Punxsutawney does not.

Native recursive queries in Spark have a similar problem. The active frontier survives from one iteration to the next, but your base table branch may still be evaluated again and again unless you manage it carefully.

That’s where the recomputation trap begins.

The Big Myth: Automatic Persistence

A dangerous myth says: Spark is in-memory, so recursive CTEs must automatically cache everything between iterations. Nope. 🤦

A CTE is not a physical temporary table. It’s query structure. Spark may optimize it, rewrite it, or reuse parts of it, but you should not assume it secretly adds .cache() or .checkpoint() to your source data.

UnionLoopExec manages the recursive frontier internally. It does not magically persist your warehouse table, your giant view, or your expensive cloud object storage scan.

Phil remembers the loop. The town still resets.

Reusing vs. Recalculating: The Core Mechanics

A recursive query has two very different data streams.

• The Frontier (Implicitly Reused): After each recursive step, Spark materializes the active frontier and wraps it as a fresh logical leaf for the next iteration. That is how “yesterday’s rows” become available tomorrow without rebuilding the whole previous plan.
• The Base Table (Potentially Recalculated): The source branch is different. If your recursive step joins against a raw, uncached table or expensive view, Spark may scan and evaluate that branch again at each depth level.

So if your hierarchy runs 20 levels deep, you may not just pay for one traversal. You may pay for the same source-side work 20 times.

The frontier remembers. The town resets.

The Lineage Debt and Driver Latency

UnionLoopExec avoids letting the active frontier plan grow forever by turning each materialized step into a fresh logical leaf. That protects the loop while it’s running.

But the final output is still the accumulated union of all mornings. Spark keeps the iteration outputs in unionChildren, then assembles them at the end with: Union(unionChildren.toSeq).

For shallow trees, fine. For very deep or very wide trees, that final assembly can become expensive for the driver and optimizer. The loop avoids one kind of lineage explosion, but it can still leave a large bill at the end.

The groundhog didn’t forget. He just stacked every diary page on your desk at once.

The Production Tuning Playbook

To keep recursive queries from becoming infrastructure compost, control the data lifecycle yourself.

• Pre-Cache Your Inputs: Don’t point recursion directly at a huge raw table or expensive view. Build a skinny edge table first, cache or persist it, and force evaluation before the recursive SQL runs.
• Keep the Loop Skinny: Carry only IDs, parent IDs, depth, and path/cycle columns through recursion. Join heavy metadata later.
• The Scale Boundary Rule: If your hierarchy is extremely deep, native SQL may stop being the right tool. For long chains requiring custom checkpoint intervals, memory cleanup, or iteration-level telemetry, switch to a manual PySpark loop.

Clean SQL is beautiful. Controlled execution is sometimes more beautiful.

🧪 The Recomputation Lab: Caching the Town, Breaking the Reset

Our fifth notebook, day_05__the_recomputation_trap.ipynb, profiles an expanding 21,845-record hierarchy across 8 layers.

It compares recursion over an uncached source branch against recursion over a pre-cached skinny edge table.

The lesson is simple: recursive CTEs remember the frontier, not your entire town. Cache the streets before making Phil walk them every morning.

---

🥵 Day 6: The JVM Sweats

When Phil travels through time, he should not drag an ever-growing trunk of historical baggage across timelines. But inside Spark, recursive queries can do exactly that if every iteration carries wide rows, expanding arrays, and metadata nobody needs yet.

The Silent Weight: Wide Rows and Expanding Arrays

Enterprise tables are naturally wide. A corporate employee table may contain IDs, managers, names, departments, compensation bands, locations, audit columns, JSON blobs, and enough HR metadata to make the JVM request therapy.

Passing that full warehouse view into a recursive CTE is usually a mistake.

In a normal query, extra columns add cost. In an iterative query, that cost repeats across layers. Every recursive step carries the payload forward through joins, unions, serialization, and intermediate state.

Then comes the cycle tracker:

-- The Cycle Tracker  
concat(er.path, array(e.employee_id)) AS path

At depth 0, path contains one ID. At depth 8, it contains a trail of ancestors. Across millions of rows, those arrays become real memory objects, not cute little SQL decorations.

The Invisible Crash: Serialization and GC Get Ugly

When wide rows meet expanding path arrays, the JVM starts sweating.

• Brutal Object Serialization Overhead: Spark must repeatedly serialize and deserialize intermediate data. Wide rows and growing arrays increase CPU cost, memory pressure, and shuffle payload size.
• Garbage Collection Pressure: Each iteration can create many temporary objects: arrays, rows, join outputs, shuffle records. Under load, GC pauses get longer, executors slow down, and your “elegant SQL query” starts sounding like a haunted dishwasher.

The crash may not look like one clean error. It may look like slow stages, disk spill, executor churn, or logs full of GC warnings.

The Playbook Rule: Cache the Skinny Source, Not the Warehouse

Do not run the recursive loop over the full warehouse layout. Split the job into two phases.

• Isolate the Skinny Profile: Before recursion, project only the structural columns: employee_id, manager_id, and any minimal guardrails like depth or path.
• Pin the Skinny Core: Cache or persist that narrow relation before the recursive query. The loop should process lightweight IDs, not the entire HR department.
• The Final Enrichment Join: After recursion finishes, join the resolved hierarchy back to the wide table once to add names, departments, descriptions, and metadata.

🦫 The Groundhog Rule: Don’t make Phil carry the whole town through the time loop. Let him solve the maze first, then dress him in the warehouse suit at the exit.

🧪 The Heap Lab: Slimming the Baggage, Saving the Heap

Our sixth notebook, day_06__the_jvm_sweats.ipynb, compares recursion over a 52-column corporate dataset against an optimized skinny-core version.

The lab measures the cost of carrying passive metadata through iterative boundaries, then shows the safer pattern: recurse over employee_id and manager_id, cache the lean core, and enrich only after the hierarchy is resolved.

---

🦦 Day 64: The Relational Groundhog Day Manifesto

The alarm clock blares: Then put your little hand in mine…
Rise and shine, camper. It is 6:00 AM again.

You are trapped inside a local data engine, resolving a lineage tree where records point to records, which point to more records, until your laptop steps into the freezing slush puddle outside the diner.

Let’s break the spell.

The Puddle: Single-Node Shuffle Purgatory

A common trap is assuming that “single-node” means “no data movement.”

😫Nope.

Even on one machine, joins still need rows with matching keys to meet. If those rows live in different partitions, the engine shuffles them. Locally, that still means work:

• The Serialization Penalty: rows are packed into binary form.
• Partition Bucket Sorting: keys are hashed into buckets.
• Local Disk Churn: temporary shuffle files may hit scratch storage.
• Deserialization: data is unpacked and reloaded for the next stage.

A linear loop repeats this ritual every iteration. A 64-link chain can mean 64 rounds of packing, sorting, writing, reading, and unpacking.

That’s not recursion. That’s cardio for your SSD.

Walking vs. Teleporting: The Logarithmic Lifeline

The core question is simple: Are you walking link by link, or teleporting?

🚶🏻‍♀️‍➡️ The Linear Walk: Link 1 finds Link 2. Then Link 2 finds Link 3. To resolve 64 layers, you may need 64 passes. Work scales with depth.

🏃🏻‍♀️‍➡️ Logarithmic Pointer Jumping: Now give every pointer magic boots.

      Loop 1: jump 2 links.  
      Loop 2: jump 4 links.  
      Loop 3: jump 8 links.  
      Loop 4: jump 16 links.  
      Loop 5: jump 32 links.  
      Loop 6: jump 64 links.

By repeatedly joining the active pointer table with itself, you update many targets at once. A 64-step chain can collapse into 6 rounds.

That’s the difference between walking to Punxsutawney and folding the map.

The Database “No Selfies” Rule

So why not using this technique to write it all as one clean recursive CTE?

Because pointer jumping needs the recursive state to join against itself inside the recursive step. Many recursive SQL implementations restrict how the recursive term can reference itself, and Spark’s recursive CTE model is built around feeding the previous frontier into the next step, not taking a hall-of-mirrors selfie.

🪞 The Hall-of-Mirrors Paradox: The planner does not want the recursive table reading itself from both sides while it is still being produced. That kind of live self-reference turns a neat loop into a planning nightmare.

So native recursive CTEs are great for linear traversal. They are not a free pass to implement every graph algorithm in one magical SQL spell.

The Hybrid Cheat Code

The workaround is a hybrid design. Use a short manual loop first to perform the big logarithmic jumps. Each step materializes a new temporary state, so self-joins are legal and controlled.

Once the long chains are compressed, hand the smaller remaining problem back to a native recursive CTE for the final cleanup.

Manual jump first. Declarative sweep later.

The Driver’s Ghost: Plan Lineage Explosion

Lazy planners build a recipe before they cook. A manual loop with dozens of joins can create a monstrous logical plan: nested joins, unions, repartitions, and historical baggage stacked like cursed paperwork.

By iteration 45, the driver may be trying to optimize a novel.

The cure is lineage truncation: force the engine to materialize state at intervals, save the current result, and burn the old instruction history before the next loop begins.

Sometimes the best optimization is telling Spark to forget.

Escaping the Data Skew Black Hole

Even after resolving endpoints, skew can still punch you in the face.

In many lineage datasets, most rows are boring: unchanged, default, or terminal. Millions may share the same placeholder target like 0, NULL, or UNKNOWN.

Run a heavy window function over that key, and one unlucky partition gets buried.

Normal Rows  
 ⤷ One giant default key  
    ⤷ 💥 Overload & Disk Spill  
Complex Chains  
 ⤷ Smaller distributed keys  
    ⤷ Waiting politely

Now your CPU cores are not a team. They are one exhausted intern and eleven spectators.

The fix is divide and conquer:

• The Linear Track: isolate unchanged rows and stamp their final values directly. No recursive traversal. No expensive window. No drama.
• The Complex Track: send only true historical chains into the heavy recursive or ranking logic.
• The Consolidation: glue both tracks back together at the end.

Keep boring rows away from expensive machinery, and the engine stops turning one default key into a black hole.

🧪 The Manifesto Lab: Escaping Shuffle Purgatory

Our last notebook, day_64__the_relational_groundhog_day_manifesto.ipynb, benchmarks four approaches on a 64-link lineage chain: native recursive CTE, manual key-aligned linear loop, logarithmic pointer jumping, and a hybrid pre-jump plus recursive cleanup.

The goal is not to declare one winner forever. It is to expose the physics: linear recursion walks, pointer jumping teleports, lineage can haunt the driver, and skew can still ruin breakfast.

Shuffle metrics per case execution.

---

The Groundhog Insights Calendar.

---

🧭The Ultimate Survival Compass for Recursive CTE Travelers in Apache Spark

If you made it this far, you now know the truth: Apache Spark 4.1.0 and Databricks Runtime 17.x made the groundhog open source. With native WITH RECURSIVE and UnionLoopExec, Spark can finally trace hierarchies directly in SQL without forcing every engineer into a caffeine-powered manual loop.

But recursive CTEs remove the ugly loop from your notebook. They do not remove the loop from Spark’s life.

Give the engine a bad join, dirty key, bloated row, or accidental cycle, and it will not just relive February 2nd. It will invoice you for it.

The Big Wins: Why This Matters

Before native recursion, unknown-depth hierarchies meant manual loops, fixed-depth LEFT JOIN chains, or external graph tooling.

• Clean Expressiveness: You can now express the real problem: start from an anchor, repeatedly find the next frontier, and stop when no new rows appear.
• Legacy Migrations Made Easier: Org charts, folder trees, BOMs, permission inheritance, and lineage chains can move to Spark SQL with less structural rewriting.
• A True Physical Home: This is not just parser sugar. Spark lowers recursive CTEs into dedicated recursive planning and execution machinery, including UnionLoopExec.

The Harsh Realities: What Isn’t Magically Fixed

Recursive SQL makes recursion expressible. It does not make it cheap.

• Repeated Join Overhead: If your recursive step joins a huge source table, that work can happen again at every depth.
• “By ID” Does Not Mean Shuffle-Free: A logical key match is not a physical layout guarantee. Spark may still need exchanges, shuffles, repartitioning, and AQE decisions.
• No Automatic Checkpointing: Native recursive CTEs do not give you user-controlled checkpoint intervals for intermediate states. Deep chains can still create recovery and planning pressure.
• Driver Planning Pressure: Large accumulated plans and outputs can stress the driver, especially when many recursive layers must be assembled at the end.

The Dashboard: Config Knobs and Guardrails

Manage the bouncers before opening the club.

• MAX RECURSION LEVEL: Default is 100. It stops runaway depth with a recursion-level error when the loop goes too far.
• Result Row Limit: Default accumulated result limit is 1,000,000 rows. It caps the total rows produced across recursive iterations.
• LIMIT ALL: In Databricks Runtime 17.2+, this removes the row limit for legitimate large traversals. Do not invite infinite recursion unless you know exactly who is coming to breakfast.

Normal Spark levers still matter: spark.sql.shuffle.partitions, AQE settings, memory tuning, cache strategy, and input layout.

The Pitfalls: Where the Groundhog Bites

1. Sibling Join Explosions. For descendants, join child.parent_id = frontier.id. If you join child.parent_id = frontier.parent_id, you find siblings, then siblings of siblings, until Spark accidentally invents LinkedIn.
2. Path Arrays Becoming Payload Monsters. Path arrays are great for cycle detection, but they grow with depth. Across millions of rows, that means real serialization and memory cost.
3. The Recursive SELECT *. Dragging text columns, audit fields, JSON blobs, and nested structs through every iteration is how elegant SQL becomes a JVM sauna.
4. Dirty Supernodes. Placeholder parents like NULL, UNKNOWN, ALL, or MISSING can become organizational black holes that explode row counts.

The Blueprint: Rules for Survival

• Rule 1: Always Add Depth. Start with 0 AS depth, increment with depth + 1, and profile results by depth after the run.
• Rule 2: Start Narrow, Enrich Later. Recurse over IDs, parent pointers, depth, and path. Join heavy metadata only after traversal finishes.
• Rule 3: Guard Cycles Explicitly. Do not rely on max recursion as business logic. Add a path check: 
   WHERE NOT array_contains(walk.path_history, edge.destination_id).
• Rule 4: Cache the Slim Edge Table, Not the Universe. Build a small id / parent_id relation, cache it, and make the recursive step read that instead of a full warehouse view.
• Rule 5: Track Accumulated Rows. Recursive CTEs return the accumulated UNION ALL, not just the final frontier. A small layer at depth 40 can still sit on top of a giant historical pile.

The Decision Matrix: Choosing the Right Tool

Do not treat recursive SQL as a hammer for every loop.

• Choose Native Recursive CTEs when: readability matters, depth is moderate, data is tree-shaped or DAG-like, and SQL migration compatibility is important.
• Choose Manual DataFrame Loops when: you need per-iteration partition control, checkpointing, cache cleanup, custom convergence, or live telemetry.
• Choose Graph Tools when: you are running PageRank, connected components, large graph traversal, or unbounded message-passing algorithms.

🪄 Recursive CTEs are a beautiful new spell. Just remember: the spell still runs on a cluster, and the groundhog still charges by the morning.

⚡ CTE = Can’t Thank Enough

Special thanks to Alfonso Roa for bringing to my attention that Databricks had open-sourced recursive CTEs into Apache Spark.

And thanks to Databricks for doing it.

Now that the recursive groundhog has been released into the open-source forest, we are politely placing one more coin in the fountain: Photon next?

---

That was Part 2 of 10 about Spark 4.x new features.

Tomorrow we change the mood: less time loop, more melody; less basement machinery, more love letters from the engine.

See you tomorrow for Part 3. 🦫⏰🫶🏻

---

“Fill your table with junk (Fill your table with junk)
 Spark a recursive CTE till it’s stuck (Spark a recursive CTE till it’s stuck)”
(adapted from “Self Destrukt” by Chill Bump)

---

Recursive CTE Survival Camp.