Databricks SQL Caching: Result Cache / Disk Cache / Materialized Views

Have you ever noticed that the same Databricks SQL query is sometimes fast and sometimes slow? Most of that variability comes from caching behavior. Databricks SQL has three distinct cache layers, each operating at a different layer with its own retention and invalidation rules. This article breaks down all three so you can run queries efficiently and keep costs under control.

Comparison Table for the Three Cache Layers

Result Cache in Detail

The Result Cache returns the previous result as-is when the exact same SQL text is re-executed on the same warehouse. It skips compilation, optimization, and data scanning entirely, so a cache hit returns in tens of milliseconds.

Hit Conditions

• The SQL text matches exactly (whitespace and case differences are significant)
• The source table data has not changed (detected via the Delta log version)
• The query contains no non-deterministic functions (CURRENT_TIMESTAMP, rand, uuid, etc.)
• Less than 24 hours have elapsed since the previous cache entry
• The query is run on the same warehouse

When the Cache Does Not Hit

• Ad-hoc queries where WHERE clause parameters differ each time
• After INSERT/UPDATE/MERGE/DELETE has been applied to the source table
• When the same query is run from a different warehouse
• Queries that include CURRENT_DATE() (the cache is invalidated when the date rolls over)

Disk Cache in Detail

The Disk Cache stores Parquet files fetched from remote storage (S3 / ADLS / GCS) on the local SSD of the warehouse node. While the Result Cache is a query-result-level cache, the Disk Cache is a data-file-level cache.

How It Works

1. When a query runs, the required Parquet files are fetched from remote storage
2. The fetched files are cached on the local SSD
3. Subsequent queries that need the same files skip the remote I/O and read directly from the SSD
4. When the SSD reaches capacity, entries are evicted using LRU

When the Cache Is Invalidated

• OPTIMIZE (file compaction) is run on the table
• VACUUM runs and removes old files
• A new version is added to the Delta log via INSERT, MERGE, etc.
• The warehouse is stopped or restarted

Materialized Views in Detail

A Materialized View pre-computes query results and stores them as a Delta table. Unlike the Result Cache and Disk Cache, which are tied to the warehouse lifecycle, a Materialized View is persisted in cloud storage and is therefore unaffected by warehouse stops or restarts.

-- Create a Materialized View
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  region,
  product_category,
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount
FROM catalog.schema.orders
GROUP BY region, product_category, DATE_TRUNC('month', order_date);

-- Manual refresh
REFRESH MATERIALIZED VIEW sales_summary;

-- Configure automatic refresh
ALTER MATERIALIZED VIEW sales_summary
SET TBLPROPERTIES (
  'pipelines.autoOptimize.managed' = 'true'
);

Choosing Between Result Cache and Materialized View

• The same query is run frequently → Result Cache is enough (no configuration required)
• Different query patterns but the same aggregate results are needed → Materialized View
• The source table changes often and fresh results are required → Result Cache (auto-invalidated on data changes)
• The source table changes rarely and you want to pre-compute heavy aggregations → Materialized View

Cache Warm-Up Strategies

Stopping or restarting a warehouse wipes both the Disk Cache and the Result Cache. The following strategies help avoid the classic 'only the first query is slow' problem when dashboards see a morning access spike.

Cost Optimization Tips

• Extending Auto Stop helps caching but incurs idle DBU costs → tune the value based on the workload pattern
• Splitting across many small warehouses fragments the cache → consolidate onto the same warehouse where possible
• Schedule OPTIMIZE outside of dashboard hours → run OPTIMIZE overnight, then run warm-up queries in the morning
• Weigh the refresh cost of Materialized Views against the query speedup → match the refresh frequency to the source table's update frequency

Points the Exam Tests

• "Why is the same SELECT slower after an INSERT into the table?" → The Result Cache was invalidated
• "Why are queries slow right after a warehouse restart?" → Both the Disk Cache and the Result Cache were lost
• "Why are queries temporarily slow after OPTIMIZE?" → The Disk Cache is invalidated (because files are restructured)
• "How can you serve fast aggregate results regardless of warehouse stops?" → Materialized View
• "What is the difference between Result Cache and Disk Cache?" → Result Cache reuses result sets; Disk Cache keeps data files on SSD

Check with a Sample Question

Frequently Asked Questions