My day job involves working with customers on performance problems. I have worked with dozens of customers and probably hundreds of queries just this year. I’ve been working in this role for more than two years now. What has all that taught me?
Snowflake Performance is Great, Out of the Box
The vast majority of Snowflake customers will never hear my name, and will never even know my team or our sister team in Engineering exist. I’m not a hero who comes in with my magic wand to make performance great for every customer. You don’t get to my team unless you’ve found an edge case, you’re pushing our platform pretty hard, you’re making some fairly significant mistakes, or a common issue you’re having has been thoroughly missed by others.
This is good. I’m often a fan of the 80/20 rule. But the first bit of performance effort is already done. While I might have to say that in my official role, this is not that official role, and I have seen some cases of absolutely terrible data models and really bad SQL just mildly slow because they happen to align with the strengths of the Snowflake platform.
Am I biased? Yes, absolutely. I also still believe IBM Db2 is technically a good product. I don’t hide my opinions or affiliations on this blog.
Follow the Data
I get asked a lot of questions. Even with a lot of experience, if I take a guess about a situation without looking at the data, my success rate is not all that great. I often have a guess about a problem going in based on descriptions, and it’s multiple times per month that guess is proved wrong.
It is absolutely critical to look at the data available to you – through the query profile, through QUERY_HISTORY, and other data. Following the data may sometimes lead to startling conclusions, but there are so many different directions it may lead you, even if you have pre-conceived notions.
If the Data is King, Testing is Queen
After following the data, or maybe even to generate more data, testing is absolutely essential. You see me test approaches and ideas in the blog articles here and my official articles on Medium, and that’s because that is part of how I do my job. If I’m seeing a performance pattern, I’ll often work up a minimal test case to reproduce it so I can isolate confounding factors. This is something any technician can and should do. It must be even easier when you can see data – in my role, I can see the queries and their performance characteristics, but never the data.
People are Bad at Writing Performant SQL
I can’t tell you how many times I’ve stared at a screen of SQL code, and asked myself “Wait, what?” or “Did they really just do the same thing three times?” or “Did they really just find a whole new way to replicate the results of a window function?”.
Some people are absolute wizards with SQL and can make it do things even I didn’t know were possible. Others stopped actively working on their SQL knowledge shortly after learning “SELECT *”, and seem to be trying to port procedural logic into a set-based language.
Even people with years of experience writing SQL on one platform may not realize some of the tricks for writing performant SQL on another platform. Spending time experimenting and learning and reading about SQL is seldom wasted if you’re in any kind of role that uses SQL.
Throughout my career, I’ve had technical topics where I’ve focused my learning a bit. Often for months at a time. The last 5 years, I’ve focused a couple of years on DevOps and Kubernetes when the job I was at required it. Then I spent a year with MySQL as my focus, and the last couple of years I’ve been heads-down learning Snowflake. Before that, for about 18 months, I focused my free learning time on SQL, and it has served me well. I can’t say I’m any guru on SQL specifically, but it was a weak spot for me at one time, and it isn’t any more. I absolutely recommend identifying an area to spend any free learning time you have. Going deep on topics has been extremely useful to me, even when I felt like “free learning time” was about 10 minutes a week.
Data Platform Comparisons are Often Biased
Nearly every data platform comparison I’ve seen has had bias in some direction baked into it. Sometimes it is Snowflake’s favor, and sometimes it is in favor of another platform. Sometimes the bias is recognized, and other times it is simply baked into people’s skill set. Knowing a platform well and recognizing that you can make that platform sing while you might not know the tricks to making the competition sing are critical for a real comparison.
Using code or a data model that is optimized for one platform and simply running it on another platform with the minimum possible changes is not a fair comparison. Many times you’ve spent years working on the data model or code on the platform you’re coming from, and differences in how things work mean that to get comparable (or better!) performance from another platform, some tuning is often needed.
I was recently involved in a comparison between another platform and Snowflake, and the initial implementation had Snowflake lagging behind the competition significantly. They worked with their Snowflake Solutions Engineer to involve my team. By simply adding cluster keys(cheap and fairly easy), we were able to get the performance to swing the other way. When the customer cut over, they found that the cost savings was even better than they estimated, which led to a very happy customer.
Try to set meaningful goals for performance and be prepared to apply basic techniques or ask for help when setting up an unfamiliar system.
A Little Architectural Knowledge Goes a Long Way
One of the things that had me comfortable on IBM Db2 was that I could reason within the system. Data points tell a story, and with a system that is not open source, there are often common values and common principles followed. Understanding even just a few basics like partition pruning (how it works and how to read the query profile for it), clustering, and the place of tools like materialized views and dynamic tables can go a long ways and give you great starting points.
There are a a lot of sources of advice. I’m sometimes appalled at the advice on the internet coming from humans. I now use a bit of AI help from time to time, as many people do. In my experience, somewhere around 80% of what they come up with is either good or at least not bad. Identifying that other 20%, however, is where the knowledge of the Snowflake platform comes in. When you’re building knowledge, you may be able to rely on the documentation, your SE, or support for a bit of validation.
Good Data Architecture and Well-Tuned SQL Win Every Time
When I worked with IBM Db2, only about 20% of the performance problems I saw were due to problems in system implementation or sizing. And every IBM Db2 system at that time had to have a fairly experienced person set it up. Snowflake is itself a well-architected system with many of the things I had to do manually in Db2 already in place. There is very little customer configuration needed. This means that the choices you do have – what your data model looks like, what SQL or other code you use – make that much MORE of a difference. Make terrible choices, get terrible performance.
Most Things Change
A couple of weeks ago, a feature related to performance was announced externally. I had never heard the term they were using before, and was very much surprised. I read a bit and found they had renamed something I had heard mentioned exactly twice, and had no idea it was being released as generally available so soon. This happens several times a year. If I have trouble keeping up when I am always cruising internal channels to learn what is up next, it must be even more difficult for customers.
If something suddenly changes, look for how and when it changed. Expect that things will change. If the Snowflake Performance Index is any indicator, the change will often be for the better.
There is Always Something New to Learn
An absolute minimum of once a week, I learn something new. More often it’s twice a day or more. There is always more to learn. Listen to the story the data is telling. Experiment. Redo experiments. Don’t be afraid to ask questions and figure out that you have been wrong.
