Snowflake Query History and Optimization

Query History provides comprehensive visibility into all queries executed in Snowflake, enabling performance analysis, cost optimization, and troubleshooting.

Architecture Overview

<svg width="800" height="450" viewBox="0 0 800 450" xmlns="http://www.w3.org/2000/svg">
  <defs>
    <linearGradient id="histGrad" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" style="stop-color:#3498DB;stop-opacity:1" />
      <stop offset="100%" style="stop-color:#5DADE2;stop-opacity:1" />
    </linearGradient>
    <linearGradient id="optGrad" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" style="stop-color:#2ECC71;stop-opacity:1" />
      <stop offset="100%" style="stop-color:#58D68D;stop-opacity:1" />
    </linearGradient>
  </defs>

  <text x="400" y="30" text-anchor="middle" font-size="18" font-weight="bold" fill="#333">Query History & Optimization Architecture</text>
  <rect x="30" y="60" width="740" height="80" rx="10" fill="url(#histGrad)" opacity="0.9"/>
  <text x="400" y="85" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Query Execution Pipeline</text>

  <rect x="50" y="95" width="100" height="35" rx="5" fill="white" opacity="0.9"/>
  <text x="100" y="117" text-anchor="middle" font-size="10" fill="#333">Parse</text>
  <rect x="160" y="95" width="100" height="35" rx="5" fill="white" opacity="0.9"/>
  <text x="210" y="117" text-anchor="middle" font-size="10" fill="#333">Optimize</text>
  <rect x="270" y="95" width="100" height="35" rx="5" fill="white" opacity="0.9"/>
  <text x="320" y="117" text-anchor="middle" font-size="10" fill="#333">Compile</text>
  <rect x="380" y="95" width="100" height="35" rx="5" fill="white" opacity="0.9"/>
  <text x="430" y="117" text-anchor="middle" font-size="10" fill="#333">Execute</text>
  <rect x="490" y="95" width="100" height="35" rx="5" fill="white" opacity="0.9"/>
  <text x="540" y="117" text-anchor="middle" font-size="10" fill="#333">Fetch</text>
  <rect x="600" y="95" width="150" height="35" rx="5" fill="white" opacity="0.9"/>
  <text x="675" y="117" text-anchor="middle" font-size="10" fill="#333">Result Cache</text>
  <rect x="30" y="160" width="740" height="100" rx="10" fill="#F39C12" opacity="0.9"/>
  <text x="400" y="185" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Query History Metadata</text>

  <rect x="50" y="200" width="140" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="120" y="227" text-anchor="middle" font-size="10" fill="#333">Execution Time</text>
  <rect x="210" y="200" width="140" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="280" y="227" text-anchor="middle" font-size="10" fill="#333">Bytes Scanned</text>
  <rect x="370" y="200" width="140" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="440" y="227" text-anchor="middle" font-size="10" fill="#333">Partitions</text>
  <rect x="530" y="200" width="140" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="600" y="227" text-anchor="middle" font-size="10" fill="#333">Spillage</text>
  <rect x="690" y="200" width="70" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="725" y="227" text-anchor="middle" font-size="10" fill="#333">Cost</text>
  <rect x="30" y="280" width="360" height="150" rx="10" fill="url(#optGrad)" opacity="0.9"/>
  <text x="210" y="305" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Optimization Tools</text>
  <text x="210" y="330" text-anchor="middle" font-size="11" fill="white">Query Profile</text>
  <text x="210" y="350" text-anchor="middle" font-size="11" fill="white">Execution Plan</text>
  <text x="210" y="370" text-anchor="middle" font-size="11" fill="white">Result Cache</text>
  <text x="210" y="390" text-anchor="middle" font-size="11" fill="white">Clustering Keys</text>
  <text x="210" y="410" text-anchor="middle" font-size="11" fill="white">Materialized Views</text>

  <rect x="420" y="280" width="350" height="150" rx="10" fill="#9B59B6" opacity="0.85"/>
  <text x="595" y="305" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Best Practices</text>
  <text x="595" y="330" text-anchor="middle" font-size="11" fill="white">Use clustering keys</text>
  <text x="595" y="350" text-anchor="middle" font-size="11" fill="white">Limit data scanning</text>
  <text x="595" y="370" text-anchor="middle" font-size="11" fill="white">Optimize warehouse size</text>
  <text x="595" y="390" text-anchor="middle" font-size="11" fill="white">Monitor spillage</text>
  <text x="595" y="410" text-anchor="middle" font-size="11" fill="white">Use result caching</text>
</svg>

Query History Access

Basic Query History

-- Recent queries
SELECT
  query_id,
  query_text,
  user_name,
  start_time,
  end_time,
  total_elapsed_time as duration_ms,
  bytes_scanned,
  rows_produced,
  warehouse_name
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(hour, -24, CURRENT_TIMESTAMP()),
  END_TIME => CURRENT_TIMESTAMP()
))
ORDER BY start_time DESC;

-- Specific user queries
SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  USER_NAME => 'analyst@company.com',
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
));

Query Profile

-- Enable query profile
ALTER SESSION SET ENABLE_QUERY_PROFILE = TRUE;

-- Run query and check profile
SELECT * FROM large_table WHERE id = 123;

-- View profile
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_PROFILE(
  QUERY_ID => LAST_QUERY_ID()
));

Performance Analysis

Top Expensive Queries

-- Find queries with most bytes scanned
SELECT
  query_text,
  total_elapsed_time,
  bytes_scanned / 1024 / 1024 / 1024 as gb_scanned,
  rows_produced,
  warehouse_name,
  compilation_time,
  execution_time,
  queue_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
))
WHERE bytes_scanned > 1024 * 1024 * 1024
ORDER BY bytes_scanned DESC
LIMIT 20;

Warehouse Utilization

-- Warehouse usage statistics
SELECT
  warehouse_name,
  COUNT(*) as query_count,
  AVG(total_elapsed_time) as avg_duration_ms,
  SUM(bytes_scanned) / 1024 / 1024 / 1024 as total_gb_scanned,
  SUM(credits_used) as total_credits
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
GROUP BY 1
ORDER BY total_gb_scanned DESC;

Query Optimization Techniques

Clustering Key Analysis

-- Check clustering depth
SELECT
  SYSTEM$CLUSTERING_DEPTH('database.schema.table');
  
-- Check clustering information
SELECT *
FROM TABLE(INFORMATION_SCHEMA.CLUSTERING_TABLE_INFO(
  'database.schema.table'
));

-- Recommend clustering key
SELECT *
FROM TABLE(INFORMATION_SCHEMA.CLUSTERING_RECOMMENDATIONS(
  'database.schema.table'
));

Result Cache Hit Rate

-- Cache performance
SELECT
  COUNT(*) as total_queries,
  SUM(CASE WHEN query_id IN (
    SELECT query_id FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
    WHERE execution_status = 'SUCCESS'
    AND total_elapsed_time < 100
  ) THEN 1 ELSE 0 END) as cached_queries,
  (cached_queries / total_queries * 100) as cache_hit_rate
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
));

Cost Analysis

-- Credit consumption by query type
SELECT
  CASE
    WHEN query_text LIKE '%SELECT%' THEN 'SELECT'
    WHEN query_text LIKE '%INSERT%' THEN 'INSERT'
    WHEN query_text LIKE '%UPDATE%' THEN 'UPDATE'
    WHEN query_text LIKE '%DELETE%' THEN 'DELETE'
    WHEN query_text LIKE '%COPY%' THEN 'COPY'
    ELSE 'Other'
  END as query_type,
  COUNT(*) as query_count,
  SUM(credits_used) as total_credits,
  AVG(credits_used) as avg_credits
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
GROUP BY 1
ORDER BY total_credits DESC;