curiositech/data-cost-optimizer

Data Cost Optimizer

Reduce data infrastructure costs through warehouse right-sizing, storage lifecycle policies, query optimization, and FinOps practices.

Activation Triggers

Activate on: "data cost", "warehouse credits", "cost reduction", "auto-scaling", "lifecycle policy", "cold storage", "cost monitoring", "Snowflake credits", "BigQuery slots", "FinOps"

NOT for: Query performance tuning → data-warehouse-optimizer | Batch job resource sizing → batch-processing-optimizer | Infrastructure provisioning → relevant DevOps skill

Quick Start

1. Audit current spend — break down costs by warehouse/project, compute vs storage, team/department
2. Identify waste — idle warehouses, full table scans, duplicate data, over-provisioned resources
3. Implement auto-scaling — auto-suspend idle warehouses, auto-scale for concurrency peaks
4. Apply lifecycle policies — move old data to cheaper storage tiers automatically
5. Set budgets and alerts — per-team cost budgets with alerts at 80% and 100%

Core Capabilities

| Domain | Technologies | |--------|-------------| | Snowflake | Resource monitors, auto-suspend, warehouse sizing, credit tracking | | BigQuery | Slot reservations, flat-rate vs on-demand, BI Engine, editions | | Storage | S3 lifecycle (Standard → IA → Glacier), GCS Nearline/Coldline | | Monitoring | Snowflake Account Usage, BigQuery INFORMATION_SCHEMA, Cost Explorer | | FinOps | Kubecost, Datadog Cloud Cost, custom dashboards |

Architecture Patterns

Snowflake Cost Control Framework

-- 1. Resource monitor: alert at 80%, suspend at 100%
CREATE RESOURCE MONITOR monthly_budget
  WITH CREDIT_QUOTA = 5000
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 80 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND;

ALTER WAREHOUSE analytics_wh SET RESOURCE_MONITOR = monthly_budget;

-- 2. Auto-suspend idle warehouses
ALTER WAREHOUSE analytics_wh SET
  AUTO_SUSPEND = 60          -- suspend after 60s idle
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3      -- auto-scale up to 3 clusters
  SCALING_POLICY = 'ECONOMY'; -- prefer queue over new cluster

-- 3. Find expensive queries
SELECT
  query_id,
  user_name,
  warehouse_name,
  total_elapsed_time / 1000 AS seconds,
  bytes_scanned / (1024*1024*1024) AS gb_scanned,
  credits_used_cloud_services
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(day, -7, CURRENT_TIMESTAMP)
ORDER BY credits_used_cloud_services DESC
LIMIT 20;

Storage Lifecycle Optimization

Data Age        Storage Tier           Cost (S3)        Access
──────────      ────────────           ─────────        ──────
0-30 days       Standard               $0.023/GB        Frequent
30-90 days      Infrequent Access      $0.0125/GB       Occasional
90-365 days     Glacier Instant        $0.004/GB        Rare
1-3 years       Glacier Flexible       $0.0036/GB       Archive
3+ years        Glacier Deep Archive   $0.00099/GB      Compliance only

Savings: moving 10TB from Standard to lifecycle-managed
  Before: $230/mo
  After:  ~$50/mo (78% reduction)

Cost Attribution Dashboard

┌─────────────────────────────────────────────────┐
│  Monthly Data Infrastructure Cost: $12,450      │
│                                                 │
│  By Team:                                       │
│    Analytics    ████████████████  $5,200 (42%)   │
│    Data Eng     ██████████       $3,100 (25%)   │
│    ML Platform  ████████        $2,500 (20%)    │
│    Ad-hoc       ████            $1,650 (13%)    │
│                                                 │
│  By Category:                                   │
│    Compute      █████████████   $7,500 (60%)    │
│    Storage      ██████          $3,200 (26%)    │
│    Egress       ███             $1,750 (14%)    │
│                                                 │
│  Top Optimization Opportunities:                │
│    1. Idle warehouse X-Large: $800/mo savings   │
│    2. Full-scan query by user@co: $400/mo       │
│    3. Duplicate staging tables: $300/mo storage  │
└─────────────────────────────────────────────────┘

Anti-Patterns

1. No cost attribution — without per-team/project cost tracking, nobody owns the bill and waste accumulates
2. Over-provisioned warehouses — X-Large warehouse for queries that finish in 2s on Small wastes 8x credits
3. Storing everything in hot tier — data older than 90 days rarely needs hot storage; lifecycle policies save 70-90%
4. Ignoring failed/retried queries — failed queries still consume credits; fix root cause instead of retrying
5. Flat-rate without analysis — BigQuery flat-rate or Snowflake capacity pricing saves money only at scale; analyze before committing

Quality Checklist

• [ ] Cost breakdown by team/project/warehouse tracked monthly
• [ ] Resource monitors with alerts at 80% and hard stops at 100%
• [ ] All warehouses auto-suspend when idle (60-300s)
• [ ] Storage lifecycle policies applied to all buckets/stages
• [ ] Top 20 expensive queries reviewed and optimized monthly
• [ ] Idle/unused warehouses and tables identified and eliminated
• [ ] Per-team budgets set with automated alerting
• [ ] Compute vs storage cost ratio analyzed quarterly
• [ ] Reserved/committed pricing evaluated for stable workloads
• [ ] Cost optimization results tracked: target 10-20% reduction per quarter