Tuning a Snowflake Database for Improved Performance and Cost Savings

Moser Consulting was recently tasked with reducing costs and ensuring more consistent SLA compliance in a company's Snowflake environment. While Snowflake differs from traditional and other cloud databases, it is still a SQL-based database, and many of the same tuning techniques apply. However, understanding Snowflake's architecture is crucial, as it offers unique cost-saving opportunities not found in traditional databases.

Snowflake is arguably the most usage-based database on the market. Its pricing model, which charges based on compute usage at a minimum of one minute, is essential to comprehend for cost savings. As a database tuner, this is great news because poorly written queries will cost more in real dollars, making it easier to demonstrate return on investment (ROI) after optimization.

For example, the following two queries return the same result set. However, the first one uses a range search on date and the other uses a function on the data to return all June 2022 data. There is a significant performance difference between these two.

Traditional database tuning involves a combination of query tuning and index tuning. While index tuning is less relevant in Snowflake due to its data handling, query tuning remains crucial, and Snowflake introduces warehouse tuning as an additional factor.

Moser Consulting aimed to save money and more consistently meet SLAs for a client that loaded over 40 million rows of data daily from SQL Server. With a multi-phase approach, we identified several areas for improvement and conducted a series of tests to verify initial findings and determine the best way forward.

Our first win was to improve the performance of the heaviest queries, which also increased SLA consistency. The most significant cost-saving measure was reducing warehouse size, as they were over-provisioned, and the majority of data loading processes ran single-threaded. We were able to cut warehouse size in half, resulting 60% cost savings from the peak.

One common misconception about Snowflake is that it has auto-tuning and doesn't require query tuning. This is not true. To save money in Snowflake, it's crucial to understand that it's a true usage-based database, charging for the resources used for as long as they are used.

In conclusion, optimizing a Snowflake database involves understanding its unique architecture and pricing model. By focusing on query tuning and warehouse tuning, organizations can reduce costs and improve performance, ensuring more consistent SLA compliance.

Contact Moser Consulting for more information.