Week 18 – Difficult

Snowflake guest challenge!

For this week’s challenge, we’re very happy to announce a guest challenge designed and written by Snowflake’s own Daniel Myers! We’ve given him carte blanche to pick up any feature or subject that he could think of and, to be honest, we’re very happy he picked this one!
Don’t be put off thinking ‘but I’ve never done data science before’, use this as an opportunity to get that experience in!
Not sure where to begin? Check the resources underneath the Help / Hints / Tips section

Hey folks!

Frost Fridays have been incredibly awesome!

I’ve put together a proposed challenge that uses snowpark and data from our data marketplace to predict inflation for 2024 in the US!

The Challenge

Inflation is rising! Today’s challenge is to predict future inflation using actual economic data from the Snowflake Data Marketplace. Specifically, you will train a linear regression model inside snowflake that predicts the personal consumption expenditures (PCE) for any given future year! To answer this challenge, provide the PCE for the year 2024.

The Data

The data source: https://www.snowflake.com/datasets/knoema-economy-data-atlas/

Help / Hints / Tips

To view historical personal consumption data:

SELECT "Date", "Value" FROM "ECONOMY"."BEANIPA" 
WHERE "Table Name" = 'Price Indexes For Personal Consumption Expenditures By Major Type Of Product' 
AND "Indicator Name" = 'Personal consumption expenditures (PCE)' 
AND "Frequency" = 'A' 
AND "Date" >= '1972-01-01'
ORDER BY "Date"

Helpful quickstart (answers): https://quickstarts.snowflake.com/guide/data_apps_summit_lab/

Expected Answer

This time, we’re not showing you the entire answer but a value from a previous year so that you’ve got a hint if you’re on the right track :

You can see that this is for 2021, not the required answer for 2024

Shout out and thanks

We want to again thank Daniel for writing this challenge for us!
You can find and follow Daniel on LinkedIn and on Twitter

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‘ (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.

5 responses to “Week 18 – Difficult”

ChrisHastie

2022-10-14
The challenge expects a 2021 value of 116.23 for this prediction, which I am not seeing. I have tried filtering to 1972 onwards like in the suggested quickstart but then I get a value of 116.18

I have attempted several different filters for the input data and none reach the desired 116.23 so I think I must be missing a specific input.

After spending far too much time trying to figure out that value and failing, I thought I’d make up for it by converting the process into a UDF. So my solution includes a notebook to target a specific value (similar to the suggested quickstart) and a sql script that generates a UDTF to output a set number of predictions based on the input table.

If anybody can figure out where I’ve gone wrong to not get the 116.23 for 2021, please let me know!
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
Log in to Reply
1. ChrisHastie
  
  2022-10-17
  I have slightly modified my answer after some investigation and believe the input data has changed. I have adjusted my input data to also remove 2021, so the years are 1972 <= year < 2021
  
  The challenge expects a 2021 value of 116.23 for this prediction, whilst I now see a value of 116.22.
  
  Comparing the results and values with the original Snowflake Quick Starts code
  https://github.com/Snowflake-Labs/sfquickstarts/blob/master/site/sfguides/src/data_apps_summit_lab/assets/project_files/my_snowpark_pce.ipynb
  it appears the original data itself has changed in very small volumes.
  For example, the actual value for 2019 is now 109.933 when it used to be 109.922
  
  I believe this means my solution is correct and the input data itself has simply changed since the challenge was encoded.
  
  Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
  Log in to Reply
dsmdavid

2022-10-14
I’ve spent too much time trying to make it work all contained as part of the dbt project…without success. Training the model locally within dbt seems…unattainable at the moment.
I’ve created a python notebook instead that creates the udf.
Similarly to @ChrisHastie above, my numbers do not quite match. But I did ran some exploration and decided to set an arbitrary cutoff to start in 1980, so there’s that…

https://github.com/dsmdavid/frostyfridays-sf/blob/main/extra/ch18_create_udf_prediction.ipynb
Log in to Reply
zlzlzl2

2023-04-21
I did it with SQL UDF.
- Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF18_LZ.sql
Log in to Reply
Daryl Kit

2024-02-27
Unfortunately the data source for this challenge is no longer available in Snowflake Marketplace
Log in to Reply

Comments

ChrisHastie says

2022-10-14 at 13:06
The challenge expects a 2021 value of 116.23 for this prediction, which I am not seeing. I have tried filtering to 1972 onwards like in the suggested quickstart but then I get a value of 116.18

I have attempted several different filters for the input data and none reach the desired 116.23 so I think I must be missing a specific input.

After spending far too much time trying to figure out that value and failing, I thought I’d make up for it by converting the process into a UDF. So my solution includes a notebook to target a specific value (similar to the suggested quickstart) and a sql script that generates a UDTF to output a set number of predictions based on the input table.

If anybody can figure out where I’ve gone wrong to not get the 116.23 for 2021, please let me know!
- Solution URL - https://github.com/ChrisHastieIW/Frosty-Friday
Log in to Reply
- ChrisHastie says
  
  2022-10-17 at 12:04
  I have slightly modified my answer after some investigation and believe the input data has changed. I have adjusted my input data to also remove 2021, so the years are 1972 <= year < 2021
  
  The challenge expects a 2021 value of 116.23 for this prediction, whilst I now see a value of 116.22.
  
  Comparing the results and values with the original Snowflake Quick Starts code
  https://github.com/Snowflake-Labs/sfquickstarts/blob/master/site/sfguides/src/data_apps_summit_lab/assets/project_files/my_snowpark_pce.ipynb
  it appears the original data itself has changed in very small volumes.
  For example, the actual value for 2019 is now 109.933 when it used to be 109.922
  
  I believe this means my solution is correct and the input data itself has simply changed since the challenge was encoded.
  - Solution URL - https://github.com/ChrisHastieIW/Frosty-Friday
  Log in to Reply
dsmdavid says

2022-10-14 at 15:34
I’ve spent too much time trying to make it work all contained as part of the dbt project…without success. Training the model locally within dbt seems…unattainable at the moment.
I’ve created a python notebook instead that creates the udf.
Similarly to @ChrisHastie above, my numbers do not quite match. But I did ran some exploration and decided to set an arbitrary cutoff to start in 1980, so there’s that…

https://github.com/dsmdavid/frostyfridays-sf/blob/main/extra/ch18_create_udf_prediction.ipynb
Log in to Reply
zlzlzl2 says

2023-04-21 at 12:05
I did it with SQL UDF.
- Solution URL - https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF18_LZ.sql
Log in to Reply
Daryl Kit says

2024-02-27 at 16:09
Unfortunately the data source for this challenge is no longer available in Snowflake Marketplace
Log in to Reply