by 
To celebrate Women in Data and the upcoming Data+Women event, we’ve partnered up with Gaia Pometto , a data consultant in the Netherlands, to release a challenge for the Data+Women event. She’ll go through the challenge, live, at the event and you can find her on LinkedIn, Twitter and YouTube.
She also wanted to give a special mention to colleague Atzmon Ben Binyamin who collaborated with her on this challenge and was a great help throughout the process. You can find Atzmon on LinkedIn.
The Challenge
In order to prepare a future ingestion flow of data, your organization wants to get data from a weather API into Snowflake. At the end of the flow, it is expected to create a fact table to be used by consumers.
Your colleague Atzmon has already pulled a sample of the weather data from the API and momentarily stored it in an s3 bucket. The s3 bucket contains a JSON file with hourly data for 7 days.
Your Flow will consist of 3 layers (schemas) of data:
a. Landing zone (external stage, raw table – weather_raw)
b. Curated zone (raw data parsed table – weather_parsed)
c. Consumption zone (aggregated data table – weather_agg)
Objectives:
1. Create a stage for the landing layer and copy the JSON.
2. Parse the JSON and create a table – weather_parsed (in the curated zone).
3. Create a table for consumption weather_agg (in the consumption zone).
The table should have aggregates per day for:
a. Temperature
b. Wind speed
c. Distinct ‘icon’ definitions for a day (e.g. [“cloudy”, “rain”])
Here is the link to the s3 bucket (hint: bucket = frostyfridaychallenges, path = challenge_25).
Good luck!
The solution should look something like this :
Remember, if you want to participate:
- Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
- 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
10 responses to “Week 25 – Beginner”
-
It was a nice surprise seeing this one launch early this week!
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
Cheating a bit this time since I created the challenge myself haha…but good to share the solution for those interested 🙂
- Solution URL – https://github.com/DownToEarthDataTips/FrostyFridays/blob/main/Week%2025
-
Ah, this was a good one for dbt!
– create stage & landing zone: https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_25_1.sql
– create curated: https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_25_2.sql
– create output: https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_25_3.sql -
Here’s my solution 🙂
https://github.com/CSimonini/Frosty_Friday/blob/main/W25_Solution.sql- Solution URL – https://github.com/CSimonini/Frosty_Friday/blob/main/W25_Solution.sql
-
Really enjoyed this one!
- Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_25/ff_week_25.sql
-
nice one
- Solution URL – https://github.com/lbinfolab/frosty-friday/blob/89b5170fad9e735626b491248968eb58f5f46982/week25.sql
-
Difficulty will be viewing the large json file, once separated it’s easier to check structure.
- Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF25_LZ.sql
-
A very nice one!
- Solution URL – https://github.com/arjansnowflake/Frosty_Friday/blob/main/Week_25/week_25.sql
-
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%2025%20%E2%80%93%20Basic
Leave a Reply
You must be logged in to post a comment.