Photon for ETL

Marcus Tran|Jun 25, 2025|6 min read

Photon for ETL: When It Helps, When It Doesn't, and How to Prove It

Introduction - "Why is my fast SQL still slow?"

You tuned partitions, compacted Delta files, and rewrote that monstrous MERGE. Still, your nightly ETL drifts into business hours. Databricks keeps mentioning Photon - its vectorized query engine that promises big gains with no code changes. Does it actually help your pipelines? This article gives you a blunt rubric: when Photon shines, when it doesn't, and a simple, defensible way to prove it for your workload.

What Photon Is (and isn't)

  • Photon is Databricks' vectorized C++ execution engine for SQL/DataFrame operators. It plugs under Spark APIs to speed up scans, filters, joins, aggregates, and writes - especially on Delta/Parquet. Databricks
  • It's available in SQL Warehouses (Serverless/Pro/Classic with different performance features) and on compute clusters when the runtime engine is set to PHOTON. Databricks Documentation+2Microsoft Learn+2
  • Key caveats: Photon does not execute UDFs (Scala/Python/pandas) and doesn't support RDD/Dataset APIs; those parts run in the regular Spark engine. Very short queries (<~2s) usually won't benefit. Databricks Documentation

When Photon Helps ETL

Photon typically pays off when your pipeline is dominated by vectorizable operators on columnar formats:

  • Wide scans + selective filters on Delta/Parquet with decent column pruning.
  • Hash joins on well-distributed keys with stats available (CBO + AQE).
  • GroupBy/aggregations over tens to hundreds of millions of rows.
  • Upserts and deletes where metadata pruning can minimize I/O (e.g., MERGE INTO with dynamic file pruning). Databricks Documentation
  • SQL Warehouses using Photon plus Predictive I/O for faster selective scans. Databricks Documentation+1

Why: Photon's vectorized operators and native I/O paths reduce CPU overhead and amplify cache friendliness on columnar data.

When Photon Doesn't (or Barely) Help

  • Heavy UDF logic (Python/Scala/pandas UDFs), RDD/Dataset steps, or custom serialization paths. These won't run inside Photon. Databricks Documentation
  • Tiny/interactive queries that already finish in ~<2 seconds - startup/coordination costs dominate. Databricks Documentation
  • Skewed joins or poorly partitioned Delta tables where the bottleneck is data layout, not compute.
  • Over-fragmented tables (many small files) where file listing/metadata dominates; fix table layout first using optimized writes/auto compaction. Delta Lake+2Microsoft Learn+2
  • Workloads gated by external systems (slow source APIs, JDBC sinks) where engine speed isn't the limit.

Quick Decision Table

ScenarioPhoton ImpactWhat to Check First
SQL/DataFrame-heavy ETL on Delta/Parquet with large scans, joins, aggsHighStats, partitioning, file sizes, AQE
Pipelines dominated by Python/Scala UDFs or RDDsLowRefactor to SQL/native functions; minimize UDFs
MERGE/UPDATE/DELETE on large Delta tablesMedium - HighDynamic file pruning; Z-ordering where appropriate
Many tiny queriesLowBatch operations; combine steps
Table has thousands of small filesLow until fixedEnable optimized writes + auto compaction
External sink bottlenecks (JDBC APIs)LowBulk operations, partitioned writes, concurrency

(See Delta/Databricks optimization guidelines for file sizing and compaction best practices. Databricks Documentation+1)

How to Enable Photon (Safely)

  • SQL Warehouses: Choose Serverless/Pro (Photon built-in) or verify Photon is enabled for your warehouse type. Microsoft Learn
  • Jobs/Clusters: Set runtime_engine = PHOTON on supported DBR versions/instances (Photon consumes DBUs at a different rate; confirm pricing implications). Microsoft Learn

A Repeatable Benchmark to Prove the Value

You need an A/B that stands up to scrutiny. Do this:

  1. Create two identical environments
    • A (Baseline): Same DBR version, same node types, Photon OFF.
    • B (Treatment): Same as A, but Photon ON (or a Warehouse with Photon).
      Keep autoscaling off; fix cluster size to remove variance. Microsoft Learn
  2. Warm the pipeline
    • Run once to prime metadata/caches. Discard results.
  3. Run a representative ETL bundle
    • Include your heaviest SELECT/JOIN/GROUP BY stages and MERGE/DELETE steps.
    • Use the same inputs and parameters in both A and B.
  4. Measure apples-to-apples
    • Capture wall-clock, cost (DBUs x time), bytes read, shuffle read/write, spill, and task time from Query History/metrics UI.
    • For SQL Warehouses, note Predictive I/O and Photon indicators. Databricks Documentation
  5. Run multiple trials (≥3)
    • Report median and standard deviation. Small deltas (<10%) aren't meaningful.
  6. Attribute the wins

Minimal Benchmark Harness (PySpark)

from time import perf_counter
from statistics import median

def run_pipeline():
    # Example: vectorizable ETL with SQL/DataFrames
    spark.sql("""
      CREATE OR REPLACE TEMP VIEW orders AS
      SELECT * FROM delta.`/mnt/bronze/orders`
    """)
    df = (spark.table("orders")
            .filter("order_date >= '2025-01-01'")
            .join(spark.table("dim_customers"), "customer_id")
            .groupBy("country")
            .agg({"amount": "sum"}))
    df.write.mode("overwrite").format("delta").save("/mnt/silver/sales_by_country")

def time_run(n=3):
    times = []
    for _ in range(n):
        t0 = perf_counter()
        run_pipeline()
        times.append(perf_counter() - t0)
    return times, median(times)

times, med = time_run(3)
print({"runs": times, "median_sec": med})

Run this notebook unchanged in A and B. Then compare medians and cost. If B (Photon) isn't ≥20-30% faster and cheaper (speedup offsets DBU rate), don't enable it for that job. (Photon DBU consumption differs; confirm in pricing docs.) Microsoft Learn

Real-World Example (SQL)

-- Heavier aggregation that Photon typically accelerates
OPTIMIZE delta.`/mnt/bronze/clicks` ZORDER BY (user_id); -- optional, if licensed

CREATE OR REPLACE TABLE analytics.daily_active_users AS
SELECT
  DATE_TRUNC('day', event_time) AS day,
  COUNT(DISTINCT user_id)       AS dau
FROM delta.`/mnt/bronze/clicks`
WHERE event_time >= DATE_TRUNC('day', NOW()) - INTERVAL 30 DAYS
GROUP BY 1;

On large clicks tables with column pruning and good file sizes, you should see lower CPU time per row scanned and shorter total runtime with Photon vs. non-Photon.

Best Practices Before/While Using Photon

  • Make data layout worth accelerating
    • Enable optimized writes to avoid tiny files; rely on auto compaction. Delta Lake+1
  • Lean on SQL/native functions over UDFs to stay inside Photon's vectorized path. Databricks Documentation
  • Feed the optimizer
  • Design joins sanely
    • Choose join keys with good cardinality/distribution; avoid extreme skew.
  • Watch warehouse features
    • Serverless/Pro Warehouses combine Photon with Predictive I/O; evaluate both together for ETL queries. Microsoft Learn
  • Cost sanity
    • Photon may have a different DBU rate; compute $ per successful run, not just runtime. Microsoft Learn

Common Pitfalls

  • "We turned on Photon and nothing changed."
    Your critical stages were UDF-heavy or I/O bound. Refactor to SQL, fix file sizes, or move logic earlier/later.
  • Benchmarking with mismatched settings.
    Autoscaling, cache state, or skew differed across runs. Lock them down.
  • Over-indexing on a single job.
    Some pipelines benefit; others don't. Classify jobs (SQL-heavy vs. UDF-heavy) and enable Photon only where it pays.

Conclusion & Takeaways

  • Photon is a multiplier, not a silver bullet. It accelerates SQL/DataFrame operators on columnar data; it doesn't speed UDF/RDD logic. Databricks Documentation
  • Prove it with a clean A/B (same DBR, same nodes, fixed size). Track runtime and cost. If you don't see a meaningful, repeatable delta, leave Photon off for that job. Microsoft Learn
  • Fix the basics first - file sizes, compaction, stats - so Photon has something worth accelerating. Databricks Documentation+1

Call to action:
Pick your top 3 longest ETL pipelines. Run the benchmark plan above. Document runtime, DBUs, and operator-level impact. Enable Photon only where data - and dollars - prove it.

Internal link ideas (placeholders)

  • Delta Lake file layout & compaction guide
  • Refactoring UDF-heavy jobs to pure SQL
  • AQE and CBO: getting stable join plans
  • Predictive I/O in SQL Warehouses: what it actually speeds up

Image prompt (for DALL-E/Midjourney)

"A clean, modern data architecture diagram showing two identical ETL pipelines - left without Photon, right with Photon - highlighting vectorized operators (scan, join, aggregate) on Delta tables, with metrics panels comparing runtime and cost. Minimalist, high contrast, 3D isometric style."

Tags

#Databricks #Photon #SparkSQL #ETL #DeltaLake #DataEngineering #Performance #Benchmarking #Lakehouse

Leave a Comment