Frosty Friday

Snowflake Weekly Challenges

Archives for December 2022

by on 12 Comments

Week 27 – Basic

It’s hard to keep up with new Snowflake features coming out left, right and center. Here’s one that’s created a lot of buzz in the community – EXCLUDE and RENAME. So simple, and yet so much time saved!

Start Up Code
create or replace table frosty_db.challenges.week27 
(
    icecream_id int,
    icecream_flavour varchar(15),
    icecream_manufacturer varchar(50),
    icecream_brand varchar(50),
    icecreambrandowner varchar(50),
    milktype varchar(15),
    region_of_origin varchar(50),
    recomendad_price number,
    wholesale_price number
);

insert into frosty_db.challenges.week27 values
    (1, 'strawberry', 'Jimmy Ice', 'Ice Co.', 'Food Brand Inc.', 'normal', 'Midwest', 7.99, 5),
    (2, 'vanilla', 'Kelly Cream Company', 'Ice Co.', 'Food Brand Inc.', 'dna-modified', 'Northeast', 3.99, 2.5),
    (3, 'chocolate', 'ChoccyCream', 'Ice Co.', 'Food Brand Inc.', 'normal', 'Midwest', 8.99, 5.5);

Take the start up code above, and write a SELECT * query that:

  1. Excludes milktype
  2. Renames icecreambrandowner to ice_cream_brand_owner

This one should only take 5 minutes!

Remember, if you want to participate:

  1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
  2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
  3. Post the URL in the comments of the challenge

12 responses to “Week 27 – Basic”

  1. ChrisHastie avatar
    ChrisHastie

    A short challenge indeed, but still a very useful one. Thanks for highlighting the new functionality, I’ve already used it several times to massively simplify scripts on larger column sets!

    • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
  2. Babu S avatar
    Babu S

    Solution URL – https://github.com/Dhanushbabus/frostyfriday.git

    • dbellgar@gmail.com avatar
      dbellgar@gmail.com

      I had no clue about this feature. Thanks for highlighting it this week.

      • vdharst avatar
        vdharst

        My first FF!

        • Solution URL – https://github.com/vdharst/Frosty_Fridays/blob/main/week_27
      • fengliplatform avatar
        fengliplatform

        Learn something new every day!

      • Jamie Laird avatar
        Jamie Laird

        Nice intro to these parameters

        • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_27/ff_week_27.sql
      • zlzlzl2 avatar
        zlzlzl2

        Nice little trick to know.

        • Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF27_LZ.sql
      • Atzmonky avatar
        Atzmonky

        Indeed simple but quite handy…

        • Solution URL – https://github.com/Atzmonky/snowflake/blob/main/FFch27
      • arjan.loogman avatar
        arjan.loogman

        Short ‘n sweet!

        • Solution URL – https://github.com/arjansnowflake/Frosty_Friday/blob/main/Week_27/week_27.sql
      • Non-deterministicNorman avatar
        Non-deterministicNorman

        short and sweet

        • Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2027%20-%20basic%20/SELECT%20EXCLUDE%20RENAME
      • gergana98 avatar
        gergana98

        This is my version of the solution for this task. I hope you find it helpful! ^^

        • Solution URL – https://github.com/GerganaAK/FrostyFridays/blob/main/Week%2027%20%E2%80%93%20Basic
      • tomo avatar
        tomo

        It’s short, but helpful!
        I love ‘exclude & group by all’.

        • Solution URL – https://github.com/tomoWakamatsu/FrostyFriday/blob/main/FrostyFriday-Week27.sql

      Leave a Reply

      by on 5 Comments

      Week 26 – Intermediate

      This week we’ll be looking at a feature that has just hit Preview Feature – Open :
      Sending Email Notifications.

      We’re asking you to use this feature in conjunction with linked tasks to create a very small chain while tackling 2 different subjects.

      The Challenge

      The assignment for this week is two-fold :

      • Create a scheduled task (task #1) that inserts the current timestamp into a table every 5 minutes
      • Create a linked task (task #2) that sends an email that confirms the running of task #1 that contains the following information:
        “Task has successfully finished on <Account> which is deployed on <region> region at <timestamp>

      Remember, if you want to participate:

      1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
      2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
      3. Post the URL in the comments of the challenge

      5 responses to “Week 26 – Intermediate”

      1. ChrisHastie avatar
        ChrisHastie

        I feel like my solution to retrieve the message variables from the table with a Stored Procedure isn’t the right way to go about this. Anybody know of a way of passing variables from the output of one task into the another task?

        My solution still works though, we just have a Python Stored Procedure grabbing the most recent timestamp from the table after the first task has run.

        • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
      2. dsmdavid avatar
        dsmdavid

        Interesting one today. Wondering how much people would use the email notification feature.

        no dbt, as it would be just passing a long SQL file —
        I used the SYSTEM$SET_RETURN_VALUE to configure the value passed to the children tasks — need to wrap that around a begin / end though.

        https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_26_nodbt.sql

        I have my inbox flooded with the incoming emails, so it seems to be working — and now it’s a reminder to suspend the parent task!

        • Jamie Laird avatar
          Jamie Laird

          This seems like a handy feature

          • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_26/ff_week_26.sql
        • allllllllez avatar
          allllllllez

          It was an interesting challenge. I also used system$send_snowflake_notification() !

          • Solution URL – https://github.com/allllllllez/frosty_friday/blob/main/sql/week-26.sq
          1. allllllllez avatar
            allllllllez

            I posted the wrong URL… 🙁

            • Solution URL – https://github.com/allllllllez/frosty_friday/blob/main/sql/week-26.sql

        Leave a Reply