Archives for March 2023

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.

Security is a top concern at Frosty Friday, and we need to make sure that not just anyone can see our customer’s contact info.

create or replace table customer_deets (
    id int,
    name string,
    email string
);

insert into customer_deets values
    (1, 'Jeff Jeffy', 'jeff.jeffy121@gmail.com'),
    (2, 'Kyle Knight', 'kyleisdabest@hotmail.com'),
    (3, 'Spring Hall', 'hall.yay@gmail.com'),
    (4, 'Dr Holly Ray', 'drdr@yahoo.com');

Apply a column level masking policy that shows me the following when I query using my role:

And the following when I query using any other role:

Remember, if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge

With every month, we get more Snowflake updates! This month, we’ve seen the general release of Stream on Views hit General Availability!
To celebrate this, we’ve created this basic challenge, focussed on Streams.

Use the following block of code to quickly form your table/view basis :

We’ve had some issues with sales being rung-up but then not showing up in the system anymore. We’ve got a feeling that they’re being removed and would like to very quickly see if that’s the case. Could you help out the store with the following?

Use the above structure to :
– Add a stream to the employee_sales VIEW
– Keep track of every deletion that was made
– Move these into a new table called ‘deleted_sales’

Recently, I’ve been training people for SnowPro Core exams and a lot of the feedback has been that unstructured data and various URLs has come up a lot. If you want to pass that exam, this challenge is probably the best challenge you can do!

We want you to create an external stage with a directory table, and query it. Each row should be accompanied by a:
– Scoped File URL: to give scoped access for internal uses
– Stage File URL: to give permanent access to a stage
– Pre-signed URL: to give external users access

create or replace storage integration week37_si
    type = external_stage
    storage_provider = 's3'
    storage_aws_role_arn = 'arn:aws:iam::184545621756:role/week37'
    enabled = true
    storage_allowed_locations = ('s3://frostyfridaychallenges/challenge_37/');

The final product should look like:

P.S. I personally think you should click on some of the URLs…maybe all of them.

Remember, if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge