What you will be able to do
- Tell the Databricks SQL UI cache, local and remote result caches, and disk cache apart by scope, lifetime and invalidation
- Write queries that can benefit from the result cache, and turn caching off only for benchmarking
- Explain why an AI/BI dashboard can show stale data and how to refresh it reliably
- Recognise a cache hit when the query profile is missing, and force a real execution
1.Why caching matters and the SQL UI cache
Caching avoids computing or fetching the same data more than once. Databricks SQL says this can significantly speed up queries and reduce warehouse usage, which lowers cost. Repeated queries benefit most, because the system can return a stored result instead of computing it again. Databricks SQL has several caching layers. Each one is defined by three things: what it stores, how long it keeps it, and what clears it.
The first layer is the Databricks SQL UI cache. It caches query results and SQL editor visualizations for each user. When you open a SQL query or a legacy SQL dashboard, it shows the most recent result, including results from scheduled runs, so you see output without waiting. Entries last at most 7 days and are invalidated once the underlying tables are updated. To remove a stored result, rerun the query, which replaces the old result. The UI cache does not apply to AI/BI dashboards, which have their own caching.
Checkpoint 1 of 7· Check yourself
Which statement about the Databricks SQL UI cache is correct?
The UI cache is per user, lasts at most 7 days, is cleared when tables update, and does not apply to AI/BI dashboards.
“The Databricks SQL UI cache has at most a 7-day life cycle.”Source: docs.databricks.com
Sources1
2.The query result cache: local and remote
The result cache stores query results for all queries that run through SQL warehouses. It has two parts. The local cache is in memory and keeps results for the cluster's lifetime or until it is full, whichever comes first. When the cluster stops or restarts, it is cleared.
The remote result cache is available only on serverless. It stores results as workspace system data and is shared by all warehouses in the workspace. Reading from it still requires a running warehouse. It applies to queries from ODBC/JDBC clients and the SQL Statement API. Both the local and remote caches keep entries for 24 hours from when they are stored, and both are invalidated when the underlying tables are updated.
Checkpoint 2 of 7· Put it in order
Put the steps a SQL warehouse follows to answer a query in order.
- 1.Look in the remote result cache if necessary
- 2.Execute the query, because neither cache holds the result
- 3.Look in the cluster's local cache
The warehouse checks the local cache first, then the remote cache. It runs the query only when both miss.
“When processing a query, a cluster first looks in its local cache and then looks in the remote result cache if necessary.”Source: docs.databricks.com
The session parameter USE_CACHED_RESULT controls whether results are reused, and it defaults to TRUE. To get cache hits, write deterministic queries: a predicate such as = NOW() prevents reuse. If you are benchmarking a query change, cached results will hide the real cost, so run SET use_cached_result = false. Databricks says to use this setting only for testing or benchmarking.
Checkpoint 3 of 7· Exam question
An analyst notices a scheduled dashboard query that used to finish in a few seconds now regularly takes several minutes, and the underlying table has grown substantially over the past month. Before changing anything, the analyst wants to confirm where the extra time is actually being spent. What should the analyst do first?
Correct answer: A — Open the slow run in Query History and inspect its Query Profile to see the time breakdown across stages, such as scanning, shuffling, or spilling to disk.
- A. Query Profile, reached from the query's entry in Query History, breaks execution down into stages like scan, shuffle, and spill so the analyst can see exactly where time is going before making any change.
- B. Adding a `LIMIT` changes what the query computes and returns, so its runtime is not representative of the original query's bottleneck and does not diagnose anything.
- C. Restarting the warehouse clears in-memory state but is not a diagnostic step, and a cold cluster is only one of many possible causes of slower execution over time.
- D. Re-typing identical SQL in a new tab executes the same logical query against the same growing table and provides no additional insight into where time is spent.
3.The disk cache
The result cache stores final answers. The disk cache stores the input data. It keeps copies of remote Parquet files, including Delta tables, on the compute nodes' local SSDs in a fast intermediate format. Later reads of the same data then come from local disk. Files are cached automatically when they are fetched. The disk cache also detects when files are created, deleted, modified or overwritten and invalidates stale entries, so you never have to clear it yourself. Like the local result cache, it is cleared when the cluster stops or restarts. It used to be called the Delta cache or DBIO cache. In SQL warehouses and Databricks Runtime 14.2 and above, the CACHE SELECT command is ignored.
| Cache | Scope | Lifetime | Cleared by |
|---|---|---|---|
| Databricks SQL UI cache | Per user, query results and editor visualizations | At most 7 days | Rerunning the query; updates to underlying tables |
| Local result cache | Per cluster, in memory | Cluster lifetime or until full, up to 24 hours | Cluster stop or restart; table updates |
| Remote result cache | Serverless only, shared across warehouses in the workspace | 24 hours from cache entry | Table updates (not warehouse restarts) |
| Disk cache | Local SSD on compute nodes, data files | Same as the local result cache | Cluster stop or restart; changes to data files |
Checkpoint 4 of 7· Match them up
Match each cache to the behaviour that sets it apart.
Tap a term, then the definition that fits it.
Each layer stores something different with a different lifetime. Only the remote result cache outlasts the warehouse.
“The disk cache shares the same lifecycle characteristics as the local result cache.”Source: docs.databricks.com
On classic compute, the easiest setup is a worker type with SSD volumes, which comes configured for disk caching. You can tune how much local storage it uses with Spark settings at cluster creation, and check whether it is on with the command below.
spark.conf.get("spark.databricks.io.cache.enabled")Checkpoint 5 of 7· Fill the gap
Which setting reserves disk space on each node for cached data?
spark.databricks.io.cache. ? 50g
spark.databricks.io.cache.maxMetaDataCache 1g
spark.databricks.io.cache.compression.enabled falsemaxDiskUsage sets the disk space per node for cached data. maxMetaDataCache does the same for cached metadata.
Source: docs.databricks.com4.Dashboard caching and recognising cache hits
AI/BI dashboards keep their own 24-hour result cache, on a best-effort basis. A dashboard checks this cache first, then the general query result cache. The query result cache never returns stale data. The dashboard cache can, because a data change does not clear it. The only reliable way to refresh it is a dashboard schedule. Each scheduled run also fills the query result cache, which speeds up the first load for viewers. Serving from the dashboard cache does not start the SQL warehouse. Also note that current_timestamp() does not invalidate the dashboard cache, but it does invalidate the query result cache.
Checkpoint 6 of 7· Exam question
A team runs the same reporting query each morning against a serverless SQL warehouse that Databricks automatically stops overnight due to inactivity. The underlying table has not changed since the previous run. When the team runs the identical query again after the warehouse restarts, the result appears almost instantly with no new compute activity. What explains this behavior?
Correct answer: A — The remote result cache is stored outside the cluster and persists across a warehouse restart, so the identical query reuses the earlier computed result.
- A. The remote result cache is a workspace-level store separate from any single cluster, has a lifecycle of up to 24 hours, and survives a warehouse being stopped and restarted, so an unchanged identical query returns the cached result immediately.
- B. The disk cache lives on local SSD attached to compute nodes and is cleared when a cluster or warehouse restarts, so it could not have survived the overnight stop to serve this result.
- C. Running a query in the SQL editor does not create a materialized view of the result; no persistent view object is created just by executing a SELECT statement.
- D. A stopped serverless warehouse is not still running underneath the billing layer; the near-instant response comes from the persistent remote result cache, not from hidden active compute.
Caching changes what you see when you investigate a query. If you open a run and get *Query profile is not available*, the result probably came from the query cache and nothing was executed to profile. To force a real execution, make a trivial change to the query, such as changing or removing the LIMIT. In the system table, from_result_cache confirms the hit.
Checkpoint 7 of 7· Check yourself
You open Query History to profile a fast query and see 'Query profile is not available'. What is the most likely cause, and what is the fix?
Queries served from the cache have no profile. A trivial change gets around the cache, so the query actually executes.
“A query profile is not available for queries that run from the query cache.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Stopping a SQL warehouse clears every result cache, so the first query after a restart always recomputes.Why is that wrong?
Only the local in-memory cache is lost. The serverless remote result cache stores results as workspace system data and survives restarts for 24 hours.
Covered in The query result cache: local and remote
2.An AI/BI dashboard always shows current data because cache entries are cleared when the table changes.Why is that wrong?
That is true of the query result cache, not the dashboard cache. The dashboard cache can serve results up to 24 hours old; use a schedule to refresh it reliably.
Covered in Dashboard caching and recognising cache hits
3.Running CACHE SELECT on a SQL warehouse preloads data into the disk cache.Why is that wrong?
On SQL warehouses and Databricks Runtime 14.2 and above, the command is ignored and an improved automatic caching algorithm is used instead.
Covered in The disk cache
4.A missing query profile means the query failed or you lack permissions.Why is that wrong?
Queries answered from the query cache have no profile. Make a trivial change, such as editing the LIMIT, to force a real execution.
Covered in Dashboard caching and recognising cache hits
Practise it for real
See the result cache work, then turn it off for a fair benchmark
1.In the SQL editor on a SQL warehouse, run SET use_cached_result = true; and then run SELECT count(1) FROM <your table>.
Why: Caching is on by default; setting it explicitly makes the starting state clear.
You should see: The query executes normally and returns a count.
2.Run exactly the same SELECT again without changing the table.
Why: A repeated query over unchanged tables can reuse the stored result.
You should see: The result comes back from the cache, and opening its query profile shows 'Query profile is not available'.
3.Change or remove a LIMIT (or make another trivial edit) and run it again.
Why: A trivial change gets around the query cache, so the query actually executes.
You should see: A query profile is now available for the run.
4.Run SET use_cached_result = false; and rerun the original SELECT while comparing timings.
Why: Benchmarks should measure real execution, and Databricks reserves this setting for testing or benchmarking.
You should see: Every run executes, so its duration reflects the actual cost.
Stuck? Get a nudge
If you have access to system.query.history, wait about an hour and filter on from_result_cache = TRUE to find the cached run and its cache_origin_statement_id.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“caching can significantly speed up query execution and minimize warehouse usage, resulting in lower costs and more efficient resource utilization.”
↩︎ Why caching matters and the SQL UI cache“The Databricks SQL UI cache does not apply to AI/BI dashboards (formerly Lakeview dashboards).”
↩︎ Why caching matters and the SQL UI cache“You can delete query results by re-running the query that you no longer want to be stored.”
↩︎ Why caching matters and the SQL UI cache“The remote result cache is a serverless-only cache system that retains query results by persisting them as workspace system data.”
↩︎ The query result cache: local and remote“Both the local and the remote caches have a life cycle of 24 hours, which starts at cache entry.”
↩︎ The query result cache: local and remote“Remote result cache is available for queries using ODBC / JDBC clients and SQL Statement API.”
↩︎ The query result cache: local and remote“You should use this option only in testing or benchmarking.”
↩︎ The query result cache: local and remote“Data is automatically cached when files are fetched, utilizing a fast intermediate format.”
↩︎ The disk cache“The remote result cache persists through the stopping or restarting of a SQL warehouse.”
↩︎ Exam trap 1“The Databricks SQL UI cache has at most a 7-day life cycle.”
↩︎ Checkpoint“The remote result cache persists through the stopping or restarting of a SQL warehouse.”
↩︎ Prediction“When processing a query, a cluster first looks in its local cache and then looks in the remote result cache if necessary.”
↩︎ Checkpoint“The disk cache shares the same lifecycle characteristics as the local result cache.”
↩︎ Checkpoint - 2.
“The system default for this setting is TRUE.”
↩︎ The query result cache: local and remote - 3.https://docs.databricks.com/aws/en/lakehouse-architecture/performance-efficiency/best-practicesOfficial docs
“focus on deterministic queries that for example, don't use predicates such as = NOW()”
↩︎ The query result cache: local and remote - 4.
“Disk caching on Databricks was formerly referred to as the Delta cache and the DBIO cache.”
↩︎ The disk cache“You can write, modify, and delete table data with no need to explicitly invalidate cached data.”
↩︎ The disk cache“In SQL warehouses and Databricks Runtime 14.2 and above, the CACHE SELECT command is ignored.”
↩︎ Exam trap 3 - 5.
“To reliably refresh the dashboard cache, configure a dashboard schedule.”
↩︎ Dashboard caching and recognising cache hits“Serving results from the dashboard cache does not start the SQL warehouse.”
↩︎ Dashboard caching and recognising cache hits“Using current_timestamp() or similar functions in your SQL query does not invalidate the dashboard-level cache.”
↩︎ Dashboard caching and recognising cache hits“The dashboard cache can return results that are up to 24 hours old even when the underlying data has changed”
↩︎ Exam trap 2“a change to the underlying data does not automatically invalidate or refresh the dashboard cache.”
↩︎ Prediction - 6.
“To circumvent the query cache, make a trivial change to the query, such as changing or removing the LIMIT.”
↩︎ Dashboard caching and recognising cache hits“To circumvent the query cache, make a trivial change to the query, such as changing or removing the LIMIT.”
↩︎ Exam trap 4“A query profile is not available for queries that run from the query cache.”
↩︎ Checkpoint