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