Snowflake Trial

Week 11 – Basic

This week FrostyFarms are looking to create a set of chained tasks – two to be exact! The farms have plenty of cows who produce milk for us, and from there, some of that milk will be converted into skim/skimmed milk. We want our data to be edited to reflect the fact that the fat percentage of the milk will determine how the data should look.

Skim milk goes through the process of fat reduction in a centrifuge, therefore, whole milky rows won’t need columns relating to that process, but the skim milky rows will.

Create a parent and child task that will perform different actions on the different rows of data depending on the fat percentage of the milk.

-- Set the database and schema
use database <insert_db_name_here>
use schema <insert_schema_name_here>

-- Create the stage that points at the data.
create stage week_11_frosty_stage
    url = 's3://frostyfridaychallenges/challenge_11/'
    file_format = <insert_csv_file_format;

-- Create the table as a CTAS statement.
create or replace table frosty_friday.challenges.week11 as
select m.$1 as milking_datetime,
        m.$2 as cow_number,
        m.$3 as fat_percentage,
        m.$4 as farm_code,
        m.$5 as centrifuge_start_time,
        m.$6 as centrifuge_end_time,
        m.$7 as centrifuge_kwph,
        m.$8 as centrifuge_electricity_used,
        m.$9 as centrifuge_processing_time,
        m.$10 as task_used
from @week_11_frosty_stage (file_format => '<insert_csv_file_format>', pattern => '.*milk_data.*[.]csv') m;


-- TASK 1: Remove all the centrifuge dates and centrifuge kwph and replace them with NULLs WHERE fat = 3. 
-- Add note to task_used.
create or replace task whole_milk_updates
    schedule = '1400 minutes'
as
    <insert_sql_here>


-- TASK 2: Calculate centrifuge processing time (difference between start and end time) WHERE fat != 3. 
-- Add note to task_used.
create or replace task skim_milk_updates
    after frosty_friday.challenges.whole_milk_updates
as
    <insert_sql_here>


-- Manually execute the task.
execute task whole_milk_updates;

-- Check that the data looks as it should.
select * from week11;

-- Check that the numbers are correct.
select task_used, count(*) as row_count from week11 group by task_used;

RESULT

When you run the first query, the 3% data should look like

And the non-3% rows should look like
Whilst the second query should produce something like
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‘ (note joining our mailing list does not give you a Frosty Friday account)
  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.

Psst… in the near-future we might be announcing something exciting for those at the top of the challenge leaderboard. 😉

17 responses to “Week 11 – Basic”

  1. ChrisHastie avatar
    ChrisHastie

    Another week, another fun task. Thanks for putting these out each week, it’s a nice way to ease into Fridays!

    Solution URL:
    https://github.com/ChrisHastieIW/Frosty-Friday/blob/main/Week%2011%20-%20Easy%20-%20Tasks/Week%2011%20-%20Easy%20-%20Tasks.sql

      1. mat avatar
        mat

        Slightly delayed, my Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w11.sql

      2. Isabella avatar
        Isabella

        My (delayed) solution

        • Solution URL – https://github.com/isarenz/FrostyFridayChallenges/blob/main/FrostyFriday_Challenge_Week11.sql
      3. dsmdavid avatar
        dsmdavid

        My approach using dbt:

        – Create the stage
        – Load the data
        – Use post_hook to
        – create or replace the tasks
        – execute the 1st task immediately
        – suspend the task
        – Add a view on top to summarise the results

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

        • Solution URL – http://dvd-il-bucket-public.s3-website-eu-west-1.amazonaws.com/#!/model/model.frosty.challenge_11
      4. CBoyles avatar
        CBoyles

        Nice and easy one to learn about tasks if you haven’t used them before.

        • Solution URL – https://github.com/ChrisBBiztory/FrostyFriday/blob/main/Week_11.sql
      5. Pekka Kanerva avatar
        Pekka Kanerva

        Good rehearsal on tasks! The good thing with these challenges is that they make you read through the documentation to get the details right!

        • Solution URL – https://github.com/pekka-kanerva/frosty-friday/blob/main/week11/ff-week11-solution
      6. Atzmonky avatar
        Atzmonky

        A slight fashionable Delay… 🙂

        • Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ch11_Tasks.sql
      7. Cesare Simonini avatar
        Cesare Simonini

        My solution
        https://github.com/CSimonini/Frosty_Friday/blob/main/W11_Solution.sql

        • Solution URL – https://github.com/CSimonini/Frosty_Friday/blob/main/W11_Solution.sql
      8. Jamie Laird avatar
        Jamie Laird

        This was a great intro to tasks!

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

        Another good chance of learning. Took a while to understand the task scheduling.

        • Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF11_LZ.sql
      10. canonicalized avatar
        canonicalized

        Tasks are so cool!

        • Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK11.sql
      11. arjan.loogman avatar
        arjan.loogman

        Great exercise! Learnt a lot. Also learnt I need to learn a lot more, especially to use the documentation… :$

        • Solution URL – https://github.com/arjansnowflake/Frosty_Friday/blob/main/Week_11/week_11.sql
      12. edraetta avatar
        edraetta

        https://github.com/nimbusedo/frosty/blob/main/frosty11.sql

        • gerardnimbus avatar
          gerardnimbus

          That’s my solution!!! 😉 Thanks for your work.

          • Solution URL – https://github.com/gerardPerello/frosty_friday_11/blob/main/frosty11.sql
        • chrisv avatar
          chrisv

          Basic solution

          • Solution URL – https://github.com/chrisvnimbus/frosty/blob/7c5ffb25b1b2746f5ff93c30f50ff22e78b2dd36/frosty_wk11
        • lucabalduzzi avatar
          lucabalduzzi

          Nice challenge, tricked by the suspending!

          • Solution URL – https://github.com/LucaBalduzzi/FrostyFriday/blob/main/Week11/Week11.sql
        • Non-deterministicNorman avatar
          Non-deterministicNorman

          enjoyed this one

          • Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2011%20/tasks

        Leave a Reply