Controlling costs in Snowflake — the Iron Triangle
Snowflake has an unfair reputation for being expensive. In my experience costs are in fact far more controllable and transparent in Snowflake than in platforms where storage and compute are coupled. Snowflake’s decoupled storage and compute architecture gives greater flexibility in balancing customer need versus spend. However with this flexibility comes a certain responsibility. When approaching costs in Snowflake, I find that there are 2 questions that you need to have clear answers to:
- Data Latency — how quickly do you need to transform data for your end user needs? Would your organisation spend more to make data available faster?
- Query performance — is read query performance important to you? Would your organisation spend more to get better query performance?
If you have a clear answer to these questions, the Snowflake platform provides the flexibility to balance these two requirements against cost in a controllable and transparent manner.
Data Latency
Snowflake is an OnLine Analytical Processing (OLAP) platform. It uses a columnar format called micro-partitions for storing data. The advantages of a columnar storage format like micro-partitions are excellent compression and reduction in the overall storage footprint. This can make querying large datasets very efficient as there is less physical data to read from disk.
Micro-partitions are immutable. Once written, they cannot be changed. Therefore when a record in a micro-partition is updated or deleted, the entire micro-partition that contained the older record needs to be marked as deleted and all other data in that micro-partition written to a new micro-partition.
Therefore writing to storage in Snowflake is not a linear equation. The time to write records isn’t directly related to the number of records being written or updated but more related to the number of micro-partitions involved. The more often you transform data, the more micro-partitions are involved and higher your costs will be. If you can delay transforming data, you create less micro-partitions and lower cost. Conversely, transforming data more often will generate more micro-partitions and increase cost.
In addition, transforming data more often will create more smaller micro-partitions and this will have an adverse affect on query performance.
Query performance
The performance of reading data from any database is generally only as good as how well the data is indexed based on the fields used to search the data. Therefore if you want good query performance, you need to spend more on arranging the data when it is being written to storage. If you are writing data at a high frequency/low latency, this will result in more micro-partitions which can have an impact on performance when reading it back. There are a number of approaches to maintaining good micro-partitions on a table but all involve extra compute and cost.
If your organisation values read query performance over everything, the simplest approach will be to define a clustering key on the table and let Snowflake maintain the micro-partitions on the table. However if your organisation also wants low data latency, frequent data changes will drive up the auto-clustering costs on tables. In the following blog, I have outlined a number of approaches to maintain micro-partitions on tables without using auto-clustering.
The good thing is that if your use case supports a write once read often workload, spending more on writing your data for optimal read query performance will be offset by lower compute costs to execute those read queries.
Framework
I would recommend having a framework in place that allows you to show the cost trade offs of having lower latency and/or better query performance. Something like this allows teams to work with their customers to decide whether they want to spend more to have lower data latency and faster read query performance. Better yet if you can put real money numbers on the diagonal costs line.
This is the secret sauce of a decoupled storage and compute architecture like Snowflake. Costs are very transparent and very controllable with the correct approach and visibility to data latency and read query performance.
Summary
The triangular interdependency of cost, data latency and read query performance can be a very powerful concept in controlling and bringing visibility to your Snowflake costs. Writing data more often will result in higher costs and lower read query performance. Maintaining micro-partitions for better query performance will also result in higher costs. However if done for the right reasons, this extra cost will be offset by lower query cost. If it doesn’t you may need to question why you are spending this money to maintain micro-partitions if the table isn’t being used enough to pay for itself. Again, the ability to balance customer need versus cost is one of the great features of Snowflake.
