Author: Kevin

Today, I want to talk about that fun edge case when you’re having to join a table to itself in Snowflake. Does it happen often? Not unless your architect just hates you. Let’s use the normal pieces of data that everyone uses for this kind of thing – employee/manager relationships. We have our employee table…… Continue reading Desperately Seeking Connections by Joining with Snowflake

In our last post, we went over one way to get a sample of data. In the end was it right? Heck, no – at least not if we wanted a percentage of rows returned. Now, SAMPLE does work fine if you want a specific number of random rows returned each time. But, let’s face…… Continue reading Finding a Percentage of Rows from a Table

One of the things you may have noticed after reading our last post on Top (found here) is that sometimes SAMPLE doesn’t give the answer you want. For example, we can run the same query to get 20% of the table. Remember that this table has 290 rows in total. As you can see on…… Continue reading Questioning the Top Results in Snowflake

One of the items that we’re asked to do as data professionals is to only take the first few hundred rows from a table. Heck, it’s important enough that I’ve actually already talked about it in SQL Server here and a few other places also listed in that article. I’m sure you’re asking yourself “Sherpa,…… Continue reading Having Great Fun with Top in Snowflake

For those of you who have seen me speak at an event for SQL Server, you have probably seen my Windowing Functions presentation. (If not, you should – it could be life-changing!) If you’ve ever had the pleasure of working with the row_number function in SQL Server, you will enjoy working with it in Snowflake.…… Continue reading Watching Our Changes in Snowflake

When you work with data, you’ll probably need to work with dates at least once a month. That is the nature of the beast. Today, let’s compare working with them in SQL Server and Snowflake. I want to focus only on adding and subtracting months when provided with a specific day. In SQL Server, you…… Continue reading Working with Dates at the End of the Month

In Snowflake, you can do something akin to an APPLY, but not. That, my friends, is a LATERAL join. According to Snowflake documentation, this type of join “allows an inline view to reference columns from the table expression that precedes the inline view.” Yes, that’s gibberish. Let’s show what you’re doing. To test this out,…… Continue reading Laterally a new move

Sometimes you have to work with different kinds of data in Snowflake – just like in most databases. Today I want to work with some JSON – the same basic premise works for XML, Arrays, and other variants. SELECT * FROM TABLE(FLATTEN(input => parse_json(‘[3089, NULL, 217]’))); So, first, let’s go thru the parse_json portion. Notice…… Continue reading Flattening out data in SnowFlake

Sometimes, you need to work with tables that have a predefined list of values. You can have these automatically uploaded and refreshed to Snowflake via several means inherent to Snowflake (or any of a variety of methods from PowerShell, R, Python, etc..) But, sometimes, you want a small list of data inherent to a query…… Continue reading Making Baby Tables In SnowFlake