BigQuery vs Snowflake

Audits the top 10 most expensive queries and unpartitioned scans over the preceding 24 hours to eliminate scan budget overruns.

-- Identify top 10 most expensive queries and unpartitioned scans over the last 24 hours
SELECT
  project_id,
  job_id,
  user_email,
  total_bytes_billed / 1024 / 1024 / 1024 / 1024 AS tb_billed,
  ROUND((total_bytes_billed / 1024 / 1024 / 1024 / 1024) * 6.25, 2) AS estimated_cost_usd,
  query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND job_type = 'QUERY'
  AND statement_type != 'SCRIPT'
ORDER BY total_bytes_billed DESC
LIMIT 10;

Identifies virtual warehouses with heavy remote disk spills and query queueing delays over the past 24 hours to isolate undersized compute.

-- Identify warehouses with heavy remote disk spills and query queueing over the last 24 hours
SELECT
  warehouse_name,
  COUNT(*) AS total_queries,
  ROUND(AVG(queued_provisioning_time) / 1000, 2) AS avg_queued_prov_sec,
  ROUND(AVG(queued_overload_time) / 1000, 2) AS avg_queued_overload_sec,
  ROUND(SUM(bytes_spilled_to_local_storage) / 1024 / 1024 / 1024, 2) AS local_spill_gb,
  ROUND(SUM(bytes_spilled_to_remote_storage) / 1024 / 1024 / 1024, 2) AS remote_spill_gb
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(hours, -24, CURRENT_TIMESTAMP())
  AND (bytes_spilled_to_remote_storage > 0 OR queued_overload_time > 0)
GROUP BY warehouse_name
ORDER BY remote_spill_gb DESC
LIMIT 10;