Detecting Photon fallback in-cluster + safe right-sizing from system tables

[Yogasathyandrun]

I'm prototyping a cluster cost / right-sizing advisor and wanted to get a reality-check from people running Databricks at real scale before I sink more time into it.

The main thing I'm chasing is Photon fallback. Photon quietly drops to the JVM on unsupported ops (Python UDFs, some struct/array predicates, a few Delta features), so you keep paying the Photon DBU premium while getting JVM speed, and as far as I can tell it's basically invisible in the UI. Alongside that, the usual right-sizing stuff: over-provisioned workers/driver, idle clusters.

Where I've got to so far:

In-cluster collection — a bundled JVM QueryExecutionListener reads executedPlan and only flags real fallback (mid-plan ColumnarToRow, RowToColumnar round-trips, BatchEvalPython/ArrowEvalPython), ignoring the benign terminal ColumnarToRow that every query ends on. A SparkListener grabs the executor curve and stage/task timing. It self-arms at interpreter startup via a .pth.

System tables for ground truth — node_timeline (CPU/mem, P95), compute.clusters (config/autoscale), billing.usage × list_prices (billed cost).

Engine — classify FIXED/AUTOSCALE, then a step (won't suggest a downsize if peak CPU/mem is high, there's memory spill, or the evidence is thin), then cost (billed when I have the grant, else modeled), and runtime impact as a bounded range rather than a single number.

The stuff I'm actually stuck on:

• Shared & serverless seal the JVM (Spark Connect), so the listener can't attach and I get nothing for Photon on standard/shared access mode. Has anyone found a supported way to see Photon fallback there (query profile API, system.query.history, something on the roadmap), or is in-process really the only path today?
• Rolling the listener out across a fleet — what's the least-intrusive pattern your platform team would actually sign off on? Cluster policy + allowlisted library, a global init script, spark.sql.queryExecutionListeners via policy (and does that even register on shared mode for you)?
• Observation window for right-sizing — to avoid recommending a downsize right before a weekend/month-end batch, what do you trust: a fixed N-day window that's guaranteed to cover the business cycle, or a peak-based sample gate? Curious what's held up in practice.
• system.billing per-cluster attribution — any gotchas joining usage → list_prices (price-window edges, how complete usage_metadata.cluster_id is, serverless SKUs)?

And the two I most want opinions on:

• If you're already fighting cluster cost, what's the part that's still annoying and unsolved? (idle detection, autoscaling tuning, ephemeral job-cluster sprawl, Photon ROI, spot/driver sizing, whatever it is.)
• Does this already overlap something that does it well (system-tables dashboards, Overwatch, a third-party FinOps tool)? And if so, where's the gap that's still worth filling?

Not selling anything, just trying to work out whether I'm reinventing a wheel or if there's a real gap here. Happy to be told it's the former.

[Louis_Frolio · Accepted solution]

Hey @Yogasathyandrun , I did some digging and would like to share some thoughts that you hopefully find useful.

You've mapped the boundary here more accurately than most people do, so let me give you a quick reality check on your four sticking points and then take the "is this a wheel" question at the end.

Start with Photon fallback on shared and serverless. This is a product observability boundary, not a gap in your work. Classic all-purpose and jobs compute expose it in the Spark UI DAG, where Photon shows up orange and Spark shows up blue. SQL warehouses and serverless expose it in the Query Profile, where you get "% of task time in Photon" along with operator coloring and the COVERAGE_PHOTON query-performance insight. The catch is that all of this is surfaced in the UI, not made machine-readable. system.query.history has no Photon column, and it only covers warehouse and serverless usage, admin-only at that. Since Spark Connect seals the JVM on shared and serverless, in-process plan inspection is effectively your only programmatic path, and only on classic dedicated compute. For serverless and SQL, I'd triage from the Query Profile or COVERAGE_PHOTON rather than promise per-statement detection.

On fleet rollout, the least intrusive approach is to pin spark.extraListeners through a cluster policy, ship the JAR from a UC volume that sits on the UC allowlist (you'll need MANAGE ALLOWLIST), and keep com.databricks...DBCEventLoggingListener in the list. OpenLineage uses this same pattern, so you're on well-worn ground. Skip global init scripts. They don't run on standard or serverless, and DBFS init scripts are end-of-life. There's a split worth keeping in mind: your QueryExecutionListener is dedicated-only, since there's no QueryExecution under Spark Connect. A plain SparkListener may travel to shared compute, but test it on your DBR before you count on it. Serverless honors neither.

For the observation window, a hybrid approach works best. The public Databricks system-tables and LakeFlow dashboard uses a rolling 30-day window and reports peak alongside average, which is a sensible default since it spans a full monthly cycle. Gate any downsizing on peak or p95 to p99, not on the mean, and keep hard vetoes for spill, swap, and sustained driver memory pressure. Optimized autoscaling already scales down conservatively, so tuning min and max workers often beats re-sizing a fixed cluster.

Billing attribution has a few traps. Handle the open price window (price_end_time IS NULL OR usage_end_time < price_end_time) or your current usage will drop out. Remember that list_prices is list price, not your contract rate. usage_metadata.cluster_id is null for SQL warehouses and all serverless, so coalesce it with warehouse_id, job_id, and the rest, then branch on billing_origin_product. Serverless emits multiple records, so sum them. Billing is global while the compute tables are regional. And product_features.is_photon is an enablement flag, not a fallback signal, so don't conflate the two.

Now the question you really care about: are you reinventing the wheel? For generic cost and right-sizing, mostly yes. The system-tables usage dashboards, Overwatch (much of which is now reachable through system tables), and the third-party FinOps tools all cover that ground. What none of them cover is Photon fallback and Photon ROI. There's no first-class fallback metric to consume, so everyone skips it. That's the real opening, and it's the strongest part of your prototype. I'd lead with Photon ROI and treat cost and right-sizing as the expected baseline. Just design around the one hard constraint: that signal is only reachable programmatically on classic dedicated compute today.

Hope this helps you focus the build.

Cheers, Lou.

[Yogasathyandrun]

@Louis_Frolio  — really appreciate this, it refocused the whole thing.

Biggest takeaway: I'm flipping the framing to lead with Photon fallback / Photon ROI and treat cost + right-sizing as the baseline, since the system-tables dashboards and Overwatch already cover that. Good to have it confirmed there's no first-class fallback metric — that's the gap I want to own.

I've stopped fighting the dedicated-classic-only constraint and now lean into it: an in-process QueryExecutionListener reads the executed plan, degrades gracefully where the JVM is sealed, and on serverless/SQL I'll point to the Query Profile / COVERAGE_PHOTON instead of promising per-statement detection.

The deploy pattern (extraListeners via cluster policy + JAR from a UC-allowlisted volume, keep DBCEventLoggingListener, skip init scripts) and the billing notes are gold — I'll stop presenting list price as the real number and add the cluster_id coalesce later.

Thanks again, genuinely helpful.

Cheers, Yoga