A Fine Slice Of SQL Server
Kevin Wilkie creates a function:
Sometimes, you’ll need to create functions that do a particular thing. They’re not always pretty. Some of them look like they should have been thrown out with the bathwater.
Unlike SQL Server, user-defined functions in Snowflake can be done in several different languages. They can be done in Java, JavaScript, Python, Scala, or plain-old SQL.
Which means that Postgres users will be a bit more comfortable here than SQL Server users.
Comments closedKevin Wilkie is implicit in this whole thing:
One of the ways we can get better at speed is to attempt several slightly different ways that can get you (hopefully) the same data. Some tables work better with one query while some work better with another query.
Let’s work through a scenario in Snowflake and we’ll see which one is faster under “normal” conditions.
Click through for a few query examples and how they end up performing.
Comments closedKevin Wilkie counts the months before his release:
Figuring out the number of years or months between 2 dates shouldn’t be hard. For some reason though, for a lot of database systems it is.
Kevin gives us answers in SQL Server and Snowflake, including a bonus bit of code to correct a problem that he spotted in the first bit of code. Date math is hard.
Comments closedRebecca O’Connor builds a map:
Power BI can leverage Geospatial data from snowflake with my favourite map visual – Iconmap – https://www.icon-map.com/ . Icon map can render points, polygons and linestrings using ‘Well Known Text’ format (WKT).
Snowflake supports converting geospatial datatypes to WKT. Not only this, Snowflake has the capabilities to perform the Engineering and analytical needs for Geospatial analysis without using any other tool. And the results can be visualised in a variety of medias such as Tableau, Hex, Carto or even a Custom built Streamlit application. I have written a Streamlit blog on this very recently.
Click through for information on how to get the data shaped in a way that Power BI likes.
Comments closedKevin Wilkie min-maxes his way through life:
By the title of this post, you’re thinking “Ho hum. Sherpa will tell us about the stupid MIN and MAX functions in Snowflake and how great they are.”
Well, they are great. Let’s not kid ourselves about that one.
But Kevin pulls the rug out from under us, as this isn’t a post about MIN() and MAX().
Nikola Ilic needs more than a lakehouse:
In the previous parts of the Data Modeling for mere mortals series, we examined traditional approaches to data modeling, with focus on dimensional modeling and Star schema importance for business intelligence scenarios. Now, it’s time to introduce the concept of the modern data platform.
As usual, let’s take a more tool-agnostic approach and learn about some of the key characteristics of the modern data estate. Please, don’t mind if I use some of the latest buzzwords related to this topic, but I promise to reduce their usage as much as possible.
Lakehouses are getting closer to being good enough, but the performance needs to be there, especially if you eventually have virtual data warehouses sitting on top of lakehouse data to deal with the need for structured fact-dimensional data for reporting tools.
Comments closedNikola Ilic gives us a primer on Kimball-style fact and dimensional modeling:
Before we come up to explain why dimensional modelling is named like that – dimensional, let’s first take a brief tour through some history lessons. In 1996, a man called Ralph Kimball published a book “The Data Warehouse Toolkit”, which is still considered a dimensional modelling “Bible”. In his book, Kimball introduced a completely new approach to modelling data for analytical workloads, the so-called “bottom-up” approach. The focus is on identifying key business processes within the organization and modelling these first, before introducing additional business processes.
This is a really good overview of the topic, though I’m saddened that “dimensional bus matrix” didn’t make the cut of things to discuss. Mostly because I like the name “dimensional bus matrix.”
Comments closedKevin Wilkie is waiting for the calendar to change:
In SQL Server, we’re used to finding the end of the month via a few different methods. We can always use the DateAdd and DateDiff functions to get our data – which sometimes takes a bit of work – or we can use the EOMonth function.
Read on to see what tools are available for Snowflake users.
Comments closedKevin Wilkie remains distinct:
Now, the more fun – “new-ish” – version of the DISTINCT keyword.
Let’s take two values – A and B. Let’s define A = 7 and B = 2.
Snowflake will allow you to ask if A IS DISTINCT FROM B. Thankfully, in this case, it is.
Click through to see how this works. Also note that this syntax is available in SQL Server 2022.
Comments closedWhen you’re working with a database, it’s very hard to not deal with strings at some point in your journey. There are lots of different functions that you will be working with when you’re working with strings. Today, I want to go over some of the basic ones that you’ll use in Snowflake.
The first two that you’ll deal with make the string either upper or lowercase. Yes, that’s right – you’ve probably figured out the names of the functions already. UPPER() and LOWER() are the 2 functions respectively.
Kevin mentions title capitalization (though not by name) and the quick rule depends on which rulebook you’re using. I grew up with MLA, which I summarize as:
And a quick bit of advice: understanding title capitalization really does make you look more professional, I promise. Unless we’re using different rulebooks, in which case at least one of us is a heretic.
Comments closed