Singdata Lakehouse MCP Server Use Case: Cost Anomaly Analysis and Troubleshooting

Core Q&A Process Demo

User Question Sequence

👤 User: "Analyze the data in sys.information_schema.instance_usage table, check the costs for September 4th and September 5th" 🤖 MCP Server: [Auto analyze table structure] → [Generate intelligent query] → [Output cost report] Found: September 4 cost ¥85.77, September 5 cost ¥66.65, mainly from GP compute cluster 👤 User: "Why are the costs so high on these two days?" 🤖 MCP Server: [Historical data comparison] → [Anomaly pattern recognition] → [Trend analysis] Found: Normal daily cost ¥1-6, surged 15x during the anomaly period, GP cluster usage duration abnormal 👤 User: "Check which instance and which workspace's cluster" 🤖 MCP Server: [Multi-dimensional analysis] → [Responsible party identification] → [Job history tracking] Found: Instance 270397's quick_start workspace, caused by frequent refresh of the mcp_test_daily_summary dynamic table

Key Insights

Issue location time: <= '2025-09-08' GROUP BY date(measurement_start) ORDER BY usage_date

Anomaly Pattern Recognition

Historical Cost Trend Analysis:

Time Period	Average Daily Cost	Cost Range	Status
Aug 25-Sep 2	¥1.8	¥0.99-¥6.06	✅ Normal baseline
Sep 3	¥25.13	-	⚠️ First anomaly
Sep 4-5	¥76.21	¥66.65-¥85.77	🚨 Severe anomaly
After Sep 6	¥0.73	¥0.53-¥1.05	✅ Returned to normal

Usage Pattern Change Analysis
SELECT date(measurement_start) as usage_date, sku_name, count(*) as record_count, sum(measurements_consumption) as total_consumption_hours, avg(measurements_consumption) as avg_consumption_per_record, sum(total_after_discount) as total_cost FROM sys.information_schema.instance_usage WHERE sku_category = 'compute' GROUP BY date(measurement_start), sku_name

GP Cluster Usage Pattern Comparison:

Date	Average Hourly Consumption	Record Count	Total Consumption Duration	Per-Instance Cost
Sep 1-2	0.03-0.04 hours	14-20 records	0.37-0.76 hours	¥1-3
Sep 4	0.57 hours	42 records	24.05 hours	¥3.5
Sep 5	0.58 hours	32 records	18.71 hours	¥3.5
After Sep 6	0.02 hours	12 records	0.20 hours	¥0.7

Key Findings:

GP cluster changed from short query mode to long-running mode
Hourly consumption duration increased 15x (0.04 → 0.57 hours)
Nearly full 1-hour billing, indicating the cluster was running continuously

Step 3: Precise Responsible Party Identification

User continues to ask:

Check which instance and which workspace's cluster

MCP Server Multi-Dimensional Analysis:

Instance and Workspace Cost Distribution
SELECT instance_id, workspace_name, sku_name, sum(total_after_discount) as total_cost FROM sys.information_schema.instance_usage WHERE date(measurement_start) IN ('2025-09-04', '2025-09-05') AND sku_category = 'compute' GROUP BY instance_id, workspace_name, sku_name ORDER BY total_cost DESC

Responsible Party Analysis Results:

Instance ID	Workspace	Sep 4 Cost	Sep 5 Cost	Anomaly Severity
270397	quick_start	¥83.48	¥64.77	🚨 Primary responsible party
270397	lakehouse_quick_experience	¥0.71	¥0.71	✅ Normal
270397	ns227206	¥0.17	¥0.17	✅ Normal

Specific Cluster Usage Details
SELECT date(measurement_start) as usage_date, hour(measurement_start) as usage_hour, measurements_consumption, amount FROM sys.information_schema.instance_usage WHERE workspace_name = 'quick_start' AND sku_name = 'GP Compute Cluster' AND amount >

GP Cluster Runtime Analysis:

Sep 4 Anomaly Pattern:

00:00-23:59: 24 hours continuous runtime
Hourly consumption: ~1.00 hours (nearly full hourly billing)
Per-hour cost: Around ¥3.5

Sep 5 Anomaly Pattern:

00:00-18:59: 19 hours continuous runtime
Cost pattern: Similarly close to full hourly billing

Key Findings:

Issue identified in the GP cluster of quick_start workspace
Cluster shows nearly 24-hour continuous runtime
Not a normal short query pattern

Step 4: Root Cause Tracking

MCP Server Auto Deep Investigation:

Job History Analysis

<= '2025-09-06 23:59:59'

Normal Period Job Characteristics:

Query execution time: seconds to minutes
Cluster auto-suspend: quick release after query completion
Cost pattern: billed by actual usage time

Root Cause Deep Analysis

Technical Analysis

1. Dynamic Table Configuration Error

-- Problematic configuration (speculated) CREATE DYNAMIC TABLE mcp_test_daily_summary REFRESH_INTERVAL = '5 MINUTES' -- ❌ Too frequent AS SELECT ...

2. Cluster Resource Management Failure

Normal mode: Query completes → Cluster idle → Auto-suspend (within minutes)
Anomaly mode: Query completes → New query after 5 minutes → Cluster stays active → Cannot suspend

3. Billing Calculation Logic

Hourly billing: GP cluster billed by usage duration, ¥3.5/hour
Minimum billing unit: Even if used for minutes, the active state accumulates billing time
Cumulative effect: 5-minute refresh interval → Nearly full 24-hour billing

Business Impact Assessment

1. Direct Cost Loss

Anomaly period: 2-day total cost ¥152.42
Normal period: 2-day expected cost ¥2-12
Direct loss: ¥140-150

2. Potential Risks

If not discovered in time: Daily additional cost ¥80+
Monthly impact: Could generate ¥2400+ additional costs
Resource waste: Cluster resources not effectively utilized

3. Systemic Issues

Configuration management: Dynamic table refresh policy lacks reasonableness checks
Monitoring alerts: Cost anomalies not discovered in time
Resource optimization: Cluster auto-management policy needs optimization

Solutions and Best Practices

Immediate Remediation Measures

1. Dynamic Table Configuration Correction

-- Check current configuration DESC DYNAMIC TABLE mcp_test_daily_summary; -- Option 1: Adjust refresh interval (recommended) ALTER DYNAMIC TABLE mcp_test_daily_summary SET REFRESH_INTERVAL = '1 HOUR'; -- Option 2: Adjust based on business requirements ALTER DYNAMIC TABLE mcp_test_daily_summary SET REFRESH_INTERVAL = '4 HOURS'; -- or other reasonable intervals -- Option 3: Suspend dynamic table (emergency) ALTER DYNAMIC TABLE mcp_test_daily_summary SUSPEND;

2. Cluster Configuration Optimization

-- Check cluster auto-suspend settings SHOW VIRTUAL_CLUSTERS; -- Ensure reasonable auto-suspend time ALTER VIRTUAL_CLUSTER your_gp_cluster SET AUTO_SUSPEND_IN_SECOND = 300; -- 5-minute auto-suspend

Preventive Measures

1. Monitoring and Alerting

-- Create cost monitoring view CREATE VIEW daily_cost_monitor AS SELECT date(measurement_start) as cost_date, sum(total_after_discount) as daily_cost, CASE WHEN sum(total_after_discount) > **2. Dynamic Table Best Practices** * **Refresh interval recommendation**: Set based on data update frequency, minimum no less than 30 minutes * **Resource consideration**: Evaluate refresh impact on compute resources * **Business alignment**: Refresh frequency should match actual business requirements **3. Resource Management Strategy** * **Cluster separation**: Separate scheduled tasks and interactive queries into different clusters * **Time window**: Execute resource-intensive tasks during off-peak hours * **Cost budget**: Set workspace-level cost budget and alerts ## Case Value and Technical Highlights ### 1. MCP Server Intelligent Analysis Capability **Natural Language Understanding** * No need for users to master complex SQL syntax * Intelligently understand analysis intent and convert to precise queries * Support multi-turn conversations, progressively dig deeper into issues **Automated Data Analysis**

User intent: "Analyze costs" → Auto table structure identification → Intelligent query generation → Multi-dimensional data correlation → Anomaly pattern recognition → Visualized result presentation

**Context Memory Capability** * Remember previous analysis results * Conduct deeper investigation based on existing findings * Maintain analytical logic continuity ### 2. Systematic Approach to Troubleshooting **Hierarchical Analysis Framework** 1. **Overview layer**: Overall cost trend identification 2. **Decomposition layer**: Break down cost composition by dimensions 3. **Localization layer**: Precisely identify responsible parties 4. **Root cause layer**: Trace to specific technical causes **Multi-Dimensional Correlation Analysis** * Time dimension: Historical comparison, trend analysis * Spatial dimension: Instance, workspace, cluster * Business dimension: Job type, execution frequency * Technical dimension: Resource usage, configuration parameters ### 3. Practical Value **Rapid Response Capability** * **Issue discovery to localization**: < 5 minutes * **Solution output**: Immediately executable SQL commands * **Effect verification**: Real-time review of fix results **Economic Value** * **Direct savings**: ¥80+/day cost savings * **Avoid losses**: Prevent ongoing losses from long-term configuration errors * **Efficiency improvement**: Significantly reduce manual investigation time **Knowledge Accumulation** * **Reusable process**: Analysis method applicable to other similar issues * **Best practices**: Form standardized troubleshooting processes * **Prevention guide**: Provide experience reference for other users ## Result Verification and Effectiveness Evaluation ### Resolution Effectiveness Confirmation **Cost Recovery Verification** ```sql -- Create cost monitoring view CREATE VIEW daily_cost_monitor AS SELECT date(measurement_start) as cost_date, sum(total_after_discount) as daily_cost, CASE WHEN sum(total_after_discount) > **Results**: * Sep 6: ¥1.05 ✅ Returned to normal * Sep 7: ¥0.96 ✅ Continuously normal * Sep 8: ¥0.53 ✅ Further optimized **Cluster Usage Pattern Recovery** * Average usage duration per instance: 0.02 hours (returned to normal short query mode) * Per-instance cost: ¥0.02-0.7 (returned to reasonable range) * Cluster auto-suspend: Working normally ### Lessons Learned **Technical Lessons** 1. **Dynamic table design**: Refresh interval should balance business needs and cost considerations 2. **Resource monitoring**: Establish multi-level cost and resource usage monitoring 3. **Configuration management**: Important configuration changes should be evaluated and tested **Operations Lessons** 1. **Timely discovery**: Establish automated anomaly detection mechanisms 2. **Rapid localization**: Master systematic troubleshooting methods 3. **Prevention first**: Avoid similar issues through best practices **Business Lessons** 1. **Cost awareness**: Technical decisions should consider cost implications 2. **Requirement assessment**: Balance between functional requirements and resource consumption 3. **Continuous optimization**: Regularly review and optimize resource usage ## Summary This real-world case perfectly demonstrates the core value of Singdata Lakehouse MCP Server in enterprise-level data platform operations: ### 🚀 Technological Innovation * **AI-driven natural language analysis**: Making complex data analysis simple and intuitive * **Intelligent issue diagnosis**: Automatically identify anomaly patterns and root causes * **Multi-dimensional correlation analysis**: Quickly extract key information from massive data ### 💰 Business Value * **Immediate cost savings**: Single case save ¥80+/day * **Significant operations efficiency improvement**: Troubleshooting time reduced from hours to minutes * **Risk prevention capability**: Avoid long-term losses from configuration errors ### 🛠️ Practicality * **Zero barrier usage**: No SQL knowledge needed, natural language completes complex analysis * **Immediately executable solutions**: Not just diagnosing issues, but providing specific solutions * **Knowledge transfer**: Analysis processes and methods reusable for similar scenarios Through this case, we see the enormous potential of AI technology in data platform operations. Singdata Lakehouse MCP Server is not just a tool, but an intelligent operations partner, helping users quickly locate issues, optimize costs, and improve efficiency in complex data environments. *** *This case is based on real user scenarios with anonymized data, demonstrating the practical value of Singdata Lakehouse MCP Server in cost management, troubleshooting, and operations optimization.* ## Reference [Instance-level Information Schema](instance-information_schema.md) [Dynamic Table](dynamic-table.md)