A data analyst in a Databricks SQL Editor frequently reruns the same complex query during iterative development. She notices that after the first run, subsequent reruns return almost instantly. Which Databricks mechanism is responsible for this behavior, and what is a potential drawback the analyst should be aware of?
Answer & explanation
Correct answer: B
WHY B: Databricks SQL has a query result cache. When an identical query is run again, the cached result is returned almost instantly without compute execution. The documented drawback is that no Query Profile is available for cache-served queries; to get a fresh profile, the analyst has to make a trivial change to the query.
WHY NOT A: Liquid Clustering speeds up file pruning at scan time but does not return instant results; the query is still executed on each rerun.
WHY NOT C: Disk caching warms up scan I/O but does not return pre-computed result sets; query execution still runs on every rerun.
WHY NOT D: Photon does not have a JIT warmup phase that spans several executions of a query in the way described.
WHY NOT E: Materialized views are separate objects that have to be created explicitly; they do not intercept and cache ad hoc queries run in the SQL Editor.