Snowflake pricing example: size matters12.02.2020
In the not so distant past, large data warehousing was reserved for the big companies – those, that could afford the high cost of hardware and compute power to support a data warehouse. But that was just the beginning of the significant up-front investments. Because of the complexity of the platforms companies also needed one or more database administrators and other IT staff members who maintained the resources or fixed software bugs.
Then, entered Snowflake…
…and times changed. Companies of any size are now able to obtain a cost-effective data warehouse they need. We’re not talking only about initial capex costs – you always need to think about the hidden charges, quotas and other penalties that sooner or later become frustrating. And when you have budgets and business plans you need to stick to, clear pricing for operating your cloud data warehouse is something you are grateful for.
With Snowflake, you can forget about following costs, which are rarely correctly measured, when deploying and running a warehouse:
- No hardware to install, operating system or database software to deploy. There are no patches to apply or database upgrades to schedule. Near Zero Management when setting up.
- No fixed charge per user or per CPU core. At Snowflake, there are no fix charges – all charges are usage based.
- No hidden quotas or price bumps. When you run queries in Snowflake, there are no added usage quotas or hidden price premiums. You pay only for what you use.
- If no queries are actively running, you can automate your warehouse to shift into suspend mode. Once suspended, charges are also suspended for idle compute time.
Let’s explore this further:
Snowflake brings a simple data warehouse pricing model
Snowflake easily helps you manage your data warehouse budget easily, cost-effectively and efficiently. Their pricing model includes only two items: the cost of storage and the cost of compute resources consumed:
- The charge for storage is per terabyte, compressed, per month.
Snowflake storage costs can begin at a flat rate of $23/TB, average compressed amount, per month accrued daily.
- The charge for compute on a virtual warehouse that enables you to load data and perform queries.
You can choose any number of virtual warehouses and you have eight “T-shirt” style sizes available: X-Small, Small, Medium, Large, and X- to 4X-Large. The bigger the size, the faster your computes will run. You can scale up or down in seconds and you can easily designate different sizes for users.
Customers pay for virtual warehouses using Snowflake credits.
They are used to pay for the consumption of resources on Snowflake. A Snowflake credit is a unit of measure, and it is consumed only when a customer is using compute resources.
Cost of credit starts at $2 – it depends on your region, preferred cloud provider (Azure, AWS and Google Cloud Platform) & chosen Snowflake platform version (Standard, Enterprise, etc.).
Each of the mentioned data warehouse sizes have a compute credit designation. As you go up in size, credits usage will vary.
Example: Oftentimes, being an eXtra Small is more than enough
We will take an example from an Eastern Europe market, where the majority of SMB and SME companies use Microsoft SQL Server for the data warehouse, because of affordability. Client has some 50+ dimensions, 10+ fact tables and couple of sources where the total fact volume is not above 200M.
Let’s say a company is choosing between two Snowflake platform versions – Standard & Enterprise. We’ll talk about the differences between the platforms at some other occasion, but for now, let’s just say that the biggest advantage of the Enterprise version is that it allows time travel, automatic horizontal scaling and it empowers you with accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period. It can, for example, restore data-related objects (tables, schemas, and databases) that may have been accidentally or intentionally deleted (more on Time Travel). On Azure cloud the price of credit on the Standard version will be $2, and $3.7 on Enterprise. Let’s first calculate the amount of credits they’ll need:
Storage cost is measured using the amount of storage used per month, after compression, for all customer data stored in Snowflake. For our case we’ll say 1TB is enough storage, so cost of storage would be:
1 TB x $23 x 12 months = $276 per year
Since Snowflake uses columnar storage and has high compression rates, it can mean that a time-series fact table of 7 columns of 5B rows can be just 100 GB.
They will not run their workload throughout the year, but only on weekdays, so we are considering 260 days per year.
- ELT production
With a shift to cloud-based data warehousing, ELT approaches have become more common as opposed to ETL. This means it first loads everything into target system and then applies any transformations after it is loaded. For the daily extraction, load and transformation of data in Snowflake they will need 3 hours per day, running on XS size cluster:
3 (hours per day) * 260 (days per year) * 1 (credits for XS Size) = 780 Credits per year
- Testing & Development
For developers who are involved in developing the required insights derived from the business they are allocating a separate warehouse for development sized XS as well. They will run it for 4 hours per day on the XS size:
4 (hours per day) * 260 (days per year) * 1 (credits for XS Size) = 1040 Credits per year
- BI production
The BI section will spend most of their time on the platform running reports and analysis. Let us assume we have up to 5 concurrent users (running queries in parallel at the same second) or 50 standard BI users. Since Snowflake charges only for processing time, which can be dropped down significantly when using BI tools running in-memory database such as Tableau, company is designating 5 hours of non-stop usage per day for the team (remember that you can automatically suspend the usage after the inactivity period and that the XS cluster needs a second to start).
5 (hours per day) * 260 (days per year) * 1 (credits for XS Size) = 1300 Credits per year
780 Credits (ETL production) + 1040 Credits (Development) + 1300 Credits (BI) = 3120 Credits
Now that we calculated the amount of credits they’ll need for the year, we can see how much each platform will cost. As we mentioned at the start, running on Azure, the price of credit on the Standard version will be $2, and $3.7 on Enterprise.
Standard: 3120 Credits x $2 = $6.240
Enterprise: 3120 Credits x $3.7 = $11.544
Add the $276 for the storage and you have the total costs per year!
Now on the other side, add also the needed man costs for managing and optimizing the existing solution, compared to almost 0 management activities on Snowflake and you get a better total cost of one system compared to the Snowflake. Based on our experience and current costs, it is also significantly lower than using even standard version of Microsoft SQL Server.
Note: Since the company in our case had 1 TB of data, we used XS size on a single cluster. Believe us, that is more than enough for the size as it can process up to 100M rows in seconds. For your reference, if you’d like to do play around with the cost, know that if you’ll be running your warehouse in XS size, you’ll use one credit per hour, for size S two, M size will use four, etc.