7.1 Caching Hierarchy in Databricks SQL

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.

---

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:

1. 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.
2. Double Caching Penalty: Manually caching tables into RAM duplicates data already cached on local NVMe SSDs by the Disk Cache.
3. 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:

1. 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.

2. 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).

3. 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).

4. 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.