Study guide
7.1 Caching Hierarchy in Databricks SQL
Databricks Certified Data Analyst Associate Exam
Track your progress as you read
Finished sections get a check mark.
1 Platform Overview & Unity Catalog Foundations
1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables
2 Managing & Cleaning Governed Data
2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques
3 Data Ingestion & Import Patterns
3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections
4 Databricks SQL Warehouses & Core Querying
4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL
5 Advanced SQL Authoring & Time Travel
5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel
6 Query Performance Analysis & Photon Engine
6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs
7 Liquid Clustering, Caching & Performance Troubleshooting
7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries
8 AI/BI Dashboards & Visualization Design
8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations
9 Dashboard Interactivity, Publishing & Alerts
9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts
10 Developing & Maintaining AI/BI Genie Spaces
10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance
11 Data Modeling & Medallion Architecture
11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog
12 Securing Data & Unity Catalog Governance
12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks
7.1 Caching Hierarchy in Databricks SQL
Key Takeaways Explain This Topic AI
Databricks SQL implements a 3-tier caching hierarchy: Result Cache (valid for 24 hours), Disk Cache (storing decompressed Parquet files on local worker NVMe SSDs), and Spark Cache (in-memory JVM heap/off-heap storage).
The Result Cache operates on the SQL Warehouse driver node and automatically invalidates whenever an underlying Delta Lake table receives a new commit or after 24 hours of inactivity.
Queries containing non-deterministic functions (such as CURRENT_TIMESTAMP(), RAND(), or NOW()) automatically bypass the Result Cache and force full query execution.
The Disk Cache (formerly Delta Cache) is auto-configured and enabled by default on SQL Warehouses, transpiling remote cloud storage Parquet files into an uncompressed binary format for up to a 10x read speedup.
Spark In-Memory Cache (via CACHE TABLE) is generally discouraged in Databricks SQL Warehouses because it competes for executor RAM, increases Out-Of-Memory risk, and bypasses Photon C++ engine optimizations.
Last updated: July 2026
Caching Hierarchy in Databricks SQL
Exam Focus: Databricks SQL utilizes a multi-tiered caching architecture to dramatically optimize query execution times and reduce network traffic to cloud object storage (Amazon S3, Azure Data Lake Storage Gen2, or Google Cloud Storage). Understanding the distinct operational characteristics, persistence models, invalidation triggers, and configuration options across the Result Cache , Disk Cache (formerly Delta Cache), and Spark Cache is essential for answering performance optimization questions on the Databricks Data Analyst Associate exam.
Overview of the Databricks SQL Caching Layers
Cloud object stores provide virtually unlimited storage capacity at low cost, but reading remote Parquet files over network storage introduces latency. To bridge the latency gap between cloud storage and compute memory, Databricks SQL implements a three-tier caching hierarchy . Each tier operates at a different point in the query execution pipeline, targeting specific types of repetitive workloads.
Caching Tier Storage Location Scope Cached Data Format Invalidation Mechanism Default Status in SQL Warehouses
Result Cache SQL Warehouse Driver / Control Plane SQL Warehouse Final Query Result Set Table mutation, view change, or 24-hr TTL Enabled automatically
Disk Cache Worker Node Local NVMe SSDs Worker Node Cluster Decompressed Binary Parquet Blocks File deletion/modification on storage Enabled automatically
Spark Cache Worker Node RAM (JVM Heap / Off-heap) Cluster Execution Engine Deserialized DataFrames / RDDs Manual unpersist or cluster shutdown Disabled by default (Manual CACHE )
1. Result Cache (Query Result Caching)
The Result Cache (also known as Query Result Caching) is the highest-level caching tier in Databricks SQL. It stores the final computed result set of an executed SQL query directly on the SQL Warehouse driver node.
Key Characteristics & Mechanics
Sub-Second Response Times : When a query hits the Result Cache, the SQL Warehouse bypasses query planning, optimization, and execution on worker nodes entirely, returning the cached result set instantaneously.
Warehouse-Wide Scope : The Result Cache is shared across all users executing queries on the same SQL Warehouse. If Analyst A runs a heavy analytical aggregation query and Analyst B subsequently executes the exact same query on that warehouse, Analyst B instantly receives the cached output.
Time-to-Live (TTL) & Invalidation : Result Cache entries remain valid for up to 24 hours . However, Databricks SQL maintains strict ACID freshness: if any underlying Delta Lake table referenced in the query receives a new commit (such as an INSERT , UPDATE , DELETE , or MERGE operation), the Result Cache entry is automatically invalidated .
Functions That Bypass the Result Cache
Queries that include non-deterministic functions cannot use the Result Cache because their output changes on every invocation. When authoring analytical queries, be aware that the following functions force full re-execution: -- Queries containing non-deterministic functions ALWAYS bypass the Result Cache:
- SELECT customer_id, order_total, CURRENT_TIMESTAMP() AS query_time
- FROM gold_sales
- WHERE order_date = CURRENT_DATE();
-- Using RAND() or UUID() also disables Result Caching:
- SELECT *, RAND() AS sample_weight
- FROM silver_events;
- 2. Disk Cache (Formerly Delta Cache)
The Disk Cache accelerates remote data reads by fetching Parquet data files from cloud object storage and saving local copies on fast NVMe SSDs attached directly to worker nodes.
Operational Mechanics & Transpilation
When a worker node reads data files from S3 or ADLS Gen2, the Disk Cache transpiles the remote Parquet format into an uncompressed binary format optimized for fast CPU processing. Subsequent read operations scan data directly from local NVMe SSDs, bypassing network I/O and CPU decompression overhead.
Incremental Block Caching : The Disk Cache operates on individual file blocks. If a query scans only a subset of columns or rows within a large Parquet file, only those specific data blocks are cached locally.
Automatic Eviction : The Disk Cache uses a Least Recently Used (LRU) eviction policy. When the local NVMe SSD reaches storage capacity, older, less frequently accessed data blocks are evicted automatically to make space for new data.
Configuration & Management
In Databricks SQL Warehouses (Serverless and Pro/Classic), the Disk Cache is fully managed, auto-configured, and enabled by default. On custom Databricks All-Purpose clusters, you can explicitly configure or toggle the Disk Cache using Spark configuration properties: -- Enabling Disk Caching on custom clusters:
SET spark.databricks.io.cache.enabled = true;
-- Specifying max disk space allocation for caching (e.g., 100 GB):
SET spark.databricks.io.cache.maxDiskUsage = "100g";
-- Explicitly caching a table into Disk Cache:
- CACHE TABLE gold_monthly_sales;
- 3. Spark In-Memory Cache (DataFrame Caching)
The Spark Cache (managed via CACHE TABLE or .cache() in PySpark) stores deserialized DataFrame data directly in worker node RAM (JVM heap or off-heap memory).
Comparison with Disk Cache
While the Spark Cache offers fast memory access, it is generally discouraged in Databricks SQL Warehouses in favor of the automatic Disk Cache for several reasons:
Memory Overhead : Storing raw Java objects in memory consumes significant RAM, increasing the risk of Out-Of-Memory (OOM) errors and disk spill during large join operations.
Double Caching Penalty : Manually caching tables into RAM duplicates data already cached on local NVMe SSDs by the Disk Cache.
Loss of Photon Acceleration : Photon, the native C++ execution engine in Databricks SQL, is optimized to work directly with the Disk Cache rather than JVM-based Spark caches.
Real-World Analytical Scenario: Cache Interaction Walkthrough
To understand how these caching layers interact during an analytical session, consider the following sequence:
First Execution (Cold Cache) :
Analyst executes SELECT region, SUM(revenue) FROM sales_fact GROUP BY region; .
Result Cache: Miss.
Disk Cache: Miss (reads Parquet files from cloud storage and writes binary blocks to local NVMe SSDs).
Execution Time: 18 seconds.
Second Execution by Second User (Hot Result Cache) :
Another user on the same SQL Warehouse runs the exact same query 5 minutes later.
Result Cache: Hit!
Execution Time: 0.2 seconds (no worker compute used).
Modified Filter Query (Hot Disk Cache) :
Analyst runs SELECT region, SUM(revenue) FROM sales_fact WHERE region = 'EMEA' GROUP BY region; .
Result Cache: Miss (predicate changed).
Disk Cache: Hit! (reads pre-decompressed blocks from worker NVMe SSDs).
Execution Time: 1.4 seconds (up to 10x faster than cold storage read).
Data Refresh Invalidation :
An ETL pipeline appends 10,000 new rows into sales_fact .
Result Cache: Invalidated automatically upon Delta commit log update.
Disk Cache: Modified files invalidated; unchanged historical files remain cached.