Photon enabled but a large share of the plan is falling back, cost up and runtime flat

[Islam_hoti]

Hi everyone,

Trying to work out whether this is expected or whether I have misconfigured something.

We enabled Photon on a job cluster running a nightly aggregation over roughly 2TB. The expectation was the usual improvement. What we got instead was runtime essentially unchanged and cost noticeably higher, since Photon carries a DBU multiplier.

Looking at the query profile, a meaningful part of the plan is running outside Photon. I can see the fallback in the plan but I am having trouble turning that observation into an action.

Questions.

What is the most reliable way to identify exactly which operators caused the fallback? I can see that fallback happened, but attributing it to a specific expression in a long query has been guesswork so far.

Are there categories of operation that are known to be unsupported and worth auditing the code for upfront? We use a couple of Python UDFs and some fairly complex nested struct handling, and I suspect one of those is the cause, but I would rather check than guess.

For a plan that partially falls back, is there a rule of thumb for when it is still worth keeping Photon on? At what share of the plan does the multiplier stop paying for itself?

And the practical one: has anyone rewritten a UDF specifically to keep a plan inside Photon, and was the rewrite worth the effort?

Happy to share an anonymised plan if that helps.

Thanks.

[data_pulse]

It's common to assume Photon is something than can be switched on and brute-force better performance with but it really depends on whether the underlying plan is actually compatible to stay in Photon.

In your case, triage where the plan is crossing between columnar Photon execution and the regular Spark/JVM path. In EXPLAIN FORMATTED (df.explain("formatted")) or the Spark UI, look for transitions such as:

PhotonProject
...
ColumnarToRow
  BatchEvalPython
RowToColumnar

ColumnarToRow and RowToColumnar are useful transitions are useful clues as they show where Photon stops being useful for part of the plan, and repeated transitions can add overhead of their own.

So, inspect the python UDFs first thing as Databricks docs also says UDFs not supported in Photon as limitation.

Fancy Rewriting UDF

• Check if spark native built in functions already exists.
• Arrow Optimized Pandas UDF : Not full photo support but reduces transition overhead by processing in columnar batches than row to row
• Rewrite as SQL expressions as it gets full photon benefit: see if the built-ins like named_struct, transform, filter, aggregate can be leveraged to rewrite the UDF.

If the expensive part of your 2TB aggregation is sitting between ColumnarToRow and back, turning Photon on may accelerate some scans/operators around it but leave the main bottleneck untouched. If python UDF is processing most of the 2TB, then rewrite is a worthy option.

There is community post on UDF efficiency which mentions on some ways to rewrite.

Similar Use case:

We had a row-level security filter on a Unity Catalog view using an EXISTS subquery. The initial plan was producing a SortMergeJoin with multiple shuffles/sorts and wasn’t taking advantage of Photon effectively. After refactoring the SQL and improving the table layout/statistics with Liquid Clustering, Deletion Vectors, and ANALYZE TABLE COMPUTE STATISTICS, the resulting plan was much more Photon friendly.

The main lesson was the same: optimize the plan first, then evaluate Photon.

[ThiamLee]

Really useful real-world scenario. I’d be interested to see how others approach pinpointing Photon fallbacks, especially with Python UDFs and nested structs. A before/after comparison of a UDF rewrite would also be a great way to quantify whether the optimization effort actually pays off.

[Khasim_1 · Accepted solution]

Hi @Islam_hoti 

This is a common "Photon realization" moment. Photon is a vectorized execution engine written in C++. When a query falls back to the standard Spark engine (Row-by-Row/JVM-based), you lose the vectorized performance but keep the higher DBU cost, which is the worst of both worlds.

Here is how to tackle your questions with a systematic approach:

1. Identifying the Fallback Operators

You don't have to guess. There are three reliable ways to identify the "fallback" culprit:

• The SQL Query Profile (UI): In the Databricks SQL/Job UI, open the query profile. Operators that are not Photon-accelerated will appear in the plan without the "Photon" label or have a distinct color/prefix. Look for the operator with the highest "wall-clock time" that is missing the Photon tag.
• EXPLAIN EXTENDED / EXPLAIN FORMATTED: Run your query with EXPLAIN FORMATTED. Look for the "Photon" flag in the nodes. If a specific node shows Photon: false, that node is your bottleneck.
• Spark UI (SQL tab): Look at the physical plan. Operators that fall back will often be represented by standard Spark operators (e.g., WholeStageCodegen without a Photon prefix).

1. Known Categories for Fallback

Your intuition is spot on. Photon is optimized for standard SQL and high-performance DataFrame operations, but it struggles with:

• Python UDFs (The #1 suspect): Photon runs in C++. When it hits a Python UDF, it must "hop" the data out of the C++ memory space, into the Python interpreter, and back again. This serialization/deserialization cost is catastrophic for performance. This is almost certainly your primary fallback cause.
• Complex Struct Handling: While Photon handles structs better now, very deep nesting or custom-logic complex struct manipulations often force a fallback.
• Specific String/Regex Functions: Some obscure Java-based regex functions or non-standard string aggregations still trigger a fallback.

1. Rule of Thumb for Photon Value

The DBU multiplier for Photon is usually 2x (depending on your SKU).

• The Metric: If your runtime does not improve by at least 2.5x to 3x on the accelerated portions of your plan, you are net-negative on cost.
• The Ratio: If > 40-50% of your plan (by execution time) is falling back to the JVM, the DBU overhead of the entire cluster being "Photon-enabled" will likely outweigh the gains. In this state, it is almost always cheaper to run on a standard runtime and increase the cluster size.

1. Rewriting UDFs: Is it worth it?

Yes, but not by just tweaking them. To make a UDF Photon-compatible, you have to move away from Python UDFs entirely:

• Pandas UDFs / Vectorized UDFs: If you aren't using these, switch. They operate on batches rather than row-by-row, which is much friendlier to the execution plan.
• Native Spark SQL Functions: This is the "Gold Standard." If you can replace your UDF with a combination of when, coalesce, struct, or array functions, do it. These execute in the vectorized engine and are 10–100x faster than any UDF.
• The "Worth It" test: For a 2TB daily job, yes, it is worth the effort. If a rewrite saves you 20 minutes of runtime, you are saving 20 minutes of cluster uptime daily. Over a year, that is 120 hours of compute savings plus the elimination of the DBU multiplier penalty.

Suggested Action Plan

1. Isolate the UDF: Take one sub-query that uses your Python UDF, run it in a notebook with EXPLAIN FORMATTED, and confirm it's the fallback source.
2. The "Native" Migration: Attempt to express the logic of that UDF using native pyspark.sql.functions. If your logic is too complex for native SQL functions, consider a Scala UDF—they are much closer to the metal and sometimes avoid the "heavy" fallback behavior associated with Python.
3. A/B Test: Keep the cluster cost constant. Run the current "fallback" version vs. a version where you've commented out the UDFs. If the performance gap is massive, the effort to rewrite the UDF is justified.

[saisaranv]

@Islam_hoti Hope you are doing good. 

 Actually we also faced the same thing in our project as customer defined UDF to be use across as per the policy but we explained that Photon is not compatible directly on Python UDF's bcz of the nature of convergence. 

Instead we had went with 2 approaches :

1. directly wrote code in pyspark to handle the UDF functionality which gave us photon compatibility

2. Broken the UDF to smaller components and created UC functions with pyspark mixed with sql, which helped too.

**Note : what kind of libraries that we are using or what kind of operations that we are performing will define the photon compatibility.

[aayush_410]

1. Pinpointing exactly which operator falls back

Don't guess from the query text — go to the Spark UI's SQL/DataFrame tab and look at the query DAG. Photon operators render in orange, standard Spark operators in blue, so you can see visually exactly which nodes fell back instead of inferring it. Pair that with the "Task Time in Photon" vs. total task time metric in the query profile to quantify how much of your runtime is actually accelerated. You can also check the extended explain output — nodes that fell back are tagged with the specific reason (unsupported expression, UDF, incompatible sort order, etc.), so you don't have to guess which expression triggered it.

2. Known unsupported categories to audit

Your instinct is right — check the Python UDFs first. Python and Pandas UDFs of any flavor always fall back, since they require a row-by-row conversion at the Photon/JVM boundary — this is the single most common cause of underwhelming Photon results, more so than nested struct handling. For the struct/map side: some operations like transform_keys, transform_values, and array-aggregate were only added to Photon's native path in more recent DBR versions, so it's worth checking your exact runtime version rather than assuming they're covered. Complex regex with non-literal patterns or lookahead/lookbehind also falls back if that's in play anywhere.

3. Rule of thumb on when the multiplier still pays off

There isn't a clean universal percentage — don't chase a rule of thumb here, measure directly instead. Look at the "Task Time in Photon" fraction in your query profile, then run the job with Photon on and off and compare actual DBU totals for your specific workload. As a general pattern: scans, filters, aggregations, joins, and sorts clear the bar easily since they're natively covered. UDF-heavy stages, very short jobs, and wide-shuffle-dominated workloads often don't, because the JVM↔native conversion overhead at the boundary eats the gain — which sounds like what's happening in your case given the unchanged runtime plus multiplier cost.

4. Is rewriting the UDF worth it?

In my experience, yes — if the UDF sits in the hot path (applied to most rows, especially before or during your aggregation), swapping it for a built-in Spark SQL function, or the native transform/aggregate higher-order functions for your struct handling, is usually worth the effort. It keeps the whole stage on the native path instead of just replacing the UDF call in isolation. It's generally not worth the rewrite if the UDF only touches your already-reduced post-aggregation output — row counts there are small enough that the fallback cost is negligible regardless.

Given a 2TB nightly job with runtime unchanged and a real DBU hit, I'd bet your Python UDFs are the dominant cause here rather than the struct handling — worth checking the DAG coloring around those specific stages first before digging into the struct expressions.

[ThiamLee]

Great questions! Would love to hear from someone who’s dealt with Photon fallbacks, especially around Python UDFs. Curious to know if rewriting them actually made a noticeable difference in runtime and cost.