by 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.
Here you can find the skeleton script
-- 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
- 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)
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- 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. 😉
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
Slightly delayed, my Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w11.sql
My (delayed) solution
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
Nice and easy one to learn about tasks if you haven’t used them before.
Good rehearsal on tasks! The good thing with these challenges is that they make you read through the documentation to get the details right!
A slight fashionable Delay… 🙂
My solution
https://github.com/CSimonini/Frosty_Friday/blob/main/W11_Solution.sql
This was a great intro to tasks!
Another good chance of learning. Took a while to understand the task scheduling.
Tasks are so cool!
Great exercise! Learnt a lot. Also learnt I need to learn a lot more, especially to use the documentation… :$
https://github.com/nimbusedo/frosty/blob/main/frosty11.sql
That’s my solution!!! 😉 Thanks for your work.
Basic solution
Nice challenge, tricked by the suspending!