Horizon Power: Snowflake cost optimisation

Client Overview

Horizon Power engaged Mechanical Rock to conduct a comprehensive review of their Snowflake platform, initially focusing on cost optimisation but later expanding to include general best practices recommendations.

Initial Situation

• Daily Snowflake credit consumption was around 130 credits
• The data team had already achieved some optimisation, reducing consumption to 60-70 credits
• Approximately half of the remaining credits were consumed by a single Virtual warehouse
• The platform served customer-facing applications, including usage reporting
• Remote Community customer account data migration was increasing platform pressure

Key Challenges Identified

1. API and Query Performance

• High latency in customer-facing queries
• AWS Lambda timeouts due to long-running queries
• Inefficient query caching due to frequent data updates
• Corporate customers unable to effectively use the portal due to query performance

2. Infrastructure and Security

• Inconsistent naming conventions between production and non-production environments
• Password-based authentication still in use where OAuth or Key-pair authentication should be implemented
• Lack of proper CI/CD tooling for Snowflake changes
• No resource monitors implemented

3. Cost Management

• Inefficient warehouse auto-suspend settings
• High AWS storage costs
• Unnecessary compute usage from frequent data transformations

Solutions Implemented

1. Platform Optimisation

• Recommended splitting into multiple purpose-specific warehouses
• Suggested implementation of Snowflake DevOps practices
• Proposed adoption of resource monitoring and tagging strategy

2. Security Enhancements

• Recommended transition from password authentication to OAuth or Key-pair authentication
• Suggested implementation of proper RBAC controls
• Advised on break-glass access procedures with MFA enforcement

3. Cost Optimisation

• Recommended reduction of auto-suspend times to 60 seconds
• Suggested conversion of frequent tasks to serverless infrastructure (ie. AWS Lambda or ECS Fargate)
• Proposed implementation of lifecycle policies for AWS S3 storage

Results and Benefits

• Potential for significant credit consumption reduction
• Improved security posture
• Enhanced platform visibility and control
• Better positioned for future growth and customer demands
• A Framework for ongoing optimisation

Future Recommendations

• Implementation of Snowflake DevOps practices
• Adoption of resource monitoring
• Migration to more efficient authentication methods
• Regular review and optimisation of warehouse configurations
• Continued focus on query performance optimisation

This case study demonstrates Mechanical Rock's ability to deliver comprehensive platform optimisation while balancing security, performance, and cost considerations.