-- ============================================================================
-- cache-diagnostics.sql
-- Find out whether your Databricks queries are actually benefiting from the
-- result cache or the disk cache -- and compare caching behavior across
-- warehouse types (serverless vs. classic/Pro).
-- Source: Sqlism (sqlism.com) -- "Databricks Query Is Cached but Still Slow"
--
-- Requires: access to the system.query.history system table (enabled by
-- default on most workspaces; ask your workspace admin if it's missing).
-- Replace '<warehouse-id>' with your own SQL warehouse ID where noted.
-- ============================================================================

-- 1) Result-cache hit rate for a specific warehouse over the last 7 days --
--    start here. from_result_cache = TRUE means the query never re-executed.
SELECT
    DATE(start_time) AS query_date,
    COUNT(*) AS total_queries,
    COUNT(CASE WHEN from_result_cache THEN 1 END) AS result_cache_hits,
    ROUND(100.0 * COUNT(CASE WHEN from_result_cache THEN 1 END) / COUNT(*), 1) AS result_cache_hit_pct,
    ROUND(AVG(CASE WHEN from_result_cache THEN total_duration_ms END), 0) AS avg_hit_duration_ms,
    ROUND(AVG(CASE WHEN NOT from_result_cache THEN total_duration_ms END), 0) AS avg_miss_duration_ms
FROM system.query.history
WHERE compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
GROUP BY DATE(start_time)
ORDER BY query_date DESC;

-- 2) Disk-cache utilization on queries that did NOT get a full result-cache
--    hit -- read_io_cache_percent measures the file-read side independently
--    of whether the whole query result was cached.
SELECT
    statement_id,
    start_time,
    execution_duration_ms,
    read_io_cache_percent,
    read_bytes,
    cache_origin_statement_id
FROM system.query.history
WHERE compute.warehouse_id = '<warehouse-id>'
  AND start_time >= NOW() - INTERVAL 7 DAY
  AND from_result_cache = FALSE
  AND read_bytes > 0
ORDER BY execution_duration_ms DESC
LIMIT 50;

-- 3) Compare result-cache hit rate across warehouse types. The remote result
--    cache is serverless-only, so a meaningfully lower hit rate on classic/Pro
--    warehouses for otherwise-similar repeated queries is expected, not a bug.
SELECT
    compute.type AS warehouse_type,
    COUNT(*) AS total_queries,
    ROUND(100.0 * COUNT(CASE WHEN from_result_cache THEN 1 END) / COUNT(*), 1) AS result_cache_hit_pct
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 30 DAY
GROUP BY compute.type
ORDER BY result_cache_hit_pct DESC;