Snowflake Trial

Week 40 – Basic

With the news about Snowflake Summit ramping up, I’d like to take a look at a feature that was announced at the last Summit :
Memoizable functions

Now, these functions allow your functions to make use of the cache!
The benefit of using a memoizable function is to improve performance for complex queries, such as multiple-column lookups in mapping tables referenced within a row access policy or masking policy.

We’re starting everyone off easy though to simple find a number that can be used more often. For this we’re using a sample dataset that should be in your account. If you don’t have access , the code below the challenge to get access :
– Use the SNOWFLAKE_SAMPLE_DATA.TPCH_SF100 Sample dataset
– Create a memoizable function that returns the Revenue ( Extended_Price with taking into account the given discount) for the European Region.

— Create a database from the share.
CREATE DATABASE SNOWFLAKE_SAMPLE_DATA FROM SHARE SFC_SAMPLES.SAMPLE_DATA;

— Grant the PUBLIC role access to the database.
— Optionally change the role name to restrict access to a subset of users.
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE_SAMPLE_DATA TO ROLE PUBLIC;

4 responses to “Week 40 – Basic”

  1. ChrisHastie avatar
    ChrisHastie

    Thanks for continuing to bring attention to new features

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

    Thank you for the Memoizable challenge.

    • Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w40.sql
  3. Jamie Laird avatar
    Jamie Laird

    Here’s my solution. I found this post from Filipe Hoffa helpful in understanding what a memoizable function does. https://medium.com/snowflake/faster-snowflake-udfs-and-policies-with-memoizable-b84544c1bf5

    • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_40/ff_week_40.sql
  4. dsmdavid avatar
    dsmdavid

    Neat functionality. Thanks!

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

Leave a Reply