Cool Items in Snowflake that Aren’t in SQL Server

Yes, I know. The name of today’s post is certainly clickbait. But it’s true! There are so many things you can do in Snowflake that may – or may not – be possible in SQL Server, at least not without several tricks and possibly low-level querying. So, let’s start shall we?

What did I run?

If you’re running an audit process in SQL Server, you can do this fairly easily. But how many of us work in a place that requires this or has enough space to do this? Sadly, very few of us…

If you have Query Store running (and why wouldn’t you?) you can find a specific query you were running. But you have to have a specific something you can search for…

But can you get just a list of what you specifically ran in order? Not that I can find….

In Snowflake, on the other hand, I can list all of them with one query:

SELECT *
FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY())
ORDER BY START_TIME DESC; 

Yes, you can see several more fields than I show in the screenshot. All kinds of data can be gathered from how long the query took overall, the warehouse size, if there were any error messages, how many rows were inserted as a result, etc…

Now, this query will only return the last 100 queries. And if you haven’t run 100 queries over the previous 14 days, you may not get 100. But there’s another query that can do more that’s eerily similar!

SELECT *
FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER(
    user_name => 'DataSherpa',
    result_limit => 10000
))
ORDER BY START_TIME DESC; 

This query shows up to the last 10,000 queries that my username specifically ran – over the last 14 days – in order. As you’ve noticed I keep saying “14 days.” That’s right, my friends – the table I’m viewing here only keeps 14 days worth of data. I think that should be more than enough time to get the data I need.

But most importantly, there is no lag in using this table. If a query has started at all – then it’ll be in this query. Such awesomeness in one query!

Some drawbacks with this last query:

• Maxes out at 10,000 queries.
• The query only allows you to pick one user at a time.
• Figuring out which columns can be filtered in the table() function or the WHERE clause is a little special and must be played with.
• Again, only over the last 14 days.

This time, we’ve worked on getting a simple – or not-so-simple – list of all of the queries that we’ve run on our Snowflake instance. Is it perfect? No, but not a bad start. Hence, in my next blog post, I will show you other queries that work with this data set that will give you different ways of getting to this data.