Watching Our Changes 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. I bet right now you’re asking yourself, “What makes you say that, Sherpa?” Well, my friend, it’s because it’s the exact same function call!

SELECT  SalesOrderID, 
	OrderDate, 
	CustomerID, 
	ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNbr
FROM Sales.SalesOrderHeader;

Notice that it has the OVER operator, you can order the data, and even partition the data as needed (Not seen in this example)!

But, as usual with Snowflake, there are even more functions we can work with! Sometimes, you just need to know when items are changed. Enter the CONDITIONAL_CHANGE_EVENT windowing function!

First, let’s create all of the data that we just happen to have around when we measure the temperature in our huge Sherpa of Data facilities – we don’t want it too hot or cold in here!

CREATE TABLE public.TemperatureAuditLog (
    Temperature number(4) NOT NULL,
    DateChange datetime NOT NULL);

INSERT INTO public.TemperatureAuditLog
SELECT 68, '2023-03-26 10:00' UNION ALL
SELECT 68, '2023-03-26 10:10' UNION ALL
SELECT 68, '2023-03-26 10:20' UNION ALL
SELECT 68, '2023-03-26 10:30' UNION ALL
SELECT 69, '2023-03-26 10:40' UNION ALL
SELECT 69, '2023-03-26 10:50' UNION ALL
SELECT 68, '2023-03-26 11:00' UNION ALL
SELECT 68, '2023-03-26 11:10' UNION ALL
SELECT 68, '2023-03-26 11:20' UNION ALL
SELECT 67, '2023-03-26 11:30' UNION ALL
SELECT 67, '2023-03-26 11:40' UNION ALL
SELECT 67, '2023-03-26 11:50';

From here, you’ll notice that we take a temperature every 10 minutes and log it into our table. We then want to know how many temperature changes we have. To find that, we would run the following:

SELECT *, 
    CONDITIONAL_CHANGE_EVENT(Temperature) OVER (ORDER BY DATECHANGE) AS Changes
FROM Public.TEMPERATUREAUDITLOG;

Notice that we’re telling our wonderful windowing function – CONDITIONAL_CHANGE_EVENT – what field we want it to look at for any changes – Temperature – and we want to look at it in a certain order – DATECHANGE.

Notice this shows that we have 3 temperature changes over our 2-hour time span. If you just want to know the time that each of these changes happens, all you have to do is grab the minimum time for each change number – and you’re off to the races.

For all of my friends out there asking “Sherpa, can we partition our data as well like many of the other windowing functions?” Yes, my friend, you can! (I just wanted to keep this example simple.)

With that my friends, I hope you’ve learned something about this great windowing function in Snowflake. Until next time!