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. Notice that the DateAdd and DateDiff functions return a…… Continue reading This is the end… of the month.
Tag: Snowflake
Distinctity. Yes, it’s a thing.
There are a lot of things that can be said about the keyword DISTINCT within Snowflake. One of them you’re probably used to, and one of them you probably aren’t. SELECT DISTINCT DEPARTMENT_ID FROM public.employees; In this case, DISTINCT is simply ensuring we have unique data. If you remove the DISTINCT keyword, the version I’m…… Continue reading Distinctity. Yes, it’s a thing.
More Fun Dealing with Strings
When 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…… Continue reading More Fun Dealing with Strings
Creating Random Data in Snowflake
One of the many things that the business team asks me to do is to create random-ish data. Thankfully, in Snowflake, there are many ways to make this happen. Today, I want to go thru just a few of them. Perhaps the one that most people are familiar with is making Snowflake create a random…… Continue reading Creating Random Data in Snowflake
Cleaning Up Your Data the Easy Way in Snowflake
I don’t know about y’all but I have to work with dirty data. A lot. When you’re working with dirty data, one of the many things you can run into is that when you try to divide two numbers – you can cause a huge error because you tried to perform a mathematical no-no -…… Continue reading Cleaning Up Your Data the Easy Way in Snowflake
Computing New Columns in Snowflake for fun and profit
Sometimes to make our lives easier, we, as database engineers, can create a table that automatically tells us the answer as we need it – or at least how we tell it we want it. In SQL Server, we create what is called “Computed Columns.” DROP TABLE IF EXISTS dbo.Payroll; GO CREATE TABLE dbo.Payroll (…… Continue reading Computing New Columns in Snowflake for fun and profit
Where We’re Going, We Don’t Need Limits
There is always a lot of fun things you can learn to do with Snowflake. Sometimes they’re even in the places you least expect to find anything even slightly different. Take for example LIMIT. SELECT * FROM public.employees LIMIT 20; Most of us have written the statement above – or something like it – a…… Continue reading Where We’re Going, We Don’t Need Limits
Desperately Seeking Connections by Joining with Snowflake
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
Finding a Percentage of Rows from a Table
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
Questioning the Top Results in Snowflake
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