-- Check warehouse load — avg_queued_load > 0 means queries are waiting
SELECT warehouse_name, start_time,
       avg_running, avg_queued_load, avg_blocked
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
  DATE_RANGE_START => DATEADD(hours, -4, CURRENT_TIMESTAMP())
))
WHERE avg_queued_load > 0
ORDER BY start_time DESC;

-- Find queries that waited in queue
SELECT query_id, query_text, queued_overload_time / 1000 AS queue_seconds,
       total_elapsed_time / 1000 AS total_seconds, warehouse_name
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE queued_overload_time > 0
  AND start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
ORDER BY queued_overload_time DESC
LIMIT 20;

-- Size recommendations based on workload type
-- XSMALL: Simple queries, dev/test, low concurrency
-- SMALL/MEDIUM: Standard analytics, dashboards
-- LARGE/XLARGE: Complex joins, large scans
-- 2XL+: Heavy ELT, ML training

-- Create right-sized warehouses per workload
CREATE WAREHOUSE IF NOT EXISTS ETL_WH
  WAREHOUSE_SIZE = 'LARGE'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE WAREHOUSE IF NOT EXISTS ANALYTICS_WH
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE WAREHOUSE IF NOT EXISTS DASHBOARD_WH
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Auto-scale from 1 to 5 clusters based on demand
CREATE OR REPLACE WAREHOUSE HIGH_CONCURRENCY_WH
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 5
  SCALING_POLICY = 'STANDARD'     -- Start new cluster when queries queue
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE;

-- Economy scaling: minimize clusters, tolerate some queuing
ALTER WAREHOUSE HIGH_CONCURRENCY_WH SET
  SCALING_POLICY = 'ECONOMY';    -- Only scale when 6+ min queue

import PQueue from 'p-queue';

// Limit concurrent Snowflake queries from your application
const snowflakeQueue = new PQueue({
  concurrency: 5,           // Max 5 concurrent queries
  intervalCap: 20,           // Max 20 queries per interval
  interval: 60000,           // Per minute
  timeout: 300000,           // 5-minute timeout per query
});

async function rateLimitedQuery<T>(
  conn: snowflake.Connection,
  sqlText: string,
  binds?: any[]
): Promise<T[]> {
  return snowflakeQueue.add(async () => {
    const result = await query<T>(conn, sqlText, binds);
    return result.rows;
  });
}

// Queue status monitoring
setInterval(() => {
  console.log({
    pending: snowflakeQueue.pending,
    size: snowflakeQueue.size,
  });
}, 30000);

// When using Snowflake SQL REST API (/api/v2/statements)
// Limit: 10 concurrent requests per user

async function sqlApiQuery(
  accountUrl: string,
  token: string,
  sqlText: string
): Promise<any> {
  const response = await fetch(
    `https://${accountUrl}/api/v2/statements`,
    {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${token}`,
        'Content-Type': 'application/json',
        'X-Snowflake-Authorization-Token-Type': 'KEYPAIR_JWT',
      },
      body: JSON.stringify({
        statement: sqlText,
        timeout: 60,
        database: 'MY_DB',
        schema: 'PUBLIC',
        warehouse: 'COMPUTE_WH',
        role: 'MY_ROLE',
      }),
    }
  );

  if (response.status === 429) {
    // SQL API throttled — back off and retry
    const retryAfter = parseInt(response.headers.get('Retry-After') || '10');
    await new Promise((r) => setTimeout(r, retryAfter * 1000));
    return sqlApiQuery(accountUrl, token, sqlText);
  }

  return response.json();
}