DEV Community

loading...
Cover image for Right Sizing Snowflake Warehouses / Compute
Leading EDJE

Right Sizing Snowflake Warehouses / Compute

Jason C
Making technology the easy part by solving the hard business problem.
Updated on ・3 min read

Snowflake is a cloud based data warehousing platform that can handle huge workloads at scale. But figuring out how much it will cost you can be tricky.

Storage is pretty straight forward to calculate, it's $40 a month per TB (you can reserve space at a cheaper rate of $23 / TB if you know how much you need up front).

It's much harder to figure out how many compute credits you'll use in a month. Most companies will pay $2 to $4 per credit, but what does that really mean?

Credits are used by Snowflake compute warehouses. In order to run queries or ingest data you'll need at least one warehouse running. The size of the warehouse has a direct impact on how many credits an hour it will cost.

Warehouse Size

Servers / Cluster

Credits / Second

X-Small

1

0.0003

Small

2

0.0006

Medium

4

0.0011

Large

8

0.0022

X-Large

16

0.0044

2X-Large

32

0.0089

3X-Large

64

0.0178

4X-Large

128

0.0356

costs as of 1/7/2021. See Snowflake Warehouse Documentation for additional info.

Let's dig into these numbers. To run a Small warehouse it costs 2 credits an hour, at $3 a credit, that's $6 an hour. $144 a day, $52.560 a year..

Right Sizing Warehouses

A couple of quick tips:

  • Turn on Auto-suspension and Auto-resumption! This turns your warehouses off when not in use and can save you big bucks.
  • Warehouses don't cost anything if they are off. Don't be afraid to define a bunch of them, just make sure they are off when not needed.
  • Spinning up a warehouse costs a minimum of 1 minute and is billed by the second after the first minute.
  • There is a small lag to spin up a suspended warehouse.

Oversized Warehouses

Let's say we have a Large warehouse set to auto suspend after 30 seconds and we run a query that only takes a few seconds. We would be billed a full minute for this query.

In this scenario we might want to look at using an X-Small instead. Even if the query took a few seconds longer on the X-Small we'd save credits if the query still finishes under a minute.

1 minute on a Large = 0.132 Credits, X-Small = 0.018, Saving 0.114 credits, That's $0.342 at $3 a credit!

This is where people start to think, we should run X-Small warehouses to save money, but that might actually cost you more!

Undersized Warehouses

Now let say we have a long running query. In a real world scenario, I built an aggregation that queries tables with 17 billions rows (about 466GB of data).

On a Large warehouse this query took 1 hour and 44 minutes, which cost 13.9 credits; $41.70 @ $3 a credit per run.

Moving this query to a 4XL finished in just under 6 minutes! Keep in mind 6 mins on a 4XL still cost $36.60 for 12.2 credits, but running on the bigger warehouse actually saved 1.7 credits; That's $5.10 in savings!

We run this aggregation every day, in one year we'll save over $1,800 on this one query alone.

Conclusion

Picking the right size warehouse can be tricky! Test heavy queries on large warehouses and move fast queries down to smaller warehouses.

Discussion (0)

Forem Open with the Forem app