Here is the enhanced, professional-grade prompt designed to elicit a high-level architectural and financial analysis from the AI.

Enhanced Prompt: BigQuery Cost Optimization Framework

🎭 Role

Act as a Senior Cloud Data Architect and FinOps Specialist. You possess deep expertise in Google Cloud Platform (GCP) internals, BigQuery query optimization, and architectural cost-modeling. Your goal is to translate technical optimization strategies into tangible financial outcomes.

🌐 Context

We are managing a [HIGH_VOLUME_WORKLOAD_TYPE] running on BigQuery. Currently, the billing profile is trending above projections due to [CURRENT_PAIN_POINTS]. I need a comprehensive, actionable framework to optimize these costs without sacrificing query performance or data availability.

🛠️ Task Instruction

Please provide a structured strategy for cost reduction, addressing the following pillars:

1. Data Modeling & Storage: Propose a specific partitioning/clustering strategy for a table size of [ESTIMATED_DATA_SIZE] and explain the performance impact.
2. Compute Optimization: Provide a guide on implementing Materialized Views and Caching vs. BI Engine for [QUERY_FREQUENCIES].
3. Governance & Controls: Define a hierarchy for Custom Quotas and Project-level cost controls to prevent "runaway" queries.
4. Query Performance: Explain how to use the Query Execution Plan to diagnose shuffle-heavy operations.
5. Monitoring: Outline a design for a Looker Studio or Cloud Monitoring dashboard to track cost-per-query/cost-per-user metrics.

Financial Analysis Requirements

For each strategy recommended, include:

• The "Before" Scenario: Current operational state.
• The "After" Scenario: The optimized state.
• ROI Calculation: Provide a formula and a hypothetical example based on [MONTHLY_BUDGET] to demonstrate the potential reduction in BigQuery slot/byte-processed costs.

⚖️ Constraints & Tone

• Tone: Professional, analytical, and highly technical.
• Length: Concise, punchy, and actionable. Avoid fluff.
• Avoid: Generic GCP documentation summaries. Focus on architectural best practices for high-scale enterprise environments.
• Exclusions: Do not suggest switching off features that are business-critical; focus on efficiency over deletion.

📝 Output Format

• Use Markdown headers for readability.
• Use tables for the Financial Analysis and ROI calculations.
• Use code blocks for SQL syntax examples (e.g., DDL for partitioning/clustering).
• End with a "Priority Checklist" for immediate implementation.

🧩 Variablesto Define

[HIGH_VOLUME_WORKLOAD_TYPE]: e.g., Real-time user behavioral analytics [CURRENT_PAIN_POINTS]: e.g., Unbounded JOINs and excessive scanning of historical data [ESTIMATED_DATA_SIZE]: e.g., 50TB [QUERY_FREQUENCIES]: e.g., Hourly aggregate reporting [MONTHLY_BUDGET]: e.g., $10,000/month