4 ms·

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, b

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.

0cf8612b2e1e 3y ago
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.
- datadrivenangel 3y ago
  $58 per hour over a year on average. Sounds like a big big warehouse. For scalability.
  - ludicity 3y ago
    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.
- kwillets 3y ago
  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.
chinchilla2020 3y ago
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.
- Johnny555 3y ago
  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-Snowflake-Cloud-Data-Platform 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.
  - fbdab103 3y ago
    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.
    - Johnny555 3y ago
      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.
themark 3y ago
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.

0cf8612b2e1e 3y ago

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.

datadrivenangel 3y ago
$58 per hour over a year on average. Sounds like a big big warehouse. For scalability.
- ludicity 3y ago
  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.
kwillets 3y ago
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.

chinchilla2020 3y ago

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.

Johnny555 3y ago
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-Snowflake-Cloud-Data-Platform 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.
- fbdab103 3y ago
  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.
  - Johnny555 3y ago
    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.

themark 3y ago

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.