-- Sqlism diagnostic script: snowflake-cache-diagnostics
-- Used in 'Snowflake Query Is Cached but Still Slow: What Happened?' (sqlism.com/blog)
-- Run these directly in your own Snowflake account (requires access to
-- SNOWFLAKE.ACCOUNT_USAGE, which has up to ~45 min of latency).

-- 1. Warehouse (local disk) cache utilization for recent runs of a
--    specific query pattern. PERCENTAGE_SCANNED_FROM_CACHE close to 1.0
--    means the warehouse cache did most of the work; close to 0 means
--    it re-read from remote storage (e.g. right after a resume).
SELECT
    query_id,
    query_text,
    warehouse_name,
    warehouse_size,
    total_elapsed_time / 1000       AS total_seconds,
    execution_time / 1000           AS execution_seconds,
    bytes_scanned,
    percentage_scanned_from_cache
FROM snowflake.account_usage.query_history
WHERE query_text ILIKE '%<a distinctive fragment of your query text>%'
  AND start_time >= dateadd('day', -2, current_timestamp())
ORDER BY start_time DESC
LIMIT 25;

-- 2. Queries that never spun up a warehouse at all -- answered entirely
--    by the cloud services layer, either from the persisted result
--    cache or from metadata alone (e.g. a trivial COUNT(*)). There's no
--    single boolean column for "this was a result-cache hit", but
--    WAREHOUSE_SIZE IS NULL combined with BYTES_SCANNED = 0 and a
--    near-zero TOTAL_ELAPSED_TIME is the unmistakable signature.
SELECT
    query_id,
    query_text,
    total_elapsed_time / 1000 AS total_seconds,
    bytes_scanned
FROM snowflake.account_usage.query_history
WHERE warehouse_size IS NULL
  AND start_time >= dateadd('day', -1, current_timestamp())
ORDER BY start_time DESC
LIMIT 25;