Analyzing Queries · Topic 1 of 2

Photon, Query History and Caching

Databricks Data Analyst: what Photon speeds up, finding slow queries with query history, the query profile and performance insights, and query caching.

5 min

In a nutshell

Photon is on by default in SQL warehouses and needs no code changes.
Photon skips UDFs, RDD and Dataset APIs and gives no gain on queries under two seconds.
Query history finds the query; the query profile shows which operator was slow.

What it is

Photon is the Databricks native vectorized query engine. It runs SQL and DataFrame work faster than the standard Spark engine with no code changes, and it is on by default in SQL warehouses.

When a query is still slow, query history lists every query with its duration and status, and the query profile shows where the time went. Caching avoids running the same work twice, which cuts latency for repeated dashboard and editor queries.

How it works

Photon

Photon processes data in columnar batches in place of the JVM based Spark execution layer, while the Spark optimizer still plans the query. Existing SQL runs unchanged.

Photon helps	Photon does not help
SQL analytics and BI queries	UDFs, RDD APIs and Dataset APIs
Joins, aggregations, sorts, window functions	Queries that normally finish in under two seconds
Scans and writes of Delta and Parquet	Stateful streaming
ETL, Lakeflow pipelines, stateless streaming

Photon is enabled on SQL warehouses, serverless compute and serverless pipelines. When it meets an operation it does not support, that part falls back to the standard engine and the result stays correct.

Query history

Open Query History from the sidebar. It lists queries run on SQL warehouses, and on serverless compute where enabled, and you can filter by user, date range, compute, duration, status and statement type.

Each entry shows the status (Queued, Running, Finished, Failed or Cancelled), the full statement, the wall clock duration split into scheduling, optimization and execution, and the share of data pruned. You see your own queries; seeing other users' queries needs at least CAN VIEW on the SQL warehouse that ran them (CAN MONITOR also includes it). The same data is in the system table system.query.history, which admins can query to find slow or costly queries at scale.

Query history also saves development time. You can reopen an earlier statement instead of rewriting it, and see at once whether a change made the query faster.

Query profile

From a query in history, click See query profile. The profile shows:

Part	What it tells you
Wall clock duration breakdown	Whether time went to queuing, optimization or execution
Top operators	The operators that took the longest
DAG graph	Each operator with time spent, rows and memory peak
Pruning percentage	How much data the scans skipped
IO	Data read and written

Look for full table scans with low pruning, joins that output far more rows than they read (an exploding join), and expensive shuffles. If the profile says it is not available, the result came from the cache.

Performance insights

Databricks also ranks problems for you as performance insights, shown in query history and in the profile; the exam guide calls this Query Insights. They are ordered by estimated effect on total task time.

Insight	Meaning
Data spill	Data did not fit in memory and was written to disk
Exploding join	A join produced far more rows than its inputs
Missing statistics	The optimizer had no table statistics to plan with
Excessive queue time	The query waited for warehouse capacity
Photon not applied	An operation ran on the standard engine

Optimize in the profile opens Genie Code, which can propose a rewritten query for your approval or explain table and compute changes. Long queue time points to warehouse sizing or scaling, not to the SQL.

Caching

Cache	Scope	Life	Cleared by
Databricks SQL UI cache	Per user, editor results	Up to 7 days	Table update or rerun
Local result cache	One warehouse, in memory	24 hours	Warehouse stop or restart, table update
Remote result cache	All serverless warehouses in the workspace	24 hours	Table update; survives restart
Disk cache	Data files on local SSD	Warehouse lifetime	Warehouse stop; detects changed files

A query checks the local result cache first, then the remote cache, and runs only if neither has a result. Any change to the underlying table invalidates cached results, so the cache never returns stale data. To measure true run time, turn it off for the session.

SET use_cached_result = false;
SELECT region, SUM(amount) FROM main.sales.orders GROUP BY region;

How the exam asks about this

"A dashboard query reran in milliseconds and the profile is not available." It was served from the result cache.
"Find which step of a slow query took the most time." Open the query profile and check Top operators.
"A query uses a Python UDF and Photon gave no speedup." Photon does not support UDFs, so that work runs on the standard engine.

What to remember for the exam

Photon is on by default in SQL warehouses and needs no code changes.
Photon skips UDFs, RDD and Dataset APIs and gives no gain on queries under two seconds.
Query history finds the query; the query profile shows which operator was slow.
Performance insights flag spill, exploding joins, missing statistics, queuing and Photon gaps.
Result caches last 24 hours; the remote result cache is serverless only and survives a restart.
SET use_cached_result = false is for testing and benchmarking only.

Watch out

Do not benchmark a query by running it twice and keeping the faster time. The second run is often a cache hit, so disable the result cache before comparing versions of a query.

Common mistakes

Blaming the SQL for queue time. If the duration is mostly queuing, the warehouse lacked capacity. Scaling or sizing the warehouse fixes it, not a rewrite.

Expecting stale cache results. Cached results are invalidated when the table changes. A cache hit always reflects the current data.

Looking for Photon to fix every query. Very short queries and UDF heavy code see little benefit. The profile shows which operators Photon ran.

Check yourself

Why might a query profile show as not available?
The query was answered from the query result cache, so nothing ran; changing the query forces a fresh run with a profile.
Which cache survives a SQL warehouse restart?
The remote result cache, available on serverless warehouses only, which is shared across the workspace for 24 hours.
What does an exploding join insight mean?
A join produced far more output rows than it read, usually because the join keys are missing or not unique.

Topic 1 of 2 in Analyzing Queries

Test yourself on Analyzing Queries

The full Databricks Data Analyst Associate question bank has 600 questions, with timed practice exams and an explanation for every answer.

Get Full Access Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Databricks. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.