Snowflake Cost Management and Optimization

Effective cost management in Snowflake requires understanding the cost drivers and implementing optimization strategies across compute, storage, and data transfer.

Cost Components

Compute Costs

-- Monitor warehouse usage
SELECT
  warehouse_name,
  SUM(credits_used) as total_credits,
  SUM(credits_used * 10) as total_cost_usd,
  AVG(credits_used) as avg_credits_per_query,
  COUNT(*) as query_count
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY(
  START_TIME => DATEADD('day', -30, CURRENT_TIMESTAMP())
))
GROUP BY warehouse_name
ORDER BY total_cost_usd DESC;

-- Track hourly compute costs
SELECT
  DATE_TRUNC('hour', start_time) as hour,
  warehouse_name,
  SUM(credits_used) as hourly_credits,
  SUM(credits_used * 10) as hourly_cost
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY(
  START_TIME => DATEADD('day', -7, CURRENT_TIMESTAMP())
))
GROUP BY 1, 2
ORDER BY hour DESC;

Storage Costs

-- Monitor storage usage
SELECT
  table_schema,
  SUM(table_size_bytes) / 1024 / 1024 / 1024 AS storage_gb,
  SUM(table_size_bytes) * 0.023 / 1024 / 1024 / 1024 AS monthly_cost_usd,
  COUNT(*) as table_count
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
GROUP BY table_schema
ORDER BY storage_gb DESC;

-- Track storage growth
SELECT
  DATE_TRUNC('week', created_on) as week,
  SUM(table_size_bytes) / 1024 / 1024 / 1024 AS total_storage_gb,
  SUM(table_size_bytes) * 0.023 / 1024 / 1024 / 1024 AS total_cost_usd
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
GROUP BY 1
ORDER BY week DESC;

Compute Optimization

Warehouse Sizing

-- Analyze warehouse utilization
SELECT
  warehouse_name,
  warehouse_size,
  AVG(query_count) as avg_queries_per_hour,
  AVG(credits_used) as avg_credits_per_hour,
  CASE
    WHEN AVG(credits_used) < 0.1 THEN 'OVERSIZED'
    WHEN AVG(credits_used) > 0.9 THEN 'UNDERSIZED'
    ELSE 'APPROPRIATELY_SIZED'
  END as size_assessment
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY(
  START_TIME => DATEADD('day', -7, CURRENT_TIMESTAMP())
))
GROUP BY warehouse_name, warehouse_size;

-- Right-size warehouses
ALTER WAREHOUSE compute_wh SET WAREHOUSE_SIZE = 'MEDIUM';

Auto-Suspend and Auto-Resume

-- Configure auto-suspend
ALTER WAREHOUSE compute_wh SET
  AUTO_SUSPEND = 60,  -- 60 seconds
  AUTO_RESUME = TRUE;

-- Monitor auto-suspend effectiveness
SELECT
  warehouse_name,
  auto_suspend,
  auto_resume,
  COUNT(*) as usage_count
FROM INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY warehouse_name, auto_suspend, auto_resume;

Scaling Policies

-- Configure scaling policy
ALTER WAREHOUSE compute_wh SET
  MIN_CLUSTER_COUNT = 1,
  MAX_CLUSTER_COUNT = 4,
  SCALING_POLICY = 'ECONOMY';  -- or 'STANDARD'

-- Monitor scaling behavior
SELECT
  warehouse_name,
  cluster_number,
  start_time,
  end_time,
  credits_used
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY(
  START_TIME => DATEADD('day', -1, CURRENT_TIMESTAMP())
))
WHERE warehouse_name = 'COMPUTE_WH'
ORDER BY start_time;

Storage Optimization

Data Compression

-- Check compression ratios
SELECT
  table_name,
  table_size_bytes,
  table_bytes / NULLIF(table_size_bytes, 0) as compression_ratio,
  total_rows,
  micro_partitions_count
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE table_schema = 'PRODUCTION'
ORDER BY compression_ratio DESC;

-- Enable automatic clustering for better compression
ALTER TABLE orders CLUSTER BY (order_date);

Time Travel and Fail-safe

-- Optimize time travel retention
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 7;
ALTER TABLE historical_data SET DATA_RETENTION_TIME_IN_DAYS = 1;

-- Monitor fail-safe storage
SELECT
  table_name,
  fail_safe_bytes / 1024 / 1024 / 1024 as fail_safe_gb,
  fail_safe_bytes * 0.023 / 1024 / 1024 / 1024 as fail_safe_cost_usd
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE fail_safe_bytes > 0;

Data Archival

-- Archive old data to reduce costs
CREATE OR REPLACE TABLE orders_archive AS
SELECT * FROM orders
WHERE order_date < DATEADD('year', -2, CURRENT_DATE());

-- Move to external storage for long-term archival
CREATE OR REPLACE EXTERNAL TABLE orders_external
  WITH LOCATION = @archive_stage
  FILE_FORMAT = (TYPE = PARQUET)
  AUTO_REFRESH = false;

-- Delete archived data from main table
DELETE FROM orders
WHERE order_date < DATEADD('year', -2, CURRENT_DATE());

Cost Monitoring and Alerts

-- Create cost monitoring view
CREATE OR REPLACE VIEW cost_monitoring AS
SELECT
  DATE_TRUNC('day', start_time) as date,
  warehouse_name,
  SUM(credits_used) as daily_credits,
  SUM(credits_used * 10) as daily_cost_usd,
  COUNT(*) as query_count
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY(
  START_TIME => DATEADD('day', -30, CURRENT_TIMESTAMP())
))
GROUP BY 1, 2;

-- Create cost alert procedure
CREATE OR REPLACE PROCEDURE check_cost_threshold()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  daily_cost FLOAT;
  threshold FLOAT := 1000;  -- $1000 daily threshold
BEGIN
  SELECT SUM(credits_used * 10) INTO daily_cost
  FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY(
    START_TIME => DATEADD('day', -1, CURRENT_TIMESTAMP())
  ));

  IF daily_cost > threshold THEN
    RETURN 'WARNING: Daily cost ($' || daily_cost || ') exceeds threshold ($' || threshold || ')';
  ELSE
    RETURN 'SUCCESS: Daily cost ($' || daily_cost || ') within threshold';
  END IF;
END;
$$;

-- Schedule cost monitoring
CREATE TASK daily_cost_check
  SCHEDULE = 'USING CRON 0 9 * * * America/New_York'
AS
  CALL check_cost_threshold();

Cost Optimization Strategies

Query Optimization

-- Identify expensive queries
SELECT
  query_id,
  query_text,
  execution_time_ms,
  bytes_scanned,
  ROUND(bytes_scanned / 1024 / 1024, 2) as scanned_mb,
  ROUND(credits_used * 10, 2) as cost_usd
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD('day', -7, CURRENT_TIMESTAMP())
))
ORDER BY cost_usd DESC
LIMIT 10;

-- Optimize query patterns
-- Use clustering for better pruning
ALTER TABLE orders CLUSTER BY (order_date, region);

-- Create materialized views for frequent queries
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
  order_date,
  COUNT(*) as order_count,
  SUM(amount) as total_sales
FROM orders
GROUP BY order_date;

Resource Monitor

-- Create resource monitor
CREATE RESOURCE MONITOR monthly_budget
  WITH
    CREDIT_QUOTA = 1000,  -- 1000 credits per month
    TRIGGERS
      ON 80% DO NOTIFY
      ON 100% DO SUSPEND;

-- Apply to warehouses
ALTER WAREHOUSE compute_wh SET RESOURCE_MONITOR = 'monthly_budget';

-- Monitor resource usage
SELECT
  monitor_name,
  credit_quota,
  credits_used,
  ROUND(credits_used / credit_quota * 100, 2) as usage_pct
FROM INFORMATION_SCHEMA<Resource_MONITORS>;

Cost Optimization Checklist