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.
HELP! I don’t have the sample dataset
— 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;
Thanks for continuing to bring attention to new features
Thank you for the Memoizable challenge.
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
Neat functionality. Thanks!