Skip to content
Cheat sheets

Data Engineer Professional · Domain 5: Cost & Performance Optimization · 15% of the exam

Task 5.3: Understand how caching (Delta cache) improves query performance for repeated reads of the same data.

The disk cache on worker local storage, how it stays consistent, CACHE SELECT, the Spark cache, and query result caching on SQL warehouses.

Study it

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

A BI tool runs the same JDBC queries each morning against a SQL warehouse that auto-stops overnight to save cost. The source tables change only once a day, at 05:00, before the dashboards refresh at 07:00 and again at 09:00. On a pro warehouse, the 07:00 refresh is always slow. The team will not keep the warehouse running at night. Which change lets results be reused from cache across the stop?

  1. AMove the dashboards to a serverless SQL warehouse
  2. BKeep the pro warehouse and raise its cluster size
  3. CTurn off auto-stop on the pro warehouse used by the dashboards
  4. DEnable Photon on the pro warehouse
Show the answer and why

  • AMove the dashboards to a serverless SQL warehouse

    Correct

    The remote result cache is serverless-only and survives stopping or restarting the warehouse, for JDBC and ODBC clients.

  • BKeep the pro warehouse and raise its cluster size

    Incorrect

    The local result cache of a pro warehouse is cleared whenever the warehouse stops.

  • CTurn off auto-stop on the pro warehouse used by the dashboards

    Incorrect

    That keeps the warehouse running overnight, which the team ruled out.

  • DEnable Photon on the pro warehouse

    Incorrect

    Photon speeds up execution; it does not keep cached results after the warehouse stops.

SQL warehouses cache query results locally in memory for the cluster's lifetime. Serverless warehouses also have a remote result cache that persists results; it lasts 24 hours and is not invalidated when the warehouse stops, so repeated queries can be served after a restart.

Databricks documentation

Task 5.3Report a problem with this question

Question 2 · choose 1

Analysts on a classic cluster run many queries over the same Delta tables all day. A teammate adds df.cache() calls everywhere. What does Databricks recommend instead?

  1. APersist each DataFrame with the MEMORY_ONLY storage level instead
  2. BUse workers with local SSDs and rely on the automatic disk cache
  3. CTurn off the disk cache so that the Spark cache has more memory
  4. DCopy the tables to the driver's local disk at cluster start
Show the answer and why

  • APersist each DataFrame with the MEMORY_ONLY storage level instead

    Incorrect

    That is still the Spark cache, which needs code changes for each DataFrame.

  • BUse workers with local SSDs and rely on the automatic disk cache

    Correct

    The disk cache stores remote Parquet data on local SSDs automatically and is recommended for repeated reads.

  • CTurn off the disk cache so that the Spark cache has more memory

    Incorrect

    Databricks recommends the disk cache for Parquet and Delta reads.

  • DCopy the tables to the driver's local disk at cluster start

    Incorrect

    The driver's disk is not shared by workers and does not stay consistent.

Databricks recommends the automatic disk cache over the Apache Spark cache for repeated reads of Parquet and Delta Lake data. Worker types with SSDs are configured for it automatically.

Databricks documentation

Task 5.3Report a problem with this question

Question 3 · choose 1

A job overwrites a Delta table that analysts query on a cluster with the disk cache enabled. What must the team do so that queries do not read stale cached data?

  1. ANothing; the disk cache detects file changes automatically
  2. BRestart the cluster after each overwrite to empty the cache
  3. CRun CLEAR CACHE on the cluster after each overwrite finishes
  4. DDisable the disk cache with a Spark conf during the overwrite
Show the answer and why

  • ANothing; the disk cache detects file changes automatically

    Correct

    The disk cache automatically detects created, deleted, modified or overwritten files.

  • BRestart the cluster after each overwrite to empty the cache

    Incorrect

    Restarting clears the cache, but it is not needed for consistency.

  • CRun CLEAR CACHE on the cluster after each overwrite finishes

    Incorrect

    Explicit invalidation is not needed for the disk cache.

  • DDisable the disk cache with a Spark conf during the overwrite

    Incorrect

    Disabling it does not drop cached data and is not required.

The disk cache keeps itself consistent: writes, modifications and deletes need no explicit invalidation, and stale entries are evicted automatically.

Databricks documentation

Task 5.3Report a problem with this question

Question 4 · choose 1

A legacy script runs CACHE SELECT on several tables of a SQL warehouse every morning to warm the cache. What does the statement do today?

  1. AIt loads the tables into the Spark cache in the warehouse memory
  2. BIt pins the tables in the disk cache until the warehouse stops
  3. CNothing; it is ignored, and enhanced disk caching is used instead
  4. DIt copies the tables into the remote result cache for later queries
Show the answer and why

  • AIt loads the tables into the Spark cache in the warehouse memory

    Incorrect

    CACHE SELECT does not use the Spark cache, and it is ignored here.

  • BIt pins the tables in the disk cache until the warehouse stops

    Incorrect

    The command is ignored on SQL warehouses.

  • CNothing; it is ignored, and enhanced disk caching is used instead

    Correct

    In SQL warehouses and Databricks Runtime 14.2 and above, CACHE SELECT is ignored.

  • DIt copies the tables into the remote result cache for later queries

    Incorrect

    The result cache stores query results, not tables.

On SQL warehouses and Databricks Runtime 14.2 and above, CACHE SELECT is ignored in favor of an enhanced disk caching algorithm.

Databricks documentation

Task 5.3Report a problem with this question

Question 5 · choose 1

A data engineer benchmarks a rewritten query on a SQL warehouse, but the second run returns in milliseconds from cache. How can they measure real execution time?

  1. ARun CLEAR CACHE before each run of the query
  2. BTurn off the disk cache in the SQL warehouse settings
  3. CAdd ORDER BY RAND() to the query for each run
  4. DSET use_cached_result = false before the test runs
Show the answer and why

  • ARun CLEAR CACHE before each run of the query

    Incorrect

    The result cache is controlled with use_cached_result.

  • BTurn off the disk cache in the SQL warehouse settings

    Incorrect

    The disk cache stores data files, not query results.

  • CAdd ORDER BY RAND() to the query for each run

    Incorrect

    A random order changes results and plans; it is not a supported way to benchmark.

  • DSET use_cached_result = false before the test runs

    Correct

    This disables query result caching; Databricks advises using it only for testing or benchmarking.

SQL warehouses cache query results locally and, on serverless, remotely. SET use_cached_result = false turns this off, which should be done only in testing or benchmarking.

Databricks documentation

Task 5.3Report a problem with this question

Practice domain 5 →Practice all domains →