Sherpa of Data

Tag: Snowflake

  • Watching Our Changes in Snowflake

    —

    by

    Kevin
    in

    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.…

  • Checking if your XML is actually well-formed

    —

    by

    Kevin
    in

    Sometimes you’ll find that you will have XML in your database. This could be for various reasons – from storing the XML after receiving an API response to keeping it in a table because a web developer couldn’t figure out another way to store their data. Sometimes – no matter how much you trust your…

  • Working with Dates at the End of the Month

    —

    by

    Kevin
    in

    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…

  • Laterally a new move

    —

    by

    Kevin
    in

    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,…

  • Flattening out data in SnowFlake

    —

    by

    Kevin
    in

    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. So, first, let’s go thru the parse_json portion. Notice that the JSON contained within is truly a…

  • Making Baby Tables In SnowFlake

    —

    by

    Kevin
    in

    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…

  • Sound of Silence in Snowflake

    —

    by

    Kevin
    in

    One of those functions that can be helpful – in the right circumstances – is SOUNDEX. If you hop into the Wayback Machine with Sherman, Mr. Peabody, and me, I created a blog post about this very subject – found here. In Snowflake, the SOUNDEX function works exactly the same way: In Snowflake, there is…

  • Inserting an Insert into Snowflake

    —

    by

    Kevin
    in

    Whenever I say the keyword INSERT in conjunction with databases, we – as data developers – automatically think that we’re going to be putting data into a table (or multiple tables with Snowflake – see here). With Snowflake, that isn’t always true. Now, you’re probably saying to yourself “Sherpa has obviously lost his/her mind. Who…

  • Expunging data, the Snowflake way.

    —

    by

    Kevin
    in

    Sometimes, in Snowflake as well as in SQL Server, you’re forced to delete data. Hopefully, you’re not deleting from the main table of a database in Production, but that is definitely one way to wake up in the morning. If you’re only deleting from one table, then Snowflake is like SQL Server in the formatting…

  • Insert differently with Snowflake and SQL Server

    —

    by

    Kevin
    in

    One of the things that I’ve gotten used to with Snowflake is that it’s just different. Well, today, I’ve got some goodness for y’all – and in a place that you really wouldn’t expect – the INSERT statement. For most of your everyday use, it works exactly as you’d expect. As some would say, it…