Hacker News Reader

Comment by cainxinth | Hacker News Reader

TLDR: They changed the idle time settings for running queries in their Snowflake database. Originally, they were set to idle for 10 minutes after every query, but most queries only took about 2 seconds to run.

View on HN

Yeah. I went through a rigorous exercise with sales last year to try to get a good estimate on what it would cost to run the database for a year for our application. They said it would be around 150k.

I threw all of their configuration advice out the window and run all extra small at 1 minute idle. No issues for my use case and we paid 17k for the year.

I have to expect that snow will change their pricing model soon.

I know nothing of Snowflake pricing, but how big is their infrastructure that leaving it on for an extended amount of time could see that much savings?

To put it another way, to rack up $500k spend for 30 days of constant provisioned time:

$500k/(30*24) = $694/hour

Presumably, with the 10 minute blocks, there was idle time where the spend was zero, so the instantaneous amount would be higher.

$58 per hour over a year on average. Sounds like a big big warehouse. For scalability.

This is approximately right. They also almost have one warehouse per two data engineers. I've been told there's a reason and usage analysis has been done by actually smart people, but it just sounds incredibly suspicious to me.

He said a few thousand queries per week, which is maybe 3000, or 156k/year. That's 1.56 million cluster minutes, or $500k/1.56M = $.32/minute. A single snowflake node is $.05/minute, so maybe it's an 8-node cluster. They could shrink the node count down to 4 or 2 to save more money.

How long does a snowflake machine take to spin up? Why have an idle time at all?

I have never used snowflake so I'm not sure how this works.

Their virtual warehouses usually take only a few seconds to spin up, but can take longer (especially for large warehouses).

If you keep the warehouse running, there's a fair amount of caching going on behind the scenes that can make your queries run faster on the warm warehouse (how much faster depends on the query) . This article shows one example - a cold query ran in 20 seconds, after caching it ran in 1.2 seconds on a warm warehouse:

https://community.snowflake.com/s/article/Caching-in-the-Sno...

So you don't always want to set the idle shutoff time to the minimum.

There are probably other use cases, but I only think of data warehouses for supporting batch/analytical workloads where latency is not a problem because the entire job takes minutes/hours to complete. In which case cold start does not significant.

Analysts and other data consumers don't want to wait 30 seconds for cache warming every time they refresh their dashboard.

Also, since you're paying by the second, it can be cheaper to keep a warehouse running longer to avoid having to re-warm the cache with every query.

Reply on news.ycombinator.com