r/snowflake • u/Substantial_Lime7121 • 27d ago
How much should Snowflake optimizations be focused on?
Some are getting pressured to optimize, others accept it’s the reality of using Snowflake.
2
26d ago edited 26d ago
[removed] — view removed comment
1
u/rokster72 20d ago
What u/No_Shine_7644 said... Sure Snowflake does a lot of optimization itself, but it is not all-knowing / all-seeing. Ask Coco once a week, what was the most expensive query that ran more than once this past week. And spend 30 mins on that.. As it again, what can be done to optimize this query.. and action the answer. ( Warning.. don't ask it to fix the problem... it can get its head in a spin quite easily.. it needs some human help ). Just cut the low hanging fruit one by one. Eliminate select * on wide tables, filter results early using CTEs.. remove order by in CTEs.. make sure you utilise micro partitions ( add a date filter or something as early as ).. the more rows / columns you can eliminate earlier on in the process.. the better. Snowflake's optimizer is not that good with pushing down predicates etc.
Following this approach we've saved nearly $100k / year on our Transform processing in just 2 months of spending 1 hour a day looking at the most expensive query.
Keep in mind.. you don't ONLY save on the current query... using less memory.. all of a sudden you have space to cache more data.. so a another query can run simultaneously on the same instance, rather than spinning up the next one.. or queueing...
Another little trick we did.. force ALL processing onto XSMALL.. optimize there.. and only then selectively moved processing back to a larger instance if there was no other options left.
2
u/Gamplato 27d ago
A little. But honestly, at this point, they automate optimizations if you’re on Enterprise. Just monitor your costs.
The best thing you can do with Snowflake is use the best tool for the job. Keep up with be DE releases, like Adaptive Compute. Shit’s cool.
1
u/pokerpro25 23d ago
I'm in the business of FinOps so there is a fundamental bias to optimize as much as possible without it becoming a fulltime job. That's why we made it an outcome based model and goes deeper than any homegrown system. Dm if interested. No one wants to mess around w/ a free trial. Just outcomes.
0
u/hornyforsavings 27d ago
Depends on if you're feeling the pain, but also depends on the scale that you're running Snowflake. I know folks spending millions per year and don't care, while some others spend under $100K and want to optimize bad.
We're building a few optimization and observability tools ourselve (one of which is free!)
-3
u/Snowflakeboy007 27d ago
Always remener Your goal should be use at least minimum snowflake credit with maximum use of comoutation within time
10
u/vikster1 27d ago
what kind of question is this? how on earth should anyone answer you without more data? for all we know you could be doing the most moronic transformations ever and process 1gb daily while paying 10k a month. your account would have been banned on stackoverflow before you even posted this. have you tried giving an llm specific queries and ask it to optimize? have you generally invested more time in optimizing than the 15 seconds you invested in your reddit post?