Reducing Credits Usage with Optimized Snowflake Environment
We optimized a front office revenue cycle management organization’s Snowflake environment, reducing overall credit usage and enhancing efficiency.
|
Situation
• The client, a revenue cycle management organization, had an ordering and scheduling analytics solution running intra-day updates on a Snowflake database, feeding a direct query analytics solution for external users. • Users required the most up-to-date data, but the Snowflake processing costs were unsustainable. • With a budget of 50,000 credits per year, the organization was forecasting a spend of over 100,000 credits. • The goal was to reduce credit usage by implementing best practices and optimizing query efficiency, including warehouse structure and clustering operations.
Approach
• Warehouse Tuning: Identified several warehouses experiencing spillage, a costly procedure. Adjusted warehouse configurations by splitting and resizing them to balance the load more efficiently. • Query Optimization: Analyzed and rewrote inefficient queries to run faster and reduce credit usage. Identified that high volumes of delete and update statements were major cost drivers. • Clustering: Applied clustering to a limited set of tables to improve query performance and reduce costs. • Load Management: Adjusted warehouse size during low-usage periods, such as weekends, to conserve credits.
|
Impact
• Achieved a 20-35% reduction in credit usage through the following measures: o Decreased warehouse size over weekends, resulting in fewer credits required during low-usage periods. o Implemented clustering on select tables, with immediate testing showing a 22% reduction in charges for one table set. o Optimized complex queries by breaking them into simpler, faster-processing individual queries, reducing one specific query’s execution time from 22 minutes with 1.86 TB of spillage to 12 minutes with no spillage. • Ensured sustainable Snowflake processing costs, enabling the organization to stay within budget while maintaining up-to-date data for external users.
|
