Problem Statement
The goal is to analyze cloud data warehouse usage and identify ways to reduce spend without compromising reliability or breaching contractual limits. The workflow must decompose cost drivers, propose safe optimization levers, and deliver a clear, ROI‑focused plan. Recommendations must include required approvals, rollback paths, and monitoring guardrails.
Steps
Ingest Cost and Usage Inputs
INGESTCollect warehouse vendor information, cost reports, storage growth trends, top expensive queries/jobs, refresh frequency, and contractual constraints. Normalize the input into a structured bundle for analysis.
Baseline Current Spend
EXECUTEAnalyze the collected inputs to build a baseline of compute, storage, and concurrency spending. Identify which components contribute the most to total spend.
Identify Key Cost Drivers
EXECUTEBreak down expensive queries, pipelines, and storage growth to determine operational behaviors driving cost. Highlight query inefficiencies, over-provisioned compute tiers, and unnecessary refresh schedules.
Propose Optimization Levers
EXECUTEGenerate prioritized savings opportunities along with quick wins and longer-term structural changes. Ensure rollback plans and approval owners are included, and avoid risky recommendations.
Validate Recommendations Against Constraints
EXECUTECheck recommendations against contractual limitations, data retention constraints, and reliability requirements. Flag items needing cross-functional approval.
Produce Final Plan and Measurement Framework
EXECUTEGenerate a measurement plan with KPIs, monitoring guardrails, and a safe rollout sequence. Output a consolidated, ROI-focused optimization document.
Prompt Configurations
Data Warehouse Cost Analysis Prompt
You are an analytical, ROI-focused cloud cost optimization expert. Based on the provided warehouse vendor, top expensive queries/jobs, storage growth, refresh schedules, and contractual constraints, produce: (1) a clear cost driver breakdown, (2) prioritized savings opportunities, (3) quick wins deliverable within 1–2 weeks, (4) structural changes within 1–3 months, and (5) a measurement plan with guardrails. Keep the tone analytical and careful. Keep company details generic. Always include rollback steps and identify who must approve each recommendation. Do not invent data; only use the supplied inputs.
Example Inputs/Outputs
Vendor: Snowflake. Top expensive queries: large nightly transformation using oversized warehouse; BI dashboard queries scanning entire tables. Storage growth: +12% MoM due to unused staging tables. Refresh schedules: multiple hourly jobs that could be daily. Contract: 20% discount with annual capacity commitment. Requested output: cost drivers, prioritized savings plan, quick wins, structural changes, measurement plan.
Baseline spend shows 68% driven by compute for nightly ETL and 22% by BI workloads. Key drivers include oversized XL warehouse usage and unnecessary full-table scans. Quick wins: downsize warehouse for nightly job with auto-suspend, purge staging tables, and reduce refresh frequency; approval needed from Analytics Engineering and Data Governance. Structural changes: refactor BI models to use clustering and incremental patterns; implement column-level pruning; requires approval from BI Lead. Measurement plan includes tracking warehouse credits/day, storage GB growth, and query latency guardrails with rollback if latency exceeds defined thresholds.